我有简单的 T-SQL 查询,它计算所有记录的行号、行数和总数量:
DECLARE @t TABLE
(
id varchar(100),
volume float,
prev_date date
);
INSERT INTO @t VALUES
('0318610084', 100, '2019-05-16'),
('0318610084', 200, '2016-06-04');
SELECT
row_num = ROW_NUMBER() OVER (PARTITION BY id ORDER BY prev_date),
rows_count = COUNT(*) OVER (PARTITION BY id ORDER BY prev_date),
vol_total = SUM(volume) OVER (PARTITION BY id ORDER BY prev_date),
*
FROM @t;
我得到以下结果:
然而,这不是我所期望的:在所有两行中行数必须是 2 并且总成交量必须是 300:
解决方法是添加无界前导和无界后继之间的行。不过,我想,一定有其他方式.
最终我发现 ORDER BY 子句必须使用id场而不是上一个日期 field:
row_num = ROW_NUMBER() OVER (PARTITION BY id ORDER BY id),
rows_count = COUNT(*) OVER (PARTITION BY id ORDER BY id),
vol_total = SUM(volume) OVER (PARTITION BY id ORDER BY id)
进行此更改后,查询的输出将符合预期。
但!我不明白为什么会这样?顺序如何影响分区?