BI 公共数据集与指标核对

本例适用于 BI 工具教程。数据包含重复购买的客户、取消订单,以及同一订单的两笔退款,规模足够小,可以手工核算。

创建数据集

管理员在新的隔离数据库中执行下列 SQL。bi_tutorial 必须不存在,创建失败时不会覆盖已有数据。初始化只执行一次,BI 连接不要使用该管理员会话。

CREATE DATABASE bi_tutorial;
USE bi_tutorial;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  region VARCHAR(20)
);
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  ordered_at DATETIME,
  category VARCHAR(20),
  status VARCHAR(20),
  quantity INT,
  gross_amount DECIMAL(24,6),
  cost_amount DECIMAL(24,6)
);
CREATE TABLE refunds (
  refund_id INT PRIMARY KEY,
  order_id INT,
  refund_amount DECIMAL(24,6)
);

INSERT INTO customers VALUES (1,'East'),(2,'West'),(3,'East');
INSERT INTO orders VALUES
  (1,1,'2026-01-01 10:00:00','A','paid',2,100.000000,60.000000),
  (2,1,'2026-01-02 11:00:00','B','paid',4,200.000000,120.000000),
  (3,2,'2026-01-02 12:00:00','A','paid',1,50.000000,30.000000),
  (4,3,'2026-01-03 09:00:00','B','paid',2,80.000000,40.000000),
  (5,2,'2026-01-03 13:00:00','A','cancelled',1,999.000000,0.000000);
INSERT INTO refunds VALUES (1,1,10.000000),(2,1,5.000000),(3,3,5.000000);

CREATE TABLE order_metrics AS
SELECT o.order_id, o.customer_id, o.ordered_at, o.category, c.region,
       o.quantity, o.gross_amount, o.cost_amount,
       CAST(COALESCE(r.refund_amount,0) AS DECIMAL(24,6)) AS refund_amount,
       CAST(o.gross_amount-COALESCE(r.refund_amount,0)
            AS DECIMAL(24,6)) AS net_amount
FROM orders o
JOIN customers c ON c.customer_id=o.customer_id
LEFT JOIN (
  SELECT order_id, SUM(refund_amount) AS refund_amount
  FROM refunds GROUP BY order_id
) r ON r.order_id=o.order_id
WHERE o.status='paid';

order_metrics 是每个已支付订单一行的物理快照,不是实时视图。源数据变化后需要刷新或重建。先将退款按订单聚合,再关联订单,避免订单金额被重复计算。本例将所有已记录退款扣回原订单,不考虑退款发生日期,因此不是按退款日期计算的现金流指标。

创建只读连接用户

在数据集所属的 MatrixOne 账号内执行,先替换密码。示例角色和用户必须尚未存在。

CREATE ROLE bi_tutorial_reader;
GRANT SELECT ON TABLE bi_tutorial.* TO bi_tutorial_reader;
CREATE USER bi_reader IDENTIFIED BY 'REPLACE_WITH_A_UNIQUE_PASSWORD'
  DEFAULT ROLE bi_tutorial_reader;

以 bi_reader 重新连接,选择数据库 bi_tutorial,然后执行下列查询。MatrixOne 将权限授予角色,再由用户继承,不要替换成 MySQL 的 user@host 授权语法。参见 CREATE USER 和 GRANT。如果后续将物理表改成视图,需要显式授予 VIEW 权限;特定工具发现元数据时也可能需要额外权限。

总计指标

SELECT COUNT(*) AS paid_orders,
       COUNT(DISTINCT customer_id) AS buyers,
       SUM(gross_amount) AS gross_amount,
       SUM(refund_amount) AS refunds,
       SUM(net_amount) AS net_amount,
       SUM(net_amount-cost_amount) AS margin,
       SUM(quantity) AS units
FROM bi_tutorial.order_metrics;

paid_orders

buyers

gross_amount

refunds

net_amount

margin

units

4

3

430.000000

20.000000

410.000000

160.000000

9

分组指标

SELECT category, COUNT(*) AS paid_orders,
       COUNT(DISTINCT customer_id) AS buyers,
       SUM(net_amount) AS net_amount, SUM(quantity) AS units
FROM bi_tutorial.order_metrics
GROUP BY category ORDER BY category;

category

paid_orders

buyers

net_amount

units

A

2

2

130.000000

3

B

2

2

280.000000

6

分类客户数相加得到 4,但整体去重客户数是 3。客户 1 同时购买了 A 和 B,所以重叠分组的去重人数不能直接相加。

每日趋势与累计净额

SELECT DATE(ordered_at) AS day, SUM(net_amount) AS net_amount
FROM bi_tutorial.order_metrics
GROUP BY DATE(ordered_at) ORDER BY day;
WITH daily AS (
  SELECT DATE(ordered_at) AS day, SUM(net_amount) AS net_amount
  FROM bi_tutorial.order_metrics GROUP BY DATE(ordered_at)
)
SELECT day, net_amount,
       SUM(net_amount) OVER (
         ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_net
FROM daily ORDER BY day;

day

net_amount

cumulative_net

2026-01-01

85.000000

85.000000

2026-01-02

245.000000

330.000000

2026-01-03

80.000000

410.000000

加权比率

SELECT SUM(gross_amount)/NULLIF(SUM(quantity),0) AS unit_price,
       SUM(net_amount-cost_amount)/NULLIF(SUM(net_amount),0) AS margin_rate
FROM bi_tutorial.order_metrics;

单位价格为 430/9,约 47.777778;利润率为 160/410,约 0.390244,即 39.0244%。比率应使用汇总后的分子与分母。逐笔比率的平均值回答的是不同问题。NULLIF 让空集或零分母返回空值而不是报错。

编码与精确数值传输

SELECT '健康中文😀' AS label,
       CAST(1.123456 AS DECIMAL(24,6)) AS small_decimal,
       CAST(9007199254740993.123456 AS DECIMAL(24,6)) AS large_decimal;

请在工具返回或序列化数据之后核对 Unicode 文本及两个小数。9007199254740993.123456 超过 JavaScript 精确整数范围且含小数位,转换为二进制浮点数会丢失信息。SQL 转字符串能保留展示文本,但不等于验证了数值图表的聚合;改成整数最小货币单位也需要检查数值范围。

比较预览与完整结果前请核对行数。笔记本的表格文本格式也可能误读制表符或换行符,而数据库返回值本身正确。