MySQL中提取用户历史购买商品并按日期倒序转列的实现

针对每个URI，提取对应用户（Name）此前购买的商品（Item），按购买时间倒序转为列，其中Item1为最近一次购买商品，Item2为次近，以此类推。

源数据表 #

Table 1 #

+--------+------------+-------+
|  URI   |    Date    | Name  |
+--------+------------+-------+
| Fred4  | 2023-04-05 | Fred  |
| Fred3  | 2023-04-01 | Fred  |
| Fred2  | 2023-03-15 | Fred  |
| Fred1  | 2023-03-06 | Fred  |
| Dave3  | 2023-05-22 | Dave  |
| Dave2  | 2023-05-11 | Dave  |
| Dave1  | 2023-05-03 | Dave  |
| Simon6 | 2023-05-20 | Simon |
| Simon5 | 2023-05-11 | Simon |
| Simon4 | 2023-04-21 | Simon |
| Simon3 | 2023-04-19 | Simon |
| Simon2 | 2023-04-12 | Simon |
| Simon1 | 2023-03-25 | Simon |
+--------+------------+-------+

Table 2 #

+--------+------------+--------+
|  URI   |    Date    |  Item  |
+--------+------------+--------+
| Fred4  | 2023-04-05 | Top    |
| Fred3  | 2023-04-01 | Shorts |
| Fred2  | 2023-03-15 | Band   |
| Fred1  | 2023-03-06 | Top    |
| Dave3  | 2023-05-22 | Shorts |
| Dave2  | 2023-05-11 | Shoes  |
| Dave1  | 2023-05-03 | Top    |
| Simon6 | 2023-05-20 | Shorts |
| Simon5 | 2023-05-11 | Band   |
| Simon4 | 2023-04-21 | Shorts |
| Simon3 | 2023-04-19 | Top    |
| Simon2 | 2023-04-12 | Shoes  |
| Simon1 | 2023-03-25 | Shoes  |
+--------+------------+--------+

期望输出 #

+--------+------------+--------+--------+-------+-------+-------+
|  URI   |    Date    | Item1  | Item2  | Item3 | Item4 | Item5 |
+--------+------------+--------+--------+-------+-------+-------+
| Fred4  | 2023-04-05 | Shorts | Band   | Top   |       |       |
| Fred3  | 2023-04-01 | Band   | Top    |       |       |       |
| Fred2  | 2023-03-15 | Top    |        |       |       |       |
| Fred1  | 2023-03-06 | NULL   |        |       |       |       |
| Dave3  | 2023-05-22 | Shoes  | Top    |       |       |       |
| Dave2  | 2023-05-11 | Top    |        |       |       |       |
| Dave1  | 2023-05-03 | NULL   |        |       |       |       |
| Simon6 | 2023-05-20 | Band   | Shorts | Top   | Shoes | Shoes |
| Simon5 | 2023-05-11 | Shorts | Top    | Shoes | Shoes |       |
| Simon4 | 2023-04-21 | Top    | Shoes  | Shoes |       |       |
| Simon3 | 2023-04-19 | Shoes  | Shoes  |       |       |       |
| Simon2 | 2023-04-12 | Shoes  |        |       |       |       |
| Simon1 | 2023-03-25 | NULL   |        |       |       |       |
+--------+------------+--------+--------+-------+-------+-------+

当前问题与现有SQL #

目前仅能获取最近一次购买的商品，无法提取第2、3次及更早的历史商品。现有SQL如下：

with all_data as (
    SELECT t1.URI, t1.date, t1.name
    ,t2.Item as Item1, t2.Item as Item2, t2.Item as Item3, t2.Item as Item4, t2.Item as Item5
    FROM Table1 t1
    LEFT OUTER JOIN Table2 t2 on t1.name = t2.name and t1.date > t2.date
)
SELECT URI, date, name, Item1, Item2, Item3, Item4, Item5
from all_data
group by URI, date, name
order by URI, date, name
; 

使用环境：MySQL数据库，HeidiSQL客户端。

解决方案 #

要实现需求，需先为每个用户的历史购买记录按时间倒序编号，再通过条件聚合将编号对应的商品转为列。以下是适配MySQL的SQL代码：

WITH user_purchases AS (
    -- 为每个用户的历史购买记录按时间倒序编号，最近的为1
    SELECT 
        t1.URI AS target_uri,
        t1.date AS target_date,
        t1.name,
        t2.Item,
        ROW_NUMBER() OVER (PARTITION BY t1.name ORDER BY t2.date DESC) AS purchase_rank
    FROM Table1 t1
    LEFT JOIN Table2 t2 
        ON t1.name = t2.name 
        AND t2.date < t1.date -- 只取当前记录之前的购买
)
-- 条件聚合，将不同rank的商品转为对应列
SELECT 
    target_uri AS URI,
    target_date AS Date,
    MAX(CASE WHEN purchase_rank = 1 THEN Item END) AS Item1,
    MAX(CASE WHEN purchase_rank = 2 THEN Item END) AS Item2,
    MAX(CASE WHEN purchase_rank = 3 THEN Item END) AS Item3,
    MAX(CASE WHEN purchase_rank = 4 THEN Item END) AS Item4,
    MAX(CASE WHEN purchase_rank = 5 THEN Item END) AS Item5
FROM user_purchases
GROUP BY target_uri, target_date, name
ORDER BY name, target_date DESC;

代码说明 #

1. CTE user_purchases：通过ROW_NUMBER()窗口函数，按用户分组，对该用户所有早于当前记录日期的购买按时间倒序编号，编号1对应最近的历史购买。
2. 条件聚合：使用MAX(CASE ...)将不同编号的商品映射到Item1至Item5列，没有对应历史购买时会返回NULL，与期望输出一致。
3. 排序：最终按用户名和目标日期倒序排列，与示例输出的顺序匹配。

内容的提问来源于stack exchange，提问作者Davehusters