编写分析查询
切片、切块并汇总指标。
编写分析查询 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。
什么是分析查询
分析查询不止于简单的行查找。它们不是询问客户 42 下了哪一笔订单？，而是询问按地区和季度划分的总收入是多少？或本月与上月相比如何？
在基于星型模式构建的数据仓库中，分析查询会对事实进行切片（筛选一个维度）、切块（筛选多个维度）和上卷（聚合到更粗的粒度），以发掘业务洞察。
星型模式回顾
星型模式包含一个中央事实表（例如 fact_sales），周围环绕着维度表（例如 dim_date、dim_product、dim_store）。分析查询会将事实表与当前分析所需的维度进行连接。
SELECT
s.store_name,
d.year,
d.quarter,
SUM(f.revenue) AS total_revenue,
SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY
s.store_name,
d.year,
d.quarter
ORDER BY
d.year,
d.quarter,
s.store_name;切片：筛选一个维度
切片是指将结果集限制为某个维度的单个值——例如只查看 2024 年的数据。WHERE 子句就是用于切片的工具。
尽早进行切片可以减少数据库必须聚合的行数，从而让大型事实表上的查询保持快速。
-- Slice: only year 2024
SELECT
p.category,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;切块：筛选多个维度
切块是指同时对两个或更多维度应用筛选条件——例如查看北部地区第一季度的电子产品销售额。每增加一个 WHERE 条件，就会筛出一个更小的数据立方体。
-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
d.month,
SUM(f.revenue) AS revenue,
SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE
p.category = 'Electronics'
AND s.region = 'North'
AND d.year = 2024
AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;上卷：聚合到更高层级
上卷是指从详细粒度（每个门店的每日销售额）移动到更粗的粒度（每个地区的月度销售额）。实现方式是移除较低层级的 GROUP BY 列，然后重新聚合。
ROLLUP 修饰符可以在一次查询中生成小计和总计，而不必编写多个 UNION ALL 块。
-- Roll up from store/month to region/quarter with subtotals
SELECT
s.region,
d.quarter,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;使用 LAG 进行期间对比
最常见的分析模式之一，是将某个指标与前一期间的同一指标进行比较。窗口函数 LAG() 可以直接将上一行的值提取到当前行中，而无需自连接。
这里我们将计算月环比收入增长率。
WITH monthly AS (
SELECT
d.year,
d.month,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
)
SELECT
year,
month,
revenue,
LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
/ NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;使用 SUM OVER 计算累计总额
累计总额（累积和）会按照既定顺序，将每一行的值加到所有前序行的总和中。这非常适合跟踪一整年的累计收入或监控预算消耗进度。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 框架子句使窗口定义明确且没有歧义。
SELECT
d.year,
d.month,
SUM(f.revenue) AS monthly_revenue,
SUM(SUM(f.revenue)) OVER (
PARTITION BY d.year
ORDER BY d.month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;使用 DENSE_RANK 对维度进行排名
排名可以帮助您找出某个组中的最佳或最差表现者。出现并列时，DENSE_RANK() 会分配不间断的连续排名，因此是 BI 报告排行榜的首选。
将排名结果封装在 CTE 中并按排名筛选，可以使前 N 名模式简洁易读。
WITH ranked_products AS (
SELECT
p.product_name,
p.category,
SUM(f.revenue) AS revenue,
DENSE_RANK() OVER (
PARTITION BY p.category
ORDER BY SUM(f.revenue) DESC
) AS rnk
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;使用窗口 SUM 计算贡献百分比
知道某个产品的绝对收入很有用，但知道它占类别收入的 38 % 会更有行动价值。在整个分区上计算窗口化 SUM()，无需通过子查询连接就能得到分母。
SELECT
p.category,
p.product_name,
SUM(f.revenue) AS product_revenue,
SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
ROUND(
100.0 * SUM(f.revenue)
/ SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
1) AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;用于趋势平滑的移动平均值
每日或每周销售额通常比较嘈杂。移动平均值可以平滑短期波动，让您看清潜在趋势。这里使用滑动窗口框架计算 3 个月移动平均值。
WITH monthly_rev AS (
SELECT
d.year,
d.month,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
)
SELECT
year,
month,
revenue,
ROUND(
AVG(revenue) OVER (
ORDER BY year, month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
),
2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;使用 CUBE 计算所有维度组合
CUBE 通过计算所列维度的每一种可能组合的小计来扩展 ROLLUP，而不只是沿层级上卷路径计算小计。这样只需一次处理就能生成完整的跨维度汇总——对于用户可以自由切换维度的多维仪表板很有用。
分组列中的 NULL 表示该维度的所有值——请使用 GROUPING() 区分数据中有意设置的 NULL 与上卷产生的 NULL。
SELECT
CASE WHEN GROUPING(s.region) = 1 THEN 'ALL REGIONS' ELSE s.region END AS region,
CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category END AS category,
CASE WHEN GROUPING(d.quarter) = 1 THEN 'ALL QUARTERS' ELSE d.quarter::TEXT END AS quarter,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;哪种操作会将结果限制为某个维度的单个值？
请检验您对数据仓库中分析查询术语的理解。
回顾：编写分析查询
本课介绍了针对星型模式编写分析查询的核心模式：
- 切片 — 使用 WHERE 筛选一个维度，聚焦于特定细分部分。
- 切块 — 同时筛选多个维度，筛选出精确的数据立方体。
- 上卷 — 聚合到更粗的粒度；使用
ROLLUP或CUBE生成多层级小计。 - LAG / LEAD — 无需自连接即可进行期间对比。
- 累计总额 & 移动平均值 — 通过窗口框架计算累计指标和平滑指标。
- DENSE_RANK — 在分区内进行清晰的前 N 名排名。
- 贡献百分比 — 使用窗口化 SUM 作为份额计算的分母。
组合使用这些模式，可以覆盖您在生产数据仓库中会遇到的绝大多数 BI 和报告需求。
常见问题解答
「编写分析查询」课时是免费的吗？
是的 — 「编写分析查询」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。
「编写分析查询」这节课中我会学到什么？
切片、切块并汇总指标。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Academy 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「编写分析查询」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Academy 课中编写并运行代码吗？
能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。