Блог

Главная - Вопросы по программированию - Группировать данные с интервалом в 15 дней

Группировать данные с интервалом в 15 дней

#sql #sql-server #tsql #group-by

#sql #sql-сервер #tsql #групповое-по

Вопрос:

Мне нужно выбрать всех пользователей, которые разместили более 3 сообщений в одной и той же категории (sql) за последний год с интервалом времени менее 15 дней. Проблема заключается в последней части «с интервалом времени менее 15 дней».

Вот как я выбираю пользователей на 2018 год и теги (вы можете запустить его на https://data.stackexchange.com/stackoverflow/query/new ):

 SELECT OwnerUserId, Id, CreationDate
FROM Posts
WHERE OwnerUserId IN (
    SELECT OwnerUserId
    FROM Posts 
    WHERE YEAR(Posts.CreationDate) >= 2018 AND YEAR(Posts.CreationDate) < 2019 
    AND Posts.Tags LIKE '%sql%'
    GROUP BY OwnerUserId,PostTypeId
    HAVING COUNT(PostTypeId) > 3
)

Я думаю, что я должен сначала сгруппировать по диапазону дат, а затем посчитать. Но я не знаю, как это можно сделать. Я читал, как группировать по неделям, но это не то, что мне нужно, потому что сообщения могут быть за разные недели.

В конце концов, я должен просто получить список пользователей.

Комментарии:

1. Отличная идея использовать данные SEDE в качестве образца!

2. Как вы определяете «категорию»?

3. @GordonLinoff для примера я выбираю «sql». в этих данных это тег, потому что он есть почти в каждом сообщении

Ответ №1:

Вы можете использовать LAG функцию с параметром смещения, чтобы найти дату 2-го предыдущего сообщения, а затем вычислить разницу в дате:

 WITH questions AS (
    SELECT OwnerUserId
         , CreationDate AS PostDate
         , LAG(CreationDate, 2) OVER (PARTITION BY OwnerUserId ORDER BY CreationDate) AS PrevDate
    FROM Posts
    WHERE OwnerUserId IS NOT NULL    -- not community owned
    AND PostTypeId = 1               -- questions only
    AND CreationDate >= '2018-01-01' -- between 2018
    AND CreationDate < '2019-01-01'
    AND Tags LIKE '%<sql>%'          -- tagged sql
)
SELECT *
FROM questions
WHERE DATEDIFF(DAY, PrevDate, PostDate) <= 14

Комментарии:

1. Большое спасибо, это именно то, что мне нужно!

Ответ №2:

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

Если я правильно понимаю вашу терминологию:

 select p.*
from (select p.*,
             lead(CreationDate, 2) over (partition by OwnerUserId, PostTypeId order by CreationDate) as CreationDate_2
      from posts p
      where p.CreationDate >= '2018-01-01' and
            p.CreationDate < '2019-01-01' and
            p.Tags like '%sql%'
     ) 
where CreationDate_2 < dateadd(day, 15, CreationDate)

Комментарии:

1. Извините, я новичок в sql, поэтому не могу быстро это исправить. я понимаю вашу идею, но она вернулась «Функция ‘lead’ должна иметь предложение OVER с ORDER BY».

Метки: