企业官网建设流程全解析

• 数据工程
• 文档
• 教程

本篇技术指南以 intermediate-bootcamp 第四周作业（Applying Analytical Patterns）为骨架，完整拆解三个进阶 SQL 实战任务：基于players系列表的状态变化追踪（State Change Tracking）、基于game_details的GROUPING SETS高效多维聚合，以及用窗口函数定位球队 90 场连胜窗口与球员连续得分纪录。读完本文，你将掌握与仓库 lecture-lab 中增长核算、SCD 生成、窗口分析同源的实现思路，并能在 PostgreSQL 环境中直接复现三类分析查询。

一、作业总览与前置数据模型

第四周作业围绕"应用分析模式"展开，要求使用第一周（Dimensional Data Modeling）建立的players、players_scd、player_seasons三张球员维度表，并联合第二周（Fact Data Modeling）引入的game_details事实表，完成三个查询任务：

1. 对players做状态变化追踪，输出 5 种生命周期状态；
2. 用GROUPING SETS对game_details沿三个维度做高效聚合；
3. 用窗口函数回答两个"纪录类"问题（球队 90 场连胜、球员连续得分）。

提交方式为：将三个查询放入intermediate-bootcamp/materials/4-applying-analytical-patterns/homework/<discord-username>/文件夹。

1.1 核心表结构

作业依赖的表定义分散在仓库第一、二周材料中，先对齐结构再写查询：

• players 表定义：核心字段player_name、seasons season_stats[]（赛季数组）、scoring_class scoring_class（枚举bad/average/good/star）、years_since_last_active、is_active BOOLEAN、current_season INTEGER，主键(player_name, current_season)；
• player_seasons 表定义：按(player_name, season)存储每名球员每个赛季的出场数gp、得分pts、篮板reb、助攻ast及高阶指标；
• players_scd_table 定义：含player_name、scoring_class、is_active、start_season、end_date，是慢变化维（SCD）的落表结构；
• game_details 表定义：每行是一条"球员-球队-比赛"粒度的出场记录，字段含game_id、team_id、team_abbreviation、player_name、pts、min等。

需要特别说明的是：game_details是球员粒度数据，同一场比赛中同一支球队有多个球员行；要判定"球队某场是否获胜"，必须先按game_id + team_id聚合出球队总得分再与对手比较（仓库 game_details.sql 中team_id、pts字段即为此服务的）。

二、任务一：players 状态变化追踪（State Change Tracking）

2.1 五种状态的定义

作业明确要求输出以下生命周期状态，判定依据是球员相邻赛季之间is_active与"是否已有赛季记录"的变化：

这与仓库中增长核算（Growth Accounting）的状态机一脉相承：growth_accounting.sql 通过yesterday（昨日快照）与today（当日活跃）两张 CTE 做FULL OUTER JOIN，再用CASE WHEN输出New / Retained / Resurrected / Churned / Stale五种状态。本作业的差异仅在于：时间粒度从"日"换成"赛季"，活跃判定从last_active_date换成is_active与years_since_last_active。

2.2 实现思路：yesterday/today 快照对比法

从源码结构看，growth_accounting.sql 给出了可直接迁移的骨架：

WITH yesterday AS ( SELECT * FROM users_growth_accounting WHERE date = DATE('2023-03-09') ), today AS ( SELECT user_id, DATE_TRUNC('day', event_time::timestamp) AS today_date FROM events WHERE user_id IS NOT NULL ... ) SELECT COALESCE(t.user_id, y.user_id) AS user_id, CASE WHEN y.user_id IS NULL THEN 'New' WHEN y.last_active_date = t.today_date - Interval '1 day' THEN 'Retained' ... END AS daily_active_state FROM today t FULL OUTER JOIN yesterday y ON t.user_id = y.user_id

套用到players表时，把yesterday换成"上一赛季的球员快照"（current_season = N-1），today换成"当前赛季的球员快照"（current_season = N），状态判定的CASE WHEN分支即对应作业要求的五态。同时注意players表本身就是累加式结构（players.sql 的seasons season_stats[]数组保留了每个赛季的聚合），years_since_last_active字段也可直接辅助判定退役时长。

2.3 示例查询（基于仓库表结构的实现参考）

以下查询依据作业需求与 players.sql 的字段设计推导而成，可直接在 PostgreSQL 中运行（假设current_season为连续递增的赛季编号）：

WITH prev AS ( SELECT player_name, is_active FROM players WHERE current_season = 1997 -- 上一赛季快照 ), curr AS ( SELECT player_name, is_active FROM players WHERE current_season = 1998 -- 当前赛季快照 ) SELECT COALESCE(c.player_name, p.player_name) AS player_name, CASE WHEN p.player_name IS NULL THEN 'New' -- 本赛季才进入联盟 WHEN p.is_active = TRUE AND c.is_active = TRUE THEN 'Continued Playing' -- 连续在联盟 WHEN p.is_active = TRUE AND (c.player_name IS NULL OR c.is_active = FALSE) THEN 'Retired' -- 本赛季离开 WHEN p.is_active = FALSE AND c.is_active = TRUE THEN 'Returned from Retirement' -- 复出 WHEN p.is_active = FALSE AND (c.player_name IS NULL OR c.is_active = FALSE) THEN 'Stayed Retired' -- 持续退役 END AS player_state FROM curr c FULL OUTER JOIN prev p ON c.player_name = p.player_name ORDER BY player_name;

