欢迎光临
hive窗口函数学习总结
   

hive窗口函数学习总结

1.窗口函数的定义

Hive中的窗口函数主要用于在数据集的子集(窗口)上执行计算。窗口函数可以解决一些复杂的数据分析问题,例如计算移动平均值、累计和、排名等。

例如,如果你想要计算每个用户的购买总额排名,或者计算每个月的销售额是前三个月的平均值的多少倍,这些问题都可以通过窗口函数来解决。

窗口函数的主要特点是可以在不改变原始数据行数的情况下,为每一行提供一个基于窗口的计算结果。这与聚合函数不同,聚合函数会将多行数据聚合成一行。

2. 窗口函数学习路径

  • 理解窗口函数的基本概念:窗口函数是对数据集的子集(窗口)进行操作的函数。它们允许用户在数据集的子集上执行聚合操作,而不会减少行数。
  • 学习窗口函数的语法:窗口函数的语法通常包括函数名称、OVER关键字、以及定义窗口的子句。例如,RANK() OVER (PARTITION BY column1 ORDER BY column2)。
  • 学习各种窗口函数:有许多不同的窗口函数,包括RANK()、ROW_NUMBER()、LEAD()、LAG()、SUM()、AVG()等。每个函数都有其特定的用途,需要分别学习和理解。
  • 通过实例进行学习:理论学习是基础,但通过实例进行学习是最有效的方法。可以找一些实际的问题,尝试使用窗口函数来解决。
  • 深入理解窗口定义:窗口函数的强大之处在于其灵活的窗口定义,可以通过PARTITION BY、ORDER BY和ROWS/RANGE子句来定义。理解这些子句的含义和用法,对于掌握窗口函数至关重要。
  • 多做练习:理论学习和实例学习之后,需要通过大量的练习来巩固和提高。可以从简单的问题开始,逐步提高难度。
  • 阅读官方文档:官方文档通常是最权威、最全面的学习资源。在学习过程中遇到问题或者想要深入理解某个功能时,可以参考官方文档。

    3.窗口函数的语法

    <窗口函数> ( <表达式> ) OVER ( [PARTITION BY <列名1>, <列名2>, …] [ORDER BY <列名> [ASC | DESC]] [ROWS | RANGE <窗口范围>] )

    各部分的含义如下:

    <窗口函数>:窗口函数的名称,例如 SUM、AVG、ROW_NUMBER、RANK 等。

    <表达式>:窗口函数的参数,通常是一个列名。

    OVER:关键字,表示开始定义窗口。

    PARTITION BY <列名1>, <列名2>, …:(可选)按照指定的一个或多个列将数据分区。每个分区都会单独进行窗口函数的计算。

    ORDER BY <列名> [ASC | DESC]:(可选)在每个分区内,按照指定的列对数据进行排序。默认为升序(ASC),也可以指定降序(DESC)。

    ROWS | RANGE <窗口范围>:(可选)定义窗口的范围。ROWS 表示按照物理行数定义窗口,RANGE 表示按照列的值定义窗口。窗口范围可以是 UNBOUNDED PRECEDING(从分区开始到当前行)、N PRECEDING(从当前行前N行到当前行)、BETWEEN N PRECEDING AND M FOLLOWING(从当前行前N行到当前行后M行)等。

    一个示例:以下SQL查询返回每个部门的每个员工的工资,以及他们所在部门的平均工资:

    SELECT depname, empno, salary, AVG(salary) 
    OVER (PARTITION BY depname) AS avg_salary 
    FROM empsalary;
    

    PARTITION BY 未指定如何分区?

    • 如果在窗口函数中没有指定 PARTITION BY 子句,那么整个结果集将被视为一个单一的区进行计算。在这种情况下,窗口函数会在整个结果集上进行操作,而不是在各个分区上分别进行。这意味着,如果你使用的是排名函数(如 RANK() 或 ROW_NUMBER()),那么生成的排名将会是在整个结果集范围内的,而不是在各个分区内的。

      表达式如何写?

      • <表达式> 在窗口函数中的必要性取决于你使用的具体窗口函数。对于一些窗口函数,如 SUM()、AVG()、MAX()、MIN() 等,<表达式> 通常是必填的,因为这些函数需要对某个列或表达式进行计算。然而,对于一些其他的窗口函数,如 ROW_NUMBER()、RANK()、DENSE_RANK() 等,<表达式> 是不需要的,因为这些函数并不需要对某个特定的列进行计算,它们只是基于窗口中的行顺序生成一个值。

        ORDER BY 如果不写默认是怎么排序

        • 如果在窗口函数中没有指定 ORDER BY 子句,那么窗口函数将不会对数据进行排序,数据的顺序将由查询结果集的默认顺序决定。需要注意的是,如果你使用的窗口函数是 RANK()、ROW_NUMBER()、DENSE_RANK() 等需要依赖排序的函数,那么不指定 ORDER BY 子句可能会得到不符合预期的结果,因为这些函数的结果依赖于数据的顺序。如果你希望窗口函数的结果能够按照某种特定的顺序,那么最好显式地指定 ORDER BY 子句。

          示例中SUM(rn_30) OVER (ORDER BY rn_30) 这个表达式中的order by的作用

          SUM(rn_30) OVER (ORDER BY rn_30) 这个表达式中的 ORDER BY 是不能省略的。因为在使用窗口函数时,如果你想要计算的是累计和(也就是当前行及其之前的所有行的和),那么 ORDER BY 是必须的,因为它定义了数据的顺序。

          如果省略 ORDER BY,那么 SUM(rn_30) OVER () 将会计算整个结果集的 rn_30 的总和,而不是每行及其之前的行的 rn_30 的总和。

          rank函数的用法

          RANK() 是一种窗口函数,用于为结果集中的每一行分配一个唯一的排名。排名的顺序由 ORDER BY 子句决定。

          基本语法如下:

          RANK() OVER ( [PARTITION BY <列名1>, <列名2>, …] ORDER BY <列名> [ASC | DESC] )

          PARTITION BY <列名1>, <列名2>, …:(可选)按照指定的一个或多个列将数据分区。每个分区都会单独进行排名。

          ORDER BY <列名> [ASC | DESC]:在每个分区内,按照指定的列对数据进行排序。默认为升序(ASC),也可以指定降序(DESC)。

          例如,以下SQL查询返回每个部门的每个员工的工资,以及他们在部门内的工资排名:

          SELECT depname, empno, salary, RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank

          FROM empsalary;

          需要注意的是,RANK() 函数在处理相同值时会跳过一些排名。例如,如果有两行具有相同的值,则它们都会被赋予相同的排名,但下一个排名将会被跳过。例如,如果两行都是排名第1,那么下一行的排名将会是第3。如果你不希望跳过任何排名,可以使用 DENSE_RANK() 函数。

          ROW_NUMBER函数的用法

          ROW_NUMBER() 是一种窗口函数,用于为结果集中的每一行分配一个唯一的序号。序号的顺序由 ORDER BY 子句决定。

          基本语法如下:

          ROW_NUMBER() OVER ( [PARTITION BY <列名1>, <列名2>, …] ORDER BY <列名>

          [ASC | DESC] ) PARTITION BY <列名1>, <列名2>,

          …:(可选)按照指定的一个或多个列将数据分区。每个分区都会单独进行序号的分配。

          ORDER BY <列名> [ASC |

          DESC]:在每个分区内,按照指定的列对数据进行排序。默认为升序(ASC),也可以指定降序(DESC)。

          例如,以下SQL查询返回每个部门的每个员工的工资,以及他们在部门内的工资序号:

          SELECT depname, empno, salary, ROW_NUMBER() OVER (PARTITION BY depname

          ORDER BY salary DESC) AS row_number FROM empsalary;

          需要注意的是,ROW_NUMBER() 函数会为每一行分配一个唯一的序号,即使两行的值完全相同。如果你希望对相同的值分配相同的序号,可以使用 RANK() 或 DENSE_RANK() 函数。

          DENSE_RANK 和 RANK的区别

          RANK() 和 DENSE_RANK() 都是 SQL 中的窗口函数,用于为结果集中的每一行分配一个排名。它们的主要区别在于如何处理相同值的情况。

          RANK():当遇到相同的值时,RANK() 会为这些值分配相同的排名。但是,它会跳过接下来的一些排名。例如,如果有两行都是排名第1,那么下一行的排名将会是第3。

          DENSE_RANK():与 RANK() 类似,DENSE_RANK() 也会为相同的值分配相同的排名。但是,它不会跳过任何排名。在上述的例子中,如果使用 DENSE_RANK(),那么下一行的排名将会是第2。

          这是一个例子来说明它们的区别:

          SELECT value, RANK() 
          OVER (ORDER BY value) AS rank, DENSE_RANK() OVER (ORDER BY value) AS dense_rank
          FROM (VALUES (1), (2), (2), (3), (4), (4), (4)) AS t(value);
          结果如下:
          value	rank	dense_rank
          1	1	1
          2	2	2
          2	2	2
          3	4	3
          4	5	4
          4	5	4
          4	5	4
          

          可以看到,RANK() 在处理值为2的行时跳过了排名3,而 DENSE_RANK() 则没有跳过任何排名。

           
打赏
版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《hive窗口函数学习总结》
文章链接:https://goodmancom.com/wl/176022.html