TSQL Group-By Over Two Columns
Hi Thanks in advance for开发者_运维百科 helping. I have the following table:
create table temp_vol (symbol nchar(10), dseqkey int, volume float)
here are some values in the table:
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
The output I am seeking is a 2-day simple moving average ((volume dseqkey + volume dseqkey-1) / 2) for each symbol, for each dseqkey. I can get the moving average working for all symbols across one dseqkey. I can get the moving average working for all dseqkeys across 1 symbol. I cannot seem to get both working together.
You could give each row a number using row_number()
. That allows you to look up the previous row(s). Example edited for a 3-value moving average:
; 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
This prints:
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
Test data:
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 )
I'm not on a computer with a database engine, so I can't be sure about this:
UPDATED following comment
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
精彩评论