如何在postgresql中为查询的前n行分配唯一标识符

Question

在postgres SQL中，如何将唯一标识符(rn？(分配给查询的前n行，并将唯一标识符分配给随后的n行？目的是根据股票代码和唯一标识符进行分组，详细信息请参阅实际屏幕截图和预期屏幕截图。

实际查询

SELECT 
*,
SUM(eps_diluted) OVER (PARTITION BY ticker ORDER BY rn ASC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS test_eps_diluted
FROM(
SELECT 
"PK",
ticker,
period_end_date,
eps_diluted,
ROW_NUMBER() OVER ( PARTITION BY ticker ORDER BY period_end_date DESC ) rn
FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED" 
--WHERE ticker = 'ACN' 
ORDER BY ticker, period_end_date DESC
) q
ORDER BY ticker, period_end_date desc 

实际结果

预期结果

Answer 1

我找到了一个使用模的解决方法。此解决方案允许我对最近一个季度、随后的4个季度等的eps_dluted求和。有关详细信息，请参阅解决方案屏幕截图。

SELECT 
* 
FROM (
SELECT 
*,
SUM(eps_diluted) OVER (PARTITION BY ticker ORDER BY rn ASC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS sum_eps_diluted,
1+ ((rn - 1) % 4) AS modulo
FROM(
SELECT 
"PK",
ticker,
period_end_date,
eps_diluted,
ROW_NUMBER() OVER ( PARTITION BY ticker ORDER BY period_end_date DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) rn
FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED" 
ORDER BY ticker, period_end_date DESC
) t1
--WHERE 1+ ((rn - 1) % 4) = 1
ORDER BY ticker, period_end_date desc ) t2
WHERE modulo = 1

解决方案