主页 / dba / 问题 / 345713
Accepted
anon
Asked: 2025-03-14 12:52:13 +0800 CST

如何在 Postgres 中得出这个观点？

  • 772

假设我们有下表：

 id |     entry_time      |  state  
----+---------------------+---------
 d1 | 2024-09-20 12:01:01 | Open
 d1 | 2024-09-20 12:01:20 | EnRoute
 d1 | 2024-09-20 12:02:40 | OnTrip
 d1 | 2024-09-20 12:04:10 | Open

我们希望最终的视图如下所示（预期输出）：

| id | start_minute        | open_time | enroute_time | ontrip_time |
|----|---------------------|-----------|--------------|-------------|
| d1 | 2024-09-20 12:01:00 | 19        | 40           | 0           |
| d1 | 2024-09-20 12:02:00 | 0         | 40           | 20          |
| d1 | 2024-09-20 12:03:00 | 0         | 0            | 60          |
| d1 | 2024-09-20 12:04:00 | 50        | 0            | 10          |

计算视图（针对第一行）

  • open_time 为 19，因为驱动程序从 12:01:01 到 12:01:20 一直处于打开状态
  • enroute_time 为 40，因为驾驶员从 12:01:20 到 12:02:00 一直处于途中状态（直到 2024-09-20 12:02:40，下一个分钟标记）

我试图计算相同的内容，但不能 100％确定如何进一步进行：

  • 我有点困惑如何将时间戳字段拆分成分钟跟踪器

    • 我正在阅读有关聚合和日期时间函数的更多信息，并且可以使用 date_trunc 来实现对记录进行分类

  • 我正在尝试找出如何做类似数据透视表的事情，这可能有助于我们得出最终答案（以我有限的理解）

或者：

  • 我们需要根据状态找到每个分钟时段的比例，因此我们可以通过在将视图从当前表扩展到分钟标记后计算每个状态的开始和结束时间标记来实现这一点。这是个好主意吗？或者有更好的方法吗？

我的尝试：

select 
    id,
    state,
    date_trunc('minute', entry_time + interval '30 second') AS trunc_min,
    entry_time as curr_time,
    date_trunc('minute', entry_time + interval '60 second') AS ceil_min,
    LEAD(entry_time) over (PARTITION by id order by entry_time) as next_time,
    extract(epoch from (lead(entry_time) over (partition by id order by entry_time) - entry_time)) as duration
from (
    values 
    ('d1', '2024-09-20 12:01:01'::timestamp, 'Open'),
    ('d1', '2024-09-20 12:01:20'::timestamp, 'EnRoute'), 
    ('d1', '2024-09-20 12:02:40'::timestamp, 'OnTrip'),
    ('d1', '2024-09-20 12:04:10'::timestamp, 'Open')
) t("id", "entry_time", "state");

输出：

 id |  state  |      trunc_min      |      curr_time      |      ceil_min       |      next_time      | duration  
----+---------+---------------------+---------------------+---------------------+---------------------+-----------
 d1 | Open    | 2024-09-20 12:01:00 | 2024-09-20 12:01:01 | 2024-09-20 12:02:00 | 2024-09-20 12:01:20 | 19.000000
 d1 | EnRoute | 2024-09-20 12:01:00 | 2024-09-20 12:01:20 | 2024-09-20 12:02:00 | 2024-09-20 12:02:40 | 80.000000
 d1 | OnTrip  | 2024-09-20 12:03:00 | 2024-09-20 12:02:40 | 2024-09-20 12:03:00 | 2024-09-20 12:04:10 | 90.000000
 d1 | Open    | 2024-09-20 12:04:00 | 2024-09-20 12:04:10 | 2024-09-20 12:05:00 |                     |

下一步该怎么办？

postgresql

1 个回答

  1. Best Answer
    WITH
source_data (id, entry_time, state) AS (
    values 
    ('d1', '2024-09-20 12:01:01'::timestamp, 'Open'),
    ('d1', '2024-09-20 12:01:20'::timestamp, 'EnRoute'), 
    ('d1', '2024-09-20 12:02:40'::timestamp, 'OnTrip'),
    ('d1', '2024-09-20 12:04:10'::timestamp, 'Open')
  ),
prepared_data (id, entry_time, next_entry_time, state) AS (
  SELECT id, 
         entry_time, 
         COALESCE(LEAD(entry_time) OVER (PARTITION BY id ORDER BY entry_time), entry_time + interval '1 minute'),
         state
  FROM source_data
  ),
timestamps (start_minute, end_minute) AS (
  SELECT generate_series, 
         generate_series + interval '1 minute'
  FROM generate_series((SELECT date_trunc('minute', MIN(entry_time)) FROM source_data),
                       (SELECT date_trunc('minute', MAX(entry_time)) FROM source_data),
                       interval '1 minute') 
  ),
overlappings (id, start_minute, state, length) AS (
  SELECT t1.id, t2.start_minute, t1.state,
         LEAST(t1.next_entry_time, t2.end_minute) - GREATEST(t1.entry_time, t2.start_minute)
  FROM prepared_data t1
  JOIN timestamps t2 ON t1.entry_time < t2.end_minute AND t1.next_entry_time > t2.start_minute
  )
SELECT id,
       start_minute,
       COALESCE(EXTRACT(EPOCH FROM SUM(CASE WHEN state = 'Open' THEN length END))::INT, 0) open_time,
       COALESCE(EXTRACT(EPOCH FROM SUM(CASE WHEN state = 'EnRoute' THEN length END))::INT, 0) enroute_time,
       COALESCE(EXTRACT(EPOCH FROM SUM(CASE WHEN state = 'OnTrip' THEN length END))::INT, 0) ontrip_time
FROM overlappings
GROUP BY id, start_minute
ORDER BY id, start_minute
    ID 开始分钟 开放时间 航路时间 行程时间
    d1 2024-09-20 12:01:00 19 40 0
    d1 2024-09-20 12:02:00 0 40 20
    d1 2024-09-20 12:03:00 0 0 60
    d1 2024-09-20 12:04:00 50 0 10

    参见分步说明。调查每个 CTE 之后的输出。

    • 3

相关问题