# MySQL 案例：计算环比

```select a.day_num as "序号",
a.create_time as "上架时间",
a.clue_num as "上架车源量",
((a.clue_num/b.clue_num)-1) as "环比"
from
(
select cast(date_diff(‘day‘,cast(‘2019-01-01‘ as timestamp),a.create_time) as int) as day_num,
a.create_time,
a.clue_num
from
(
SELECT
--substr(create_time,1,7) as year_mo,
date(from_unixtime(to_unixtime(cast(create_time as timestamp)))) as create_time,
cast(count(distinct clue_id) as double) as clue_num
FROM guazi_dw_dwd.dim_com_car_source_ymd as a
WHERE dt = CAST(date_add(‘day‘, -1, current_date) AS VARCHAR)
and substr(create_time,1,10) between ‘\${date1}‘ and ‘\${date2}‘
AND platform in (2,3)
group by 1
)a
)a
left join
(
select cast(date_diff(‘day‘,cast(‘2019-01-01‘ as timestamp),a.create_time) as int) as day_num,
a.create_time,
a.clue_num
from
(
SELECT
--substr(create_time,1,7) as year_mo,
date(from_unixtime(to_unixtime(cast(create_time as timestamp)))) as create_time,
cast(count(distinct clue_id) as double) as clue_num
FROM guazi_dw_dwd.dim_com_car_source_ymd as a
WHERE dt = CAST(date_add(‘day‘, -1, current_date) AS VARCHAR)
and substr(create_time,1,10) between ‘\${date1}‘ and ‘\${date2}‘
AND platform in (2,3)
group by 1
)a
)b
on a.day_num = b.day_num + 1
order by 1 asc```

每一个你不满意的现在，都有一个你没有努力的曾经。
一个历史类的公众号，欢迎关注 欢迎关注本站公众号,获取更多程序园信息 