直接統計的傳統方式
測試資料
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(q