postgresql高階應用之行轉列&彙總求和

funnyZpC發表於2021-05-05
SQL

postgresql高階應用之行轉列&彙總求和

轉載請註名出處 https://www.cnblogs.com/funnyzpc/p/14732165.html

前言

節前公司業務方需要做一個統計報表，這個報表用於統計當月估計幾個明星品的銷售情況，而我們的資料是按行儲存的就是日期|產品|渠道|銷售額這樣，說是也奇了怪了，我們買的報(guan)表(yuan)系(la)統(ji) 竟然不能容易地實現。。。，於是我看了看，然後想了想，發現是可以通過sql算出這樣一個報表(多虧了postgresql的高階函式?)，然後直接將資料輸出到報表系統 完事兒~ ，以下 我將sql關鍵部分描述下，至於對前端展示有興趣的同學可留言，可考慮作一節講講哈?~

報表

首先，業務需要的報表長這樣子的，看起來似乎還OK哈~

接下來我先給出我的測試指令碼（均測試&無bug）~

表結構

drop table if EXISTS  report1 ;
CREATE TABLE "report1" (
  "id" numeric(22) NOT NULL,
  "date" date NOT NULL,
  "product" varchar(100),
  "channel" varchar(100),
  "amount" numeric(20,4)
);

表註釋

欄位 描述
id 主鍵
date 日期
product 產品
channel 渠道
amount 銷售額

表資料

INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051726328010100000', '2021-05-04', '產品1', '京東', '8899.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051726328010100001', '2021-05-04', '產品2', '京東', '99.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100010', '2021-05-04', '產品1', '天貓', '230.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100011', '2021-05-04', '產品2', '天貓', '9.9000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100011', '2021-05-04', '產品3', '線下門店', '10.1000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100000', '2021-05-04', '產品1', '其它', '10');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100099', '2021-05-04', '產品2', '其它', '20000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100033', '2021-05-01', '產品1', '其它', '20000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100044', '2021-05-01', '產品3', '線下門店', '12345');

思考

如果你看到這裡請稍稍思考下，一開篇我説過我們的資料是按 日期|產品|渠道|銷售額 這樣按行儲存的，以上截圖大家一看就懂，然後再看看開篇的報表截圖，我想大家可以同我一樣可以分析出以下幾點：

  • 報表縱向看大致分三部分

    • 一部分是前一日產品銷售明細
    • 然後一部分是前一日產品渠道產品合計
    • 最後一部分是按渠道做的月統計

  • 報表橫向看大致分兩部分

    • 上半部分是渠道明細及合計(日和月)
    • 最後一部分則是所有渠道的產品合計、日合計、月合計

好了，問題來了，如何做呢，我是這麼想的：首先要很清楚的是你的sql大致分兩大部分(兩個子查詢)

  • 一部分是前一日的資料
  • 另一部分則是月份匯總資料

最後需要將兩部分資料做聯表查詢，這樣太贊了，似乎完成了報表的80%，至於最後一行的求總，這裡先賣個關子哈~

第一部分資料(前一日的資料)

  • 我想我們立馬能做的第一部分sql恐怕就是行專列吧(似乎這是最容易實現的?)
select
  channel,
  sum(case product when '產品1' then amount end) as c1,
  sum(case product when '產品2' then amount end) as c2,
  sum(case product when '產品3' then amount end) as c3
from report1
group by channel ;

sql似乎沒什麼問題，但是我們少了一列，對那就是按渠道日合計，當然如果您對postgresql視窗函式熟悉的話，這裡實現的方式估計你已經猜到了(視窗over函式),上sql...

select
  channel,
  day_sum,
  sum(case product when '產品1' then amount end) as c1,
  sum(case product when '產品2' then amount end) as c2,
  sum(case product when '產品3' then amount end) as c3
from
  ( select *,sum(amount) over (partition by channel) as day_sum from report1  where date=to_date('2021-05-04','yyyy-MM-dd') ) as t1
group by t1.channel ,t1.day_sum;

