SQL刷题20220315

视频完播率

select a.video_id, 
       round(sum(case when (end_time-start_time) >= duration then 1 else 0 end)/count(*),3) as avg_comp_play_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where year(a.start_time) = 2021
group by a.video_id
order by avg_comp_play_rate DESC;

select t1.video_id video_id,if(TIMESTAMPDIFF(second,t1.start_time,t1.end_time)>=t2.duration,1,0) tag,
from tb_user_video_log t1 left join tb_video_info t2
on t1.video_id=t2.video_id
WHERE year(t1.start_time)='2021'

TIMESTAMPDIFF(day/hour/second…, ‘小时间’, ‘大时间’)

平均播放进度大于60%的视频类别

select tag, concat(avg_play_progress,"%") as avg_play_progress
from (
    select tag, round(avg(
      if(
          timestampdiff(second,start_time,end_time) > duration,1, 
          timestampdiff(second,start_time,end_time) / duration)) * 100,2) as avg_play_progress
    from tb_user_video_log a
    left join tb_video_info b
    on a.video_id = b.video_id
    group by tag
    having avg_play_progress > 60 ) as c
    order by avg_play_progress DESC

concat(x,"%") 百分比的表示
select 从新表获取数据，记得要给新表重命名，同时给新表加括号
注意having 是加在group by 后面对分组计算结果进行筛选
注意百分比等数值里面，如果超过1，可以采用IF（a,1,b）
对比case when case when a then 1 else 0 end


每类视频#近一个月#的转发量/率

select tag, sum(a.if_retweet) retweet_cut,round(sum(a.if_retweet)/count(*),3) retweet_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
WHERE DATEDIFF((select max(start_time) FROM tb_user_video_log), a.start_time) <30
group by tag 
order by retweet_rate DESC

在group by多字段分组的基础上使用窗口函数，需要逻辑清晰。group by会改变表的行数，窗口函数则不会。但是窗口函数会通过partition by的方式对结果进行分组（不改变表结构）。

SELECT author, 
DATE_FORMAT(start_time,'%Y-%m') AS month, 
ROUND((SUM(IF(if_follow=1,1,0))-SUM(IF(if_follow=2,1,0))) / COUNT(start_time),3) AS fans_growth_rate,
SUM(          
#外面这个sum是把里面的每个月涨跌数全部再加起来，得到总粉丝数。，如果只有一层SUM粉丝总数始终不正确
    SUM(IF(if_follow=2,-1,if_follow))  
#里面的sum计算的是每个创作者每个月的涨跌粉丝数,这里只能用2去判断，因为还有粉丝数没有发生变化的时候
    ) OVER (PARTITION BY author ORDER BY DATE_FORMAT(start_time,'%Y-%m')) AS total_fans 
#开窗函数，按照start_time实现累计 ORDER BY是默认的开窗函数，按照author实现分区    
FROM tb_user_video_log l LEFT JOIN tb_video_info i USING(video_id)
WHERE YEAR(start_time)=2021
GROUP BY author, month 
ORDER BY  author, total_fans

https://cloud.tencent.com/developer/article/1579711

select t2.*
from (select t1.tag,t1.d
      ,sum(t1.if_like_sum)over(partition by t1.tag order by t1.d rows 6 preceding)
      ,max(t1.if_retweet_sum)over(partition by t1.tag order by t1.d rows 6 preceding)
      from (select tag,date(start_time) d
            ,sum(if_like) if_like_sum
            ,sum(if_retweet) if_retweet_sum
            from tb_user_video_log tvl,tb_video_info tvi
            where tvl.video_id=tvi.video_id
            group by tag,d) as t1
     ) as t2
where t2.d between '2021-10-01' and '2021-10-03'
order by t2.tag desc,t2.d

SELECT
	user_id,COUNT(*) days_count
FROM
	(SELECT
	#对用户ID去重，并且采用DENSE_RANK()排序1112
	 DISTINCT user_id,sales_date,DENSE_RANK() over(PARTITION by user_id ORDER BY sales_date) rn
FROM
	sales_tb) a
GROUP BY
	user_id,DATE_ADD(sales_date,INTERVAL - rn day)
HAVING
	#通过having 筛选连续天数
	days_count >=2
ORDER BY
	user_id

