Вопрос по sql – Проблема SQL - вычислите максимальную последовательность дней

6

Есть таблица с данными о посещениях:

uid (INT) | created_at (DATETIME)

Я хочу узнать, сколько дней подряд пользователь посещал наше приложение. Так, например:

SELECT DISTINCT DATE(created_at) AS d FROM visits WHERE uid = 123

вернусь:

     d      
------------
 2012-04-28
 2012-04-29
 2012-04-30
 2012-05-03
 2012-05-04

Имеется 5 записей и два интервала - 3 дня (28–30 апреля) и 2 дня (3–4 мая).

У меня вопрос, как найти максимальное количество дней, которое пользователь посещал приложение подряд (в примере 3 дня). Пытался найти подходящую функцию в документах SQL, но безуспешно. Я что-то пропустил?

UPD: Спасибо, ребята, за ваши ответы! На самом деле, я работаю с аналитической базой данных vertica (http://vertica.com/), но это очень редкое решение, и только несколько человек имеют опыт работы с ним. Хотя он поддерживает стандарт SQL-99.

Ну, большинство решений работают с небольшими изменениями. Наконец я создал свою собственную версию запроса:

-- returns starts of the vitit series 
SELECT t1.d as s FROM testing t1
LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', -1, t1.d))
WHERE t2.d is null GROUP BY t1.d

          s          
---------------------
 2012-04-28 01:00:00
 2012-05-03 01:00:00

-- returns end of the vitit series 
SELECT t1.d as f FROM testing t1
LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', 1, t1.d))
WHERE t2.d is null GROUP BY t1.d

          f          
---------------------
 2012-04-30 01:00:00
 2012-05-04 01:00:00

Так что теперь единственное, что нам нужно сделать, это как-то присоединиться к ним, например, по индексу строки.

SELECT s, f, DATEDIFF(day, s, f) + 1 as seq FROM (
    SELECT t1.d as s, ROW_NUMBER() OVER () as o1 FROM testing t1
    LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', -1, t1.d))
    WHERE t2.d is null GROUP BY t1.d
) tbl1 LEFT JOIN (
    SELECT t1.d as f, ROW_NUMBER() OVER () as o2 FROM testing t1
    LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', 1, t1.d))
    WHERE t2.d is null GROUP BY t1.d
) tbl2 ON o1 = o2 

Пример вывода:

          s          |          f          | seq 
---------------------+---------------------+-----
 2012-04-28 01:00:00 | 2012-04-30 01:00:00 |   3
 2012-05-03 01:00:00 | 2012-05-04 01:00:00 |   2
+1 Хороший подход. НаWHERE t2.d is null GROUP BY t1.dВы можете удалитьGROUP BY t1.d однако :-) ВашWHERE t2.d is null уже возвращается уникальныйt1.d тем не мение Michael Buen
Теги с двумя различными внедрениями sql? И то и другоеMySQL а такжеPostGreSQL иметь разные способности ... MatBailie
Спасибо за совет, Майкл! deadrunk

Ваш Ответ

10   ответов
1

я слишком сложными. Это может быть достигнуто с помощью базового SQL, способа перечисления строк и некоторой арифметики даты.

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

Следующий запрос использует это наблюдение для ответа на исходный вопрос:

select uid, min(d) as startdate, count(*) as numdaysinseq
from 
(
   select uid, d, adddate(d, interval -offset day) as groupstart
   from 
   (
     select uid, d, row_number() over (partition by uid order by date) as offset
     from 
     (
       SELECT DISTINCT uid, DATE(created_at) AS d
       FROM visits
     ) t
   ) t
) t

Увы, MySQL не имеетrow_number() функция. Тем не менее, существует обходной путь с переменными (и большинство других баз данных имеют эту функцию).

-offset трюк, похоже, не работает на Vertica :( deadrunk
2

что в Postgresql есть нечто похожее на обычные табличные выражения, доступные в MSSQL. Я не очень хорошо знаком с Postgresql, но приведенный ниже код работает для MSSQL и делает то, что вы хотите.

create table #tempdates (
    mydate date
)

