postgres中包含多个值列的数据透视表

Question

我需要一个查询，这将有助于我获得如下所述的数据

我有一个表如下

<表类> ID 日期 状态 TIME09_10 TIME10_11 TIME11_12 TIME12_13 tbody><<tr>12021-09-01运行306045022021-09-01走150o3032021-09-01停止1501530

可以使用crosstab(source_sql text, category_sql text)函数。您需要安装tablefunc扩展:

create extension if not exists tablefunc;

阅读文档中的扩展。

函数期望的数据格式为三列(row_name, category, value)。在这种情况下他们date+timestatusduration。

select 
date+ '8 hour'::interval+ '1 hour'::interval* i as hour,
status,
(array[time09_10, time10_11, time11_12, time12_13])[i] as duration
from my_table
cross join generate_series(1, 4) i

hour         | status | duration
---------------------+--------+----------
2021-09-01 09:00:00 | RUN    |       30
2021-09-01 09:00:00 | WALK   |       15
2021-09-01 09:00:00 | STOP   |       15
2021-09-01 10:00:00 | RUN    |       60
2021-09-01 10:00:00 | WALK   |        0
2021-09-01 10:00:00 | STOP   |        0
2021-09-01 11:00:00 | RUN    |       45
2021-09-01 11:00:00 | WALK   |        0
2021-09-01 11:00:00 | STOP   |       15
2021-09-01 12:00:00 | RUN    |        0
2021-09-01 12:00:00 | WALK   |       30
2021-09-01 12:00:00 | STOP   |       30
(12 rows)

将查询作为函数的第一个参数传递:

select *
from crosstab(
$source$
select 
date+ '8 hour'::interval+ '1 hour'::interval* i as hour,
status,
(array[time09_10, time10_11, time11_12, time12_13])[i] as duration
from my_table
cross join generate_series(1, 4) i
$source$,
$category$
values('RUN'), ('STOP'), ('WALK')
$category$
) as (hour timestamp, run int, stop int, walk int)

hour         | run | stop | walk
---------------------+-----+------+------
2021-09-01 09:00:00 |  30 |   15 |   15
2021-09-01 10:00:00 |  60 |    0 |    0
2021-09-01 11:00:00 |  45 |   15 |    0
2021-09-01 12:00:00 |   0 |   30 |   30
(4 rows)

如果您不想使用扩展名，还有一个不错的选择。使用jsonb函数将第一个查询结果转换为预期输出:

select 
hour, 
(activities->>'RUN')::int as run,
(activities->>'STOP')::int as stop,
(activities->>'WALK')::int as walk
from (
select hour, jsonb_object_agg(status, duration) as activities
from (
select 
date+ '8 hour'::interval+ '1 hour'::interval* i as hour,
status,
(array[time09_10, time10_11, time11_12, time12_13])[i] as duration
from my_table
cross join generate_series(1, 4) i
) s
group by hour
) s
order by hour

在Db<>fiddle中测试jsonb解决方案。