logo标签列表

SQL Server - 跨多天按分钟聚合数据

sqlsql-servert-sqltimedata-warehouse
3

背景

我正在使用Microsoft SQL Server 2016。

有一个名为“原始数据”（Raw_data）的数据库表，其中包含了一台机器的状态以及它的启动时间。有几台机器，每台机器每分钟向数据库写入它的状态。

为了降低数据量，我正在尝试将数据聚合到1分钟的块中，以便存储以进行进一步的分析。由于容量限制，我想每隔几分钟执行一次这个转换逻辑（例如，安排SQL Server代理作业），删除原始数据并只保留聚合数据。

为了简化示例，假设“Raw_data”看起来像这样：

╔════╦════════════╦════════╦═════════════════════╗
║ id ║ fk_machine ║ status ║     created_at      ║
╠════╬════════════╬════════╬═════════════════════╣
║  1 ║       2222 ║      0 ║ 2020-08-19 22:15:00 ║
║  2 ║       2222 ║      3 ║ 2020-08-19 22:15:30 ║
║  3 ║       2222 ║      5 ║ 2020-08-19 23:07:00 ║
║  4 ║       2222 ║      1 ║ 2020-08-20 00:20:00 ║
║  5 ║       2222 ║      0 ║ 2020-08-20 00:45:00 ║
║  6 ║       2222 ║      5 ║ 2020-08-20 02:20:00 ║
╚════╩════════════╩════════╩═════════════════════╝

此外，还有名为“Dim_date”和“Dim_time”的数据库表，大致如下所示：
╔══════════╦══════════════╗
║ datekey  ║ date_iso8601 ║
╠══════════╬══════════════╣
║ 20200101 ║ 2020-01-01   ║
║ 20200102 ║ 2020-01-02   ║
║ ...      ║ ...          ║
║ 20351231 ║ 2035-12-31   ║
╚══════════╩══════════════╝

╔═════════╦══════════╦═════════════════╗
║ timekey ║ time_iso ║ min_lower_bound ║
╠═════════╬══════════╬═════════════════╣
║ 1       ║ 00:00:01 ║ 00:00:00        ║
║ 2       ║ 00:00:02 ║ 00:00:00        ║
║ ...     ║ ...      ║ ...             ║
║ 80345   ║ 08:03:45 ║ 08:03:00        ║
║ ...     ║ ...      ║ ...             ║
║ 134504  ║ 13:45:04 ║ 13:45:00        ║
║ 134505  ║ 14:45:05 ║ 13:45:00        ║
║ ...     ║ ...      ║ ...             ║
║ 235959  ║ 23:59:59 ║ 23:59:59        ║
╚═════════╩══════════╩═════════════════╝

结果应该是这样的：
╔══════════════╦═════════════════╦════════════╦════════╦═══════════════╗
║ date_iso8601 ║ min_lower_bound ║ fk_machine ║ status ║ total_seconds ║
╠══════════════╬═════════════════╬════════════╬════════╬═══════════════╣
║ 2020-08-19   ║ 22:15:00        ║ 2222       ║ 0      ║ 30            ║
║ 2020-08-19   ║ 20:15:00        ║ 2222       ║ 3      ║ 30            ║
║ 2020-08-19   ║ 20:16:00        ║ 2222       ║ 3      ║ 60            ║
║ 2020-08-19   ║ 20:17:00        ║ 2222       ║ 3      ║ 60            ║
║ ...          ║ ...             ║ ...        ║ ...    ║ ...           ║
║ 2020-08-19   ║ 23:06:00        ║ 2222       ║ 3      ║ 60            ║
║ 2020-08-19   ║ 23:07:00        ║ 2222       ║ 5      ║ 60            ║
║ 2020-08-19   ║ 23:08:00        ║ 2222       ║ 5      ║ 60            ║
║ ...          ║ ...             ║ ...        ║ ...    ║ ...           ║
║ 2020-08-20   ║ 00:19:00        ║ 2222       ║ 5      ║ 60            ║
║ 2020-08-20   ║ 00:20:00        ║ 2222       ║ 1      ║ 60            ║
║ 2020-08-20   ║ 00:21:00        ║ 2222       ║ 1      ║ 60            ║
║ ...          ║ ...             ║ ...        ║ ...    ║ ...           ║
║ 2020-08-20   ║ 00:44:00        ║ 2222       ║ 1      ║ 60            ║
║ 2020-08-20   ║ 00:45:00        ║ 2222       ║ 0      ║ 60            ║
╚══════════════╩═════════════════╩════════════╩════════╩═══════════════╝

