SQL面试题练习 —— 连续支付订单合并

1 题目

现有一张用户支付表：t_user_pay 包含字段订单ID，用户ID，商户ID，支付时间，支付金额。

如果同一用户在同一商户存在多笔订单，且中间该用户没有其他商户的支付记录，则认为是连续订单，请把连续订单进行合并，时间取最早支付时间，金额求和。

样例数据如下：


+----------+---------+-------------+---------------------+--------------+
| order_id | user_id | merchant_id |      pay_time        |  pay_amount  |
+----------+---------+-------------+---------------------+--------------+
| 001      | user_01 | merchant_01 | 2023-03-01 12:30:00 | 50.0         |
| 002      | user_01 | merchant_01 | 2023-03-01 13:45:00 | 75.5         |
| 003      | user_01 | merchant_02 | 2023-03-01 14:00:00 | 100.0        |
| 004      | user_01 | merchant_03 | 2023-03-02 09:15:00 | 25.0         |
| 005      | user_01 | merchant_03 | 2023-03-02 10:30:00 | 150.25       |
| 006      | user_01 | merchant_01 | 2023-03-02 11:00:00 | 500.0        |
| 007      | user_01 | merchant_02 | 2023-03-03 08:00:00 | 80.0         |
| 008      | user_02 | merchant_01 | 2023-03-03 09:30:00 | 120.0        |
| 009      | user_02 | merchant_02 | 2023-03-04 13:45:00 | 65.0         |
| 010      | user_02 | merchant_03 | 2023-03-04 14:00:00 | 150.0        |
| 011      | user_02 | merchant_03 | 2023-03-05 11:30:00 | 20.0         |
| 012      | user_02 | merchant_03 | 2023-03-05 12:00:00 | 105.0        |
| 013      | user_03 | merchant_02 | 2023-03-05 13:15:00 | 250.0        |
| 014      | user_03 | merchant_01 | 2023-03-06 09:45:00 | 30.0         |
| 015      | user_03 | merchant_02 | 2023-03-06 10:00:00 | 90.5         |
+----------+---------+-------------+---------------------+--------------+

2 建表语句

CREATE TABLE t_user_pay (
  order_id VARCHAR(255),       -- 订单ID
  user_id VARCHAR(255),        -- 用户ID
  merchant_id VARCHAR(255),    -- 商户ID
  pay_time TIMESTAMP,          -- 支付时间
  pay_amount DOUBLE            -- 支付金额
);

INSERT INTO t_user_pay VALUES
  ('001', 'user_01', 'merchant_01', '2023-03-01 12:30:00', 50.0),
  ('002', 'user_01', 'merchant_01', '2023-03-01 13:45:00', 75.5),
  ('003', 'user_01', 'merchant_02', '2023-03-01 14:00:00', 100.0),
  ('004', 'user_01', 'merchant_03', '2023-03-02 09:15:00', 25.0),
  ('005', 'user_01', 'merchant_03', '2023-03-02 10:30:00', 150.25),
  ('006', 'user_01', 'merchant_01', '2023-03-02 11:00:00', 500.0),
  ('007', 'user_01', 'merchant_02', '2023-03-03 08:00:00', 80.0),
  ('008', 'user_02', 'merchant_01', '2023-03-03 09:30:00', 120.0),
  ('009', 'user_02', 'merchant_02', '2023-03-04 13:45:00', 65.0),
  ('010', 'user_02', 'merchant_03', '2023-03-04 14:00:00', 150.0),
  ('011', 'user_02', 'merchant_03', '2023-03-05 11:30:00', 20.0),
  ('012', 'user_02', 'merchant_03', '2023-03-05 12:00:00', 105.0),
  ('013', 'user_03', 'merchant_02', '2023-03-05 13:15:00', 250.0),
  ('014', 'user_03', 'merchant_01', '2023-03-06 09:45:00', 30.0),
  ('015', 'user_03', 'merchant_02', '2023-03-06 10:00:00', 90.5);

样例结果

在这里插入图片描述

3 题解

select
    user_id,
    merchant_id,
    min(pay_time) new_pay_time,
    sum(pay_amount) new_pay_amount
from
(select
		*,
	# 分别按照用户分组按照支付时间进行排序得到 u_rn,
	# 用户和商户分组按照支付时间排序u_m_rn
    row_number()over(partition by user_id order by pay_time) as u_rn,
    row_number()over(partition by user_id,merchant_id order by pay_time) as u_m_rn
from t_user_pay)t
group by user_id,merchant_id,u_rn-u_m_rn;