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;

執行結果如下圖:

hs1,hs2 擇一即可.

結語

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