数据工程文档教程【免费下载链接】data-engineer-handbookThis is a repo with links to everything youd ever want to learn about data engineering项目地址https://gitcode.com/GitHub_Trending/da/data-engineer-handbook点击查看免费下载本篇技术指南以 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事实表完成三个查询任务对players做状态变化追踪输出 5 种生命周期状态用GROUPING SETS对game_details沿三个维度做高效聚合用窗口函数回答两个纪录类问题球队 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 Tracking2.1 五种状态的定义作业明确要求输出以下生命周期状态判定依据是球员相邻赛季之间is_active与是否已有赛季记录的变化状态判定逻辑基于上一赛季与当前赛季对比New球员本赛季首次进入联盟上一赛季不存在该球员记录Retired球员上一赛季在联盟is_active true本赛季离开Continued Playing球员上一赛季在联盟且本赛季继续在联盟持续is_active trueReturned from Retirement球员上一赛季退役本赛季复出重新进入联盟Stayed Retired球员上一赛季已退役且本赛季继续保持退役状态这与仓库中增长核算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-1today换成当前赛季的球员快照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 分解三步法标记变化点用LAG(condition, 1) OVER (PARTITION BY player_name ORDER BY 时间列)与当前行比较条件变化或首行记为 1生成分组编号对变化标记做SUM(...) OVER (PARTITION BY player_name ORDER BY 时间列)累加得到 streak_identifier同一连续段内编号相同聚合取极值按(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/。提交前建议逐项自查状态五态是否齐全New / Retired / Continued Playing / Returned from Retirement / Stayed Retired五种分支都要在CASE WHEN中出现且边界情况首次进入、连续退役、复出各自命中正确分支GROUPING SETS 维度是否覆盖(player, team)、(player, season)、(team)三个分组子集是否都在同一查询中并用GROUPING()/GROUPING_ID或CASE标记层级参考 grouping_sets.sql 的aggregation_level写法避免与UNION ALL混用导致重复扫描窗口边界是否正确90 场窗口用ROWS BETWEEN 89 PRECEDING AND CURRENT ROW共 90 行连续得分 streak 用LAG 分组累加注意LAG首行为 NULL 时要视作变化点否则首个连续段会被漏计见 scd_generation_query.sql 中OR LAG(...) IS NULL的写法可复现性查询基于仓库 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 能力。赞分享数据工程文档教程【免费下载链接】data-engineer-handbookThis is a repo with links to everything youd ever want to learn about data engineering项目地址https://gitcode.com/GitHub_Trending/da/data-engineer-handbook点击查看免费下载相关推荐Apache Flink 流式会话化实战按 IP 与 Host 构建 5 分钟 Session 窗口data-engineer-handbook 第 4 周作业解析Apache Flink 流式会话化实战按 IP 与 Host 构建 5 分钟 Session 窗口data engineer handbook 第 4 周数据工程文档教程Data Engineer Handbook数据工程数据血缘追踪Data Engineer Handbook数据工程数据血缘追踪 你是否还在为数据异常溯源焦头烂额当报表数据出现偏差时是否需要逐层排查数十个ETLExt数据工程文档教程StarRocks GROUPING 函数详解在 ROLLUP、CUBE 与 GROUPING SETS 中识别聚合行StarRocks GROUPING 函数详解在 ROLLUP、CUBE 与 GROUPING SETS 中识别聚合行 GROUPING 是 StarRock数据库OLAP数据仓库大数据湖仓一体数据分析上一篇opensource.guide 维护者最佳实践全指南从文档化流程到善用社区与自动化下一篇Aptos Move 单元测试编写指南属性语法、用例设计与覆盖率工作流创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考