如何在大型MySQL表中高效计算每日滚动7天独立客户数

数据老陈

2026-7-10

问题背景

我有一个约38GB的MySQL表my_table，结构如下：

DESC my_table;
+------------------+----------+-----+-----+
| Field            | Type     | Null| Key |
+------------------+----------+-----+-----+
| date_available   | date     | NO  | PRI |
| transaction_date | datetime | NO  | PRI |
| customer_id      | char(36) | NO  | PRI |
| transaction_count| bigint   | YES |     |
+------------------+----------+-----+-----+

需求：为每个transaction_date计算过去7天的独立客户数，例如针对2020-03-07，要统计2020-03-01至2020-03-07（含）的所有唯一客户。

已尝试的方法及问题

GROUP_CONCAT子查询+Python处理
用以下查询获取每日独立客户，耗时约14分钟：

SELECT group_concat(distinct customer_id) AS cust_id, transaction_date 
FROM my_table 
WHERE transaction_date BETWEEN "2019-12-26" AND "2023-09-05" 
GROUP BY transaction_date;

将结果导入Python DataFrame后，尝试用explode展开客户ID时，因每日独立客户过多导致内存耗尽，Python内核崩溃：

with ENGINE.connect() as conn:
    conn.execute("SET SESSION group_concat_max_len = 999999999999999999")
    %time res = pd.read_sql(text(query), conn)

res['cust_id'] = res.explode('cust_id')[['cust_id', 'transaction_date']]

有逻辑缺陷的SQL查询
尝试直接在SQL中计算，但WHERE子句自引用导致条件始终为真：

SELECT group_concat(cust_id) AS concatenated_cust_ids,transaction_date 
FROM 
(
    SELECT group_concat(DISTINCT customer_id) AS cust_id, transaction_date 
    FROM my_table
    WHERE transaction_date BETWEEN '2019-12-26' AND '2023-09-05' 
    GROUP BY transaction_date
) AS intermediate
WHERE transaction_date BETWEEN DATE_SUB(transaction_date, INTERVAL 6 DAY) AND transaction_date 
GROUP BY transaction_date;

JOIN方案（性能担忧）
写出基于JOIN的查询，但担心38GB表的性能问题：

SELECT t1.transaction_date, COUNT(DISTINCT t2.customer_id) as unique_customers
FROM 
my_table t1 JOIN my_table t2 
ON t2.transaction_date BETWEEN DATE_SUB(t1.transaction_date, INTERVAL 6 DAY) 
AND t1.transaction_date
WHERE t1.transaction_date BETWEEN "2019-12-26" AND "2023-09-05"
GROUP BY t1.transaction_date
ORDER BY t1.transaction_date;

表的索引信息：

mysql> show indexes from my_table;
+----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| my_table |          0 | PRIMARY     |            1 | date_available   | A         |      333594 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          0 | PRIMARY     |            2 | transaction_date | A         |     2181937 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          0 | PRIMARY     |            3 | customer_id      | A         |   288982624 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | au_date_idx |            1 | transaction_date | A         |     1785462 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| my_table |          1 | au_date_idx |            2 | date_available   | A         |     1785462 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

寻求指导

无需担心性能问题、可直接在SQL中实现的优化方案；
若SQL不可行，如何在Python中处理大数据表以避免内存耗尽。

解决方案

一、SQL优化方案

1. 窗口函数方案（MySQL 8.0+推荐）

窗口函数是滚动统计的最优选择，效率远高于JOIN。先去重每日客户，再用滑动窗口计算：

