事实表的三种类型及特点

✅ 1. 事务型事实表（Transactional Fact Table）

🔹 特点：

• 每一行代表一次业务事件或事务（如一次销售、一次登录）。

• 粒度非常细（明细级）。

• 数据通常是可插入不可更新（即历史记录保留）。

🔹 示例：

order_id	customer_id	product_id	order_date	quantity	price
1001	001	A123	2025-07-28	2	100.0

🔹 应用场景：

• 电商订单

• 银行交易记录

• 网站点击日志

---

✅ 2. 周期快照型事实表（Periodic Snapshot Fact Table）

🔹 特点：

• 定期（如日、周、月）记录某一时点或汇总某一周期的度量数据。

• 每一行表示某个时间点的状态或某一周期内的汇总。

• 数据是定期刷新，不更新历史记录。

🔹 示例：

account_id	snapshot_date	total_balance	total_deposit
10001	2025-07-01	5000.00	200.00
10001	2025-08-01	5200.00	300.00

🔹 应用场景：

• 每日账户余额

• 每月销售汇总

• 周期性KPI统计

---

✅ 3. 累积快照型事实表（Accumulating Snapshot Fact Table）

🔹 特点：

• 记录一个完整过程中各阶段的状态，随着进展不断更新同一行。

• 适合跟踪生命周期型业务流程。

• 通常包含多个时间戳字段（如开始、处理、完成时间等）。

🔹 示例：

order_id	created_date	shipped_date	delivered_date	total_amount
1001	2025-07-01	2025-07-03	2025-07-05	250.00

🔹 应用场景：

• 订单处理流程跟踪

• 投诉处理流程

• 项目进度管理

---

📌 总结对比表

类型	数据记录方式	更新方式	适用场景
事务型	每行代表一个事件	插入，不更新	订单、交易、日志等
周期快照型	每行代表一个时间点	定期插入，不更新	月度汇总、库存快照等
累积快照型	每行代表一个流程	初始插入，后续更新	生命周期追踪

 

 

能否详细讲解一下累积快照型事实表的示例？

 

✅ 一、什么是累积快照型事实表？

累积快照型事实表用于表示一个业务流程从开始到结束的整个生命周期，每一行记录代表一个业务流程的完整进度。它会随着业务流程的推进不断更新状态和时间戳字段。

---

✅ 二、典型业务场景

比如：一个订单从下单 → 支付 → 发货 → 送达

• 我们希望追踪每个订单各个阶段的完成时间，以及整个流程花费的时间。

• 每个订单记录一行，并在不同阶段更新对应的时间字段。

---

✅ 三、示例：订单处理过程表

表名：order_process_fact

字段名	含义
order_id	订单ID（主键）
customer_id	客户ID
product_id	产品ID
order_created_date	订单创建时间
payment_date	支付完成时间
shipping_date	发货时间
delivery_date	订单送达时间
total_amount	订单总金额
order_status	当前订单状态（如：已创建、已支付、已发货、已完成）
total_days_to_complete	总耗时（单位：天，交付后计算）

 

---

示例数据：

order_id	customer_id	product_id	order_created_date	payment_date	shipping_date	delivery_date	total_amount	order_status	total_days_to_complete
1001	C001	P123	2025-07-01	2025-07-02	2025-07-03	2025-07-06	200.00	已完成	5
1002	C002	P456	2025-07-05	2025-07-06	NULL	NULL	99.00	已支付	NULL

 

注：随着订单状态推进，会不断更新该表的一行记录，直到完成。

---

✅ 四、设计特点

特点	说明
每个过程一个字段	每个关键事件用一个日期字段记录（比如：下单、支付、发货等）
一行记录整个生命周期	不像事务型事实表那样多行，而是汇总在一行中
数据会更新	不断更新字段值（时间戳、状态等）直到生命周期结束
可衍生指标	可以计算总耗时、各阶段耗时、转化率等

 

---

✅ 五、建模注意事项

1. 适合流程较固定的场景（如订单、请假、审批等），不能用于不确定多阶段的复杂流程。

2. 要求有稳定的生命周期节点，如“开始 → 中间阶段1 → 中间阶段2 → 完成”。

3. 为避免过多更新，可能会使用CDC技术或增量更新机制同步数据。

4. 为方便分析，应增加状态字段和派生指标字段，如耗时天数、平均阶段耗时等。

