Как я могу идентифицировать группы последовательных дат в SQL?

Я пытаюсь написать функцию, которая идентифицирует группы дат и измеряет размер группы.

До сих пор я делал это процедурно в Python, но я хотел бы перенести его в SQL.

например, список

Bill 01/01/2011 
Bill 02/01/2011 
Bill 03/01/2011 
Bill 05/01/2011 
Bill 07/01/2011

должны быть выведены в новую таблицу как:

Bill 01/01/2011  3 
Bill 02/01/2011  3 
Bill 03/01/2011  3 
Bill 05/01/2011  1 
Bill 07/01/2011  1

В идеале это также должно учитывать выходные и праздничные дни — даты в моей таблице всегда будут с понедельника по пятницу (я думаю, что смогу решить эту проблему, создав новую таблицу рабочих дней и последовательно пронумеровав их). Кто-то на работе предложил мне попробовать CTE. Я довольно новичок в этом, поэтому я был бы признателен за любое руководство, которое кто-либо мог бы предоставить! Спасибо.


sql date gaps-and-islands
person Pythonn00b    schedule 10.07.2012    source источник
comment
Какую СУБД вы используете? SQL - это просто язык.   -  person Lamak    schedule 10.07.2012
comment
Как вы определяете праздники?   -  person Gordon Linoff    schedule 10.07.2012
comment
Если вы хотите иметь возможность учитывать выходные и праздничные дни, ваша идея сохранить таблицу дат — это то, что вам нужно. Она обычно называется «Файл календаря» (или «Таблица») и имеет группу дополнительного использования; среди прочего, финансовые даты (т. е. период финансового года, день периода). Если в вашем бизнесе его еще нет, создайте его — примеры сценариев есть везде.   -  person Clockwork-Muse    schedule 10.07.2012
comment
Когда вы говорите «учитывать выходные и праздничные дни», что это значит? Означает ли это, что они не учитываются в течение «последовательных» дней (т. е. допускаются промежутки)? Или что эти дни должны автоматически генерировать пробелы?   -  person Clockwork-Muse    schedule 10.07.2012

Ответы (2)


arrow_upward
21
arrow_downward

Вы можете сделать это с умным применением оконных функций. Рассмотрим следующее:

select name, date, row_number() over (partition by name order by date)
from t

Это добавляет номер строки, который в вашем примере будет просто 1, 2, 3, 4, 5. Теперь возьмите разницу с датой, и у вас будет постоянное значение для группы.

select name, date,
       dateadd(d, - row_number() over (partition by name order by date), date) as val
from t

Наконец, вам нужно количество групп в последовательности. Я бы также добавил идентификатор группы (например, чтобы различать два последних).

select name, date,
       count(*) over (partition by name, val) as NumInSeq,
       dense_rank() over (partition by name order by val) as SeqID
from (select name, date,
             dateadd(d, - row_number() over (partition by name order by date), date) as val
      from t
     ) t

Как-то я пропустил часть о буднях и праздниках. Это решение не решает эту проблему.

person Gordon Linoff    schedule 10.07.2012
comment
Хорошее решение - вы можете захотеть обрабатывать повторяющиеся даты, хотя требования к этому неясны. - person D'Arcy Rittich; 10.07.2012
comment
Если вы хотите задать еще один вопрос о повторяющихся датах, я буду рад ответить на него. Это не было частью первоначального вопроса. - person Gordon Linoff; 10.07.2012

arrow_upward
7
arrow_downward

Следующий запрос учитывает выходные и праздничные дни. В запросе предусмотрена возможность включения праздников на лету, хотя для того, чтобы сделать запрос более понятным, я просто материализовал праздники в реальную таблицу.

CREATE TABLE tx
    (n varchar(4), d date);

INSERT INTO tx
    (n, d)
