HiveQL DQL9—窗口函数

最新推荐文章于 2022-10-21 19:38:54 发布
最新推荐文章于 2022-10-21 19:38:54 发布
阅读量302 收藏 2
点赞数
分类专栏： Hive 文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/CPP_MAYIBO/article/details/104202719
版权

目录

概述

Hive从0.11.0开始支持窗口函数，这是一组特殊的函数，可以扫描多个输入行来计算每个输出值。窗口函数通常和OVER, PARTITION BY, ORDER BY 以及 windowing specification一起使用。不同于和GROUP BY一起使用的常规聚合函数（每组只有一个结果值），窗口函数在窗口上工作，其中的输入行会被排序并且通过OVER 和 PARTITION子句使用灵活的条件表达进行分组。窗口函数也会提供一个聚合结果，但不对结果集进行分组，它们会随每条记录多次返回group值。相比常规的GROUP BY子句，窗口函数提供了极大的灵活性和功能性，使HQL的特殊聚合更加简单、强大。
窗口函数的语法格式如下：

Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

Function (arg1,…, argn) 是下面几类函数中的任意一个

  • 窗口聚合函数 ：也就是常规聚合函数，如sum(…)、max(…)、min(…)、avg(…)、count(…)等
  • 窗口排序函数：用以对数据进行排序，如rank(…)、row_number(…)等
  • 窗口分析函数：用以统计和比较的函数，如lead(…)、lag(…)、first_value(…)、last_value(…)等

OVER [PARTITION BY <…>] 子句类似于GROUP BY子句，它按列将行划分为一个或多个分区包含相同值的组。这些逻辑组被称为分区（与分区表中的分区概念不同）。忽略PARTITION BY语句的操作会作用于表中的所有行上。
[ORDER BY <…>] 子句和常规的ORDER BY是一样的。它确保由 PARTITION BY 子句生成的行是按照特定方式排序的，如升序或降序。

窗口聚合函数

在窗口函数中使用聚合函数会有比GROUP BY更好的灵活性。从Hive 2.2.0开始（见HIVE-13453），DISTINCT 支持和窗口函数中的聚合函数一起使用。

示例

  1. 准备测试表及测试数据
