SQL进阶业务题

大多得到的列是需要计算间接得到

1、好评率：利用case when统计出好评数目

统计2018年1月1日到2018年1月31日，用户'小明'提交的母婴类目"花王"品牌的好评率（好评率=“好评”评价量/总评价量）:

用户评价详情表：a
字段：id（评价id，主键），create_time（评价创建时间，格式'2017-01-01'）， user_name(用户名称)，goods_id(商品id，外键) ，
sub_time（评价提交时间，格式'2017-01-01 23:10:32'），sat_name（好评率类型，包含：“好评”、“中评”、“差评”）
商品详情表：b
字段：good_id（商品id，主键），bu_name（商品类目）, brand_name(品牌名称)

--mysql版本
select count(case when sat_name='G' then 1 else null end)/count(sat_name) as good_rate
from a, b
where a.goods_id = b.good_id
    and a.user_name = '小明'
    and b.bu_name = '母婴' 
    and b.brand_name = '花王'
    and a.sub_time between '2018-01-01' and '2018-01-31'

2、销售最高的3天，及对应的销售额
小明负责多个品牌的销售业绩，请完成：
（1）请统计小明负责的各个品牌，在2017年销售最高的3天，及对应的销售额。
销售表 a:
字段：logday（日期，主键组），SKU_ID（商品SKU，主键组），sale_amt(销售额)
商品基础信息表 b:
字段：SKU_ID（商品SKU，主键）,bu_name（商品类目），brand_name(品牌名称)，user_name（运营负责人名称）

链接：https://www.nowcoder.com/questionTerminal/f9979de28d204d0f89a2c027d4a45b3b

--Using sql server
SELECT 
    logday,sale_amt 
FEOM     
    (SELECT 
        ROW_NUMBER() over (partition by brand_name order by sale_amt) as rowId,* 
    FROM a,b 
    WHERE 
        a.SKU_ID=b.SKU_ID and user_name='小明' and a.logday between '2017-01-01' and '2017-12-31'
    )as result 
WHERE result.rowId in (1,2,3)

3、根据相邻条件查询

• 连续3天增长超过50%
  （2）请统计小明负责的各个品牌，在2017年连续3天增长超过50%的日期，及对应的销售额

---Using MYSQL
create view table_joined as (
select * from a,b
where a.SKU_id=b.SKU_id
and user_name='小明'
and year(logday)=2017)

select v1.logday,v1.amt from table_joined v1
where 3<(select count(*) from table_joined v2
where v1.brand_name=v2.brand_name
and v1.sale_amt<v2.sale_amt)
order by logday,sale_amt

• 取连续三天或以上人流量大于100的日期

SELECT DISTINCT S1.*
FROM stadium AS S1,stadium AS S2,stadium AS S3
WHERE S1.people>=100 AND S2.people>=100 AND S3.people>=100 AND (
    S1.id +1 = S2.id AND S1.id+2=S3.id OR
    S1.id +1 = S2.id AND S1.id-1=S3.id OR
    S1.id -1 = S2.id AND S1.id-2=S3.id
)
ORDER BY S1.id

• 连续进行A、B操作
  表格字段为user_id,oprerate_type,create_time，让统计有A操作然后又有B操作这种用户模式的用户数目，
  要求A操作和B操作要相邻

select creat_time,count(distinct user_id) lead(type,1) over () type1 
from activity where type='A' and type1 = 'B' group by creat_time

4、新增留存问题

• 统计新增用户

select a.sign_time, count(a.user_id) number
    from
(select user_id, min(time) sign_time from login group by user_id) a group by a.sign_time

• 统计留存率
  1 查询得到用户最早登录日期log_time作为注册日期
  2 计算每个用户的登录日期-注册日期，存为byday
  3 byday=1/当天的新增用户为次日留存率

SELECT
    --根据新增表newUsertable和留存表liucuntable得到每个注册日的次日留存率和30日留存率
    liucuntable.sign_time,
    newUsertable.newUser,
    round(cast( liucuntable.day_2 as float )/newUsertable.newUser,2) as day2_rate,
    round(cast( liucuntable.day_30 as float )/newUsertable.newUser,2) as day30_rate
