postgresql使用filter进行多维度聚合的解决方法
你有没有碰到过有这样一种场景，就是我们需要看一下某个时间段内各种维度的汇总，比如这样：最近三年我们卖了多少货？有多少订单？平均交易价格多少？每个店铺卖了多少？交易成功的订单有多少？交易失败的订单有多少？ 等等...，假使这些数据的明细都在一个表内，该这么做呢？ 有没有简单方式？还有如何减少全表扫描以更改的拿到数据?
如果只是简单的利用聚合拿到数据可能您需要写很多sql，具体表现为每一个问题写一段sql 相互之间join起来，这样也许是个好主意，不过对于未充分优化的数据库系统，针对每一块的问题求解可能就是一个巨大的表扫描，当然还有一个问题就是重复的where条件，所以能不能把相同的where条件抽取出来以简化sql呢？让我们思考一下，也许有这样的解决办法~ （结论是有，当然有，哈哈哈~）
首先我提供下基本的表结构及测试数据
基本表结构
CREATE TABLE "order_info" ( "id" numeric(22) primary key , "oid" varchar(100) COLLATE "pg_catalog"."default", -- 订单号 "shop" varchar(100) COLLATE "pg_catalog"."default", -- 店铺 "date" date NOT NULL, --订单日期 "status" varchar(100) COLLATE "pg_catalog"."default", -- 订单状态 "payme
初始化表数据
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payme
准备个问题
这里我找几个基本的问题，比如： 1.我们要找最近两年（2019、2020）有多少笔交易？+ 2.交易成功的平均价格多少？ + 3.交易成功的订单有多少？ + 4.店铺1、2、3分别卖了多少？
使用filter前
对于以上同类多维度数据求解这里推荐filter,可能熟悉同学大概会记得有这么个用法，不过我们还是简单的思考下：
如果我们将条件筛选放在一个查询里面(不含子查询及表连接) , 这样会在末尾where条件内放置公共条件, 随后我们使用filter对每个结果进行特定的筛选，也许就好了
OK，来尝试使用filter解决以下问题： 找最近两年（2019、2020）有多少笔交易？
问题求解
我们上面抛出了个问题： 找最近两年（2019、2020）有多少笔交易？
很显然这个结果集框定的范围是2019年和2020年 ，所以~
select
count(1) as 交易总订单_20_and_19,
count(1) filter ( where date>=to_date('2020-01-01','yyyy-MM-dd') and date < to_date('2021-01-01','yyyy-MM-dd') ) as 交易总订单_20,
count(1) filter ( where date>=to_date('2019-01-01','yyyy-MM-dd') and date < to_date('2020-01-01','yyyy-MM-dd') ) as 交易总订单_19
from order_info
where date >= date_trunc('year',to_date('2021-07-12','yyyy-MM-dd')+interval '-2 year')::date
and date < date_trunc('year',to_date('2021-07-12','yyyy-MM-dd'))::date
运行结果：
交易总订单_20_and_19 | 交易总订单_20 | 交易总订单_19
----------------------+---------------+---------------
45 | 24 | 21
(1 row)
如果你是首次使用filter子句，这里我简单的验证下，就验证2019年多少订单吧：
select count(1) as 交易总订单_19 from order_info where date>=to_date('2019-01-01','yyyy-MM-dd') and date < to_date('2020-01-01','yyyy-MM-dd') ;
交易总订单_19
---------------
21
(1 row)
【注意，不论您筛选的上面什么范围内的数据，一定要考虑 where条件一定要框定当前所有结果集合最大的范围，不然sql运行的结果不及预计~ 】
最后，对于一开始的问题给出一个参考sql：
select
count(1) as 交易总订单_20_and_19,
count(1) filter ( where date>=to_date('2020-01-01','yyyy-MM-dd') and date < to_date('2021-01-01','yyyy-MM-dd') ) as 交易总订单_20,
count(1) filter ( where date>=to_date('2019-01-01','yyyy-MM-dd') and date < to_date('2020-01-01','yyyy-MM-dd') ) as 交易总订单_19,
avg(payment) filter (where status='交易成功' ) as 交易成功的均价,
count(1) filter (where status='交易成功' ) as 交易成功的订单数,
count(1) filter (where status!='交易成功' ) as 交易失败的订单数,
sum(payment) filter (where status='交易成功' and shop='店铺1' ) as 店铺1交易额,
sum(payment) filter (where status='交易成功' and shop='店铺2' ) as 店铺2交易额,
sum(payment) filter (where status='交易成功' and shop='店铺3' ) as 店铺3交易额
from order_info
where date >= date_trunc('year',to_date('2021-07-12','yyyy-MM-dd')+interval '-2 year')::date
and date < date_trunc('year',to_date('2021-07-12','yyyy-MM-dd'))::date
到此这篇关于postgresql使用filter进行多维度聚合的文章就介绍到这了,更多相关postgresql多维度聚合内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！
版权声明：本站文章来源标注为YINGSOO的内容版权均为本站所有，欢迎引用、转载，请保持原文完整并注明来源及原文链接。禁止复制或仿造本网站，禁止在非www.yingsoo.com所属的服务器上建立镜像，否则将依法追究法律责任。本站部分内容来源于网友推荐、互联网收集整理而来，仅供学习参考，不代表本站立场，如有内容涉嫌侵权，请联系alex-e#qq.com处理。
关注官方微信