语义视图关系建模与聚合粒度

语义视图通过外键关系自动处理表连接和聚合。关系怎么建，直接决定查询结果对不对——同样的数据，关系定义不同，"订单数""客户数"可能差几倍。本文用一组带边界数据的例子，说明外键关系如何影响 JOIN 和聚合粒度，以及哪些场景会被引擎拦截。

前置准备

全文示例共用以下四张表。数据特意构造了几种边界情况：一笔没有对应客户的孤儿订单、一个没有任何订单的客户、以及一个有多个地址的客户。

CREATE TABLE doc_customers (c_custkey INT, c_name STRING, c_city STRING); CREATE TABLE doc_orders (o_orderkey INT, o_custkey INT, o_totalprice DECIMAL(12,2), o_orderdate DATE); CREATE TABLE doc_line_items (l_orderkey INT, l_linenumber INT, l_qty INT); CREATE TABLE doc_addresses (a_custkey INT, a_type STRING); INSERT INTO doc_customers VALUES (1,'Alice','New York'),(2,'Bob','Boston'),(3,'Carol','New York'),(4,'Dave','LA'); INSERT INTO doc_orders VALUES (101,1,250.00,DATE'2025-01-01'),(102,1,150.00,DATE'2025-02-01'), (103,2,300.00,DATE'2025-01-15'),(104,3,500.00,DATE'2025-03-01'), (105,99,999.00,DATE'2025-04-01'); -- 客户 99 不存在：孤儿订单 INSERT INTO doc_line_items VALUES (101,1,5),(101,2,3),(103,1,7),(104,1,2); INSERT INTO doc_addresses VALUES (1,'home'),(1,'work'),(2,'home'),(3,'home'),(3,'work'),(3,'billing');

数据特征：Dave（客户 4）没有订单；订单 105 的客户号 99 在客户表里不存在；Alice 有 2 笔订单、2 个地址，Carol 有 1 笔订单、3 个地址。

语义视图定义如下。customers 下挂两条一对多分支（orders、addresses），orders 下再挂 line_items：

CREATE SEMANTIC VIEW doc_rel_analysis TABLES ( customers AS doc_customers PRIMARY KEY (c_custkey), orders AS doc_orders PRIMARY KEY (o_orderkey) FOREIGN KEY (o_custkey) REFERENCES customers, line_items AS doc_line_items PRIMARY KEY (l_orderkey, l_linenumber) FOREIGN KEY (l_orderkey) REFERENCES orders, addresses AS doc_addresses PRIMARY KEY (a_custkey, a_type) FOREIGN KEY (a_custkey) REFERENCES customers ) DIMENSIONS ( customers.customer_name AS customers.c_name, customers.customer_city AS customers.c_city ) METRICS ( orders.order_count AS COUNT(orders.o_orderkey), orders.order_total AS SUM(orders.o_totalprice), line_items.qty_total AS SUM(line_items.l_qty), addresses.addr_count AS COUNT(addresses.a_type) );

查询粒度由指标所在表驱动

按客户名分组、查订单指标时，结果不是"每个客户一行"，而是以订单表为事实表、把客户属性挂上去：

SELECT * FROM semantic_view( doc_rel_analysis DIMENSIONS customers.customer_name METRICS orders.order_count, orders.order_total ) ORDER BY customer_name;

+---------------+-------------+-------------+ | customer_name | order_count | order_total | +---------------+-------------+-------------+ | NULL | 1 | 999.00 | | Alice | 2 | 400.00 | | Bob | 1 | 300.00 | | Carol | 1 | 500.00 | +---------------+-------------+-------------+

两个关键现象：

  • 孤儿订单出现，但维度为 NULL：订单 105 的客户号 99 在客户表里不存在，它仍出现在结果中，
    customer_name
    customer_name
    为 
    NULL
    NULL
    。说明订单不会因为关联不上客户而被丢弃。
  • 无订单的客户不出现：Dave 没有任何订单，结果里没有他。客户属性是挂在订单事实上的，没有订单事实就没有对应行。

记住这个心智模型：查询的粒度由指标所在的表决定，维度表的属性沿外键关系挂上去。如果你需要"列出所有客户（含无订单的）"，应直接查客户表，而不是查语义视图的订单指标。

