狠狠撸

大纲
1. sql基础
2. 分析函数
3. 常用hive udf
4. sql优化

面向群体
有SQL语法基础，了解自助查询平台使用方法的：
1. 数据分析师
2. 产物经理
3. 其它使用自助查询平台进行数据分析的同学

SELECT
column_1,
column_2,
[operation](column_3)
...
FROM table
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number]
sql基础
结构化查询语言(Structured Query Language)

子查询
SELECT
t.ID,
t.name
FROM
(
SELECT
ID,
NAME
FROM DIM.CITY WHERE ID>10
)t
WHERE t.ID>20
sql基础

假设我们有两张表。
1) elective_math_students记录了选修数学课的同学信息
2) elective_english_students记录了选修英语课的同学信息
sql基础_表连接
sql表连接方式
? inner join
? left outer join
? right outer join
? full outer join

Inner join产生的结果集是两表的交集。
sql基础_表连接
inner join
执行sql:
select
A.user_id,
A.user_name,
B.user_id,
B.user_name
from elective_english_students A
inner join
elective_math_students B
on A.user_id=B.user_id;

Left outer join 产生表A的完全集，而B表中匹配的则有值，没
有匹配的则以NULL值填充。
sql基础_表连接
left outer join
执行sql:
select
A.user_id,
A.user_name,
B.user_id,
B.user_name
left outer join
right outer join 产生表B的完全集，而A表中匹配的则有
值，没有匹配的则以NULL值填充。

Full outer join 产生A和B的并集。但是需要注意的是，对于没
有匹配的记录，则会以NULL值填充。
sql基础_表连接
full outer join
执行sql:
select
A.user_id,
A.user_name,
B.user_id,
B.user_name
full outer join
mysql不支持full outer join

sql基础_表union
union 语句
执行sql:
select
user_id,
user_name
from elective_math_students
union
select
user_id,
user_name
from elective_english_students

sql基础_表union
union all 语句
执行sql:
select
user_id,
user_name
from elective_math_students
union all
select
user_id,
user_name
from elective_english_students

...
10007
10008
10009
1
40
42
45
...
sql基础_排序
ORDER BY和SORT BY
ORDER BY全局排序，就是对指定的所有排序键进
行全局排序，使用ORDER BY的查询语句，最后会用
一个Reduce Task来完成全局排序。
SORT BY
SORT BY用于分区内排序，即每个 Reduce任务内排
序
set mapred.reduce.tasks=2;
select id from dim.city sort by id
[order | sort] by column [desc|asc]
desc 为降序，asc 为升序
sort by

sql基础_排序
DISTRIBUTE BY和CLUSTER BY
? distribute by：按照指定的字段或表达式对数据进行划分，输出到
对应的Reduce或者文件中。
? cluster by：除了兼具distribute by的功能，还兼具sort by的排序功
能。

user_id pay_datekey pay_amount
35811758 20150101 232.6
169 20150408 67.8
35811758 20150101 123.5
35811758 20150108 142.6
169 20150501 56.3
169 20150608 19.9
35811758 20150501 84.2
... ... ...
分析函数
假设有一张购买明细表

分析场景
● 查询客户各个日期的历史累积购买金额
● 查询每个客户第一次(首购)或前N次购买记录
● 查询每个客户最后一次购买记录
● 某活动推广后，每天的累积数据
● 其它
分析函数

查询客户每天的历史累积购买金额
userid pay_datekey pay_amount accumulate_pay_amount
169 20150408 67.8 67.80000305175781
169 20150501 56.3 124.10000228881836
169 20150608 19.9 144.00000190734863
35811758 20150101 123.5 123.5
35811758 20150101 232.6 356.1000061035156
35811758 20150108 142.6 498.70001220703125
35811758 20150501 84.2 582.9000091552734
分析函数

查询客户每天的历史累积购买金额
SELECT
userid user_id,
pay_datekey pay_datekey,
pay_amount pay_amount,
SUM(pay_amount) OVER (PARTITION BY userid ORDER BY pay_datekey ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW ) accumulate_pay_amount
FROM order_detail
分析函数

