psql计算环比和同比

\c dw; –连接到数据库

drop table if exists stg.d_mars_rate_hb_1 ;

create table if not exists stg.d_mars_rate_hb_1(
category_1 TEXT,
date DATE,
quantity numeric(30,16)
);

insert into stg.d_mars_rate_hb_1(
category_1,
date,
quantity
)select
category_1,
date,
quantity
from stg.d_mars_order_date ;–从表里面选出需要的字段备用

drop table if exists stg.d_mars_cat1_month_hb;
create table if not exists stg.d_mars_cat1_month_hb(
category_1 TEXT,
hb NUMERIC(30,16)
);

insert into stg.d_mars_cat1_month_hb
//计算出每天category_1对应的值作为原表
with every as (
select category_1,date,sum(quantity) as quantity
from stg.d_mars_rate_product group by category_1,date order by date),

//计算出最近一天的category_1的对应值作为副表1
max_date as(
select category_1, max(date) as date from every where quantity is not null group by category_1),

//将副表1与原表left join 得到最近一个月的值，因为之前的原表没有quantity字段，所以left join一下得到quantity字段
quantity as(
select d.category_1,r.date,d.quantity
from max_date r
left join every d
on r.category_1 = d.category_1 and d.date > (r.date - interval ‘1 month’)::date and d.date <= r.date ),
//又将副表1与原表left join得到的表作为副表2
hb_1 as(
select a.category_1,
//最近一个月的quantity
sum(case when a.date = e.date then a.quantity end) as current_quantity,
//上一个月的quantity，如果求同比的话，将’1 month ’ 换成’12 month’
sum(case when (e.date > (a.date - interval ‘1 month’)::date and e.date <= a.date) then e.quantity end) as preview_quantity
from quantity a
left join every e
on a.category_1 = e.category_1
group by a.category_1
),
result as(
select category_1,(current_quantity - preview_quantity )/ preview_quantity as hb
from hb_1 )
select * from result ;

环比增长计算公式：（这个月的quantity-上个月的quantity）/上个月的quantity
同比增长计算公式：（这个月的quantity-去年同月的quantity）/去年同月的quantity