Try   HackMD

PostgreSQL 直轉橫統計 一些方式的探討

前言

在之前的一些分享中,有一些關於直轉橫的方式,也有固定時段與不固定時段的統計方式.
這篇會對直轉橫統計及統計後直轉橫做一些探討.

直接統計的傳統方式

測試資料

create table t231122a ( id int not null generated always as identity primary key , gal text , dt date , qty int ); insert into t231122a (gal, dt, qty) select gal , date '2023-10-31' + interval '1 day' * n , floor(random() * 100) from generate_series(1, 4) n , (values ('水卜櫻'), ('石川澪'), ('七沢米亞')) a(gal); -- 第二次 insert into t231122a (gal, dt, qty) select gal , date '2023-10-31' + interval '1 day' * n , floor(random() * 100) from generate_series(1, 4) n , (values ('水卜櫻'), ('石川澪'), ('七沢米亞')) a(gal); select * from t231122a; id | gal | dt | qty ----+----------+------------+----- 1 | 水卜櫻 | 2023-11-01 | 83 2 | 石川澪 | 2023-11-01 | 49 3 | 七沢米亞 | 2023-11-01 | 46 4 | 水卜櫻 | 2023-11-02 | 84 5 | 石川澪 | 2023-11-02 | 81 6 | 七沢米亞 | 2023-11-02 | 48 7 | 水卜櫻 | 2023-11-03 | 25 8 | 石川澪 | 2023-11-03 | 86 9 | 七沢米亞 | 2023-11-03 | 60 10 | 水卜櫻 | 2023-11-04 | 27 11 | 石川澪 | 2023-11-04 | 92 12 | 七沢米亞 | 2023-11-04 | 37 13 | 水卜櫻 | 2023-11-01 | 62 14 | 石川澪 | 2023-11-01 | 76 15 | 七沢米亞 | 2023-11-01 | 65 16 | 水卜櫻 | 2023-11-02 | 82 17 | 石川澪 | 2023-11-02 | 61 18 | 七沢米亞 | 2023-11-02 | 58 19 | 水卜櫻 | 2023-11-03 | 47 20 | 石川澪 | 2023-11-03 | 80 21 | 七沢米亞 | 2023-11-03 | 63 22 | 水卜櫻 | 2023-11-04 | 8 23 | 石川澪 | 2023-11-04 | 61 24 | 七沢米亞 | 2023-11-04 | 21 (24 rows)

使用 case 的方式

select gal , sum(case when dt = date '2023-11-01' then qty else 0 end) as d1 , sum(case when dt = date '2023-11-02' then qty else 0 end) as d2 , sum(case when dt = date '2023-11-03' then qty else 0 end) as d3 , sum(case when dt = date '2023-11-04' then qty else 0 end) as d4 from t231122a group by gal; gal | d1 | d2 | d3 | d4 ----------+-----+-----+-----+----- 水卜櫻 | 145 | 166 | 72 | 35 石川澪 | 125 | 142 | 166 | 153 七沢米亞 | 111 | 106 | 123 | 58 (3 rows)

case方式,大多數主流資料庫都有支援.缺點是繁瑣,語法寫起來不方便.
時常會看到一些寫的很長的,典型的是12個月份的.

filter 子句的方式

select gal , sum(qty) filter (where dt = date '2023-11-01') as d1 , sum(qty) filter (where dt = date '2023-11-02') as d2 , sum(qty) filter (where dt = date '2023-11-03') as d3 , sum(qty) filter (where dt = date '2023-11-04') as d4 from t231122a group by gal; gal | d1 | d2 | d3 | d4 ----------+-----+-----+-----+----- 水卜櫻 | 145 | 166 | 72 | 35 石川澪 | 125 | 142 | 166 | 153 七沢米亞 | 111 | 106 | 123 | 58 (3 rows)

filter 是PostgreSQL特有的,語法會比case 方便清晰.
不只是sum()之類的可以使用.

select gal , array_agg(qty) filter (where dt = date '2023-11-01') as d1 , array_agg(qty) filter (where dt = date '2023-11-02') as d2 , array_agg(qty) filter (where dt = date '2023-11-03') as d3 , array_agg(qty) filter (where dt = date '2023-11-04') as d4 from t231122a group by gal; gal | d1 | d2 | d3 | d4 ----------+---------+---------+---------+--------- 水卜櫻 | {83,62} | {84,82} | {25,47} | {27,8} 石川澪 | {49,76} | {81,61} | {86,80} | {92,61} 七沢米亞 | {46,65} | {48,58} | {60,63} | {37,21}

這樣在做發展的過程中,可以方便對照,確認數字的正確性.

