sql - SQL需要10分钟内发生的事件列表的最小时间实例和总持续时间

问题描述

我有一张广告表及其播出日期。有时广告以 2 或 3 组为一组依次播放。我想找到发生这种情况时的最短播放时间，以确定组的开始时间，以及每个组的广告持续时间的总和。

例如，在我的表格中，我有这两组广告：

+-------------+--------------------+--------------------+-------------+ 
| sequence_id | ad_id              | ad_start_time      | ad_duration | 
+-------------+--------------------+--------------------+-------------+ 
| 28975352680 | M2783148-173883627 | 8/24/2020 19:12:50 | 20          | 
| 28976517640 | M2783148-174389250 | 8/24/2020 19:13:10 | 20          | 
| 28975352679 | M2783148-173883795 | 8/24/2020 19:13:30 | 22          | 
| 28976145221 | M2783148-173883627 | 8/24/2020 22:51:43 | 21          | 
| 28976517639 | M2783148-174389250 | 8/24/2020 22:52:04 | 20          | 
| 28976145219 | M2783148-173883795 | 8/24/2020 22:52:24 | 23          | 
+-------------+--------------------+--------------------+-------------+

我想更新表来得到这个：

+-------------+--------------------+--------------------+-------------+--------------------+----------------+ 
| sequence_id | ad_id              | ad_start_time      | ad_duration | group_start_time   | group_duration | 
+-------------+--------------------+--------------------+-------------+--------------------+----------------+ 
| 28975352680 | M2783148-173883627 | 8/24/2020 19:12:50 | 20          | 8/24/2020 19:12:50 | 62             | 
| 28976517640 | M2783148-174389250 | 8/24/2020 19:13:10 | 20          | 8/24/2020 19:12:50 | 62             | 
| 28975352679 | M2783148-173883795 | 8/24/2020 19:13:30 | 22          | 8/24/2020 19:12:50 | 62             | 
| 28976145221 | M2783148-173883627 | 8/24/2020 22:51:43 | 21          | 8/24/2020 22:51:43 | 64             | 
| 28976517639 | M2783148-174389250 | 8/24/2020 22:52:04 | 20          | 8/24/2020 22:51:43 | 64             | 
| 28976145219 | M2783148-173883795 | 8/24/2020 22:52:24 | 23          | 8/24/2020 22:51:43 | 64             | 
+-------------+--------------------+--------------------+-------------+--------------------+----------------+

其他一些领域的信息：

sequence_id 是非连续升序的唯一id
ad_id 标识广告，但广告可以在一天中多次出现

从这个查询开始尝试识别 1st/min 实例并加入到原始表中，但它并不是很接近我需要的东西：

SELECT *
FROM (
    SELECT ad_id
        ,ad_start_time
        ,(
            SELECT TOP 1 ad_start_time
            FROM my_table AS tPrevious
            WHERE tPrevious.ad_id = t.ad_id
                AND tPrevious.ad_start_time < t.ad_start_time
            ORDER BY ad_start_time DESC
            ) AS previousCreated
    FROM my_table AS t
    ) AS joined
WHERE previousCreated IS NOT NULL
    AND DATEDIFF(min, ad_start_time, previousCreated) BETWEEN 0
        AND 10

最终有效的查询是：

UPDATE my_table main
SET group_start_time = 
    (WITH cte
        AS (
        SELECT CONVERT(VARCHAR(13), ad_start_time) AS group_id
                ,MIN(ad_start_time) AS group_start_time
                ,SUM(ad_duration) AS group_duration
        FROM my_table
        GROUP BY CONVERT(VARCHAR(13), ad_start_time)
        ) 
        SELECT cte.group_start_time, cte.group_duration
        FROM cte
        WHERE CONVERT(VARCHAR(13), main.ad_start_time) = cte.group_id)

标签： sql

由于我们在查找聚合值时没有使用组 ID，因此我假设时间的日期部分和小时部分是组 ID，因此解决方案如下：

WITH cte
AS (
    SELECT CONVERT(VARCHAR(13), ad_start_time, 120) AS group_id
        ,MIN(ad_start_time) AS group_start_time
        ,SUM(ad_duration) AS group_duration
    FROM A_Sena_Temp_Table
    GROUP BY CONVERT(VARCHAR(13), ad_start_time, 120)
    )
UPDATE T
SET T.group_start_time = cte.group_start_time
    ,T.group_duration = cte.group_duration
FROM A_Sena_Temp_Table AS T
INNER JOIN cte ON CONVERT(VARCHAR(13), T.ad_start_time, 120) = cte.group_id

这给了我以下结果（请注意，在我的数据中 seq_id 和 ad_id 是假人）

sql - SQL需要10分钟内发生的事件列表的最小时间实例和总持续时间

问题描述

解决方案

推荐阅读