insert into #tempdates(mydate) values('2012-04-28')
insert into #tempdates(mydate) values('2012-04-29')
insert into #tempdates(mydate) values('2012-04-30')
insert into #tempdates(mydate) values('2012-05-03')
insert into #tempdates(mydate) values('2012-05-04');

with maxdays (s, e, c)
as
(
    select mydate, mydate, 1
    from #tempdates
    union all
    select m.s, mydate, m.c + 1
    from #tempdates t
    inner join maxdays m on DATEADD(day, -1, t.mydate)=m.e
)
select MIN(o.s),o.e,max(o.c)
from (
  select m1.s,max(m1.e) e,max(m1.c) c
  from maxdays m1
  group by m1.s
) o
group by o.e

drop table #tempdates

А вот скрипка SQL:http://sqlfiddle.com/#!3/42b38/2

2

но я думаю, что я должен внести свой вклад, показывая другой подход, использующий аналитические возможности, специфичные для Vertica (в конце концов, это часть того, за что вы заплатили). И я обещаю, что последний запрос будет коротким.

Fi, первый запрос с помощью conditional_true_event (). Из документации Vertica:

Assigns an event window number to each row, starting from 0, and increments the number by 1 when the result of the boolean argument expression evaluates true.

Пример запроса выглядит так:

select uid, created_at, 
       conditional_true_event( created_at - lag(created_at) > '1 day' ) 
       over (partition by uid order by created_at) as seq_id
from visits;

И вывод:

uid  created_at           seq_id  
---  -------------------  ------  
123  2012-04-28 00:00:00  0       
123  2012-04-29 00:00:00  0       
123  2012-04-30 00:00:00  0       
123  2012-05-03 00:00:00  1       
123  2012-05-04 00:00:00  1       
123  2012-06-04 00:00:00  2       
123  2012-06-04 00:00:00  2     

Теперь последний запрос становится легким:

select uid, seq_id, count(1) num_days, min(created_at) s, max(created_at) f
from
(
    select uid, created_at, 
       conditional_true_event( created_at - lag(created_at) > '1 day' ) 
       over (partition by uid order by created_at) as seq_id
    from visits
) as seq
group by uid, seq_id;

Окончательный результат:

uid  seq_id  num_days  s                    f                    
---  ------  --------  -------------------  -------------------  
123  0       3         2012-04-28 00:00:00  2012-04-30 00:00:00  
123  1       2         2012-05-03 00:00:00  2012-05-04 00:00:00  
123  2       2         2012-06-04 00:00:00  2012-06-04 00:00:00  

Последнее замечание: num_days на самом деле количество строк внутреннего запроса. Если есть два'2012-04-28' посещения в исходной таблице (т.е. дубликаты), вы можете обойти это.

1

и не требовать рекурсивной логики.

;WITH
  visit_dates (
    visit_id,
    date_id,
    group_id
  )
AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY TRUNC(created_at)),
    TRUNC(SYSDATE) - TRUNC(created_at),
    TRUNC(SYSDATE) - TRUNC(created_at) - ROW_NUMBER() OVER (ORDER BY TRUNC(created_at))
  FROM
    visits
  GROUP BY
    TRUNC(created_at)
)
,
  group_duration (
    group_id,
    duration
  )
AS
(
  SELECT
    group_id,
    MAX(date_id) - MIN(date_id) + 1  AS duration
  FROM
    visit_dates
  GROUP BY
    group_id
)
SELECT
  MAX(duration)  AS max_duration
FROM
  group_duration
1

самая короткая, и использует минимальную переменную (только одна переменная):

select 
   min(d) as starting_date, max(d) as ending_date, 
   count(d) as consecutive_days
from
(
  select 
     sr.d,
     IF(fr.d is null,@group_number := @group_number + 1,@group_number) 
        as group_number
  from tbl sr
  left join tbl fr on sr.d = adddate(fr.d,interval 1 day)
  cross join (select @group_number := 0) as grp
) as x
group by group_number

