SELECT department, total_salary / total_employees AS avg_salary_calc
FROM semantic_view(
my_view,
DIMENSIONS department,
METRICS total_salary,
METRICS total_employees
);
使用说明
TABLES
TABLES
子句是必须的，不可省略。
DIMENSIONS
DIMENSIONS
和
METRICS
METRICS
均为可选。
被外键引用的逻辑表必须在
TABLES
TABLES
子句中先于引用方定义。
视图已存在时
CREATE SEMANTIC VIEW
CREATE SEMANTIC VIEW
报错，建议先执行
DROP SEMANTIC VIEW IF EXISTS
DROP SEMANTIC VIEW IF EXISTS
。
维度支持表达式（计算维度），如
YEAR(hire_date)
YEAR(hire_date)
，查询结果返回整数类型。
维度和指标命名支持限定名称（
alias.name
alias.name
）和短名称（
name
name
）两种形式，名称唯一时两者等效。
FILTERS
FILTERS
、
WITH SYNONYMS
WITH SYNONYMS
、
is_unique
is_unique
、
is_time
is_time
、
enum_values
enum_values
这些子句创建时都会被接受，但目前都不会出现在
DESC EXTENDED
DESC EXTENDED
的视图定义中，在纯 SQL 查询链路里没有可观测效果，更多是面向上层 AI/元数据工具的声明。尤其
FILTERS
FILTERS
不能传入
semantic_view()
semantic_view()
，所有过滤通过外层
WHERE
WHERE
子句实现。
示例
基础示例：单表语义视图
DROP SEMANTIC VIEW IF EXISTS doc_test.emp_dept_analysis;
CREATE SEMANTIC VIEW doc_test.emp_dept_analysis
TABLES (
depts AS doc_test.departments
PRIMARY KEY (dept_name),
emps AS doc_test.employees
PRIMARY KEY (id)
FOREIGN KEY (dept) REFERENCES depts (dept_name)
)
DIMENSIONS (
emps.employee_name AS emps.name
WITH SYNONYMS = ('员工姓名', 'staff name')
is_unique = true
COMMENT = '员工姓名',
emps.department AS emps.dept
COMMENT = '所在部门',
emps.hire_year AS YEAR(emps.hire_date)
is_time = true
COMMENT = '入职年份',
depts.manager_name AS depts.manager
COMMENT = '部门经理'
)
METRICS (
emps.total_employees AS COUNT(emps.id)
COMMENT = '员工总数',
emps.avg_salary AS AVG(emps.salary)
COMMENT = '平均薪资',
emps.max_salary AS MAX(emps.salary)
COMMENT = '最高薪资'
)
COMMENT = '员工部门分析语义视图';
说明：
depts
depts
和
emps
emps
是逻辑表别名，物理表分别是
doc_test.departments
doc_test.departments
和
doc_test.employees
doc_test.employees
FOREIGN KEY (dept) REFERENCES depts (dept_name)
FOREIGN KEY (dept) REFERENCES depts (dept_name)
：
emps.dept
emps.dept
（string）关联
depts.dept_name
depts.dept_name
（string），类型一致
hire_year
hire_year
是计算维度，通过
YEAR()
YEAR()
表达式从日期列派生
带维度元数据的语义视图
以下示例展示了
is_unique
is_unique
、
is_time
is_time
、
enum_values
enum_values
等维度元数据，以及
FILTERS
FILTERS
、
WITH SYNONYMS
WITH SYNONYMS
子句的写法。注意这些子句虽然创建时被接受，但目前在 SQL 链路中无可观测效果（见上方使用说明）：
DROP SEMANTIC VIEW IF EXISTS tpch_rev_analysis;
CREATE SEMANTIC VIEW tpch_rev_analysis
TABLES (
customers AS TPCH_AI.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
orders AS TPCH_AI.ORDERS
PRIMARY KEY (o_orderkey)
FOREIGN KEY (o_custkey) REFERENCES customers
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
line_items AS TPCH_AI.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
FOREIGN KEY (l_orderkey) REFERENCES orders
COMMENT = 'Line items in orders'
)
FILTERS (
customers.is_ny AS customers.c_city = 'New York'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name')
is_unique = true
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
is_time = true
enum_values = [date'2025-01-01', date'2025-06-01', date'2025-12-01']
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders'
)
COMMENT = 'Semantic view for revenue analysis';
