postgresql使用filter进行多维度聚合

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说postgresql使用filter进行多维度聚合,希望您对编程的造诣更进一步.

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

初始化表数据


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年，所以~

  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

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6032.html

postgresql使用filter进行多维度聚合