哈哈，上圖的day_sum估計大家很熟悉了吧，哈哈哈~
看來已經成功地完成了日資料部分，這裡可能的難點可能就兩點

  • 一是使用聚合函式(sum)+分組(group by)做行專列(當然postgresql也有其他很好用的行專列擴充套件，這裡就不介紹啦~)
  • 另一個是使用視窗函式(over)對明細提前做 按渠道的視窗匯總，這樣渠道日合計(行）的資料就有啦~

想想是不是很容易?，接下來我們看看第二部分資料怎麼獲取~

第二部分資料(月份匯總資料)

月份匯總的資料看似簡單的可怕，如果您熟練掌握postgresql中的日期處理的話估計分分鐘就能搞定，這裡就不耍大刀了，直接放出sql，哈哈哈?

 select 
  channel,sum(amount) as month_sum from report1 
where 
  date>=date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd'))) and date < date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd')) + '1 month') 
group by  
  channel

報表資料最終求解

現在，我們將求解的兩部分資料按渠道channel欄位做inner join合併以上兩部分資料，合併後的資料大致是這樣子的

這個是sql

 select
    ttt.channel,
    sum(ttt.day_sum) as day_sum,
    sum(ttt.month_sum) as month_sum,
    sum(ttt.c1) as c1,
    sum(ttt.c2) as c2,
    sum(ttt.c3) as c3
from (
        select tt1.*,tt2.month_sum from
        (
        select
        channel,
      day_sum,
        sum(case product when '產品1' then amount end) as c1,
        sum(case product when '產品2' then amount end) as c2,
        sum(case product when '產品3' then amount end) as c3
        from
        ( select *,sum(amount) over (partition by channel) as day_sum from report1  where date=to_date('2021-05-04','yyyy-MM-dd') ) as t1
        group by t1.channel ,t1.day_sum
        ) as tt1 left join
        (
          select channel,sum(amount) as month_sum from report1 where date>=date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd'))) and date < date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd')) + '1 month') group by  channel
        ) as tt2 on tt1.channel = tt2.channel
    ) ttt
GROUP BY ttt.channel
order by channel asc

看，匯總的資料已經有了,已經可以算作是最終結果了（如果你需要報表系統來計算匯總行資料的話），當然 ，我們的報表系統過於繁瑣(不是不能做，而是太麻煩)，需要你將做好的菜喂給它吃，這時，該怎麼辦呢。。。，哈哈哈 我們似乎忘記了很久不用的rollup函式(一開始我也沒發現有這麼個函式哈哈)，試試看吧

 select
    ttt.channel,
    sum(ttt.day_sum) as day_sum,
    sum(ttt.month_sum) as month_sum,
    sum(ttt.c1) as c1,
    sum(ttt.c2) as c2,
    sum(ttt.c3) as c3
    from (
        select tt1.*,tt2.month_sum from
        (
        select
        channel,
      day_sum,
        sum(case product when '產品1' then amount end) as c1,
        sum(case product when '產品2' then amount end) as c2,
        sum(case product when '產品3' then amount end) as c3
        from
        ( select *,sum(amount) over (partition by channel) as day_sum from report1  where date=to_date('2021-05-04','yyyy-MM-dd') ) as t1
        group by t1.channel ,t1.day_sum
        ) as tt1 left join
        (
          select channel,sum(amount) as month_sum from report1 where date>=date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd'))) and date < date(date_trunc('month',to_date('2021-05-04','yyyy-MM-dd')) + '1 month') group by  channel
        ) as tt2 on tt1.channel = tt2.channel
    ) ttt
    group by rollup(ttt.channel)
    order by channel asc

數是對的，意味著我們成功了~?

總結

如果您肯下功夫學，postgresql世界有很多精彩的東西，當然也有一些東西對比mysql顯得繁瑣些，不過本著學習的心態，我們縂能剋服這些，同時我們還是能做出超出我們自身能力範疇的東西的，哈哈，各位加油哦~

下章，我將講一講如何實現通過sql實現前端合併單元格的效果，是不是很神奇(我保證你全網搜不到), 希望不翻車，哈哈哈~

相關文章