WITH daily_unique_customers AS (
    SELECT 
        DATE(transaction_date) AS transaction_day,
        customer_id
    FROM my_table
    WHERE transaction_date BETWEEN "2019-12-26" AND "2023-09-05"
    GROUP BY DATE(transaction_date), customer_id
)
SELECT 
    transaction_day,
    COUNT(DISTINCT customer_id) OVER (
        ORDER BY transaction_day 
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS rolling_7d_unique_customers
FROM daily_unique_customers
GROUP BY transaction_day
ORDER BY transaction_day;

核心优化点：

先通过GROUP BY获取每日唯一客户，大幅减少窗口函数处理的数据量；
RANGE BETWEEN精准定义7天滑动窗口，匹配需求；
依赖现有au_date_idx索引，快速完成日期筛选和分组。

2. 优化版JOIN方案（兼容低版本MySQL）

如果无法使用窗口函数，可通过缩小数据集范围优化JOIN：

SELECT 
    DATE(t1.transaction_date) AS transaction_day,
    COUNT(DISTINCT t2.customer_id) AS rolling_7d_unique_customers
FROM (
    SELECT DISTINCT DATE(transaction_date) AS transaction_day
    FROM my_table
    WHERE transaction_date BETWEEN "2019-12-26" AND "2023-09-05"
) t1
JOIN (
    SELECT 
        DATE(transaction_date) AS transaction_day,
        customer_id
    FROM my_table
    WHERE transaction_date BETWEEN "2019-12-20" AND "2023-09-05" -- 提前6天覆盖窗口范围
    GROUP BY DATE(transaction_date), customer_id
) t2 ON t2.transaction_day BETWEEN DATE_SUB(t1.transaction_day, INTERVAL 6 DAY) AND t1.transaction_day
GROUP BY t1.transaction_day
ORDER BY t1.transaction_day;

核心优化点：

子查询t1仅提取需统计的日期，避免全表JOIN；
子查询t2提前去重每日客户，减少JOIN的数据集大小；
调整t2的日期范围，确保覆盖所有窗口起始日期，同时避免无效数据扫描。

索引补充建议

现有au_date_idx已支持日期筛选，可新增联合索引进一步加速去重和JOIN：

CREATE INDEX idx_trans_cust ON my_table(transaction_date, customer_id);

二、Python处理大数据表方案

如果SQL方案仍不适用，可采用分块读取+增量统计的方式规避内存问题：

1. 分块读取数据

用pandas.read_sql的chunksize参数分批次加载每日唯一客户数据：

query = """
SELECT DATE(transaction_date) AS transaction_day, customer_id
FROM my_table
WHERE transaction_date BETWEEN "2019-12-26" AND "2023-09-05"
GROUP BY DATE(transaction_date), customer_id
"""

chunk_size = 100000  # 根据本地内存调整大小
customer_records = []

with ENGINE.connect() as conn:
    for chunk in pd.read_sql(text(query), conn, chunksize=chunk_size):
        customer_records.append(chunk)

# 合并所有分块
daily_customers = pd.concat(customer_records, ignore_index=True)

2. 滚动窗口统计

用set高效维护7天内的客户集合，避免内存爆炸：

# 按日期排序
daily_customers = daily_customers.sort_values('transaction_day')
# 转换日期为datetime类型
daily_customers['transaction_day'] = pd.to_datetime(daily_customers['transaction_day'])

# 初始化结果和客户集合
rolling_results = {}
customer_set = set()
# 按日期分组获取每日客户列表
grouped = daily_customers.groupby('transaction_day')['customer_id'].apply(list)

for day, customers in grouped.items():
    # 添加当日客户到集合
    customer_set.update(customers)
    # 移除7天前的客户
    cutoff_day = day - pd.Timedelta(days=7)
    if cutoff_day in grouped.index:
        customer_set.difference_update(grouped[cutoff_day])
    # 记录当日滚动客户数
    rolling_results[day] = len(customer_set)

# 转换为DataFrame
result_df = pd.DataFrame.from_dict(rolling_results, orient='index', columns=['rolling_7d_unique_customers'])
result_df = result_df.sort_index()

优势：

分块读取避免一次性加载全量数据；
set的增删操作效率高，内存占用远低于展开所有客户ID。

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

如何在大型MySQL表中高效计算每日滚动7天独立客户数

已尝试的方法及问题 #

寻求指导 #

一、SQL优化方案 #

1. 窗口函数方案（MySQL 8.0+推荐） #

2. 优化版JOIN方案（兼容低版本MySQL） #

索引补充建议 #

二、Python处理大数据表方案 #

1. 分块读取数据 #

2. 滚动窗口统计 #