postgresql高級(jí)應(yīng)用之行轉(zhuǎn)列&匯總求和的實(shí)現(xiàn)思路
前言
節(jié)前公司業(yè)務(wù)方需要做一個(gè)統(tǒng)計(jì)報(bào)表，這個(gè)報(bào)表用于統(tǒng)計(jì)當(dāng)月估計(jì)幾個(gè)明星品的銷售情況，而我們的數(shù)據(jù)是按行存儲(chǔ)的就是日期|產(chǎn)品|渠道|銷售額這樣，說是也奇了怪了，我們買的報(bào)(guan)表(yuan)系(la)統(tǒng)(ji) 竟然不能容易地實(shí)現(xiàn)。。。，于是我看了看，然后想了想，發(fā)現(xiàn)是可以通過sql算出這樣一個(gè)報(bào)表(多虧了postgresql的高階函數(shù)😂)，然后直接將數(shù)據(jù)輸出到報(bào)表系統(tǒng) 完事兒~ ，以下 我將sql關(guān)鍵部分描述下，至於對(duì)前端展示有興趣的同學(xué)可留言，可考慮作一節(jié)講講哈😄~
報(bào)表
首先，業(yè)務(wù)需要的報(bào)表長(zhǎng)這樣子的，看起來似乎還OK哈~
接下來我先給出我的測(cè)試腳本（均測(cè)試&無bug）~
表結(jié)構(gòu)
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 | 產(chǎn)品 |
| channel | 渠道 |
| amount | 銷售額 |
表數(shù)據(jù)
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051726328010100000', '2021-05-04', '產(chǎn)品1', '京東', '8899.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051726328010100001', '2021-05-04', '產(chǎn)品2', '京東', '99.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100010', '2021-05-04', '產(chǎn)品1', '天貓', '230.0000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100011', '2021-05-04', '產(chǎn)品2', '天貓', '9.9000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100011', '2021-05-04', '產(chǎn)品3', '線下門店', '10.1000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100000', '2021-05-04', '產(chǎn)品1', '其它', '10');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100099', '2021-05-04', '產(chǎn)品2', '其它', '20000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100033', '2021-05-01', '產(chǎn)品1', '其它', '20000');
INSERT INTO "report1"("id", "date", "product", "channel", "amount") VALUES ('2105051727068010100044', '2021-05-01', '產(chǎn)品3', '線下門店', '12345');
思考
如果你看到這裏請(qǐng)稍稍思考下，一開篇我説過我們的數(shù)據(jù)是按 日期|產(chǎn)品|渠道|銷售額 這樣按行存儲(chǔ)的，以上截圖大家一看就懂，然後再看看開篇的報(bào)表截圖，我想大家可以同我一樣可以分析出以下幾點(diǎn)：
- 報(bào)表縱向看大致分三部分
一部分是前一日產(chǎn)品銷售明細(xì)
然後一部分是前一日產(chǎn)品渠道產(chǎn)品合計(jì)
最後一部分是按渠道做的月統(tǒng)計(jì)
- 報(bào)表橫向看大致分兩部分
一部分是前一日的數(shù)據(jù)
另一部分則是月份匯總數(shù)據(jù)
最後一部分則是所有渠道的產(chǎn)品合計(jì)、日合計(jì)、月合計(jì)
好了，問題來了，如何做呢，我是這麼想的：首先要很清楚的是你的sql大致分兩大部分(兩個(gè)子查詢)
一部分是前一日的數(shù)據(jù)另一部分則是月份匯總數(shù)據(jù)
最後需要將兩部分?jǐn)?shù)據(jù)做聯(lián)表查詢，這樣太贊了，似乎完成了報(bào)表的80%，至於最後一行的求總，這裏先賣個(gè)關(guān)子哈~
第一部分?jǐn)?shù)據(jù)(前一日的數(shù)據(jù))
我想我們立馬能做的第一部分sql恐怕就是行專列吧(似乎這是最容易實(shí)現(xiàn)的😄)
select channel, sum(case product when '產(chǎn)品1' then amount end) as c1, sum(case product when '產(chǎn)品2' then amount end) as c2, sum(case product when '產(chǎn)品3' then amount end) as c3 from report1 group by channel ;
sql似乎沒什麼問題，但是我們少了一列，對(duì)那就是按渠道日合計(jì)，當(dāng)然如果您對(duì)postgresql窗口函數(shù)熟悉的話，這裏實(shí)現(xiàn)的方式估計(jì)你已經(jīng)猜到了(窗口over函數(shù)),上sql...
select
channel,
day_sum,
sum(case product when '產(chǎn)品1' then amount end) as c1,
sum(case product when '產(chǎn)品2' then amount end) as c2,
sum(case product when '產(chǎn)品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估計(jì)大家很熟悉了吧，哈哈哈~
看來已經(jīng)成功地完成了日數(shù)據(jù)部分，這裏可能的難點(diǎn)可能就兩點(diǎn)
- 一是使用聚合函數(shù)(
sum)+分組(group by)做行專列(當(dāng)然postgresql也有其他很好用的行專列擴(kuò)展，這裏就不介紹啦~)另一個(gè)是使用窗口函數(shù)(over)對(duì)明細(xì)提前做按渠道的窗口匯總，這樣渠道日合計(jì)(行）的數(shù)據(jù)就有啦~
想想是不是很容易😂，接下來我們看看第二部分?jǐn)?shù)據(jù)怎麼獲取~
第二部分?jǐn)?shù)據(jù)(月份匯總數(shù)據(jù))
月份匯總的數(shù)據(jù)看似簡(jiǎn)單的可怕，如果您熟練掌握postgresql中的日期處理的話估計(jì)分分鐘就能搞定，這裏就不耍大刀了，直接放出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
報(bào)表數(shù)據(jù)最終求解
現(xiàn)在，我們將求解的兩部分?jǐn)?shù)據(jù)按渠道channel字段做inner join合并以上兩部分?jǐn)?shù)據(jù)，合并后的數(shù)據(jù)大致是這樣子的
這個(gè)是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 '產(chǎn)品1' then amount end) as c1,
sum(case product when '產(chǎn)品2' then amount end) as c2,
sum(case product when '產(chǎn)品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
看，匯總的數(shù)據(jù)已經(jīng)有了,已經(jīng)可以算作是最終結(jié)果了（如果你需要報(bào)表系統(tǒng)來計(jì)算匯總行數(shù)據(jù)的話），當(dāng)然 ，我們的報(bào)表系統(tǒng)過於繁瑣(不是不能做，而是太麻煩)，需要你將做好的菜喂給它吃，這時(shí)，該怎麼辦呢。。。，哈哈哈 我們似乎忘記了很久不用的rollup函數(shù)(一開始我也沒發(fā)現(xiàn)有這麼個(gè)函數(shù)哈哈)，試試看吧
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 '產(chǎn)品1' then amount end) as c1,
sum(case product when '產(chǎn)品2' then amount end) as c2,
sum(case product when '產(chǎn)品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
數(shù)是對(duì)的，意味著我們成功了~😂
總結(jié)
如果您肯下功夫?qū)W，postgresql世界有很多精彩的東西，當(dāng)然也有一些東西對(duì)比mysql顯得繁瑣些，不過本著學(xué)習(xí)的心態(tài)，我們縂能剋服這些，同時(shí)我們還是能做出超出我們自身能力範(fàn)疇的東西的，哈哈，各位加油哦~
下章，我將講一講如何實(shí)現(xiàn)通過sql實(shí)現(xiàn)前端合并單元格的效果，是不是很神奇(我保證你全網(wǎng)搜不到), 希望不翻車，哈哈哈~
到此這篇關(guān)于postgresql高級(jí)應(yīng)用之行轉(zhuǎn)列&匯總求和的實(shí)現(xiàn)思路的文章就介紹到這了,更多相關(guān)postgresql行轉(zhuǎn)列匯總求和內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Postgresql?REGEXP開頭的正則函數(shù)用法圖文詳解
正則表達(dá)式是指一個(gè)用來描述或者匹配一系列符合某個(gè)句法規(guī)則的字符串的單個(gè)字符串,下面這篇文章主要給大家介紹了關(guān)于Postgresql?REGEXP開頭的正則函數(shù)用法的相關(guān)資料,需要的朋友可以參考下2024-02-02
Postgresql 數(shù)據(jù)庫權(quán)限功能的使用總結(jié)
這篇文章主要介紹了Postgresql 數(shù)據(jù)庫權(quán)限功能的使用總結(jié)，具有很好的參考價(jià)值，對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-02-02
在一個(gè)（差）的PostgreSQL 查詢中只要一個(gè)小小到改動(dòng)(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查詢時(shí)間從20s縮減到0.2s2013-09-09
Debian中PostgreSQL數(shù)據(jù)庫安裝配置實(shí)例
這篇文章主要介紹了Debian中PostgreSQL數(shù)據(jù)庫安裝配置實(shí)例,一個(gè)簡(jiǎn)明教程,需要的朋友可以參考下2014-06-06
在pg里表分區(qū)是通過表繼承來實(shí)現(xiàn)的，一般都是建立一個(gè)主表，里面是空，然后每個(gè)分區(qū)都去繼承它。2009-09-09
盡管PostgreSQL中支持大量的數(shù)據(jù)類型，但是對(duì)于URL似乎并沒有一個(gè)相應(yīng)的類型能夠去存儲(chǔ)。那么對(duì)于URL的數(shù)據(jù)我們?cè)跀?shù)據(jù)庫中要怎么去處理呢？今天通過本文給大家介紹下，需要的朋友參考下吧2021-07-07
這篇文章主要介紹了postgresql表死鎖問題的排查方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql json取值為何這么慢,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-06-06
這篇文章主要介紹了PostgreSQL教程(九)：事物隔離介紹,本文主要針對(duì)讀已提交和可串行化事物隔離級(jí)別進(jìn)行說明和比較,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了PostgreSQL教程(十六)：系統(tǒng)視圖詳解,本文講解了pg_tables、pg_indexes、pg_views、pg_user、pg_roles、pg_rules、pg_settings等視圖的作用和字段含義等內(nèi)容,需要的朋友可以參考下2015-05-05