Lightdash QueryBuilder 架构解析：从 MetricQuery 到 SQL 的两阶段生成管线-酒店常州论坛

Lightdash QueryBuilder 架构解析：从 MetricQuery 到 SQL 的两阶段生成管线

【免费下载链接】lightdashAgentic BI. Analytics at the speed of code ⚡️项目地址: https://gitcode.com/GitHub_Trending/li/lightdash

导读

本文以 Lightdash 后端 SQL 生成与变换模块为核心，剖析其 "一个门面 + 四个构建器" 的架构设计：QueryComposer门面如何端到端地负责度量查询（Metric Query）的 SQL 生成，MetricQueryBuilder/PivotQueryBuilder/SqlQueryBuilder/TotalQueryBuilder四个构建器各自承担的职责，以及透视（Pivot）SQL 中三种 CTE 管线的运行模式与度量排序锚点系统的底层原理。读完本文，你将掌握 Lightdash 从MetricQuery配置到最终可执行 SQL 的完整调用链、row_index/column_index两阶段透视机制的边界划分，以及如何在实际开发中正确使用这套门面而非手工拼装构建器。

本文面向的源码位于 packages/backend/src/utils/QueryBuilder 目录；关于从配置到渲染的完整透视流水线（config → SQL → transform → reshape → render），可进一步阅读 docs/pivoting.md。

架构总览：一个门面、四个构建器

Lightdash 的 SQL 生成代码被刻意收敛为一个清晰的层次结构。核心文档（CLAUDE.md）将其概括为：

门面（Facade）：QueryComposer负责编排上下文准备与下面各构建器，端到端地拥有度量 SQL 的生成流程；其子类SqlQueryComposer把 SQL 图表（SQL charts）折叠进同一个门面——它包装用户手写的 SQL 而不是编译度量查询，并共享同一条getSql()透视缝（pivot seam）。
四个构建器：
- MetricQueryBuilder——负责带联表（joins）的指标/维度 SQL；
- PivotQueryBuilder——负责把平面表（flat table）变换为带行/列索引的透视表 SQL；
- SqlQueryBuilder——负责 SQL 图表（带过滤与参数替换）；
- TotalQueryBuilder——负责把源查询变换为总计（grand total）、行/列总计与小计（row/column/subtotal）查询。

一个关键的设计事实是：PivotQueryBuilder并不真正"透视"数据。它生成的是给每一行打上row_index与column_index元数据标签的 SQL（通过DENSE_RANK()实现），真正的透视（把值展开成{field}_{aggregation}_{groupByValue}列）发生在下游的AsyncQueryService.runQueryAndTransformRows中。理解这一点是读懂整套管线的前提。

QueryComposer：度量 SQL 生成的总门面

QueryComposer的定位在 QueryComposer.ts 中写得很明确：它自己不生成 SQL，而是内部完成三件上下文准备工作，然后编排MetricQueryBuilder与PivotQueryBuilder：

保留参数合并（reserved-parameter merge）——把保留参数定义折叠进可用参数，再解析出保留参数的值（如 dateZoom 反映所选粒度），用户同名参数优先（shadow 保留参数）；
dateZoom 的 explore 重写——调用updateExploreWithDateZoom重写目标日期维度 SQL；
度量查询编译（compileMetricQuery）。

调用方应直接在调用点构造QueryComposer，而不是手工接线各构建器。官方推荐用法如下：

