1. 多表关联电商交易分析实战当我们需要分析商品销售数据时往往需要同时查看商品信息和交易记录。这就是典型的多表关联场景。假设我们有两张表商品表(goods)和交易表(trans)商品表记录商品的基本信息交易表记录每次购买的情况。多表关联最常用的方式是使用JOIN操作。在实际项目中我发现很多新手容易混淆不同的JOIN类型。简单来说INNER JOIN只返回两表中匹配的行LEFT JOIN返回左表所有行右表不匹配则为NULLRIGHT JOIN返回右表所有行左表不匹配则为NULLFULL JOIN返回两表所有行MySQL不支持来看一个实际案例找出购买个数超过20且重量小于50的商品。这个需求需要同时考虑商品属性和交易行为SELECT goods.id, SUM(count) AS total FROM goods JOIN trans ON goods.id trans.id WHERE weight 50 GROUP BY goods.id HAVING total 20 ORDER BY goods.id;这里有几个关键点需要注意JOIN条件要明确goods.id trans.idWHERE在GROUP BY之前执行用于过滤原始数据HAVING在GROUP BY之后执行用于过滤分组结果聚合函数SUM要配合GROUP BY使用我在实际项目中遇到过这样的坑忘记写GROUP BY就直接用聚合函数结果只返回一行数据。MySQL 5.7以后会报错但早期版本可能不会提示错误。2. 子查询嵌套社交音乐推荐系统音乐推荐是社交平台的常见功能。假设我们有三个表关注关系表(follow)、音乐喜好表(music_likes)和音乐信息表(music)。现在要给用户推荐其关注的人喜欢的音乐但不能推荐用户已经喜欢的音乐。这个需求需要用到子查询和多重关联SELECT DISTINCT music_name FROM follow f1 JOIN music_likes ml1 ON f1.follower_id ml1.user_id JOIN music m1 ON m1.id ml1.music_id WHERE f1.user_id 1 AND music_name NOT IN ( SELECT music_name FROM music_likes ml2 JOIN music m2 ON ml2.music_id m2.id WHERE ml2.user_id 1 ) ORDER BY music_name;子查询的执行顺序是从内到外。这个查询中NOT IN子查询先找出用户1已经喜欢的音乐然后外层查询排除这些音乐。性能优化小技巧当子查询结果集很大时NOT IN可能效率较低。可以考虑改用NOT EXISTSAND NOT EXISTS ( SELECT 1 FROM music_likes ml2 WHERE ml2.user_id 1 AND ml2.music_id ml1.music_id )我在处理千万级数据时测试过NOT EXISTS通常比NOT IN性能更好特别是当子查询结果集很大时。3. 视图应用用户行为统计分析业务分析经常需要统计日活跃用户(DAU)、新增用户留存等指标。假设我们有三个表新用户表(new_user)、支付表(user_pay)和登录表(login_record)。直接写复杂查询可能难以维护这时视图(View)就派上用场了。视图可以简化复杂查询提高SQL可读性-- 创建每日总支付视图 CREATE VIEW top(dt, total_pay) AS SELECT dt, SUM(pay_money) FROM user_pay GROUP BY dt; -- 创建每日活跃用户视图 CREATE VIEW login(dt, dau) AS SELECT dt, COUNT(DISTINCT user_id) FROM login_record GROUP BY dt; -- 创建新增用户支付视图 CREATE VIEW np(dt, total_pay_new) AS SELECT dt, SUM(pay_money) FROM user_pay, new_user WHERE new_user.is_new 1 AND user_pay.user_id new_user.user_id GROUP BY dt; -- 创建新增用户活跃视图 CREATE VIEW nl(dt, dau_new) AS SELECT dt, COUNT(DISTINCT login_record.user_id) FROM login_record, new_user WHERE new_user.is_new 1 AND login_record.user_id new_user.user_id GROUP BY dt; -- 最终查询 SELECT login.dt, dau, dau_new, total_pay, total_pay_new FROM login, nl, top, np WHERE login.dt nl.dt AND nl.dt top.dt AND top.dt np.dt ORDER BY dt;视图的优势简化复杂查询提高可读性复用SQL逻辑避免重复代码可以像普通表一样被查询需要注意的是视图不存储数据每次查询视图都会执行底层SQL。对于性能敏感的场景可以考虑使用物化视图(Materialized View)。4. 窗口函数用户留存率计算窗口函数是SQL高级功能可以在不减少行数的情况下进行聚合计算。计算用户留存率是典型的使用场景。假设有登录表(login)要计算每天新增用户的次日留存率SELECT x.date, ROUND(COUNT(y.user_id)/COUNT(x.user_id), 3) AS p FROM ( SELECT user_id, MIN(date) AS date FROM login GROUP BY user_id ) x LEFT JOIN login y ON x.user_id y.user_id AND y.date DATE_ADD(x.date, INTERVAL 1 DAY) GROUP BY x.date UNION -- 处理没有新增用户的日子 SELECT date, 0 AS p FROM login WHERE date NOT IN ( SELECT MIN(date) FROM login GROUP BY user_id ) ORDER BY date;这个查询使用了几个关键技巧子查询找出每个用户的首次登录日期LEFT JOIN关联次日登录记录UNION合并有新增用户和没有新增用户的日子ROUND函数保留3位小数窗口函数特别适合这类既要看明细又要看统计的场景。其他常用窗口函数包括ROW_NUMBER(): 行号RANK(): 排名相同值会有并列DENSE_RANK(): 密集排名相同值并列但不会跳过序号LAG()/LEAD(): 访问前后行的数据5. 高级窗口函数订单模式识别窗口函数还能用于识别用户行为模式。例如找出在指定日期后多次购买特定课程的用户SELECT user_id, MIN(CASE WHEN num 1 THEN date END) AS first_buy_date, MAX(CASE WHEN num 2 THEN date END) AS second_buy_date, COUNT(1) AS cnt FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS num FROM order_info WHERE status completed AND date 2021-10-15 AND product_name IN (C, Java, Python) ) v GROUP BY user_id HAVING COUNT(1) 2;这个查询使用了ROW_NUMBER()窗口函数为每个用户的订单按日期编号然后筛选出购买次数≥2的用户。窗口函数的关键组成部分PARTITION BY定义分组类似GROUP BY但不聚合ORDER BY定义窗口内的排序框架子句(ROWS/RANGE)定义窗口范围如前3行到当前行实际项目中窗口函数可以大幅简化复杂查询。我曾用窗口函数将原本需要多次查询的报表优化为单次查询性能提升了10倍。6. 综合应用工程供应分析最后一个综合案例查询各工程最大的零件供应量及对应零件号。这需要结合GROUP BY和窗口函数-- 创建计算视图 CREATE VIEW calculate(JNO, PNO, maxsum) AS SELECT JNO, PNO, SUM(QTY) FROM SPJ GROUP BY JNO, PNO ORDER BY JNO, PNO; -- 查询每个工程供应量最大的零件 SELECT JNO, PNO, maxsum FROM calculate AS x WHERE x.maxsum ALL( SELECT maxsum FROM calculate AS y WHERE x.JNO y.JNO );这个方案先创建视图计算每个工程-零件组合的总供应量然后使用ALL关键字找出每个工程中供应量最大的零件。ALL表示大于等于子查询返回的所有值。替代方案是使用窗口函数SELECT JNO, PNO, total_qty FROM ( SELECT JNO, PNO, SUM(QTY) AS total_qty, RANK() OVER(PARTITION BY JNO ORDER BY SUM(QTY) DESC) AS rnk FROM SPJ GROUP BY JNO, PNO ) t WHERE rnk 1;这种方法更简洁性能通常也更好。窗口函数的RANK()会为每个工程的零件按供应量降序排名我们只需取排名第一的记录。