MySQL统计多列中各SKU的出现次数及距上次请求时长

现有一张记录物品请求的表格，结构如下：

Date, Item1, Item2, Item3, Item4

Item列存储SKU编号（示例用物品名称替代），表内数据示例：

09/13/2022, Stapler, Tape, Glue,
10/02/2022, Screws, Nails, Hammer, Screwdriver
12/12/2022, Paint, Brush, Dropcloth, Roller
01/12/2023, Glue, Tape, Stapler
03/23/2023, Roller, Brush, Paint, Dropcloth
04/14/2023, Screwdriver, Paint, Glue, Screws

已知：

• 物品输入顺序无规律
• SKU种类超100种

需要统计每个SKU的出现次数，以及距上次请求的天数，期望输出格式如下：

Hammer, 2, n_days
Stapler, 2, n_days
Paint, 3, 22

用Python处理的话，步骤如下：

1. 将宽表转换为窄表：把每行的多个Item拆成单独的（日期，SKU）记录，过滤空值
2. 按SKU分组，收集所有请求日期并排序
3. 统计出现次数，计算最后两次请求的间隔天数（若仅出现一次，可标记为N/A）

代码实现 #

from datetime import datetime
from collections import defaultdict

# 示例数据
raw_data = [
    "09/13/2022, Stapler, Tape, Glue,",
    "10/02/2022, Screws, Nails, Hammer, Screwdriver",
    "12/12/2022, Paint, Brush, Dropcloth, Roller",
    "01/12/2023, Glue, Tape, Stapler",
    "03/23/2023, Roller, Brush, Paint, Dropcloth",
    "04/14/2023, Screwdriver, Paint, Glue, Screws"
]

date_format = "%m/%d/%Y"
sku_date_records = defaultdict(list)

# 解析数据，按SKU收集日期
for line in raw_data:
    segments = [s.strip() for s in line.split(",")]
    req_date = datetime.strptime(segments[0], date_format)
    # 过滤空的SKU条目
    skus = [sku for sku in segments[1:] if sku]
    for sku in skus:
        sku_date_records[sku].append(req_date)

# 计算统计结果
output = []
for sku, dates in sku_date_records.items():
    sorted_dates = sorted(dates)
    count = len(sorted_dates)
    # 计算间隔天数
    if count >= 2:
        days_since_last = (sorted_dates[-1] - sorted_dates[-2]).days
    else:
        days_since_last = "N/A"
    output.append(f"{sku}, {count}, {days_since_last}")

# 打印结果
for line in output:
    print(line)

输出示例（部分） #

Stapler, 2, 121
Paint, 3, 22
Glue, 3, 93

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