import { QueryComposer } from './QueryComposer'; const composer = new QueryComposer( { metricQuery, pivotConfiguration, // undefined for a flat query totalConfiguration, // undefined unless building a totals query (见下文) }, { explore, warehouseSqlBuilder, intrinsicUserAttributes, userAttributes, timezone, availableParameterDefinitions, parameters, dateZoom, pivotDimensions, // pivotItemsMap 覆盖 pivot 解析所依赖的 itemsMap // （默认取新编译出的字段）；pre-agg 场景传入源查询持久化的字段。 pivotItemsMap, continueOnError, useTimezoneAwareDateTrunc, columnTimezone, applyDateZoomToFilters, }, ); const compiled = composer.compile(); // 记忆化的 CompiledQuery（基础 SQL、字段、警告、参数） const sql = composer.getSql({ columnLimit }); // 设置 pivotConfiguration 时经 PivotQueryBuilder 包装，否则为基础 SQL

compile() 的模板方法缝（template-method seam）

compile()委托给一个受保护的、可覆盖的computeCompiled()方法，并对结果做记忆化（memoized）：

基类computeCompiled()通过getQueryBuilder()拿到MetricQueryBuilder，调用其compileQuery()，并用wrapSentryTransactionSync('QueryBuilder.buildQuery', ...)包裹以接入 Sentry 追踪；
子类（如SqlQueryComposer）覆盖computeCompiled()从不同输入构建基础CompiledQuery，而getSql()的透视管线被原样继承——这正是"门面 + 模板方法"的精髓：换输入、不换缝。

getSql()的逻辑同样体现了这种统一：先compile()，若没有pivotConfiguration直接返回基础 SQL；否则用compiledQuery.query构造PivotQueryBuilder，把pivotItemsMap ?? compiledQuery.fields作为 itemsMap 传入，最后还有一个finalizeSql()组合缝供组合结果集（composed result sets）附加断言或包装层。

Getter 表面：异步执行缝的唯一数据载体

QueryComposer还是查询/上下文数据穿过异步执行缝（async execute seam）的唯一载体：AsyncQueryService.executePreparedAsyncQuery直接从get*()getter 上读取 explore、metric query、fields、pivot、时区、参数、访问控制等，而不是传递散落的参数。几个不显然的 getter 行为：

getFields()会应用来自源查询的指标/维度格式覆盖（含 PoP 指标覆盖的基指标继承）；
getParameters()返回的是原始合并参数值（而非保留参数合并后的编译变体）；
getDisplayTimezone()只是一个上下文携带值（非编译输入），SQL 图表把它钉死为null。

dateZoom：explore 重写工具

updateExploreWithDateZoom（dateZoom.ts）是QueryComposer使用的唯一 explore 重写步骤。它接收源 explore/query、warehouse SQL builder、可用参数名列表以及可选的DateZoom，返回生效后的 explore 以及dateZoomApplied、dateZoomTargetFieldId两个元数据。

重写逻辑要点：

目标日期维度若未显式指定（dateZoom.xAxisFieldId为空），则从metricQuery.dimensions中找第一个可解析为基础时间维度的字段；
对标准粒度，用createDimensionWithGranularity生成带粒度的维度并replaceDimensionInExplore替换；对自定义粒度，则查找预先编译好的<baseDim>_<granularity>维度；
DATE 类型 + 亚日（sub-day）粒度会被跳过（没有时间部分无法缩放）；
默认只影响选中的维度；仅 underlying-data 路径需要设置applyDateZoomToFilters，让匹配的点击过滤器针对重写后的表达式编译。

文档明确建议：调用方应把dateZoom传给 composer，而不是自行调用该工具或直接修改 explore。

SqlQueryComposer：SQL 图表的统一门面

SQL 图表（SQL charts）运行的是用户手写的 SQL 而非编译度量查询，因此SqlQueryComposer（SqlQueryComposer.ts）从原始输入构建一切：

虚拟视图（virtual view）——用发现到的列通过createVirtualView创建sql_query_explorer虚拟视图（虚拟视图还持有生成的 time interval 维度，因此只选取timeInterval === undefined的维度作为 mockMetricQuery.dimensions）；
包装的SqlQueryBuilder——referenceMap（列引用 → 类型 + 方言引用 SQL）与方言配置均取自 warehouse client（字段引号、字符串引号、转义、startOfWeek、adapterType）；
mock 的MetricQuery元数据载体——默认limit: 500，用于回显给客户端；
Dashboard 路径的过滤器/排序——仅当dashboardFilters && tileUuid时，通过getDashboardFilterRulesForTileAndReferences提取该 tile 相关的维度过滤规则并写入SqlQueryBuilder的 filters，同时把dashboardSorts合并进 mock metricQuery。

随后它覆盖computeCompiled()，把包装后的用户 SQL 塑造成CompiledQuery。由于getSql()被继承，请求/配置中的pivotConfiguration会与度量查询走同一条缝。该门面被AsyncQueryService.prepareSqlChartAsyncQueryArgs用于三条 SQL 执行路径：raw SQL runner、已保存 SQL 图表、Dashboard SQL 图表。

import { SqlQueryComposer } from './SqlQueryComposer'; const composer = new SqlQueryComposer({ userSql, // 用户属性已替换后的用户 SQL columns, // 通过 LIMIT 1 探测查询发现的列 warehouseClient, pivotConfiguration, // 请求提供（SQL runner）或由图表配置推导 limit, parameters, dashboardFilters, // 仅 Dashboard SQL 图表路径 tileUuid, dashboardSorts, }); const compiled = composer.compile(); // 包装后的用户 SQL 作为基础 CompiledQuery const sql = composer.getSql({ columnLimit }); // 设置 pivotConfiguration 时做透视包装 const metricQuery = composer.getMetricQuery(); // mock 元数据载体（回显给客户端） const virtualView = composer.getExplore(); const appliedDashboardFilters = composer.getAppliedDashboardFilters();

MetricQueryBuilder：基础 SQL 构建器

MetricQueryBuilder（MetricQueryBuilder.ts）从Explore + MetricQuery构建基础 SQL：维度、指标、过滤器、联表、表计算，并通过 CTE 处理扇出保护（fan-out protection）与环比（period-over-period）比较。它通常由QueryComposer驱动而非直接构造，但也可独立使用：

import { MetricQueryBuilder } from './MetricQueryBuilder'; const builder = new MetricQueryBuilder({ explore, compiledMetricQuery, warehouseSqlBuilder, userAttributes, intrinsicUserAttributes, parameters, parameterDefinitions, timezone, }); const { query, fields, warnings } = builder.buildQuery();

从QueryComposer.getQueryBuilder()可以看到它被注入的完整上下文：除上述字段外，还包括pivotConfiguration、pivotDimensions、continueOnError、skipModelRequiredFilters、originalExplore（dateZoom 存在时保留原 explore 供字段查找）、dateZoomFilterTargetFieldId、useTimezoneAwareDateTrunc、columnTimezone、dataTimezone、totalConfiguration与queryExecutionContext。

PivotQueryBuilder：三种 CTE 管线模式

PivotQueryBuilder（PivotQueryBuilder.ts）包装一个平面 SQL 查询，为其添加row_index/column_index元数据。根据配置不同，它运行三种 CTE 管线模式：

简单模式（Simple）——无groupByColumns：original_query → group_by_query → SELECT with ORDER BY + LIMIT，无透视、无行列索引；
维度排序模式（Dimension sort）——有groupByColumns且按维度/索引列排序：original_query → group_by_query → pivot_query → filtered_rows → total_columns。pivot_query内联用DENSE_RANK计算row_index与column_index，无锚点 CTE；
度量排序模式（Metric sort）——按值列排序时，锚点窗口折叠进排名 CTE：

直接使用的 API 如下（PivotConfiguration类型定义见 packages/common/src/types/pivot.ts）：

import { PivotQueryBuilder } from './PivotQueryBuilder'; const pivotBuilder = new PivotQueryBuilder( query, // 平面 SQL（通常来自 MetricQueryBuilder） pivotConfiguration, // { indexColumn, valuesColumns, groupByColumns, sortBy } warehouseSqlBuilder, limit, itemsMap, ); const sql = pivotBuilder.toSql({ columnLimit: 100 });

度量排序锚点系统（Metric Sorting Anchor System）

当用户按指标排序时，行按该指标在第一个透视列（锚点列）中的值排序，而不是跨所有列取 MIN/MAX。锚点列由column_ranking确定（col_idx = 1）。这是"按最新月份排序列（DESC）时，每行显示最新值"这类用户体验的 SQL 基础。

锚点折叠进排名 CTE（PROD-8441）

列锚点（FIRST_VALUEper groupBy value，别名${ref}_ca_value）与行锚点（指标在锚点列处的值，别名${ref}_ra_value）分别被计算在column_ranking/row_ranking内部的嵌套子查询（别名g）中，而不是作为独立的{ref}_ca/{ref}_raCTE。这样group_by_query在度量排序路径上只被引用约 3 次（每个嵌套锚点扫描一次 +pivot_query一次），而不是约 6 次——对 Trino 这类内联（inlining）引擎的query.max-stage-count限制意义重大。同时它对 Databricks/Spark 更稳健：锚点值与排名 Window 位于同一次扫描内，内联器不会丢失跨 CTE 的列引用。anchor_column（以及每个指标的${ref}_anchor_column钉扎变体）仍然是独立 CTE，但它读的是column_ranking而非group_by_query。

Databricks/Spark 的预计算排名

Databricks 会内联 CTE 而非物化它们。当pivot_query内联 DENSE_RANK 并引用锚点列时，Spark 无法跨内联 CTE 边界解析这些引用。修复方案是让row_ranking与column_ranking成为自包含 CTE（各自折叠自己的锚点扫描），pivot_query只 JOIN 预计算结果（通过getNullSafeEqualJoinSql做空安全等值连接）。当"度量排序 + 索引列存在"时该模式自动激活。

单扫描变体（无 CTE 物化的引擎）

Trino、Athena 等引擎从不物化 CTE，每次引用group_by_query都会重跑其整条血缘（基础扫描 + 过滤 + GROUP BY）。3-CTE 预计算形式会引用它 3 次、基础数据被扫描约 4 次。对这些引擎，度量排序路径会把column_ranking+anchor_column+row_ranking折叠成一个自包含的pivot_query，只扫描group_by_query一次：列锚点、列排名、行锚点、行排名变成堆叠在单次嵌套扫描上的窗口函数栈，而不是分开的 CTE 再 JOIN 回来。这在语义上等价——每个锚点值在其组合内是常量，去掉SELECT DISTINCT不影响排名。该变体由能力开关warehouseSqlBuilder.supportsCteMaterialization()返回false门控（默认true），而非按适配器类型硬编码，因此包括 Databricks/Spark（会复用计算出的 CTE 结果）在内的其他方言保持 3-CTE 形式不变。

锚点值别名与标识符长度

折叠后的锚点值列保持${ref}_ca_value/${ref}_ra_value别名（针对嵌套派生表g解析）。_ca/_ra后缀刻意保持简短，以在字段引用很长时仍落在 Postgres 63 字符标识符上限内；硬编码的 CTE 名（row_ranking、pivot_query等）无需加引号。

钉扎排序（pinned sorts）与 sort-only 表计算

当某个排序带pivotValues（把锚点钉到具体透视列）时，共享的anchor_column升级为每个指标的${ref}_anchor_column，使两个指标可独立钉到不同列；锚点等值谓词通过过滤器编译器renderFilterRuleSql生成，保证布尔/日期/时间戳钉扎在各仓库上输出类型正确的 SQL，null钉扎短路为IS NULL；
未钉扎的 sort-only（不显示的）表计算排序使用行级语义——跨所有透视列取 MAX——因为把按 (index, group) 的计算锚定到单个组，会让该组不存在的每个索引元组得到 NULL。显示的、带pivotValues的显式钉扎排序以及指标排序保持锚点语义。

脚本语句处理（sqlScript.ts）

用户 SQL 可以是一个脚本，其最后一条语句才是查询（例如 BigQuery 的DECLARE/SET变量）。直接把脚本包进original_query AS (...)是非法的 SQL，因此：

PivotQueryBuilder的构造函数通过parseSqlScript把开头的DECLARE/SET语句切分出来，toSql()把它们输出到WITH子句之上，使声明的变量仍在作用域内；
Dashboard 过滤生效时，SqlQueryBuilder在把最终查询包进FROM (...)前执行同样的切分，并在透视阶段重新拼接 prelude；
不以DECLARE/SET开头的 SQL 原样通过；以它们开头但后续还有其他非声明语句的脚本会抛出ParameterError（提示"图表只能由最后一条语句之前全部是 DECLARE 或 SET 的 SQL 生成"）。

parseSqlScript的切分器 sqlScript.ts 会正确跳过字符串、三引号、引号标识符与注释内的分号，并处理只有注释的语句（把它们挂到相邻语句上），保证切分稳健。

SqlQueryBuilder：SQL 图表的过滤与参数替换

SqlQueryBuilder（SqlQueryBuilder.ts）负责 SQL 图表的查询构建——用户手写 SQL + 过滤 + 参数替换：

import { SqlQueryBuilder } from './SqlQueryBuilder'; const builder = new SqlQueryBuilder( { referenceMap, select, from: { name, sql: userSql }, filters, parameters, limit, }, warehouseConfig, ); const { sql, parameterReferences } = builder.getSqlAndReferences();

参数替换实现位于 parameters.ts（支持 safe 与 raw 两种模式），SQL 解析、排序辅助与联接工具位于 utils.ts。

两阶段透视：标记与展开的边界

PivotQueryBuilder输出的行只带row_index+column_index标签。真正的透视——把值展开成{field}_{aggregation}_{groupByValue}列——发生在 AsyncQueryService.ts 的runQueryAndTransformRows（其注释标明代码取自ProjectService.pivotQueryWorkerTask）：它流式读取结果，并在row_index变化时执行透视变换。这一边界让 SQL 层保持纯净（只做标记），把有状态的展开逻辑隔离到执行服务中。

columnLimit通过calculateMaxColumnsPerValueColumn换算为每个值列的最大列数（columnLimit / valuesColumns.length，metricsAsRows时不除），并以column_index <= maxColumnsPerValueColumn的过滤下推到total_columns最终 SELECT；row_limit默认DEFAULT_PIVOT_ROW_LIMIT = 500，作用于filtered_rows的row_index <= rowLimit。

TotalQueryBuilder 与 Totals 模式

门面转发，构建器全权负责

设置totalConfiguration: { kind, subtotalDimensions }即可构建 totals 查询。QueryComposer只做转发——totals 完全是MetricQueryBuilder的职责：在其构造函数中，构建器把（编译后的）源查询 + 透视配置按请求粒度（grandTotal/columnTotal/rowTotal/columnSubtotal/rowSubtotal）折叠，通过TotalQueryBuilder得到折叠后的查询并内部编译，同时保留原始查询作为内嵌源（embedded source）。生效的（折叠后的）查询/透视通过构建器的getEffectiveMetricQuery()/getEffectivePivotConfiguration()暴露；composer 的getMetricQuery()/getPivotConfiguration()委托给它们，使路由、请求回显与响应都使用折叠形态。dateZoom 在这里保持惰性——它针对源查询的维度，而折叠后的 totals 查询通常不再选中这些维度。

这是executeAsyncCalculateTotalFromQueryHistory（AsyncQueryService.ts）路径使用的方式，而不是在调用点手工折叠。

互斥不变式

totalConfiguration与dashboardFilters互斥。totals 重放的是已持久化在查询历史中的查询，再次应用源 dashboard 过滤器会改变其语义。AsyncQueryService在executeAsyncMetricQuery入口准备 composer 之前断言该不变式。

TotalQueryBuilder 的变换逻辑

TotalQueryBuilder（TotalQueryBuilder.ts）不发射 SQL——它返回一个变换后的MetricQuery+PivotConfiguration，随后经正常路径（MetricQueryBuilder/PivotQueryBuilder）执行。它镜像MetricQueryBuilder的表面：构造时配置，然后调用compileQuery()：

import { TotalQueryBuilder } from './TotalQueryBuilder'; const { metricQuery, pivotConfiguration } = new TotalQueryBuilder({ metricQuery: source.metricQuery, pivotConfiguration: source.pivotConfiguration, // or null kind: 'columnTotal', subtotalDimensions, // subtotal 类粒度必填 }).compileQuery();

各粒度的关键语义（TotalQueryKind定义见 utils.ts）：

grandTotal：折叠为单行总计，去掉维度与 PoP 指标（PoP 需要选中时间维度才能成立）；
columnTotal：按groupByColumns重新聚合、去掉indexColumn；非透视源退化为 grandTotal；
rowTotal：按indexColumn重新聚合、清空groupByColumns（[]使PivotQueryBuilder走出透视 SQL 路径，返回平面形态）；非透视查询不支持行总计（每行已自带指标值），明确抛NotSupportedError；
columnSubtotal / rowSubtotal：要求至少一个subtotalDimensions，子总计把内部行维度折叠到子总计维度（同时保留透视列），输出平面（非透视）结果；
所有折叠都会剥离"阻塞性过滤器"（指标/表计算过滤器，见hasBlockingTotalFilters）与不可总计的表计算（模板、含 window/聚合的 SQL、公式聚合——getTotalableReferences判定），并过滤掉引用已删除字段的 valuesColumns（filterTotalsValuesColumns），防止PivotQueryBuilder聚合一个从未被选中的列导致整个 totals SQL 失败；折叠后若无可选值列则抛NotSupportedError('Nothing to total: ...')。

在源查询之上计算（sourceQuery 嵌入）

totals 有时需要原始查询的结果，而不只是折叠形态。在 totals 模式下，MetricQueryBuilder把原始编译查询保留为内部sourceQuery，作为顶层source_rowsCTE 只嵌入一次（其主体本身可含WITH链——与PivotQueryBuilder的original_query同款久经验证的模式；通过compileQueryAsCteBody()的子编译省略 ORDER BY / LIMIT 并跳过参数替换，使外层查询的单次替换同时覆盖两段文本），再由deriveSourceQueryUses依据 totals 种类与两个查询推导在其之上要计算什么。每个用途都从这一次嵌入派生，新增"基于原始查询计算"的功能应扩展推导逻辑而非再增加嵌入：

groupRestrictions——每个条目推导一个SELECT DISTINCT <join dims>CTE（source_dimension_groups，或 scope 为'visiblePage'时在visible_page_rows=source_rows+ 源查询的 ORDER BY/LIMIT 之上构建visible_dimension_groups），并通过getNullSafeEqualJoinSql追加INNER JOIN到dimensionsSQL.joins，从而到达每个原始扫描（扇出、distinct-metric、嵌套聚合与 totals CTE）。DISTINCT 使连接在任何粒度都无扇出。未被 totals 查询选中的自定义 bin 连接维度会补上 min/max CTE + CROSS JOIN。用途：scope'results'强制执行指标/表计算过滤器（PROD-8431——它们在源粒度编译为聚合后 WHERE，折叠的 totals 查询会剥离它们并改为限制原始行，由于聚合仍作用于原始行，所有指标类型的 totals 依然精确）；scope'visiblePage'把子总计钉到用户所见页面（PROD-7570——子总计响应恰好覆盖渲染出的粒度组，其继承的行数上限永远不会截断其中一个；子总计值仍是整组聚合）；
aggregations——在 totals 粒度上把源行列聚合进source_aggregationsCTE（粒度列以sa_为前缀避免别名冲突），强制走 post-agg CTE 路径，并把结果 LEFT JOIN（grand 粒度 CROSS JOIN）到最终 SELECT。用于totalMode: 'sum_of_rows'的表计算（PROD-8594）：其总计是行级值的 SUM，这也给无法对折叠行重新应用的 calc（窗口函数、模板）提供了正确总计。'formula'默认保留既有重算行为；'none'让 calc 完全退出总计（从折叠查询中剔除、永不聚合）。每种 calc 的total_mode持久化在saved_queries_version_table_calculations.total_mode。

测试与验证入口

整套实现有完备的测试支撑，可作为深入学习的起点：

PivotQueryBuilder.test.ts——覆盖全部 CTE 路径（简单、维度排序、度量排序、单扫描变体）；
MetricQueryBuilder.test.ts——构建器基础 SQL 行为；
TotalQueryBuilder.test.ts 与 QueryComposer.test.ts、SqlQueryComposer.test.ts——门面与 totals 变换；
sqlScript.test.ts 与 parameters.test.ts——脚本切分与参数替换；
快照目录 metricQueryBuilderSnapshots 保留生成的 SQL 快照，可用于观察实际产出。

小结

Lightdash 的 SQL 生成架构以"门面 + 模板方法 + 构建器"为核心：QueryComposer统一了上下文准备与透视缝，SqlQueryComposer让 SQL 图表与度量查询共享同一条getSql()管线，MetricQueryBuilder/PivotQueryBuilder/SqlQueryBuilder/TotalQueryBuilder各司其职，PivotQueryBuilder只负责用DENSE_RANK打上row_index/column_index标记、把真正的数据展开留给AsyncQueryService，而 totals 的折叠与"基于源查询计算"则由TotalQueryBuilder变换 +source_rows单次嵌入 + 按用途推导共同完成。这一分层让每条查询路径（Explorer、SQL Runner、Dashboard 图表、计算总计）都能复用同一条经过生产验证的 SQL 生成与透视管线。

【免费下载链接】lightdashAgentic BI. Analytics at the speed of code ⚡️项目地址: https://gitcode.com/GitHub_Trending/li/lightdash

创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考

企业官网建设流程全解析