# PostgreSQL 使用 Hstore 來做 pivot
## 前言
在前一篇([傳送門](https://hackmd.io/@pgsql-tw/rJ0jk894T))中,我們使用了json的函數,搭配動態產生的table,來做到pivot的功能.
在PostgreSQL的資料型態中,還有另一個key-value的hstore.屬於extension.
我們來看一下之前使用array存放tag的例子([傳送門](https://hackmd.io/@pgsql-tw/rJSOHh8zT)),來做tag統計並轉換.
## 測試資料與前期準備
```sql=
select * from videos_array;
-[ RECORD 1 ]-------------------------------------------
vid_id | 1
vid_number | MEYD-844
vid_tags | {1,2,3,4,5,6,7,8}
txt_tags | {熟女,人妻,巨乳,泳裝,單體作品,高清,獨家,4K}
-[ RECORD 2 ]-------------------------------------------
vid_id | 2
vid_number | WAAA-293
vid_tags | {1,3,7,8,9,10,11,12}
txt_tags | {熟女,巨乳,獨家,4K,女教師,痴女,大屁股,中出}
select distinct unnest(txt_tags)
from videos_array;
unnest
----------
泳裝
獨家
痴女
中出
女教師
大屁股
4K
巨乳
高清
單體作品
人妻
熟女
(12 rows)
-- 因為 4K 不能當欄位名稱,所以我們要變化一下.
-- 數字及符號開頭不能當欄位名稱
-- PostgreSQL 標準是使用小寫欄位名稱,要用大寫時需要再處理
create view v_videos_array(tag) as
with t1 as (
select distinct unnest(txt_tags) as tag
from videos_array
)
select case
when tag = '4K' then 'fourk'
else tag
end as tag
from t1;
-- 建立上面各個tag int型態欄位的 temp table
DO LANGUAGE plpgsql $$
DECLARE v_sqlstring VARCHAR = '';
BEGIN
v_sqlstring := concat('create temp table tmpxt231121f as select ',
(select string_agg(concat('NULL::int AS ', tag), ' ,')
from v_videos_array)::text);
EXECUTE(v_sqlstring);
END $$;
\d tmpxt231121f
Table "pg_temp_3.tmpxt231121f"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
泳裝 | integer | | |
獨家 | integer | | |
痴女 | integer | | |
中出 | integer | | |
女教師 | integer | | |
大屁股 | integer | | |
fourk | integer | | |
巨乳 | integer | | |
高清 | integer | | |
單體作品 | integer | | |
人妻 | integer | | |
熟女 | integer | | |
```
# Hstore 的聚合方式
```sql=
with t1 as (
select unnest(txt_tags) as tag
from videos_array
), t2 as (
select case
when tag = '4K' then 'fourk'
else tag
end as tag
, count(*) as cnt
from t1
group by tag
)
select hstore(array_agg(array[tag, cnt::text])) as hs1
, hstore(array_agg(tag), array_agg(cnt)::text[]) as hs2
from t2;
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------
hs1 | "fourk"=>"2", "中出"=>"1", "人妻"=>"1", "巨乳"=>"2", "泳裝"=>"1", "熟女"=>"2", "獨家"=>"2", "痴女"=>"1", "高清"=>"1", "大屁股"=>"1", "女教師"=>"1", "單體作品"=>"1"
hs2 | "fourk"=>"2", "中出"=>"1", "人妻"=>"1", "巨乳"=>"2", "泳裝"=>"1", "熟女"=>"2", "獨家"=>"2", "痴女"=>"1", "高清"=>"1", "大屁股"=>"1", "女教師"=>"1", "單體作品"=>"1"
```
可以觀察到,我們是使用了array的聚合,再將array轉為hstore.可以使用兩種方式,結果都相同.要注意到數字是轉型為text了.
## Hstore 展開的方式
```sql=
with t1 as (
select unnest(txt_tags) as tag
from videos_array
), t2 as (
select case
when tag = '4K' then 'fourk'
else tag
end as tag
, count(*) as cnt
from t1
group by tag
), t3 as (
select hstore(array_agg(array[tag, cnt::text])) as hs1
, hstore(array_agg(tag), array_agg(cnt)::text[]) as hs2
from t2
)
select (populate_record(null::tmpxt231121f, hs1)).*
from t3;
```
執行結果如下圖:
![螢幕快照 2023-11-22 下午3.30.36](https://hackmd.io/_uploads/SkpjRXoNT.png)
hs1,hs2 擇一即可.
## 結語
可以看到跟json的方式類似.這樣就有了一個套路(pattern),聚合成key-value 結構,再展開為指定的型態.
在實務上,我們不會將所有的tag都顯示,因為tag可能會非常多,這時候可以只取出前N種來顯示.