統計後再直轉橫

輔助表的方式

可以透過輔助表join之後取得結果,在我一些分享中有介紹過,在此就不贅述.

pivot 指令或是 tablefunc

Oracle / SQL Server 有 pivot
PostgreSQL 使用 tablefunc
https://www.postgresql.org/docs/current/tablefunc.html

聚合成 json / hstore 再展開

這兩種方式,我有分享,請參考傳送門

聚合後再展開方式的延伸探討

在常見的應用中,除了之前有提到的12個月分,還有一個月份,或是一段日期也許是20天或是45天等等.像是商品的每日銷售額,或是議題的點擊量.
在之前的分享中,是以tag為例,但是因為日期是數字開頭,這樣不能建立欄位名稱,需要變通一下.所以就用來舉例,順帶把方式變通方式一併介紹.

使用array 聚合

with t1 as ( select gal, dt, sum(qty) as qtys from t231122a group by gal, dt ) select gal , array_agg(dt order by dt) as dtarr , array_agg(qtys order by dt) as qtyarr from t1 group by gal; gal | dtarr | qtyarr ----------+-----------------------------------------------+------------------- 水卜櫻 | {2023-11-01,2023-11-02,2023-11-03,2023-11-04} | {145,166,72,35} 石川澪 | {2023-11-01,2023-11-02,2023-11-03,2023-11-04} | {125,142,166,153} 七沢米亞 | {2023-11-01,2023-11-02,2023-11-03,2023-11-04} | {111,106,123,58} (3 rows)

array 再轉為 hstore

with t1 as ( select gal, dt, sum(qty) as qtys from t231122a group by gal, dt ), t2 as ( select gal , array_agg(dt order by dt) as dtarr , array_agg(qtys order by dt) as qtyarr from t1 group by gal ) select gal , hstore(dtarr::text[], qtyarr::text[]) as hs1 from t2; -[ RECORD 1 ]--------------------------------------------------------------------------- gal | 水卜櫻 hs1 | "2023-11-01"=>"145", "2023-11-02"=>"166", "2023-11-03"=>"72", "2023-11-04"=>"35" -[ RECORD 2 ]--------------------------------------------------------------------------- gal | 石川澪 hs1 | "2023-11-01"=>"125", "2023-11-02"=>"142", "2023-11-03"=>"166", "2023-11-04"=>"153" -[ RECORD 3 ]--------------------------------------------------------------------------- gal | 七沢米亞 hs1 | "2023-11-01"=>"111", "2023-11-02"=>"106", "2023-11-03"=>"123", "2023-11-04"=>"58"

將日期格式變化

with t1 as ( select gal, dt, sum(qty) as qtys from t231122a group by gal, dt ), t2 as ( select gal , array_agg('d'|| to_char(dt, 'mmDD') order by dt) as dtarr , array_agg(qtys::text order by dt) as qtyarr from t1 group by gal ) select gal , hstore(dtarr, qtyarr) as hs1 from t2; gal | hs1 ----------+---------------------------------------------------------------- 水卜櫻 | "d1101"=>"145", "d1102"=>"166", "d1103"=>"72", "d1104"=>"35" 石川澪 | "d1101"=>"125", "d1102"=>"142", "d1103"=>"166", "d1104"=>"153" 七沢米亞 | "d1101"=>"111", "d1102"=>"106", "d1103"=>"123", "d1104"=>"58" (3 rows)

使用hsotre展開

DO LANGUAGE plpgsql $$ DECLARE v_sqlstring TEXT = ''; BEGIN v_sqlstring := concat('create temp table tmpxt231122b as select ', (select string_agg(concat('NULL::int AS d', to_char(dt, 'mmDD')), ' ,' order by dt) from generate_series(date '2023-11-01' , date '2023-11-04', interval '1 day') dt )); EXECUTE(v_sqlstring); END $$; --- # \d tmpxt231122b Table "pg_temp_3.tmpxt231122b" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- d1101 | integer | | | d1102 | integer | | | d1103 | integer | | | d1104 | integer | | | with t1 as ( select gal, dt, sum(qty) as qtys from t231122a group by gal, dt ), t2 as ( select gal , array_agg('d'|| to_char(dt, 'mmDD') order by dt) as dtarr , array_agg(qtys order by dt) as qtyarr from t1 group by gal ), t3 as ( select gal , hstore(dtarr, qtyarr::text[]) as hs1 from t2 ) select gal , (populate_record(null::tmpxt231122b, hs1)).* from t3; gal | d1101 | d1102 | d1103 | d1104 ----------+-------+-------+-------+------- 水卜櫻 | 145 | 166 | 72 | 35 石川澪 | 125 | 142 | 166 | 153 七沢米亞 | 111 | 106 | 123 | 58 (3 rows)

