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 转字符串能保留展示文本,但不等于验证了数值图表的聚合;改成整数最小货币单位也需要检查数值范围。
比较预览与完整结果前请核对行数。笔记本的表格文本格式也可能误读制表符或换行符,而数据库返回值本身正确。