pgsql环比和同比计算

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;

结果展示:

pgsql环比和同比计算

 

 

5、重难点分析

5.1 WITH查询(公共表表达式)

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE,它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECTINSERTUPDATEDELETE,并且WITH子句本身也可以被附加到一个主语句,主语句也可以是SELECTINSERTUPDATEDELETE

WITHSELECT的基本价值是将复杂的查询分解称为简单的部分。

5.2 CASE WHEN...THEN...ELSE...EDN

详情请参考官方文档:http://www.postgres.cn/docs/10/functions-conditional.html#FUNCTIONS-CASE

CASE表达式是一种通用的条件表达式,类似于其它编程语言中的 if/else 语句:

pgsql环比和同比计算

 

CASE子句可以用于任何表达式可以出现的地方。每一个condition是一个返回boolean结果的表达式。如果结果为真,那么CASE表达式的结果就是符合条件的result,并且剩下的CASE表达式不会被处理。如果条件的结果不为真,那么以相同方式搜寻任何随后的WHEN子句。如果没有WHEN condition为真,那么CASE表达式的值就是在ELSE子句里的result。如果省略了ELSE子句而且没有条件为真,结果为空。

 5.3 ROUND

pgsql环比和同比计算

 

 

5.4 CAST

CAST函数用于类型转换:

CAST(aa as NUMERIC)

CAST( bb as VARCHAR)

 

转载:https://blog.csdn.net/qq_34721505/article/details/101428948

pgsql环比和同比计算

上一篇:mysql MVCC+间隙锁解决幻读理解


下一篇:zookeeper集群搭建