高级查询用法
semantic_view()
semantic_view()
的查询结果是普通的关系型结果集，可以像普通表一样用于子查询、CTE、JOIN 和 CTAS。
以下示例均基于
doc_test.emp_dept_analysis
doc_test.emp_dept_analysis
（定义见
创建语义视图）。
子查询
语义视图可作为子查询的数据来源，外层可对结果再次过滤或处理：
SELECT dept, avg_sal
FROM (
SELECT * FROM semantic_view(
doc_test.emp_dept_analysis,
DIMENSIONS emps.department,
METRICS emps.avg_salary
)
) t
WHERE avg_salary > 9000;
+-------------+--------------+
| dept | avg_sal |
+-------------+--------------+
| Engineering | 11500.000000 |
+-------------+--------------+
CTE（WITH 子句）
语义视图可放入 CTE，方便在同一查询中多次引用：
WITH dept_stats AS (
SELECT * FROM semantic_view(
doc_test.emp_dept_analysis,
DIMENSIONS emps.department,
METRICS emps.avg_salary,
METRICS emps.total_employees
)
)
SELECT *
FROM dept_stats
WHERE total_employees > 1
ORDER BY avg_salary DESC;
+-------------+--------------+-----------------+
| department | avg_salary | total_employees |
+-------------+--------------+-----------------+
| Engineering | 11500.000000 | 2 |
| Marketing | 8750.000000 | 2 |
+-------------+--------------+-----------------+
与普通表 JOIN
语义视图结果可以与普通表做进一步关联：
SELECT
sv.department,
sv.avg_salary,
d.manager
FROM semantic_view(
doc_test.emp_dept_analysis,
DIMENSIONS emps.department,
METRICS emps.avg_salary
) sv
JOIN doc_test.departments d ON sv.department = d.dept_name;
+-------------+--------------+---------+
| department | avg_salary | manager |
+-------------+--------------+---------+
| Engineering | 11500.000000 | Frank |
| Marketing | 8750.000000 | Grace |
| HR | 7500.000000 | Henry |
+-------------+--------------+---------+
CTAS（CREATE TABLE AS SELECT）
语义视图查询结果可以物化为普通表，用于进一步加工或导出：
CREATE TABLE doc_test.dept_salary_snapshot AS
SELECT * FROM semantic_view(
doc_test.emp_dept_analysis,
DIMENSIONS emps.department,
METRICS emps.total_employees,
METRICS emps.avg_salary,
METRICS emps.max_salary
);
INSERT INTO ... SELECT
同样支持将语义视图查询结果写入已有表：
INSERT INTO doc_test.dept_salary_snapshot
SELECT * FROM semantic_view(
doc_test.emp_dept_analysis,
DIMENSIONS emps.department,
METRICS emps.total_employees,
METRICS emps.avg_salary,
METRICS emps.max_salary
);
注意事项
相关文档
