窗口函数基础

最新推荐文章于 2024-09-10 14:35:22 发布

你咋那么帅0

最新推荐文章于 2024-09-10 14:35:22 发布

阅读量254

点赞数

分类专栏： SQL 文章标签： sql hive

本文链接：https://blog.csdn.net/Meng_000000/article/details/125441908

版权

SQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

简单理解

在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。

示例：

1.先创建一张表
CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);
2.数据查询与分析
SELECT * FROM sales; 
结果：
+----------------+-------------+--------+
| sales_employee | fiscal_year | sale   |
+----------------+-------------+--------+
| Alice          |        2016 | 150.00 |
| Alice          |        2017 | 100.00 |
| Alice          |        2018 | 200.00 |
| Bob            |        2016 | 100.00 |
| Bob            |        2017 | 150.00 |
| Bob            |        2018 | 200.00 |
| John           |        2016 | 200.00 |
| John           |        2017 | 150.00 |
| John           |        2018 | 250.00 |
+----------------+-------------+--------+

查询所有员工销售额：SELECT SUM(sale) FROM sales; 
+-----------+
| SUM(sale) |
+-----------+
|   1500.00 |
+-----------+

按照年度统计销售额
SELECT fiscal_year,SUM(sale) FROM sales GROUP BY fiscal_year; 
+-------------+-----------+
| fiscal_year | SUM(sale) |
+-------------+-----------+
|        2016 |    450.00 |
|        2017 |    400.00 |
|        2018 |    650.00 |
+-------------+-----------+

与带有GROUP BY子句的聚合函数一样，窗口函数也对行的子集进行操作，但它们不会减
少查询返回的行数。
例如，以下查询返回每个员工的销售额，以及按会计年度计算的员工总销售额：
SELECT fiscal_year,sales_employee,sale,
SUM(sale) OVER (PARTITION BY fiscal_year) total_sales FROM sales; 
+-------------+----------------+--------+-------------+
| fiscal_year | sales_employee | sale   | total_sales |
+-------------+----------------+--------+-------------+
|        2016 | Alice          | 150.00 |      450.00 |
|        2016 | Bob            | 100.00 |      450.00 |
|        2016 | John           | 200.00 |      450.00 |
|        2017 | Alice          | 100.00 |      400.00 |
|        2017 | Bob            | 150.00 |      400.00 |
|        2017 | John           | 150.00 |      400.00 |
|        2018 | Alice          | 200.00 |      650.00 |
|        2018 | Bob            | 200.00 |      650.00 |
|        2018 | John           | 250.00 |      650.00 |
+-------------+----------------+--------+-------------+
结果解释：SELECT fiscal_year,sales_employee,sale 这些字段是表里面的数据，不用做改变，最后一个是窗口函数计算出来的结果（sum）
PARTITION BY fiscal_year：可以理解为将查询出来的结果按照fiscal_year进行分组（类似group by），并将窗口函数计算出来的结果放在每一行后面

基础

语法

over (partition by xxx order by xxx between xxx and xxx)
partition by：指定分区
order by：将分区的数据按照某个字段排序
between and：将分区的结果，在进行过滤，可以理解为按照某些条件，将分区进一步缩小了，比如：按照时间排序后，我只取当前时间之前的 3条数据，来进行处理

如果不指定rows between,默认为从起点到当前行;
如果不指定order by，则将分组内所有值累加;
关键是理解rows between含义,也叫做window子句：
preceding：往前
following：往后
current row：当前行
unbounded：起点
unbounded preceding 表示从前面的起点
unbounded following：表示到后面的终点

数据处理过程

1.先创建表
create table window_test_table (
id int,    --用户id
sq string,  --可以标识每个商品
cell_type int, --标识每个商品的类型，比如广告，非广告
rank int  --这次搜索下商品的位置，比如第一个广告商品就是1，后面的依次2，3，4...
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

窗口函数的实现，主要借助 Partitioned Table Function （即PTF）；
PTF的输入可以是：表、子查询或另一个PTF函数输出；
PTF输出也是一张表。
写一个相对复杂的sql，来看一下执行窗口函数时，数据的流转情况：
 select id,sq,cell_type,rank,
    row_number() over(partition by id  order by rank ) naturl_rank,
    rank() over(partition by id order by rank) as r,
    dense_rank() over(partition by  cell_type order by id) as dr  
 from window_test_table 
 group by id,sq,cell_type,rank;

在这里插入图片描述
以上分为三个过程

1.计算除窗口函数以外所有的其他运算，如：group by，join ，having等。上面的代码的第一阶段即为
select id,sq,cell_type,rank from window_test_table group by id, sq, cell_type, rank

2.将第一步的输出作为第一个 PTF 的输入，计算对应的窗口函数值。上面代码的第二阶段即为
select id,sq,cell_type,rank,naturl_rank,r from 
window(
<w>,--将第一阶段输出记为w
partition by id, --分区
order by rank, --窗口函数的order
[naturl_rank:row_number(),r:rank()] --窗口函数调用
)
注意：由于row_number()，rank() 两个函数对应的窗口是相同的（partition by id  order by rank），因此，这两个函数可以在一次shuffle中完成

3.将第二步的输出作为 第二个PTF 的输入，计算对应的窗口函数值。上面代码的第三阶段即为
select id,sq,cell_type,rank,naturl_rank,r,dr from 
window(
<w1>,--将第二阶段输出记为w1
partition by cell_type, --分区
order by id, --窗口函数的order
[dr:dense_rank()] --窗口函数调用
)