HiveSQL题——排序函数(row_number/rank/dense_rank)

2024-01-30 13:52

本文主要是介绍HiveSQL题——排序函数(row_number/rank/dense_rank),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、窗口函数的知识点

1.1 窗户函数的定义

        窗口函数可以拆分为【窗口+函数】。窗口函数官网指路:

LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundationicon-default.png?t=N7T8https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics

  • 窗口:限定函数的计算范围(窗口函数:针对分组后的数据,从逻辑角度指定计算的范围,并没有从物理上真正的切分,只有group by 是物理分组,真正意义上的分组)
  • 函数:计算逻辑
  •  窗口函数的位置:跟sql里面聚合函数的位置一样,from -> join -> on -> where -> group by->select 后面的普通字段,窗口函数 -> having -> order by  -> lmit 。 窗口函数不能跟聚合函数同时出现。聚合函数包括count、sum、 min、max、avg。
  • sql 执行顺序:from -> join -> on -> where -> group by->select 后面的普通字段,聚合函数-> having -> order by -> limit

1.2 窗户函数的语法

       <窗口函数>window_name  over ( [partition by 字段...]  [order by 字段...]  [窗口子句] )

  • window_name:给窗口指定一个别名。
  • over:用来指定函数执行的窗口范围,如果后面括号中什么都不写,即over() ,意味着窗口包含满足where 条件的所有行,窗口函数基于所有行进行计算。
  • 符号[] 代表:可选项;  | : 代表二选一
  •  partition by 子句: 窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行。分组间互相独立。
  • order by 子句:每个partition内部按照哪些字段进行排序,如果没有partition ,那就直接按照最大的窗口排序,且默认是按照升序(asc)排列。
  • 窗口子句:显示声明范围(不写窗口子句的话,会有默认值)。常用的窗口子句如下:
    rows between unbounded preceding and  unbounded following; -- 上无边界到下无边界(一般用于求 总和)rows between unbounded preceding and current row;  --上无边界到当前记录(累计值)rows between 1 preceding and current row; --从上一行到当前行rows between 1 preceding and 1 following; --从上一行到下一行rows between current row and 1 following; --从当前行到下一行

    ps: over()里面有order by子句,但没有窗口子句时 ,即: <窗口函数> over ( partition by 字段... order by 字段... ),此时窗口子句是有默认值---->   rows between unbounded preceding and current row (上无边界到当前行)。

      此时窗口函数语法:<窗口函数> over ( partition by 字段... order by 字段... ) 等价于

     <窗口函数> over ( partition by 字段... order by 字段... rows between unbounded preceding and current row)
      需要注意有个特殊情况:当order by 后面跟的某个字段是有重复行的时候, <窗口函数> over ( partition by 字段... order by 字段... )  不写窗口子句的情况下,窗口子句的默认值是:range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。

    因此,遇到order by 后面跟的某个字段出现重复行,且需要计算【上无边界到当前行】,那就需要手动指定窗口子句 rows between unbounded preceding and current row ,偷懒省略窗口子句会出问题~

      ps: 窗口函数的执行顺序是在where之后,所以如果where子句需要用窗口函数作为条件,需要多一层查询,在子查询外面进行。

     【例如】求出登录记录出现间断的用户Id

selectid
from (selectid,login_date,lead(login_date, 1, '9999-12-31')over (partition by id order by login_date) next_login_date--窗口函数 lead(向后取n行)--lead(column1,n,default)over(partition by column2 order by column3) 查询当前行的后边第n行数据,如果没有就为nullfrom (--用户在同一天可能登录多次,需要去重selectid,date_format(`date`, 'yyyy-MM-dd') as login_datefrom user_loggroup by id, date_format(`date`, 'yyyy-MM-dd')) tmp1) tmp2
where  datediff(next_login_date, login_date) >=2
group by id;

1.3 窗口函数分类

      哪些函数可以是窗口函数呢?(放在over关键字前面的)

  • 聚合函数
