我需要一個查詢來幫助我獲取如下所述的資料
我有一張桌子如下
| ID | 日期 | 地位 | TIME09_10 | TIME10_11 | TIME11_12 | TIME12_13 |
|---|---|---|---|---|---|---|
| 1 | 2021-09-01 | 跑 | 30 | 60 | 45 | 0 |
| 2 | 2021-09-01 | 走 | 15 | 0 | ○ | 30 |
| 3 | 2021-09-01 | 停止 | 15 | 0 | 15 | 30 |
我希望這些資料采用以下格式。從上表中，值列名稱必須替換為小時并與日期列連接。我真的很感激有人的幫助。我正在使用 postgres sql 資料庫。
| 約會時間 | 跑 | 走 | 停止 |
|---|---|---|---|
| 2021-09-01 09:00 | 30 | 15 | 15 |
| 2021-09-01 10:00 | 60 | 0 | 0 |
| 2021-09-01 11:00 | 45 | 0 | 15 |
| 2021-09-01 12:00 | 0 | 30 | 30 |
uj5u.com熱心網友回復：
您可以使用該crosstab(source_sql text, category_sql text)功能。您需要安裝tablefunc擴展：
create extension if not exists tablefunc;
閱讀檔案中的擴展。
該函式需要三列格式的資料(row_name, category, value)。在這種情況下，它們是date time，status和duration。
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
Test the jsonb solution in Db<>fiddle.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/313936.html
標籤：sql PostgreSQL 枢
下一篇：如何在原始SQL中查詢？