窗口分析函数
OVER(PARTITION BY COLUMN ROWS BETWEEN ..)
ROWS ((CURRENT ROW) | (UNBOUNDED | [num]) PRECEDING) AND
(UNBOUNDED | [num]) FOLLOWING
1. PRECEDING：往前
2. FOLLOWING：往后
3. CURRENT ROW：当前行
4. UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED
FOLLOWING：表示到后面的终点
如果不指定ROWS BETWEEN,默认为从起点到当前值
SUM(pay_amount) OVER (PARTITION BY userid ORDER BY pay_datekey ROWS
BETWEEN 2 PRECEDING AND 0 FOLLOWING)
分析函数

除了可以计算到当前行的sum聚合函数之外，还支持以下
标准聚合函数
● COUNT
● MIN
● MAX
● AVG
比如计算：截止某天历史平均购买交易额、最小购买金额等。
分析函数

序列函数
为每一行加一个序号
1. row_number()
2. rank()
3. dense_rank()
4. percent_rank() 分组内当前行的RANK值-1/分组内总行数-1
5. ntile(n) 将数据集分成n片，返回分片号
6. cume_dist 小于等于当前值的行数和分组内总行数的比值
分析函数

序列函数使用
userid pay_datekey pay_amount row_number rank dense_rank percent_rank ntile
169 20150408 67.8 1 1 1 0.0 1
169 20150501 56.3 2 2 2 0.5 1
169 20150608 19.9 3 3 3 1.0 2
35811758 20150101 123.5 1 1 1 0.0 1
35811758 20150101 232.6 2 1 1 0.0 1
35811758 20150108 142.6 3 3 2 0.667 2
35811758 20150501 84.2 4 4 3 1.0 2
分析函数

SELECT
user_id,
pay_datekey,
pay_amount,
ROW_NUMBER() OVER (PARTITION BY userid ORDER BY pay_datekey ASC)
row_number,
RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) rank,
DENSE_RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) dense_rank,
PERCENT_RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC)
percent_rank,
NTILE(2) OVER (PARTITION BY userid ORDER BY pay_datekey ASC) ntile
FROM order_detail
分析函数

LAG
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值
（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD
与LAG相反,LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值.
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值
（当往下第n行为NULL时候，取默认值，如不指定，则为NULL
FIRST_VALUE
取分组内排序后，第一个值.应用场景，如求第一次购买日期。
LAST_VALUE
取分组内排序后，最后一个值.应用场景，如求第一次购买日期。
分析函数

取第一次购买日期
userid pay_datekey pay_amount first_pay_datekey
169 20150408 67.8 20150408
169 20150501 56.3 20150408
169 20150608 19.9 20150408
35811758 20150101 123.5 20150101
35811758 20150101 232.6 20150101
35811758 20150108 142.6 20150101
35811758 20150501 84.2 20150101
当然这里还可以换成，第一次购买金额、品类等等
分析函数

取第一次购买日期SQL：
SELECT
userid,
pay_datekey,
pay_amount,
FIRST_VALUE(pay_datekey) over (partition by userid ORDER BY
pay_datekey ) first_pay_datekey
FROM order_detail
分析函数

其它
GROUPING SETS,CUBE,ROLLUP
GROUPING SETS(pay_datekey,pay_amount)
==>
select xx ,yy from tb group by pay_datekey
union all
select xx ,yy from tb group by pay_amount

字符串函数
1、函数：regexp_extract(origin_str,regexp,index)
使用说明：本函数将字符串 subject按照pattern正则表达式的规则分组，返回index分组的字符串;
示例：regexp_extract(‘1111_2222_3333_4444’,'_(d+)_(d+)_(d+)',3) -->3333
2、substr(string|binary A, int start, int len)
使用说明：返回从start位置开始(含)向后的len个字符串，如果start为负数则从最右边开始计算位置;
示例：substr(‘1234567’,4,3) → 456 substr(‘1234567’,-3,2) → 56
3、concat(string|binary A, string|binary B...)
使用说明：将各个字符串拼接起来
concat(‘xx’,’yy’,’zz’) --》’xxyyzz’
4、concat_ws(string SEP, string A, string B...)
使用说明：和concat一样，只不过会用分隔符分隔开
concat(‘,’,‘xx’,’yy’,’zz’) --》’xx,yy,zz’
使用示例：
提取stid
SELECT regexp_extract('AgroupBiphoneC23232323_c12Fabtest__sss', 'C([^A-Z]+)', 1) FROM xxTable limit 1
输出结果:23232323_c12
常用hive udf

