‘壹’ hive 分页sql语句
分页实现的方式比较多了 下面举个例子 比如 获取前10条数据
注：同时需要记录这10条中最大的id为preId，作为下一页的条件。
select * from table order by id asc limit 10;
select * from table where id >preId order by id asc limit 10;
‘贰’ Hive中Order by和Sort by的区别是什么
Hive基于HADOOP来执行分布式程序的，和普通单机程序不同的一个特点就是最终的数据会产生多个子文件，每个recer节点都会处理partition给自己的那份数据产生结果文件，这导致了在HADOOP环境下很难对数据进行全局排序，如果在HADOOP上进行order by全排序，会导致所有的数据集中在一台recer节点上，然后进行排序，这样很可能会超过单个节点的磁盘和内存存储能力导致任务失败。
一种替代的方案则是放弃全局有序，而是分组有序，比如不求全网络最高的点击词排序，而是求每种产品线的最高点击词排序。
使用order by会引发全局排序
select * from _click order by click desc;
使用distribute和sort进行分组排序
select * from _click distribute by proct_line sort by click desc;
distribute by + sort by就是该替代方案，被distribute by设定的字段为KEY，数据会被HASH分发到不同的recer机器上，然后sort by会对同一个recer机器上的每组数据进行局部排序。
order by是全局有序而distribute+sort是分组有序
distribute+sort的结果是按组有序而全局无序的，输入数据经过了以下两个步骤的处理：
1) 根据KEY字段被HASH，相同组的数据被分发到相同的recer节点；
2) 对每个组内部做排序
由于每组数据是按KEY进行HASH后的存储并且组内有序，其还可以有两种用途：
1) 直接作为HBASE的输入源，导入到HBASE；
2) 在distribute+sort后再进行orderby阶段，实现间接的全局排序；
不过即使是先distribute by然后sort by这样的操作，如果某个分组数据太大也会超出rece节点的存储限制，常常会出现137内存溢出的错误，对大数据量的排序都是应该避免的。
‘叁’ Hive sql下的Order by和Sort by的区别
使用order
by会引发全局排序
select
*
from
_click order
by click
desc;
使用distribute和sort进行分组排序
select
*
from
_click distribute
by proct_line sort
by click
desc;
distribute
by
+
sort
by就是该替代方案，被distribute
by设定的字段为KEY，数据会被HASH分发到不同的recer机器上，然后sort
by会对同一个recer机器上的每组数据进行局部排序。
‘肆’ hive order by是升序还是降序
Hive基于HADOOP来执行分布式程序的，和普通单机程序不同的一个特点就是最终的数据会产生多个子文件，每个recer节点都会处理partition给自己的那份数据产生结果文件，这导致了在HADOOP环境下很难对数据进行全局排序，如果在HADOOP上进行order by全排序，会导致所有的数据集中在一台recer节点上，然后进行排序，这样很可能会超过单个节点的磁盘和内存存储能力导致任务失败。一种替代的方案则是放弃全局有序，而是分组有序，比如不求全网络最高的点击词排序，而是求每种产品线的最高点击词排序。使用order by会引发全局排序select * from _click order by click desc;
使用distribute和sort进行分组排序
select * from _click distribute by proct_line sort by click desc;
distribute by + sort by就是该替代方案，被distribute by设定的字段为KEY，数据会被HASH分发到不同的recer机器上，然后sort by会对同一个recer机器上的每组数据进行局部排序。
‘伍’ hive中order by，distribute by，sort by和cluster by的区别和联系
1、order
by
order
by
会对数据进行全局排序,和oracle和mysql等数据库中的order
by
效果一样，它只在一个rece中进行所以数据量特别大的时候效率非常低。而且当设置
：set
hive.
mapred.
mode
=strict的时候不指定limit，执行select会报错，如下：LIMIT
must
also
be
specified.
2、sort
by
sort
by
是单独在各自的rece中进行排序，所以并不能保证全局有序，一般和distribute
by
一起执行，而且distribute
by
要写在sort
by前面。
如果mapred.rece.tasks=1和order
by效果一样，如果大于1会分成几个文件输出每个文件会按照指定的字段排序，而不保证全局有序。
sort
by
不受
hive.mapred.mode
是否为strict
,nostrict
的影响
3、distribute
by
用distribute
by
会对指定的字段按照hashCode值对rece的个数取模，然后将任务分配到对应的rece中去执行，就是在maprece程序中的patition分区过程，默认根据指定key.hashCode()&Integer.MAX_VALUE%numRece
确定处理该任务的rece。
4、cluster
By
distribute
by
和
sort
by
合用就相当于cluster
by，但是cluster
by
不能指定排序为asc或
desc
的规则，只能是desc倒序排列。
‘陆’ hive order by 和group by可以为同一个字段吗
不可以
order by
会对输入做全局排序，因此只有一个
recer
（多个recer无法保证全局有序）
只有一个recer，会导致当输入规模较大时，需要较长的计算时间。
set hive.mapred.mode=nonstrict; (default value /
默认值) set hive.mapred.mode=strict; order by 和数据库中的Order by 功能一致，按照某一项
&
几项排序输出。
‘柒’ HiveSQL核心技能之窗口计算
目标：
1、掌握 sum()、avg()等用于累计计算的聚合函数，学会对行数的限制（移动计算）；
2、掌握 row_number(),rank()、dense_rank()用于排序的函数；
3、掌握 ntile()用于分组查询的函数；
4、掌握 lag()、lead()偏移分析函数
窗口函数（window function）：
与聚合函数类似，但是窗口函数是每一行数据都生成一个结果，聚合函数可以将多行数据按照规定聚合为一行，一般来说聚合后的行数要少于聚合前的行数，但是有时我们想要既显示聚合前的数据，又要显示聚合后的数据，这时便引入了窗口函数， 窗口函数是在 select 时执行的，位于 order by 之前 。
在日常工作中，经常遇到 计算截止某月或某天的累计数值 ，在Excel可以通过函数来实现，
在HiveSQL里，可以利用窗口函数实现。
1）2018年每月的支付总额和当年累计支付总额
2）对2017年和2018年公司的支付总额按月度累计进行分析，按年度进行汇总
说明：1、over中的 partition by 起到分组的作用；
2、order by 按照什么顺序进行累加，升序ASC、降序DESC，默认升序
3、正确的分组是非常重要的，partition by 后面的字段是需要累计计算的区域，需要仔细理解
（计算三日留存、七日留存、三十日留存等方式可以使用这个函数。）
3）对2018年每个月的近三个月进行移动的求平均支付金额
用法：这三个函数的作用都是返回相应规则的排序序号，由于排序函数不是二次聚合计算，因此不一定要使用子查询
4）2019年1月，用户购买商品品类数量的排名
5）选出2019年支付金额排名在第10、20、30名的用户
6）将2019年1月的支付用户，按照支付金额分成5组
7）选出2019年退款金额排名前10%的用户
说明：Lag和Lead分析函数可以在同一次查询中取出同一字段的 前N行数据(Lag)和后N行的数据(Lead) 作为独立的列。
在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。
当然，这种操作可以用表的 自连接实现 ，但是Lag和Lead与 left join、 right join等自连接相比，效率更高，SQL语句更简洁。
8）支付时间间隔超过100天的用户数(这一次购买距离下一次购买的时间？，注意datediff函数是日期大的在前面)
9）每个城市，不同性别，2018年支付金额最高的TOP3用户
步骤总结：
1、首先筛选出每个用户和每个用户总的消费金额；
2、对两个表进行连接提取需要的字段；
3、对连接后的表进行二次聚合计算，计算出不同城市、性别的金额排名；
4、对二次聚合计算的表进行条件筛选提取
10）每个手机品牌退款金额前25%的用户
步骤总结：
1、首先筛选出每个用户和每个用户的总退款金额；
2、对两个表进行连接提取需要的字段；
3、对连接后的表进行按手机品牌内分组；
4、对分组后的表进行条件筛选提取
‘捌’ 数据分析课程笔记 - 20 - HIVE 核心技能之窗口函数
大家好呀，这节课我们学习 Hive 核心技能中最难的部分——窗口函数。窗口函数我们之前在学 MySQL 的时候有学过一些，但是只学了三个排序的窗口函数。这节课我们会学习更多的窗口函数，包括累计计算、分区排序、切片排序以及偏移分析。
在正式学习之前，我们需要先明确一下窗口函数和GROUP BY分组的区别。二者在功能上有相似之处，但是它们存在本质区别。
1. 分组会改变表的结构，而窗口函数不会改变表的结构。比如原表有10行数据，分成两组后只有两行，而窗口函数仍然返回十行数据。
2. 分组只能查询分组后的字段，包括分组字段（组名）和聚合函数字段。而窗口函数对查询字段没有限制，也就是可以查询原表的任意字段，再加上窗口函数新增的一列值。
好啦，现在让我们一起进入窗口函数的世界吧~
本节课主要内容：
1、累计计算窗口函数
（1）sum(…) over(……)
（2）avg(…) over(……)
（3）语法总结
2、分区排序窗口函数
（1）row_number()
（2）rank()
（3）dense_rank()
3、切片排序窗口函数
（1）ntile(n) over(……)
4、偏移分析窗口函数
5、重点练习
大家在做报表的时候，经常会遇到计算截止某月的累计数值，通常在EXCEL里可以通过函数来实现。
那么在HiveSQL里，该如何实现这种累计数值的计算呢？那就是利用窗口函数！
关于窗口函数的几点说明：
需求分析 ：既然要进行按月累计，我们就先要把2018年的每笔交易时间转换成月并按月分组聚合计算，得出一个2018年每月支付金额总合表，再基于这张表用窗口函数进行累计计算。
2018年每月支付金额总和表：
再用窗口函数进行月度累计：
年度进行汇总。
这个需求比需求1多了一个需求，那就是年度汇总。那我们只需要在上个需求的子查询中加一个 year 字段即可。
说明：
1、over 中的 partition by 起到了窗口内将数据分组的作用。事实上，加上partition by之后，可以理解为分成了多个窗口，并在每个窗口内进行累加计算或者分区。
如果不加 partition by a.year 的话，运行结果就是这样单纯按月份进行分组的：
2、order by 按照什么顺序进行累加，升序ASC、降序DESC，默认是升序。
大家看股票的时候，经常会看到这种K线图，里面经常用到的就是7日、30日移动平均的趋势图，那如何使用窗口函数来计算移动平均值呢？
需求分析 ：这个需求要求每个月近三个月的移动平均支付金额，这里我们要用到一个新知识点，在窗口函数 avg over 的 order by a.month 之后加一句 rows between 2 preceding and current row 来设定计算移动平均的范围，这个语句的含义就是包含本行及前两行。其他部分的写法跟前面的需求类似，先取出2018年每个月的支付金额总和，再用窗口函数求移动平均。
注意：
sum(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)
avg(…A…) over(partition by …B… order by …C… rows between…D1… and …D2…)
A：需要被加工的字段名称
B：分组的字段名称
C：排序的字段名称
D：计算的行数范围
rows between unbounded preceding and current row
——包括本行和之前所有的行
rows between current row and unbounded following
——包括本行和之后所有的行
rows between 3 preceding and current row
——包括本行以内和前三行
rows between 3 preceding and 1 following
——从前三行到下一行(5行)
max(……) over(partition by …… order by …… rows between ……and ……)
min(……) over(partition by …… order by …… rows between ……and ……)
row_number() 、rank()、dense_rank()
用法：这三个函数的作用都是返回相应规则的排序序号
row_number() over(partition by …A… order by …B… )
rank() over(partition by …A… order by …B… )
dense_rank() over(partition by …A… order by …B… )
A：分组的字段名称
B：排序的字段名称
注意： 这3个函数的括号内是不加任何字段名称的！
row_number ：它会为查询出来的每一行记录生成一个序号，依次排序且不会重复。
rank&dense_rank ：在各个分组内， rank() 是跳跃排序，有两个第一名时接下来就是第三名， dense_rank() 是连续排序，有两个第一名时仍然跟着第二名。
实例练习：
再眼熟一下 user_trade 的表结构：
需求分析 ：先限定时间范围，然后根据 user_name 进行分组，接着选出 分组去重后的 user_name，并计算每个用户 goods_category 的数量（记得 distinct 去重），再然后就是用窗口函数对 goods_category 的数量进行排序，当然选择哪一种排序方法要看具体要求，这里我们可以三种方法都试一下看看结果：
注意 ：窗口函数中的 order by 字段不能用 select 中字段的重命名，因为二者是同时执行的。
需求分析 ： 先用窗口函数将2019年每个用户的支付总金额算出来并进行排序，再以此作为子查询，从中取出排名在第10、20、30名的用户名、支付总金额以及排名次序。企业一般会使用 dense_rank 进行排序，所以我们这里直接用 dense_rank。
2019年每个用户的支付总金额排名：
2019年支付金额排名在第10、20、30名的用户：
ntile(n) over(partition by …A… order by …B… )
n：切分的片数
A：分组的字段名称
B：排序的字段名称
需求分析 ：这个需求很简单，把需求5第一步的排序窗口函数变成切片即可。注意时间筛选条件变成2019年1月。
需求分析 ： 排名前10%，也就是一共分成10组，取第1组。那么我们先切片分组：
然后再取第一组：
说明：Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。
在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与left join、right join等自连接相比，效率更高，SQL语句更简洁。
lag(exp_str,offset,defval) over(partion by ……order by ……)
lead(exp_str,offset,defval) over(partion by ……order by ……)
lag() 函数示例：
lead() 函数示例：
需求分析： 先要从 user_trade 表中取出每个用户的支付时间，把每个用户放到一个窗口中，按照支付时间进行排序，取出偏移列： lead(dt,1,dt) over(partition by user_name order by dt)。接着基于该子查询，筛选出时间间隔大于100天的用户，并计算数量。
注意 ： 如果上面偏移分析函数写成 lead(dt,1,dt) 就不用加后面的 dt is not null 了，因为有默认值的话，间隔就是0，肯定是不满足条件的。
需求分析 ：
第一步 ：这个需求要用到 user_trade 和 user_info 两张表，前者取支付时间和金额，后者取城市和性别。先对这两张表基于 user_name 进行左连接，并取出相应字段，用窗口函数进行分组排序：
这一步的运行结果是这样的：
第二步 ：基于上述结果取出TOP3：
需求分析：
第一步 ：这个需求同样要用到两张表 user_refund 和 user_info。我们先把每个退款用户的退款金额和手机品牌取出来，并用窗口函数进行切片排序，25%就是分成4片：
注意 ：这里之所以要加 WHERE dt is not null 是因为 user_refund 是一个分区表，分区表要对分区字段进行限制，否则 hive 会报错。
第二步 ：选择前25%，也就是第一片：
最后补充一个从 hive 导出结果数据的命令：
以上就是这节课的全部内容了。做完整个练习，真的半条命都没了。窗口函数果然很难，不过掌握方法、多多练习，学会拆解需求，一步一步来做，就能明显降低难度。希望以后有机会能用到这么复杂的技能，哈哈~！
‘玖’ hive sql 中 怎么用变量
分页实现的方式比较多了
下面举个例子
比如
获取前10条数据
注：同时需要记录这10条中最大的id为preid，作为下一页的条件。
select
*
from
table
order
by
id
asc
limit
10;
select
*
from
table
where
id
>preid
order
by
id
asc
limit
10;
‘拾’ hive sql里，帮我描述一个简单的sql的原理
select a.id,a.info,b.num from a join b on a.id=b.id and where b.num>=10
两个表做关联，首先where会过滤掉不需要的数据。
至于表怎么做map和rece操作，在hive里的表是虚拟的，其实还是对hdfs文件进行操作，你可以在hdfs:///user/hive/warehouse路径下找到以表名来命名的文件，里面就是表的内容，可以执行-cat命令查看。所以，它的map操作很简单，就是按行读文件，然后会根据hive的默认分隔符\001对每行进行切分。切分完成后就会按照你SQL指定的逻辑进行合并，最后再输出成hdfs文件，只不过在hive里面看它是以表的形式展现的。
job数会在你执行sql语句之后紧接着有相应的日志记录，
Total MapRece jobs = 2
Launching Job 1 out of 2
Number of rece tasks not specified. Estimated from input data size: 2
In order to change the average load for a recer (in bytes):
set hive.exec.recers.bytes.per.recer=<number>
In order to limit the maximum number of recers:
set hive.exec.recers.max=<number>
In order to set a constant number of recers:
这样就是有两个job，正在执行第一个job。
Hadoop job information for Stage-1: number of mappers: 5; number of recers: 2
而这个就会告诉你有多少个mapper和recer。
像你写的这个sql有join操作，而且是hiveSQL里面最普通的join，那么一定会有recer参与，如果数据量很大，比如上千万条记录，join就会特别慢，job进度就会一直卡在rece操作。可以改成mapjoin或者sort merge bucket mapjoin。
其实hive效率不高，不适合实时查询，即使一个表为空，用hive进行查询也会很耗时，因为它要把sql语句翻译成MR任务。虽然简化了分布式编程，但是效率上就会付出代价。
你的这句sql应该会翻译成一个JOB来执行，就是简单地map和rece。
maprece就是按行读文件，然后切分，合并，输出成文件。