如果想对每个赛季批量生成状态，可以结合 scd_generation_query.sql 展示的LAG()相邻行对比技巧——用LAG(is_active, 1) OVER (PARTITION BY player_name ORDER BY current_season)拿到上一赛季的is_active，与当前行比较即可在同一份数据上判定连续状态，无需额外快照 CTE。

三、任务二：GROUPING SETS 高效多维聚合

3.1 为什么用 GROUPING SETS

作业要求在一条查询内沿三个维度聚合game_details：

• (player, team)：回答"某球员为某支球队单场得分总和"类问题，例如谁为单一球队得分最多；
• (player, season)：回答"单赛季得分"类问题，例如谁在单个赛季得分最多；
• (team)：回答"球队维度"类问题，例如哪支球队赢得比赛最多。

若用UNION ALL拼接三组GROUP BY，需要扫描三次数据；GROUPING SETS让一条SELECT内同时输出多个分组层级的结果，只需扫描一次，这正是作业强调的"高效聚合"。

3.2 仓库中的 GROUPING SETS 参考实现

grouping_sets.sql 是本周 lecture-lab 的现成范例，它把events与devices关联后，用GROUPING SETS同时产出三层统计，并通过GROUPING()函数标记当前行属于哪个聚合层级：

SELECT CASE WHEN GROUPING(os_type) = 0 AND GROUPING(device_type) = 0 AND GROUPING(browser_type) = 0 THEN 'os_type__device_type__browser' WHEN GROUPING(browser_type) = 0 THEN 'browser_type' WHEN GROUPING(device_type) = 0 THEN 'device_type' WHEN GROUPING(os_type) = 0 THEN 'os_type' END AS aggregation_level, COALESCE(os_type, '(overall)') AS os_type, ... COUNT(1) AS number_of_hits FROM events_augmented GROUP BY GROUPING SETS ( (browser_type, device_type, os_type), (browser_type), (os_type), (device_type) ) ORDER BY COUNT(1) DESC;

本作业可完全复用这套模式，仅需把维度换成player_name、team_id、season。

3.3 作业查询示例（基于 game_details 表结构）

game_details中没有season列，因此"player + season"维度的聚合需要先关联player_seasons（以player_name为键、提供season字段）。以下示例查询依据 game_details.sql 与 player_seasons.sql 的结构推导：

WITH game_details_joined AS ( SELECT gd.player_name, gd.team_id, ps.season, gd.pts, CASE WHEN gd.pts > 0 THEN 1 ELSE 0 END AS scored_points, gd.game_id FROM game_details gd LEFT JOIN player_seasons ps ON gd.player_name = ps.player_name ), team_game_results AS ( -- 先聚合出"球队每场比赛是否获胜" SELECT game_id, team_id, SUM(pts) AS team_pts FROM game_details_joined GROUP BY game_id, team_id ) SELECT COALESCE(player_name, '(all players)') AS player_name, COALESCE(CAST(team_id AS TEXT), '(all teams)') AS team_id, COALESCE(CAST(season AS TEXT), '(all seasons)') AS season, SUM(pts) AS total_points, CASE WHEN GROUPING(player_name) = 0 AND GROUPING(team_id) = 0 THEN 'player__team' WHEN GROUPING(player_name) = 0 AND GROUPING(season) = 0 THEN 'player__season' WHEN GROUPING(team_id) = 0 THEN 'team' END AS aggregation_level FROM game_details_joined GROUP BY GROUPING SETS ( (player_name, team_id), (player_name, season), (team_id) );

三个维度分别回答作业中的问题：

• (player_name, team_id)：按SUM(pts)降序即可得到"为某队得分最多的球员"；
• (player_name, season)：按SUM(pts)降序即可得到"单赛季得分最多的球员"；
• (team_id)：结合上面的team_game_results统计每队获胜场次COUNT(*) FILTER (WHERE team_pts 为当场最高)，即可得到"获胜最多的球队"。

注意：上述team_game_results判定胜负需要与同场次对手球队比较，实际操作时可将game_details按(game_id, team_id)自连接或聚合出两队比分后比较，仓库中的 games.sql（比赛主表，含对阵双方与比分信息）可以更直接地支撑"球队胜场"统计。

四、任务三：窗口函数回答纪录类问题

4.1 问题一：球队在 90 场比赛中最多获胜多少场

"滑动窗口内累计事件数"是窗口函数的经典场景。核心思路：先把game_details按(game_id, team_id)聚合成"球队每场比赛是否获胜"的结果（胜 = 1，负 = 0），再按球队分区、按比赛日期排序，用ROWS BETWEEN 89 PRECEDING AND CURRENT ROW累加一个 90 场窗口内的胜场数，最后取每个球队的MAX。

