服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - postgresql使用filter进行多维度聚合的解决方法

postgresql使用filter进行多维度聚合的解决方法

2021-08-25 17:14funnyZpC PostgreSQL

这篇文章给大家介绍postgresql使用filter进行多维度聚合的解决方法，本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友参考下吧

你有没有碰到过有这样一种场景，就是我们需要看一下某个时间段内各种维度的汇总，比如这样：最近三年我们卖了多少货？有多少订单？平均交易价格多少？每个店铺卖了多少？交易成功的订单有多少？交易失败的订单有多少？ 等等...，假使这些数据的明细都在一个表内，该这么做呢？ 有没有简单方式？还有如何减少全表扫描以更改的拿到数据?

如果只是简单的利用聚合拿到数据可能您需要写很多sql，具体表现为每一个问题写一段sql 相互之间join起来，这样也许是个好主意，不过对于未充分优化的数据库系统，针对每一块的问题求解可能就是一个巨大的表扫描，当然还有一个问题就是重复的where条件，所以能不能把相同的where条件抽取出来以简化sql呢？让我们思考一下，也许有这样的解决办法~ （结论是有，当然有，哈哈哈~）

首先我提供下基本的表结构及测试数据

基本表结构

?
1
2
3
4
5
6
7
8
9
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", -- 订单状态
  "payment" numeric(18,2), -- 交易支付金额
  "product" varchar(100) COLLATE "pg_catalog"."default" -- 产品名称
  );

