SQL 分层建模CTE 嵌套三层以上就该拆视图了一、CTESQL 的段落标题写文章不用段落标题几百行文字挤在一起读者找不到重点。SQL 也一样——几百行查询挤在一起没有结构划分读的人只能从头到尾一行行啃。CTECommon Table Expression公用表表达式就是 SQL 的段落标题。它把一个复杂查询拆成多个命名子查询每个子查询有明确的职责最后用WITH串联起来-- 用CTE重构的分层查询 WITH -- 第一层清洗基础数据 cleaned_orders AS ( SELECT order_id, user_id, amount, channel, dt FROM orders WHERE status paid AND amount 0 AND dt 2026-01-01 ), -- 第二层按日渠道聚合 daily_channel_summary AS ( SELECT dt, channel, SUM(amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS buyer_cnt FROM cleaned_orders GROUP BY dt, channel ), -- 第三层计算占比和排名 channel_ranking AS ( SELECT dt, channel, gmv, order_cnt, buyer_cnt, gmv / SUM(gmv) OVER (PARTITION BY dt) AS gmv_share, RANK() OVER (PARTITION BY dt ORDER BY gmv DESC) AS gmv_rank FROM daily_channel_summary ) -- 最终查询只取前3渠道 SELECT * FROM channel_ranking WHERE gmv_rank 3 ORDER BY dt, gmv_rank;三个 CTE每层职责清晰清洗→聚合→计算。读代码的人不用从头到尾啃只需要看 CTE 名字就知道每步做了什么。二、三层嵌套的红线CTE 好用但不能无限嵌套。三层以上的 CTE 嵌套就像文章的章节标题套了四层——1.2.3.4.1——读者已经分不清层级关系了。flowchart TB subgraph 三层CTE[三层CTE结构清晰] A[第一层: 清洗] -- B[第二层: 聚合] B -- C[第三层: 排名] C -- D[最终查询] end subgraph 六层CTE[六层CTE失控了] E[清洗] -- F[聚合] F -- G[排名] G -- H[占比] H -- I[趋势] I -- J[对比] J -- K[最终查询] end style 三层CTE fill:#4ecdc4 style 六层CTE fill:#ff6b6b三层嵌套是红线原因有三个1. 可读性急剧下降每个 CTE 都要读懂输入来自哪一层、输出是什么格式。三层以内人脑能记住每层的职责超过三层你开始需要画图才能理解数据流向——这已经说明代码不够自解释了。2. 调试困难CTE 嵌套的查询调试只能从最内层一层层往外扒。如果第四层的 CTE 有 bug你要先理解前三层的输出格式才能定位问题。层数越多调试成本越高。3. 优化器负担加重部分数据库如 MySQL 8.0会把 CTE 物化为临时表。三层 CTE 意味着最多三个临时表存储和计算开销可控。六层 CTE 可能物化六个临时表中间结果堆叠内存和 I/O 压力急剧上升。-- 六层CTE嵌套的典型反面教材 WITH layer1 AS (...), -- 清洗 layer2 AS (...), -- 从layer1聚合 layer3 AS (...), -- 从layer2排名 layer4 AS (...), -- 从layer3计算占比 layer5 AS (...), -- 从layer4算趋势 layer6 AS (...), -- 从layer5对比历史 final AS ( -- 从layer6筛选 SELECT * FROM layer6 WHERE ... ) SELECT * FROM final;这段代码能跑但没人愿意读调试时更是噩梦。三、拆视图把嵌套变成平铺超过三层的 CTE 嵌套解决方法是拆视图View。视图把临时结果持久化每层独立维护、独立调试、独立复用。-- 第一层清洗视图持久化 CREATE VIEW v_cleaned_orders AS SELECT order_id, user_id, amount, channel, dt FROM orders WHERE status paid AND amount 0 AND dt 2026-01-01; -- 第二层聚合视图 CREATE VIEW v_daily_channel_summary AS SELECT dt, channel, SUM(amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS buyer_cnt FROM v_cleaned_orders GROUP BY dt, channel; -- 第三层排名占比视图 CREATE VIEW v_channel_ranking AS SELECT dt, channel, gmv, order_cnt, buyer_cnt, gmv / SUM(gmv) OVER (PARTITION BY dt) AS gmv_share, RANK() OVER (PARTITION BY dt ORDER BY gmv DESC) AS gmv_rank FROM v_daily_channel_summary; -- 最终查询简洁明了 SELECT * FROM v_channel_ranking WHERE gmv_rank 3 ORDER BY dt, gmv_rank;拆视图的好处不只是减少嵌套对比维度六层CTE拆视图可读性需要画图理解数据流每层视图独立可读调试一层层扒直接查某个视图的数据复用整个WITH块才能复用每个视图独立复用维护改一层影响全部改一个视图不影响其他权限控制无法对中间层设权限可以对每个视图设权限四、视图分层建模的最佳实践视图拆分不是随意切的——每层视图应该有明确的职责边界。SQL 分层建模借鉴了软件工程的分层思想flowchart TB subgraph DWD层[DWD层: 明细清洗层] A[v_cleaned_orders] -- B[v_cleaned_users] A -- C[v_cleaned_products] end subgraph DWS层[DWS层: 轻度汇总层] D[v_daily_channel_summary] -- E[v_daily_user_summary] D -- F[v_weekly_product_summary] end subgraph ADS层[ADS层: 应用分析层] G[v_channel_ranking] -- H[v_user_retention] G -- I[v_product_top10] end B -- D B -- E A -- D C -- F D -- G E -- H F -- I-- DWD层明细清洗 -- 清洗规则统一去掉脏数据标准化字段格式 CREATE OR REPLACE VIEW v_dwd_orders AS SELECT order_id, user_id, product_id, -- 标准化金额统一为元去掉负数 ABS(amount) AS amount, -- 标准化渠道统一命名 CASE WHEN channel IN (search, 搜索) THEN search WHEN channel IN (recommend, 推荐) THEN recommend WHEN channel IN (ads, 广告) THEN ads ELSE other END AS channel, -- 标准化状态 LOWER(status) AS status, dt FROM raw_orders WHERE amount 0 -- 去掉零金额 AND user_id IS NOT NULL -- 去掉无用户ID AND dt 2025-01-01; -- 去掉过期数据 -- DWS层轻度汇总 -- 按业务维度聚合消除明细层的大数据量 CREATE OR REPLACE VIEW v_dws_daily_channel AS SELECT dt, channel, SUM(amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS buyer_cnt, AVG(amount) AS avg_amount FROM v_dwd_orders WHERE status paid GROUP BY dt, channel; CREATE OR REPLACE VIEW v_dws_daily_user AS SELECT dt, user_id, SUM(amount) AS total_spent, COUNT(*) AS order_cnt, MIN(amount) AS min_amount, MAX(amount) AS max_amount FROM v_dwd_orders WHERE status paid GROUP BY dt, user_id; -- ADS层应用分析 -- 直接服务于看板和报告的最终指标 CREATE OR REPLACE VIEW v_ads_channel_top3 AS SELECT dt, channel, gmv, gmv_share, gmv_rank FROM ( SELECT dt, channel, gmv, gmv / SUM(gmv) OVER (PARTITION BY dt) AS gmv_share, RANK() OVER (PARTITION BY dt ORDER BY gmv DESC) AS gmv_rank FROM v_dws_daily_channel ) ranked WHERE gmv_rank 3; CREATE OR REPLACE VIEW v_ads_user_retention_7d AS SELECT cohort_dt, COUNT(DISTINCT user_id) AS cohort_size, COUNT(DISTINCT CASE WHEN active_dt cohort_dt 7 THEN user_id END) AS retained_7d, COUNT(DISTINCT CASE WHEN active_dt cohort_dt 7 THEN user_id END) / COUNT(DISTINCT user_id) AS retention_rate_7d FROM ( -- 首次活跃日期作为cohort SELECT first_dt AS cohort_dt, user_id, dt AS active_dt FROM v_dws_daily_user JOIN ( SELECT user_id, MIN(dt) AS first_dt FROM v_dws_daily_user GROUP BY user_id ) first ON v_dws_daily_user.user_id first.user_id ) cohort_data GROUP BY cohort_dt;三层架构的命名规则很重要——v_dwd_、v_dws_、v_ads_的前缀让你一眼看出视图属于哪一层不用看代码就能理解职责边界。实践要点规则说明DWD 层只做清洗不做聚合保持明细粒度让上层自由聚合DWS 层只聚合不计算排名聚合是确定的排名要看场景ADS 层只做最终计算直接看板/报告能用不做中间计算视图命名带层级前缀v_dwd_/v_dws_/v_ads_一眼分层ADS 层视图不超过一层 CTE如果 ADS 层还有两层 CTE说明 DWS 层没做够禁止跨层引用ADS 层只能引用 DWS 层不能直接引用 DWD 层禁止跨层引用是最重要的规则。ADS 层直接引用 DWD 层意味着绕过了汇总层明细数据量直接暴露给最终查询——这是性能隐患也是架构混乱的根源。五、总结SQL 分层建模的核心原则CTE 嵌套不超过三层超过就拆视图。三层是红线不是建议——超过三层可读性、调试性、性能都急剧恶化。分层建模的三层架构DWD明细清洗层只做清洗不做聚合保持明细粒度DWS轻度汇总层按业务维度聚合消除大数据量ADS应用分析层最终指标计算直接服务看板和报告关键实践视图命名带层级前缀一眼看出职责禁止跨层引用ADS 只引用 DWSDWS 只引用 DWDADS 层视图不超过一层 CTE否则说明 DWS 没做够每层视图独立调试——直接SELECT * FROM v_dws_daily_channel LIMIT 10检查中间结果CTE 是好的段落标题但文章不能只有标题没有章节。三层嵌套以上把 CTE 拆成视图——从嵌套变成平铺代码的可读性和可维护性就从量变到质变。
SQL 分层建模CTE 嵌套三层以上就该拆视图了一、CTESQL 的段落标题写文章不用段落标题几百行文字挤在一起读者找不到重点。SQL 也一样——几百行查询挤在一起没有结构划分读的人只能从头到尾一行行啃。CTECommon Table Expression公用表表达式就是 SQL 的段落标题。它把一个复杂查询拆成多个命名子查询每个子查询有明确的职责最后用WITH串联起来-- 用CTE重构的分层查询 WITH -- 第一层清洗基础数据 cleaned_orders AS ( SELECT order_id, user_id, amount, channel, dt FROM orders WHERE status paid AND amount 0 AND dt 2026-01-01 ), -- 第二层按日渠道聚合 daily_channel_summary AS ( SELECT dt, channel, SUM(amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS buyer_cnt FROM cleaned_orders GROUP BY dt, channel ), -- 第三层计算占比和排名 channel_ranking AS ( SELECT dt, channel, gmv, order_cnt, buyer_cnt, gmv / SUM(gmv) OVER (PARTITION BY dt) AS gmv_share, RANK() OVER (PARTITION BY dt ORDER BY gmv DESC) AS gmv_rank FROM daily_channel_summary ) -- 最终查询只取前3渠道 SELECT * FROM channel_ranking WHERE gmv_rank 3 ORDER BY dt, gmv_rank;三个 CTE每层职责清晰清洗→聚合→计算。读代码的人不用从头到尾啃只需要看 CTE 名字就知道每步做了什么。二、三层嵌套的红线CTE 好用但不能无限嵌套。三层以上的 CTE 嵌套就像文章的章节标题套了四层——1.2.3.4.1——读者已经分不清层级关系了。flowchart TB subgraph 三层CTE[三层CTE结构清晰] A[第一层: 清洗] -- B[第二层: 聚合] B -- C[第三层: 排名] C -- D[最终查询] end subgraph 六层CTE[六层CTE失控了] E[清洗] -- F[聚合] F -- G[排名] G -- H[占比] H -- I[趋势] I -- J[对比] J -- K[最终查询] end style 三层CTE fill:#4ecdc4 style 六层CTE fill:#ff6b6b三层嵌套是红线原因有三个1. 可读性急剧下降每个 CTE 都要读懂输入来自哪一层、输出是什么格式。三层以内人脑能记住每层的职责超过三层你开始需要画图才能理解数据流向——这已经说明代码不够自解释了。2. 调试困难CTE 嵌套的查询调试只能从最内层一层层往外扒。如果第四层的 CTE 有 bug你要先理解前三层的输出格式才能定位问题。层数越多调试成本越高。3. 优化器负担加重部分数据库如 MySQL 8.0会把 CTE 物化为临时表。三层 CTE 意味着最多三个临时表存储和计算开销可控。六层 CTE 可能物化六个临时表中间结果堆叠内存和 I/O 压力急剧上升。-- 六层CTE嵌套的典型反面教材 WITH layer1 AS (...), -- 清洗 layer2 AS (...), -- 从layer1聚合 layer3 AS (...), -- 从layer2排名 layer4 AS (...), -- 从layer3计算占比 layer5 AS (...), -- 从layer4算趋势 layer6 AS (...), -- 从layer5对比历史 final AS ( -- 从layer6筛选 SELECT * FROM layer6 WHERE ... ) SELECT * FROM final;这段代码能跑但没人愿意读调试时更是噩梦。三、拆视图把嵌套变成平铺超过三层的 CTE 嵌套解决方法是拆视图View。视图把临时结果持久化每层独立维护、独立调试、独立复用。-- 第一层清洗视图持久化 CREATE VIEW v_cleaned_orders AS SELECT order_id, user_id, amount, channel, dt FROM orders WHERE status paid AND amount 0 AND dt 2026-01-01; -- 第二层聚合视图 CREATE VIEW v_daily_channel_summary AS SELECT dt, channel, SUM(amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS buyer_cnt FROM v_cleaned_orders GROUP BY dt, channel; -- 第三层排名占比视图 CREATE VIEW v_channel_ranking AS SELECT dt, channel, gmv, order_cnt, buyer_cnt, gmv / SUM(gmv) OVER (PARTITION BY dt) AS gmv_share, RANK() OVER (PARTITION BY dt ORDER BY gmv DESC) AS gmv_rank FROM v_daily_channel_summary; -- 最终查询简洁明了 SELECT * FROM v_channel_ranking WHERE gmv_rank 3 ORDER BY dt, gmv_rank;拆视图的好处不只是减少嵌套对比维度六层CTE拆视图可读性需要画图理解数据流每层视图独立可读调试一层层扒直接查某个视图的数据复用整个WITH块才能复用每个视图独立复用维护改一层影响全部改一个视图不影响其他权限控制无法对中间层设权限可以对每个视图设权限四、视图分层建模的最佳实践视图拆分不是随意切的——每层视图应该有明确的职责边界。SQL 分层建模借鉴了软件工程的分层思想flowchart TB subgraph DWD层[DWD层: 明细清洗层] A[v_cleaned_orders] -- B[v_cleaned_users] A -- C[v_cleaned_products] end subgraph DWS层[DWS层: 轻度汇总层] D[v_daily_channel_summary] -- E[v_daily_user_summary] D -- F[v_weekly_product_summary] end subgraph ADS层[ADS层: 应用分析层] G[v_channel_ranking] -- H[v_user_retention] G -- I[v_product_top10] end B -- D B -- E A -- D C -- F D -- G E -- H F -- I-- DWD层明细清洗 -- 清洗规则统一去掉脏数据标准化字段格式 CREATE OR REPLACE VIEW v_dwd_orders AS SELECT order_id, user_id, product_id, -- 标准化金额统一为元去掉负数 ABS(amount) AS amount, -- 标准化渠道统一命名 CASE WHEN channel IN (search, 搜索) THEN search WHEN channel IN (recommend, 推荐) THEN recommend WHEN channel IN (ads, 广告) THEN ads ELSE other END AS channel, -- 标准化状态 LOWER(status) AS status, dt FROM raw_orders WHERE amount 0 -- 去掉零金额 AND user_id IS NOT NULL -- 去掉无用户ID AND dt 2025-01-01; -- 去掉过期数据 -- DWS层轻度汇总 -- 按业务维度聚合消除明细层的大数据量 CREATE OR REPLACE VIEW v_dws_daily_channel AS SELECT dt, channel, SUM(amount) AS gmv, COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS buyer_cnt, AVG(amount) AS avg_amount FROM v_dwd_orders WHERE status paid GROUP BY dt, channel; CREATE OR REPLACE VIEW v_dws_daily_user AS SELECT dt, user_id, SUM(amount) AS total_spent, COUNT(*) AS order_cnt, MIN(amount) AS min_amount, MAX(amount) AS max_amount FROM v_dwd_orders WHERE status paid GROUP BY dt, user_id; -- ADS层应用分析 -- 直接服务于看板和报告的最终指标 CREATE OR REPLACE VIEW v_ads_channel_top3 AS SELECT dt, channel, gmv, gmv_share, gmv_rank FROM ( SELECT dt, channel, gmv, gmv / SUM(gmv) OVER (PARTITION BY dt) AS gmv_share, RANK() OVER (PARTITION BY dt ORDER BY gmv DESC) AS gmv_rank FROM v_dws_daily_channel ) ranked WHERE gmv_rank 3; CREATE OR REPLACE VIEW v_ads_user_retention_7d AS SELECT cohort_dt, COUNT(DISTINCT user_id) AS cohort_size, COUNT(DISTINCT CASE WHEN active_dt cohort_dt 7 THEN user_id END) AS retained_7d, COUNT(DISTINCT CASE WHEN active_dt cohort_dt 7 THEN user_id END) / COUNT(DISTINCT user_id) AS retention_rate_7d FROM ( -- 首次活跃日期作为cohort SELECT first_dt AS cohort_dt, user_id, dt AS active_dt FROM v_dws_daily_user JOIN ( SELECT user_id, MIN(dt) AS first_dt FROM v_dws_daily_user GROUP BY user_id ) first ON v_dws_daily_user.user_id first.user_id ) cohort_data GROUP BY cohort_dt;三层架构的命名规则很重要——v_dwd_、v_dws_、v_ads_的前缀让你一眼看出视图属于哪一层不用看代码就能理解职责边界。实践要点规则说明DWD 层只做清洗不做聚合保持明细粒度让上层自由聚合DWS 层只聚合不计算排名聚合是确定的排名要看场景ADS 层只做最终计算直接看板/报告能用不做中间计算视图命名带层级前缀v_dwd_/v_dws_/v_ads_一眼分层ADS 层视图不超过一层 CTE如果 ADS 层还有两层 CTE说明 DWS 层没做够禁止跨层引用ADS 层只能引用 DWS 层不能直接引用 DWD 层禁止跨层引用是最重要的规则。ADS 层直接引用 DWD 层意味着绕过了汇总层明细数据量直接暴露给最终查询——这是性能隐患也是架构混乱的根源。五、总结SQL 分层建模的核心原则CTE 嵌套不超过三层超过就拆视图。三层是红线不是建议——超过三层可读性、调试性、性能都急剧恶化。分层建模的三层架构DWD明细清洗层只做清洗不做聚合保持明细粒度DWS轻度汇总层按业务维度聚合消除大数据量ADS应用分析层最终指标计算直接服务看板和报告关键实践视图命名带层级前缀一眼看出职责禁止跨层引用ADS 只引用 DWSDWS 只引用 DWDADS 层视图不超过一层 CTE否则说明 DWS 没做够每层视图独立调试——直接SELECT * FROM v_dws_daily_channel LIMIT 10检查中间结果CTE 是好的段落标题但文章不能只有标题没有章节。三层嵌套以上把 CTE 拆成视图——从嵌套变成平铺代码的可读性和可维护性就从量变到质变。
