时间戳

首页 > 解决方案 > 按具有相同值的列分组

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

标签： sqlsql-server

解决方案

我不确定您何时希望重置（从 1 开始），例如对于不同的 RelatieId 或不同的 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 的结果。

推荐阅读