程序员经验分享-hwhale

Hive实现分组排序、分组求取topN或者分页的实现方法

2023-11-07

使用到的语法：ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的每条xlh记录返回一个序号。
表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)实例.

语法：row_number() over (partition by 字段a order by 计算项b desc ) rank 

--这里rank是别名

partition by：类似hive的建表，分区的意思；

order by ：排序，默认是升序，加desc降序；

这里按字段a分区，对计算项b进行降序排序

实例一、

要取top10品牌，各品牌的top10渠道，各品牌的top10渠道中各渠道的top10档期 

1、取top10品牌

select 品牌,sum(income) as num  from table_name group by 品牌 order by num desc limit 10;

 2、 取top10品牌下各品牌的top10渠道        

select 

          a.*

from

          (

                  select 品牌,渠道,sum(income) as num, row_number() over (partition by 品牌 order by num desc ) rank  

                   from table_name

                   where 品牌限制条件

                   group by 品牌,渠道

          )a

where 

          a.rank<=10

 3、 取top10品牌下各品牌的top10渠道中各渠道的top10档期

select 

          a.*

from

          (

                  select 品牌,渠道,档期,sum(income) as num, row_number() over (partition by 品牌,渠道 order by num desc ) rank  

                   from table_name

                   where 品牌,渠道 限制条件

                   group by 品牌,渠道,档期

          )a

where 

          a.rank<=10

实例二：

初始化数据:

create table employee (empid int ,deptid int ,salary decimal(10,2));
insert into employee values(1,10,5500.00);
insert into employee values(2,10,4500.00);
insert into employee values(3,20,1900.00);
insert into employee values(4,20,4800.00);
insert into employee values(5,40,6500.00);
insert into employee values(6,40,14500.00);
insert into employee values(7,40,44500.00);
insert into employee values(8,50,6500.00);
insert into employee values(9,50,7500.00);

显示数据：

hive> select * from employee;
1    10    5500.00
2    10    4500.00
3    20    1900.00
4    20    4800.00
5    40    6500.00
6    40    14500.00
7    40    44500.00
8    50    6500.00
9    50    7500.00
Time taken: 4.078 seconds, Fetched: 9 row(s)
hive> desc employee;
empid                   int
deptid                  int
salary                  decimal(10,2)
Time taken: 0.042 seconds, Fetched: 3 row(s)

（1）需求：根据部门分组，显示每个部门的工资等级

实现的HSQL脚本：

hive> SELECT *, row_number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee;

OK
1    10    5500.00    1
2    10    4500.00    2
4    20    4800.00    1
3    20    1900.00    2
7    40    44500.00    1
6    40    14500.00    2
5    40    6500.00    3
9    50    7500.00    1
8    50    6500.00    2
Time taken: 25.019 seconds, Fetched: 9 row(s)

（2）根据部门分组，求出每个部门的工资等级排名top 2的数据

SELECT
    *
FROM
    (
        SELECT
            *, row_number() OVER(PARTITION BY deptid
                ORDER BY salary DESC
            ) rank
        FROM
            employee
    ) a
WHERE
    a.rank <= 2;

（3）根据部门分组，求出每个部门的工资等级排第二名的那条数据

SELECT
    *
FROM
    (
        SELECT
            *, row_number() OVER(PARTITION BY deptid
                ORDER BY salary DESC
            ) rank
        FROM
            employee
    ) a
WHERE
    a.rank = 2;

按照empid降序排列，并按照排列顺序给出编号：

SELECT
    row_number() over(ORDER BY empid DESC) AS rnum ,
    employee.*
FROM
    employee

（4）hive中一般取top n时，row_number(),rank,dense_ran()这三个函数就派上用场了，

先简单说下这三函数都是排名的，不过呢还有点细微的区别。

通过代码运行结果一看就明白了。

除Row_number外还有rank,dense_rank 
以下是语法： 
rank() over([partition by col1] order by col2) 
dense_rank() over([partition by col1] order by col2) 
row_number() over([partition by col1] order by col2)

功能差不多，但是有细微的差别 

row_number的排序不允许并列，即使两条记录的值相等也不会出现相等的排序值 
rank排序时出现相等的值时会有并列，即值相等的两条数据会有相同的序列值 
dense_rank排序的值允许并列，但会跳跃的排序，像这样：1,1,3,4,5,5,7.


select empid,
deptid,
salary,
rank()over( order by deptid desc ) rank,
dense_rank() over( order by deptid desc ) dense_rank,
row_number()over( order by deptid desc) row_number
from employee

从结果看出

rank() 排序相同时会重复，总数不会变  

dense_rank()排序相同时会重复，总数会减少

row_number() 会根据顺序计算

正好听到一个需求，求sal前50%的人

（5）基于row_number函数也很容易实现分页：

SELECT
    *
FROM
    (
        SELECT
            row_number() over(ORDER BY empid DESC) AS rnum ,
            employee.*
        FROM
            employee
    ) t
WHERE
    rnum >= 1
AND rnum <= 5;

参考：https://www.2cto.com/net/201803/733548.html

           http://blog.sina.com.cn/s/blog_6676d74d0102vm2c.html

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

hive

rownumber

分组取topN

RANK

denserank

Hive实现分组排序、分组求取topN或者分页的实现方法 的相关文章

