sparksql一些指标

最新推荐文章于 2023-04-14 17:05:04 发布

给我一个苹果

最新推荐文章于 2023-04-14 17:05:04 发布

阅读量164

点赞数

文章标签：大数据

本文链接：https://blog.csdn.net/weixin_43705952/article/details/106464292

版权

统计指标

select
substr(tb.begin_address_code , 1 ,4) as begin_address_code ,
count(distinct vehicle_license) as dayVehicleCount
from
(select
begin_address_code ,
vehicle_license
from
order
where
date_format(create_time , 'yyyy-MM-dd')  = '2020-02-15
') tb
group by
substr(tb.begin_address_code , 1 ,4)

汇总指标

select
monthVehicle.begin_address_code ,
NVL(monthVehicle.monthVehicleCount ,0) as monthVehicleCount,
NVL(dayVehicle.dayVehicleCount , 0) as dayVehicleCount
from
monthVehicle left join dayVehicle
on
monthVehicle.begin_address_code = dayVehicle.begin_address_code

日指标

select
substr(tb.begin_address_code , 1 , 4)  as begin_address_code  ,
count(1) as dayOrderCount
from
(select begin_address_code from order
where
date_format(create_time , 'yyyy-MM-dd') = '2020-02-15') tb
group by
substr(tb.begin_address_code , 1 , 4)

周指标

select
substr(tb.begin_address_code , 1 , 4)  as begin_address_code  ,
count(1) as weekOrderCount
from
(select begin_address_code from order
where
weekofyear(create_time) = '29') tb
group by
substr(tb.begin_address_code , 1 , 4)

月指标

select
substr(tb.begin_address_code , 1 , 4)  as begin_address_code  ,
count(1) as monthOrderCount
from
(select begin_address_code from order
where
date_format(create_time , 'yyyy-MM') = '2020-02') tb
group by
substr(tb.begin_address_code , 1 , 4)

汇总

select
tb1.begin_address_code ,
tb1.monthOrderCount ,
tb1.weekOrderCount ,
dayOrder.dayOrderCount
from
(select
monthOrder.begin_address_code ,
monthOrder.monthOrderCount ,
weekOrder.weekOrderCount
from
monthOrder left join weekOrder
on
monthOrder.begin_address_code  =  weekOrder.begin_address_code)  tb1 left join dayOrder
on
tb1.begin_address_code = dayOrder.begin_address_code

汇总

select
cast(
concat(
 if(v_begin_address_code is null ,o_begin_address_code , v_begin_address_code)
, '00')
as string) as rk,
cast(if(monthVehicleCount is null , 0 ,monthVehicleCount) as string) as monthVehicleCount ,
cast(if(dayVehicleCount is null , 0 , dayVehicleCount) as string) as dayVehicleCount ,
cast(
concat(
 if(o_begin_address_code is null ,v_begin_address_code , o_begin_address_code)
, '00')
as string) as o_begin_address_code,
cast(if(monthOrderCount is null , 0 , monthOrderCount) as string) as monthOrderCount ,
cast(if(weekOrderCount is null, 0 , weekOrderCount) as string) as weekOrderCount ,
cast(if(dayOrderCount is null ,0, dayOrderCount) as string) as dayOrderCount
from
(select
vehcileCount.begin_address_code as v_begin_address_code,
vehcileCount.monthVehicleCount ,
vehcileCount.dayVehicleCount ,
_totalOrder.begin_address_code as o_begin_address_code ,
_totalOrder.monthOrderCount ,
_totalOrder.weekOrderCount ,
_totalOrder.dayOrderCount
from
vehcileCount full outer join _totalOrder
on
vehcileCount.begin_address_code = _totalOrder.begin_address_code
) tb