HIVE_HSQL

select from_unixtime(unix_timestamp(apply_time),'yyyyMMdd')date
,CASE WHEN apply_url like 'http://m.jia.com/page/zxjzj.html?source=jzjtusou%' then '图搜' ELSE '非图搜' end cats
,count(distinct case when apply_id is not null then apply_id else null end) apply_num
,count(distinct case when apply_mobile is not null then apply_mobile else null end) apply_user_num
,count(distinct case when need_id is not null then need_id else null end)need_num
,count(distinct case when need_mobile is not null then need_mobile else null end)need_user_num
,count(distinct case when order_id is not null then order_id else null end)order_num
,count(distinct case when order_mobile is not null then order_mobile else null end)order_user_num FROM dw.kn2_zx_apply_order_detail WHERE apply_url like 'http://m.jia.com/page/zxjzj.html%' 
and from_unixtime(unix_timestamp(apply_time),'yyyyMMdd')>='20180220'
group by from_unixtime(unix_timestamp(apply_time),'yyyyMMdd'),CASE WHEN apply_url like 'http://m.jia.com/page/zxjzj.html?source=jzjtusou%' then '图搜'ELSE '非图搜' end
ORDER BY date DESC,cats


select from_unixtime(unix_timestamp(apply_time),'yyyyMMdd')date
,CASE WHEN apply_url like 'http://m.jia.com/page/zxjzj.html?source=jzjtusou%' then '图搜' ELSE '非图搜' end cats
,count(distinct case when apply_id is not null then apply_id else null end) apply_num
,count(distinct case when apply_mobile is not null then apply_mobile else null end) apply_user_num
,count(distinct case when need_id is not null then need_id else null end)need_num
,count(distinct case when need_mobile is not null then need_mobile else null end)need_user_num
,count(distinct case when order_id is not null then order_id else null end)order_num
,count(distinct case when order_mobile is not null then order_mobile else null end)order_user_num FROM dw.kn2_zx_apply_order_detail WHERE apply_url like 'http://m.jia.com/page/zxjzj.html%' 
and from_unixtime(unix_timestamp(apply_time),'yyyyMMdd')>='20180220'
group by from_unixtime(unix_timestamp(apply_time),'yyyyMMdd'),CASE WHEN apply_url like 'http://m.jia.com/page/zxjzj.html?source=jzjtusou%' then '图搜'ELSE '非图搜' end
ORDER BY date DESC,cats



select dt,leibie,SUM(pv)pv,SUM(UV)uv FROM (select 
from_unixtime(unix_timestamp(server_time),'yyyyMMdd') dt,
CASE WHEN url LIKE '%h5.m.jia.com%' then '主app' else 'm站' end leibie ,
case when lower(url) like '%friendcircle%' then 'friendcircle'
when lower(url) like '%childcircle%' then 'childcircle'
when lower(url) like '%newcircle%' then 'newcircle'
when lower(url) like '%newtiezi%' then 'newtiezi'
when lower(url) like '%app_pinglun%' then 'app_pinglun'
when lower(url) like '%zixun/u/%' then 'person_center'else 'qita' END fenlei ,
count(1)pv,
count(distinct visitor_id)uv,ROUND(sum(residence_time)/count(distinct visitor_id),2) avg_Rtime
from dw.kn2_traf_detail
where dt>= '20180227' and (lower(url) like '%friendcircle%' 
or lower(url) like '%childcircle%' 
or lower(url) like '%newcircle%' 
or lower(url) like '%newtiezi%' 
or lower(url) like '%app_pinglun%' )
group by from_unixtime(unix_timestamp(server_time),'yyyyMMdd'),
CASE WHEN url LIKE '%h5.m.jia.com%' then '主app' else 'm站' end ,
case when lower(url) like '%friendcircle%' then 'friendcircle'
when lower(url) like '%childcircle%' then 'childcircle'
when lower(url) like '%newcircle%' then 'newcircle'
when lower(url) like '%newtiezi%' then 'newtiezi'
when lower(url) like '%app_pinglun%' then 'app_pinglun'
when lower(url) like '%zixun/u/%' then 'person_center'else 'qita' END
) A
GROUP BY A.DT,A.leibie
ORDER BY A.DT DESC ,A.leibie












select dt,'头条' as leibie,sum(pv)pv,sum(UV)uv FROM (
SELECT dt,case when page_name like '%圈子%' then '圈子' else '用户个人主页' end page_name ,COUNT(1)pv,COUNT(distinct udid)uv
FROM dw.kn1_traf_app_tracker_detail_source where DT>='20180227' -- and DT<'20180202'
and (page_name like '%圈子%'
OR page_name like '%用户个人主页%')
GROUP BY dt,page_name
ORDER BY DT desc ) A
group by a.dt
ORDER BY A.DT DESC