select a.day_num as "Serial Number",
a.create_time as "Shelf time",
a.clue_num as "The amount of vehicles on the shelves",
((a.clue_num/b.clue_num)-1) as "Chain Ratio"
from
(
select cast(date_diff('day',cast('2019-01-01< /span>' 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 span>,10) between '${date1}' and '${date2} span>'
AND platform in (2,3)
group by 1
)a
)a
left join
(
select cast(date_diff('day',cast('2019-01-01< /span>' 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 span>,10) between '${date1}' and '${date2} span>'
AND platform in (2,3)
group by 1
)a
)b
on a.day_num = b.day_num + 1
order by 1 asc
select a.day_num as "Serial Number",
a.create_time as "Shelf time",
a.clue_num as "The amount of vehicles on the shelves",
((a.clue_num/b.clue_num)-1) as "Chain Ratio"
from
(
select cast(date_diff('day',cast('2019-01-01< /span>' 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 span>,10) between '${date1}' and '${date2} span>'
AND platform in (2,3)
group by 1
)a
)a
left join
(
select cast(date_diff('day',cast('2019-01-01< /span>' 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 span>,10) between '${date1}' and '${date2} span>'
AND platform in (2,3)
group by 1
)a
)b
on a.day_num = b.day_num + 1
order by 1 asc