首页 > 编程语言 > postgresql高级应用之行转列&汇总求和的实现思路
2021
06-19

postgresql高级应用之行转列&汇总求和的实现思路

前言

节前公司业务方需要做一?????蟊恚?飧霰ū碛糜谕臣频痹鹿兰萍父雒餍瞧返南?矍榭觯??颐堑氖?菔前葱写娲⒌木褪?code>日期|产品|渠道|销售额这样，说是也奇了怪了，我们买的报(guan)表(yuan)系(la)统(ji) 竟然不能容易地实现。。。，于是我看了看，然后想了想，发现是可以通过sql算出这样一个报表(多亏了postgresql的高阶函数😂)，然后直接将数据输出到报表系统 完事?? ，以下 我??ql??部分描述下，至於?η岸苏故居信d趣的同?W可留言，可考?]作一???哈😄~

报表

首先，?I?招枰??蟊黹L??幼拥模?雌?硭坪踹?OK哈~

接下?砦蚁冉o出我的?y?脚本（均?y?&?obug）~

表结构

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');

思考

如果你看到???稍稍思考下，一?篇我??我??的???前 日期|产品|渠道|销售额 ??影葱写?Φ模?陨辖?D大家一看就懂，然後再看看?篇的?蟊斫?D，我想大家可以同我一?涌梢苑治龀鲆韵??c：

  • ?蟊砜v向看大致分三部分

一部分是前一日?品?售明?
然後一部分是前一日?品渠道?品合?
最後一部分是按渠道做的月??

  • ?蟊?M向看大致分?刹糠?/li>

一部分是前一日的???br /> 另一部分?t是月份?R????/p>

最後一部分?t是所有渠道的?品合?、日合?、月合?

好了，????砹耍?绾巫瞿兀?沂沁@麽想的：首先要很清楚的是你的sql大致分?纱蟛糠????子查?)

一部分是前一日的???硪徊糠?t是月份?R????/p>

最後需要??刹糠???雎?表查?，??犹??了，似乎完成了?蟊淼?0%，至於最後一行的求?，??先????子哈~

第一部分???前一日的???

我想我??立?能做的第一部分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似乎?]什麽???，但是我??少了一列，?δ蔷褪?code>按渠道日合?，?然如果您??ostgresql窗口函?凳煜さ脑?，?????的方式估?你已?猜到了(窗口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;

哈哈，上?D的day_sum估?大家很熟悉了吧，哈哈哈~
看?硪呀?成功地完成了日???/code>部分，??可能的??可能就?牲c

  • 一是使用聚合函??sum)+分?(group by)做行?Ａ??然postgresql也有其他很好用的行?Ａ?U展，??就不介?啦~)另一??是使用窗口函??over)?γ骷?提前做 按渠道的窗口?R?，??忧?廊蘸嫌?(行）的???陀欣?

想想是不是很容易😂，接下?砦??看看第二部分???貅岖@取~

第二部分???月份?R????

月份?R?的???此坪??蔚目膳拢?绻??炀?掌握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

?蟊???罱K求解

?在，我???⑶蠼獾?刹糠???辞??code>channel字段做inner join合并以上?刹糠????喜⒑蟮???笾率沁@?幼拥?/p>

???是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

看，?R?的???呀?有了,已?可以算作是最??果了（如果你需要?蟊硐到y?碛?算?R?行???脑?），?然 ，我??的?蟊硐到y?於繁?(不是不能做，而是太麻??，需要你?⒆龊玫牟宋菇o它吃，??r，?怎麽?呢。。。，哈哈哈 我??似乎忘?了很久不用的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

?凳?Φ模?馕吨???成功了~😂

??

如果您肯下功夫?W，postgresql世界有很多精彩的?|西，?然也有一些?|西?Ρ?ysql?得繁?些，不?本著?W?的心?B，我???能?w服?些，同?r我???是能做出超出我??自身能力??的?|西的，哈哈，各位加油哦~

下章，我?⒅v一?如何???通?sql???前端合并?卧?竦男Ч??遣皇呛苌衿?我保?你全?搜不到), 希望不翻?，哈哈哈~

到此这篇关于postgresql高级应用之行转列&汇总求和的实现思路的文章就介绍到这了,更多相关postgresql行转列汇总求和内容请搜索自学编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持自学编程网！

编程技巧