sql多表计算问题

mc_devappear表中的数据可以计算出当天新增的设备数，launch2表中存储的数据可以计算出当天启动的所有设备数。用一句sql计算出二者比例（新增设备数/所有设备数）

【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