小编典典

计算指定窗口上的滚动计数

sql

样本数据可能会有助于解释我想做的事情,而不是解释它,因此,我将从此开始。

这是我目前正在使用的数据:

+-------------------------+--------------+
|        CallStart        | CallDuration |
+-------------------------+--------------+
| 2017-09-15 09:15:15.313 | 00:01:28     |
| 2017-09-15 09:15:15.317 | 00:01:45     |
| 2017-09-15 09:16:45.603 | 00:01:31     |
| 2017-09-15 09:17:00.637 | 00:01:24     |
| 2017-09-15 09:18:20.853 | 00:01:42     |
| 2017-09-15 09:18:25.870 | 00:01:24     |
| 2017-09-15 11:27:05.117 | 00:00:59     |
| 2017-09-15 11:31:16.053 | 00:01:18     |
| 2017-09-15 11:34:41.627 | 00:01:00     |
| 2017-09-15 12:16:45.413 | 00:01:01     |
| 2017-09-15 12:18:15.820 | 00:01:05     |
| 2017-09-15 12:30:43.607 | 00:01:04     |
| 2017-09-15 12:31:48.817 | 00:00:55     |
| 2017-09-15 12:35:14.563 | 00:00:59     |
| 2017-09-15 12:42:10.947 | 00:00:43     |
| 2017-09-15 12:56:28.807 | 00:01:14     |
| 2017-09-15 13:05:10.643 | 00:00:37     |
| 2017-09-15 13:20:08.400 | 00:00:37     |
| 2017-09-15 14:30:12.607 | 00:00:59     |
| 2017-09-15 14:31:22.807 | 00:00:49     |
| 2017-09-15 15:19:47.240 | 00:01:07     |
| 2017-09-15 16:04:47.753 | 00:00:55     |
| 2017-09-15 16:58:08.080 | 00:00:55     |
| 2017-09-15 17:05:04.557 | 00:00:50     |
| 2017-09-15 17:20:42.753 | 00:00:58     |
| 2017-09-15 17:28:09.140 | 00:01:05     |
| 2017-09-15 17:39:46.690 | 00:00:38     |
| 2017-09-15 17:40:21.957 | 00:01:05     |
| 2017-09-15 17:43:47.570 | 00:01:08     |
| 2017-09-15 17:47:23.390 | 00:01:05     |
| 2017-09-15 17:47:28.410 | 00:00:56     |
| 2017-09-15 17:51:59.380 | 00:01:04     |
+-------------------------+--------------+

我正在尝试COUNT(*)在15分钟的时间内滚动显示此数据中的出现次数。该数据的预期结果如下:

+-------------------------+--------------+------------------+
|        CallStart        | CallDuration | DropsIn15Minutes |
+-------------------------+--------------+------------------+
| 2017-09-15 09:15:15.313 | 00:01:28     |                1 |
| 2017-09-15 09:15:15.317 | 00:01:45     |                2 |
| 2017-09-15 09:16:45.603 | 00:01:31     |                3 |
| 2017-09-15 09:17:00.637 | 00:01:24     |                4 |
| 2017-09-15 09:18:20.853 | 00:01:42     |                5 |
| 2017-09-15 09:18:25.870 | 00:01:24     |                6 |
| 2017-09-15 11:27:05.117 | 00:00:59     |                1 |
| 2017-09-15 11:31:16.053 | 00:01:18     |                2 |
| 2017-09-15 11:34:41.627 | 00:01:00     |                3 |
| 2017-09-15 12:16:45.413 | 00:01:01     |                1 |
| 2017-09-15 12:18:15.820 | 00:01:05     |                2 |
| 2017-09-15 12:30:43.607 | 00:01:04     |                3 |
| 2017-09-15 12:31:48.817 | 00:00:55     |                3 |
| 2017-09-15 12:35:14.563 | 00:00:59     |                3 |
| 2017-09-15 12:42:10.947 | 00:00:43     |                4 |
| 2017-09-15 12:56:28.807 | 00:01:14     |                2 |
| 2017-09-15 13:05:10.643 | 00:00:37     |                2 |
| 2017-09-15 13:20:08.400 | 00:00:37     |                2 |
| 2017-09-15 14:30:12.607 | 00:00:59     |                1 |
| 2017-09-15 14:31:22.807 | 00:00:49     |                2 |
| 2017-09-15 15:19:47.240 | 00:01:07     |                1 |
| 2017-09-15 16:04:47.753 | 00:00:55     |                1 |
| 2017-09-15 16:58:08.080 | 00:00:55     |                1 |
| 2017-09-15 17:05:04.557 | 00:00:50     |                2 |
| 2017-09-15 17:20:42.753 | 00:00:58     |                1 |
| 2017-09-15 17:28:09.140 | 00:01:05     |                2 |
| 2017-09-15 17:39:46.690 | 00:00:38     |                2 |
| 2017-09-15 17:40:21.957 | 00:01:05     |                3 |
| 2017-09-15 17:43:47.570 | 00:01:08     |                3 |
| 2017-09-15 17:47:23.390 | 00:01:05     |                4 |
| 2017-09-15 17:47:28.410 | 00:00:56     |                5 |
| 2017-09-15 17:51:59.380 | 00:01:04     |                6 |
+-------------------------+--------------+------------------+