随机推荐

  • 1041 考试座位号

    每个 PAT 考生在参加考试时都会被分配两个座位号 一个是试机座位 一个是考试座位 正常情况下 考生在入场时先得到试机座位号码 入座进入试机状态后 系统会显示该考生的考试座位号码 考试时考生需要换到考试座位就座 但有些考生迟到了 试机已经结

  • 【华为OD统一考试B卷

    在线OJ 已购买本专栏用户 请私信博主开通账号 在线刷题 运行出现 Runtime Error 0Aborted 请忽略 华为OD统一考试A卷 B卷 新题库说明 2023年5月份 华为官方已经将的 2022 0223Q 1 2 3 4 统一

  • 两数之和（C++）

    两数之和 C 两数之和 问题描述 问题求解 问题总结 两数之和 问题描述 给定一个整数数组 nums 和一个整数目标值 target 请你在该数组中找出 和为目标值 target 的那 两个 整数 并返回它们的数组下标 你可以假设每种输入只

  • T-Kernel 里 sleep API

    T Kernel 里有类似于 msleep 的函数 tk slp tsk TMO tmout TMO Timeout time Time base 1 millisecond 32 bit or higher signed integer

  • LeetCode62：不同路径（深搜 && 动态规划）

    题目 一个机器人位于一个 m x n 网格的左上角 起始点在下图中标记为 Start 机器人每次只能向下或者向右移动一步 机器人试图达到网格的右下角 在下图中标记为 Finish 问总共有多少条不同的路径 来源 力扣 LeetCode 链接

  • python itertools功能详解

    python itertools功能详解 介绍 itertools是python内置的模块 使用简单且功能强大 这里尝试汇总整理下 并提供简单应用示例 如果还不能满足你的要求 欢迎加入补充 使用只需简单一句导入 import itertoo

  • LeetCode 剑指 Offer 13. 机器人的运动范围

    目录结构 1 题目 2 题解 1 题目 地上有一个m行n列的方格 从坐标 0 0 到坐标 m 1 n 1 一个机器人从坐标 0 0 的格子开始移动 它每次可以向左 右 上 下移动一格 不能移动到方格外 也不能进入行坐标和列坐标的数位之和大于

  • 行走在路，偶有弯道——致这半年

    不知不觉半年又转瞬即逝 不得不说总结是一个回顾过去开创未来的时刻 一直在拖着半年总结 因为害怕面对自己不好的一面 害怕想象半年前的目前 对比半年后的成果 但是总结终究是要有的 因为有总结 我们才会更加系统的认识自己 反思自己 然后在今后的生

  • linux的文件安装路径

    如果是别人发布的二进制包 可执行文件通常都安装到 usr bin 下面 如果是自己从源代码安装的 可执行文件通常都在 usr local bin 下面 除非配置时指定了安装位置 例如 rpm ivh xxx1 rpm 可执行文件通常都安装到

  • UIViewAnimation动画与Core Animation的CATransition类动画

    void leftClick UIView beginAnimations nil context nil display mode slow at beginning and end UIView setAnimationCurve UI

  • Dubbo+spring+springmvc+mybatis+Maven+Jetty+Mysql+Ajax多框架融合学习网址收藏

    原文地址 http blog csdn net wp1603710463 article details 49232797 Dubbo spring springmvc mybatis Maven Jetty Mysql Ajax多框架融合

  • keras的Sequential神经网络,keras实现卷积神经网络

    1 如何用keras实现多变量输入神经网络 要点如下 1 神经网络基本结构 应该是输入层 若干个隐含层 输出层 2 输入层应该有7个输入变量 3 隐含层层数自定 但每层要超过7个神经元 4 输出层可以用softmax之类的函数 将隐含层的输

  • C/C++中时间函数的介绍

    1 概念 在C C 中 对字符串的操作有很多值得注意的问题 同样 C C 对时间的操作也有许多值得大家注意的地方 最近 在技术群中有很多网友也多次问到过C 语言中对时间的操作 获取和显示等等的问题 下面 在这篇文章中 笔者将主要介绍在C C

  • M1芯片运行ReactNative踩坑

    运行 报 Flipper 什么找不到的错 具体什么错记不清了 解决办法 编辑 ios Podfile 删除以下内容 use flipper post install do installer flipper post install ins

  • 技术栈选型之微服务公共关注点及Dubbo、Spring Cloud和K8s横向比对

    技术栈选型之微服务公共关注点及Dubbo Spring Cloud和K8s横向比对 文章目录 技术栈选型之微服务公共关注点及Dubbo Spring Cloud和K8s横向比对 前言 微服务公共关注点 Dubbo Spring Cloud和

  • Mac电脑不能读取您的磁盘怎么办

    品牌型号 MacBook Air 系统 macOS 10 13 软件版本 Tuxera Ntfs for mac 2021 Mac电脑不能读取磁盘数据 无非是两种情况 一是固件故障 二是磁盘格式问题 在常用的三种磁盘格式NTFS FAT 3

  • Quartz 的使用

    项目代码 https github com yuleiqq quartz example tree master quartz study 使用Quartz 的调度器之前 需要先实例化该调度器 Scheduler 实例化完成之后 就可以启动

  • python学习：异常处理、with用法以及utf8中文显示

    usr bin env python coding utf8 为文件中每行的开头添加行号 def addLineNumberForFile srcFilePath dstFilePath if srcFilePath dstFilePath

  • SpringWeb(SpringMVC)

    目录 SpringWeb介绍 搭建 SpringWeb SpringWeb介绍 Spring Web是一个基于 Servlet API 构建的原始 web 框架 用于构建基于MVC模式的Web应用程序 在 web 层框架历经 Strust1

  • Hive实现分组排序、分组求取topN或者分页的实现方法

    使用到的语法 ROW NUMBER OVER PARTITION BY COL1 ORDER BY COL2 简单的说row number 从1开始 为每一条分组记录返回一个数字 这里的ROW NUMBER OVER ORDER BY xl

热门标签