Выход:

STARTING_DATE                  ENDING_DATE                  CONSECUTIVE_DAYS
April, 28 2012 08:00:00-0700   April, 30 2012 08:00:00-0700 3
May, 03 2012 08:00:00-0700     May, 04 2012 08:00:00-0700   2

Живой тест:http://www.sqlfiddle.com/#!2/65169/1

1

PostgreSQL 8.4 or laterесть короткий и чистый способwindow functions and no JOIN.
Я ожидаю, что это будетfastest solution размещено до сих пор:

WITH x AS (
    SELECT created_at AS d
         , lag(created_at) OVER (ORDER BY created_at) = (created_at - 1) AS nu
    FROM   visits
    WHERE  uid = 1
    )
   , y AS (
    SELECT d, count(NULLIF(nu, TRUE)) OVER (ORDER BY d) AS seq
    FROM   x
    )
SELECT count(*) AS max_days, min(d) AS seq_from,  max(d) AS seq_to
FROM   y
GROUP  BY seq
ORDER  BY 1 DESC
LIMIT  1;

Возвращает:

max_days | seq_from   | seq_to
---------+------------+-----------
3        | 2012-04-28 | 2012-04-30

При условии, чтоcreated_at этоdate а такжеunique.

In CTE x: for every day our user visits, check if he was here yesterday, too. To calculate "yesterday" just use created_at - 1 The first row is a special case and will produce NULL here.

In CTE y: calculate a running count of "days without yesterday so far" (seq) for every day. NULL values don't count, so count(NULLIF(nu, TRUE)) is the fastes and shortest way, also covering the special case.

Finally, group days per seq and count the days. While being at it I added first and last day of the sequence. ORDER BY length of the sequence, and pick the longest one.

2

SET @nextDate = CURRENT_DATE;
SET @RowNum = 1;

SELECT MAX(RowNumber) AS ConecutiveVisits
FROM    (   SELECT  @RowNum := IF(@NextDate = Created_At, @RowNum + 1, 1) AS RowNumber,
                    Created_At,
                    @NextDate := DATE_ADD(Created_At, INTERVAL 1 DAY) AS NextDate
            FROM    Visits
            ORDER BY Created_At
        ) Visits

Пример здесь:

http://sqlfiddle.com/#!2/6e035/8

Однако я не уверен на 100%, что это лучший способ сделать это.

В Postgresql:

 ;WITH RECURSIVE VisitsCTE AS
 (  SELECT  Created_At, 1 AS ConsecutiveDays
    FROM    Visits
    UNION ALL
    SELECT  v.Created_At, ConsecutiveDays + 1
    FROM    Visits v
            INNER JOIN VisitsCTE cte
                ON 1 + cte.Created_At = v.Created_At
)
SELECT  MAX(ConsecutiveDays) AS ConsecutiveDays
FROM    VisitsCTE

Пример здесь:

http://sqlfiddle.com/#!1/16c90/9

Вместо DATE_ADD () вы можете использовать просто +
1

with headers as
(
    select 
        d,
        lag(d) over m is null or d - lag(d) over m  <> interval '1 day' as header

    from tbl
    window m as (order by d)
)      
,sequence_group as
(
    select d, sum(header::int) over m as group_number 
    from headers
    window m as (order by d)
)
,consecutive_list as
(
    select d, group_number, count(d) over m as consecutive_count
    from sequence_group 
    window m as (partition by group_number)
)
select * from consecutive_list

Разделяй и властвуй подход: 3 шага

1-й шаг, найти заголовки:

with headers as
(
    select 
        d,
        lag(d) over m is null or d - lag(d) over m  <> interval '1 day' as header

    from tbl
    window m as (order by d)
)
select * from headers

Выход:

          d          | header 
---------------------+--------
 2012-04-28 08:00:00 | t
 2012-04-29 08:00:00 | f
 2012-04-30 08:00:00 | f
 2012-05-03 08:00:00 | t
 2012-05-04 08:00:00 | f
