sql - 元组的重复频率

Question

ID              Date
7019730         16-03-2015
7019721         16-03-2015
7020250         16-03-2015
7219750         08-04-2015
7019730         22-03-2015
7019721         25-03-2015
7019730         28-03-2015
7019721         30-03-2015

对于上表，我想提取 ID 出现的频率以及日期之间的差异作为另一列。我的意思是输出应该是这样的:

ID              Date          Occurrence    Frequency
7019730         16-03-2015          1       0
7019721         16-03-2015          1       0
7020250         16-03-2015          1       0  
7219750         08-04-2015          1       0
7019730         22-03-2015          2       6
7019721         25-03-2015          2       9
7019730         29-03-2015          3       7
7019721         30-03-2015          3       5

Answer 1

这是一种方法。假设您输入的表名是 D:

declare @d1 table(x bigint, y bigint, id int, dt date);

insert @d1 (x, y, id, dt)
select 
    row_number() over (order by id, [date]) x, 
    (row_number() over (order by id, [date]) - 1) y, 
    id, 
    [date]
from 
    D
order by 
    id, [date];

select * 
from (
    select
        a.id, 
        a.dt, 
        row_number() over (partition by a.id order by a.id) as occurence, 
        coalesce(datediff(day, b.dt, a.dt), 0) as frequency
    from @d1 a
        left outer join @d1 b
        on a.y = b.x
        and a.id = b.id
    ) as results
order by occurence, id