本文共 6942 字，大约阅读时间需要 23 分钟。

SQL

SQL基础语法

基本含义（与excel对应）

库（文件夹）

表（Excel名称） 字段（Excel第一行，包含字段名，字段数据类型、注释） 分区字段（sheet表，一般是日期，相当于在查询的时候提升速度）(必须限制分区，否则hive会报错)

数据来源

数据地图（查寻需要的表）

KwaiBI(查询平台)

Hive SQL查询语法

select[all | distinct] select_expr,…

from [where] [group by] [having] [order by] [limit [offset,]rows]

单表查询语法和使用

1,select

select a+b as 'cnt’

from where

2,group by*

group by后，必须包含group by的字段，剩余内容为分组的计算结果

select pic, count(1) as cnt

from where p_date = having count(1)>1000

3,聚合函数

count(*) :包括null

count(expr):不包括null count(DISTINCT expr):去重后行数，不包括null

sum(col)

sum(DISTINCT col)：去重求和

avg(col),avg(DISTINCT col)：去重求平均

collect_set(col)：拼成去重数组

在hive中求出一个数据表中在某天内首次登陆的人；

select a.idfrom (select id,collect_set(time) as t from t_action_login where time<='20150906' group by id) as a where size(a.t)=1 and a.t[0]='20150906';

123@163.com | [“20150620”,“20150619”] |

| abc@163.com | [“20150816”] | | cde@qq.com | [“20150606”,“20150608”,“20150607”,“20150609”,“20150613”,“20150610”,“20150616”,“20150615”] |

collect_list(col)：拼成数组

4.行转列

统计20200701北京和成都活跃用户数

法1:group by + count(1)

select 	city,p_date,count(1) as cnt	from biaowhere	p_date = '20200701' and city in ('北京','成都')group by city,p_date

法2:更方便常用 case where

select 	p_date,	sum(if(city = '北京',1,0)) as 'beijing_user_cnt',	sum(if(city = '成都'，1,0)) as 'chengdu_user_cnt'from biao

5,join

left join ,right join, inner join, full join

6,Hive分析/窗口函数

聚合函数：将多行数据按照规则聚集为一行，不能显示聚合前的数据

sum(),avg(),max() 窗口函数： 既显示聚集前的数据，又能显示聚集后的数据，一般用于olap分析

使用方式：

聚合函数（row_number/sum/min/max）over(partition by 子句order by 子句desc window子句)

7,Hive SQL怎么写

数据裁剪-维度关联-聚合窗口聚合-结果过滤排序

7.1找出5月新用户数>10w的日期

数据裁剪：between 限定日期，选择新用户

聚合：按天group by,计算新用户数

结果过滤：对于聚合结果having 过滤出符合条件的日期

7.2找出7.2号新客，在7.3-7.10仍活跃的用户数

数据裁剪1：限定7.2号且为新客

数据裁剪2：7.3-7.10的用户 维度关联：以用户ID为节点进行链接 聚合：按照时间进行分组计数

8,开发限制和建议

1，提前进行分区，数据过滤和列裁剪，减少每个阶段的数据量

2，尽量把对每张表的操作写成子查询，只select exr,不能select * 3，需要group by(distinct value)时，尽可能先转化为group by ，再sum(1)# distinct相当于从头排序，工作量很大 4, 如果需要排序，尽量不要对整表排，取排序字段大于某个书再进行排序，或者取limit 5，如果进行查询分析的话，可以用spark进行加速，不一定使用mr

SQL常用函数

优秀SQLer的进阶

1、SQL基本概念和基本语法

2、常用函数介绍与应用+主题场景分析与实践 3、SQL执行计划+分析优化+计算引擎

常用函数介绍与应用

一、基础数据类型

布尔型：boolean

整型：int\bigint\ 浮点型：单精度float\精度double\decimal 字符串型：string 日期：timestamp(整数或者字符串)、DATE（日期2018-08-23）

二、复杂数据类型

STRUCT:可以通过点符号来访问元素内容。如，STRUCT(first STRING,last STRING),那么第一个元素可以通过字段名.first来引用

