Postgres:将连续行转换为离散行

Question

我不确定这个标题是否正确。我花了一些时间找出适合这个案子的最佳头衔。

算了吧。我有这种格式的数据：

| room |     changes      |   created_at    |
|------|------------------|-----------------|
| A    | [free, occupied] | 2021-11-1 18:00 |
| B    | [free, occupied] | 2021-11-1 20:30 |
| B    | [occupied, free] | 2021-11-1 23:30 |
| A    | [occupied, free] | 2021-11-2 00:15 |
| B    | [free, occupied] | 2021-11-2 01:00 |

所需的输出将是

+------+----------+-----------------+
| room | occupied |   Period        |
+------+----------+-----------------+
| A    | NO       | 2021-11-1 18:00 |
| B    | NO       | 2021-11-1 18:00 |
| A    | NO       | 2021-11-1 19:00 |
| B    | NO       | 2021-11-1 19:00 |
| A    | YES      | 2021-11-1 20:00 |
| B    | NO       | 2021-11-1 20:00 |
| A    | YES      | 2021-11-1 21:00 |
| B    | NO       | 2021-11-1 21:00 |
| A    | YES      | 2021-11-1 22:00 |
| B    | NO       | 2021-11-1 22:00 |
| A    | YES      | 2021-11-1 23:00 |
| B    | YES      | 2021-11-1 23:00 |
| A    | YES      | 2021-11-2 00:00 |
| B    | YES      | 2021-11-2 00:00 |
| A    | NO       | 2021-11-2 01:00 |
| B    | YES      | 2021-11-2 01:00 |
| A    | NO       | 2021-11-2 02:00 |
| B    | YES      | 2021-11-2 02:00 |
+------+----------+-----------------+

使用此逻辑生成的输出：

• 在那段时间里。房间改为occupied。results.occupied将是YES
• 在那段时间里。如果没有更改，则更改是从以前的更改中提取的。
• 在那段时间里。房间从occupied改为free。下一个句点(下一行)结果。“已占用”将是NO。

Answer 1

首先，关于你的问题的一些评论：

• 输出中的某些句点值与表中的created_at值不对应。
• 输出中的id列与表中的id列无关，因为有一半的输出行不对应于表中的一行(无法确定它们的id值)。
• 可以为表中的相关房间(A)设置输出中第一个周期值的占用值，但不能设置其他房间的占用值(B)，因此这里必须设置任意值。

然后，我向您提供一个实现以下规则的查询：

• 当房间更改为已占用的房间时，则占用期间=“是”= created_at
• 如果没有更改，即输出的给定(房间、期间)在your_table中不存在行，则从具有相同房间和最近的created_at值的前几行复制占用的值。
• 当房间改为空闲时，就占用期间= "NO“= created_at

此查询提供的结果可能与您的预期略有不同，但可以轻松地进行调整。

见SQLfiddle

SELECT t.id -- may be NULL according to the LEFT JOIN and this is a gap with the expected result as presented in the question
     , r.room
     , CASE
         WHEN t.room = r.room AND t.changes = '[free, occupied]'
         THEN 'YES'
         WHEN t.room = r.room AND t.changes = '[occupied, free]'
         THEN 'NO'
         WHEN ( jsonb_agg(t.changes)
                         FILTER (WHERE t.room = r.room)
                         OVER (PARTITION BY r.room ORDER BY p.created_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
              )->>-1 IS NOT DISTINCT FROM '[free, occupied]'
         THEN 'YES'
         ELSE 'NO'
       END AS occupied
     , p.created_at AS period
  FROM 
     ( SELECT DISTINCT ON (created_at) created_at
         FROM your_table
     ) AS p
 CROSS JOIN
     ( SELECT DISTINCT ON (room) room
         FROM your_table
     ) AS r
  LEFT JOIN your_table AS t
    ON t.room = r.room
   AND t.created_at = p.created_at
 ORDER BY p.created_at, r.room

结果，id列对应于your_table中的id值。因此，当输出行与your_table中的任何行不对应时，此id为空。