VALUES
    ('Bill', '2006-12-29'), -- Friday    
    -- 2006-12-30 is Saturday
    -- 2006-12-31 is Sunday

    -- 2007-01-01 is New Year's Holiday    
    ('Bill', '2007-01-02'), -- Tuesday
    ('Bill', '2007-01-03'), -- Wednesday
    ('Bill', '2007-01-04'), -- Thursday
    ('Bill', '2007-01-05'), -- Friday    
    -- 2007-01-06 is Saturday
    -- 2007-01-07 is Sunday

    ('Bill', '2007-01-08'), -- Monday
    ('Bill', '2007-01-09'), -- Tuesday

    ('Bill', '2012-07-09'), -- Monday
    ('Bill', '2012-07-10'), -- Tuesday
    ('Bill', '2012-07-11'); -- Wednesday

create table holiday(d date);
insert into holiday(d) values
('2007-01-01');


/* query should return 7 consecutive good 
   attendance(from December 29 2006 to January 9 2007) */    
/* and 3 consecutive attendance from July 7 2012 to July 11 2012. */

Запрос:

with first_date as
(
    -- get the monday of the earliest date
    select dateadd( ww, datediff(ww,0,min(d)), 0 ) as first_date 
    from tx 
)
,shifted as
(
    select 
        tx.n, tx.d,                     
        diff = datediff(day, fd.first_date, tx.d) 
                   - (datediff(day, fd.first_date, tx.d)/7 * 2)             
    from tx
    cross join first_date fd
    union
    select 
        xxx.n, h.d,             
        diff = datediff(day, fd.first_date, h.d) 
                   - (datediff(day, fd.first_date, h.d)/7 * 2) 
    from holiday h 
    cross join first_date fd
    cross join (select distinct n from tx) as xxx
)
,grouped as
(
    select *, grp = diff - row_number() over(partition by n order by d)
    from shifted
)
select 
    d, n, dense_rank() over (partition by n order by grp) as nth_streak
    ,count(*) over (partition by n, grp) as streak
from grouped
where d not in (select d from holiday)  -- remove the holidays

Выход:

|          D |    N | NTH_STREAK | STREAK |
-------------------------------------------
| 2006-12-29 | Bill |          1 |      7 |
| 2007-01-02 | Bill |          1 |      7 |
| 2007-01-03 | Bill |          1 |      7 |
| 2007-01-04 | Bill |          1 |      7 |
| 2007-01-05 | Bill |          1 |      7 |
| 2007-01-08 | Bill |          1 |      7 |
| 2007-01-09 | Bill |          1 |      7 |
| 2012-07-09 | Bill |          2 |      3 |
| 2012-07-10 | Bill |          2 |      3 |
| 2012-07-11 | Bill |          2 |      3 |

Живой тест: http://www.sqlfiddle.com/#!3/815c5/1< /а>

Основная логика запроса — сдвинуть все даты на два дня назад. Это делается путем деления даты на 7 и умножения на два, а затем вычитания из исходного числа. Например, если заданная дата выпадает на 15 число, это будет вычислено как 15/7 * 2 == 4; затем вычтите 4 из исходного числа, 15 - 4 == 11. 15 станет 11-м днем. Точно так же 8-й день становится 6-м днем; 8 - (8/7*2) == 6.

                  Weekends are not in attendance(e.g. 6,7,13,14)
 1  2  3  4  5     6  7
 8  9 10 11 12    13 14
15

Применение вычислений ко всем числам дней недели даст следующие значения:

 1  2  3  4  5    
 6  7  8  9 10    
11

В праздничные дни вам нужно выделить их по посещаемости, чтобы можно было легко определить последовательность, а затем просто удалить их из окончательного запроса. Вышеупомянутая посещаемость дает 11 последовательных хороших посещаемости.

Подробное объяснение логики запроса здесь: http://www.ienablemuch.com/2012/07/monitoring-perfect-attendance.html

person Michael Buen    schedule 11.07.2012
comment
Это самая красивая вещь, которую я когда-либо читал в Интернете. Спасибо, Майкл. - person Pythonn00b; 11.07.2012