查詢同一行的 Min、Max 和關聯列數據

我有查詢，其中按站點和月份返回平均值。我想知道的是每個站點的每月平均值的最小值和最大值以及（困難的部分）每個發生的月份。

這是一個例子：

SQL Fiddle 的東西在這裡

CREATE TABLE events (
   esite     integer NOT NULL,
   edate     timestamp with time zone NOT NULL,
   evalue    integer NOT NULL
);

INSERT INTO events Values
   (1, '2016-01-03', 11),
   (2, '2016-01-05', 90),
   (1, '2016-01-08', 7),
   (2, '2016-01-10', 40),
   (1, '2016-01-15', 12),
   (1, '2016-01-18', 66),
   (2, '2016-01-22', 54),
   (2, '2016-02-03', 70),
   (2, '2016-02-05', 56),
   (1, '2016-02-08', 61),
   (2, '2016-02-10', 23),
   (1, '2016-02-15', 30),
   (1, '2016-02-18', 15),
   (1, '2016-02-22', 41);

我正在尋找一個查詢，它返回（按站點）最小和最大月平均值evalue以及最小值和最大值出現的月份。我可以通過使用下面的查詢得到這個：

SQL Fiddle this

select esite, date_trunc('month', edate) as emonth, round(avg(evalue),2) as evalue_avg from events
 Group by esite, emonth

產生以下輸出：

esite | emonth                      | evalue_avg
2     | January, 01 2016 00:00:00   | 61.33
1     | January, 01 2016 00:00:00   | 24
1     | February, 01 2016 00:00:00  | 36.75
2     | February, 01 2016 00:00:00  | 49.67

現在對於我遇到困難的部分，我需要產生以下結果 - 基本上是每個站點發生的最小值、最大值和日期（月份）。

期望的輸出：

-- (one row per site)
esite | avg_min | eval_avg_min_date          | avg_max | eval_avg_max
1     | 24.00   | January, 01 2016 00:00:00  | 36.75   | February, 01 2016 00:00:00
2     | 49.67   |February, 01 2016 00:00:00  | 61.33   | January, 01 2016 00:00:00

我四處搜尋並看到了一些使用視窗和橫向連接的範例，但我沒有成功讓它們中的任何一個工作。這可能是一個支點，但由於我沒有固定數量的站點，這對於 PostgreSQL 來說往往很困難。

我猜是否有人有使用 Postgres 9.4+ 的簡單方法。

這個問題很老了，但 CommunityBot 一直在碰到它。讓我們添加一個正確的答案。

將您的 avg 計算移到 CTE 中，並在兩個連接DISTINCT ON查詢中使用結果：

WITH cte AS (
  SELECT esite, date_trunc('month', edate) AS emonth, round(avg(evalue), 2) AS evalue_avg
  FROM   events
  GROUP  BY esite, emonth
  )
SELECT *
FROM  (
  SELECT DISTINCT ON (esite) 
         esite, evalue_avg, emonth
  FROM   cte
  ORDER  BY esite, evalue_avg, emonth
  ) a
JOIN  (
  SELECT DISTINCT ON (esite) 
         esite, evalue_avg, emonth
  FROM   cte
  ORDER  BY esite, evalue_avg DESC, emonth
  ) z USING (esite);

db<>在這裡擺弄

（甚至可以在過時的 Postgres 9.4 中工作。）

看：

• 在每個 GROUP BY 組中選擇第一行？
• 從每組的第一行和最後一行獲取值

我建議你做這樣的事情——不要走每月排的路。

當您有 5 (10…15… x) 年的數據時會發生什麼？

這是卓越的恕我直言。您始終可以使用CROSSTABtable 函式，但我建議不要使用它。

SELECT
 esite,
 MIN(evalue), 
 MAX(evalue),
 AVG(evalue)::INT,
 EXTRACT(MONTH FROM edate) AS emonth,
 CASE
   WHEN EXTRACT(MONTH FROM edate) = 1 THEN 'January'
   WHEN EXTRACT(MONTH FROM edate) = 2 THEN 'February'
   -- <... fill in rest of months here ...>
 END AS litMonth
FROM events
GROUP BY esite, emonth, litMonth
ORDER BY esite, emonth -- , min, max, avg;

這給出了結果：

esite;     min;   max;   avg;    emonth;    litmonth
----------------------------------------------------
   1;       7;    66;    24;     1;         January
   1;      15;    61;    37;     2;        February
   2;      40;    90;    61;     1;         January
   2;      23;    70;    50;     2;        February

請注意，我CAST將 AVG 用作INTEGER- 您當然可以隨意使用ROUND它。您可以在後面添加EXTRACT(YEAR FROM edate) AS eyear,以EXTRACT(MONTH...獲得更清晰和更優雅的結果。

引用自：https://dba.stackexchange.com/questions/138677