Блог

Главная - Вопросы по программированию - Группировать по столбцам с одинаковым значением

Группировать по столбцам с одинаковым значением

#sql #sql-server

#sql #sql-сервер

Вопрос:

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

Что у меня есть сейчас:

 SELECT RelatieId,
   AccountId,
   [UaId],
   [VisitDate],
   [Titel],
   [Editie],
   CASE
      WHEN DATEDIFF(SECOND, v.LastVisit, v.VisitDate) > (10 * 60)
       THEN 0
       ELSE 1
   END AS reset
   FROM rfv.click v

Результат выглядит примерно так 

 RelatieId   AccountId   UaId    VisitDate   Titel   reset
70409280    120211  8408    2019-04-01 09:15:52.000 Nx  1
70409280    120211  14531   2019-04-01 11:45:41.000 Nx  0
70409280    120211  14531   2019-04-01 11:45:55.000 Nx  1
70409280    120211  14531   2019-04-01 11:46:10.000 Nx  1
70409280    120211  14531   2019-04-01 11:47:16.000 Nx  1
70409280    120211  14531   2019-04-01 11:52:52.000 Nx  1
70409280    120211  14531   2019-04-01 11:53:14.000 Nx  1
70409280    120211  14531   2019-04-01 12:02:44.000 Nx  1
70409280    120211  14531   2019-04-01 12:03:08.000 Nx  1
70409280    120211  14531   2019-04-02 08:06:42.000 Nx  0
70409280    120211  14531   2019-04-02 08:07:07.000 Nx  1
70409280    120211  14531   2019-04-02 08:36:15.000 Nx  0
70409280    120211  14531   2019-04-02 08:36:26.000 Nx  1

но что мне на самом деле нужно, так это 

 RelatieId   AccountId   UaId    VisitDate   Titel   needed 
70409280    120211  8408    2019-04-01 09:15:52.000 Nx  1
70409280    120211  14531   2019-04-01 11:45:41.000 Nx  2
70409280    120211  14531   2019-04-01 11:45:55.000 Nx  3
70409280    120211  14531   2019-04-01 11:46:10.000 Nx  3
70409280    120211  14531   2019-04-01 11:47:16.000 Nx  3
70409280    120211  14531   2019-04-01 11:52:52.000 Nx  3
70409280    120211  14531   2019-04-01 11:53:14.000 Nx  3
70409280    120211  14531   2019-04-01 12:02:44.000 Nx  3
70409280    120211  14531   2019-04-01 12:03:08.000 Nx  3
70409280    120211  14531   2019-04-02 08:06:42.000 Nx  4
70409280    120211  14531   2019-04-02 08:07:07.000 Nx  5
70409280    120211  14531   2019-04-02 08:36:15.000 Nx  6
70409280    120211  14531   2019-04-02 08:36:26.000 Nx  7

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

1. Его sql 2017 @jarlh

2. Какова длина периода времени, по которому вы хотите сгруппировать строку?

3. Я не понимаю результатов. Почему вторая строка не должна иметь того же значения, что и третья строка?

Ответ №1:

Я не уверен, когда вы хотите это сбросить (начать сначала с 1), например, для другого RelatioId, или для другого AccountId, или для обоих. Вам нужно было бы настроить PARTITION BY обе LAG и SUM оконные функции. Вот базовая версия:

 ;WITH resetChangeCTE AS
(
    SELECT *
         , IIF(reset != LAG(reset, 1, 0) OVER (PARTITION BY RelatieId, AccountId ORDER BY VisitDate), 1, 0) AS hasResetChanged
     FROM (VALUES (70409280,    120211,  8408 ,   CONVERT(DATETIME, '2019-04-01 09:15:52.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 11:45:41.000'), 0),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 11:45:55.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 11:46:10.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 11:47:16.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 11:52:52.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 11:53:14.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 12:02:44.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-01 12:03:08.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-02 08:06:42.000'), 0),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-02 08:07:07.000'), 1),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-02 08:36:15.000'), 0),
                  (70409280,    120211,  14531,   CONVERT(DATETIME, '2019-04-02 08:36:26.000'), 1)) x(RelatieId  , AccountId   , UaId    ,VisitDate   ,reset)
)
SELECT *, SUM(hasResetChanged) OVER (PARTITION BY RelatieId, AccountId ORDER BY VisitDate) AS needed 
  FROM resetChangeCTE

Итак, я сначала проверяю, изменяется ли reset значение между текущей записью и предыдущей (с использованием LAG , упорядоченное по дате посещения), а затем суммирую количество изменений с первой записи до текущей с использованием SUM . Это оконные функции, очень полезные.

sqlfiddle с результатом.

Ответ №2:

Я предполагаю, что вы действительно хотите:

 SELECT c.*,
       SUM(CASE WHEN DATEDIFF(SECOND, v.LastVisit, v.VisitDate) > (10 * 60)
                THEN 0
                ELSE 1
           END) AS needed
FROM (SELECT c.*,
             LAG(VisitDate) OVER (PARTITION BY RelatieId, AccountId, UaId ORDER BY VisitDate) as LastVisit
      FROM rfv.click c
     ) c;

Это возвращает не совсем те результаты, которые вы указали. Однако это кажется более полезным и может быть тем, что вы действительно намереваетесь.