根据如下订单表orders的字段和类型，按要求写出满足条件的SQL语句：
| order_id | user_id | product_id | paid_time | is_refunded |
|---|---|---|---|---|
| 1001 | 123 | A | 2023-10-24 11:14:07 | 0 |
| 1002 | 123 | B | 2023-10-25 18:03:24 | 0 |
| 1003 | 234 | C | 2023-11-11 00:03:32 | 1 |
| 1004 | 456 | D | 2023-11-11 01:10:01 | 0 |
| 1005 | 234 | A | 2023-12-20 16:09:50 | 1 |
| 1006 | 456 | B | 2023-12-21 17:24:12 | 0 |
| 1007 | 123 | A | 2023-12-31 15:20:21 | 0 |
| 1008 | 234 | C | 2023-12-31 19:13:30 | 0 |
字段说明：
- order_id：订单ID，String类型
- user_id：用户ID，String类型
- product_id：商品ID，String类型
- paid_time：付款时间，String类型
- is_refunded：是否退款，1表示退款，0表示未退款，Bigint类型
1、数据准备
create table orders (order_id string,user_id string,product_id string,paid_time string,is_refunded bigint
);insert into orders values
('1001', '123', 'A', '2023-10-24 11:14:07', 0),
('1002', '123', 'B', '2023-10-25 18:03:24', 0),
('1003', '234', 'C', '2023-11-11 00:03:32', 1),
('1004', '456', 'D', '2023-11-11 01:10:01', 0),
('1005', '234', 'A', '2023-12-20 16:09:50', 1),
('1006', '456', 'B', '2023-12-21 17:24:12', 0),
('1007', '123', 'A', '2023-12-31 15:20:21', 0),
('1008', '234', 'C', '2023-12-31 19:13:30', 0);select * from orders order by order_id;
2、题目描述与题解
1） 查询购买过每种商品的总人数（不限时间、退款与否）
select product_id,count(distinct user_id) cnt from orders group by product_id
结果如下：
| product_id | cnt |
|---|---|
| A | 2 |
| D | 1 |
| B | 2 |
| C | 1 |
2） 查询2023-11-01及之后购买过商品C超过1次的用户（不限退款与否）
select user_id,count(1) cnt
from orders where substr(paid_time, 0, 11) >= '2023-11-01' and product_id='C'
group by user_id having cnt>1
结果如下：
| user_id | cnt |
|---|---|
| 234 | 2 |
3） 查询2023-11-01及之后每天的总订单数和退款率
-- 方式1
select substr(paid_time, 0, 11) dt,count(order_id) nums,count(case when is_refunded=1 then is_refunded end)/count(is_refunded) refund_rate
from orders where substr(paid_time, 0, 11) >= '2023-11-01'
group by substr(paid_time, 0, 11)-- 方式2
select substr(paid_time, 0, 11) dt,count(order_id) nums,count(case when is_refunded=1 then 1 end)/count(is_refunded) refund_rate
from orders where substr(paid_time, 0, 11) >= '2023-11-01'
group by substr(paid_time, 0, 11)-- 方式3（注意：if(is_refunded='1',1)不能正确计算）
select substr(paid_time, 0, 11) dt,count(order_id) nums,count(if(is_refunded=1, is_refunded))/count(is_refunded) refund_rate
from orders where substr(paid_time, 0, 11) >= '2023-11-01'
group by substr(paid_time, 0, 11)
结果如下：
| dt | nums | refund_rate |
|---|---|---|
| 2023-11-11 | 2 | 0.5 |
| 2023-12-31 | 2 | 0.0 |
| 2023-12-20 | 1 | 1.0 |
| 2023-12-21 | 1 | 0.0 |
4） 查询日退款率前3的商品及对应退款率（不限时间）
select substr(paid_time, 0, 11) dt,count(if(is_refunded=1, is_refunded))/count(is_refunded) refund_rate
from orders group by substr(paid_time, 0, 11) order by refund_rate desc limit 3
结果如下：
| dt | refund_rate |
|---|---|
| 2023-12-20 | 1.0 |
| 2023-11-11 | 0.5 |
| 2023-10-24 | 0.0 |
5） 查询每个用户购买每种商品的最后一次未退款的记录（结果仅包含表中字段）
-- 方式1
select order_id,user_id,product_id,paid_time,is_refunded from (select *,row_number() over(partition by user_id,product_id order by paid_time desc) rk from orders where is_refunded=0
) t where t.rk=1-- 方式2
select order_id,user_id,product_id,paid_time,is_refunded from (select *,first_value(paid_time) over(partition by user_id,product_id order by paid_time desc) last_paid_time from orders where is_refunded=0
) t where t.paid_time=t.last_paid_time
结果如下：
| order_id | user_id | product_id | paid_time | is_refunded |
|---|---|---|---|---|
| 1008 | 234 | C | 2023-12-31 19:13:30 | 0 |
| 1006 | 456 | B | 2023-12-21 17:24:12 | 0 |
| 1004 | 456 | D | 2023-11-11 01:10:01 | 0 |
| 1007 | 123 | A | 2023-12-31 15:20:21 | 0 |
| 1002 | 123 | B | 2023-10-25 18:03:24 | 0 |
以上SQL若存在错误或者大家有更好的方案，欢迎交流和指正
)