> CREATE TABLE IF NOT EXISTS employee_contract
(
name string,
dept_num int,
employee_id int,
salary int,
type string,
start_date date
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;

测试数据如下：

cat employee_contract.txt
Michael|1000|100|5000|full|2014-01-29
Will|1000|101|4000|full|2013-10-02
Will|1000|101|4000|part|2014-10-02
Steven|1000|102|6400|part|2012-11-03
Lucy|1000|103|5500|full|2010-01-03
Lily|1001|104|5000|part|2014-11-29
Jess|1001|105|6000|part|2014-12-02
Mike|1001|106|6400|part|2013-11-03
Wei|1002|107|7000|part|2010-04-03
Yun|1002|108|5500|full|2014-01-29
Richard|1002|109|8000|full|2013-09-01

加载数据

> LOAD DATA INPATH
'/tmp/employee_contract.txt' 
OVERWRITE INTO TABLE employee_contract;
  1. 常规聚合函数作为窗口函数使用
> SELECT 
name, 
dept_num as deptno, 
salary,
count(*) OVER (PARTITION BY dept_num) as cnt,
count(distinct dept_num) OVER (PARTITION BY dept_num) as dcnt,
sum(salary) OVER(PARTITION BY dept_num ORDER BY dept_num) as sum1,
sum(salary) OVER(ORDER BY dept_num) as sum2,
sum(salary) OVER(ORDER BY dept_num, name) as sum3
FROM employee_contract
ORDER BY deptno, name;
+----------+---------+---------+------+-------+--------+--------+--------+
|   name   | deptno  | salary  | cnt  | dcnt  |  sum1  |  sum2  |  sum3  |
+----------+---------+---------+------+-------+--------+--------+--------+
| Lucy     | 1000    | 5500    | 5    | 1     | 24900  | 24900  | 5500   |
| Michael  | 1000    | 5000    | 5    | 1     | 24900  | 24900  | 10500  |
| Steven   | 1000    | 6400    | 5    | 1     | 24900  | 24900  | 16900  |
| Will     | 1000    | 4000    | 5    | 1     | 24900  | 24900  | 24900  |
| Will     | 1000    | 4000    | 5    | 1     | 24900  | 24900  | 24900  |
| Jess     | 1001    | 6000    | 3    | 1     | 17400  | 42300  | 30900  |
| Lily     | 1001    | 5000    | 3    | 1     | 17400  | 42300  | 35900  |
| Mike     | 1001    | 6400    | 3    | 1     | 17400  | 42300  | 42300  |
| Richard  | 1002    | 8000    | 3    | 1     | 20500  | 62800  | 50300  |
| Wei      | 1002    | 7000    | 3    | 1     | 20500  | 62800  | 57300  |
| Yun      | 1002    | 5500    | 3    | 1     | 20500  | 62800  | 62800  |
+----------+---------+---------+------+-------+--------+--------+--------+

窗口排序函数

窗口排序函数在返回的数据中提供特定组内的排序数据信息，如行号和列组。常用的窗口排序函数包括：

  • RANK：对组中的项进行排序，排名相等会在名次中留下空位，例如根据特定条件查找前N行。
  • ROW_NUMBER： 根据分区和顺序规范，为每行指定一个从1开始的唯一序列号。
  • DENSE_RANK：类似于RANK，但排名相等不会在名次中留下空位，例如，如果使用DENSE_RANK对一场比赛进行排名，并且有两名选手并列第二，我们将看到这两名选手都排在第二位，而下一名选手则排在第三位。但是，RANK函数将两个人排在第二位，而下一个人将排在第四位
  • CUME_DIST：小于等于当前值的行数/分组内总行数
  • PERCENT_RANK：返回(current rank - 1)/(total number of rows - 1)的百分比值。
  • NTILE：将一个有序的数据集划分为若干个bucket，并为每一行分配一个适当的bucket号。它可用于将行分成相等的集合，并为每行指定一个数字。

示例

> SELECT 
name, 
dept_num as deptno, 
salary,
row_number() OVER () as rnum,
rank() OVER (PARTITION BY dept_num ORDER BY salary) as rk, 
dense_rank() OVER (PARTITION BY dept_num ORDER BY salary) as drk,
percent_rank() OVER(PARTITION BY dept_num ORDER BY salary) as prk,
ntile(4) OVER(PARTITION BY dept_num ORDER BY salary) as ntile
FROM employee_contract
ORDER BY deptno, name;
+----------+---------+---------+-------+-----+------+-------+--------+
|   name   | deptno  | salary  | rnum  | rk  | drk  |  prk  | ntile  |
+----------+---------+---------+-------+-----+------+-------+--------+
| Lucy     | 1000    | 5500    | 7     | 4   | 3    | 0.75  | 3      |
| Michael  | 1000    | 5000    | 11    | 3   | 2    | 0.5   | 2      |
| Steven   | 1000    | 6400    | 8     | 5   | 4    | 1.0   | 4      |
| Will     | 1000    | 4000    | 9     | 1   | 1    | 0.0   | 1      |
| Will     | 1000    | 4000    | 10    | 1   | 1    | 0.0   | 1      |
| Jess     | 1001    | 6000    | 5     | 2   | 2    | 0.5   | 2      |
| Lily     | 1001    | 5000    | 6     | 1   | 1    | 0.0   | 1      |
| Mike     | 1001    | 6400    | 4     | 3   | 3    | 1.0   | 3      |
| Richard  | 1002    | 8000    | 1     | 3   | 3    | 1.0   | 3      |
| Wei      | 1002    | 7000    | 3     | 2   | 2    | 0.5   | 2      |
| Yun      | 1002    | 5500    | 2     | 1   | 1    | 0.0   | 1      |
+----------+---------+---------+-------+-----+------+-------+--------+

从Hive 2.1.0开始，可以在OVER子句中使用聚合函数

> SELECT
dept_num,
rank() OVER (PARTITION BY dept_num ORDER BY sum(salary)) as rk
FROM employee_contract
GROUP BY dept_num;
+-----------+-----+
| dept_num  | rk  |
+-----------+-----+
| 1000      | 1   |
| 1001      | 1   |
| 1002      | 1   |
+-----------+-----+

窗口分析函数

窗口分析函数提供了扩展的数据分析功能，如获取有序集中的 lag, lead, 最后一行或第一行。常用的分析函数如下：

  • CUME_DIST：计算当前值小于等于(number of rows ≤ current row)/(total number of rows)值的行数
  • LEAD(value_expr[,offset[,default]])： 用于从下一行返回数据。第一个参数为列名，第二个参数指定要lead的行数（偏移量），可选，默认为1；第三个参数表示如果未指定默认值，则函数返回[，default]或如不指定default时则返回NULL。
  • LAG(value_expr[,offset[,default]]) ： 用于从上一行接收数据。第一个参数为列名，第二个参数指定要lag的行数（偏移量），可选，默认为1；第三个参数表示如果未指定默认值，则函数返回[，default]或如不指定default时则返回NULL。
  • FIRST_VALUE ： 取分组内排序后的第一行。最多接收2个参数，第一个参数为列名，第二个参数是一个布尔值（可选），默认是false，如果设置为true，则忽略NULL值。
  • LAST_VALUE ： 取分组内排序后的最后一行。最多接收2个参数，第一个参数为列名，第二个参数是一个布尔值（可选），默认是false，如果设置为true，则忽略NULL值。

示例

> SELECT 
name,
dept_num as deptno,
salary,
round(cume_dist() OVER (PARTITION BY dept_num ORDER BY salary), 2) as cume,
lead(salary, 2) OVER (PARTITION BY dept_num ORDER BY salary) as lead,
lag(salary, 2, 0) OVER (PARTITION BY dept_num ORDER BY salary) as lag,
first_value(salary) OVER (PARTITION BY dept_num ORDER BY salary) as fval,
last_value(salary) OVER (PARTITION BY dept_num ORDER BY salary) as lvalue,
last_value(salary) OVER (PARTITION BY dept_num ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lvalue2
FROM employee_contract 
ORDER BY deptno, salary;
+----------+---------+---------+-------+-------+-------+-------+---------+----------+
|   name   | deptno  | salary  | cume  | lead  |  lag  | fval  | lvalue  | lvalue2  |
+----------+---------+---------+-------+-------+-------+-------+---------+----------+
| Will     | 1000    | 4000    | 0.4   | 5500  | 0     | 4000  | 4000    | 6400     |
| Will     | 1000    | 4000    | 0.4   | 5000  | 0     | 4000  | 4000    | 6400     |
| Michael  | 1000    | 5000    | 0.6   | 6400  | 4000  | 4000  | 5000    | 6400     |
| Lucy     | 1000    | 5500    | 0.8   | NULL  | 4000  | 4000  | 5500    | 6400     |
| Steven   | 1000    | 6400    | 1.0   | NULL  | 5000  | 4000  | 6400    | 6400     |
| Lily     | 1001    | 5000    | 0.33  | 6400  | 0     | 5000  | 5000    | 6400     |
| Jess     | 1001    | 6000    | 0.67  | NULL  | 0     | 5000  | 6000    | 6400     |
| Mike     | 1001    | 6400    | 1.0   | NULL  | 5000  | 5000  | 6400    | 6400     |
| Yun      | 1002    | 5500    | 0.33  | 8000  | 0     | 5500  | 5500    | 8000     |
| Wei      | 1002    | 7000    | 0.67  | NULL  | 0     | 5500  | 7000    | 8000     |
| Richard  | 1002    | 8000    | 1.0   | NULL  | 5500  | 5500  | 8000    | 8000     |
+----------+---------+---------+-------+-------+-------+-------+---------+----------+

对于last_value，结果（字段 lval）有点出乎意料，这是因为所使用的默认窗口子句是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这在示例中意味着当前行将始终是最后一个值。将窗口子句改为 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 就可以得到预期的结果（见列lvalue2）。

窗口表达式

[<window_expression>] 用于进一步细分结果并应用窗口函数，有2种类型的窗口：Row Type 和 Range Type。

注意：根据JIRA https://issues.apache.org/jira/browse/HIVE-4797， rank(…), ntile(…), dense_rank(…), cume_dist(…), percent_rank(…), lead(…), lag(…) 和 row_number(…) 函数还不支持和窗口表达式一起使用。

Row Type

对于row type的窗口，其以当前行之前或之后的行号为处理单元。常用的语法如下：

ROWS BETWEEN <start_expr> AND <end_expr>

<start_expr> 是如下任一语句：

  • UNBOUNDED PRECEDING
  • CURRENT ROW
  • N PRECEDING or FOLLOWING
    <end_expr> 是如下任一语句：
  • UNBOUNDED FOLLOWING
  • CURRENT ROW
  • N PRECEDING or FOLLOWING

下面详细介绍如何使用窗口表达式及其组合：

  • BETWEEN … AND：用以指定窗口的起始点和结束点。第一个表达式指定起始点，第二个指定结束点。如果省略了 BETWEEN…AND（如ROWS * N PRECEDING 或 ROWS UNBOUNDED PRECEDING），hive会将其看作起始点，而结束点默认是当前行。
  • N PRECEDING or FOLLOWING: 用以表示当前行之前或之后的N行
  • UNBOUNDED PRECEDING: 表示窗口从分区的第一行开始。这是起始点的说明规范，不能用以结束点说明规范
  • UNBOUNDED FOLLOWING:表示窗口以分区的最后一行作为结束点。这是结束点的说明规范，不能用以起始点的说明规范
  • UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 表示每行的第一行和最后一行，也就是表中的所有行
  • CURRENT ROW: 作为起始点，CURRENT ROW指定从当前行或值开始，这取决于是否指定了ROW或RANGE，这种情况下，结束点不能是 M * PRECEDING；而作为结束点，CURRENT ROW指定当前行或值作为窗口的结束，这取决于是否指定了ROW或RANGE，这种情况下，起始点不能是 M FOLLOWING

下图可以帮助我们更清楚地理解前面的定义：
在这里插入图片描述
示例：window expression preceding and following

> SELECT 
name, dept_num as dno, salary AS sal,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) win2,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) win3,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) win4,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) win5,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS 2 PRECEDING) win6,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS UNBOUNDED PRECEDING) win7
FROM employee_contract
ORDER BY dno, name;
+----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
|   name   |  dno  |  sal  | win1  | win2  | win3  | win4  | win5  | win6  | win7  |
+----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| Lucy     | 1000  | 5500  | 5500  | 6400  | 6400  | NULL  | 6400  | 5500  | 5500  |
| Michael  | 1000  | 5000  | 5500  | 6400  | 6400  | 5500  | 6400  | 5500  | 5500  |
| Steven   | 1000  | 6400  | 6400  | 6400  | 6400  | 5500  | 4000  | 6400  | 6400  |
| Will     | 1000  | 4000  | 6400  | 6400  | 4000  | 6400  | NULL  | 6400  | 6400  |
| Will     | 1000  | 4000  | 6400  | 6400  | 6400  | 6400  | 4000  | 6400  | 6400  |
| Jess     | 1001  | 6000  | 6000  | 6400  | 6400  | NULL  | 6400  | 6000  | 6000  |
| Lily     | 1001  | 5000  | 6000  | 6400  | 6400  | 6000  | 6400  | 6000  | 6000  |
| Mike     | 1001  | 6400  | 6400  | 6400  | 6400  | 6000  | NULL  | 6400  | 6400  |
| Richard  | 1002  | 8000  | 8000  | 8000  | 8000  | NULL  | 7000  | 8000  | 8000  |
| Wei      | 1002  | 7000  | 8000  | 8000  | 8000  | 8000  | 5500  | 8000  | 8000  |
| Yun      | 1002  | 5500  | 8000  | 8000  | 7000  | 8000  | NULL  | 8000  | 8000  |
+----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+