样本数据:

Create Table #Calls 
(
    CallStart DateTime,
    CallDuration Time(0)
);
Insert Into #Calls
Values (N'2017-09-15T09:15:15.313', N'00:01:28'),
    (N'2017-09-15T09:15:15.317', N'00:01:45'),
    (N'2017-09-15T09:16:45.603', N'00:01:31'),
    (N'2017-09-15T09:17:00.637', N'00:01:24'),
    (N'2017-09-15T09:18:20.853', N'00:01:42'),
    (N'2017-09-15T09:18:25.87', N'00:01:24'),
    (N'2017-09-15T11:27:05.117', N'00:00:59'),
    (N'2017-09-15T11:31:16.053', N'00:01:18'),
    (N'2017-09-15T11:34:41.627', N'00:01:00'),
    (N'2017-09-15T12:16:45.413', N'00:01:01'),
    (N'2017-09-15T12:18:15.82', N'00:01:05'),
    (N'2017-09-15T12:30:43.607', N'00:01:04'),
    (N'2017-09-15T12:31:48.817', N'00:00:55'),
    (N'2017-09-15T12:35:14.563', N'00:00:59'),
    (N'2017-09-15T12:42:10.947', N'00:00:43'),
    (N'2017-09-15T12:56:28.807', N'00:01:14'),
    (N'2017-09-15T13:05:10.643', N'00:00:37'),
    (N'2017-09-15T13:20:08.4', N'00:00:37'),
    (N'2017-09-15T14:30:12.607', N'00:00:59'),
    (N'2017-09-15T14:31:22.807', N'00:00:49'),
    (N'2017-09-15T15:19:47.24', N'00:01:07'),
    (N'2017-09-15T16:04:47.753', N'00:00:55'),
    (N'2017-09-15T16:58:08.08', N'00:00:55'),
    (N'2017-09-15T17:05:04.557', N'00:00:50'),
    (N'2017-09-15T17:20:42.753', N'00:00:58'),
    (N'2017-09-15T17:28:09.14', N'00:01:05'),
    (N'2017-09-15T17:39:46.69', N'00:00:38'),
    (N'2017-09-15T17:40:21.957', N'00:01:05'),
    (N'2017-09-15T17:43:47.57', N'00:01:08'),
    (N'2017-09-15T17:47:23.39', N'00:01:05'),
    (N'2017-09-15T17:47:28.41', N'00:00:56'),
    (N'2017-09-15T17:51:59.38', N'00:01:04');

我可以通过以下方式 使它 起作用:

Select  CallStart,
        CallDuration,
        DropsIn15Minutes = 
        (
            Select  Count(*) 
            From    #Calls C2 
            Where   C2.CallStart Between DateAdd(Minute, -15, C1.CallStart) 
                                 And     C1.CallStart
        )
From    #Calls  C1

但是,我想避免使用子查询,而建议使用COUNT(*) OVER ()(或其他任何可能的解决方案)解决方案。

这可能吗?还是子查询是正确的解决方案?


阅读 175

收藏
2021-04-28

共1个答案

小编典典

一种方法-如果表很大,可能比嵌套循环在一个范围上连接的性能更好-一种方法是首先创建一个数字表…

CREATE TABLE dbo.Numbers
(
N INT PRIMARY KEY
);

    WITH E1(N) AS 
    (
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    )                                       -- 1*10^1 or 10 rows
    , E2(N) AS (SELECT 1 FROM E1 a, E1 b)   -- 1*10^2 or 100 rows
    , E4(N) AS (SELECT 1 FROM E2 a, E2 b)   -- 1*10^4 or 10,000 rows
    , E8(N) AS (SELECT 1 FROM E4 a, E4 b)   -- 1*10^8 or 100,000,000 rows
INSERT INTO dbo.Numbers
    SELECT TOP (60*60*24) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E8;

然后使用以下内容。

WITH Calls
     AS (SELECT *,
                --pre-truncate all call starts to second precision
                CallStart_sec = DATEADD(SECOND, DATEDIFF(SECOND, '20000101', CallStart), '20000101')
         FROM   #Calls),
     PreAgg
     AS (SELECT CallStart_sec,
                COUNT(*) AS Cnt
         FROM   Calls
         GROUP  BY CallStart_sec),
     Dates(D)
     --Todo - something else other than hardcoding the dates
     AS (SELECT CAST('2017-09-15' AS DATETIME2)),
     RT
     AS (SELECT *,
                Cume = SUM(Cnt) OVER (ORDER BY DATEADD(SECOND, N.N, Dates.D) 
                               ROWS BETWEEN 900 PRECEDING AND CURRENT ROW)
         FROM   Dates
                INNER JOIN dbo.Numbers N
                  ON N.N BETWEEN 0 AND 86399
                LEFT JOIN PreAgg P
                  ON P.CallStart_sec = DATEADD(SECOND, N.N, Dates.D))
SELECT C.CallStart_sec AS CallStart,
       CallDuration,
       DropsIn15Minutes = Cume
FROM   Calls C
       JOIN RT
         ON RT.CallStart_sec = C.CallStart_sec
2021-04-28