1. Hive sql及窗口函数
hive函数：
1、根据指定条件返回结果：case when then else end as
2、基本类型转换：CAST（）
3、nvl：处理空字段：三个str时，是否为空可以指定返回不同的值
4、sql通配符： https://www.w3school.com.cn/sql/sql_wildcards.asp
5、count(1)与COUNT(*)：返回行数
如果表没有主键，那么count（1）比count（*）快；
如果有主键，那么count（主键，联合主键）比count（*）快；
count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。
性能问题：
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择,(指没有where的情况）；
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
count(expression)：查询 is_reply=0 的数量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;
6、distinct与group by
distinct去重所有distinct之后所有的字段，如果有一个字段值不一致就不作为一条
group by是根据某一字段分组，然后查询出该条数据的所需字段，可以搭配 where max(time)或者Row_Number函数使用，求出最大的一条数据
7、使用with 临时表名 as() 的形式,简单的临时表直接嵌套进sql中，复杂的和需要复用的表写到临时表中，关联的时候先找到关联字段，过滤条件最好在临时表中先过滤后关联
处理json的函数：
split(json_array_string(schools), '\\|\\|') AS schools
get_json_object(school, '$.id') AS school_id,
字符串函数：
1、instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）
instr(sourceString,destString,start,appearPosition)
1.sourceString代表源字符串； destString代表要从源字符串中查找的子串；
2.start代表查找的开始位置，这个参数可选的，默认为1；
3.appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1
4.如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。
5.返回值为：查找到的字符串的位置。如果没有查找到，返回0。
最简单例子： 在abcd中查找a的位置，从第一个字母开始查，查找第一次出现时的位置
select instr(‘abcd’,’a’,1,1) from al; —1
应用于模糊查询：instr(字段名/列名, ‘查找字段’)
select code,name,dept,occupation from staff where instr(code, ‘001’)> 0;
等同于 select code, name, dept, occupation from staff where code like ‘%001%’ ;
应用于判断包含关系：
select ccn,mas_loc from mas_loc where instr(‘FH,FHH,FHM’,ccn)>0;
等同于 select ccn,mas_loc from mas_loc where ccn in (‘FH’,’FHH’,’FHM’);
2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样
substr(time,1,8) 表示将time从第1位开始截取，截取的长度为8位
第一种用法：
substr（string A，int start）和 substring（string A，int start），用法一样
功效：返回字符串A从下标start位置到结尾的字符串
第二种用法：
substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样
功效：返回字符串A从下标start位置开始，长度为len的字符串
3、get_json_object(form_data,'$.学生姓名') as student_name
json_tuple 函数的作用：用来解析json字符串中的多个字段
4、split(full_name, '\\.') [5] AS zq; 取的是数组里的第六个
日期(时间)函数：
1、to_date(event_time) 返回日期部分
2、date_sub：返回当前日期的相对时间
当前日期：select curdate()
当前日期前一天：select date_sub(curdate(),interval 1 day)
当前日期后一天：select date_sub(curdate(),interval -1 day)
date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14) 将现在的时间总秒数转为标准格式时间，返回14天之前的时间
时间戳>>>>日期：
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 将现在的时间总秒数转为标准格式时间
from_unixtime(get_json_object(get_json_object(form_data,'$.挽单时间'),'$.$date')/1000) as retain_time
unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss') --1565858400
日期>>>>时间戳：unix_timestamp()
date_format：yyyy-MM-dd HH:mm:ss 时间转格式化时间
select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000
1.日期比较函数: datediff语法: datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例： hive> select datediff('2016-12-30','2016-12-29'); 1
2.日期增加函数: date_add语法: date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例： hive>select date_add('2016-12-29',10); 2017-01-08
3.日期减少函数: date_sub语法: date_sub (string startdate,int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例： hive>select date_sub('2016-12-29',10); 2016-12-19
4.查询近30天的数据
select * from table where datediff(current_timestamp,create_time)<=30；
create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00
3、trunc()函数的用法：当前日期的各种第一天,或者对数字进行不四舍五入的截取
日期：
1.select trunc(sysdate) from al --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from al --2011-3-1 返回当月第一天.
上月1号 trunc(add_months(current_date(),-1),'MM')
3.select trunc(sysdate,'yy') from al --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from al --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from al --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from al --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from al --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from al --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
数字：TRUNC（number,num_digits） Number 需要截尾取整的数字。Num_digits 的默认值为 0。TRUNC()函数截取时不进行四舍五入
11.select trunc(123.458,1) from al --123.4
12.select trunc(123.458,-1) from al --120
4、round()：四舍五入：
select round(1.455, 2) #结果是：1.46，即四舍五入到十分位，也就是保留两位小数
select round(1.5) #默认四舍五入到个位，结果是：2
select round(255, -1) #结果是：260，即四舍五入到十位，此时个位是5会进位
floor()：地板数
ceil()天花板数
5、
6.日期转年函数: year语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：
hive> select year('2011-12-08 10:03:01') from al;
2011
hive> select year('2012-12-08') fromal;
2012
7.日期转月函数: month语法: month (string date)
返回值: int
说明: 返回日期中的月份。
举例：
hive> select month('2011-12-08 10:03:01') from al;
12
hive> select month('2011-08-08') fromal;
8
8.日期转天函数: day语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：
hive> select day('2011-12-08 10:03:01') from al;
8
hive> select day('2011-12-24') fromal;
24
9.日期转小时函数: hour语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：
hive> select hour('2011-12-08 10:03:01') from al;
10
10.日期转分钟函数: minute语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：
hive> select minute('2011-12-08 10:03:01') from al;
3
11.日期转秒函数: second语法: second (string date)
返回值: int
说明: 返回日期中的秒。
举例：
hive> select second('2011-12-08 10:03:01') from al;
1
12.日期转周函数: weekofyear语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：
hive> select weekofyear('2011-12-08 10:03:01') from al;
49
查看hive表在hdfs中的位置：show create table 表名;
在hive中hive2hive，hive2hdfs：
HDFS、本地、hive -----> Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;
Hive ----> Hdfs、本地：使用：insert overwrite | local
网站访问量统计：
uv：每用户访问次数
ip：每ip(可能很多人)访问次数
PV:是指页面的浏览次数
VV:是指你访问网站的次数
sql：
基本函数：
count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正则）
and、or、not、in
where、group by、having、{ join on 、full join} 、order by（desc降序）
sort by需要与distribut by集合结合使用：
hive (default)> set maprece.job.reces=3; //先设置rece的数量
insert overwrite local directory '/opt/mole/datas/distribute-by'
row format delimited fields terminated by '\t'
先按照部门编号分区，再按照员工编号降序排序。
select * from emp distribute by deptno sort by empno desc;
外部表 create external table if not exists dept
分区表：create table dept_partition ( deptno int, dname string, loc string ) partitioned by ( month string )
load data local inpath '/opt/mole/datas/dept.txt' into table default.dept_partition partition(month='201809');
alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');
多分区联合查询：union
select * from dept_partition2 where month='201809' and day='10';
show partitions dept_partition;
desc formatted dept_partition;
二级分区表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';
分桶抽样查询：分区针对的是数据的存储路径；分桶针对的是数据文件
create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';
设置开启分桶与rece为1：
set hive.enforce.bucketing=true;
set maprece.job.reces=-1;
分桶抽样：select * from stu_bucktablesample(bucket x out of y on id);
抽取，桶数/y，x是从哪个桶开始抽取，y越大 抽样数越少，y与抽样数成反比，x必须小于y
给空字段赋值：
如果员工的comm为NULL，则用-1代替或用其他字段代替 ：select nvl(comm,-1) from emp;
case when:如何符合记为1，用于统计、分组统计
select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;
用于组合归类汇总(行转列)：UDAF：多转一
concat：拼接查询结果
collect_set(col)：去重汇总，产生array类型字段，类似于distinct
select t.base, concat_ws('|',collect_set(t.name)) from (select concat_ws(',',xingzuo,blood_type) base,name from person_info) t group by t.base;
解释：先第一次查询得到一张没有按照（星座血型）分组的表，然后分组，使用collect_set将名字组合成数组，然后使用concat将数组变成字符串
用于拆分数据：(列转行)：UDTF：一转多
explode(col)：将hive一列中复杂的array或者map结构拆分成多行。
lateral view 侧面显示：用于和UDTF一对多函数搭配使用
用法：lateral view udtf(expression) tablealias as cate
cate：炸开之后的列别名
temptable ：临时表表名
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
开窗函数：
Row_Number,Rank，Dense_Rank over：针对统计查询使用
Row_Number：返回从1开始的序列
Rank:生成分组中的排名序号，会在名词s中留下空位。3 3 5
dense_rank：生成分组中的排名序号，不会在名词中留下空位。3 3 4
over：主要是分组排序，搭配窗口函数使用
结果：
SUM、AVG、MIN、MAX、count
preceding：往前
following：往后
current row：当前行
unbounded：unbounded preceding 从前面的起点， unbounded following：到后面的终点
sum：直接使用sum是总的求和，结合over使用可统计至每一行的结果、总的结果、当前行+之前多少行/之后多少行、当前行到往后所有行的求和。
over(rowsbetween 3/current ) 当前行到往后所有行的求和
ntile：分片，结合over使用，可以给数据分片，返回分片号
使用场景：统计出排名前百分之或n分之一的数据。
lead,lag,FIRST_VALUE,LAST_VALUE
lag与lead函数可以返回上下行的数据
lead(col,n,dafault) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
使用场景：通常用于统计某用户在某个网页上的停留时间
FIRST_VALUE:取分组内排序后，截止到当前行，第一个值
LAST_VALUE:取分组内排序后，截止到当前行,最后一个值
范围内求和: https://blog.csdn.net/happyrocking/article/details/105369558
cume_dist，percent_rank
–CUME_DIST :小于等于当前值的 行数 / 分组内总行数
–比如，统计小于等于当前薪水的人数，占总人数的比例
percent_rank:分组内当前行的RANK值-1/分组内总行数-1
总结：
在Spark中使用spark sql与hql一致，也可以直接使用sparkAPI实现。
HiveSql窗口函数主要应用于求TopN，分组排序TopN、TopN求和，前多少名前百分之几。
与Flink窗口函数不同。
Flink中的窗口是用于将无线数据流切分为有限块处理的手段。
window分类：
CountWindow：按照指定的数据条数生成一个 Window，与时间无关。
TimeWindow：按照时间生成 Window。
1. 滚动窗口（Tumbling Windows）：时间对齐，窗口长度固定，不重叠：：常用于时间段内的聚合计算
2.滑动窗口（Sliding Windows）：时间对齐，窗口长度固定，可以有重叠：：适用于一段时间内的统计（某接口最近 5min 的失败率来报警）
3. 会话窗口（Session Windows）无时间对齐，无长度，不重叠：：设置session间隔，超过时间间隔则窗口关闭。
2. HiveSQL核心技能之表连接
目标：
1、掌握HQL中的各种连接及其组合使用；
2、掌握数据分析中业务指标思路转换的技巧；
3、区分好full join 和 union all 的使用场景；
4、在多表连接时，注意各种细节和业务逻辑；
5、复杂表连接要学会分步骤处理
需注意：
1、表连接时，必须进行重命名；
2、on后面使用的连接条件必须起到 唯一键值 的作用（有时会有多个字段组合）；
3、inner可省略不写，效果是一样的
4、表连接时不能使用 a join b join c这种方式，不然会极度浪费电脑的资源和延长查询时间，要在子查询的表里先做好筛选之后在连接；
1）找出在2019年购买后又退款的用户（记得要去重）
注意：一定要先去重，再做表连接，养成良好的习惯（虽然可以先连接再去重，但是那么做会使执行效率很低）
2）在2017年和2018年都购买的用户
3）在2017年、2018年、2019年都有交易的用户
进行左连接之后，以左表为全集，返回能够匹配上的右边表的匹配结果，没有匹配上的则显示NULL。
拓展：
right join：以右表为全集，返回能够匹配上的左边表的匹配结果，没有匹配上的则显示NULL，可以由left join改写出同样的结果。
4）在2019年购买，但是没有退款的用户
5）在2019年由购买的用户的学历分布
6）在2017年和2018年都购买，但是没有在2019年购买的用户
查询两个表的所有用户时使用full join是一个比较好的方法（需要用到coalesce函数：
注：coalesce函数，coalesce(expression1,expression2,...,expression n)，依次参考各参数表达式，遇到非null值即停止并返回该值，如果所有的表达式都是空值，最终将返回一个空值。
注：表合并时字段名称必须一致，字段顺序必须一致，而且不用填写连接条件
7）2017-2019年由交易的所有用户数
union all 和 union 的区别：
union all 不会去重，不会排序，效率较快；union 会去重且排序，效率较慢。
如果表很大时，推荐先去重，再进行 union all ，不能直接对表进行 union all，不然效率很慢。
8）2019年每个用户的支付和退款金额汇总
也可以使用 full join 的方式：
9）2019年每个支付用户的支付金额和退款金额
10）首次激活时间在2017年，但是一直没有支付的用户年龄段分布
步骤总结：
1、先筛选出年份为2017注册的用户；
2、没有支付的人；
3、年龄段分布
注意：由于age也是在user_info的表格里，第三步用的字段需要在第一步进行预处理，所以在限制时间的时候需要同时对年龄段进行预处理，这样在第三步的时候才会由年龄段这个字段；需要注意对 case when 的字段进行重命名才能进行后续的操作
11）2018、2019年交易的用户，其激活时间段分布
步骤总结：
1. 取出2018和2019年所有的交易用户的交集
2. 取出所有用户的激活时间
3. 统计时间分布
3. 数据分析课程笔记 - 19 - HiveSQL 常用优化技巧
大家好呀，这节课学习 HiveSQL 的常用优化技巧。由于 Hive 主要用来处理非常大的数据，运行过程由于通常要经过 MapRece 的过程，因此不像 MySQL 一样很快出结果。而使用不同方法写出来的 HiveSQL 语句执行效率也是不一样的，因此为了减少等待的时间，提高服务器的运行效率，我们需要在 HiveSQL 的语句上进行一些优化。
本节课的主要内容 ：
引言
1、技巧一：列裁剪和分区裁剪
（1）列裁剪
（2）分区裁剪
2、技巧二：排序技巧——sort by代替order by
3、技巧三：去重技巧——用group by来替换distinct
4、技巧四：聚合技巧——grouping sets、cube、rollup
（1）grouping sets
（2）cube
（3）rollup
5、技巧五：换个思路解题
6、技巧六：union all时可以开启并发执行
7、技巧七：表连接优化
8、技巧八：遵循严格模式
Hive 作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job 或 I/O 过多、MapRece 分配不合理等等。对 Hive 的调优既包含对HiveSQL 语句本身的优化，也包含 Hive 配置项和 MR 方面的调整。
列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时，如果select 所有的列或者不指定分区，导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是 hive.optimize.cp ，默认是 true 。
分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是 hive.optimize.pruner ，默认是 true 。
HiveSQL中的 order by 与其他 SQL 语言中的功能一样，就是将结果按某个字段全局排序，这会导致所有map端数据都进入一个 rece 中，在数据量大时可能会长时间计算不完。
如果使用 sort by ，那么就会视情况启动多个 recer 进行排序，并且保证每个 recer 内局部有序。为了控制 map 端数据分配到 rece 的 key，往往还要配合 distribute by 一同使用。如果不加 distribute by 的话，map 端数据就会随机分配给 recer。
这里需要解释一下， distribute by 和 sort by 结合使用是如何相较于 order by 提升运行效率的。
假如我们要对一张很大的用户信息表按照年龄进行分组，优化前的写法是直接 order by age 。使用 distribute by 和 sort by 结合进行优化的时候， sort by 后面还是 age 这个排序字段， distribute by 后面选择一个没有重复值的均匀字段，比如 user_id 。
这样做的原因是，通常用户的年龄分布是不均匀的，比如20岁以下和50岁以上的人非常少，中间几个年龄段的人又非常多，在 Map 阶段就会造成有些任务很大，有些任务很小。那通过 distribute by 一个均匀字段，就可以让系统均匀地进行“分桶”，对每个桶进行排序，最后再组合，这样就能从整体上提升 MapRece 的效率。
取出 user_trade 表中全部支付用户：
原有写法的执行时长：
优化写法的执行时长：
考虑对之前的案例进行优化：
注意： 在极大的数据量（且很多重复值）时，可以先 group by 去重，再 count() 计数，效率高于直接 count(distinct **) 。
如果我们想知道用户的性别分布、城市分布、等级分布，你会怎么写？
通常写法：
缺点 ：要分别写三次SQL，需要执行三次，重复工作，且费时。
那该怎么优化呢？
注意 ：这个聚合结果相当于纵向地堆在一起了（Union all），分类字段用不同列来进行区分，也就是每一行数据都包含 4 列，前三列是分类字段，最后一列是聚合计算的结果。
GROUPING SETS() ：在 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all。聚合规则在括号中进行指定。
如果我们想知道用户的性别分布以及每个性别的城市分布，你会怎么写？
那该怎么优化呢？
注意： 第二列为NULL的，就是性别的用户分布，其余有城市的均为每个性别的城市分布。
cube：根据 group by 维度的所有组合进行聚合
注意 ：跑完数据后，整理很关键！！！
rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。
如果我想同时计算出，每个月的支付金额，以及每年的总支付金额，该怎么办？
那应该如何优化呢？
条条大路通罗马，写SQL亦是如此，能达到同样效果的SQL有很多种，要学会思路转换，灵活应用。
来看一个我们之前做过的案例：
有没有别的写法呢？
Hive 中互相没有依赖关系的 job 间是可以并行执行的，最典型的就是
多个子查询union all。在集群资源相对充足的情况下，可以开启并
行执行。参数设置： set hive.exec.parallel=true;
时间对比：
所谓严格模式，就是强制不允许用户执行3种有风险的 HiveSQL 语句，一旦执行会直接报错。
要开启严格模式，需要将参数 hive.mapred.mode 设为 strict 。
好啦，这节课的内容就是这些。以上优化技巧需要大家在平时的练习和使用中有意识地去注意自己的语句，不断改进，就能掌握最优的写法。
4. Hivesql计算两个时间戳相差的分钟数
1.Hive row_number() 函数的高级用法 row_num 按照某个字段分区显示第几条数据
select imei,ts,fuel_instant,gps_longitude,gps_latitude,row_number() over (PARTITION BY imei ORDER BY ts ASC) as row_num from sample_data_2
2.row_num 是相互连续的，join 自身，然后时间相减可求差
create table obd_20140101 as
select a.imei,a.row_num,a.ts,COALESCE(unix_timestamp(a.ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(b.ts, 'yyyy-MM-dd HH:mm:ss.S') as intervel ,a.fuel_instant,a.gps_speed as obd_speed,a.gps_status,a.gps_longitude,a.gps_latitude,a.direct_angle,a.obdspeed from obddata_20140101 a join obddata_20140101 b on a.imei = b.imei and a.row_num = b.row_num +1
5. SQL语句如何根据出生日期计算年龄
第一种:
一张人员信息表里有一人生日(Birthday)列,跟据这个列,算出该人员的年龄
datediff(year,birthday,getdate())
例:birthday = '2003-3-8'
getDate()= '2008-7-7'
结果为:5
这样结果是会返回该人员的大概年龄,但不精确.不会精确到月或日.
按照上面测试的日期,该人员的实际年龄应该还不满5岁。在需要精确年龄的时候，就会有错.
第二种：
FLOOR(datediff(DY,birthday,getdate())/365.25)
FLOOR函数：
FLOOR(expr) 返回小于或等于expr的最大整数.FLOOR(1.1)返回1,FLOOR(-1.1)返回-2,FLOOR(1)返回1
这样就可以精确算出，该人员当前实际年龄了.
测试:
birthday = '2000-7-8'
getdate()= '2007-7-7'
算出结果为:6
在Oracle中，要获得日期中的年份,例如把sysdate中的年份取出来。
一种常用的方法是：to_number(to_char(sysdate,'yyyy'))
还有一种更好的方法，那就是使用oracle提供的Extract函数，使用方法是：
extract(year from sysdate) ，此方法获得的结果是数值型的 ，这种方法省掉了类型转换，更加简洁。
相应的，要取得月份或日，可以用extract (month from sysdate) 和extract (day from sysdate)
6. HiveSQL核心技能之常用函数
目标：
1、掌握hive基础语法、常用函数及其组合使用
2、掌握一些基本业务指标的分析思路与实现技巧
1）某次经营活动中，商家发起了“异性拼团购”，试着针对某个地区的用户进行推广，找出匹配用户。
注意：如果该表是一个分区表，则where条件中必须对分区字段进行限制
2）选出在2018年12月31日，购买的商品品类是food的用户名、购买数量、支付金额
3）试着对本公司2019年第一季度商品的热度与价值度进行分析。
"2019年一月到三月，每个品类有多少人购买，累计金额是多少"
GROUP BY ... HAVING（分类汇总过滤）
4）找出在2019年4月支付金额超过5万元的用户，给这些VIP用户赠送优惠券
5）2019年4月，支付金额最多的TOP5用户
查看Hive中的函数：show functions；
查看具体函数的用法：1）desc function 函数名；2）desc function extended函数名；
6）
7）用户的首次激活时间，与2019年5月1日的日期间隔。
datediff(string enddate,string startdate):结束日期减去开始日期的天数
拓展:日期增加函数（增加天数）、减少函数（减少天数） —— date_add、date_sub（类型要是string类型的）
date_add（string startdate,int days)、date_sub（string startdate,int days)
case when 函数
8）统计以下四个年龄段20岁以下、20-30岁、30-40岁、40岁以上的用户数
case when 的时候不用加group by，只有在聚合函数的时候才需要group by
if 函数
9）统计每个性别的用户等级高低分布情况（假设level大于5为高级，注意列名不能使用中文的）
10）分析每个月都拉新情况
substring(stringA,INT start,int len),substr(stringA,INT start,int len)，截取起始位置和截取长度
extra1需要解析json字段，然后用$.key取出想要获取的value;
extra2使用的是中括号加引号的方式进行字段提取和分组；
两种写法要看字段类型的不同采取不同的方式
11）求不同手机品牌的用户数
12）ELLA用户的2018年的平均每次支付金额，以及2018年最大的支付日期和最小的支付日期的间隔
13）2018年购买的商品品类在两个以上的用户数
步骤总结：
1、先求出每个人购买的商品品类书
2、筛选出购买商品品类书大于2的用户
3、统计符合条件的用户有多少个
14）用户激活时间在2018年，年龄段在20-30岁和30-40岁的婚姻情况分布
步骤总结：
1、先选出激活时间在2018年的用户，并把他们所在的年龄段计算好，并提取出婚姻状况；
如何select后面没有进行聚合，则可以不用使用group by，直接使用where进行过滤就可以；
2、取出年龄段在20-30岁和30-40岁的用户，把他们的婚姻状况转义成可理解的说明；
3、聚合计算，针对年龄段，婚姻状况的聚合
7. 以下哪些sql语句在hive中执行可以得到系统当前时间年月日
substring(now(),1,10)
from_unixtime(unix_timestamp,'yyyy-MM-dd')
8. 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、对分组后的表进行条件筛选提取
9. 怎么将本地的.sql文件导入hadoop master
完成sqoop的安装后，可以这样测试是否可以连接到mysql(注意：mysql的jar包要放到 SQOOP_HOME/lib 下)：
sqoop list-databases --connect jdbc:mysql://192.168.1.109:3306/ --username root --password 19891231
结果如下
即说明sqoop已经可以正常使用了。
下面，要将mysql中的数据导入到hadoop中。
我准备的是一个300万条数据的身份证数据表：
先启动hive（使用命令行：hive 即可启动）
然后使用sqoop导入数据到hive：
sqoop import --connect jdbc:mysql://192.168.1.109:3306/hadoop --username root --password 19891231 --table test_sfz --hive-import
sqoop 会启动job来完成导入工作。
完成导入用了2分20秒，还是不错的。
在hive中可以看到刚刚导入的数据表：
我们来一句sql测试一下数据：
select * from test_sfz where id < 10;
可以看到，hive完成这个任务用了将近25秒，确实是挺慢的（在mysql中几乎是不费时间），但是要考虑到hive是创建了job在hadoop中跑，时间当然多。
接下来，我们会对这些数据进行复杂查询的测试：
我机子的配置如下：
hadoop 是运行在虚拟机上的伪分布式，虚拟机OS是ubuntu12.04 64位，配置如下：
TEST 1 计算平均年龄
测试数据：300.8 W
1. 计算广东的平均年龄
mysql：select (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz where address like '广东%';
用时： 0.877s
hive：select (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz where address like '广东%';
用时：25.012s
2. 对每个城市的的平均年龄进行从高到低的排序
mysql：select
address, (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge
from test_sfz GROUP BY address order by ageAvge desc;
用时：2.949s
hive：select
address, (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as
ageAvge from test_sfz GROUP BY address order by ageAvge desc;
用时：51.29s
可以看到，在耗时上面，hive的增长速度较mysql慢。
TEST 2
测试数据：1200W
mysql 引擎： MyISAM（为了加快查询速度）
导入到hive：
1. 计算广东的平均年龄
mysql：select (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz2 where address like '广东%';
用时： 5.642s
hive：select (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as ageAvge from test_sfz2 where address like '广东%';
用时：168.259s
2. 对每个城市的的平均年龄进行从高到低的排序
mysql：select
address, (sum(year(NOW()) - SUBSTRING(borth,1,4))/count(*)) as ageAvge
from test_sfz2 GROUP BY address order by ageAvge desc;
用时：11.964s
hive：select
address, (sum(year('2014-10-01') - SUBSTRING(borth,1,4))/count(*)) as
ageAvge from test_sfz2 GROUP BY address order by ageAvge desc;
用时：311.714s