引言
你接到了一个电商客户，他们每天有 100500MB 的订单数据需要分析，但现有流程是用 Excel 手工处理，每周耗时 23 小时。客户不满意，你也不满意。
怎么办？
用 DuckDB 搭一个自动化销售分析引擎——把分析时间从小时级压缩到秒级，同时支持实时 BI 看板对接。这个项目你可以：
- 直接交付给客户作为 SaaS 工具，一次性收费或按月订阅
- 封装成内嵌分析模块，卖给更多商家，形成标准化产品
- 作为案例展示，建立个人品牌，吸引更多客户
本文基于 DuckDB 掘金实战频道 2026 年 8 月 30 日的推送内容，扩展为完整教程，包含全部可运行代码。
第一步：准备模拟数据
实际项目中，数据来源是客户的 PostgreSQL / MySQL / CSV 文件。为了演示，我们用 DuckDB 内置的 CTE 和随机函数生成一份接近真实的电商数据集，无需外部下载。
import duckdb
import pandas as pd
# 创建连接（内存模式，生产环境可换成 .duckdb 文件）
con = duckdb.connect(":memory:")
# 用 CTE 生成模拟订单数据
con.execute("""
CREATE TABLE orders AS
WITH dates AS (
SELECT DATE '2024-01-01' + n AS order_date
FROM unnest(generate_series(0, 364)) AS t(n)
),
categories AS (
SELECT unnest(['Electronics', 'Clothing', 'Home & Kitchen', 'Books', 'Sports']) AS cat
),
products AS (
SELECT
unnest(generate_series(1, 500)) AS product_id,
unnest(categories) AS category,
FLOOR(RANDOM() * 500 + 10)::DOUBLE AS price,
FLOOR(RANDOM() * 5 + 1) AS rating
FROM categories
LIMIT 500
),
orders_detail AS (
SELECT
p.product_id,
p.category,
p.price * (1 + (RANDOM() - 0.5) * 0.2) AS final_price,
d.order_date,
FLOOR(RANDOM() * 5 + 1)::INTEGER AS quantity,
FLOOR(RANDOM() * 1000000) AS order_id
FROM dates d
CROSS JOIN products p
WHERE RANDOM() < 0.15
)
SELECT * FROM orders_detail
""")
count = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
print(f"📦 订单数据量: {count:,} 条")
con.execute("CREATE INDEX idx_date ON orders(order_date)").fetchall()
💡 变现提示：真实项目中这里替换为连接客户的数据库或 CSV 文件，代码结构完全一致，只需换掉
CREATE TABLE那一步。
第二步：核心分析查询（秒级出结果）
2.1 每日销售趋势 + 周环比（窗口函数）
这是最基础也最常用的分析。用 DuckDB 的窗口函数 LAG() 轻松计算周环比：
daily_sales = con.execute("""
WITH daily AS (
SELECT
order_date,
SUM(final_price * quantity) AS revenue,
COUNT(DISTINCT order_id) AS orders,
AVG(final_price * quantity) AS avg_order_value
FROM orders
GROUP BY order_date
)
SELECT
order_date,
revenue,
orders,
ROUND(avg_order_value, 2) AS avg_order_value,
LAG(revenue, 7) OVER (ORDER BY order_date) AS revenue_last_week,
ROUND(
(revenue - LAG(revenue, 7) OVER (ORDER BY order_date))
/ NULLIF(LAG(revenue, 7) OVER (ORDER BY order_date), 0) * 100, 2
) AS WoW_change_pct
FROM daily
ORDER BY order_date
""").df()
print(daily_sales.tail(7))
# 输出最近一周的销售趋势，自动计算周环比
关键技巧：DuckDB 的窗口函数在百万行数据上通常在 100ms 内完成，比 Pandas 快 10~50 倍。这是因为 DuckDB 使用的是列式存储和向量化执行引擎，而 Pandas 是行式处理。
2.2 品类贡献度 + 帕累托分析（二八定律）
告诉客户"哪 20% 的品类贡献了 80% 的收入"——这是最有价值的洞察之一。用 CTE + 窗口函数实现：
category_pareto = con.execute("""
WITH cat_rev AS (
SELECT
category,
SUM(final_price * quantity) AS total_revenue,
ROUND(
SUM(final_price * quantity) * 100.0
/ SUM(SUM(final_price * quantity)) OVER (), 2
) AS revenue_pct
FROM orders
GROUP BY category
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY total_revenue DESC) AS rn,
SUM(total_revenue) OVER (ORDER BY total_revenue DESC
ROWS UNBOUNDED PRECEDING) AS cumulative_revenue
FROM cat_rev
)
SELECT
category,
total_revenue,
revenue_pct,
rn,
ROUND(cumulative_revenue / SUM(total_revenue) OVER () * 100, 1) AS cumulative_pct
FROM ranked
ORDER BY total_revenue DESC
""").df()
print(category_pareto.to_string(index=False))
这个查询可以直接用于给客户的「品类策略建议报告」。
2.3 用户生命周期价值（LTV）分层
用户分层是付费订阅服务的核心功能。用 PERCENTILE_CONT 将用户分为青铜/白银/黄金/白金四个层级：
ltv = con.execute("""
WITH user_metrics AS (
SELECT
FLOOR(RANDOM() * 5000) + 1 AS user_id,
COUNT(DISTINCT order_id) AS total_orders,
SUM(final_price * quantity) AS lifetime_value,
MIN(order_date) AS first_order_date,
MAX(order_date) AS last_order_date,
DATEDAY(MAX(order_date)) - DATEDAY(MIN(order_date)) AS active_days
FROM orders
GROUP BY user_id
HAVING total_orders >= 1
)
SELECT
CASE
WHEN lifetime_value >= PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY lifetime_value) THEN 'Platinum'
WHEN lifetime_value >= PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY lifetime_value) THEN 'Gold'
WHEN lifetime_value >= PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lifetime_value) THEN 'Silver'
ELSE 'Bronze'
END AS tier,
COUNT(*) AS user_count,
ROUND(AVG(lifetime_value), 2) AS avg_ltv,
ROUND(SUM(lifetime_value), 2) AS total_revenue,
ROUND(AVG(total_orders), 2) AS avg_orders
FROM user_metrics
GROUP BY 1
ORDER BY avg_ltv DESC
""").df()
print(ltv.to_string(index=False))
📌 变现点：LTV 分层稍作改造就能嵌入到客户的 CRM 系统中，这是付费订阅服务的核心功能。
第三步：封装为可复用模块
把上面的查询封装成 Python 类，方便一键调用：
class DuckDBSalesEngine:
"""电商销售分析引擎，一次初始化，无限复用"""
def __init__(self, data_path: str = ":memory:"):
self.con = duckdb.connect(data_path)
def load_csv(self, table_name: str, file_path: str):
"""自动推断 schema 并导入 CSV"""
self.con.execute(
f"CREATE TABLE {table_name} AS SELECT * FROM read_csv_auto('{file_path}')"
)
def daily_trend(self, days: int = 30) -> pd.DataFrame:
return self.con.execute("""
WITH daily AS (
SELECT order_date,
SUM(final_price * quantity) AS revenue,
COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY order_date
)
SELECT order_date, revenue, orders,
LAG(revenue, 7) OVER (ORDER BY order_date) AS prev_week_rev,
ROUND((revenue - LAG(revenue,7) OVER (ORDER BY order_date))
/ NULLIF(LAG(revenue,7) OVER (ORDER BY order_date),0) * 100, 2) AS wow_pct
FROM daily
WHERE order_date >= (SELECT MAX(order_date) - INTERVAL '{days}' DAY FROM daily)
ORDER BY order_date
""".format(days=days)).df()
def category_pareto(self) -> pd.DataFrame:
return self.con.execute("""
WITH cat_rev AS (
SELECT category,
SUM(final_price * quantity) AS total_revenue
FROM orders GROUP BY category
),
ranked AS (
SELECT *,
SUM(total_revenue) OVER (ORDER BY total_revenue DESC
ROWS UNBOUNDED PRECEDING) AS cum_rev
FROM cat_rev
)
SELECT category, total_revenue,
ROUND(total_revenue/SUM(total_revenue) OVER()*100, 2) AS pct,
ROUND(cum_rev/SUM(total_revenue) OVER()*100, 1) AS cum_pct
FROM ranked ORDER BY total_revenue DESC
""").df()
def export_report(self, output_path: str = "sales_report.parquet"):
"""导出分析结果为 Parquet 文件"""
trend = self.daily_trend()
pareto = self.category_pareto()
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
trend.to_excel(writer, sheet_name='Daily Trend', index=False)
pareto.to_excel(writer, sheet_name='Category Pareto', index=False)
print(f"✅ 报告已导出: {output_path}")
使用方式非常简单：
engine = DuckDBSalesEngine()
# engine.load_csv('orders', '/path/to/your/orders.csv')
trend = engine.daily_trend(30)
pareto = engine.category_pareto()
engine.export_report("weekly_report.xlsx")
第四步：接入 Streamlit 看板
让分析结果可视化，客户可以直接在浏览器中查看：
# streamlit_app.py
import streamlit as st
import duckdb
import pandas as pd
st.set_page_config(page_title="📊 电商销售分析", layout="wide")
st.title("🦆 DuckDB 电商销售分析引擎")
# 侧边栏：数据源选择
st.sidebar.header("📁 数据源")
data_source = st.sidebar.selectbox("选择数据源", ["示例数据", "上传 CSV"])
if data_source == "示例数据":
con = duckdb.connect(":memory:")
# ... 同前面的模拟数据生成代码
else:
uploaded = st.sidebar.file_uploader("上传 CSV", type=["csv"])
if uploaded:
df = pd.read_csv(uploaded)
con = duckdb.connect(":memory:")
con.register("orders", df)
# 主界面：三个分析卡片
col1, col2, col3 = st.columns(3)
with col1:
st.metric("📈 今日销售额", "$12,450", "+8.3%")
with col2:
st.metric("🛒 今日订单", "342", "+5.1%")
with col3:
st.metric("💰 客单价", "$36.41", "-1.2%")
# 销售趋势图
st.subheader("📅 近 30 天销售趋势")
trend_df = con.execute("""
SELECT order_date,
SUM(final_price * quantity) AS revenue
FROM orders
GROUP BY order_date
ORDER BY order_date
""").df()
st.line_chart(trend_df.set_index('order_date')['revenue'])
# 品类帕累托
st.subheader("🏷️ 品类收入贡献（帕累托）")
pareto_df = con.execute("""
SELECT category,
SUM(final_price * quantity) AS revenue,
ROUND(SUM(final_price * quantity)*100.0/SUM(SUM(final_price * quantity)) OVER (), 1) AS pct
FROM orders GROUP BY category
""").df()
pareto_df = pareto_df.sort_values('revenue', ascending=False)
pareto_df['cum_pct'] = pareto_df['pct'].cumsum()
st.bar_chart(pareto_df.set_index('category')[['revenue', 'pct']])
# 导出按钮
if st.button("📥 导出分析报告"):
with pd.ExcelWriter("sales_report.xlsx", engine='openpyxl') as writer:
con.execute("SELECT * FROM orders").df().to_excel(writer, sheet_name='Raw Data', index=False)
st.success("✅ 报告已下载！")
运行：
pip install streamlit duckdb pandas openpyxl
streamlit run streamlit_app.py
DuckDB vs 传统方案对比
| 维度 | Excel 手工处理 | Pandas | DuckDB |
|---|---|---|---|
| 100万行数据处理 | ❌ 卡顿/崩溃 | ✅ 可处理（慢） | ✅ 秒级 |
| 内存占用 | 高（行式） | 高（行式） | 低（列式+向量化） |
| SQL 支持 | ❌ VBA 复杂 | ⚠️ 有限 | ✅ 完整 SQL |
| 多数据源连接 | ❌ 手动合并 | ⚠️ 需 ETL | ✅ 原生支持 |
| 部署难度 | 低 | 中 | 低（嵌入式） |
| 成本 | 人工成本高 | 免费 | 免费 |
💰 变现建议
这个项目有多种变现路径：
- SaaS 工具：将分析引擎封装为 Web 应用，按店铺收费（$29~$99/月），100 个客户就是 $2,900~$9,900/月
- 咨询服务：为单个电商客户提供定制分析报告，单次收费 $500~$2,000
- 模板销售：将 Streamlit 模板打包，在 Gumroad 或爱发电上出售，定价 $19~$49
- 内嵌模块：将 DuckDB 分析模块嵌入到客户的现有系统（Shopify 插件、ERP 等），按功能模块收费
关键提示：不要只卖"技术"，要卖"结果"——客户要的是"每天自动收到的销售报告"，不是"DuckDB 查询"。
📖 本文的完整版已在 duckdblab.org 发布，包含更详细的步骤和更多电商分析案例。