postgresql使用filter進(jìn)行多維度聚合的解決方法
更新時(shí)間：2021年07月15日 16:27:28 作者：funnyZpC
這篇文章給大家介紹postgresql使用filter進(jìn)行多維度聚合的解決方法，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友參考下吧
你有沒(méi)有碰到過(guò)有這樣一種場(chǎng)景，就是我們需要看一下某個(gè)時(shí)間段內(nèi)各種維度的匯總，比如這樣：最近三年我們賣了多少貨？有多少訂單？平均交易價(jià)格多少？每個(gè)店鋪賣了多少？交易成功的訂單有多少？交易失敗的訂單有多少？ 等等...，假使這些數(shù)據(jù)的明細(xì)都在一個(gè)表內(nèi)，該這么做呢？ 有沒(méi)有簡(jiǎn)單方式？還有如何減少全表掃描以更改的拿到數(shù)據(jù)?
如果只是簡(jiǎn)單的利用聚合拿到數(shù)據(jù)可能您需要寫很多sql，具體表現(xiàn)為每一個(gè)問(wèn)題寫一段sql 相互之間join起來(lái)，這樣也許是個(gè)好主意，不過(guò)對(duì)于未充分優(yōu)化的數(shù)據(jù)庫(kù)系統(tǒng)，針對(duì)每一塊的問(wèn)題求解可能就是一個(gè)巨大的表掃描，當(dāng)然還有一個(gè)問(wèn)題就是重復(fù)的where條件，所以能不能把相同的where條件抽取出來(lái)以簡(jiǎn)化sql呢？讓我們思考一下，也許有這樣的解決辦法~ （結(jié)論是有，當(dāng)然有，哈哈哈~）
首先我提供下基本的表結(jié)構(gòu)及測(cè)試數(shù)據(jù)
基本表結(jié)構(gòu)
CREATE TABLE "order_info" ( "id" numeric(22) primary key , "oid" varchar(100) COLLATE "pg_catalog"."default", -- 訂單號(hào) "shop" varchar(100) COLLATE "pg_catalog"."default", -- 店鋪 "date" date NOT NULL, --訂單日期 "status" varchar(100) COLLATE "pg_catalog"."default", -- 訂單狀態(tài) "payment" numeric(18,2), -- 交易支付金額 "product" varchar(100) COLLATE "pg_catalog"."default" -- 產(chǎn)品名稱 );
初始化表數(shù)據(jù)
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', '某某贈(zèng)品');
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', '某某贈(zèng)品');
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', '某某贈(zèng)品');
準(zhǔn)備個(gè)問(wèn)題
這里我找?guī)讉€(gè)基本的問(wèn)題，比如： 1.我們要找最近兩年（2019、2020）有多少筆交易？+ 2.交易成功的平均價(jià)格多少？ + 3.交易成功的訂單有多少？ + 4.店鋪1、2、3分別賣了多少？
使用filter前
對(duì)于以上同類多維度數(shù)據(jù)求解這里推薦filter,可能熟悉同學(xué)大概會(huì)記得有這么個(gè)用法，不過(guò)我們還是簡(jiǎn)單的思考下：
如果我們將條件篩選放在一個(gè)查詢里面(不含子查詢及表連接) , 這樣會(huì)在末尾where條件內(nèi)放置公共條件, 隨后我們使用filter對(duì)每個(gè)結(jié)果進(jìn)行特定的篩選，也許就好了
OK，來(lái)嘗試使用filter解決以下問(wèn)題： 找最近兩年（2019、2020）有多少筆交易？
問(wèn)題求解
我們上面拋出了個(gè)問(wèn)題： 找最近兩年（2019、2020）有多少筆交易？
很顯然這個(gè)結(jié)果集框定的范圍是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
運(yùn)行結(jié)果：
交易總訂單_20_and_19 | 交易總訂單_20 | 交易總訂單_19
----------------------+---------------+---------------
45 | 24 | 21
(1 row)
如果你是首次使用filter子句，這里我簡(jiǎn)單的驗(yàn)證下，就驗(yàn)證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)
【注意，不論您篩選的上面什么范圍內(nèi)的數(shù)據(jù)，一定要考慮 where條件一定要框定當(dāng)前所有結(jié)果集合最大的范圍，不然sql運(yùn)行的結(jié)果不及預(yù)計(jì)~ 】
最后，對(duì)于一開始的問(wèn)題給出一個(gè)參考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 交易成功的均價(jià),
count(1) filter (where status='交易成功' ) as 交易成功的訂單數(shù),
count(1) filter (where status!='交易成功' ) as 交易失敗的訂單數(shù),
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
到此這篇關(guān)于postgresql使用filter進(jìn)行多維度聚合的文章就介紹到這了,更多相關(guān)postgresql多維度聚合內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
PostgreSQL關(guān)閉數(shù)據(jù)庫(kù)服務(wù)的三種模式
PostgreSQL 提供了三種關(guān)閉數(shù)據(jù)庫(kù)服務(wù)的不同方式,它們最終都是發(fā)送一個(gè)關(guān)閉信號(hào)到 postgres 主服務(wù)進(jìn)程,本文將給大家詳細(xì)的介紹一下這三種模式,需要的朋友可以參考下2024-07-07
這篇文章主要給大家介紹了關(guān)于Postgresql查詢效率計(jì)算的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用Postgresql具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-05-05
PostgreSQL數(shù)據(jù)庫(kù)時(shí)間類型相加減操作
PostgreSQL提供了許多函數(shù),這些函數(shù)返回與當(dāng)前日期和時(shí)間相關(guān)的值,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL數(shù)據(jù)庫(kù)時(shí)間類型相加減操作的相關(guān)資料,需要的朋友可以參考下2023-10-10
PostgreSQL中調(diào)用存儲(chǔ)過(guò)程并返回?cái)?shù)據(jù)集實(shí)例
這篇文章主要介紹了PostgreSQL中調(diào)用存儲(chǔ)過(guò)程并返回?cái)?shù)據(jù)集實(shí)例,本文給出一創(chuàng)建數(shù)據(jù)表、插入測(cè)試數(shù)據(jù)、創(chuàng)建存儲(chǔ)過(guò)程、調(diào)用創(chuàng)建存儲(chǔ)過(guò)程和運(yùn)行效果完整例子,需要的朋友可以參考下2015-01-01
postgresql行轉(zhuǎn)列與列轉(zhuǎn)行圖文教程
PostgreSQL是一種開源的關(guān)系型數(shù)據(jù)庫(kù),它提供了多種管理工具來(lái)操作數(shù)據(jù)庫(kù),下面這篇文章主要給大家介紹了關(guān)于postgresql行轉(zhuǎn)列與列轉(zhuǎn)行的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
這篇文章主要介紹了postgresql如何兼容MySQL if函數(shù)問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-03-03
PostgreSQL因大量并發(fā)插入導(dǎo)致的主鍵沖突的解決方案
在數(shù)據(jù)庫(kù)操作中,并發(fā)插入是一個(gè)常見(jiàn)的場(chǎng)景,然而,當(dāng)大量并發(fā)插入操作同時(shí)進(jìn)行時(shí),可能會(huì)遇到主鍵沖突的問(wèn)題,本文將深入探討 PostgreSQL 中解決因大量并發(fā)插入導(dǎo)致的主鍵沖突的方法,并通過(guò)具體的示例進(jìn)行詳細(xì)說(shuō)明,需要的朋友可以參考下2024-07-07
postgresql~*符號(hào)的含義及用法說(shuō)明
這篇文章主要介紹了postgresql~*符號(hào)的含義及用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
postgresql數(shù)據(jù)庫(kù)配置文件postgresql.conf，pg_hba.conf,pg_ident.conf
這篇文章主要為大家介紹了postgresql數(shù)據(jù)庫(kù)中三個(gè)重要的配置文件postgresql.conf，pg_hba.conf,pg_ident.conf使用示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-02-02
PostgreSQL 實(shí)現(xiàn)子查詢返回多行的案例
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)子查詢返回多行的案例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01