hive top n (order by与sort by区别)

最新推荐文章于 2024-03-05 19:27:33 发布
最新推荐文章于 2024-03-05 19:27:33 发布
阅读量1.7k 收藏
点赞数
分类专栏： hadoop 文章标签： hadoop
本文介绍在Hive中如何使用sort by替代order by来提高SELECT TOP N查询的效率。通过实例展示，当面对大数据量时，sort by能够启动多个reduce任务进行局部排序，再进行全局排序，有效提升查询速度。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

我想说的SELECT TOP N是取最大前N条或者最小前N条。
Hive提供了limit关键字，再配合order by可以很容易地实现SELECT TOP N。
但是在Hive中order by只能使用1个reduce，如果表的数据量很大，那么order by就会力不从心。
例如我们执行SQL：select a from ljntest01 order by a limit 10;
控制台会打印出：Number of reduce tasks determined at compile time: 1
说明启动的reduce数量是编译时确定的。
查看该SQL的执行计划，该SQL只启动1个JOB。 

假设数据表有1亿条数据，而我们只想取TOP 10，那对1亿条数据在1个reduce中做全排序是非常不合理的。
幸好有sort by，使用sort by替换order by就可以解决这个问题：
select a from ljntest01 sort by a limit 10;
首先执行该SQL控制台打印出：Number of reduce tasks not specified. Estimated from input data size: 1
说明reduce数不是编译时确定的，而是根据输入文件大小动态确定的。
此外查看该SQL的执行计划：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-2 depends on stages: Stage-1
  Stage-0 is a root stage 

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        ljntest01
          TableScan
            alias: ljntest01
            Select Operator
              expressions:
                    expr: a
                    type: int
              outputColumnNames: _col0
              Reduce Output Operator
                key expressions:
                      expr: _col0
                      type: int
                sort order: +
                tag: -1
                value expressions:
                      expr: _col0
                      type: int
      Reduce Operator Tree:
        Extract
          Limit
            File Output Operator
              compressed: true
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
 

  Stage: Stage-2
    Map Reduce
      Alias -> Map Operator Tree:
        hdfs://hdpnn:9000/group/alidw-cbu/tmp/hive-admin/hive_2012-12-16_01-19-42_893_2878471909568139281/-mr-10002
            Reduce Output Operator
              key expressions:
                    expr: _col0
                    type: int
              sort order: +
              tag: -1
              value expressions:
                    expr: _col0
                    type: int
      Reduce Operator Tree:
        Extract
          Limit
            File Output Operator
              compressed: true
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
 

  Stage: Stage-0
    Fetch Operator
      limit: 10 

sort by可以启动多个reduce，每个reduce做局部排序，但是这对于sort by limit N已经够用了。从执行计划中可以看出sort by limit N启动了两个JOB。第一个JOB是在每个reduce中做局部排序，然后分别取TOP N。假设启动了M个reduce，第二个JOB再对M个reduce分别局部排好序的总计M * N条数据做全局排序，取TOP N，从而得到想要的结果。这样就可以大大提高SELECT TOP N的效率。

