真实场景的报表模式
仅使用窗口函数实现经典仪表板：留存曲线、每个类别的前 N 名和会话化
真实场景的报表模式 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。
模式：每组前 N 项
每个用户排名前 3 的订单：
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;模式：运行总计
随时间累计的收入：
SELECT day, revenue,
SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;模式：首次出现
每个用户首次执行每项操作的时间：
SELECT user_id, action, MIN(ts) AS first_at
FROM events
GROUP BY user_id, action;
-- Or with window functions for full row:
WITH firsts AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, action ORDER BY ts) AS rn
FROM events
)
SELECT * FROM firsts WHERE rn = 1;模式：用户群组留存
按注册周分组的用户，按第 N 周统计留存：
WITH cohorts AS (
SELECT id AS user_id, date_trunc('week', created_at) AS cohort_week
FROM users
),
activities AS (
SELECT user_id, date_trunc('week', ts) AS active_week FROM events
)
SELECT c.cohort_week,
(a.active_week - c.cohort_week) / 7 AS week_offset,
COUNT(DISTINCT a.user_id) AS active
FROM cohorts c
JOIN activities a USING (user_id)
WHERE a.active_week >= c.cohort_week
GROUP BY c.cohort_week, week_offset
ORDER BY c.cohort_week, week_offset;模式：漏斗分析
到达每个步骤的用户数：
SELECT
COUNT(*) AS signed_up,
COUNT(*) FILTER (WHERE first_login_at IS NOT NULL) AS logged_in,
COUNT(*) FILTER (WHERE first_purchase_at IS NOT NULL) AS purchased
FROM users;模式：会话划分
当间隔超过 30 分钟时，将事件分组到不同会话中：
WITH gaps AS (
SELECT user_id, ts,
CASE
WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
> INTERVAL '30 min'
THEN 1 ELSE 0
END AS new_session
FROM events
)
SELECT user_id, ts,
SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;模式：周期对比
比较当前月份和上一个月份：
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
(revenue::FLOAT / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 1) * 100 AS pct_change
FROM monthly_revenue
ORDER BY month;模式：透视输出
使用 FILTER 生成宽格式：
SELECT user_id,
SUM(amount) FILTER (WHERE month = '2024-01') AS jan,
SUM(amount) FILTER (WHERE month = '2024-02') AS feb,
SUM(amount) FILTER (WHERE month = '2024-03') AS mar
FROM monthly_spend
GROUP BY user_id;模式：今日活跃用户
DAU / WAU / MAU：
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '1 day') AS dau,
COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '7 days') AS wau,
COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '30 days') AS mau
FROM events;模式：填补缺口
没有事件的日期应显示 0，而不是缺失：
SELECT day, COALESCE(COUNT(e.id), 0) AS events
FROM generate_series(CURRENT_DATE - 30, CURRENT_DATE, INTERVAL '1 day') AS day
LEFT JOIN events e ON date_trunc('day', e.ts) = day
GROUP BY day
ORDER BY day;组合窗口函数获取洞察
在一个查询中包含多个窗口列——清晰且快速：
SELECT day, revenue,
LAG(revenue) OVER w AS prev,
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue
WINDOW w AS (ORDER BY day)
ORDER BY day;回顾
大多数报告都可以归结为少数几种模式：前 N 项、运行总计、用户群组、漏斗、会话划分、周期对比、透视和缺口填补。掌握这些模式，您就能构建 SQL 仪表板所需的任何报告。
快速检查
您正在构建“每个类别排名前 5 的产品”报告。应该使用哪种惯用 SQL 模式？
常见问题解答
「真实场景的报表模式」课时是免费的吗？
是的 — 「真实场景的报表模式」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。
「真实场景的报表模式」这节课中我会学到什么？
仅使用窗口函数实现经典仪表板：留存曲线、每个类别的前 N 名和会话化 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 SQL Academy 需要有经验吗？
无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「真实场景的报表模式」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 SQL Academy 课中编写并运行代码吗？
能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。