select product_id, round(click_cnt/show_cnt, 3) as ctr,
    round(IF(click_cnt>0, cart_cnt/click_cnt, 0), 3) as cart_rate,
    round(IF(cart_cnt>0, payment_cnt/cart_cnt, 0), 3) as payment_rate,
    round(IF(payment_cnt>0, refund_cnt/payment_cnt, 0), 3) as refund_rate
from (
    select product_id, COUNT(1) as show_cnt,
        sum(if_click) as click_cnt,
        sum(if_cart) as cart_cnt,
        sum(if_payment) as payment_cnt,
        sum(if_refund) as refund_cnt
    from tb_user_event
    where DATE_FORMAT(event_time, '%Y%m') = '202110'
    group by product_id
) as t_product_index_cnt
where payment_cnt = 0 or refund_cnt/payment_cnt <= 0.5
order by product_id;

SELECT product_id, CONCAT(profit_rate, "%") as profit_rate
FROM (
	#如空id 加入店铺汇总
    SELECT IFNULL(product_id, '店铺汇总') as product_id,
        ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) as profit_rate
    FROM (
        SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        left JOIN tb_product_info USING(product_id)
        left JOIN tb_order_overall USING(order_id)
        WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"
    ) as t_product_in_each_order
    GROUP BY product_id
    WITH ROLLUP
    #按列汇总
    HAVING profit_rate > 24.9 OR product_id IS NULL
    #空id保留
    ORDER BY product_id
) as t1;

SELECT product_id,
    ROUND(SUM(repurchase) / COUNT(repurchase), 3) as repurchase_rate
FROM (
    SELECT uid, product_id, IF(COUNT(event_time)>1, 1, 0) as repurchase
    FROM tb_order_detail
    JOIN tb_order_overall USING(order_id)
    JOIN tb_product_info USING(product_id)
    WHERE tag="零食" AND event_time >= (
        SELECT DATE_add(MAX(event_time), INTERVAL -89 DAY)
        FROM tb_order_overall
    )
    GROUP BY uid, product_id
) as t_uid_product_info
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3;

#指标：平均首单客单价=订单总金额/订单数
# 1.平均首单客单价
# select round(sum(total_amount)/count(distinct order_id),1) avg_amount
# from tb_order_overall
# where date_format(event_time,'%Y-%m')='2021-10'
# 2.平均获客成本=优惠总金额/订单数
# select round(sum(price-total_amount)/count(distinct  too.order_id),1)
# from tb_order_overall too
# inner join (
#   select order_id,sum(price*cnt) price
#   from tb_order_detail
#   group by order_id
#   ) as t
# on too.order_id=t.order_id
# where date_format(event_time,'%Y-%m')='2021-10'
# 3.整合+完善筛选条件
select round(sum(total_amount)/count(distinct too.order_id),1) avg_amount
,round(sum(price-total_amount)/count(distinct  too.order_id),1) avg_cost
from tb_order_overall too
inner join (
  select order_id,sum(price*cnt) price
  from tb_order_detail
  group by order_id
  ) as t
on too.order_id=t.order_id
where date_format(event_time,'%Y-%m')='2021-10'
and (uid,date(event_time)) in (select uid,min(date(event_time)) from tb_order_overall group by uid)

SQL18 店铺901国庆期间的7日动销率和滞销率

SELECT dt, sale_rate, 1 - sale_rate as unsale_rate
FROM (
    SELECT dt, ROUND(MIN(sale_pid_cnt) / COUNT(all_pid), 3) as sale_rate
    FROM (
        -- 国庆期间店铺901截止每天的近7天有销量的商品数
        SELECT dt, COUNT(DISTINCT IF(shop_id!=901, NULL, product_id)) as sale_pid_cnt
        FROM (
            SELECT DISTINCT DATE(event_time) as dt
            FROM tb_order_overall
            WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'
        ) as t_dates
        LEFT JOIN (
            SELECT DISTINCT DATE(event_time) as event_dt, product_id
            FROM tb_order_overall
            JOIN tb_order_detail USING(order_id)
        ) as t_dt_pid ON DATEDIFF(dt,event_dt) BETWEEN 0 AND 6
        LEFT JOIN tb_product_info USING(product_id)
        GROUP BY dt
    ) as t_dt_901_pid_cnt
    LEFT JOIN (
        -- 店铺901每个商品上架日期
        SELECT DATE(release_time) as release_dt, product_id as all_pid
        FROM tb_product_info
        WHERE shop_id=901
    ) as t_release_dt ON dt >= release_dt # 当天店铺901已上架在售的商品
    GROUP BY dt
) as t_dt_sr;