TSQL按两列分组

Question

你好，感谢您提前帮助。我有以下表格：

create table temp_vol (symbol nchar(10), dseqkey int, volume float)

以下是表格中的一些数值：

symbol     dseqkey    volume (no commas in the actual values)
C          20672      422,764,512 
F          20672       47,144,600 
F          20671       51,695,000 
C          20671      371,950,496 
F          20670       48,680,600 
C          20670      654,208,512 
C          20669      626,121,600 
F          20669       93,489,504 
C          20668      712,674,304 

我需要的输出是每个符号在每个dseqkey上的2天简单移动平均值((volume dseqkey + volume dseqkey-1) / 2)。我可以得到所有符号在一个dseqkey上的移动平均值，也可以得到一个符号在所有dseqkeys上的移动平均值，但似乎无法同时获得两者的结果。

- Frank Zappa

Answer 1

您可以使用row_number()为每一行编号。这样可以查找前面的行。以下是编辑后的示例，用于计算3个数值的移动平均值：

; with numbered as 
        (
        select  ROW_NUMBER() over (partition by symbol order by dseqkey) rn
        ,       *
        from    temp_vol
        )
select  cur.symbol
,       cur.dseqkey
,       avg(window.volume) as MovingAverage
from    numbered cur
join    numbered window
on      cur.symbol = window.symbol
        and window.rn between cur.rn - 2 and cur.rn
group by
        cur.symbol
,       cur.dseqkey
order by
        cur.symbol
,       cur.dseqkey

这将打印：

symbol     dseqkey     MovingAverage
---------- ----------- ----------------------
C          20668       712674304
C          20669       669397952
C          20670       664334805,333333
C          20671       550760202,666667
C          20672       482974506,666667
F          20669       93489504
F          20670       71085052
F          20671       64621701,3333333
F          20672       49173400

测试数据：

if OBJECT_ID('temp_vol') is not null
    drop table temp_vol
create table temp_vol (symbol nchar(10), dseqkey int, volume float)
insert temp_vol values
('C', 20672,  422764512 ),
('F', 20672,  47144600  ),
('F', 20671,  51695000  ),
('C', 20671,  371950496 ),
('F', 20670,  48680600  ),
('C', 20670,  654208512 ),
('C', 20669,  626121600 ),
('F', 20669,  93489504  ),
('C', 20668,  712674304 )

- Andomar

---

@user590822：示例SQL现在使用了3天的移动平均值，如果将cur.rn - 2更改为cur.rn - 29，则可以将其扩展到30天。 - Andomar

这段描述表明 dseqkey 值保证没有间隙：a 2-day simple moving average ((volume dseqkey + volume dseqkey-1) / 2)。在这种情况下，可以使用 Andomar 的解决方案而不需要 CTE + ROW_NUMBER()。删除 WITH 部分后，我认为应该将 numbered 替换为 temp_vol，将 rn 替换为 dseqkey。 - Andriy M

Answer 2

我现在不在一台有数据库引擎的电脑上，所以我不能确定这个：

根据评论更新

SELECT A.symbol, A.dseqkey, AVG(B.volume) MovingAverage
FROM temp_vol A
LEFT JOIN temp_vol B
ON A.symbol = B.symbol AND A.dseqkey BETWEEN B.dseqkey - 30 AND B.dseqkey
GROUP BY A.symbol, A.dseqkey

- Lamak

---

以上似乎是最接近的，谢谢。当我最初发布时，为了简单地传达问题，我使用了2天移动平均线。我真正需要的是30天移动平均线。因此，以上内容很接近，但我认为我需要使用聚合函数AVG才能使其工作？以下是目前有效的两个查询，我只是无法将查询/结果组合成我真正需要的内容。1）从temp_vol中选择SYMBOL，AVG（Volume）AS VOLAVG30，其中dseqkey在20642和20673之间，在Symbol ASC上按组排序 - Frank Zappa

SELECT x.dseqkey, AVG(y.VOLUME) AS moving_average FROM temp_vol x, temp_vol y WHERE x.dseqkey>=30 AND x.dseqkey BETWEEN y.dseqkey AND y.dseqkey+29 AND Y.Symbol=X.Symbol AND X.Symbol='C' GROUP BY x.dseqkey ORDER BY x.dseqkey DESC - Frank Zappa

抱歉耽搁了，我之前没在电脑旁。好的，我已经修改了查询语句，应该反映了你的评论。如果可以的话，请告诉我它是否有效。 - Lamak