SQL每日练习（一）

题目来源：SQL练习

1.计算商城中2021年每月的GMV

题目描述：

订单总表tb_order_overall（order_id-订单号, uid-用户ID, event_time-下单时间, total_amount-订单总金额, total_cnt-订单商品总件数, status-订单状态）

场景逻辑说明：

用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）；
当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；
若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：

请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。

解答：
（1）GMV为已付款订单和未付款订单两者之和，即用status=0或1进行筛选；
（2）每月的GMV，说明要按月分组输出，根据示例来看还需要对时间的格式做调整，用date_format函数

select date_format(event_time,'%Y-%m') month,sum(total_amount) GMV
from tb_order_overall
where status in (0,1)
group by month
having GMV>100000
order by GMV

知识点：date_format函数

date_format函数的使用
用于以不同的格式显示日期/时间数据，语法DATE_FORMAT(date,format)

格式	描述
%Y	年，4 位
%y	年，2 位
%m	月，数值(00-12)
%b	缩写月名
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)

2.统计2021年10月每个退货率不大于0.5的商品的各项指标

题目描述：
商品行为表tb_user_event（uid-用户ID, product_id-商品ID, event_time-行为时间, if_click-是否点击, if_cart-是否加购物车, if_payment-是否付款, if_refund-是否退货退款）

场景逻辑说明：
商品点展比=点击数÷展示数；
加购率=加购数÷点击数；
成单率=付款数÷加购数；退货率=退款数÷付款数，

问题：
请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

解答：
（1）保留三位小数，用 round
（2）有一堆指标要计算，并赋别名，以group by product_id 按商品分组输出

select product_id,round(sum(if_click)/count(*),3) ctr,
       round(sum(if_cart)/sum(if_click),3) cart_rate,
       round(sum(if_payment)/sum(if_cart),3) payment_rate,
       round(sum(if_refund)/sum(if_payment),3) refund_rate
from tb_user_event
group by product_id
having refund_rate<=0.5
order by product_id

这样在少量的数据中能得到正确的结果，但并不眼睛，还有一些问题没有处理：
（1）没有对时间进行筛选（2021年10月）
（2）未考虑分母可能为0的情况

比较完整的作答：

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;

生成了一个子表 t_product_index_cnt 进行sum计数，并只选取出规定时间内的记录，这里也用到了 date_format函数，对时间的格式进行处理以便筛选。利用 if 语句处理分母为0的情况。

count(1) count(*) count(列名)的区别
总结一下就是，count(1) count(*) 结果一样，但count(1)速度可能快一些。