深度解析 Hive 排序命令：ORDER BY、SORT BY、DISTRIBUTE BY、CLUSTER BY
2401_83088008的博客
03-23 502
四个命令的设计体现了 Hive 在排序精度和处理性能ORDER BY是牺牲性能的绝对排序SORT BY是兼顾性能的局部排序是数据分布的底层控制CLUSTER BY是特定场景的语法糖Map 阶段负责数据读取与初步处理，Reduce 阶段负责数据聚合与最终排序。合理组合使用这四个命令，能让您在 Hive 开发中既保证结果正确性，又获得良好的性能表现。
Hive中SELECT TOP N的方法(order by与sort by)
dux++ 的专栏
07-04 8048
原文地址：http://blog.sina.com.cn/s/blog_6ff05a2c0101eaxf.html 我想说的SELECT TOP N是取最大前N条或者最小前N条。 Hive提供了limit关键字，再配合order by可以很容易地实现SELECT TOP N。 但是在Hive中order by只能使用1个reduce，如果表的数据量很大，那么order by就会力不从
top N hive sql
weixin_38670967的博客
04-11 279
insert into table vin_geo_summary select vin,province,city from (select vin,province,cityname as city,sub.c,rank() over (partition by vin order by sub.c desc) as r from (select vin,province,cityname,...
Hive分组取Top N
weixin_34220963的博客
01-08 121
2019独角兽企业重金招聘Python工程师标准>>> ...
hive 分组取top n的写法
licai_1001的博客
07-16 574
注：技术交流可以加我VX：k-loop，昵称：默读者。 创建测试表 create table tmp_ma( class string comment'品类', brand string comment'品牌', sales int comment'销量' ) 插入测试数据 insert into tmp_ma(class,brand,sales) values('手机','小米...
Hive分组排序取topN的sql查询示例
qq_36382892的博客
03-05 965
要在Hive中实现分组排序并取每组的前N条记录，可以使用 ROW_NUMBER() 窗口函数结合 PARTITION BY 和 ORDER BY 子句。您可以根据需要调整 rn
Hive order by limit和sort by limit区别
sinat_30371347的博客
11-26 1995
一、导言 order by和sort by都是hive中的排序方式，区别在于order by只有一个reduce对全表数据进行排序，sort by会按照key值分区在不同的reduce内部排序。那么对排序结果取limit的话，Hive会怎么处理呢？ 二、执行计划比较 数据表myhive.score select * from score; score.s_id score.c_id score.s_score score.month 06 04 80 2
Hive求topN
RayfunC的博客
12-31 1141
select t2.* from( select pid,uid,cnt,row_number() over (partition by pid order by cnt desc ) as rank from ( select pid,uid,count(uid) as cnt from visit2 group by pid,uid order by pid,cnt desc ) as t1 ) as t2 where t2.r.
Hive学习（3）ORDER BY排序NULL值终极指南：默认行为、显式控制与实战避坑
热门推荐
海量干货+实战指南，与百万技术人并肩跃迁，代码与思维共成长！
12-17 2万+
本文深入解析Hive中 ORDER BY 对NULL值的处理机制，结合实战案例演示如何精准控制NULL值位置，并提供跨版本兼容方案。在Hive数据分析中，‌排序操作‌（ORDER BY）是核心高频场景，例如生成排行榜、按时间筛选日志、统计Top-N数据等。然而，当数据中存在 ‌NULL值‌ 时，排序结果往往与预期不符，导致以下问题：默认排序混乱‌、‌跨引擎差异‌、隐式风险‌
Hive练习项目统计各种TOP的实战(数据和代码)
05-24
数据包括运行代码和数据类型，启动hive导入表和数据就可以运行结果
hive 分组取TopN
07-10
hive不直接支持分组取TopN的操作，需要自定义udf函数打成jar包添加到hive运行环境中
Hive TOP N 实现方法
weixin_33713707的博客
08-11 1182
2019独角兽企业重金招聘Python工程师标准>>> ...
Hive.分组排序和TOP
辰采星
04-16 1万+
HQL中的order by子句执行的排序操作始终是全局的，我们有时候想要的却是分组排序，即按fieldA排序以后，然后针对fieldA的每个值所对应的fieldB和（或）fieldC排序，而不是像order by那样，针对所有fieldA的值对fieldB和（或）fieldC排序。
hive里面，随机排序后取TOP N，与用limit取N条，有差别吗？
julyclj55555的博客
12-07 270
【代码】hive里面，随机排序后取TOP N，与用limit取N条，有差别吗？
Hive练习项目统计各种TOP的实战（详细）
m0_67144365的博客
05-24 964
Hive练习项目统计各种TOP的实战（详细）
Hive分组排序取top(N)
Sky_WANGJ的博客
12-25 2907
案列：求每个班级每科成绩的第1名的学生。 样例数据如下： 这个是典型的分组，组内排序求top N的问题，可以利用rank函数来解决。语法如下： rank() over([partition by col1] order by col2)  ：排序时出现相等的值时会有相同的序列值，但会跳跃的排序，如：1，1，3，4，5，5，7 相同的功能的函数语法： dense_rank() over(...
Hive 求Top N
Java大数据
07-22 947
之前面试就碰到过hive求top n操作，当时回答简单的排序就OK，现在一回想简直可笑。 这里用到hive，分析性函数，ROW_NUMBER 总共有rank,row_number,dense_rank等，但是Row_number用的频率最高，这里这介绍Row_number 语法： row_number() OVER (partition by COL1 order by COL2 desc ) r...
oracle中partition by与group by的区别
最新发布
04-18
<think>好的，用户之前已经询问过Hive SQL中PARTITION BY和GROUP BY的区别，现在转而询问Oracle数据库中的区别。我需要结合用户提供的引用资料和之前的回答，确保内容准确且符合Oracle的特性。首先，确认用户提供的引用中的关键点： 1. 引用2提到GROUP BY用于数据分组，而PARTITION BY用于数据分区，两者在大数据处理中有不同应用。 2. 引用3指出Oracle中GROUP BY需要包含所有SELECT中的非聚合字段，而PARTITION BY允许分组后排序并保留所有字段。 3. 引用4强调PARTITION BY保留全部数据，仅对某些字段分组排序，而GROUP BY仅保留分组字段和聚合结果。 4. 引用1关于Oracle分区表的信息，可能与PARTITION BY在窗口函数中的应用不同，但用户问题集中在GROUP BY和窗口函数的PARTITION BY，需注意区分。 用户之前的问题已覆盖Hive中的区别，现在需要调整到Oracle环境。需注意Oracle的窗口函数语法和可能的特定行为。需要比较两者在数据保留、聚合方式、语法结构、性能影响和应用场景的差异。同时，用户可能希望了解具体示例和常见错误，如GROUP BY的字段限制。需要确保回答结构清晰，引用资料中的例子，如引用3中的row_number()用法，并添加相关问题和注意事项。最后验证是否有遗漏点，比如执行计划差异或Oracle特有的优化提示。</think>### Oracle 数据库中 `PARTITION BY`（窗口函数）与 `GROUP BY` 的对比分析 --- #### **核心区别总结** | 维度 | `GROUP BY` | `PARTITION BY`（窗口函数） | |--------------------|-------------------------------------|-----------------------------------| | **数据保留** | 仅保留分组字段和聚合结果 | 保留所有原始字段，仅对分组字段计算 | | **聚合方式** | 全组聚合（每组输出一行） | 分组内逐行计算（输出行数不变） | | **语法限制** | `SELECT`字段必须参与分组或聚合 | 可自由引用所有字段 | | **执行过程** | 触发数据重分布（Shuffle） | 单次扫描数据，本地排序计算 | | **典型应用** | 生成汇总报表、去重统计 | 明细数据增强分析、滑动窗口计算 | --- #### **详细对比与示例** ##### 1. **数据保留与输出形式** - **`GROUP BY`** **核心规则**：`SELECT`子句中的字段必须参与分组或使用聚合函数[^3][^4]。 ```sql -- 正确示例（分组字段+聚合函数） SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept; -- 错误示例（id未参与分组或聚合） SELECT id, dept, AVG(salary) -- ORA-00979: 不是GROUP BY表达式 FROM employees GROUP BY dept; ``` - **`PARTITION BY`** **核心优势**：保留所有原始字段，仅对分组字段进行窗口计算[^3][^4]。 ```sql -- 显示每个员工的薪资及其部门平均薪资 SELECT id, dept, salary, AVG(salary) OVER(PARTITION BY dept) AS dept_avg_sal FROM employees; ``` --- ##### 2. **聚合范围与灵活性** - **`GROUP BY`** 固定全组聚合，无法实现滑动窗口或累计计算。 ```sql -- 统计部门总人数 SELECT dept, COUNT(*) AS emp_count FROM employees GROUP BY dept; ``` - **`PARTITION BY`** 支持自定义窗口范围（如累计值、移动平均）[^3][^4]。 ```sql -- 计算部门内薪资累计值（按入职时间排序） SELECT id, dept, salary, SUM(salary) OVER(PARTITION BY dept ORDER BY hire_date) AS cumulative_sal FROM employees; ``` --- ##### 3. **排序与排名功能** - **`GROUP BY`** 无法直接实现排序功能，需额外使用`ORDER BY`子句。 ```sql -- 部门平均薪资降序排列 SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept ORDER BY avg_sal DESC; ``` - **`PARTITION BY`** 与排序函数（如`ROW_NUMBER`、`RANK`）结合，实现分组内排序[^3]。 ```sql -- 部门内薪资排名 SELECT id, dept, salary, RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees; ``` --- #### **性能与执行计划差异** 1. **`GROUP BY`** - 触发全表扫描和`Hash Group By`操作，可能因数据倾斜导致性能问题。 - 优化方法：使用`DISTRIBUTE BY`或`HASH`分发数据[^4]。 2. **`PARTITION BY`** - 依赖内存或本地排序（`Window Sort`），大数据量时需注意窗口范围定义。 - 优化方法：减少窗口内数据量（如`RANGE BETWEEN INTERVAL`）。 --- #### **典型应用场景** | 场景 | `GROUP BY` | `PARTITION BY` | |--------------------------|-------------------------------------|-----------------------------------| | **数据汇总** | 部门薪资总额、地区销售总量 | 员工薪资与部门平均值的对比 | | **数据清洗** | 去重统计（如唯一用户数） | 标记重复数据（如`ROW_NUMBER`） | | **时间序列分析** | 月销售额统计 | 近7天累计销售额、移动平均 | | **排名与分位数计算** | 不支持 | Top-N分析、薪资分位数排名 | --- #### **关键注意事项** 1. **`GROUP BY`字段完整性** Oracle严格要求`SELECT`字段必须参与分组或聚合，否则报错`ORA-00979`[^4]。 2. **窗口函数中的`ORDER BY`** 在`PARTITION BY`中，`ORDER BY`会隐式定义窗口范围（默认`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`）。 ```sql -- 累计值（默认窗口范围） SUM(salary) OVER(PARTITION BY dept ORDER BY hire_date) -- 全组聚合（显式指定窗口范围） SUM(salary) OVER(PARTITION BY dept ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ``` ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值