struct(‘John’,‘Doe’)

MAP:键值对，字段名[‘first’]

map(‘first’,‘John’,‘last’,‘Doe’)

ARRAY:变量集合，可通过名[1]进行引用

ARRAY(‘John’,‘Doe’)

三、函数分类

• show functions:显示当前对话有多少函数可用；
• desc function concat:显示函数的表述信息；
• desc function extended concat:显示函数的扩展描述信息；

四、简单函数

数学函数：

四舍五入：round(2.5)、round(0.5002,2) 向下取整：float(6.10) 大于该值的最小整数：ceil(6) 取随机数：rand(2)/rand() 平方根：sqrt(2) 绝对值：abs(-1) 取模：pmod(4,2) 相反值：negetive(2) 最大值：greatest(2,3,6,7) 最小值：least(2,3,6,7)

场景应用一：借助rand()解决数据倾斜问题(有一部分user_id缺失)

on a.user_id = b.user_id -> on if(nvl(a.user_id,0)>0,a.user_id,concat(‘hive’,rand*())) = b.user_id 场景应用二：借助取模pmod()函数，实现数据分区

五、日期函数

时间戳转换成日期from_unixtime()

获取当时日期时间戳unix_timestamp()/unix_timestamp(‘2020-08-07 13:01:03’) 日期时间转化成日期to_date() 获取日期中年/月/日：year()/month()/day() 获取日期中时/分/秒：hour()/minute()/second() 日期转周：weekofyear() 日期比较：datediff() 日期增加：date_add() 日期减少：date_sub() 当前日期：current_date() 增加月份：add_months()

场景应用一：动态时间获取，查看2天前数据

select count(1)from sdhjdu awhere a.p_date = replace(date_sub(current_date,2),'-','')

场景应用二：日期格式转化 20200820 -> 2020-08-20

select from_unixtime(unix_timestamp('20200820','yyyymmdd'),'yyyy-mm-dd')

六、条件函数

if\nvl(将空值转化为实际值)\coalesce（返回第一个非空值）\case when\isnull\isnotnull

场景应用一：根据只进行打标，及格or不及格

if(score >= 60,‘及格’，‘不及格’)

场景应用二：数据处理-空值处理，返回第一个非空值

coalesce(col1,col2,col3)

场景应用三：数据清洗-过滤null值

select * from .... a where a.pdate = 20200820 and a.photo_tag is not null

七、字符函数

字符拼接concat()

指定拼接符拼接concat_ws 子字符位置instr() 字符长度length() 转大小写upper()/lower() 字符补齐lpad() 去空格trim() 取出url特定值parse_url() 正则截取regexp_extract() 正则替换regesp_replace() 重复输出repeat() 反转输出reverse() 字符拆分split() 字符截取substr() 行转列explode(array(or map)) collect_set() collect_list()

场景应用一：取字符串中的一部分

substr(‘abcdefg’,2,3)

场景应用二：将字符按‘,’进行拆分，形成数组，取第三位

split(‘a,b, c,d’ , ‘,’)[2]. c

场景应用三：列转行，将多列转成一行 ，形成一个列表

select id,collect_set(time) as t from t_action_login where time<='20150906' group by id

场景应用四：Json格式操作

一层key: get_json_object(json , ‘$.key{1}^{\prime })二层key:ge{t}_{j}so{n}_{o}bject(json{,}^{\prime }$.key1.key2’)

八、类型转换函数

转换成二进制；binary(‘4’). . 强制类型转换：cast(‘1’ as BIGINT)

场景应用一：将string类型转换为二进制

将string转换成二进制：select binary(‘kexiong’)

场景应用二：严格模式下，不同类型字段之间join

On cast(column1 as bigint) = column2

九：聚合函数

场景应用一：count()使用场景

count(*) ->统计行数，包括null

count(col1) ->col字段非空值个数 count(distinct col1). ->col字段非空值的个数

场景应用二：sum()

sum(col) sum(distinct col)

十、特殊函数-窗口/分析函数

使用场景：用于分区排序、动态Group by,Top N,层次查询