統計聚合後先存起來

在前面的例子中,已經把日期的方式做了展示,array當基礎,可以轉為hstore,需要時再做展開.
因為統計需要消耗計算資源與時間,加以做統計時紀錄表可以正在做寫入等動作,所以我們可以先將統計資料存起來,避免多次存取.

create table t231122c ( id int not null generated always as identity primary key , gal text , qtys hstore , qtyarr int[] ); with t1 as ( select gal, dt, sum(qty) as qtys from t231122a group by gal, dt ), t2 as ( select gal , array_agg('d'|| to_char(dt, 'mmDD') order by dt) as dtarr , array_agg(qtys order by dt) as qtyarr from t1 group by gal ) insert into t231122c(gal, qtys, qtyarr) select gal , hstore(dtarr, qtyarr::text[]) , qtyarr from t2; select * from t231122c; -[ RECORD 1 ]---------------------------------------------------------- id | 1 gal | 水卜櫻 qtys | "d1101"=>"145", "d1102"=>"166", "d1103"=>"72", "d1104"=>"35" qtyarr | {145,166,72,35} -[ RECORD 2 ]---------------------------------------------------------- id | 2 gal | 石川澪 qtys | "d1101"=>"125", "d1102"=>"142", "d1103"=>"166", "d1104"=>"153" qtyarr | {125,142,166,153} -[ RECORD 3 ]---------------------------------------------------------- id | 3 gal | 七沢米亞 qtys | "d1101"=>"111", "d1102"=>"106", "d1103"=>"123", "d1104"=>"58" qtyarr | {111,106,123,58}

直轉橫之後的應用

資料庫適合做大量資料的處理,但是每次都要下指令.當我們要做資料分析時,需要資料庫把資料撈出來,這時候看到資料,才能再做後續的處理,例如挑選某些日期某些類別再做加總等等.所以才會有先把資料聚合後直轉橫,再傳給試算表或是給R,Python Panda 再來做處理,像是某個日期的加總或平均,較為方便直觀.
但是試算表之類的速度較慢,若要用資料庫,可能建立如31個日期欄位,再把直轉橫的結果存看來,但是這樣要做縱向的加總平均,SQL 指令會很繁瑣.

vector 的引進

最近vector已經開始引進了.在PostgreSQL 有 pgvector 這個extension.傳送門

vector 的應用

-- 將array 轉為 vector select id , array_to_vector(qtyarr,4,true) , array_to_vector(qtyarr,4,false) , qtyarr::vector from t231122c; id | array_to_vector | array_to_vector | qtyarr ----+-------------------+-------------------+------------------- 1 | [145,166,72,35] | [145,166,72,35] | [145,166,72,35] 2 | [125,142,166,153] | [125,142,166,153] | [125,142,166,153] 3 | [111,106,123,58] | [111,106,123,58] | [111,106,123,58] (3 rows) -- 縱向加總 with t1 as ( select qtyarr::vector as v1 from t231122c ) select sum(v1) from t1; sum ------------------- [381,414,361,246] select qtyarr from t231122c; qtyarr ------------------- {145,166,72,35} {125,142,166,153} {111,106,123,58} (3 rows) -- 驗算 select 145+125+111; ?column? ---------- 381 -- 縱向平均 with t1 as ( select qtyarr::vector as v1 from t231122c ) select sum(v1) , avg(v1) from t1; sum | avg -------------------+------------------------- [381,414,361,246] | [127,138,120.333336,82] (1 row) select 381 / 3; ?column? ---------- 127

可以觀察到,我們可以透過array轉為vector,可以很方便的做縱向的加總與平均.

以array為中心

我們可以做單日加總後,存到array,同時將array轉為hstore與vector存到分析工作用的table.
需要轉為單日欄位的,可以透過前面介紹的方式,轉出來.
也可以轉為json,方便傳給其他系統.
也可以利用vector,方便做大量的縱向加總平均.這樣當我們在做分析時,
例如品項也可以帶tag,而這些tag也是利用array存放,還記得前面分享的佐山愛嗎?這樣就能方便分類計算,試算表需要一行行拉,資料庫一下就30天一起算,更不用說強大的過濾條件.
至於array橫向的計算,之前有一篇做多欄位計算,就是利用array,所以array的橫向計算是不成問題的,另外也有extension提供了一些函數,這個會另外安排分享.

結語

連續幾篇,介紹了一些統計的方法,希望這些分享對大家能有一些幫助.

感謝

感謝水卜櫻,石川澪,七沢米亞,當然還有愛醬.