尝试

为了计算每个状态每分钟的持续时间，我使用了CTE和LEAD从数据库表中获取下一个状态的开始日期和时间，然后与维度表进行连接并对结果进行聚合。

WITH CTE_MACHINE_STATES(START_DATEKEY, 
                        START_TIMEKEY, 
                        FK_MACHINE, 
                        END_DATEKEY, 
                        END_TIMEKEY)
     AS (SELECT CAST(CONVERT(CHAR(8), CREATED_AT, 112) AS INT), -- ISO: yyyymmdd
                CONVERT(INT, REPLACE(CONVERT(CHAR(8), READING_TIME, 108), ':', '')), 
                FK_MACHINE, 
                STATUS, 
                CAST(CONVERT(CHAR(8), LEAD(CREATED_AT, 1) OVER(PARTITION BY FK_MACHINE
                ORDER BY CREATED_AT), 112) AS INT),
                CONVERT(INT, REPLACE(CONVERT(CHAR(8), LEAD(CREATED_AT, 1) OVER(PARTITION BY FK_MACHINE
                ORDER BY CREATED_AT), 108), ':', ''))
         FROM RAW_DATA)
     SELECT DATE_ISO8601, 
            MIN_LOWER_BOUND, 
            FK_MACHINE, 
            STATUS, 
            SUM(1) AS TOTAL_SECONDS -- Duration
     FROM CTE_MACHINE_STATES
     CROSS JOIN DIM_DATE
     CROSS JOIN DIM_TIME
     WHERE TIMEKEY >= START_TIMEKEY AND 
           TIMEKEY < END_TIMEKEY AND 
           END_TIMEKEY IS NOT NULL AND -- last entry per machine and status
           DATEKEY BETWEEN START_DATEKEY AND END_DATEKEY
     GROUP BY FK_MACHINE, 
              STATUS, 
              DATE_ISO8610, 
              MIN_LOWER_BOUND
     ORDER BY DATE_ISO8610, 
              MIN_LOWER_BOUND;

问题

如果状态持续到午夜，它将无法正确聚合。例如， "Raw_data" 中 id = 3 的状态从23:07开始，到第二天00:20结束。在这种情况下，timekey大于end_timekey，因此状态被过滤器TIMEKEY < END_TIMEKEY从结果表中排除。我还没有想出如何更改连接条件以包括这种持久状态的解决方案，但得到预期的结果。

PS：我已经写过，通常每几秒钟会更新状态。因此，该问题仅在极端情况下出现，例如机器关闭。

解决方案

不幸的是，我没有收到如何使用日期和时间维度表获得预期结果的答案。但dnoeth的递归CTE方法很好，所以我选择了它：

WITH cte_outer AS (
    SELECT fk_machine,
           status,
           created_at,
           DATEADD(minute, DATEDIFF(minute, '2000', created_at), '2000') AS min_lower_bound, --truncates seconds from start time
           LEAD(created_at) OVER(PARTITION BY fk_machine ORDER BY created_at) AS end_time
    FROM raw_data
),
    cte_recursive AS (
        SELECT fk_machine,
               status,
               min_lower_bound,
               end_time,
               CASE
                 WHEN end_time > DATEADD(minute, 1, min_lower_bound)
                 THEN DATEDIFF(s, created_at, DATEADD(minute, 1, min_lower_bound))
                 ELSE DATEDIFF(s, created_at, end_time)
               END AS total_seconds
        FROM cte_outer

        UNION ALL

        SELECT fk_machine,
               status,
               DATEADD(minute, 1, min_lower_bound), -- next time segment (minute)
               end_time,
               CASE
                 WHEN end_time >= DATEADD(minute, 2, min_lower_bound)
                 THEN 60
                 ELSE DATEDIFF(s, DATEADD(minute, 1, min_lower_bound), end_time)
               END
        FROM cte_recursive
        WHERE end_time > DATEADD(minute, 1, min_lower_bound)
)
SELECT min_lower_bound,
       fk_machine,
       status,
       total_seconds
FROM cte_recursive
ORDER BY  fk_machine, 
          min_lower_bound