示例：window expression current_row

> SELECT 
name, dept_num as dno, salary AS sal,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND CURRENT ROW) win8,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) win9,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) win10,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) win11,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) win12,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) win13,
max(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) win14
FROM employee_contract
ORDER BY dno, name;
+----------+-------+-------+-------+-------+--------+--------+--------+--------+--------+
|   name   |  dno  |  sal  | win8  | win9  | win10  | win11  | win12  | win13  | win14  |
+----------+-------+-------+-------+-------+--------+--------+--------+--------+--------+
| Lucy     | 1000  | 5500  | 5500  | 5500  | 6400   | NULL   | 5500   | 5500   | 6400   |
| Michael  | 1000  | 5000  | 5000  | 6400  | 6400   | 5500   | 5500   | 6400   | 6400   |
| Steven   | 1000  | 6400  | 6400  | 6400  | 6400   | 5500   | 6400   | 6400   | 6400   |
| Will     | 1000  | 4000  | 4000  | 4000  | 4000   | 6400   | 6400   | 6400   | 6400   |
| Will     | 1000  | 4000  | 4000  | 4000  | 4000   | 6400   | 6400   | 6400   | 6400   |
| Jess     | 1001  | 6000  | 6000  | 6000  | 6400   | NULL   | 6000   | 6000   | 6400   |
| Lily     | 1001  | 5000  | 5000  | 6400  | 6400   | 6000   | 6000   | 6400   | 6400   |
| Mike     | 1001  | 6400  | 6400  | 6400  | 6400   | 6000   | 6400   | 6400   | 6400   |
| Richard  | 1002  | 8000  | 8000  | 8000  | 8000   | NULL   | 8000   | 8000   | 8000   |
| Wei      | 1002  | 7000  | 7000  | 7000  | 7000   | 8000   | 8000   | 8000   | 8000   |
| Yun      | 1002  | 5500  | 5500  | 5500  | 5500   | 8000   | 8000   | 8000   | 8000   |
+----------+-------+-------+-------+-------+--------+--------+--------+--------+--------+

