Home SQL Server: Compute group size and group number in a single query
Reply: 1

SQL Server: Compute group size and group number in a single query

Oleg Melnikov
1#
Oleg Melnikov Published in 2017-12-07 00:55:00Z

Is there a way to compute group size and group number in a single query instead of using subqueries, in SQL Server 2017 (Developers Edition). I thought this can be done with OVER(PARTITION BY) and ROW_NUMBER or RANK or NTILES somehow. A runtime efficiency is also important.

Here is a quick example that I produced with two subqueries. It groups records based on ID and date (ignoring time) and adds two fields with parameters for the groups:

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)

The output has two extra columns: one with a group number per each ID, another has the size of the group per each 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
Gordon Linoff
2#
Gordon Linoff Reply to 2017-12-07 00:57:08Z

You seem to want dense_rank():

select t.*,
       count(*) over (partition by id, cast(dtm as date)) as grpsize,
       dense_rank() over (partition by id order by cast(dtm as date)) as grpid
from #t t;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.331182 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO