DuckDB 语义视图
一个 DuckDB 扩展，允许你一次性定义维度和指标，然后以任意组合进行查询。该扩展为你编写 GROUP BY 和 JOIN 逻辑。
受 Snowflake Semantic Views 启发，并作为可加载扩展适配于 DuckDB。
工作原理
你在一个或多个表上定义语义视图，声明：
- Dimensions -- 用于分组的列或表达式（region、category、
date_trunc('month', created_at)等） - Metrics -- 聚合函数（
sum(amount)、count(*)等） - Relationships -- 表之间的 PK/FK 连接路径，仅在查询需要时包含
然后你通过选择所需的维度和指标进行查询。扩展生成 SQL -- SELECT、FROM、JOIN、GROUP BY -- 并由 DuckDB 执行。
快速入门
CREATE TABLE orders (
id INTEGER, region VARCHAR, category VARCHAR,
amount DECIMAL(10,2)
);
CREATE SEMANTIC VIEW order_metrics AS
TABLES (
o AS orders PRIMARY KEY (id)
)
DIMENSIONS (
o.region AS o.region,
o.category AS o.category
)
METRICS (
o.revenue AS sum(o.amount),
o.order_count AS count(*)
);
-- Pick any combination of dimensions and metrics
SELECT * FROM semantic_view('order_metrics',
dimensions := ['region', 'category'],
metrics := ['revenue', 'order_count']
);
-- Dimensions only (distinct values)
SELECT * FROM semantic_view('order_metrics',
dimensions := ['region']
);
-- Metrics only (grand total)
SELECT * FROM semantic_view('order_metrics',
metrics := ['revenue']
);
-- WHERE works on the result
SELECT * FROM semantic_view('order_metrics',
dimensions := ['region'], metrics := ['revenue']
) WHERE region = 'East';只读数据库： 查询（
semantic_view、list_semantic_views、describe_semantic_view等）针对以read_only=True打开的数据库执行。CREATE/DROP/ALTER SEMANTIC VIEW需要可写数据库。请参阅 事务性 DDL 及限制 说明页面，了解先引导后重新打开的工作流程。
多表（主键/外键关系）
使用 PRIMARY KEY 和 REFERENCES 定义表之间的关系。仅会连接您请求的维度和指标所需的表。
CREATE TABLE customers (id INTEGER, name VARCHAR, tier VARCHAR);
CREATE TABLE products (id INTEGER, name VARCHAR, category VARCHAR);
CREATE TABLE orders (
id INTEGER, customer_id INTEGER, product_id INTEGER,
amount DECIMAL(10,2), region VARCHAR
);
CREATE SEMANTIC VIEW analytics AS
TABLES (
o AS orders PRIMARY KEY (id),
c AS customers PRIMARY KEY (id),
p AS products PRIMARY KEY (id)
)
RELATIONSHIPS (
order_customer AS o(customer_id) REFERENCES c,
order_product AS o(product_id) REFERENCES p
)
DIMENSIONS (
c.customer_name AS c.name,
p.product_name AS p.name,
o.region AS o.region
)
METRICS (
o.revenue AS sum(o.amount),
o.order_count AS count(*)
);
-- Only customers table is joined (products not needed)
SELECT * FROM semantic_view('analytics',
dimensions := ['customer_name'],
metrics := ['revenue']
);
-- Both customers and products tables are joined
SELECT * FROM semantic_view('analytics',
dimensions := ['customer_name', 'product_name'],
metrics := ['revenue']
);使用 explain_semantic_view 查看生成的 SQL：
SELECT * FROM explain_semantic_view('analytics',
dimensions := ['customer_name'],
metrics := ['revenue']
);┌──────────────────────────────────────────────────────────────┐
│ explain_output │
│ varchar │
├──────────────────────────────────────────────────────────────┤
│ -- Semantic View: analytics │
│ -- Dimensions: customer_name │
│ -- Metrics: revenue │
│ │
│ -- Expanded SQL: │
│ SELECT │
│ c.name AS "customer_name", │
│ sum(o.amount) AS "revenue" │
│ FROM "orders" AS "o" │
│ LEFT JOIN "customers" AS "c" ON "o"."customer_id" = "c"."id" │
│ GROUP BY │
│ 1 │
│ │
│ -- DuckDB Plan: │
│ ... │
├──────────────────────────────────────────────────────────────┤
│ 15+ rows │
└──────────────────────────────────────────────────────────────┘
FACTS（可复用的行级表达式）
将常见的行级计算命名一次，并在指标中引用它们。Facts 在展开时会被内联到指标表达式中。
子句方向： 与 Snowflake 类似，每个条目都是
alias.<logical_name> AS <sql_expression>— 名称位于AS之前，SQL 表达式位于其后。这与普通 SQLexpression AS alias相反。逻辑名称是你查询的对象（facts := ['net_price']），也是DESCRIBE返回的列。Fact 可以以其自身的列命名 —FACTS (s.unit_price AS s.unit_price)定义了一个直通 Factunit_price。相同的 方向也适用于DIMENSIONS和METRICS。
CREATE SEMANTIC VIEW sales AS
TABLES (
li AS line_items PRIMARY KEY (id)
)
FACTS (
li.net_price AS li.extended_price * (1 - li.discount),
li.tax_amount AS li.net_price * li.tax_rate
)
DIMENSIONS (
li.region AS li.region
)
METRICS (
li.total_net AS SUM(li.net_price),
li.total_tax AS SUM(li.tax_amount)
);事实可以引用其他事实 -- 扩展会按依赖顺序解析它们。
派生指标（指标组合）
组合不带表前缀的基础指标。扩展会替换底层表达式。
METRICS (
li.revenue AS SUM(li.net_price),
li.cost AS SUM(li.unit_cost),
profit AS revenue - cost,
margin AS profit / revenue * 100
);基数与扇形陷阱检测
关系基数是从被引用表上的 PRIMARY KEY / UNIQUE 约束中推断得出的——你无需对其进行标注。对表上已声明键的连接是多对一（或一对一）；可能导致聚合结果膨胀的连接会被检测为扇形陷阱，扩展会抛出错误，而不是返回不正确的结果。
RELATIONSHIPS (
li_to_order AS li(order_id) REFERENCES o,
order_to_customer AS o(customer_id) REFERENCES c
)o 和 c 表在 TABLES (... PRIMARY KEY (...)) 中声明了它们的键，因此每个关系的基数取决于目标键。（显式的 ONE TO ONE / ONE TO MANY / MANY TO ONE 注解已在 v0.5.4 中移除，现在会被拒绝。）
角色扮演维度 (USING RELATIONSHIPS)
当同一张表通过多个关系连接时（例如，机场既作为出发地又作为目的地），请在指标上使用 USING 来选择要使用的连接路径。
CREATE SEMANTIC VIEW flight_analytics AS
TABLES (
f AS flights PRIMARY KEY (flight_id),
a AS airports PRIMARY KEY (airport_code)
)
RELATIONSHIPS (
dep_airport AS f(departure_code) REFERENCES a,
arr_airport AS f(arrival_code) REFERENCES a
)
DIMENSIONS (
a.city AS a.city,
f.carrier AS f.carrier
)
METRICS (
f.departures USING (dep_airport) AS COUNT(*),
f.arrivals USING (arr_airport) AS COUNT(*)
);如果没有 USING，涉及模糊连接路径的查询将会报错。
DDL 参考
-- Full clause order (RELATIONSHIPS/FACTS optional; at least one of DIMENSIONS or METRICS required)
CREATE SEMANTIC VIEW name AS
TABLES (...)
RELATIONSHIPS (...)
FACTS (...)
DIMENSIONS (...)
METRICS (...);
CREATE OR REPLACE SEMANTIC VIEW name AS ...;
CREATE SEMANTIC VIEW IF NOT EXISTS name AS ...;
DROP SEMANTIC VIEW name;
DROP SEMANTIC VIEW IF EXISTS name;
DESCRIBE SEMANTIC VIEW name;
SHOW SEMANTIC VIEWS;文档
完整文档：anentropic.github.io/duckdb-semantic-views
包含入门教程、DDL 和查询参考、高级功能（FACTS、派生指标、角色扮演维度、扇形陷阱）的使用指南，以及架构说明。
构建
基于 Rust 构建，使用 DuckDB extension template for Rust。
您需要：Rust（stable）、just、make、Python 3。
just setup # one-time: installs dev tools, configures build
just build # debug build
cargo test # unit + property-based tests
just test-sql # SQL logic tests (needs just build first)
just test-all # everything
just lint # fmt + clippy + cargo-deny许可证
MIT