如何在PostgreSQL中计算指定状态切换的秒级时间差

数据表结构与示例数据 #

rid |    m_id    |      prosessdate       | m30 | door | cutting
-----+------------+------------------------+-----+------+---------
   1 | 536477698  | 05-07-2023 11:05:12    |   1 |    0 |       0
   2 | 536477698  | 05-07-2023 11:05:13    |   1 |    0 |       0
   3 | 536477698  | 05-07-2023 11:05:14    |   1 |    0 |       0
   4 | 536477698  | 05-07-2023 11:05:15    |   1 |    0 |       0
   5 | 536477698  | 05-07-2023 11:05:16    |   1 |    0 |       0
   6 | 536477698  | 05-07-2023 11:05:17    |   1 |    0 |       0
   7 | 536477698  | 05-07-2023 11:05:18    |   1 |    0 |       0
   8 | 536477698  | 05-07-2023 11:05:19    |   1 |    0 |       0
   9 | 536477698  | 05-07-2023 11:05:20    |   1 |    0 |       0
  10 | 536477698  | 05-07-2023 11:05:21    |   1 |    1 |       0
  11 | 536477698  | 05-07-2023 11:05:22    |   1 |    1 |       0
  12 | 536477698  | 05-07-2023 11:05:23    |   1 |    1 |       0
  13 | 536477698  | 05-07-2023 11:05:24    |   1 |    1 |       0
  14 | 536477698  | 05-07-2023 11:05:25    |   1 |    1 |       0
  15 | 536477698  | 05-07-2023 11:05:26    |   1 |    1 |       0
  16 | 536477698  | 05-07-2023 11:05:27    |   1 |    1 |       0
  17 | 536477698  | 05-07-2023 11:05:28    |   1 |    1 |       0
  18 | 536477698  | 05-07-2023 11:05:29    |   0 |    0 |       0
  19 | 536477698  | 05-07-2023 11:05:30    |   0 |    0 |       0
  20 | 536477698  | 05-07-2023 11:05:31    |   0 |    0 |       0
  21 | 536477698  | 05-07-2023 11:05:32    |   0 |    0 |       0
  22 | 536477698  | 05-07-2023 11:05:33    |   0 |    0 |       1
  23 | 536477698  | 05-07-2023 11:05:34    |   0 |    0 |       1

需求 #

需要计算两组秒级时间差：

• 第一条m30=1记录的prosessdate，与第一条door=1记录的prosessdate的时间差
• 第一条cutting=1记录的prosessdate，与第一条door=1记录的prosessdate的时间差

解决方案 #

方法一：聚合函数直接计算（简洁版） #

SELECT
  -- 计算m30首次到door首次的秒级时间差
  EXTRACT(EPOCH FROM (MIN(CASE WHEN door = 1 THEN prosessdate END) - MIN(CASE WHEN m30 = 1 THEN prosessdate END))) AS m30_to_door_seconds,
  -- 计算cutting首次到door首次的秒级时间差
  EXTRACT(EPOCH FROM (MIN(CASE WHEN cutting = 1 THEN prosessdate END) - MIN(CASE WHEN door = 1 THEN prosessdate END))) AS cutting_to_door_seconds
FROM your_table_name; -- 替换为你的实际表名

方法二：窗口函数精准定位（灵活版） #

如果需要更明确的排序规则（比如按时间严格排序确保取最早记录），或者后续要扩展多维度分组（如按m_id分组计算），可以用窗口函数：

WITH first_occurrences AS (
  SELECT
    prosessdate,
    m30,
    door,
    cutting,
    -- 标记每个状态首次出现的行
    ROW_NUMBER() OVER (PARTITION BY m30 ORDER BY prosessdate) AS m30_rn,
    ROW_NUMBER() OVER (PARTITION BY door ORDER BY prosessdate) AS door_rn,
    ROW_NUMBER() OVER (PARTITION BY cutting ORDER BY prosessdate) AS cutting_rn
  FROM your_table_name
)
SELECT
  EXTRACT(EPOCH FROM (door_first.prosessdate - m30_first.prosessdate)) AS m30_to_door_seconds,
  EXTRACT(EPOCH FROM (cutting_first.prosessdate - door_first.prosessdate)) AS cutting_to_door_seconds
FROM
  (SELECT prosessdate FROM first_occurrences WHERE m30 = 1 AND m30_rn = 1) AS m30_first,
  (SELECT prosessdate FROM first_occurrences WHERE door = 1 AND door_rn = 1) AS door_first,
  (SELECT prosessdate FROM first_occurrences WHERE cutting = 1 AND cutting_rn = 1) AS cutting_first;

说明 #

• EXTRACT(EPOCH FROM ...)：PostgreSQL中专门用于将时间差转换为秒数的函数
• 聚合函数方法逻辑简洁，适合单维度场景；窗口函数方法扩展性更强，支持复杂分组和筛选规则

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