sum(column) over (partition by .. order by .. 窗口子句);
count(column) over (partition by .. order by .. 窗口子句);
max(column) over  (partition by .. order by .. 窗口子句);
min(column) over (partition by .. order by .. 窗口子句);
avg(column) over (partition by .. order by .. 窗口子句);
   ps : 高级聚合函数:

          collect_list 收集并形成list集合,结果不去重;

          collect_set 收集并形成set集合,结果去重; 

      举例:

--每个月的入职人数以及姓名select 
month(replace(hiredate,'/','-')),count(*) as cnt,collect_list(name) as name_list
from employee
group by month(replace(hiredate,'/','-'));/*
输出结果
month  cn  name_list
4	    2	["宋青书","周芷若"]
6	    1	["黄蓉"]
7	    1	["郭靖"]
8	    2	["张无忌","杨过"]
9	    2	["赵敏","小龙女"]
*/
  • 排序函数

      row_number() 、rank()、dense_rank() 函数不支持自定义窗口子句。

--  顺序排序——1、2、3
row_number() over(partition by .. order by .. )--  并列排序,跳过重复序号——1、1、3(横向加)
rank() over(partition by .. order by .. )-- 并列排序,不跳过重复序号——1、1、2(纵向加)
dense_rank()  over(partition by .. order by .. )
  • 前后函数 

       laglead函数不支持自定义窗口子句。

-- 取得column列的前n行,如果存在则返回,如果不存在,返回默认值default
lag(column,n,default) over(partition by.. order by...) as lag_test
-- 取得column列的后n行,如果存在则返回,如果不存在,返回默认值default
lead(column,n,default) over(partition by.. order by...) as lead_test
  • 头尾函数
first_value(column,true)  ---当前窗口column列的第一个数值,如果有null值,则跳过
first_value(column,false) ---当前窗口column列的第一个数值,如果有null值,不跳过
last_value(column,true)  --- 当前窗口column列的最后一个数值,如果有null值,则跳过
last_value(column,false) --- 当前窗口column列的最后一个数值,如果有null值,不跳过

1.4 排序函数

        rank/dense_rank/row_number 函数,一般用于求分组topN。

--  顺序排序——1、2、3
row_number() over(partition by .. order by .. )--  并列排序,跳过重复序号——1、1、3(横向加)
rank() over(partition by .. order by .. )-- 并列排序,不跳过重复序号——1、1、2(纵向加)
dense_rank()  over(partition by .. order by .. )

二、实际案例

2.1 每个学生成绩第二高的科目

0 问题描述

    根据学生成绩表,求出每个学生成绩第二高的科目。

1 数据准备

create table if not exists table5
(class     string comment '学科',student   string comment '学生姓名',score     int comment '成绩'
)comment '学生成绩表';insert overwrite table table5 values
('a','吱吱1',100),
('a','吱吱2',60),
('b','吱吱1',80),
('b','吱吱2',70),
('c','吱吱2',50),
('c','吱吱3',90);

2 数据分析

3种排序函数的区别:row_number (行号)-- 1 2 3 ;rank (重复跳过)--1 1 3;dense_rank (重复不跳过) --1 1 2
selectclass,student
from (selectclass,student,score,dense_rank()  over (partition by student order by score desc) rnfrom table5) tmp1
where rn = 2;

3 小结

    排序函数在分组tpoN场景应用十分广泛,需要注意的是在sql语句中,窗口函数的执行顺序是在where过滤条件之后,所以如果where子句需要用窗口函数作为条件,需要多一层查询,在子查询外面进行。

这篇关于HiveSQL题——排序函数(row_number/rank/dense_rank)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/660458

相关文章

hdu1171(母函数或多重背包)

题意:把物品分成两份,使得价值最接近 可以用背包,或者是母函数来解,母函数(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v) 其中指数为价值,每一项的数目为(该物品数+1)个 代码如下: #include<iostream>#include<algorithm>

【数据结构】——原来排序算法搞懂这些就行,轻松拿捏

