hive hsql 漏斗模型_数据分析之SQL：常用模型

select last_name,job_id,salary,case job_id
                                                 when 'IT_PROG' then 1.1*salary 
                                                 when 'ST_CLERK' then 1.15*salary 
                                                  when 'SA_REP' then 1.2*salary 
                                          else salary 
                                          end 
“zhangngongzi”
from employees;

select deptno,sum(clerk) as clerk ,sum(salesman) as salesman ,sum(manager) as manager,sum(analyst) as analyst ,sum(president) as president
from 
(select deptno,case job when 'CLERK' THEN SAL  end as  clerk, case job WHEN 'SALESMAN' THEN SAL  end as salesman, case job WHEN 'MANAGER' THEN SAL  end as manager,
case job WHEN 'ANALYST' THEN SAL   end as analyst, 
case job WHEN 'PRESIDENT' THEN SAL  END as president   FROM EMP ) group by deptno;

create table qiuzhiliao as 
    select week_begin_date_id,u_user,
    (case 
        when channel = 'oppokeke' then  'oppokeke'
        when channel = 'huawei' then  'huawei'
        when channel = 'xiaomi' then 'xiaomi'
        when channel = 'yingyongbao' then  'yingyongbao'
        when channel = 'yingyongbaozx' then  'yingyongbaozx'
        when channel = 'AppStore' then  'AppStore'
        when channel = 'baidu' then  'baidu'
        --when channel = '360zhushou' then channel ='360zhushou'
        when channel = 'wandoujia'  then 'wandoujia'
        when channel like 'bdsem%' then 'bdsem'
        when channel like 'sgsem%' then 'sgsem'
        when channel like 'smsem%' then 'smsem'
        else '360zhushou'
        end 
    ) AS channel
    from tmp.qzl_1
    where (channel in ('oppokeke','huawei','xiaomi','yingyongbao','yingyongbaozx','AppStore','baidu','360zhushou','wandoujia')
or channel like 'bdsem%'
or channel like 'sgsem%'
or channel like 'smsem%')


select week_begin_date_id,channel ,count (distinct u_user)
    from qiuzhiliao
group by week_begin_date_id,channel 
order by week_begin_date_id,channel

select zc.stab,
    sum(case when toppop>10000 then 1 else  0 end ) as num_10000,
    --sum(case when toppop>10000 then 1  end ) as num_10000
    sum(case when toppop >1000 then 1 else 0 end) as num_10000
from ZipCensus  zc
group by zc.stab

create table tmp.qiuzhiliao as  
select t1.u_user,t1.date_sk
from (
select u_user,date_sk
    from dw.order
    where date_sk between 20190211 and 20190318
    and status in  ('支付成功')
)t1
left join (
    select u_user
    from dw.order
    where date_sk between 20190211 and 20190318
    and status = '退款成功'
)t2
on t1.u_user=t2.u_user
where t2.u_user is null

select 课程号,max(成绩) as 最大成绩
from score 
group by 课程号;

select * 
from score as a 
where 成绩 = (
select max(成绩) 
from score as b 
where b.课程号 = a.课程号);

select 课程号,max(成绩) as 最大成绩
from score 
group by 课程号;

(select * from score where 课程号 = '0001' order by 成绩  desc limit 2)
union all
(select * from score where 课程号 = '0002' order by 成绩  desc limit 2)
union all
(select * from score where 课程号 = '0003' order by 成绩  desc limit 2);

select 学号,姓名
from student
where  学号 in (
select 学号 
from student
where 成绩 < 60
);

select t1.学号,t1.姓名
from (
select 学号,姓名
from student 
)t1
inner join (
      select 学号 
from student
where 成绩 < 60
)t2
on t1.学号=t2.学号

select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 
	 then 1 else 0 end) as '[100-85]',
sum(case when 成绩 >=70 and 成绩<85 
	 then 1 else 0 end) as '[85-70]',
sum(case when 成绩>=60 and 成绩<70  
	 then 1 else 0 end) as '[70-60]',
sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b 
on a.课程号=b.课程号
group by a.课程号,b.课程名称;

--产品路径统计可以这么写
--春节活动入口统计
 SELECT day,
        count(CASE when x2_1.event_key = 'initApp'  then x2_1.u_user ELSE NULL end) as ad_pv,--开屏曝光的pv
        count(DISTINCT CASE when x2_1.event_key = 'initApp'  then x2_1.u_user ELSE NULL end) as ad_uv,--开屏曝光的uv
FROM
    (
        SELECT from_unixtime(unix_timestamp(cast(day as string),'yyyyMMdd'),'yyyy-MM-dd') as day,
               event_key,u_user,status,button,device
        from table
        WHERE from_unixtime(unix_timestamp(cast(day as string),'yyyyMMdd'),'yyyy-MM-dd') BETWEEN '2019-03-28' and date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1) --int
            AND
                    --开屏曝光
                    event_key = 'initApp'
group by day 
order by day