数据血缘相似度探索 v1.0

select
user_id  as uid
,user_name  as uname
from
(
    select user_id, concat("test",user_name) as user_name
    from user
)t

--- 输出列uid 数据来源于user表的user_id 列
uid	from:{"expression":"user_id","isEnd":true,"sourceTableName":"user","targetColumnName":"user_id"}


-- 输出列uname 数据来源于常量字段test及 user表的user_name 列
uname	from:{"expression":"concat('test', user_name)","isEnd":true,"sourceTableName":"user","targetColumnName":"user_name"}

uname	from:{"expression":"concat('test', user_name)","isEnd":true,"sourceTableName":"user","targetColumnName":"'test'"}

-- 报表A
select date_format(imp_date,'%Y-%m-%d') as imp_date,
	case extra
		when 'icon' then '主动'
		when 'push' then 'push推送'
		when 'weixin' then '微信'
		when 'mobileQQpush' then '手Q'
        else 'other'
	end as extra,sum(value2) as uv
from t_1
where imp_date>='startDate' and imp_date<='endDate'
	and indicator in ('indicator')
	and subin=0
    and extra in ('icon','push','weixin','mobileQQpush')
group by imp_date,case extra
		when 'icon' then '主动'
		when 'push' then 'push推送'
		when 'weixin' then '微信'
		when 'mobileQQpush' then '手Q'
        else 'other'
	end
order by imp_date

-- 报表B
select imp_date,
	case extra
		when 'icon' then '主动'
		when 'push' then 'push推送'
		when 'weixin' then '微信'
		when 'mobileQQpush' then '手Q'
	end as extra,sum(value1) as pv
from t_1
where imp_date>='startDate' and imp_date<='endDate'
	and indicator in ('indicator')
	and subin=0
	and extra<>'other'
group by imp_date,extra
order by imp_date

-- 报表A
select date_format(imp_date,'%Y-%m-%d') as imp_date,
	case extra
		when 'icon' then '主动'
		when 'push' then 'push推送'
		when 'weixin' then '微信'
		when 'mobileQQpush' then '手Q'
        else 'other'
	end as extra,sum(value2) as uv
from t_1
where imp_date>='startDate' and imp_date<='endDate'
	and indicator in ('indicator')
	and subin=0
    and extra in ('icon','push','weixin','mobileQQpush')
group by imp_date,case extra
		when 'icon' then '主动'
		when 'push' then 'push推送'
		when 'weixin' then '微信'
		when 'mobileQQpush' then '手Q'
        else 'other'
	end
order by imp_date

-- 报表B
select imp_date,
case when c1 = 1  and c2 = 1 and c3 = 1 then '顶部下拉刷新'
     when c1 = 1  and c2 = 1 and c3 = 2 then '频道tab点击刷新'
     when c1 = 1  and c2 = 1 and c3 = 3 then '中部主动刷新p'
     when c1 = 1  and c2 = 1 and c3 = 4 then '底部tab栏点击刷新'
     when c1 = 1  and c2 = 1 and c3 = 9999 then '顶部主动刷新'
     when c1 = 1  and c2 = 2 and c3 = 9999 then '底部主动刷新'
     when c1 = 1  and c2 = 9999 and c3 = 9999 then '主动刷新'
     when c1 = 2  and c2 = 9999 and c3 = 9999 then '自动刷新'
     when c1 = 9999  and c2 = 9999 and c3 = 9999 then '汇总'
     end mode
     ,
    sum(uv) uv
from t_2
where imp_date>='startDate' and imp_date<='endDate'
    and ssitename='9999'
    and ostype = 'ostype' and (ista is null or ista = '-')
    group by imp_date,c1,c2,c3

select imp_date,
  case devtype when 1 then 'all' when 2 then 'android' when 3 then 'ipad' when 4 then 'iphone' else devtype end as devtype,
  max(case indicator when 105 then value end ) value_1,
  max(case indicator when 105 then concat(rate1,'%') end ) rate1_1,
  max(case indicator when 105 then concat(rate2,'%') end ) rate2_1,
  max(case indicator when 106 then concat(value,'%') end ) value_2,
  max(case indicator when 106 then concat(rate1,'%') end ) rate1_2,
  max(case indicator when 106 then concat(rate2,'%') end ) rate2_2
from (
	select imp_date,indicator,devtype,value,rate1,rate2
	from t_1
	where imp_date>='startDate' and imp_date<='endDate'
		and indicator in ('indicator')
		and subin=0
		and devtype in (devtype)
) t
group by imp_date,devtype
order by imp_date desc,devtype

select
	a.imp_date as imp_date, dau, pv, uv, avg_pv, video_pv, video_uv,avg_video_pv,pic_pv, pic_uv,avg_pic_pv,
	play_vv, play_uv,avg_play_vv,play_video_vv, play_video_uv,avg_play_video_vv,play_pic_vv, play_pic_uv,avg_play_pic_vv, video_click_vv, video_click_uv
from(
	select distinct
		imp_date,
		pv, uv, ROUND((pv) / (uv) , 2) AS avg_pv,
		video_pv, video_uv, ROUND((video_pv) / (video_uv) , 2) AS avg_video_pv,
		pic_pv, pic_uv, ROUND((pic_pv) / (pic_uv) , 2) AS avg_pic_pv,
		(play_video_vv+play_pic_vv) as play_vv, play_uv, ROUND((play_video_vv+play_pic_vv) / (play_uv) , 2) AS avg_play_vv,
		play_video_vv, play_video_uv, ROUND((play_video_vv) / (play_video_uv) , 2) AS avg_play_video_vv,
		play_pic_vv, play_pic_uv, ROUND((play_pic_vv) / (play_pic_uv) , 2) AS avg_play_pic_vv,
		video_click_vv, video_click_uv
	from
		t_2
	where
		imp_date>='startDate' and imp_date <= 'endDate'
		and (aboutista is null or aboutista = 'all') and (site_name = 'all') and (activefrom is null or activefrom='all')

) a
join
(
	select
		imp_date,value as dau
	from
		t_3
	where indicator='103' and devtype = 1 and imp_date>='startDate' and imp_date <= 'endDate'
)b
on a.imp_date = b.imp_date
order by imp_date desc