對于每家公司，我想總結最近 4 個季度的收入，然后是隨后的 4 個季度，依此類推（有關詳細資訊，請參閱所附螢屏截圖）。我怎樣才能做到這一點？SQL 查詢和結果 https://i.stack.imgur.com/wWhhb.png
SELECT
ticker,
period_end_date,
revenue,
1 ((rn - 1) % 4) AS test
FROM (
SELECT
ticker,
period_end_date,
revenue,
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY period_end_date DESC) rn
FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED"
--WHERE ticker = 'ACN'
ORDER BY ticker
) q
uj5u.com熱心網友回復：
你可以試試這個：
SELECT ticker
, period_end_date
, total_revenue
FROM (
SELECT ticker
, period_end_date
, SUM(revenue) OVER (PARTITION BY ticker ORDER BY period_end_date DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS total_revenue
, max(period_end_date) OVER (PARTITION BY ticker) AS period_end_date_max
FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED"
--WHERE ticker = 'ACN
) q
WHERE EXTRACT(MONTH FROM period_end_date) = EXTRACT(MONTH FROM period_end_date_max)
ORDER BY ticker, period_end_date ASC
uj5u.com熱心網友回復：
以下代碼滿足我的需求
SELECT
ticker,
period_end_date,
SUM(revenue) OVER (PARTITION BY ticker ORDER BY period_end_date DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS total_revenue
FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED"
--WHERE ticker = 'ACN'
ORDER BY ticker
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/388203.html
標籤：PostgreSQL的 通过...分组 模数 秩 行号
上一篇：計算每種型別的禮物發送了多少
下一篇：EF或Npgsql中的常量表