mc_devappear表中的数据可以计算出当天新增的设备数,launch2表中存储的数据可以计算出当天启动的所有设备数。用一句sql计算出二者比例(新增设备数/所有设备数)
【1】第一版
缺陷:
【2】第二版
缺陷:devicenum为0的时候无法计算
【3】第三版
【1】第一版
with temp as(
select count(distinct deviceid) as newdevnum from ana_fx_middle.mc_devappear where first_appear_hdfspar ='20161214' and hdfs_par='20161214'
full join
select count(distinct deviceid) as devicenum from src_huidu_mc.launch2 where hdfs_par ='20161214'
)
select newdevnum/devicenum from temp
缺陷:
AnalysisException: Syntax error in line 3: full join ^ Encountered: FULL Expected: AND, BETWEEN, DIV, GROUP, HAVING, ILIKE, IN, IREGEXP, IS, LIKE, LIMIT, NOT, OFFSET, OR, ORDER, REGEXP, RLIKE, UNION CAUSED BY: Exception: Syntax error
【2】第二版
with temp as(
select * from
(select '1' as id, count(distinct deviceid) as newdevnum from ana_fx_middle.mc_devappear where first_appear_hdfspar ='20161214' and hdfs_par='20161214') as tmp1
left join
(select '1' as id,count(distinct deviceid) as devicenum from src_huidu_mc.launch2 where hdfs_par ='20161214' ) as tmp2
on tmp1.id=tmp2.id
)
select newdevnum/devicenum from temp
缺陷:devicenum为0的时候无法计算
【3】第三版
with temp as(
select * from
(select '1' as id, count(distinct deviceid) as newdevnum from ana_fx_middle.mc_devappear where first_appear_hdfspar ='20161214' and hdfs_par='20161214') as tmp1
left join
(select '1' as id,count(distinct deviceid) as devicenum from src_huidu_mc.launch2 where hdfs_par ='20161214' ) as tmp2
on tmp1.id=tmp2.id
)
select (cast(newdevnum as bigint))/(if cast(devicenum as bigint)=0,1,cast(devicenum as bigint)) from temp