在业务系统的数据查询中，排序、分组与统计是最基础也最容易写出性能瓶颈的操作。下面介绍十种较新的实用技巧，帮助你用更简洁的SQL完成复杂分析。
一、使用窗口函数进行组内排序
传统做法是用子查询找最大值，窗口函数可以直接在每行标出组内排名。
select dept_id, emp_name, salary, rank() over (partition by dept_id order by salary desc) as dept_rank from employee;
二、用ROW_NUMBER去重取最新记录
按某字段分组后只保留一条最新数据，可以用row_number编号再过滤。
with ranked as (
select *,
row_number() over (partition by user_id order by create_time desc) as rn
from user_log
)
select * from ranked where rn = 1;
三、FILTER子句简化条件聚合
在PostgreSQL等数据库中，使用filter替代case when让统计更直观。
select dept_id, count(*) filter (where status = 'active') as active_cnt, count(*) filter (where status = 'left') as left_cnt from employee group by dept_id;
四、ROLLUP实现多层级小计
一次查询同时算出分组小计与总计。
select region, city, sum(sales) as total from orders group by rollup(region, city);
五、用NULLS LAST控制空值排序
排序时让空值始终在末尾，避免影响业务阅读。
select * from product order by price desc nulls last;
六、GROUPING SETS自由组合统计维度
相比rollup的固定层级，可指定任意维度组合。
select region, category, sum(amount) from sales group by grouping sets ((region), (category), (region, category));
七、用PERCENT_RANK算相对排名
获取行在组内的百分比位置，适合做分布分析。
select emp_name, percent_rank() over (order by score) as pct from evaluation;
八、LAG与LEAD计算环比
不用自关联即可拿到前一条与后一条数据。
select month, revenue, lag(revenue) over (order by month) as last_month, lead(revenue) over (order by month) as next_month from monthly_report;
九、用HAVING结合窗口函数过滤组
先算组内指标再筛选，避免重复聚合。
select dept_id, avg_salary from (
select
dept_id,
avg(salary) over (partition by dept_id) as avg_salary
from employee
) t
where avg_salary > 10000;
十、CTE让统计逻辑更清晰
把多步统计拆成公用表表达式，方便维护和调试。
with base as ( select user_id, count(*) as cnt from action group by user_id ) select case when cnt > 10 then 'high' else 'low' end as level, count(*) from base group by level;
以上十种技巧覆盖了日常排序、分组与统计的大部分场景。合理运用能让SQL更短、更快，也更容易让团队理解。