此外，窗口可以在单独的 window 子句中定义，也可以由其他 windows 引用
示例：window reference

> SELECT name, dept_num, salary,
MAX(salary) OVER w1 AS win1,
MAX(salary) OVER w2 AS win2,
MAX(salary) OVER w3 AS win3
FROM employee_contract
WINDOW
w1 as (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
w2 as w3,
w3 as (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
+----------+-----------+---------+-------+-------+-------+
|   name   | dept_num  | salary  | win1  | win2  | win3  |
+----------+-----------+---------+-------+-------+-------+
| Lucy     | 1000      | 5500    | 5500  | 6400  | 6400  |
| Michael  | 1000      | 5000    | 5500  | 6400  | 6400  |
| Steven   | 1000      | 6400    | 6400  | 6400  | 6400  |
| Will     | 1000      | 4000    | 6400  | 6400  | 6400  |
| Will     | 1000      | 4000    | 6400  | 4000  | 4000  |
| Jess     | 1001      | 6000    | 6000  | 6400  | 6400  |
| Lily     | 1001      | 5000    | 6000  | 6400  | 6400  |
| Mike     | 1001      | 6400    | 6400  | 6400  | 6400  |
| Richard  | 1002      | 8000    | 8000  | 8000  | 8000  |
| Wei      | 1002      | 7000    | 8000  | 8000  | 8000  |
| Yun      | 1002      | 5500    | 8000  | 7000  | 7000  |
+----------+-----------+---------+-------+-------+-------+

range type

相比以行为单位的row type窗口，range type窗口是在窗口表达式中指定值的范围值。如下示例， max(salary) RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING 语句会计算分区内的相对于当前行值从-500到+1000中的max(salary)，假如当前行的salary值为4000，那么max(salary)的计算范围就是每个由dept_num指定的分区内的salary值在3500和5000之间的数据。

> SELECT 
dept_num, start_date, name, salary, 
max(salary) OVER (PARTITION BY dept_num ORDER BY salary
RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING) win1,
max(salary) OVER (PARTITION BY dept_num ORDER BY salary
RANGE BETWEEN 500 PRECEDING AND CURRENT ROW) win2
FROM employee_contract
order by dept_num, start_date;
+-----------+-------------+----------+---------+-------+-------+
| dept_num  | start_date  |   name   | salary  | win1  | win2  |
+-----------+-------------+----------+---------+-------+-------+
| 1000      | 2010-01-03  | Lucy     | 5500    | 6400  | 5500  |
| 1000      | 2012-11-03  | Steven   | 6400    | 6400  | 6400  |
| 1000      | 2013-10-02  | Will     | 4000    | 5000  | 4000  |
| 1000      | 2014-01-29  | Michael  | 5000    | 5500  | 5000  |
| 1000      | 2014-10-02  | Will     | 4000    | 5000  | 4000  |
| 1001      | 2013-11-03  | Mike     | 6400    | 6400  | 6400  |
| 1001      | 2014-11-29  | Lily     | 5000    | 6000  | 5000  |
| 1001      | 2014-12-02  | Jess     | 6000    | 6400  | 6000  |
| 1002      | 2010-04-03  | Wei      | 7000    | 8000  | 7000  |
| 1002      | 2013-09-01  | Richard  | 8000    | 8000  | 8000  |
| 1002      | 2014-01-29  | Yun      | 5500    | 5500  | 5500  |
+-----------+-------------+----------+---------+-------+-------+

注意：如果省略了整个窗口表达式语句，那么默认的窗口语句就是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。如果ORDER BY 和 WINDOW 语句同时都没有，则默认的窗口语句是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

参考

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
书籍 Apache Hive Essentials Second Edition (by Dayong Du) Chapter 6

看得出的就是
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
HiveQL DQL7—高级聚合
MAYIBO—BG的专栏
02-04 233
目录Grouping sets示例：使用一个字段对元素的GROUPING SETS示例：使用2个元素的GROUPING SETS示例：使用2个元素的GROUPING SETS，一个字段对，一个是单独字段示例：使用4个元素的GROUPING SETSRollup 和 Cube聚合条件过滤 HAVING参考 通过使用 GROUPING SETS, CUBE, 和 ROLLUP，Hive提供了高级聚合功...
HQL中over()函数的一些用法
zhyaoeng
10-19 1510
hive over()聚合函数
Hive分析函数之SUM，AVG，MIN和MAX OVER(PARTITION BY xxx order by xxx，用于求一段时间内截至到每天的累计访问次数、平均访问次数、最小访问次数、最大访问次
helloxiaozhe的博客
12-17 1万+
Hive分析函数之SUM，AVG，MIN和MAX OVER(PARTITION BY xxx order by xxx，用于求一段时间内截至到每天的累计访问次数、平均访问次数、最小访问次数、最大访问次数 Hive提供了很多分析函数，用于统计分析，比如SUM OVER PARTITION BY ORDER BY 是 实现分组累计的计算方法。 本文先介绍SUM、AVG、MIN、MA...
Hive的函数
qq_56740631的博客
10-21 863
（1）在idea中创建Maven项目，并在pom.xml中导入依赖（2）自定义一个java类继承UDF，重载 evaluate 方法。}}（3）将Java类打成jar包上传到服务器（4）添加jar包到hive中（5）创建临时函数与开发好的 class 关联起来（6）在hive中使用写好自定义函数（注：这种方式创建的临时函数只在一次hive会话中有效，重启会话后就无效，若想永久生效用以下方法）（7）删除函数。
hive over窗口函数使用
bearsfreedom的博客
12-01 1429
前提： 按照OVER() 进行partion，然后每个分组执行函数计算。 最后为每一个分组增加对应的字段数据。最后原始数据行数 没有改变，可以在此基础上继续使用。 一、计算累计和 统计1-12月的累积销量，即1月为1月份的值，2月为1.2月份值的和，3月为123月份的和，12月为1-12月份值的和 代码块 Plain Text SELECT month,SUM(amount) month_amount, SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN
MySQL DQL - 聚合函数.md
最新发布
07-31
此Markdown文档提供了MySQL数据库中聚合函数的DQL操作示例代码和说明。通过文档，您可以学习如何使用不同的聚合函数对表中的数据进行统计和计算。 示例代码演示了使用`SUM`、`AVG`、`MAX`和`MIN`等聚合函数的方法。...
教义函数：:books:MySQL的DQL函数集
02-04
教义功能 该软件包包含教义函数，您可以分叉它，并使用自己的函数提出请求请求。 可用功能列表： DATE(expr) DATEDIFF(expr1, expr2) DAYOFWEEK(expr) WEEK(expr) DAY(expr) DAYOFMONTH(expr) DAYOFYEAR(expr) HOUR...
SQL（DQL）
01-27
1.字段(列)控制 1)查询所有列 SELECT*FROM表名; SELECT*FROMemp;-->其中“*”表示查询所有列2)查询指定列 SELECT列1[,列2,...列N]FROM表名;SELECTempno,ename,sal,commFROM
DQL中的函数（1.字符串函数等）.md
08-30
Mysql中的DQL中的函数
Hive窗口函数
m0_57468722的博客
10-25 230
在SQL中有一类函数叫做聚合函数，例如sum()、avg()、max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据，又要显示聚集后的数据，这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能 Hive 窗口函数 窗口函数最重要的关键字是partition by和order by 具体语法如下：XXX over (partition by xxx order by xxx) 特...
Hive 开窗函数 汇总
薛秋艳的博客
08-18 4695
近期用开窗函数比较多老是用不好 ，这里做一下汇总
Hive中窗口函数 over()
weixin_44175698的博客
12-03 1029
over() 粗浅理解：一行对应一个窗口，至于这个窗口的范围是什么就要看over()函数里面对窗口范围的约束是什么了 over()函数中包括三个函数：包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。 我们在使用over()窗口函数时，over()函数中的这三个函数可组合使用也可以不使用。 over() 默认的效果和order by类似，即第一行窗口大小为1，第二行窗口大小为2，以此类推，但是数据只不过没有什统计意义，所以一
hive中取最大值最小值的函数
qq_42456324的博客
09-18 1万+
max()和min()函数 select a,max(b) from t group by a select a,min(b) from t group by a max和min函数是取某一列中的最大或者最小值 greatest()和least()函数 select greatest(-1, 0, 5, 8) --8 select least(-1, 0, 5, 8) -- -1 greatest和least函数是取某一行中的最大或者最小值，但一定是比较相同类型的数据，如果数据类..
Hive窗口函数详解——及3套案例
肥宅小谢的博客
06-18 992
前言：要先了解窗口函数的结构，over（）才是窗口函数，而avg()、sum()、max()、min()等是与over（）分析函数 一，hive窗口函数语法 首先，avg()、sum()、max()、min()等是分析函数，而over()才是窗口函数 ...
Hive 开窗函数 —— over(partition by) 介绍
weixin_45417821的博客
08-01 8700
开窗函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 –排序，即便值一样，也不会出现重复排序， select row_number() over(order by name) as 排序, * from t2_temp –排序，值一样，就重复排序,例如1,1,3,4 select rank() over(order by name)
by max over partition_大数据技术之Hive Sum MAX Over Demo
weixin_39616367的博客
01-13 143
本篇教程探讨了大数据技术之Hive Sum MAX Over Demo，希望阅读本篇文章以后大家有所收获，帮助大家对相关内容的理解更加深入。A,2015-01,5A,2015-01,15B,2015-01,5A,2015-01,8B,2015-01,25A,2015-01,5A,2015-02,4A,2015-02,6B,2015-02,10B,2015-02,5A,2015-03,16A,201...
hive over窗口函数的使用
Mrerlou的博客
08-31 1626
前言：我们在学习hive窗口函数的时候，一定要先了解窗口函数的结构。而不是直接百度sum() over()、row_number() over()、或者count() over()的用法，如果这样做，永远也掌握不到窗口函数的核心，当然我刚开始的时候也是这样做的。 还好我比较顽强，在HIVE窗口函数问题上折腾了半个月、看了很多文章后才知道over()才是窗口函数，而sum、row_numb...
HIVE中的OVER函数解析
zhao_guo_feng的博客
07-10 885
over函数解析
HIVE over() 超全讲解
热门推荐
csdn问鼎
06-16 2万+
over() over() 函数在Hive中运用广泛，通常搭配row_number() ,min(),max(),sum()来使用,总结下over函数划定窗口的范围 id name num 1 a 1 2 b 3 3 a 5 4 c 9 5 b 3 over() 此时每一行的窗口都是所有的行 SELECT id,name,num, sum(num) ov...
简述 ：MySQL日常使用的DML/DDL/DQL，窗口函数等常用SQL命令，MySQL的触发器，事务，锁机制，Innodb存储引擎及简单的MySQL优化
05-25
以下是MySQL日常使用的DML/DDL/DQL和窗口函数等常用SQL命令： 1. DML（数据操作语言）：包括INSERT、UPDATE和DELETE。这些命令用于向数据库中添加、更新和删除数据。 2. DDL（数据定义语言）：包括CREATE、ALTER和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值