如何用SQL计算指定时段内特定灯光的累计开启时长？

当然可以实现，下面是具体的解决方案：

假设表结构 #

先假设你用来存储开关事件的两张表结构如下（如果实际字段名不同，对应调整即可）：

• light_on_events：存储灯光开启事件，包含字段 iddec（灯光十进制ID）、label（灯光名称）、on_time（开启时间戳）
• light_off_events：存储灯光关闭事件，包含字段 iddec（灯光十进制ID）、off_time（关闭时间戳）

核心SQL查询 #

以下SQL可以计算指定24小时时段内（示例时段为'2024-01-01 00:00:00'到'2024-01-02 00:00:00'）各灯光的累计开启时长：

WITH time_range AS (
    SELECT 
        '2024-01-01 00:00:00'::TIMESTAMP AS start_time,
        '2024-01-02 00:00:00'::TIMESTAMP AS end_time
)
SELECT
    lo.iddec,
    lo.label,
    SUM(
        EXTRACT(EPOCH FROM 
            LEAST(COALESCE(loff.off_time, tr.end_time), tr.end_time) 
            - GREATEST(lo.on_time, tr.start_time)
        )
    ) AS TimeOnInSeconds
FROM light_on_events lo
CROSS JOIN time_range tr
LEFT JOIN LATERAL (
    SELECT off_time
    FROM light_off_events
    WHERE iddec = lo.iddec
      AND off_time > lo.on_time
    ORDER BY off_time ASC
    LIMIT 1
) loff ON TRUE
WHERE lo.on_time < tr.end_time
  AND GREATEST(lo.on_time, tr.start_time) < LEAST(COALESCE(loff.off_time, tr.end_time), tr.end_time)
GROUP BY lo.iddec, lo.label
ORDER BY lo.iddec;

关键逻辑说明 #

• 时段定义：用CTE time_range统一指定统计时段，后续修改只需调整这里的时间值
• 匹配对应关闭事件：通过LATERAL JOIN为每个开启事件找到同一灯光的最早后续关闭时间，避免匹配错位
• 边界处理：用GREATEST和LEAST覆盖两种特殊场景：
  • 开启时间早于统计时段：从时段起始点开始计算时长
  • 关闭时间晚于统计时段/无对应关闭事件：计算到时段结束点
• 时长转换：EXTRACT(EPOCH FROM ...)将时间差转为秒数，再通过SUM累加单灯光的总开启时长

示例输出 #

执行后会得到符合需求的结果：

内容的提问来源于stack exchange，提问作者rm.