CODEWITHSUNDEEP
sqlgroup-bycountwindow-functionsgaps-and-islands
Jack Armstrong
Jack Armstrong

Reputation: 1249

SQL a cumulative distinct count

I have a SQL table that lists individual events and I am trying to aggregate to get a group of events as follows.

id |Name | Date|
0  |A    |2018-05-08
1  |A    |2018-05-09
2  |B    |2018-05-11
3  |B    |2018-05-12
4  |A    |2018-05-17
5  |A    |2018-05-17
6  |A    |2018-05-18
7  |C    |2018-05-25
8  |C    |2018-05-26
9  |B    |2018-05-27

Becomes:

Name|Group
|A  |1
|B  |2
|A  |3
|C  |4
|B  |5

This I believe is some form of a Count(), then OVER BY, which have always tripped me up. I do not know what I would even count over because there is little grouping these Names together. So far, I have the following:

select
    Name
    ,Count(Name)
from table
Group BY
    Name

Upvotes: 1

Views: 117

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

There is no reason to think of this as a gap-and-islands problem. I mean, it is, but there is a simpler solution.

In this case, use lag() and row_number():

select name, row_number() over (order by date, id) as grp
from (select t.*,
             lag(name) over (order by date, id) as prev_name
      from t
     ) t
where prev_name is null or prev_name <> name;

Upvotes: 1

GMB
GMB

Reputation: 222462

This is a gaps and island problem, where you want to group together "adjacent" rows.

One approach relies on the difference between row numbers to indentify the groups. If id is continously increasing by 1, consider:

select 
    name,
    row_number() over(partition by name, id - rn order by min(date)) grp
from (
    select
        t.*,
        row_number() over(partition by name order by date) rn
    from mytable t
) t
group by name, id - rn
order by grp

Otherwise, we can generate the id with row_number():

select 
    name,
    row_number() over(partition by name, rn1 - rn2 order by min(date)) grp
from (
    select
        t.*,
        row_number() over(order by date) rn1,
        row_number() over(partition by name order by date) rn2
    from mytable t
) t
group by name, rn1 - rn2
order by grp

Upvotes: 1

Related Questions