仓库 window_based_analysis.sql 提供了完全同构的窗口写法示范，其中weekly_rolling_count正是用ROWS BETWEEN 6 PRECEDING AND CURRENT ROW实现的 7 日滚动求和：

SUM(count) OVER ( PARTITION BY referrer, url ORDER BY event_date ROWS BETWEEN 6 preceding AND CURRENT ROW ) AS weekly_rolling_count

将6 preceding换成89 preceding即得 90 场滚动窗口。示例（基于game_details结构推导）：

WITH team_games AS ( SELECT game_id, team_id, -- 按比赛+球队聚合，与对手比分比较判定胜负（1 胜 0 负） CASE WHEN team_pts > opponent_pts THEN 1 ELSE 0 END AS is_win, game_date FROM team_game_results -- 上一节中按 (game_id, team_id) 聚合的比分结果 ), windowed AS ( SELECT team_id, game_date, is_win, SUM(is_win) OVER ( PARTITION BY team_id ORDER BY game_date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW ) AS wins_in_90_games FROM team_games ) SELECT team_id, MAX(wins_in_90_games) AS max_wins_in_90_game_stretch FROM windowed GROUP BY team_id;

4.2 问题二：LeBron James 连续得分超过 10 分的场次

"连续满足条件的场次"本质是**连续性分组（streak）**问题，仓库第一周材料中的 scd_generation_query.sql 给出了标准的 streak 分解三步法：

1. 标记变化点：用LAG(condition, 1) OVER (PARTITION BY player_name ORDER BY 时间列)与当前行比较，条件变化（或首行）记为 1；
2. 生成分组编号：对变化标记做SUM(...) OVER (PARTITION BY player_name ORDER BY 时间列)累加，得到 streak_identifier，同一连续段内编号相同；
3. 聚合取极值：按(player_name, streak_identifier)分组，MIN/MAX得到起止，COUNT(*)得到连续长度。

WITH streak_started AS ( SELECT player_name, game_id, LAG(is_over_10, 1) OVER (PARTITION BY player_name ORDER BY game_date) <> is_over_10 OR LAG(is_over_10, 1) OVER (PARTITION BY player_name ORDER BY game_date) IS NULL AS did_change FROM (...), -- 过滤出 player_name = 'LeBron James' 且含 is_over_10 标记的结果 ), streak_identified AS ( SELECT player_name, game_id, SUM(CASE WHEN did_change THEN 1 ELSE 0 END) OVER (PARTITION BY player_name ORDER BY game_date) AS streak_identifier FROM streak_started ), aggregated AS ( SELECT player_name, streak_identifier, COUNT(*) AS games_in_streak FROM streak_identified GROUP BY player_name, streak_identifier ) SELECT MAX(games_in_streak) AS longest_streak_over_10_pts FROM aggregated;

其中is_over_10标记由game_details.pts > 10得出，并过滤player_name = 'LeBron James'与"实际出场"记录（min非空）即可。该查询与 scd_generation_query.sql 的LAG(scoring_class...)+SUM(CASE WHEN did_change...)逻辑完全对应，是仓库中验证过的 streak 模式。

五、提交要求与验收自查

按作业原文，将三个查询文件放入homework/<discord-username>/目录（例如homework/zach-wilson/）。提交前建议逐项自查：

1. 状态五态是否齐全：New / Retired / Continued Playing / Returned from Retirement / Stayed Retired五种分支都要在CASE WHEN中出现，且边界情况（首次进入、连续退役、复出）各自命中正确分支；
2. GROUPING SETS 维度是否覆盖：(player, team)、(player, season)、(team)三个分组子集是否都在同一查询中，并用GROUPING()/GROUPING_ID或CASE标记层级（参考 grouping_sets.sql 的aggregation_level写法），避免与UNION ALL混用导致重复扫描；
3. 窗口边界是否正确：90 场窗口用ROWS BETWEEN 89 PRECEDING AND CURRENT ROW（共 90 行），连续得分 streak 用LAG+ 分组累加，注意LAG首行为 NULL 时要视作变化点，否则首个连续段会被漏计（见 scd_generation_query.sql 中OR LAG(...) IS NULL的写法）；
4. 可复现性：查询基于仓库 players.sql、game_details.sql、player_seasons.sql 三张表的实际字段编写，命名与类型保持一致，可在 PostgreSQL 中直接执行。

六、小结

本周作业是仓库中三类分析模式的集中演练：状态变化追踪复用 growth_accounting.sql 的快照对比状态机；GROUPING SETS复用 grouping_sets.sql 的单次扫描多维聚合；窗口函数与 streak 分析复用 window_based_analysis.sql 的滚动窗口与 scd_generation_query.sql 的连续段分解。将本周 lecture-lab 的四个脚本（funnel、grouping sets、growth accounting、retention、window）通读并与本文示例对照，即可从"看懂"跨越到"独立写出"这三类高频分析查询，这也是后续 KPIs 与实验评估、数据管道维护等进阶周次反复依赖的核心 SQL 能力。

• 数据工程
• 文档
• 教程