背景
我正在使用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