23. 销售额完成任务指标的商品

题目需求

商家要求每个商品每个月需要售卖出一定的销售总额
假设1号商品销售总额大于21000，2号商品销售总额大于10000，其余商品没有要求
请写出SQL从订单详情表中（order_detail）查询连续两个月销售总额大于等于任务总额的商品

期望结果如下：

sku_id （商品id）
1

需要用到的表：

订单明细表：order_detail

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
…	…	…	…	…	…

存在的疑问

实现一

-- 实现1-1 : 2）、3） 较难理解  --> 请看实现1-2

select sku_id
from (
-- 4） 选择出 (create_date_for 与 create_date_for_last 相差一个月) 的记录
         select sku_id,
                sum_price,
                sum_price_last,
                -- 5） 判断是否连续两个月的销售总额是否达到要求。1: 达到; 0: 未达到。
                case
                    when sku_id = 1 and sum_price >= 21000 and sum_price_last >= 21000 then 1
                    when sku_id = 2 and sum_price >= 10000 and sum_price_last >= 10000 then 1
                    else 0
                    end as whether_ok
         from (
                  -- 1） 统计 1号 2号 商品每个月的销售总额
                  select sku_id,
                         date_format(create_date, 'yyyy-MM')                                             create_date_for,
                         sum(price)                                                                      sum_price,
                         -- 2） 获取下一次销售月份, 如果为null，则默认使用 '9999-99'
                         lead(date_format(create_date, 'yyyy-MM'), 1, '9999-99')
                              over (partition by sku_id order by date_format(create_date, 'yyyy-MM')) as create_date_for_last,
                         -- 3） 获取下一次销售月份的销售总额, 如果为null，则默认使用 0
                         -- 注: 此处的 sum(price) 是计算的下一组的 销售总额
                         lead(sum(price), 1, 0.00)
                              over (partition by sku_id order by date_format(create_date, 'yyyy-MM')) as sum_price_last
                  from order_detail
                  where sku_id in (1, 2)
                        --  这里 date_format(create_date, 'yyyy-MM') 被视为一个整体
                  group by sku_id, date_format(create_date, 'yyyy-MM')
              ) t1
         where datediff(concat(create_date_for_last, '-01'), concat(create_date_for, '-01')) <= 31
     ) t2
where whether_ok = 1;


-- 实现1-2

select sku_id
from (
-- 4） 选择出 (create_date_for 与 create_date_for_last 相差一个月) 的记录
         select sku_id,
                sum_price,
                sum_price_last,
                -- 5） 判断是否连续两个月的销售总额是否达到要求。1: 达到; 0: 未达到。
                case
                    when sku_id = 1 and sum_price >= 21000 and sum_price_last >= 21000 then 1
                    when sku_id = 2 and sum_price >= 10000 and sum_price_last >= 10000 then 1
                    else 0
                    end as whether_ok
         from (
                  select sku_id,
                         create_date_for,
                         sum_price,
                         -- 2） 获取下一次销售月份, 如果为null，则默认使用 '9999-99'
                         lead(create_date_for, 1, '9999-99')
                              over (partition by sku_id order by create_date_for)                     as create_date_for_last,
                         -- 3） 获取下一次销售月份的销售总额, 如果为null，则默认使用 0
                         lead(sum_price, 1, 0.00) over (partition by sku_id order by create_date_for) as sum_price_last
                  from (
                           -- 1） 统计 1号 2号 商品每个月的销售总额
                           select sku_id,
                                  date_format(create_date, 'yyyy-MM') create_date_for,
                                  sum(price)                          sum_price
                           from order_detail
                           where sku_id in (1, 2)
                                 --  这里 date_format(create_date, 'yyyy-MM') 被视为一个整体
                           group by sku_id, date_format(create_date, 'yyyy-MM')
                       ) t1
              ) t2
         where datediff(concat(create_date_for_last, '-01'), concat(create_date_for, '-01')) <= 31
     ) t3
where whether_ok = 1;

题目来源

http://practice.atguigu.cn/#/question/23/desc?qType=SQL