sql 常用总结
1、Hive中日期函数整理
yyyymmdd和yyyy-mm-dd日期之间的切换
方法1: from_unixtime+ unix_timestamp
--20171205转成2017-12-05
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;
--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual;
方法2: substr + concat
--20171205转成2017-12-05
select concat(substr('20171205',1,4),'-',substr('20171205',5,2),'-',substr('20171205',7,2)) from dual;
--2017-12-05转成20171205
select concat(substr('2017-12-05',1,4),substr('2017-12-05',6,2),substr('2017-12-05',9,2)) from dual;
方法3：
--20171205转成2017-12-05
to_date('20201231','yyyyMMdd')
当前日期所在月份天数
day(last_day('${--date(0,0,-1):yyyy-MM-dd--}'))
日期相减
datediff(to_date('20201231','yyyyMMdd'),to_date('20201230','yyyyMMdd'))
2、常用函数汇总
1、ntile
https://help.aliyun.com/document_detail/158535.html
2、行转列 & 列转行
行转列
eg.表
hive > select * from table_a
user_id order_id
1 123
1 123
1 134
2 145
转换
select user_id,concat_ws(',',collect_list(order_id)) as order_value
from table_a
where user_id is not null
group by user_id
user_id order_value
1 123,123,134
2 145
select user_id,concat_ws(',',collect_set(order_id)) as order_value
from table_a
where user_id is not null
group by user_id
user_id order_value
1 123,134
2 145
总结：使用函数： concat_ws（‘,’,collect_set(column)）
说明：collect_list 不去重，collect_set去重。column数据类型要求string
列转行：
user_id order_value
1 123,134
2 145
select user_id, order_value,order_id from table_a
lateral view explode(split(order_value,',')) num as order_id
where user_id is not null
user_id order_value order_id
1 123,134 123
1 123,134 134
2 145 145
总结：使用数组函数 lateral view explode(split(col,',')) num as col_1
说明： num 自定义（目前没搞懂什么含义） ，where语句写在此函数下面
3、百分位函数
percentile：
percentile(col, p) col是要计算的列（值必须为int类型），p的取值为0-1，若为0.2，那么就是2分位数，依次类推。
percentile_approx：
percentile_approx(col, p)。列为数值类型都可以。
percentile_approx还有一种形式percentile_approx(col, p，B)，参数B控制内存消耗的近似精度，B越大，结果的精度越高。默认值为10000。当col字段中的distinct值的个数小于B时，结果就为准确的百分位数。
percentile_approx(a, array(0.2,0.4,0.6,0.8), 9999) as a
3、常用指令
参考：https://blog.csdn.net/ddydavie/article/details/80667727
基础命令
--1、快速查看表分区
show table_a PARTITIONS
--2、快速查看表分区
show table_a PARTITIONS
--3、显示所有的可用函数，包括运算符、内置函数、自定义函数
show functions;
--4、显示指定函数的描述信息
desc function trim;
--5、显示指定函数的详细信息
desc function extended trim;
--6、显示表结构
desc formatted table_name;
desc table_name;
配置优化
-- 开启任务并行执行
set hive.exec.parallel=true
-- 设置运行内存
set mapreduce.map.memory.mb=1024;
set mapreduce.reduce.memory.mb=1024;
-- 指定队列
set mapreduce.job.queuename=jppkg_high;
-- 动态分区，为了防止一个reduce处理写入一个分区导致速度严重降低，下面需设置为false
-- 默认为true
set hive.optimize.sort.dynamic.partition=false;
-- 设置变量
set hivevar:factor_timedecay=-0.3;
set hivevar:pre_month=${zdt.addDay(-30).format("yyyy-MM-dd")};
set hivevar:pre_date=${zdt.addDay(-1).format("yyyy-MM-dd")};
set hivevar:cur_date=${zdt.format("yyyy-MM-dd")};
-- 添加第三方jar包, 添加临时函数
add jar ***.jar;
-- 压缩输出，ORC默认自带压缩，不需要额外指定,如果使用非ORCFile,则设置如下
hive.exec.compress.output=true
-- 如果一个大文件可以拆分，为防止一个Map读取过大的数据，拖慢整体流程，需设置
hive.hadoop.suports.splittable.combineinputformat
-- 避免因数据倾斜造成的计算效率，默认false
hive.groupby.skewindata
-- 避免因join引起的数据倾斜
hive.optimize.skewjoin
-- map中会做部分聚集操作，效率高，但需要更多内存
hive.map.aggr -- 默认打开
hive.groupby.mapaggr.checkinterval -- 在Map端进行聚合操作的条目数目
-- 当多个group by语句有相同的分组列，则会优化为一个MR任务。默认关闭。
hive.multigroupby.singlemr
-- 自动使用索引，默认不开启，需配合row group index，可以提高计算速度
hive.optimize.index.filter