字符串函数
1、A RLIKE /REGEXP B
使用说明:A中任意字串匹配正则表达式B则返回true
示例：‘wddsds2323’ regexp ‘d+’ --->true
常用hive udf

条件函数
if语句
用法： if(conditional expr, true value, false value)
当表达式expr成立时，取true value，当表达式expr不成立时，取false value
case when 语句
case
when rank_biz_sequence>1 then '老客'
when rank_biz_sequence=1 and rank_all_biz_sequence>1 then '转新'
when rank_biz_sequence=1 and rank_all_biz_sequence=1 then '纯新'
else ‘未知’
end
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
使用说明: When a = b, returns c; when a = d, returns e; else returns f.
常用hive udf

条件函数
nvl(T value, T default_value)
如果value为null，则返回默认值
COALESCE(T v1, T v2, ...)
返回第一个不为null的值，如果都为null 则返回null
boolean isnull( a )
使用说明:Returns true if a is NULL and false otherwise.
boolean isnotnull ( a )
使用说明:Returns true if a is not NULL and false otherwise.
greatest(T v1, T v2, ...)
使用说明:Returns the greatest value of the list of values
least(T v1, T v2, ...)
使用说明:Returns the least value of the list of values
常用hive udf

日期函数
1、int datediff(string enddate, string startdate)
使用说明: Returns the number of days from startdate to enddate
使用示例:
datediff('2009-03-01', '2009-02-27') = 2
datediff('2009-02-27', '2009-03-01') = -2
2、string date_sub(string startdate, int days)
返回从startdate减去days天的日期: date_sub('2008-12-31', 1) = '2008-12-30'
3、string date_add(string startdate, int days)
返回从startdate减去days天的日期: date_sub('2008-12-31', 1) = '2008-12-30'
4、bigint unix_timestamp()
返回当前时间的时间戳，精度为秒
5、string from_unixtime(bigint unixtime, string format)
将时间戳(seconds)转化为格式化的日期;
from_unixtime('1237573801','yyyy-MM-dd HH:mm:ss') = ‘2009-03-20 11:30:01’
from_unixtime('1237573801','yyyy-MM-dd') = ’2009-03-20‘
SELECT from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') from dim.city limit 1
输出：2015-09-17 11:02:24
常用hive udf

常用hive udf
如果不明确输出什么:
http://mobdata.sankuai.com sql工具箱

hive udf 难以实现
如解析字符串
[{"cateID":1,"children":[{"cateID":55,"children":[],"name":"川湘菜"},{"cateID":509,"children":[],"name":"川菜"},{"cateID":528,"
children":[],"name":"小龙虾"},{"cateID":529,"children":[],"name":"夜宵"}],"name":"美食"}]
尝试etl嵌入脚本,将表中的每一行数据交给脚本来处理
http://data.sankuai.com/sweditor/etl/hmart_mobile.deal_frontcate_mappings
http://data.sankuai.com/sweditor/etl/scripts/parse_frontcatemappings.py

1、少用COUNT DISTINCT，数据量小的时候无所谓，数据量大的情况下，由于 COUNT
DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会
导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换。
select count(distinct uuid) from log.mobilelog where dt=’20150501’
====>
select sum(1) from (select uuid from log.mobilelog where dt=’20150501’ group by uuid)t
SQL优化

数据倾斜问题
数据倾斜是Hive sql中对性能影响的一大杀手
症状：
个别任务长时间维持在99%（或100%），查看yarn发现大部分任务的都早早执行完了
，只有少数（通常1个）reduce任务执行特别慢。
导致数据倾斜操作:
GROUP BY, COUNT DISTINCT, join
原因：
key分布不均匀
SQL优化

数据倾斜问题
使用JOIN引起的数据倾斜
如关联键存在大量空值或者某一特殊值，如”NULL”
1. 空值或特殊值单独处理，不参与关联；
2. 空值或特殊值加随机数作为关联键；
如表t1存在大量-1(标识无意义)
case when t1.dealid=-1 then concat(rand(),’xx’) else t1.dealid end = t2.dealid
SQL优化

狠狠撸

Sql培训 (1)

More Related Content

Sql培训 (1)