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 tableDO LANGUAGE plpgsql $$DECLARE v_sqlstring VARCHAR  = '';BEGINv_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), t4 as (select json_populate_recordset(null::tmpxt231121e, json_build_array(vals)) as jspr  from t3)select (ROW((jspr).*)::tmpxt231121e).*  from t4; 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 提供了许多强大的函数及功能,灵活搭配使用,可以让我们在处理资料有更多有弹性的方法.