在传统的编程语言如 Python, Java, C中我们习惯了“过程式”思维写一个for循环遍历列表用if-else判断用变量记录中间状态。但是SQL 是建立在“关系代数”之上的声明式语言。如果你用for循环的思维去写 SQL比如使用游标 Cursor 或写极其复杂的嵌套子查询不仅代码臃肿而且执行效率极差因为数据库引擎最怕的就是“一行一行地处理”。真正的 SQL 高手脑海中没有“循环”和“指针”只有“集合映射Set Mapping”和“状态流转State Transition”。一、集合映射思维从“遍历比对”到“集合运算”过程式思维拿出一行数据去另一张表里“找”有没有匹配的如果没有就标记一下然后再看下一行。集合式思维把表看作数学上的“集合”。不要关心“怎么找”而是定义集合之间的拓扑关系交集、并集、差集、包含让数据库引擎自己去算。经典案例关系除法找出“购买了所有类别商品”的超级用户场景Categories表平台共有 3 个商品类别A, B, C。Orders表用户的购买记录user_id, cat_id。目标找出买齐了所有 3 个类别的用户。过程式思维的死胡同遍历每个用户用一个 Set 记录他买过的类别最后判断len(Set) 3。在 SQL 里你很难维护一个动态的 Set。集合映射思维的降维打击我们使用关系代数中的“除法Division”或者“差集为空”的思想。解法 1基于基数映射Count 聚合把“买齐所有类别”映射为用户去重后的类别数 平台总类别数。SELECT user_id FROM Orders GROUP BY user_id -- 集合的基数Cardinality映射 HAVING COUNT(DISTINCT cat_id) (SELECT COUNT(*) FROM Categories);SELECT user_id FROM Users u WHERE NOT EXISTS ( -- 集合 C平台所有类别 SELECT cat_id FROM Categories c WHERE NOT EXISTS ( -- 集合 O该用户买过的类别 SELECT 1 FROM Orders o WHERE o.user_id u.user_id AND o.cat_id c.cat_id ) );思维顿悟不要试图去“拼凑”用户买了什么而是站在“全集”的高度用NOT EXISTS去“剔除”那些有残缺的集合。这种双重否定的写法在执行计划中通常会被优化为极高效的Anti Join反连接。二、 状态流转思维 从“变量累加”到“事件流”过程式思维按时间顺序一行行读数据定义一个变量last_status null如果当前行的状态和last_status不一样就触发一个动作然后更新last_status。状态流转思维把表看作一条“时间轴上的事件流”。当前行的“状态”是由它周围的“历史事件”或“未来事件”通过窗口函数Window Functions映射出来的。经典案例用户会话切割与连续活跃天数场景User_Logins表记录了用户的登录时间user_id, login_time。目标如果两次登录的时间间隔 30分钟就认为是一个“新的会话Session”。求每个用户的会话数量和每个会话的持续时间。过程式思维的死胡同按用户和时间排序写个循环拿当前时间减去上一条记录的时间大于 30 分钟就session_id 1。状态流转思维的降维打击我们将问题拆解为三个“状态流转”的阶段WITH -- 阶段 1获取“上一个事件”的状态时间穿越 events_with_prev AS ( SELECT user_id, login_time, LAG(login_time) OVER(PARTITION BY user_id ORDER BY login_time) AS prev_login_time FROM User_Logins ), -- 阶段 2状态机翻转定义“新会话”的触发条件 session_flags AS ( SELECT user_id, login_time, -- 状态流转逻辑如果当前时间与上次时间差 30分钟或者没有上次时间则标记为新会话起点 (1)否则为 0 CASE WHEN prev_login_time IS NULL THEN 1 WHEN TIMESTAMPDIFF(MINUTE, prev_login_time, login_time) 30 THEN 1 ELSE 0 END AS is_new_session FROM events_with_prev ), -- 阶段 3状态累加生成 Session_ID利用前缀和 sessions AS ( SELECT user_id, login_time, -- 将 0/1 标记进行累加相同的累加值自然归属于同一个 Session SUM(is_new_session) OVER(PARTITION BY user_id ORDER BY login_time) AS session_id FROM session_flags ) -- 阶段 4按生成的状态 ID 进行集合聚合 SELECT user_id, session_id, MIN(login_time) AS session_start, MAX(login_time) AS session_end, COUNT(*) AS actions_in_session FROM sessions GROUP BY user_id, session_id;思维顿悟LAG() 是“时间机器”它让你无需维护变量就能直接“看到”上一行的状态。SUM() OVER() 是“状态生成器”通过累加 0 和 1Flag我们无中生有地创造出了一个原本不存在的session_id维度。这就是把“过程”变成了“状态”。三、 终极结合用“状态流转”解“集合匹配”当这两种思维结合时SQL 就能解决极其复杂的业务逻辑。案例漏斗转化分析严格时间顺序的状态机场景User_Actions表user_id, action_type, action_time。action_type 包含View浏览,Cart加购,Pay支付。目标找出完成了“严格时间顺序的 View - Cart - Pay” 完整漏斗的用户。注意如果用户先 Pay 后 Cart不算数必须按顺序发生。思维拆解不要试图用三次JOIN去拼凑那样会产生巨大的笛卡尔积且极难处理时间顺序。我们要把每个用户的行为流看作一个“状态机”用MATCH_RECOGNIZE如果数据库支持或者条件窗口函数来推进状态。降维解法使用条件 MAX 状态流转WITH -- 1. 状态机推进为每个用户的每一个动作计算它“之前”发生过的最高级状态 action_states AS ( SELECT user_id, action_type, action_time, -- 状态定义View1, Cart2, Pay3 -- 核心获取当前时间点之前包含当前出现过的最大状态值 MAX(CASE action_type WHEN View THEN 1 WHEN Cart THEN 2 WHEN Pay THEN 3 ELSE 0 END) OVER(PARTITION BY user_id ORDER BY action_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_state_so_far FROM User_Actions WHERE action_type IN (View, Cart, Pay) ), -- 2. 提取状态跃迁点只保留让 max_state_so_far 发生“升级”的关键事件 state_transitions AS ( SELECT user_id, action_type, action_time, max_state_so_far, LAG(max_state_so_far) OVER(PARTITION BY user_id ORDER BY action_time) AS prev_max_state FROM action_states ) -- 3. 集合筛选找出那些状态成功从 1 - 2 - 3 流转的用户 SELECT DISTINCT user_id FROM state_transitions WHERE user_id IN (SELECT user_id FROM state_transitions WHERE max_state_so_far 1 AND (prev_max_state IS NULL OR prev_max_state 1)) AND user_id IN (SELECT user_id FROM state_transitions WHERE max_state_so_far 2 AND prev_max_state 2) AND user_id IN (SELECT user_id FROM state_transitions WHERE max_state_so_far 3 AND prev_max_state 3);(注如果是在 Flink/Oracle 中直接用 MATCH_RECOGNIZE (PATTERN (V C P) DEFINE V AS actionView, C AS actionCart, P AS actionPay) 一行代码搞定这就是正则映射的极致。)总结如何刻意练习这两种思维下次写 SQL 卡壳时请在脑海中默念以下“思维转换口诀”当你想要写... (过程式)请强迫自己换成... (集合/状态思维)对应的 SQL 终极武器for循环遍历每一行集合映射定义全集用条件过滤子集WHERE,JOIN,EXISTSif-else判断上一条记录状态流转让当前行跨行去读取历史LAG(),LEAD(),FIRST_VALUE()维护一个变量做累加状态流转把过程变成前缀和生成新维度SUM() OVER(),COUNT() OVER()找出“没有买过某物”的人集合映射用全集减去已买集合双重否定LEFT JOIN ... WHERE IS NULLEXCEPT,NOT EXISTS按时间顺序拼接字符串/状态状态机映射定义状态流转规则MATCH_RECOGNIZE, 窗口函数结合CASE WHEN最高境界当你看着一张表看到的不再是 Excel 那样的行和列而是“高维空间中的点集合”和“时间轴上状态流转”时你就真正掌握了 SQL 的灵魂。所有的奇技淫巧不过是这两种思维的自然产物。往期精彩面试问数仓中项目里最难的是什么阿里大数据开发面试星型和雪花模型的trade-off是什么?实际中如何选业务问题用“孤岛与间隙”算法计算设备宕机时长与 MTTR某制造业面试题LOT历史日志设备空值数据补全问题面试问建模中粒度设计不当会造成什么影响SQL数据分析实战电商新品高流量低转化问题SQL数据分析购物篮分析游戏数仓 : 用户行为属性大表整合【宽表设计】SQL数据分析实战物流轨迹行为区间划分SQL数据分析实战用户WiFi行为区间划分数据治理数据波动如何校验
声明式之美：超越 CRUD 的 SQL
在传统的编程语言如 Python, Java, C中我们习惯了“过程式”思维写一个for循环遍历列表用if-else判断用变量记录中间状态。但是SQL 是建立在“关系代数”之上的声明式语言。如果你用for循环的思维去写 SQL比如使用游标 Cursor 或写极其复杂的嵌套子查询不仅代码臃肿而且执行效率极差因为数据库引擎最怕的就是“一行一行地处理”。真正的 SQL 高手脑海中没有“循环”和“指针”只有“集合映射Set Mapping”和“状态流转State Transition”。一、集合映射思维从“遍历比对”到“集合运算”过程式思维拿出一行数据去另一张表里“找”有没有匹配的如果没有就标记一下然后再看下一行。集合式思维把表看作数学上的“集合”。不要关心“怎么找”而是定义集合之间的拓扑关系交集、并集、差集、包含让数据库引擎自己去算。经典案例关系除法找出“购买了所有类别商品”的超级用户场景Categories表平台共有 3 个商品类别A, B, C。Orders表用户的购买记录user_id, cat_id。目标找出买齐了所有 3 个类别的用户。过程式思维的死胡同遍历每个用户用一个 Set 记录他买过的类别最后判断len(Set) 3。在 SQL 里你很难维护一个动态的 Set。集合映射思维的降维打击我们使用关系代数中的“除法Division”或者“差集为空”的思想。解法 1基于基数映射Count 聚合把“买齐所有类别”映射为用户去重后的类别数 平台总类别数。SELECT user_id FROM Orders GROUP BY user_id -- 集合的基数Cardinality映射 HAVING COUNT(DISTINCT cat_id) (SELECT COUNT(*) FROM Categories);SELECT user_id FROM Users u WHERE NOT EXISTS ( -- 集合 C平台所有类别 SELECT cat_id FROM Categories c WHERE NOT EXISTS ( -- 集合 O该用户买过的类别 SELECT 1 FROM Orders o WHERE o.user_id u.user_id AND o.cat_id c.cat_id ) );思维顿悟不要试图去“拼凑”用户买了什么而是站在“全集”的高度用NOT EXISTS去“剔除”那些有残缺的集合。这种双重否定的写法在执行计划中通常会被优化为极高效的Anti Join反连接。二、 状态流转思维 从“变量累加”到“事件流”过程式思维按时间顺序一行行读数据定义一个变量last_status null如果当前行的状态和last_status不一样就触发一个动作然后更新last_status。状态流转思维把表看作一条“时间轴上的事件流”。当前行的“状态”是由它周围的“历史事件”或“未来事件”通过窗口函数Window Functions映射出来的。经典案例用户会话切割与连续活跃天数场景User_Logins表记录了用户的登录时间user_id, login_time。目标如果两次登录的时间间隔 30分钟就认为是一个“新的会话Session”。求每个用户的会话数量和每个会话的持续时间。过程式思维的死胡同按用户和时间排序写个循环拿当前时间减去上一条记录的时间大于 30 分钟就session_id 1。状态流转思维的降维打击我们将问题拆解为三个“状态流转”的阶段WITH -- 阶段 1获取“上一个事件”的状态时间穿越 events_with_prev AS ( SELECT user_id, login_time, LAG(login_time) OVER(PARTITION BY user_id ORDER BY login_time) AS prev_login_time FROM User_Logins ), -- 阶段 2状态机翻转定义“新会话”的触发条件 session_flags AS ( SELECT user_id, login_time, -- 状态流转逻辑如果当前时间与上次时间差 30分钟或者没有上次时间则标记为新会话起点 (1)否则为 0 CASE WHEN prev_login_time IS NULL THEN 1 WHEN TIMESTAMPDIFF(MINUTE, prev_login_time, login_time) 30 THEN 1 ELSE 0 END AS is_new_session FROM events_with_prev ), -- 阶段 3状态累加生成 Session_ID利用前缀和 sessions AS ( SELECT user_id, login_time, -- 将 0/1 标记进行累加相同的累加值自然归属于同一个 Session SUM(is_new_session) OVER(PARTITION BY user_id ORDER BY login_time) AS session_id FROM session_flags ) -- 阶段 4按生成的状态 ID 进行集合聚合 SELECT user_id, session_id, MIN(login_time) AS session_start, MAX(login_time) AS session_end, COUNT(*) AS actions_in_session FROM sessions GROUP BY user_id, session_id;思维顿悟LAG() 是“时间机器”它让你无需维护变量就能直接“看到”上一行的状态。SUM() OVER() 是“状态生成器”通过累加 0 和 1Flag我们无中生有地创造出了一个原本不存在的session_id维度。这就是把“过程”变成了“状态”。三、 终极结合用“状态流转”解“集合匹配”当这两种思维结合时SQL 就能解决极其复杂的业务逻辑。案例漏斗转化分析严格时间顺序的状态机场景User_Actions表user_id, action_type, action_time。action_type 包含View浏览,Cart加购,Pay支付。目标找出完成了“严格时间顺序的 View - Cart - Pay” 完整漏斗的用户。注意如果用户先 Pay 后 Cart不算数必须按顺序发生。思维拆解不要试图用三次JOIN去拼凑那样会产生巨大的笛卡尔积且极难处理时间顺序。我们要把每个用户的行为流看作一个“状态机”用MATCH_RECOGNIZE如果数据库支持或者条件窗口函数来推进状态。降维解法使用条件 MAX 状态流转WITH -- 1. 状态机推进为每个用户的每一个动作计算它“之前”发生过的最高级状态 action_states AS ( SELECT user_id, action_type, action_time, -- 状态定义View1, Cart2, Pay3 -- 核心获取当前时间点之前包含当前出现过的最大状态值 MAX(CASE action_type WHEN View THEN 1 WHEN Cart THEN 2 WHEN Pay THEN 3 ELSE 0 END) OVER(PARTITION BY user_id ORDER BY action_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_state_so_far FROM User_Actions WHERE action_type IN (View, Cart, Pay) ), -- 2. 提取状态跃迁点只保留让 max_state_so_far 发生“升级”的关键事件 state_transitions AS ( SELECT user_id, action_type, action_time, max_state_so_far, LAG(max_state_so_far) OVER(PARTITION BY user_id ORDER BY action_time) AS prev_max_state FROM action_states ) -- 3. 集合筛选找出那些状态成功从 1 - 2 - 3 流转的用户 SELECT DISTINCT user_id FROM state_transitions WHERE user_id IN (SELECT user_id FROM state_transitions WHERE max_state_so_far 1 AND (prev_max_state IS NULL OR prev_max_state 1)) AND user_id IN (SELECT user_id FROM state_transitions WHERE max_state_so_far 2 AND prev_max_state 2) AND user_id IN (SELECT user_id FROM state_transitions WHERE max_state_so_far 3 AND prev_max_state 3);(注如果是在 Flink/Oracle 中直接用 MATCH_RECOGNIZE (PATTERN (V C P) DEFINE V AS actionView, C AS actionCart, P AS actionPay) 一行代码搞定这就是正则映射的极致。)总结如何刻意练习这两种思维下次写 SQL 卡壳时请在脑海中默念以下“思维转换口诀”当你想要写... (过程式)请强迫自己换成... (集合/状态思维)对应的 SQL 终极武器for循环遍历每一行集合映射定义全集用条件过滤子集WHERE,JOIN,EXISTSif-else判断上一条记录状态流转让当前行跨行去读取历史LAG(),LEAD(),FIRST_VALUE()维护一个变量做累加状态流转把过程变成前缀和生成新维度SUM() OVER(),COUNT() OVER()找出“没有买过某物”的人集合映射用全集减去已买集合双重否定LEFT JOIN ... WHERE IS NULLEXCEPT,NOT EXISTS按时间顺序拼接字符串/状态状态机映射定义状态流转规则MATCH_RECOGNIZE, 窗口函数结合CASE WHEN最高境界当你看着一张表看到的不再是 Excel 那样的行和列而是“高维空间中的点集合”和“时间轴上状态流转”时你就真正掌握了 SQL 的灵魂。所有的奇技淫巧不过是这两种思维的自然产物。往期精彩面试问数仓中项目里最难的是什么阿里大数据开发面试星型和雪花模型的trade-off是什么?实际中如何选业务问题用“孤岛与间隙”算法计算设备宕机时长与 MTTR某制造业面试题LOT历史日志设备空值数据补全问题面试问建模中粒度设计不当会造成什么影响SQL数据分析实战电商新品高流量低转化问题SQL数据分析购物篮分析游戏数仓 : 用户行为属性大表整合【宽表设计】SQL数据分析实战物流轨迹行为区间划分SQL数据分析实战用户WiFi行为区间划分数据治理数据波动如何校验
![3分钟掌握VideoDownloadHelper：简单高效的网页视频下载插件终极指南 [特殊字符]](images/news3.jpg)
