SQL 如何新增列统计用户按购买日期排序的第几次购买记录

以下是不同场景下的实现方法：

1. 数据库SQL实现 #

你可以直接用窗口函数实现序号列生成和金额统计：

-- 第一步：生成带购买序号的中间表
WITH user_purchase_with_order AS (
    SELECT
        userid,
        `purchase date`,
        `purchase item`,
        -- 同一用户按购买日期升序生成购买序号，已经全局排序也建议明确指定排序规则避免出错
        ROW_NUMBER() OVER(PARTITION BY userid ORDER BY `purchase date` ASC) AS purchase_order,
        消费金额字段 -- 替换为你表中实际存储消费金额的字段名
    FROM 你的数据表名
)
-- 第二步：统计每个用户各次购买的总金额
SELECT
    userid,
    purchase_order AS 用户购买次数,
    SUM(消费金额字段) AS 本次消费总金额
FROM user_purchase_with_order
GROUP BY userid, purchase_order
ORDER BY userid, purchase_order;

2. Excel/WPS表格实现 #

• 假设你的表格表头在第一行，A列是userid、B列是购买日期、C列是购买商品，D列用来存储购买序号
• D2单元格输入公式：=COUNTIF(A$2:A2,A2)，下拉填充整列即可自动生成同一用户的1、2、3...顺序购买序号
• 需要统计各次购买金额的话，插入数据透视表，行区域依次选择userid、新增的购买序号列，值区域选择消费金额字段，聚合方式设置为求和即可

3. Python Pandas实现 #

import pandas as pd

# df为你已经读取完成、且提前按购买日期排序好的DataFrame
# 新增购买序号列，cumcount从0开始计数所以加1
df['purchase_order'] = df.groupby('userid').cumcount() + 1

# 统计每个用户各次购买的总消费金额
stats_result = df.groupby(['userid', 'purchase_order'])['消费金额字段'].sum().reset_index(name='本次消费总金额')

注意：如果原表没有单独存储消费金额字段，需要先关联商品维度表补全对应商品的金额后再做统计。

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