1、postgres中with用法的官方文档地址
http://www.postgres.cn/docs/10/queries-with.html
2、有一张表biz_ticket,建表语句如下
CREATE TABLE "public"."biz_ticket" ( "id" int4 DEFAULT nextval(‘"crt".biz_ticket_id_seq‘::regclass) NOT NULL, "create_time" timestamp(6), "type" text COLLATE "default" ) WITH (OIDS=FALSE) ; ALTER TABLE "public"."biz_ticket" OWNER TO "postgres"; COMMENT ON COLUMN "public"."biz_ticket"."id" IS ‘自增主键‘; COMMENT ON COLUMN "public"."biz_ticket"."create_time" IS ‘创建时间‘; COMMENT ON COLUMN "public"."biz_ticket"."type" IS ‘类型‘;
3、向表中插入10条数据
INSERT INTO "biz_ticket" VALUES (1, ‘2019-9-26 11:06:05‘, ‘REQ‘); INSERT INTO "biz_ticket" VALUES (2, ‘2019-9-26 11:06:50‘, ‘BUG‘); INSERT INTO "biz_ticket" VALUES (3, ‘2019-9-26 11:07:00‘, ‘REQ‘); INSERT INTO "biz_ticket" VALUES (4, ‘2019-9-26 11:07:17‘, ‘OPPTY‘); INSERT INTO "biz_ticket" VALUES (5, ‘2019-9-26 11:07:29‘, ‘BUG‘); INSERT INTO "biz_ticket" VALUES (6, ‘2019-9-26 11:07:58‘, ‘BUG‘); INSERT INTO "biz_ticket" VALUES (7, ‘2019-8-26 11:13:00‘, ‘REQ‘); INSERT INTO "biz_ticket" VALUES (8, ‘2018-9-26 11:13:22‘, ‘REQ‘); INSERT INTO "biz_ticket" VALUES (9, ‘2018-9-26 11:13:49‘, ‘REQ‘); INSERT INTO "biz_ticket" VALUES (10, ‘2019-8-26 11:16:00‘, ‘BUG‘);
4、需求分析
计算9月份type=‘‘REQ"的占比、同比和环比;
说明:
环比指的是相邻两月(即9月份和8月份)进行比较;
同比指的是历史同期数据(即2019月9月与2018年9月)进行比较。
SQL语句如下:
WITH total_t AS ( -- 临时表:获取9月份数量总数量为:6条 SELECT COUNT (TYPE) AS tc FROM biz_ticket T WHERE -- 当前时间 T .create_time >= ‘2019-09-01 00:00:00‘ AND T .create_time <= ‘2019-09-30 23:59:59‘ ), type_zbc_t AS ( -- 临时表:获取9月份type=‘REQ‘的数量为:2条 SELECT COUNT (TYPE) AS zbc FROM biz_ticket T WHERE -- 当前时间 T ."type" = ‘REQ‘ AND T .create_time >= ‘2019-09-01 00:00:00‘ AND T .create_time <= ‘2019-09-30 23:59:59‘ ) , type_hbc_t as( -- 临时表,获取月环比(8月份)type=‘REQ‘数量为:1条 SELECT COUNT (TYPE) AS hbc FROM biz_ticket T WHERE -- 环比时间 T ."type" = ‘REQ‘ AND T .create_time >= ‘2019-08-01 00:00:00‘ AND T .create_time <= ‘2019-08-30 23:59:59‘ ), type_tbc_t as ( --临时表,获取去年同期(2018年9月type=‘REQ‘)同比数量为:2条 SELECT COUNT (TYPE) AS tbc FROM biz_ticket T WHERE -- 同比时间 T ."type" = ‘REQ‘ AND T .create_time >= ‘2018-09-01 00:00:00‘ AND T .create_time <= ‘2018-09-30 23:59:59‘ ) SELECT case when t2.tc>0 then round(CAST (t1.zbc * 100.0 / t2.tc AS NUMERIC),2) else 0 end as zb, case when t3.hbc>0 then round(CAST (t1.zbc * 100.0 / t3.hbc AS NUMERIC),2) else 0 end as hb, case when t4.tbc>0 then round(CAST (t1.zbc * 100.0 / t4.tbc AS NUMERIC),2) else 0 end as tb FROM total_t t2, type_zbc_t t1, type_hbc_t t3,type_tbc_t t4;
结果展示:
5、重难点分析
5.1 WITH
查询(公共表表达式)
WITH
提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE,它们可以被看成是定义只在一个查询中存在的临时表。在WITH
子句中的每一个辅助语句可以是一个SELECT
、INSERT
、UPDATE
或DELETE
,并且WITH
子句本身也可以被附加到一个主语句,主语句也可以是SELECT
、INSERT
、UPDATE
或DELETE
。
WITH
中SELECT
的基本价值是将复杂的查询分解称为简单的部分。
5.2 CASE WHEN...THEN...ELSE...EDN
详情请参考官方文档:http://www.postgres.cn/docs/10/functions-conditional.html#FUNCTIONS-CASE
CASE
表达式是一种通用的条件表达式,类似于其它编程语言中的 if/else 语句:
CASE
子句可以用于任何表达式可以出现的地方。每一个condition
是一个返回boolean
结果的表达式。如果结果为真,那么CASE
表达式的结果就是符合条件的result
,并且剩下的CASE
表达式不会被处理。如果条件的结果不为真,那么以相同方式搜寻任何随后的WHEN
子句。如果没有WHEN
condition
为真,那么CASE
表达式的值就是在ELSE
子句里的result
。如果省略了ELSE
子句而且没有条件为真,结果为空。
5.3 ROUND
5.4 CAST
CAST函数用于类型转换:
CAST(aa as NUMERIC)
CAST( bb as VARCHAR)
转载:https://blog.csdn.net/qq_34721505/article/details/101428948