將 12 個月的滾動/移動平均值、中位數、最小值、最大值、百分位數等計算為 Postgres 中的單個查詢

我的問題有兩個部分：

. 首先，如何調整下面的程式碼來計算 12 個月移動平均值、中位數等？照原樣，查詢將返回每日、每週、每月或每年值的列表（取決於指定的內容），但僅針對單個“週期”。也就是說，如果指定了“年份”，則將計算 2019 年、2018 年、2017 年等全年的匯總統計數據，但數據會返回。如果是“月”，那麼摘要統計資訊將僅針對給定的月份，例如 2019 年 10 月、2019 年 9 月、2019 年 8 月等……但是如何計算例如。2019 年 8 月至 2019 年 10 月，然後是 2019 年 7 月至 2019 年 9 月等 3 個月的單一值？

. 其次，能夠使用任何時間間隔 + 週期數（如“4 週”、“6 個月”、“12 個月”、“2 年”）的通用版本的程式碼是什麼？

SELECT
 date_trunc('year', t.time2), -- or hour, day, week, month, year
 count(1), 
 percentile_cont(0.25) within group (order by t.price) as Q1,
 percentile_cont(0.5) within group (order by t.price) as Q2,
 percentile_cont(0.75) within group (order by t.price) as Q3,
 avg(t.price) as A,
 min(t.price) as Mi,
 max(t.price) as Mx

FROM my_table AS t
GROUP BY 1
ORDER BY date_trunc

數據表由單個交易列表組成（日期 -> 時間 2 為時間戳；價格為 bigint）。

用於計算 1、2、3、4、..6、…12 年/季度/月/週/天/小時移動平均值、中位數、百分位數等的通用程式碼匯總統計數據，其中表格包含單個時間列表記錄（如銷售交易等）

WITH grid AS (
     SELECT end_time, start_time
     FROM (

           SELECT end_time
         , lag(end_time, 12, 'infinity') OVER (ORDER BY end_time) AS start_time
           FROM (

               SELECT
               generate_series(date_trunc('month', min(time2))
             , date_trunc('month', max(time2)) + interval '1 month', interval '1 month') AS end_time
               FROM   my_table

               ) sub

          ) sub2

     WHERE end_time > start_time

)

SELECT
   to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d
 , count(e.time2)
 , percentile_cont(0.25) within group (order by e.price) as Q1
 , percentile_cont(0.5) within group (order by e.price) as median
 , percentile_cont(0.75) within group (order by e.price) as Q3
 , avg(e.price) as Aver
 , min(e.price) as Mi
 , max(e.price) as Mx

FROM grid a

LEFT JOIN my_table e ON e.time2 >= a.start_time

                  AND e.time2 <  a.end_time

GROUP  BY end_time
ORDER  BY d DESC

答案看起來很“簡單”，但非常令人失望的是，沒有人願意提供一些程式碼片段來幫助我走上正軌……

關於時間的說明：

. 腳本的第一部分生成一個開始-結束時間範圍的列表以執行查詢。

. 第二部分計算統計數據並輸出每個時間間隔的結果（例如，對於“12 個月中位數”，第一列中顯示的時間將是“12 個月到給定月份的月底”）

. PosgreSQL 中的約定是“月底”實際上是下個月的“0 小時”（即 2019 年 10 月結束是“2019.11.01 at 00:00:00”）。這同樣適用於任何時間範圍（例如，2019 年底實際上是“2020.01.01 at 00:00:00”）。

. 因此，出於顯示目的，您可能希望使用以下內容：

to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d

否則，如果您不包括“-interval ‘1 month’”，則截至 2019 年 10 月的 12 個月移動統計數據將顯示為“for” 2019 年 11 月 1 日 00:00:00（截至 2019-11）。

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