SQL 数据库中的窗口函数排名函数知识点运用

xiaoshi 05-30 12 抢沙发

默认

摘要： ...

SQL窗口函数中的排名函数：高效数据分析的秘密武器

在数据分析领域，SQL窗口函数特别是排名函数已经成为数据工作者不可或缺的工具。这些函数能够在不改变原始数据行数的情况下，为每一行数据计算排名、分组统计等复杂指标，极大提升了数据分析的效率和灵活性。

什么是窗口函数中的排名函数？

SQL窗口函数中的排名函数专门用于在结果集中为行分配排名值。与普通聚合函数不同，这些排名函数不会将多行合并为一行，而是为查询结果中的每一行计算一个排名值，同时保留原始数据的所有细节。

排名函数主要包括ROW_NUMBER()、RANK()、DENSE_RANK()和NTILE()四种，每种都有其特定的使用场景和计算规则。理解它们的区别是掌握SQL高级查询的关键。

四大排名函数详解

1. ROW_NUMBER()：简单的行编号

ROW_NUMBER()是最基础的排名函数，它为结果集中的每一行分配一个唯一的连续整数，从1开始递增。即使存在相同的值，ROW_NUMBER()也会为它们分配不同的序号。

SELECT 
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

这个查询会按照部门分组，在每个部门内部根据薪水降序为员工编号。即使同一部门有相同薪水的员工，他们也会获得不同的行号。

2. RANK()：处理并列情况的排名

RANK()函数与ROW_NUMBER()类似，但当遇到相同值时，它会为这些行分配相同的排名，并跳过后续的排名数字。例如，如果有两个第一名，下一个排名将是第三名。

SELECT 
    product_name,
    category,
    sales_amount,
    RANK() OVER (PARTITION BY category ORDER BY sales_amount DESC) AS sales_rank
FROM products;

这个查询会按照产品类别分组，在每个类别内部根据销售额降序排名。如果有两个产品销售额相同，它们会获得相同的排名，下一个产品将跳过一位排名。

3. DENSE_RANK()：不跳过排名的并列处理

DENSE_RANK()与RANK()类似，都会为相同值的行分配相同排名。不同之处在于DENSE_RANK()不会跳过排名数字。在前例中，如果有两个第一名，下一个排名将是第二名而非第三名。

SELECT 
    student_name,
    class,
    exam_score,
    DENSE_RANK() OVER (PARTITION BY class ORDER BY exam_score DESC) AS score_rank
FROM students;

这个查询适合教育场景，可以更直观地显示学生在班级中的成绩排名情况，不会因为并列情况导致排名数字跳跃过大。

4. NTILE()：数据分桶利器

NTILE()函数将结果集分成指定数量的桶，并为每一行分配它所属的桶号。这在数据分位数分析、抽样等场景非常有用。

SELECT 
    customer_id,
    total_purchases,
    NTILE(4) OVER (ORDER BY total_purchases DESC) AS quartile
FROM customers;

这个查询将客户按照总购买金额分成四个等分组（四分位数），便于进行客户分层分析。

窗口函数的高级应用技巧

多维度分区排序

窗口函数的强大之处在于可以同时按照多个维度进行分区和排序：

SELECT 
    salesperson,
    region,
    quarter,
    sales_amount,
    RANK() OVER (PARTITION BY region, quarter ORDER BY sales_amount DESC) AS regional_quarterly_rank
FROM sales_data;

这个查询按照地区和季度两个维度进行分区，然后在每个分区内根据销售额进行排名，可以同时分析销售人员在地区内和季度内的表现。

动态窗口范围

窗口函数还支持定义更复杂的窗口范围，如前N行、后N行或当前行前后范围：

SELECT 
    date,
    product_id,
    daily_sales,
    AVG(daily_sales) OVER (PARTITION BY product_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_day_avg
FROM product_sales;

这个查询计算每个产品最近3天（包括当天）的销售平均值，用于分析短期销售趋势。

实际业务场景应用

电商平台的热销商品分析

SELECT 
    product_id,
    product_name,
    category,
    monthly_sales,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY monthly_sales DESC) AS category_rank,
    RANK() OVER (ORDER BY monthly_sales DESC) AS overall_rank
FROM products
WHERE month = '2023-11';

这个查询可以同时获取商品在所属类别中的排名和全平台总排名，便于制定营销策略。

金融行业的客户价值分层

SELECT 
    customer_id,
    total_assets,
    NTILE(5) OVER (ORDER BY total_assets DESC) AS asset_quintile,
    CASE 
        WHEN NTILE(5) OVER (ORDER BY total_assets DESC) = 1 THEN '高净值'
        WHEN NTILE(5) OVER (ORDER BY total_assets DESC) <= 3 THEN '中产'
        ELSE '普通'
    END AS customer_segment
FROM customers;

这个查询将客户按资产分为五等分，并自动打上客户分层的标签，便于精准营销和服务分级。