牛客-SQL13 计算商城中2021年每月的GMV

场景逻辑说明：

用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，status-订单状态为0，表示待付款）；

当用户支付完成时，在订单总表修改对应订单记录的status-订单状态为1，表示已付款；

若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，status-订单状态为2表示已退款）。

问题：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。

思路

1.查出已支付的 status=1的表

注意Hive时间格式化函数与mysql的不同

Hive:DATE_FORMAT(event_time,'yyyy-MM')

mysql:DATE_FORMAT(event_time,'%Y-%m')

select order_id,DATE_FORMAT(event_time,'%Y-%m')as month,DATE_FORMAT(event_time,'%Y')as year,total_amount ,total_cnt,status

from

tb_order_overall

where

`status` =1

2.查出已付款未支付的ststus=0的表（此处把已支付的status=1表过滤掉，但是测试用例中并没有初现已付款和已支付的表重的现象）

select order_id,DATE_FORMAT(event_time,'%Y-%m') as month,DATE_FORMAT(event_time,'%Y')as year,total_amount ,total_cnt,status

from

tb_order_overall

where

`status` =0

and order_id not in (select id from tb_order_overall where `status`=1 )

3.把以上两个表union即是，没有重复订单的已付款和已支付订单 作为s1表

    将s1表按照year=2021过滤，再次按照group by月份聚合sum(total_amount) GMV  查询结果作为s2

4.s2按照GMV》=100000过滤再排序，保存整数

select

month,

ROUND(GMV,0)

FROM

(

select

month,

sum(total_amount) GMV

FROM

(select order_id,DATE_FORMAT(event_time,'%Y-%m')as month,DATE_FORMAT(event_time,'%Y')as year,total_amount ,total_cnt,status

from

tb_order_overall

where

`status` =1

UNION

select order_id,DATE_FORMAT(event_time,'%Y-%m') as month,DATE_FORMAT(event_time,'%Y')as year,total_amount ,total_cnt,status

from

tb_order_overall

where

`status` =0

and order_id not in (select id from tb_order_overall where `status`=1 ))s1

where year=2021

GROUP BY month)S2

where GMV>=100000

order by GMV

题目不难，总是会漏掉条件 ,第一次提交漏了好多条件， 需要更严谨些。