链路内扇出：一对多的正确聚合

当查询涉及一条关系链上的两级（customers→orders→line_items），引擎会在各自的粒度上计算指标，不会因为 JOIN 放大：

SELECT * FROM semantic_view( doc_rel_analysis DIMENSIONS customers.customer_name METRICS orders.order_count, line_items.qty_total ) ORDER BY customer_name;

+---------------+-------------+-----------+ | customer_name | order_count | qty_total | +---------------+-------------+-----------+ | NULL | 1 | NULL | | Alice | 2 | 8 | | Bob | 1 | 7 | | Carol | 1 | 2 | +---------------+-------------+-----------+

Alice 有 2 笔订单（101、102），订单 101 含 2 行明细（数量 5、3），订单 102 无明细。

order_count
order_count
是 2 而不是被明细行数放大成 3，
qty_total
qty_total
正确累加为 8。这正是语义层相对手写 JOIN 的价值：手写 
orders JOIN line_items
orders JOIN line_items
后对订单做 
COUNT
COUNT
会重复计数，语义视图自动按指标的原始粒度聚合。

孤儿订单 105（客户号 99 不存在）仍以 

customer_name = NULL
customer_name = NULL
出现：它有 1 笔订单，但没有任何明细行，所以 
qty_total
qty_total
为 
NULL
NULL
——子表指标对没有子行的父行返回 
NULL
NULL
，不是 0。需要 0 时在外层用 
COALESCE(qty_total, 0)
COALESCE(qty_total, 0)
。

多分支扇出（chasm trap）的自动处理

orders 和 addresses 是 customers 下两条独立的一对多分支。经典的 chasm trap（扇出陷阱）问题是：如果天真地把两个分支通过 customers 连成一张宽表，Alice 的 2 笔订单会和 2 个地址交叉成 4 行，订单数和地址数被同时放大。语义视图引擎会在各自粒度上分别聚合再对齐维度，把两个分支的指标放进同一次查询也能得到正确结果，不会放大：

SELECT * FROM semantic_view( doc_rel_analysis DIMENSIONS customers.customer_name METRICS orders.order_count, addresses.addr_count ) ORDER BY customer_name;

+---------------+-------------+------------+ | customer_name | order_count | addr_count | +---------------+-------------+------------+ | NULL | 1 | NULL | | Alice | 2 | 2 | | Bob | 1 | 1 | | Carol | 1 | 3 | +---------------+-------------+------------+

Alice 的 

order_count
order_count
是 2、
addr_count
addr_count
是 2，都没有被交叉放大成 4——引擎分别在订单粒度和地址粒度算好各自的聚合，再按 
customer_name
customer_name
对齐。孤儿订单 105 以 
NULL
NULL
出现，它没有对应地址，
addr_count
addr_count
为 
NULL
NULL
。

多分支能力对更深的组合同样成立。三条分支的指标一起取，也各自正确聚合：

SELECT * FROM semantic_view( doc_rel_analysis DIMENSIONS customers.customer_name METRICS orders.order_count, line_items.qty_total, addresses.addr_count ) ORDER BY customer_name;

+---------------+-------------+-----------+------------+ | customer_name | order_count | qty_total | addr_count | +---------------+-------------+-----------+------------+ | NULL | 1 | NULL | NULL | | Alice | 2 | 8 | 2 | | Bob | 1 | 7 | 1 | | Carol | 1 | 2 | 3 | +---------------+-------------+-----------+------------+

按非唯一维度分组时，跨分支聚合也在分组粒度上分别汇总。按城市分组（Alice、Carol 同在 New York）：

SELECT * FROM semantic_view( doc_rel_analysis DIMENSIONS customers.customer_city METRICS orders.order_count, addresses.addr_count ) ORDER BY customer_city;

+---------------+-------------+------------+ | customer_city | order_count | addr_count | +---------------+-------------+------------+ | NULL | 1 | NULL | | Boston | 1 | 1 | | New York | 3 | 5 | +---------------+-------------+------------+

New York 的 

order_count
order_count
是 3（Alice 2 + Carol 1）、
addr_count
addr_count
是 5（Alice 2 + Carol 3），两个分支各自在城市粒度汇总后对齐，互不干扰。

