这些SQL排名及分析函数，你知道吗？（5）

万能数据的小草

发布于 2024-07-23 15:22:40

930

发布于 2024-07-23 15:22:40

文章被收录于专栏：万能的小草

1. 窗口函数理解

窗口函数作用于一个窗口，窗口是由一个over子句定义的多行记录；
聚合函数对其作用的每一组记录输出一条结果，而窗口函数对其作用的窗口中的每一行记录输出一条结果；
窗口函数一般在OLAP分析、制作报表过程中使用；

窗口函数格式：

聚合函数 over()
聚合函数 over(partition by 字段） --分组
聚合函数 over(order by 字段） --框架子句：分组中的行如何排序；在计算中包含哪些行

2.排名函数

SQL的排名函数主要有ROW_NUMBER(), RANK(), 和 DENSE_RANK()，它们分别返回行号、排名和紧密排名。这三个函数的区别在于处理并列排名的方式。

例如，假设我们有一张学生考试成绩表：

对这张表使用这三个函数进行排名，结果如下：

SELECT student_id, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
RANK() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

学生ID  成绩  row_number  rank  dense_rank
1  90  1  1  1
2  85  2  2  2
3  85  3  2  2
4  80  4  4  3

可以看到，ROW_NUMBER()给每行赋予一个唯一的整数，即使成绩相同。RANK()给相同的成绩赋予相同的排名，但会跳过下一个排名（在该例中，没有排名3）。而DENSE_RANK()则给相同的成绩赋予相同的排名，不会跳过任何排名。

至于窗口函数，例如 SUM() OVER()和AVG() OVER()等，它们通常用于计算滚动统计，如移动平均或累计和。例如：

SELECT
date
    ,sales
    ,SUM(sales) OVER (
ORDER BY
date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales,
AVG(sales) OVER (
ORDER BY
date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average_salesFROM sales_data;

这个查询计算了截至每一天的累积销售额（cumulative_sales），以及过去7天（包括当天）的移动平均销售额（moving_average_sales）。

3.分析函数

3.1 first_value/last_value

FIRST_VALUE() 和 LAST_VALUE()：这两个函数分别返回在窗口中第一个和最后一个值。

例如，如果我们想要获取每个部门中按照工资排名的第一名和最后一名的员工工资，可以使用以下查询：

SELECT department, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_salary
FROM employees;

3.2 lag/lead

按偏移量取当前行之前(lag)/之后（lead）第几行的值

例如，如果我们想要得到每个员工的工资以及他们前一个和后一个员工的工资（按照工资排序），可以使用以下查询：

SELECT name, salary,
       LAG(salary) OVER (ORDER BY salary) as previous_salary,
LEAD(salary) OVER (ORDER BY salary) as next_salary
FROM employees;

3.4 cume_dist

计算累计分布，求累计分布，相当于求分组中值小于等于当前值的行数占分组总行数的比例

SELECT name, salary,
       CUME_DIST() OVER (ORDER BY salary) as salary_percentile
FROM employees;

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-10-13，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自万能数据的小草微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度