SQL高级查询与分析能力是处理复杂业务数据的关键，通过合理运用窗口函数、子查询、分组聚合等特性，可以快速完成多维度数据统计、排名计算、趋势分析等任务。下面结合实际业务场景，介绍几个典型的高级查询实战案例。
案例一：电商订单销售额排名统计
需求是统计每个店铺内订单销售额的排名，同时展示订单的基础信息，这里使用窗口函数实现排名计算。
-- 统计每个店铺内订单的销售额排名
SELECT
shop_id,
order_id,
order_amount,
order_date,
-- 按店铺分区，销售额降序排名
RANK() OVER (PARTITION BY shop_id ORDER BY order_amount DESC) AS shop_rank
FROM
order_table
WHERE
order_status = '已完成'
AND order_date BETWEEN '2024-01-01' AND '2024-01-31';
上述语句中，PARTITION BY shop_id表示按店铺分组计算排名，ORDER BY order_amount DESC指定排名依据为销售额降序，RANK()函数会生成对应的排名序号，相同销售额的订单会获得相同排名，且后续排名会跳过占用的位次。
案例二：用户月度消费趋势分析
需求是统计每个用户近3个月的消费总额，以及和上个月的消费差值，使用LAG窗口函数获取上月数据。
-- 用户月度消费趋势分析
WITH monthly_consume AS (
SELECT
user_id,
DATE_FORMAT(order_date, '%Y-%m') AS consume_month,
SUM(order_amount) AS total_amount
FROM
order_table
WHERE
order_status = '已完成'
GROUP BY
user_id, DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
user_id,
consume_month,
total_amount,
-- 获取上月的消费总额
LAG(total_amount, 1) OVER (PARTITION BY user_id ORDER BY consume_month) AS last_month_amount,
-- 计算月度消费差值
total_amount - LAG(total_amount, 1) OVER (PARTITION BY user_id ORDER BY consume_month) AS amount_diff
FROM
monthly_consume
ORDER BY
user_id, consume_month;
这里先通过CTE（公用表表达式）统计每个用户每个月的消费总额，再使用LAG()窗口函数按用户分区、月份排序，获取上个月的消费数据，进而计算差值，清晰展示用户的消费变化趋势。
案例三：商品分类销售占比统计
需求是统计每个商品分类的销售额，以及该分类销售额占全部分类总销售额的比例，结合分组聚合和子查询实现。
-- 商品分类销售占比统计
SELECT
c.category_name,
SUM(o.order_amount) AS category_total_amount,
-- 计算分类销售额占比
ROUND(
SUM(o.order_amount) / (SELECT SUM(order_amount) FROM order_table WHERE order_status = '已完成') * 100,
2
) AS amount_ratio
FROM
order_table o
INNER JOIN
goods_table g ON o.goods_id = g.goods_id
INNER JOIN
category_table c ON g.category_id = c.category_id
WHERE
o.order_status = '已完成'
GROUP BY
c.category_id, c.category_name
ORDER BY
category_total_amount DESC;
该查询先通过关联获取商品分类信息，按分类分组统计销售额，再通过子查询计算所有已完成订单的总销售额，最终算出每个分类的销售占比，方便运营人员了解不同分类的销售贡献。
案例四：连续登录用户统计
需求是统计连续登录至少3天的用户，使用窗口函数和日期差值计算实现。
-- 统计连续登录至少3天的用户
WITH user_login_mark AS (
SELECT
user_id,
login_date,
-- 用登录日期减去排名，得到连续登录的标记日期
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS mark_date
FROM
user_login_table
GROUP BY
user_id, login_date
),
continuous_login AS (
SELECT
user_id,
mark_date,
COUNT(*) AS continuous_days
FROM
user_login_mark
GROUP BY
user_id, mark_date
HAVING
COUNT(*) >= 3
)
SELECT
user_id,
continuous_days
FROM
continuous_login
ORDER BY
continuous_days DESC;
首先通过ROW_NUMBER()窗口函数给每个用户的登录日期排序，用登录日期减去排序值得到标记日期，连续登录的日期会得到相同的标记日期，再按用户和标记日期分组统计天数，筛选出连续天数大于等于3的记录即可。
高级查询使用注意事项
- 窗口函数不会减少原表的行数，适合需要保留明细数据同时计算聚合结果的场景，而GROUP BY分组聚合会减少行数，需要根据需求选择。
- 子查询尽量放在FROM子句中作为派生表，避免放在SELECT子句里导致查询性能下降。
- CTE可以让复杂查询的逻辑更清晰，适合多层嵌套的查询场景，但部分数据库对CTE的优化不如子查询，需要根据实际数据库特性选择。
- 涉及大表查询时，先通过WHERE条件过滤数据，再进行聚合或窗口函数计算，减少计算的数据量。