Try   HackMD

PostgreSQL 動態產生欄位並使用 JSON 做 pivot 功能

前言

ptt database版有一個討論,傳送門

分析

這是常見的直轉橫 pivot 功能.但是這裡會有一個較為不方便的地方,就是需要產生如a1, a2, a3 z1, z2, z3,欄位非常多.
一一列舉手動產生,會很無聊,也容易出錯,眼殘就少了,或是重複.
實務上比較常見的是月份之類的,12個比較容易做.
另外 PostgreSQL 要做 pivot 需要使用tablefunc 可以參考文件
https://www.postgresql.org/docs/current/tablefunc.html
裡面也是需要列舉,可以看到使用月份的例子.

產生動態欄位

這裡需要使用一些技巧來組合,為了方便起見,不要佔了寬度,我把欄位減少了.若是要比較長的,可以搭配使用兩個generate_series及ascii code方式來產生.在此不贅述.

-- 建立12個int欄位的 temp table DO LANGUAGE plpgsql $$ DECLARE v_sqlstring VARCHAR = ''; BEGIN v_sqlstring := concat('create temp table tmpxt231121e as select ', (select string_agg(concat('NULL::int AS ', ch, n::text), ' ,' order by ch, n) from (values ('a'),('b'),('c'),('d')) as a(ch) , generate_series(1,3) as n)::text); EXECUTE(v_sqlstring); END $$;

產生之後的結構

# \d tmpxt231121e Table "pg_temp_3.tmpxt231121e" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a1 | integer | | | a2 | integer | | | a3 | integer | | | b1 | integer | | | b2 | integer | | | b3 | integer | | | c1 | integer | | | c2 | integer | | | c3 | integer | | | d1 | integer | | | d2 | integer | | | d3 | integer | | |

簡單的統計結果

with t1 as ( select string_to_table(txt, ',') as val from t231121e ) select val, count(*) as cnt from t1 group by val; val | cnt -----+----- a1 | 1 b1 | 2 d3 | 1 c1 | 2 a2 | 2 (5 rows)

在之前產生密碼那篇裡面有使用string_to_table()函數,也有講到使用unnest()的方式,在此只使用一種.

這種逗號分隔的方式會很多應用嗎?

先不論資料庫是否有支持array,或是這樣好不好.使用逗號來分隔建立類似tag的方式,也是蠻多的.有存在這樣型態,工程師就需要面對與處理.

使用JSON的方式聚合

承接上面的處理,再聚合起來.

with t1 as ( select string_to_table(txt, ',') as val from t231121e ), t2 as ( select val, count(*) as cnt from t1 group by val ) select json_object_agg(val, cnt) as vals from t2; vals ------------------------------------------------------ { "a1" : 1, "b1" : 2, "d3" : 1, "c1" : 2, "a2" : 2 } (1 row)

在一些應用情境下,把JSON傳出去,讓前端來處理,其實這樣就很方便了.

但是

一些情況下需要配合一些公司既有的工具或是一些習慣,所以往往需要做pivot,這樣符合一般的閱讀習慣.
前面產生的動態table,同時會在PostgreSQL裡面建立type.
我們可以利用那個type.

with t1 as ( select string_to_table(txt, ',') as val from t231121e ), t2 as ( select val, count(*) as cnt from t1 group by val ), t3 as ( select json_object_agg(val, cnt) as vals from t2 ) select (json_populate_recordset(null::tmpxt231121e, json_build_array(vals))).* from t3; a1 | a2 | a3 | b1 | b2 | b3 | c1 | c2 | c3 | d1 | d2 | d3 ----+----+------+----+------+------+----+------+------+------+------+---- 1 | 2 | NULL | 2 | NULL | NULL | 2 | NULL | NULL | NULL | NULL | 1 (1 row)

這樣我們就做到了不使用pivot,但是有了pivot的方式.

結語

PostgreSQL 提供了許多強大的函數及功能,靈活搭配使用,可以讓我們在處理資料有更多有彈性的方法.