1
我在工业界工作多年，这种问题经常出现（而且，我是法国人，所以我们每年有两次夏令时问题...） 如果您的工作配置/政策允许，可以看看时序数据库，例如InfluxDb，您会感到惊讶。 - Romka
@Marmite Bomber：我简化了示例以展示在尝试聚合跨越多天的状态时遇到的问题。原始数据库通过每台机器大约3秒的状态更新间隔进行填充，只要它们没有关闭。聚合数据将用于进一步分析，例如机器的占用率。 - jequ
我的观点是，如果机器关闭了一个小时，你真的想要用最后一个可用状态来“聚合”60分钟的行吗？只保存最后一个可用的分钟和持续时间3600秒不是更好/足够吗？ - Marmite Bomber
@Parfait：感谢您的反馈。我编辑了我的问题并添加了明确的交叉连接子句，以使发生的情况更加清晰。 - jequ
1
我能用一个计数表来做这个吗？这样更容易，也不需要你的DIM表。 - SteveC
显示剩余4条评论
2个回答
2
对于这样的情况，将键连接到单个日期时间并不像看起来那样昂贵。然后，您可以调用DATEDIFF()检查比较的正数、负数、绝对值。我曾经运行过类似的代码，将瞬时数据转换为跨越多个十年的分钟聚合，并且DATEDIFF确实起到了很大的作用。但是，如果您只是提取原始数据并在具有良好日期时间库的语言中执行计算，则效果会更好。 SQL总是答案，直到它不是。
可能导致问题之一的原因是以下语句：
WHERE TIMEKEY >= START_TIMEKEY AND 
              TIMEKEY < END_TIMEKEY AND 
              END_TIMEKEY IS NOT NULL AND 
              DATEKEY BETWEEN START_DATEKEY AND END_DATEKEY

如果日期和时间没有分开，你可以这样说：
WHERE DateTimeKey >= START_DateTimeKey AND 
              DateTimeKey < END_DateTimeKey AND 
              END_TIME-KEY IS NOT NULL

如果您正在尝试按时间值聚合数据，消除任何可能导致问题的时间表可能会很有帮助。用递归和持续时间替换时间表可能是个好主意。您还需要考虑以下条件：

事件的结束时间必须始终在聚合期的开始时间之后：

DateDiff(second, Period_Start_Time, Event_End) > 0

事件开始时间必须始终早于聚合周期结束时间：
DateDiff(second, Period_Start_Time, Event_Start) <= @Period_Duration

有几种方法可以在不同时间段分发事件数据，但datediff函数也可以帮助线性分布。

谢谢您的回复。无需连接日期和时间键，因为状态的开始时间（Raw_data中的“created_at”）是以日期时间格式表示的。您能否更加精确或者提供示例代码，说明在我们通过Datediff计算出每个状态的持续时间后，如何按分钟进行聚合？ - jequ
2

这是一个递归CTE的用例，每次递归增加created_at一分钟：

with cte as 
 (
   select fk_machine
     ,status  
     ,start_minute
     ,end_time
     ,case
        when end_time > dateadd(minute, 1,start_minute)
        then datediff(s, created_at, dateadd(minute, 1,start_minute)) 
        else datediff(s, created_at, end_time )
      end as seconds
   from
    (
      select fk_machine
        ,status
        ,created_at 
        ,dateadd(minute, datediff(minute, 0, created_at), 0) as start_minute
        ,lead(created_at)
         over (PARTITION BY fk_machine
               order by created_at) as end_time
      from tab
    ) as dt
 
   union all
 
   select fk_machine
     ,status
     ,dateadd(minute, 1,start_minute)
     ,end_time
     ,case
        when end_time >= dateadd(minute, 2,start_minute)
        then 60
        else datediff(s, dateadd(minute, 1,start_minute), end_time)
      end
    from cte
    where end_time > dateadd(minute, 1,start_minute)
 )
select * from cte
order by 1,3,4;

请查看fiddle。
非常感谢！我会采用那个解决方案。虽然它没有使用维度表（这不是我提出的要求），但我已将其添加为被接受的答案。当存在多台机器时，获取当前状态结束时间的“partition by”部分变得非常重要。 - jequ
当然，如果没有 PARTITION BY fk_machine，它将不能按预期工作，我忘记添加了。如果您想要维度表的外键，可以简单地使用您最初的 CASTs。 - dnoeth
是的，我知道。谢谢！ :) - jequ
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接