Have you ever encountered such a scenario where we need to look at the summary of various dimensions within a certain period of time, such as: How many goods have we sold in the past three years? How many orders are there? What is the average transaction price? How much does each store sell? How many orders are there for successful transactions? How many orders are there for failed transactions? Wait..., what if the details of these data are all in the same table? Is there an easy way? Also, how to reduce the full table scan to change the data?
If you just simply use aggregation to get data, you may need to write a lot of SQL, which is specifically manifested as writing a paragraph of SQL for each problem. This may be a good idea. However, for under-optimized database systems, solving each problem may be a huge table scan, and of course there is another problem that is duplication.whereConditions, so can the samewhereWhat about conditions extracting to simplify SQL? Let's think about it, maybe there is such a solution~ (The conclusion is, of course, there is, hahaha~)
First, I provide the basic table structure and test data
Basic table structure
CREATE TABLE "order_info" ( "id" numeric(22) primary key , "oid" varchar(100) COLLATE "pg_catalog"."default", -- Order number "shop" varchar(100) COLLATE "pg_catalog"."default", -- Shop "date" date NOT NULL, --Order date "status" varchar(100) COLLATE "pg_catalog"."default", -- Order Status "payment" numeric(18,2), -- Transaction payment amount "product" varchar(100) COLLATE "pg_catalog"."default" -- Product Name );
Initialize table data
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217794', '16135476150276171', 'Shop 2', '2019-07-01', 'Trading failed', '139.00', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217761', '16132502190562224', 'Shop 2', '2020-05-01', 'Trading is successful', '9.90', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217795', '16122384743927326', 'Shop 3', '2019-06-01', 'Trading failed', '357.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217796', '16138945194036971', 'Shop 2', '2019-05-01', 'Intrade', '59.90', 'Some item');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217797', '16131909251901209', 'Shop 1', '2019-04-01', 'Trading failed', '359.00', 'Some Gift');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217798', '16135391935074761', 'Shop 2', '2019-03-01', 'Trading failed', '139.00', 'Some item 01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217762', '16132472268456370', 'Shop 3', '2020-04-01', 'Trading is successful', '79.00', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217763', '16122960304700879', 'Shop 2', '2020-03-01', 'Trading is successful', '357.00', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217764', '16139491271154103', 'Shop 1', '2020-02-01', 'Trading is successful', '139.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217765', '16122930818314343', 'Shop 2', '2020-01-01', 'Trading is successful', '79.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217766', '12581133644786193', 'Shop 3', '2019-12-01', 'Trading is successful', '79.00', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217767', '16122904539659361', 'Shop 2', '2019-11-01', 'Trading is successful', '359.00', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217752', '16136227870425525', 'Shop 1', '2021-02-01', 'Trading is successful', '4.90', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217753', '16139781339192958', 'Shop 2', '2021-01-01', 'Trading failed', '89.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217754', '16136217317281545', 'Shop 3', '2020-12-01', 'Intrade', '6.90', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217756', '16123091065663616', 'Shop 1', '2020-10-01', 'Trading failed', '95.00', 'Some item 01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217757', '16123013684517817', 'Shop 2', '2020-09-01', 'Intrade', '79.00', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217758', '16139678011781848', 'Shop 3', '2020-08-01', 'Intrade', '59.90', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217759', '16139576187535157', 'Shop 2', '2020-07-01', 'Trading is successful', '9.90', 'Some item 04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217791', '16132066938478413', 'Shop 4', '2019-10-01', 'Trading is successful', '359.00', 'Some item 05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217792', '12589185047405699', 'Shop 5', '2019-09-01', 'Trading is successful', '6.90', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217760', '16139601047542860', 'Shop 1', '2020-06-01', 'Trading is successful', '359.00', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217837', '16138184483906283', 'Shop 4', '2021-03-04', 'Trading is successful', '359.00', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217838', '16134581997874325', 'Shop 5', '2021-03-04', 'Trading is successful', '299.00', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217839', '16131099658443817', 'Shop 3', '2021-03-04', 'Trading is successful', '9.90', 'Some item 04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217840', '16131081649792689', 'Shop 2', '2021-03-04', 'Trading is successful', '15.89', 'Some item 05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217841', '16131087729266410', 'Shop 1', '2021-03-04', 'Trading is successful', '49.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217842', '16138126191679446', 'Shop 2', '2021-03-04', 'Trading is successful', '6.90', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217843', '16138166422967430', 'Shop 3', '2021-03-04', 'Trading is successful', '579.00', 'Some item');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217844', '16121412752067761', 'Shop 2', '2021-03-04', 'Trading is successful', '359.00', 'Some Gift');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217845', '12580980977280299', 'Shop 3', '2021-03-04', 'Trading is successful', '359.00', 'Some item 01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217799', '16135358470437562', 'Shop 2', '2019-02-01', 'Trading is successful', '339.00', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217800', '16135320673129243', 'Shop 1', '2019-01-01', 'Trading is successful', '299.00', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217801', '16131874317933316', 'Shop 2', '2021-03-04', 'Trading failed', '359.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217802', '16131792695743424', 'Shop 3', '2021-03-04', 'Intrade', '79.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217803', '16122278134767414', 'Shop 2', '2021-03-04', 'Trading failed', '99.00', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217804', '16131790093817033', 'Shop 3', '2021-03-04', 'Trading is successful', '15.89', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217805', '16135230297238674', 'Shop 2', '2021-03-04', 'Trading is successful', '247.81', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217806', '16135220588746073', 'Shop 1', '2021-03-04', 'Trading is successful', '25.79', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217831', '16131159355051065', 'Shop 3', '2021-03-04', 'Trading is successful', '359.00', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217832', '16131196017949185', 'Shop 2', '2021-03-04', 'Trading is successful', '4.90', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217833', '16131207902538323', 'Shop 1', '2021-03-04', 'Trading is successful', '339.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217834', '12580998687179491', 'Shop 2', '2021-03-04', 'Trading is successful', '15.89', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217835', '16138210374123403', 'Shop 3', '2021-03-04', 'Trading is successful', '189.00', 'Some item 11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217836', '16138242030068870', 'Shop 2', '2021-03-04', 'Trading is successful', '39.90', 'Some item 01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217846', '16134490408511254', 'Shop 3', '2021-03-04', 'Trading is successful', '238.00', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217847', '16134370276544509', 'Shop 2', '2021-03-04', 'Trading is successful', '100.00', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217854', '16121202131801564', 'Shop 1', '2021-03-04', 'Trading is successful', '359.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217855', '16121178732153257', 'Shop 2', '2021-03-04', 'Trading is successful', '499.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217856', '16130716264223504', 'Shop 3', '2021-03-04', 'Trading is successful', '9.81', 'Some item 11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217857', '16130734211002184', 'Shop 2', '2021-03-04', 'Trading is successful', '9.90', 'Some item 01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217858', '16134100289526412', 'Shop 5', '2021-03-04', 'Trading is successful', '359.00', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217859', '16134103486626066', 'Shop 3', '2021-03-04', 'Trading is successful', '189.00', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217860', '16121142702989101', 'Shop 2', '2021-03-04', 'Trading is successful', '259.00', 'Some item 04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217861', '16137767910421049', 'Shop 1', '2021-03-04', 'Trading is successful', '299.00', 'Some item 05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217862', '16121018164688502', 'Shop 5', '2021-03-04', 'Trading is successful', '299.00', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217887', '16120248152353139', 'Shop 3', '2021-03-04', 'Trading is successful', '9.90', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217888', '16136951424489400', 'Shop 2', '2021-06-07', 'Trading is successful', '9.90', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217889', '16136924750406856', 'Shop 1', '2021-05-07', 'Trading is successful', '6.90', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217916', '16119522769335722', 'Shop 2', '2021-02-07', 'Intrade', '6.90', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217917', '12588728512745597', 'Shop 1', '2021-01-07', 'Trading is successful', '89.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217848', '16138039330168579', 'Shop 2', '2021-03-04', 'Trading is successful', '314.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217849', '16130922810196821', 'Shop 3', '2021-03-04', 'Trading failed', '199.00', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217890', '16136941319549862', 'Shop 2', '2021-04-07', 'Trading is successful', '79.00', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217793', '16135470341712568', 'Shop 1', '2019-08-01', 'Trading is successful', '180.00', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217755', '16132741910343927', 'Shop 2', '2020-11-01', 'Trading is successful', '6.90', 'Some item 11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217807', '16138852921447547', 'Shop 2', '2021-03-04', 'Trading is successful', '238.00', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217891', '16133225738639350', 'Shop 1', '2021-03-07', 'Trading failed', '49.00', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217850', '12591040185524596', 'Shop 2', '2021-03-04', 'Intrade', '6.90', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217851', '16130856267945884', 'Shop 3', '2021-03-04', 'Trading is successful', '299.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217852', '16121205784010168', 'Shop 2', '2021-03-04', 'Trading failed', '19.70', 'Some item 11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217853', '16137863356208213', 'Shop 1', '2021-03-04', 'Intrade', '19.70', 'Some item 01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217958', '12588659047949994', 'Shop 2', '2019-08-07', 'Trading is successful', '9.90', 'Some item 11');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217959', '16117515001200723', 'Shop 3', '2019-07-07', 'Trading is successful', '99.00', 'Some item 01');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217960', '16126968285988680', 'Shop 2', '2019-06-07', 'Trading is successful', '6.90', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217985', '12588376827205292', 'Shop 3', '2019-05-07', 'Trading is successful', '337.00', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217986', '12588344485529392', 'Shop 2', '2019-04-07', 'Trading is successful', '139.00', 'Some item 04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217987', '16125503474522303', 'Shop 1', '2021-03-04', 'Trading failed', '9.81', 'Some item 05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217988', '16129065212801070', 'Shop 2', '2021-03-04', 'Intrade', '359.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217989', '16125466354777343', 'Shop 3', '2021-03-04', 'Intrade', '49.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217918', '16136147162483080', 'Shop 2', '2020-12-07', 'Trading is successful', '6.90', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217919', '12580777996543594', 'Shop 3', '2020-11-07', 'Trading is successful', '299.00', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217926', '16135916055519587', 'Shop 2', '2020-04-07', 'Trading is successful', '359.00', 'Some item 04');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217927', '16128748461350415', 'Shop 3', '2020-03-07', 'Trading is successful', '9.90', 'Some item 05');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217952', '16130772755076508', 'Shop 2', '2020-02-07', 'Trading is successful', '139.00', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217953', '16130750443205377', 'Shop 4', '2020-01-07', 'Trading is successful', '4.90', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217954', '16117587731623017', 'Shop 5', '2019-12-07', 'Trading is successful', '4.90', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217955', '16127065063959102', 'Shop 3', '2019-11-07', 'Trading is successful', '69.00', 'Some item 02');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217920', '16128970251579383', 'Shop 2', '2020-10-07', 'Trading is successful', '90.00', 'Some item 03');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217921', '16128964832564531', 'Shop 2', '2020-09-07', 'Trading is successful', '175.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217922', '16135999993916188', 'Shop 3', '2020-08-07', 'Trading is successful', '139.00', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217923', '16136051439214988', 'Shop 2', '2020-07-07', 'Trading is successful', '9.90', 'Some item 06');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217924', '16119347018161682', 'Shop 5', '2020-06-07', 'Trading is successful', '9.90', 'Some item 07');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217925', '16132344851576556', 'Shop 3', '2020-05-07', 'Trading is successful', '9.90', 'Some item 08');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217956', '16130631650814848', 'Shop 2', '2019-10-07', 'Trading is successful', '79.00', 'Some gift box');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217957', '16130549587928221', 'Shop 1', '2019-09-07', 'Trading is successful', '6.90', 'Some set');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217990', '12590493961403993', 'Shop 2', '2021-03-04', 'Trading is successful', '129.00', 'Some item');
INSERT INTO "order_info"("id", "oid", "shop", "date", "status", "payment", "product") VALUES ('051802588006217991', '16115933800269974', 'Shop 1', '2021-03-04', 'Trading is successful', '79.00', 'Some Gift');
Prepare a question
Here I will find a few basic questions, such as: 1. How many transactions are there in the past two years (2019 and 2020)? + 2. What is the average price for successful transactions? + 3. How many orders are there for successful transactions? + 4. How many stores 1, 2 and 3 were sold?
Before using filter
For the above similar multi-dimensional data solutions, here is recommendedfilter, If you are familiar with this, you will probably remember this usage, but let’s just think about it simply:
If we put the conditional filter in a query (without subquery and table join), it will be at the endwherePlace public conditions within the conditions, and then we usefilterJust filter each result in a specific filter, maybe it's fine
OK, try itfilterSolve the following problems: Find out how many transactions have been made in the past two years (2019, 2020)?
Questions to be solved
We raised a question above: Find out how many transactions have been made in the past two years (2019 and 2020)?
Obviously, the ranges set by this result set are 2019 and 2020, so ~
select
count(1) as Total transaction order_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 Total transaction order_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 Total transaction order_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
Running results:
Total transaction order_20_and_19 | Total transaction order_20 | Total transaction order_19
----------------------+---------------+---------------
45 | 24 | 21
(1 row)
If you are using the filter clause for the first time, I will verify it briefly and verify how many orders are in 2019:
select count(1) as total transaction order_19 from order_info where date>=to_date('2019-01-01','yyyy-MM-dd') and date < to_date('2020-01-01','yyyy-MM-dd') and date < to_date('2020-01-01','yyyy-MM-dd') ;
Total transaction order_19
---------------
21
(1 row)
[Note that no matter what range of data you filter, you must consider where conditions must frame the maximum range of all current result sets, otherwise the results of SQL running are not as expected~ 】
Finally, for the initial question, a reference sql is given:
select
count(1) as Total transaction order_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 Total transaction order_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 Total transaction order_19,
avg(payment) filter (where status='Trading is successful' ) as Average price for successful transactions,
count(1) filter (where status='Trading is successful' ) as Number of successful orders,
count(1) filter (where status!='Trading is successful' ) as Number of orders that failed transactions,
sum(payment) filter (where status='Trading is successful' and shop='Shop 1' ) as Shop1Transaction volume,
sum(payment) filter (where status='Trading is successful' and shop='Shop 2' ) as Shop2Transaction volume,
sum(payment) filter (where status='Trading is successful' and shop='Shop 3' ) as Shop3Transaction volume
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
This is the end of this article about postgresql using filter for multi-dimensional aggregation. For more related postgresql multi-dimensional aggregation content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!