我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

postgresql使用filter进行多维度聚合

2015-05-04 20:55

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

postgresql使用filter进行多维度聚合

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

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

postgresql使用filter进行多维度聚合
阅读原文内容投诉

postgresql使用filter进行多维度聚合

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

postgresql使用filter进行多维度聚合

postgresql使用filter进行多维度聚合你有没有碰到过有这样一种场景，就是我们需要看一下某个时间段内各种维度的汇总，比如这样：最近三年我们卖了多少货？有多少订单？平均交易价格多少？每个店铺卖了多少？交易成功的订单有多少？交易失败的订单有多少？ 等等.
postgresql使用filter进行多维度聚合
2015-05-04

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

2024-04-02

如何使用 PHP 函数进行数据聚合？

php 提供了函数来进行数据聚合，包括：sum() 计算总和count() 计算数量max() 和 min() 查找最大值和最小值array_column() 从数组中提取指定列array_reduce() 应用聚合函数实战案例中，展示了计
如何使用 PHP 函数进行数据聚合？
2024-05-03

怎么使用Kubernetes进行日志管理和聚合

怎么使用Kubernetes进行日志管理和聚合
2024-04-02

PostgreSQL执行聚合函数所使用的数据结构有哪些

2024-04-02

PHP 中使用 Elasticsearch 进行数据清洗与聚合计算

概要：本文将介绍如何在 PHP 中使用 Elasticsearch 进行数据清洗和聚合计算。Elasticsearch 是一个强大而灵活的分布式搜索和分析引擎，它可以帮助我们通过对数据进行索引和查询来进行数据清洗和聚合计算。本文将通过具体的
2023-10-21

如何使用Puppeteer进行新闻网站数据抓取和聚合

导语 Puppeteer是一个基于Node.js的库，它提供了一个高级的API来控制Chrome或Chromium浏览器。通过Puppeteer，我们可以实现各种自动化任务，如网页截图、PDF生成、表单填写、网络监控等。本文将介绍如何使用
2023-08-30

pytorch 如何使用amp进行混合精度训练

2024-04-02

如何使用SQL语句在MySQL中进行数据聚合和统计？

如何使用SQL语句在MySQL中进行数据聚合和统计？在进行数据分析和统计时，数据聚合和统计是非常重要的步骤。MySQL作为一个功能强大的关系型数据库管理系统，提供了丰富的聚合和统计函数，可以很方便地进行数据聚合和统计操作。本文将介绍使用SQ
如何使用SQL语句在MySQL中进行数据聚合和统计？
2023-12-17

如何使用Java和NumPy进行多维数组操作？

2023-06-02

使用canvas怎么将二维码和图片进行合成

这期内容当中小编将会给大家带来有关使用canvas怎么将二维码和图片进行合成，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。实现思路是这样的使用jr-qrcode将url生成data:base64供img使
2023-06-09

使用Java8进行分组(多个字段的组合分组)

2024-04-02

SQL基础之使用集合运算符进行多表查询（十二）

2024-04-02

mybatis-plus在Mapper类中使用@select标签进行多表联合动态条件查询

2023-10-20

(完全解决)如何输入一个图的邻接矩阵（每两个点的亲密度矩阵affinity）,然后使用sklearn进行谱聚类

2023-10-26

MyBatis中动态sql实现传递多个参数并使用if进行参数的判断和实现like模糊搜索以及foreach实现in集合

场景在mapper接口层方法参数有id的数组和姓名的模糊搜索和类型的三个参数。现在需要将这三个参数传递在动态sql的xml中接受并进行判断不为空。然后查询数据库中记录在第一个参数id数组中的并且姓名是模糊搜索的并且类型是直接相等于的。注：博客： https:/
MyBatis中动态sql实现传递多个参数并使用if进行参数的判断和实现like模糊搜索以及foreach实现in集合
2020-04-27

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