---

✅ 六、典型应用分析

使用该表可以进行如下分析：

• 订单平均完成时间、按阶段耗时统计

• 处于某阶段的订单数（如：待发货、运输中）

• 阶段转换率分析（如支付率、发货率、完成率）

• 异常检测（如“支付后超过3天未发货”）

 

下面我将基于这个累积快照型事实表 order_process_fact，演示常见分析需求的 SQL 查询，这些查询非常适合在数据仓库或BI报表中使用。

---

🔸前提：表结构回顾

CREATE TABLE order_process_fact (
  order_id              BIGINT,
  customer_id           STRING,
  product_id            STRING,
  order_created_date    DATE,
  payment_date          DATE,
  shipping_date         DATE,
  delivery_date         DATE,
  total_amount          DECIMAL(10,2),
  order_status          STRING,
  total_days_to_complete INT
);

 

---

✅ 一、查询：已完成订单的平均处理时间

SELECT
  AVG(total_days_to_complete) AS avg_days_to_complete
FROM
  order_process_fact
WHERE
  order_status = '已完成';

 

---

✅ 二、各阶段平均耗时（下单到支付、支付到发货、发货到送达）

SELECT
  AVG(DATEDIFF(payment_date, order_created_date)) AS avg_order_to_pay_days,
  AVG(DATEDIFF(shipping_date, payment_date))      AS avg_pay_to_ship_days,
  AVG(DATEDIFF(delivery_date, shipping_date))     AS avg_ship_to_delivery_days
FROM
  order_process_fact
WHERE
  delivery_date IS NOT NULL; -- 仅统计完整生命周期

 

---

✅ 三、订单当前状态分布（统计还在不同阶段的订单数）

SELECT
  order_status,
  COUNT(*) AS order_count
FROM
  order_process_fact
GROUP BY
  order_status;

 

---

✅ 四、统计每月完成的订单数量及总金额

SELECT
  DATE_FORMAT(delivery_date, '%Y-%m') AS delivery_month,
  COUNT(*) AS completed_orders,
  SUM(total_amount) AS total_revenue
FROM
  order_process_fact
WHERE
  delivery_date IS NOT NULL
GROUP BY
  DATE_FORMAT(delivery_date, '%Y-%m')
ORDER BY
  delivery_month;

 

---

✅ 五、识别长时间未发货的订单（例如支付后超过3天还未发货）

SELECT
  order_id,
  customer_id,
  payment_date
FROM
  order_process_fact
WHERE
  payment_date IS NOT NULL
  AND shipping_date IS NULL
  AND DATEDIFF(CURRENT_DATE, payment_date) > 3;

 

---

✅ 六、计算各阶段转化率（例如：下单 → 支付、支付 → 发货）

SELECT
  COUNT(CASE WHEN payment_date IS NOT NULL THEN 1 END) * 1.0 / COUNT(*) AS order_to_payment_rate,
  COUNT(CASE WHEN shipping_date IS NOT NULL THEN 1 END) * 1.0 / COUNT(CASE WHEN payment_date IS NOT NULL THEN 1 END) AS payment_to_shipping_rate,
  COUNT(CASE WHEN delivery_date IS NOT NULL THEN 1 END) * 1.0 / COUNT(CASE WHEN shipping_date IS NOT NULL THEN 1 END) AS shipping_to_delivery_rate
FROM
  order_process_fact;

 

---

✅ 七、TOP 5 最慢处理的订单（按总处理时间排序）

SELECT
  order_id,
  total_days_to_complete,
  order_created_date,
  delivery_date
FROM
  order_process_fact
WHERE
  total_days_to_complete IS NOT NULL
ORDER BY
  total_days_to_complete DESC
LIMIT 5;

 

---

✅ 八、客户维度的订单平均处理时间分析

SELECT
  customer_id,
  COUNT(*) AS order_count,
  AVG(total_days_to_complete) AS avg_days
FROM
  order_process_fact
WHERE
  total_days_to_complete IS NOT NULL
GROUP BY
  customer_id
ORDER BY
  avg_days DESC;

 

---

📌 小结

通过累积快照型事实表，可以很方便地进行：

• 生命周期分析

• 各阶段转化率统计

• 流程瓶颈识别

• 异常处理时效追踪

• 客户、产品、区域等维度的流程质量分析