FROM
    --liucuntable是根据ab得到每个注册日的第二天回访人数，第30天回访人数
    (SELECT sign_time,
        sum(case when byday=1 then 1 else 0 end) as day_2,
        sum(case when byday=29 then 1 else 0 end) as day_30,
    FROM
        --evetable是每天每个用户的登录时间，取当天该用户最早的登录时间，signtable是每个用户的注册时间 ,
        --ab得到每个新增日的每个新增用户的登录时间-注册时间      
        (SELECT 
            evetable.user_id,signtable.sign_time,evetable.eve_time,
            DATEDIFF(day,signtable.sign_time,evetable.eve_time) as byday 
        FROM
            --DATEPART(dy,time)取time在1-365的天数，这里可能会有错误，如果不同年同一天
            (SELECT user_id, min(time) eve_time FROM login group by user_id,to_Date(dy,time)) evetable,
            (SELECT user_id, min(time) sign_time FROM login group by user_id) signtable
        WHERE evetable.user_id = signtable.user_id) ab group by sign_time) liucuntable,
    (SELECT a.sign_time, count(distinct a.user_id) newUser
        FROM
        (SELECT user_id, min(time) sign_time FROM login group by user_id) a 
    group by a.sign_time) newUsertable
WHERE  liucuntable.sign_time = newUsertable.sign_time 

统计回购率和复购率（回购率是指3月份购买的用户下个月又下单了，复购率是在一段时间内两次及以上购买）

 --求3月份复购率,这里假设年份一致
 --先得到每个人的购买次数
 --统计所有人数，以及复购的人数量
 select count(ct)/count(if(ct>1,1,null)) from   
    (select userID,count(userID) as ct from orders
    where month(paidtime)=3
    group by userID) t
    
 --求回购率，如果仅仅是说3月的回购率，则可以简单的将4月份购买的用户在3月份也购买筛选出来
 select count(*) from orders 
 where userID in (子查询，算出3月份的userID) and month(paidtime)=4
 group by userID
 
 --求每个月的回购率，
--得到每个用户在每个月是否有消费记录
 --利用表的左连接,得到每个用户在不同月份的消费记录
 --每个用户后一个消费记录的月份-前一个消费记录=1,匹配出每个用户连续两个月份都有消费的记录,
 --得到每个月份，对应的消费人数，以及回购人数
 select t1.m,count(t1.m),count(t2.m) from
     (select userID,date_formate(paidtime,'%Y-%m-01') as m
     from orders
     group by userID,date_formate(paidtime,'%Y-%m-01')) t1,
 left join    
    (select userID,date_formate(paidtime,'%Y-%m-01') as m
    from orders
    group by userID,date_formate(paidtime,'%Y-%m-01')) t2
 on t1.userID = t2.userID and t1.m=date_sub(t2.m,interval 1 month)
 group by t1.m

• 统计男女用户的消费频次是否有差异

--筛选出use表中性别列不为空的数据
--和order表内连接，根据用户和性别分组，得到每个用户的消费频次
--根据性别分组，得到男女的平均消费数目
select sex,avg(ct)
    (select t.userID,sex,count(1) as ct from orders,
        (select * from user
        where sex<>'') t
    where orders.userID = t.userID
    group by userID,sex) t2
group by sex   

• 统计多次消费的用户，第一次和最后一次的消费间隔，可以想象成生命周期

--先提取出多次消费的用户
--计算每个用户最后一次消费时间与第一次时间间隔
 select userID,date_diff(max(paidtime),min(paidtime)) from orders 
 group by userID having count(1)>1

• 统计不同年龄段的消费频次是否有差异

--先将出生日换算成年龄,并分割成年龄段,并剔除脏数据
--与orders内连接，并按年龄段,TIPS:group by带上age，是为了查询age不用再嵌套
select age,avg(ct)
    (select o.userID,o.age,count(o.userID) as ct from orders o,
    inner join
        (select userID,cell((year(now())-year(birth))/10) from users
        where birth > '1901-00-00') t
    on o.userID = t.userID
    group by o.userID,age) t2
group by age