在SQL Server 2017(开发者版)中,是否有方法在单个查询中计算组大小和组数,而不是使用子查询。我想这可以用
OVER(PARTITION BY)
和
ROW_NUMBER
或
RANK
或
NTILES
以某种方式运行时效率也很重要。
下面是我用两个子查询生成的一个快速示例。它根据ID和日期(忽略时间)对记录进行分组,并为组添加两个带参数的字段:
CREATE TABLE #t(ID INT, DtTm SMALLDATETIME)
INSERT INTO #t SELECT 1, '2017-01-07 06:00:00';
INSERT INTO #t SELECT 1, '2017-01-07 07:00:00';
INSERT INTO #t SELECT 1, '2017-01-07 08:00:00';
INSERT INTO #t SELECT 1, '2017-01-13 09:00:00';
INSERT INTO #t SELECT 1, '2017-01-13 10:00:00';
INSERT INTO #t SELECT 2, '2017-01-14 11:00:00';
INSERT INTO #t SELECT 2, '2017-01-14 12:00:00';
WITH a AS (
SELECT ID, Dt=CAST(DtTm AS DATE), GrpSize=COUNT(*)
FROM #t GROUP BY ID, CAST(DtTm AS DATE)),
c AS (
SELECT *, GrpNum=ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Dt)
FROM a)
SELECT #t.*, c.GrpSize, c.GrpNum
FROM #t JOIN c
ON #t.ID=c.ID AND c.Dt=CAST(#t.DtTm AS DATE)
输出有两个额外的列:一个是每个ID的组号,另一个是每个ID的组大小。
ID DtTm GrpSize GrpNum
1 2017-01-07 06:00:00 3 1
1 2017-01-07 07:00:00 3 1
1 2017-01-07 08:00:00 3 1
1 2017-01-13 09:00:00 2 2
1 2017-01-13 10:00:00 2 2
2 2017-01-14 11:00:00 2 1
2 2017-01-14 12:00:00 2 1