(5 rows)

2-й шаг, обозначить группировку:

with headers as
(
    select 
        d,
        lag(d) over m is null or d - lag(d) over m  <> interval '1 day' as header

    from tbl
    window m as (order by d)
)      
,sequence_group as
(
    select d, sum(header::int) over m as group_number 
    from headers
    window m as (order by d)
)
select * from sequence_group

Выход:

          d          | group_number 
---------------------+--------------
 2012-04-28 08:00:00 |            1
 2012-04-29 08:00:00 |            1
 2012-04-30 08:00:00 |            1
 2012-05-03 08:00:00 |            2
 2012-05-04 08:00:00 |            2
(5 rows)

3-й шаг, подсчитать максимальное количество дней:

with headers as
(
    select 
        d,
        lag(d) over m is null or d - lag(d) over m  <> interval '1 day' as header

    from tbl
    window m as (order by d)
)      
,sequence_group as
(
    select d, sum(header::int) over m as group_number 
    from headers
    window m as (order by d)
)
,consecutive_list as
(
select d, group_number, count(d) over m as consecutive_count
from sequence_group 
window m as (partition by group_number)
)
select * from consecutive_list

Выход:

          d          | group_number | consecutive_count 
---------------------+--------------+-----------------
 2012-04-28 08:00:00 |            1 |               3
 2012-04-29 08:00:00 |            1 |               3
 2012-04-30 08:00:00 |            1 |               3
 2012-05-03 08:00:00 |            2 |               2
 2012-05-04 08:00:00 |            2 |               2
(5 rows)
1

я попытался запустить два соединения одновременно:

Эти версии запросов Postgresql и Sql Server должны работать в Vertica

Версия Postgresql:

select 
  min(gr.d) as start_date,
  max(gr.d) as end_date,
  date_part('day', max(gr.d) - min(gr.d))+1 as consecutive_days
from 
(
  select 
  cr.d, (row_number() over() - 1) / 2 as pair_number
  from tbl cr   
  left join tbl pr on pr.d = cr.d - interval '1 day'
  left join tbl nr on nr.d = cr.d + interval '1 day'
  where pr.d is null <> nr.d is null
) as gr
group by pair_number
order by start_date

относительноpr.d is null <> nr.d is null, Это означает, что предыдущая строка имеет значение NULL или следующая строка имеет значение NULL, но они никогда не могут иметь значение NULL, так что это в основном удаляет непоследовательные даты, как непоследовательные даты ». предыдущий & amp; следующий ряд - пустые значения (и это в основном дает нам все даты, которые являются только верхними и нижними колонтитулами). Это также называетсяОперация XOR

Если у нас остались только последовательные даты, теперь мы можем связать их через row_number:

(row_number() over() - 1) / 2 as pair_number

row_number() начинается с 1, нам нужно вычесть это с 1 (мы можем также добавить с 1 вместо), затем мы делим это на два; это делает парную дату смежной друг с другом

Живой тест:http://www.sqlfiddle.com/#!1/fc440/7

Это версия сервера Sql:

select 
  min(gr.d) as start_date,
  max(gr.d) as end_date,
  datediff(day, min(gr.d),max(gr.d)) +1 as consecutive_days
from 
(
  select 
     cr.d, (row_number() over(order by cr.d) - 1) / 2 as pair_number
  from tbl cr   
  left join tbl pr on pr.d = dateadd(day,-1,cr.d)
  left join tbl nr on nr.d = dateadd(day,+1,cr.d)
  where         
       case when pr.d is null then 1 else 0 end
    <> case when nr.d is null then 1 else 0 end
) as gr
group by pair_number
order by start_date

Та же логика, что и выше, за исключением искусственных различий в функциях даты. И SQL Server требуетORDER BY пункт о егоOVERв то время как PostgresqlOVER можно оставить пустым.

Сервер Sql не имеет логических значений первого класса, поэтому мы не можем напрямую сравнивать логические значения:

