使用模时如何分组

Question

对于每家公司，我想汇总最近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

编辑：以下代码符合我的需要。"收入"是使用最近一个季度和之后的三个季度进行汇总。

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

Answer 1

你可以试试这个：

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