基于多categorical columns排列的销售聚合数据库构建及YoY差异分析需求

一、核心思路 #

• 生成5个分类列的所有排列组合（共120种）
• 结合年份维度对每种排列分组聚合销售额（用于后续YoY计算）
• 存储聚合结果（适配数据规模增长，推荐列式数据库或分区表）
• 计算各聚合维度的YoY差异，排序定位最大差异来源
• 基于预设规则生成自动化洞察注释

二、Python实现示例 #

1. 依赖库安装 #

pip install pandas itertools sqlalchemy

2. 代码实现 #

import pandas as pd
import itertools
from sqlalchemy import create_engine

# 读取原始数据（需包含Year、5个分类列及Sales字段）
df = pd.read_csv("sales_data.csv")

# 定义分类列列表
cat_cols = ["Business", "Product Type", "Vendor", "Region", "Country"]

# 生成所有排列组合
all_permutations = list(itertools.permutations(cat_cols))

# 存储所有聚合结果的列表
agg_results = []

# 遍历每个排列，分组聚合
for perm in all_permutations:
    # 分组维度：年份 + 当前排列的分类列
    group_cols = ["Year"] + list(perm)
    # 聚合销售额总和
    agg_df = df.groupby(group_cols, as_index=False)["Sales"].sum()
    # 添加排列标识列，方便后续溯源
    agg_df["perm_id"] = "_".join(perm)
    agg_results.append(agg_df)

# 合并所有聚合结果
final_agg = pd.concat(agg_results, ignore_index=True)

# 存储到数据库（以SQLite为例，可替换为PostgreSQL/ClickHouse等）
engine = create_engine("sqlite:///sales_aggregations.db")
final_agg.to_sql("sales_agg_permutations", engine, if_exists="replace", index=False)

# ----------------------
# YoY差异计算与洞察生成
# ----------------------
# 按排列标识和分类列计算上年销售额及YoY增长率
final_agg["prev_year_sales"] = final_agg.groupby(["perm_id"] + cat_cols)["Sales"].shift(1)
final_agg["yoy_growth"] = (final_agg["Sales"] - final_agg["prev_year_sales"]) / final_agg["prev_year_sales"] * 100

# 过滤无上年数据的行，按YoY绝对值排序取Top10差异
top_yoy_diff = final_agg.dropna(subset=["yoy_growth"]).sort_values(by="yoy_growth", key=abs, ascending=False).head(10)

# 自动化生成洞察
for idx, row in top_yoy_diff.iterrows():
    perm_desc = " -> ".join(row["perm_id"].split("_"))
    if row["yoy_growth"] > 0:
        insight = f"【增长洞察】在维度[{perm_desc}]下，{row['Year']}年销售额较上年增长{row['yoy_growth']:.2f}%，达到{row['Sales']:.2f}。"
    else:
        insight = f"【下滑洞察】在维度[{perm_desc}]下，{row['Year']}年销售额较上年下降{abs(row['yoy_growth']):.2f}%，当前为{row['Sales']:.2f}。"
    print(insight)

三、R实现示例 #

1. 依赖包安装 #

install.packages(c("dplyr", "itertools", "DBI", "RSQLite"))

2. 代码实现 #

library(dplyr)
library(itertools)
library(DBI)
library(RSQLite)

# 读取原始数据（需包含Year、5个分类列及Sales字段）
df <- read.csv("sales_data.csv")

# 定义分类列列表
cat_cols <- c("Business", "Product.Type", "Vendor", "Region", "Country")

# 生成所有排列组合
all_permutations <- permutations(n = length(cat_cols), r = length(cat_cols), v = cat_cols)

# 初始化聚合结果列表
agg_results <- list()

# 遍历每个排列分组聚合
for(i in 1:nrow(all_permutations)) {
    perm <- all_permutations[i, ]
    group_cols <- c("Year", perm)
    agg_df <- df %>%
        group_by(!!!syms(group_cols)) %>%
        summarise(Sales = sum(Sales, na.rm = TRUE), .groups = "drop") %>%
        mutate(perm_id = paste(perm, collapse = "_"))
    agg_results[[i]] <- agg_df
}

# 合并所有聚合结果
final_agg <- bind_rows(agg_results)

# 存储到数据库
con <- dbConnect(SQLite(), "sales_aggregations.db")
dbWriteTable(con, "sales_agg_permutations", final_agg, overwrite = TRUE)
dbDisconnect(con)

# ----------------------
# YoY差异计算与洞察生成
# ----------------------
final_agg <- final_agg %>%
    group_by(perm_id, !!!syms(cat_cols)) %>%
    mutate(prev_year_sales = lag(Sales),
           yoy_growth = (Sales - prev_year_sales)/prev_year_sales * 100) %>%
    ungroup()

# 取Top10最大YoY差异
top_yoy_diff <- final_agg %>%
    filter(!is.na(yoy_growth)) %>%
    arrange(desc(abs(yoy_growth))) %>%
    head(10)

# 自动化生成洞察
for(i in 1:nrow(top_yoy_diff)) {
    row <- top_yoy_diff[i, ]
    perm_desc <- paste(strsplit(row$perm_id, "_")[[1]], collapse = " -> ")
    if(row$yoy_growth > 0) {
        insight <- sprintf("【增长洞察】在维度[%s]下，%d年销售额较上年增长%.2f%%，达到%.2f。",
                          perm_desc, row$Year, row$yoy_growth, row$Sales)
    } else {
        insight <- sprintf("【下滑洞察】在维度[%s]下，%d年销售额较上年下降%.2f%%，当前为%.2f。",
                          perm_desc, row$Year, abs(row$yoy_growth), row$Sales)
    }
    print(insight)
}

四、性能优化建议（应对数据规模增长） #

• 数据库选型：使用列式数据库（如ClickHouse、Snowflake）或分区表（PostgreSQL分区、BigQuery分区），提升聚合查询速度
• 增量聚合：仅对新增年份或更新的数据重新聚合，避免全量计算
• 并行计算：Python用dask/swifter，R用future.apply实现并行聚合，缩短计算时间
• 缓存策略：将高频访问的聚合结果缓存到Redis等内存数据库

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