pr.d is null <> nr.d is null

Мы должны сделать это в Sql Server:

   case when pr.d is null then 1 else 0 end
<> case when nr.d is null then 1 else 0 end

Живой тест:http://www.sqlfiddle.com/#!3/65df2/17

7

with grouped_result as
(
    select 
       sr.d,
       sum((fr.d is null)::int) over(order by sr.d) as group_number
    from tbl sr
    left join tbl fr on sr.d = fr.d + interval '1 day'
)
select d, group_number, count(d) over m as consecutive_days
from grouped_result
window m as (partition by group_number)

Выход:

          d          | group_number | consecutive_days 
---------------------+--------------+------------------
 2012-04-28 08:00:00 |            1 |                3
 2012-04-29 08:00:00 |            1 |                3
 2012-04-30 08:00:00 |            1 |                3
 2012-05-03 08:00:00 |            2 |                2
 2012-05-04 08:00:00 |            2 |                2
(5 rows)

Живой тест:http://www.sqlfiddle.com/#!1/93789/1

sr = вторая строка, fr = первая строка (или, возможно, предыдущая строка?& # X30C4; ). По существу, мы выполняем отслеживание в обратном направлении, это имитация задержки в базе данных, которая не поддерживаетLAG (Postgres поддерживает LAG, но решениеочень длинный, поскольку управление окнами не поддерживает вложенное управление окнами). Таким образом, в этом запросе мы используем гибридный подход, имитируем LAG с помощью соединения, затем используем SUM-окно для него, что дает номер группы

UPDATE

Забыл поставить последний запрос, приведенный выше запрос иллюстрирует основы нумерации групп, необходимо преобразовать это в следующее:

with grouped_result as
(
    select 
       sr.d,
       sum((fr.d is null)::int) over(order by sr.d) as group_number
    from tbl sr
    left join tbl fr on sr.d = fr.d + interval '1 day'
)
select min(d) as starting_date, max(d) as end_date, count(d) as consecutive_days
from grouped_result
group by group_number
-- order by consecutive_days desc limit 1


STARTING_DATE                END_DATE                     CONSECUTIVE_DAYS
April, 28 2012 08:00:00-0700 April, 30 2012 08:00:00-0700 3
May, 03 2012 08:00:00-0700   May, 04 2012 08:00:00-0700   2

UPDATE

Я знаю почему мойдругое решение использование оконной функции стало длинным, я попытался проиллюстрировать логику нумерации групп и подсчета по группе. Если бы я пошел в погоню, как в моемMySql подходэта оконная функция может быть короче. Сказав это, вот мой старый подход оконной функции, хотя и лучше сейчас:

with headers as
(
    select 
      d,lag(d) over m is null or d - lag(d) over m  <> interval '1 day' as header
    from tbl
    window m as (order by d)
)      
,sequence_group as
(
    select d, sum(header::int) over (order by d) as group_number
    from headers  
)
select min(d) as starting_date,max(d) as ending_date,count(d) as consecutive_days
from sequence_group
group by group_number
-- order by consecutive_days desc limit 1

Живой тест:http://www.sqlfiddle.com/#!1/93789/21

+1: очень мило Не уверен, что у меня есть хотя бы насчет типового итогового ответа (чтобы сгенерировать идентификаторы группы). О, как хорошо было бы иметь клиента с такой современной реализацией SQL. Было бы интересно сравнить это с механизмом группировки на основе ROW_NUMBER () (который избегает объединения).
Я думаю, что есть более короткий (и более быстрый) способ с оконными функциями и нет JOIN, чем тот, на который вы ссылаетесь. Я отправил ответ.
@Dems Правда, приятно иметь обновленную SQL-реализацию. Что делает его более странным, так это то, что база данных с открытым исходным кодом / бесплатная превосходит платные.
Спасибо, Майкл! Вы спасли меня! :) deadrunk
+1 за очень элегантное решение и новый смайлик & # x30C5;

Похожие вопросы