初始化表数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217794', '16135476150276171', '店铺2', '2019-07-01', '交易失败', '139.00', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217761', '16132502190562224', '店铺2', '2020-05-01', '交易成功', '9.90', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217795', '16122384743927326', '店铺3', '2019-06-01', '交易失败', '357.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217796', '16138945194036971', '店铺2', '2019-05-01', '交易中', '59.90', '某某单品');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217797', '16131909251901209', '店铺1', '2019-04-01', '交易失败', '359.00', '某某赠品');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217798', '16135391935074761', '店铺2', '2019-03-01', '交易失败', '139.00', '某某单品01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217762', '16132472268456370', '店铺3', '2020-04-01', '交易成功', '79.00', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217763', '16122960304700879', '店铺2', '2020-03-01', '交易成功', '357.00', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217764', '16139491271154103', '店铺1', '2020-02-01', '交易成功', '139.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217765', '16122930818314343', '店铺2', '2020-01-01', '交易成功', '79.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217766', '12581133644786193', '店铺3', '2019-12-01', '交易成功', '79.00', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217767', '16122904539659361', '店铺2', '2019-11-01', '交易成功', '359.00', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217752', '16136227870425525', '店铺1', '2021-02-01', '交易成功', '4.90', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217753', '16139781339192958', '店铺2', '2021-01-01', '交易失败', '89.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217754', '16136217317281545', '店铺3', '2020-12-01', '交易中', '6.90', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217756', '16123091065663616', '店铺1', '2020-10-01', '交易失败', '95.00', '某某单品01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217757', '16123013684517817', '店铺2', '2020-09-01', '交易中', '79.00', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217758', '16139678011781848', '店铺3', '2020-08-01', '交易中', '59.90', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217759', '16139576187535157', '店铺2', '2020-07-01', '交易成功', '9.90', '某某单品04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217791', '16132066938478413', '店铺4', '2019-10-01', '交易成功', '359.00', '某某单品05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217792', '12589185047405699', '店铺5', '2019-09-01', '交易成功', '6.90', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217760', '16139601047542860', '店铺1', '2020-06-01', '交易成功', '359.00', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217837', '16138184483906283', '店铺4', '2021-03-04', '交易成功', '359.00', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217838', '16134581997874325', '店铺5', '2021-03-04', '交易成功', '299.00', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217839', '16131099658443817', '店铺3', '2021-03-04', '交易成功', '9.90', '某某单品04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217840', '16131081649792689', '店铺2', '2021-03-04', '交易成功', '15.89', '某某单品05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217841', '16131087729266410', '店铺1', '2021-03-04', '交易成功', '49.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217842', '16138126191679446', '店铺2', '2021-03-04', '交易成功', '6.90', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217843', '16138166422967430', '店铺3', '2021-03-04', '交易成功', '579.00', '某某单品');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217844', '16121412752067761', '店铺2', '2021-03-04', '交易成功', '359.00', '某某赠品');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217845', '12580980977280299', '店铺3', '2021-03-04', '交易成功', '359.00', '某某单品01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217799', '16135358470437562', '店铺2', '2019-02-01', '交易成功', '339.00', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217800', '16135320673129243', '店铺1', '2019-01-01', '交易成功', '299.00', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217801', '16131874317933316', '店铺2', '2021-03-04', '交易失败', '359.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217802', '16131792695743424', '店铺3', '2021-03-04', '交易中', '79.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217803', '16122278134767414', '店铺2', '2021-03-04', '交易失败', '99.00', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217804', '16131790093817033', '店铺3', '2021-03-04', '交易成功', '15.89', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217805', '16135230297238674', '店铺2', '2021-03-04', '交易成功', '247.81', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217806', '16135220588746073', '店铺1', '2021-03-04', '交易成功', '25.79', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217831', '16131159355051065', '店铺3', '2021-03-04', '交易成功', '359.00', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217832', '16131196017949185', '店铺2', '2021-03-04', '交易成功', '4.90', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217833', '16131207902538323', '店铺1', '2021-03-04', '交易成功', '339.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217834', '12580998687179491', '店铺2', '2021-03-04', '交易成功', '15.89', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217835', '16138210374123403', '店铺3', '2021-03-04', '交易成功', '189.00', '某某单品11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217836', '16138242030068870', '店铺2', '2021-03-04', '交易成功', '39.90', '某某单品01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217846', '16134490408511254', '店铺3', '2021-03-04', '交易成功', '238.00', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217847', '16134370276544509', '店铺2', '2021-03-04', '交易成功', '100.00', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217854', '16121202131801564', '店铺1', '2021-03-04', '交易成功', '359.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217855', '16121178732153257', '店铺2', '2021-03-04', '交易成功', '499.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217856', '16130716264223504', '店铺3', '2021-03-04', '交易成功', '9.81', '某某单品11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217857', '16130734211002184', '店铺2', '2021-03-04', '交易成功', '9.90', '某某单品01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217858', '16134100289526412', '店铺5', '2021-03-04', '交易成功', '359.00', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217859', '16134103486626066', '店铺3', '2021-03-04', '交易成功', '189.00', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217860', '16121142702989101', '店铺2', '2021-03-04', '交易成功', '259.00', '某某单品04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217861', '16137767910421049', '店铺1', '2021-03-04', '交易成功', '299.00', '某某单品05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217862', '16121018164688502', '店铺5', '2021-03-04', '交易成功', '299.00', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217887', '16120248152353139', '店铺3', '2021-03-04', '交易成功', '9.90', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217888', '16136951424489400', '店铺2', '2021-06-07', '交易成功', '9.90', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217889', '16136924750406856', '店铺1', '2021-05-07', '交易成功', '6.90', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217916', '16119522769335722', '店铺2', '2021-02-07', '交易中', '6.90', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217917', '12588728512745597', '店铺1', '2021-01-07', '交易成功', '89.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217848', '16138039330168579', '店铺2', '2021-03-04', '交易成功', '314.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217849', '16130922810196821', '店铺3', '2021-03-04', '交易失败', '199.00', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217890', '16136941319549862', '店铺2', '2021-04-07', '交易成功', '79.00', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217793', '16135470341712568', '店铺1', '2019-08-01', '交易成功', '180.00', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217755', '16132741910343927', '店铺2', '2020-11-01', '交易成功', '6.90', '某某单品11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217807', '16138852921447547', '店铺2', '2021-03-04', '交易成功', '238.00', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217891', '16133225738639350', '店铺1', '2021-03-07', '交易失败', '49.00', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217850', '12591040185524596', '店铺2', '2021-03-04', '交易中', '6.90', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217851', '16130856267945884', '店铺3', '2021-03-04', '交易成功', '299.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217852', '16121205784010168', '店铺2', '2021-03-04', '交易失败', '19.70', '某某单品11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217853', '16137863356208213', '店铺1', '2021-03-04', '交易中', '19.70', '某某单品01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217958', '12588659047949994', '店铺2', '2019-08-07', '交易成功', '9.90', '某某单品11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217959', '16117515001200723', '店铺3', '2019-07-07', '交易成功', '99.00', '某某单品01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217960', '16126968285988680', '店铺2', '2019-06-07', '交易成功', '6.90', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217985', '12588376827205292', '店铺3', '2019-05-07', '交易成功', '337.00', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217986', '12588344485529392', '店铺2', '2019-04-07', '交易成功', '139.00', '某某单品04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217987', '16125503474522303', '店铺1', '2021-03-04', '交易失败', '9.81', '某某单品05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217988', '16129065212801070', '店铺2', '2021-03-04', '交易中', '359.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217989', '16125466354777343', '店铺3', '2021-03-04', '交易中', '49.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217918', '16136147162483080', '店铺2', '2020-12-07', '交易成功', '6.90', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217919', '12580777996543594', '店铺3', '2020-11-07', '交易成功', '299.00', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217926', '16135916055519587', '店铺2', '2020-04-07', '交易成功', '359.00', '某某单品04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217927', '16128748461350415', '店铺3', '2020-03-07', '交易成功', '9.90', '某某单品05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217952', '16130772755076508', '店铺2', '2020-02-07', '交易成功', '139.00', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217953', '16130750443205377', '店铺4', '2020-01-07', '交易成功', '4.90', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217954', '16117587731623017', '店铺5', '2019-12-07', '交易成功', '4.90', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217955', '16127065063959102', '店铺3', '2019-11-07', '交易成功', '69.00', '某某单品02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217920', '16128970251579383', '店铺2', '2020-10-07', '交易成功', '90.00', '某某单品03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217921', '16128964832564531', '店铺2', '2020-09-07', '交易成功', '175.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217922', '16135999993916188', '店铺3', '2020-08-07', '交易成功', '139.00', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217923', '16136051439214988', '店铺2', '2020-07-07', '交易成功', '9.90', '某某单品06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217924', '16119347018161682', '店铺5', '2020-06-07', '交易成功', '9.90', '某某单品07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217925', '16132344851576556', '店铺3', '2020-05-07', '交易成功', '9.90', '某某单品08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217956', '16130631650814848', '店铺2', '2019-10-07', '交易成功', '79.00', '某某礼盒');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217957', '16130549587928221', '店铺1', '2019-09-07', '交易成功', '6.90', '某某套装');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217990', '12590493961403993', '店铺2', '2021-03-04', '交易成功', '129.00', '某某单品');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217991', '16115933800269974', '店铺1', '2021-03-04', '交易成功', '79.00', '某某赠品');