多列主键与多跳外键

line_items 用复合主键 

(l_orderkey, l_linenumber)
(l_orderkey, l_linenumber)
，并通过 line_items→orders→customers 两跳外键关联到客户。这类多列主键和多跳关系都受支持——前面"链路内扇出"按客户名查明细量，明细量就正确上卷到了客户粒度。

聚合粒度规则：上卷合法，下钻非法

外键把逻辑表串成一条粒度阶梯：父表粗、子表细（customers → orders → line_items，客户比订单粗、订单比明细粗）。指标能按哪些维度分组，由这条阶梯决定：

  • 上卷（roll-up）合法：定义在细粒度表的指标，可以按任意更粗的父层级分组，引擎自动沿外键 JOIN 汇总。明细指标 
    qty_total
    qty_total
    （定义在最细的 line_items）既能按 customer 分组，也能上卷到更粗的 city：

SELECT * FROM semantic_view( doc_rel_analysis DIMENSIONS customers.customer_city METRICS line_items.qty_total ) ORDER BY customer_city;

+---------------+-----------+ | customer_city | qty_total | +---------------+-----------+ | Boston | 7 | | New York | 10 | +---------------+-----------+

New York 汇总了 Alice（8）和 Carol（2）的明细量得 10，Boston 是 Bob 的 7。同一个 

qty_total
qty_total
在 customer、city 层级返回不同聚合，全局合计恒为 17。

  • 下钻（drill-down）非法：父表指标不能直接聚合子表的列。给 customers 表定义 
    SUM(orders.o_totalprice)
    SUM(orders.o_totalprice)
    这样的指标，创建时就报错：

CZLH-42000: Semantic analysis exception - cannot resolve column 'o_totalprice'

父表要基于子表列做聚合，必须先用 

FACTS
FACTS
把子表列恒等透传上来，指标再引用这个事实（见创建语义视图的"带 FACTS 的语义视图"）。透传后还能在父粒度做双层聚合，如 
AVG(SUM(子表列))
AVG(SUM(子表列))
求每个父行的子表均值。

  • 无子行的父行：有父行但没有对应子行时（如孤儿订单没有明细），子表指标返回 
    NULL
    NULL
    而非 0；需要 0 时外层用 
    COALESCE
    COALESCE
    。前面"链路内扇出"里孤儿订单的 
    qty_total = NULL
    qty_total = NULL
    就是这条规则。

外键约束

建模时注意两条硬约束：

  • 类型一致：外键列与被引用列的数据类型必须相同，否则创建报错。例如订单的 
    o_custkey
    o_custkey
    （int）若去引用客户的 
    c_name
    c_name
    （string），报 
    type int ... does not match type string
    type int ... does not match type string
    。当外键列与被引用表主键列名不同时，需显式指定引用列名，如 
    FOREIGN KEY (o_custkey) REFERENCES customers (c_custkey)
    FOREIGN KEY (o_custkey) REFERENCES customers (c_custkey)
    。
  • 定义顺序：被引用的逻辑表必须在 
    TABLES
    TABLES
    子句中先于引用方定义。

详见创建语义视图和语义视图能力与限制参考。

常见问题与对策

现象原因对策
维度值出现 NULL 行子表存在关联不上父表的孤儿行检查外键数据完整性；或在外层 
WHERE
WHERE
过滤 NULL
某些维度成员缺失该成员在指标表里没有事实行（如无订单的客户）需要全集时直接查维度表，不查语义视图指标
子表指标为 NULL（不是 0）父行没有对应子行（如孤儿订单没有明细）需要 0 时外层用 
COALESCE(指标, 0)
COALESCE(指标, 0)
父表指标报 cannot resolve column父表指标直接聚合了子表的列先用 
FACTS
FACTS
把子表列透传到父表，指标再引用透传的事实
跨表指标数值被放大手写 JOIN 才会双重计算用语义视图自动按指标粒度聚合，不要手写 JOIN

相关文档

高级查询用法
管理语义视图
Yunqi © 2024 Yunqi, Inc. All Rights Reserved.
联系我们
预约咨询
微信咨询
电话咨询
邮件咨询