山东潍坊疫情名单_微信小程序入驻_专业放心关键词优化参考价格_北京seo加盟

目录

题目

准备数据

分析数据

---

题目

您正在运行一个电子商务网站，该网站正在寻找不平衡的订单。不平衡订单的订单最大数量严格大于每个订单（包括订单本身）的平均数量。

订单的平均数量计算为（订单中所有产品的总数量）/（订单中不同产品的数量）。订单的最大数量是订单中任何单个产品的最高数量。

编写SQL查询以查找所有不平衡订单的订单id。

准备数据

Create table If Not Exists OrdersDetails (order_id int, product_id int, quantity int);Truncate table OrdersDetails;insert into OrdersDetails (order_id, product_id, quantity) values ('1', '1', '12');insert into OrdersDetails (order_id, product_id, quantity) values ('1', '2', '10');insert into OrdersDetails (order_id, product_id, quantity) values ('1', '3', '15');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '1', '8');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '4', '4');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '5', '6');insert into OrdersDetails (order_id, product_id, quantity) values ('3', '3', '5');insert into OrdersDetails (order_id, product_id, quantity) values ('3', '4', '18');insert into OrdersDetails (order_id, product_id, quantity) values ('4', '5', '2');insert into OrdersDetails (order_id, product_id, quantity) values ('4', '6', '8');insert into OrdersDetails (order_id, product_id, quantity) values ('5', '7', '9');insert into OrdersDetails (order_id, product_id, quantity) values ('5', '8', '9');insert into OrdersDetails (order_id, product_id, quantity) values ('3', '9', '20');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '9', '4');

分析数据

第一步:得出每个订单的平均值和最大值

selectorder_id,avg(quantity) avg,max(quantity)  max
from OrdersDetails
group by order_id
;

第二步:满足最大数量大于平均值的最大值

with t1 as (selectorder_id,avg(quantity) avg,max(quantity)  maxfrom OrdersDetailsgroup by order_id
)select order_id from t1
where max > (select max(avg) from t1);