sql - 将具有一个时间戳和对应的 x 整数数组的一行显示为具有 x 个时间戳和一个对应整数的 x 行

Question

抱歉问题措辞不佳，我不知道如何简洁地描述我的问题。我正在处理来自传感器的数据，该传感器每 10 分钟扫描一次房间，以查看房间内有多少人。数据看起来像这样

reading_id |      timestamp      |    values      |          
--------------------------------------------------
1          | 2019-02-28 01:00:00 | {1,1,6,17,4,4} |
2          | 2019-02-28 02:00:00 | {3,4,6,3,0,8}  |
3          | 2019-02-28 03:00:00 | {0,0,0,0,0,0}  |

有什么办法可以让它看起来像这样吗？

reading_id |      timestamp      |    values      |
-------------------------------------------------- 
1          | 2019-02-28 01:00:00 | 1              |
1          | 2019-02-28 01:10:00 | 1              |
1          | 2019-02-28 01:20:00 | 6              |
1          | 2019-02-28 01:30:00 | 17             |
1          | 2019-02-28 01:40:00 | 4              |
1          | 2019-02-28 01:50:00 | 4              |
2          | 2019-02-28 02:00:00 | 3              |
...

我不确定 reading_id 会发生什么，但无论如何我都不需要它。

Answer 1

您可以使用 unnest with ordinality 来获取每个数组元素的索引，并使用它来添加 10 分钟的间隔。

select t.reading_id,
       t."timestamp" + interval '10 minutes' * (u.idx - 1) AS timestamp,
       u.value
from the_table t
   cross join unnest(t."values") with ordinality AS u(value, idx)
order by 1,2;

在线示例:https://rextester.com/TEB87353