我有這個玩具示例，它為我提供了按不同類別分隔的稀疏值表。我想要一個密集的矩陣，其中所有列都是單獨排序的。
drop table if exists temp_table;
create temp table temp_table(
rowid int
, category text
, score int
);
insert into temp_table values (0, 'cat1', 10);
insert into temp_table values (1, 'cat2', 21);
insert into temp_table values (2, 'cat3', 32);
insert into temp_table values (3, 'cat2', 23);
insert into temp_table values (4, 'cat2', 24);
insert into temp_table values (5, 'cat3', 35);
insert into temp_table values (6, 'cat1', 16);
insert into temp_table values (7, 'cat1', 17);
insert into temp_table values (8, 'cat2', 28);
insert into temp_table values (9, 'cat2', 29);
這給出了這個臨時表：
| 行號 | 類別 | 分數 |
|---|---|---|
| 0 | 貓1 | 10 |
| 1 | 貓2 | 21 |
| 2 | 三類 | 32 |
| 3 | 貓2 | 23 |
| 4 | 貓2 | 24 |
| 5 | 三類 | 35 |
| 6 | 貓1 | 16 |
| 7 | 貓1 | 17 |
| 8 | 貓2 | 28 |
| 9 | 貓2 | 29 |
然后根據類別將分數值排序到不同的列：
select "cat1", "cat2", "cat3"
from crosstab(
$$ select rowid, category, score from temp_table $$ -- as source_sql
, $$ select distinct category from temp_table order by category $$ -- as category_sql
) as (rowid int, "cat1" int, "cat2" int, "cat3" int)
輸出：
| 貓1 | 貓2 | 三類 |
|---|---|---|
| 10 | ||
| 21 | ||
| 32 | ||
| 23 | ||
| 24 | ||
| 35 | ||
| 16 | ||
| 17 | ||
| 28 | ||
| 29 |
但我希望查詢的結果是密集的，比如：
| 貓1 | 貓2 | 三類 |
|---|---|---|
| 10 | 21 | 32 |
| 16 | 23 | 35 |
| 17 | 24 | |
| 28 | ||
| 29 |
也許 PostgreSQL 的交叉表甚至不是執行此操作的正確工具，但我首先想到的是，它生成的稀疏表接近我需要的結果。
uj5u.com熱心網友回復：
這應該適用于確切給定的示例資料和預期輸出。
select max(cat1), max(cat2), max(cat3)
from crosstab(
$$ select rank() over(partition by category order by rowid) as ranking,
rowid,
category,
score
from temp_table
order by rowid, category asc$$ -- as source_sql
, $$ select distinct category
from temp_table
order by category $$ -- as category_sql
) as (ranking int, rowid int, "cat1" int, "cat2" int, "cat3" int)
group by ranking
order by ranking asc
您可以在這里測驗解決方案 - 
- 在外部查詢中，對于在源 SQL 查詢中獲得的每個排名，我有效地選擇了
max()每個類別的值。
uj5u.com熱心網友回復：
with cte as (
select category, score, row_number() over (
partition by category order by score
) as r
from temp_table
)
select
sum(score) filter (where category = 'cat1') as cat1,
sum(score) filter (where category = 'cat2') as cat2,
sum(score) filter (where category = 'cat3') as cat3
from cte
group by r
order by r
;
如果列數已知并且相當小，則FILTER可能是比CROSSTAB需要擴展的更好的選擇。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/478882.html
標籤：PostgreSQL 交叉表