准备个问题

这里我找几个基本的问题，比如： 1.我们要找最近两年（2019、2020）有多少笔交易？+ 2.交易成功的平均价格多少？ + 3.交易成功的订单有多少？ + 4.店铺1、2、3分别卖了多少？

使用filter前

对于以上同类多维度数据求解这里推荐filter,可能熟悉同学大概会记得有这么个用法，不过我们还是简单的思考下：
如果我们将条件筛选放在一个查询里面(不含子查询及表连接) , 这样会在末尾where条件内放置公共条件, 随后我们使用filter对每个结果进行特定的筛选，也许就好了
OK，来尝试使用filter解决以下问题： 找最近两年（2019、2020）有多少笔交易？

问题求解

我们上面抛出了个问题： 找最近两年（2019、2020）有多少笔交易？
很显然这个结果集框定的范围是2019年和2020年 ，所以~

?
1
2
3
4
5
6
7
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：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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多维度聚合内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/funnyzpc/archive/2021/07/13/15000589.html

  • PostgreSQL
  • Filter
  • 多维度聚合

    • 延伸 · 阅读

    精彩推荐
    • PostgreSQLPostgreSQL标准建表语句分享

      PostgreSQL标准建表语句分享

      这篇文章主要介绍了PostgreSQL标准建表语句分享，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      码上得天下7962021-02-27
    • PostgreSQLpostgresql 中的to_char()常用操作

      postgresql 中的to_char()常用操作

      这篇文章主要介绍了postgresql 中的to_char()常用操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      J符离13432021-04-12
    • PostgreSQL分布式 PostgreSQL之Citus 架构

      分布式 PostgreSQL之Citus 架构

      节点 Citus 是一种 PostgreSQL 扩展，它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群，允许 PostgreSQL 保存比单...

      未知802023-05-07
    • PostgreSQLPostgresql查询效率计算初探

      Postgresql查询效率计算初探

      这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价...

      轨迹4622020-05-03
    • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

      深入理解PostgreSQL的MVCC并发处理方式

      这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

      PostgreSQL教程网3622020-04-25
    • PostgreSQLpostgresql 数据库中的数据转换

      postgresql 数据库中的数据转换

      postgres8.3以后，字段数据之间的默认转换取消了。如果需要进行数据变换的话，在postgresql数据库中，我们可以用"::"来进行字段数据的类型转换。...

      postgresql教程网12482021-10-08
    • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

      RDS PostgreSQL一键大版本升级技术解密

      一、PostgreSQL行业位置 （一）行业位置 在讨论PostgreSQL（下面简称为PG）在整个数据库行业的位置之前，我们先看一下阿里云数据库在全球的数据库行业里的...

      未知1192023-05-07
    • PostgreSQLPostgresql开启远程访问的步骤全纪录

      Postgresql开启远程访问的步骤全纪录

      postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

      我勒个去6812020-04-30
    最近更新
    编辑推荐
    阅读排行
    热门标签
    5432 　 postgresql命令 　 数据库教程 　 入门命令 　 死锁进程 　 PostgreSQL教程 　 表继承 　 事物隔离 　 性能提升技巧 　 PL/pgSQL 　 过程语言 　 partitioning 　 PostgreSQL安装教程 　 跨库访问 　 postgresql数据库 　 并发处理 　 MVCC 　 Citus 　 OID 　 XID 　 表空间大小 　 Postgre 　 phpPgAdmin 　 phpPgAdmin错误 　 phpPgAdmin问题 　 phpPgAdmin配置文件 　 app应用 　 PostgreSQL配置 　 Postgresql下载 　 PostgreSQL入门教程 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图