Try   HackMD

PostgreSQL 使用 Hstore 來做 pivot

前言

在前一篇(傳送門)中,我們使用了json的函數,搭配動態產生的table,來做到pivot的功能.
在PostgreSQL的資料型態中,還有另一個key-value的hstore.屬於extension.
我們來看一下之前使用array存放tag的例子(傳送門),來做tag統計並轉換.

測試資料與前期準備

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 的聚合方式

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 展開的方式

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
hs1,hs2 擇一即可.

結語

可以看到跟json的方式類似.這樣就有了一個套路(pattern),聚合成key-value 結構,再展開為指定的型態.
在實務上,我們不會將所有的tag都顯示,因為tag可能會非常多,這時候可以只取出前N種來顯示.