窗口函数：可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。 分析函数： RANK()会跳位(跳过重复1，1，3)、 ROW_NUMBER()连续、 DENSE_RANK()多位连接（1，1，2）、 NTILE(n)分成n组， FIRST_VALUE()分组内取第一个值、

Oracle提供了2个额外的函数：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3

个月的销售平均值

LAST_VALUE()分组内取第一个值、

LAG()为向前

每次显示当月的销售额和上个月的销售额

Oracle有lag函数让我们来比较2条记录

LEAD()为向后

场景应用二：计算累计值、动态累加

select month,	sum(tot_sales) month_sales,    sum(sum(tot_sales)) over(order by month rows between unbounded preceding and current row) current_total_salesfrom ordersgroup by month;

统计1-12月的累积销量，即1月为1月份的值，2月为1-2月份值的和，三月为1-3月份的和，12月为1-12月份值的和

十、特殊函数-表生成函数

行转列：lateralView()\explode函数

一行，单个id有多个标签，进行行转列，多行，同一个id对应多个标签

Tips:lateral view用于和json_tuple,parse_url_tuple,split,explode等UDTF一起使用，能够将一行数据拆成多行数据，在此基础上对拆分后数据进行聚合

十一、集合函数

集合查找函数：find_in_set()

求map的长度：size(Map<K.V>) 返回map中多有key：map_keys(Map) 返回map中的所有value：map_values(Map) 数组是否包含值：array_contains(Array<>,value) 按自然顺序对数组进行排序并返回：sort_array(Array)

十二、自定义函数

类型：UDF(1:1)/UDTF(1:N)/UDAF(N:1)

流程：定义一个java类，继承实现相应类型与方法，打包jar包，Hive执行add jar(add jar/home/jar/function.jar),hive执行创建模版函数（create temporary function big than as ‘com.udf.udftest’),hql中使用

具体场景分析

1、

明确型问题 探索型问题 2、 分析模型设计（粒度/指标主题数据） 3、 选择数据域和数据表 4、 命题探索和结论产出

HIVE执行计划与SQL优化

执行计划

执行计划能做什么

• 帮助定位问题
• 辅助SQL优化
• Bug fix

基本SQL的MR实现原理（map\reduce）

MAP：生成k-v对

REDUCE：输入阶段所有的key都是排好序的

执行计划的生成过程

语法分析

生成逻辑计划 逻辑优化 生成物理计划 物理优化 执行优化后的Task Tree

如何查看执行计划

语法：

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION | VECTORIZATION | ANALYZE]（SQL语句） QUERY

查询会转换成Stage的DAG图

SQL优化

目标：

加快SQL执行 减少资源使用

方面：

架构方面 业务方面

数据倾斜（Reduce长尾）

现象：由于数据分布不均匀，大量数据集中在某些特定key上，造成数据热点。Reduce进度99%，执行时间很长。

原因：Map-Reduce执行时，会按照key hash分发到对应的reduce task，当存在数据热点时，就会导致某些reduce task处理的数据量远远超过其他task（几倍甚至几十倍），最终表现为少量reduce task执行长尾，任务整体进度长时间卡在99%或100%。 场景：常见于Join/Group By/Count Dinstinct中，或存在大量热点key值

处理数据倾斜的核心原则：

• 避免大量的Key被分发到同一个Reducer上

数据倾斜优化方法：

架构上： 业务上： 1、过滤掉不关心的热点key值，如null、空值、0等

select * fromon a.user_id >0

2、预先统计出热点的key值，对其进行特殊处理，如打散key值

select * from test a left outer join user bon case when a.user_id = 0 then bigint(-10000*rand()) else a.user_id end = b.user_id;

3、使用distribute by rand()通过随机值进行分区而不是通过key的一个哈希值进行划分，很多情况下可以解决。

取分组首末条记录，数据量特别巨大的情况

优化：使用min、man取代row_number,实现在map阶段进行了数据聚合，reduce计算数据量会大大减小；

使用struct类型，取最小值时同时保存其他字段

转载地址：http://fogwi.baihongyu.com/