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