我在sql中有一個表
表鏈接
有沒有辦法用contract_kind作為行和百分位值作為列重新格式化這個表，而我不必創建4個帶有where子句的表并加入它們。使用 Postgres。
uj5u.com熱心網友回復：
TLDR：只需crosstab按照檔案使用該功能
長回復：我重新創建了一個類似的案例
create table test (id int, contract_kind text, percentile int, cut_off_time float);
insert into test values
(1,'TEMPLATE',25,1.91),
(2,'TEMPLATE',50,51.93),
(3,'TEMPLATE',75,158.41),
(4,'TEMPLATE',90,343.01),
(5,'TEMPLATE_EDITABLE',25,26),
(6,'TEMPLATE_EDITABLE',50,27),
(7,'TEMPLATE_EDITABLE',75,28),
(8,'TEMPLATE_EDITABLE',90,29),
(9,'UPLOAD_EDITABLE',25,10),
(10,'UPLOAD_EDITABLE',50,20),
(11,'UPLOAD_EDITABLE',75,30),
(12,'UPLOAD_EDITABLE',90,40),
(13,'UPLOAD_SIGN',25,40),
(14,'UPLOAD_SIGN',50,77),
(15,'UPLOAD_SIGN',75,99),
(16,'UPLOAD_SIGN',90,133);
結果：
id | contract_kind | percentile | cut_off_time
---- ------------------- ------------ --------------
1 | TEMPLATE | 25 | 1.91
2 | TEMPLATE | 50 | 51.93
3 | TEMPLATE | 75 | 158.41
4 | TEMPLATE | 90 | 343.01
5 | TEMPLATE_EDITABLE | 25 | 26
6 | TEMPLATE_EDITABLE | 50 | 27
7 | TEMPLATE_EDITABLE | 75 | 28
8 | TEMPLATE_EDITABLE | 90 | 29
9 | UPLOAD_EDITABLE | 25 | 10
10 | UPLOAD_EDITABLE | 50 | 20
11 | UPLOAD_EDITABLE | 75 | 30
12 | UPLOAD_EDITABLE | 90 | 40
13 | UPLOAD_SIGN | 25 | 40
14 | UPLOAD_SIGN | 50 | 77
15 | UPLOAD_SIGN | 75 | 99
16 | UPLOAD_SIGN | 90 | 133
(16 rows)
現在要使用crosstab您需要創建tablefunc擴展。
create extension tablefunc;
然后你可以用它來透視資料
select * from
crosstab('select percentile, contract_kind, cut_off_time from test order by 1,2')
as ct(percentile int, template float, template_editable float, upload_editable float, upload_sing float);
結果
percentile | template | template_editable | upload_editable | upload_sing
------------ ---------- ------------------- ----------------- -------------
25 | 1.91 | 26 | 10 | 40
50 | 51.93 | 27 | 20 | 77
75 | 158.41 | 28 | 30 | 99
90 | 343.01 | 29 | 40 | 133
(4 rows)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/504855.html
標籤：PostgreSQL 格式化
下一篇：從Auth0連接到HerokuPostgres會導致：errnopg_hba.confentryforhost,noencryption