前言:快速排序的实现最重要的是找基准值,下面让我们来了解如何实现找基准值 基准值的注释:在快排的过程中,每一次我们要取一个元素作为枢纽值,以这个数字来将序列划分为两部分。 在此我们采用三数取中法,也就是取左端、中间、右端三个数,然后进行排序,将中间数作为枢纽值。 快速排序实现主框架: //快速排序 void QuickSort(int* arr, int left, int rig

usaco 1.3 Mixing Milk (结构体排序 qsort) and hdu 2020(sort)

到了这题学会了结构体排序 于是回去修改了 1.2 milking cows 的算法~ 结构体排序核心: 1.结构体定义 struct Milk{int price;int milks;}milk[5000]; 2.自定义的比较函数,若返回值为正,qsort 函数判定a>b ;为负,a<b;为0,a==b; int milkcmp(const void *va,c

usaco 1.2 Name That Number(数字字母转化)

巧妙的利用code[b[0]-'A'] 将字符ABC...Z转换为数字 需要注意的是重新开一个数组 c [ ] 存储字符串 应人为的在末尾附上 ‘ \ 0 ’ 详见代码: /*ID: who jayLANG: C++TASK: namenum*/#include<stdio.h>#include<string.h>int main(){FILE *fin = fopen (

hdu 1285(拓扑排序)

题意: 给各个队间的胜负关系,让排名次,名词相同按从小到大排。 解析: 拓扑排序是应用于有向无回路图(Direct Acyclic Graph,简称DAG)上的一种排序方式,对一个有向无回路图进行拓扑排序后,所有的顶点形成一个序列,对所有边(u,v),满足u 在v 的前面。该序列说明了顶点表示的事件或状态发生的整体顺序。比较经典的是在工程活动上,某些工程完成后,另一些工程才能继续,此时

C++操作符重载实例(独立函数)

C++操作符重载实例,我们把坐标值CVector的加法进行重载,计算c3=c1+c2时,也就是计算x3=x1+x2,y3=y1+y2,今天我们以独立函数的方式重载操作符+(加号),以下是C++代码: c1802.cpp源代码: D:\YcjWork\CppTour>vim c1802.cpp #include <iostream>using namespace std;/*** 以独立函数

题目1380:lucky number

题目1380:lucky number 时间限制:3 秒 内存限制:3 兆 特殊判题:否 提交:2839 解决:300 题目描述: 每个人有自己的lucky number,小A也一样。不过他的lucky number定义不一样。他认为一个序列中某些数出现的次数为n的话,都是他的lucky number。但是,现在这个序列很大,他无法快速找到所有lucky number。既然

《数据结构(C语言版)第二版》第八章-排序(8.3-交换排序、8.4-选择排序)

8.3 交换排序 8.3.1 冒泡排序 【算法特点】 (1) 稳定排序。 (2) 可用于链式存储结构。 (3) 移动记录次数较多,算法平均时间性能比直接插入排序差。当初始记录无序,n较大时, 此算法不宜采用。 #include <stdio.h>#include <stdlib.h>#define MAXSIZE 26typedef int KeyType;typedef char In

函数式编程思想

我们经常会用到各种各样的编程思想,例如面向过程、面向对象。不过笔者在该博客简单介绍一下函数式编程思想. 如果对函数式编程思想进行概括,就是f(x) = na(x) , y=uf(x)…至于其他的编程思想,可能是y=a(x)+b(x)+c(x)…,也有可能是y=f(x)=f(x)/a + f(x)/b+f(x)/c… 面向过程的指令式编程 面向过程,简单理解就是y=a(x)+b(x)+c(x)

【软考】希尔排序算法分析

目录 1. c代码2. 运行截图3. 运行解析 1. c代码 #include <stdio.h>#include <stdlib.h> void shellSort(int data[], int n){// 划分的数组,例如8个数则为[4, 2, 1]int *delta;int k;// i控制delta的轮次int i;// 临时变量,换值int temp;in