文章目录
一、数据准备
1.1 创建用户信息表(user_info)并加载数据
1.2 创建用户交易表(user_trade)并加载数据
二、基础查询语句、函数
2.1 select 【列名】 from 【表名】 where 【筛选条件】
2.2 group by
2.3 order by
2.4 将时间戳转化成日期(from_unixtime)
2.5 计算时间间隔(datediff)
2.6 条件函数(case when)
2.7 字符串函数 (substr)
2.8 聚合统计函数
2.9 累计计算窗口函数
2.10 排序窗口函数
2.11 分组窗口函数
2.12 偏移分析窗口函数
三、总结
一、数据准备
1.1 创建用户信息表(user_info)并加载数据
create table if not exists user_info (
user_id string,
user_name string,
sex string,
age int,
city string,
firstactivetime string,
level int,
extra1 string,
extra2 map<string,string>)
row format DELIMITED FIELDS TERMINATED BY 't'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY 'n'
stored as textfile;
其中
1.2 创建用户交易表(user_trade)并加载数据
CREATE TABLE IF NOT EXISTS user_trade(
user_name string,
piece int,
price double,
pay_amount double,
goods_category string,
pay_time bigint)
partitioned by (dt string)
row format delimited fields terminated by 't';
二、基础查询语句、函数
2.1 select 【列名】 from 【表名】 where 【筛选条件】
示例:选出城市在上海,性别为男的5个用户名
select user_name
from user_info
where city='shanghai' and sex='male'
limit 5;
示例:选出在2019年4月19号,购买的商品品类的是food 的用户名、购买数量、支付金额
select user_name,
price,
pay_amount
from user_trade
where dt='2019-04-09' and goods_category='food';
注意,因为user_trade为分区表,where条件中必须对分区字段进行限制,不然会报错。
2.2 group by
GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。
示例:取出2019年1月到4月,每个品类有多少人购买,累计金额是多少
SELECT goods_category,
count(distinct user_name) as user_num,
sum(pay_amount) as total_amount
FROM user_trade
WHERE dt between '2019-01-01' and '2019-04-30'
GROUP BY goods_category;
示例:取出2019年4月份支付金额超过3万元的用户
SELECT user_name,
sum(pay_amount) as total_amount
FROM user_trade
WHERE dt between '2019-04-01' and '2019-04-30'
GROUP BY user_name HAVING sum(pay_amount)>50000;
2.3 order by
ORDER BY 关键字用于对结果集进行排序。
示例:取出2019年4月,支付金额最多的TOP5用户
SELECT user_name,
sum(pay_amount) as total_amount
FROM user_trade
WHERE dt between '2019-04-01' and '2019-04-30'
GROUP BY user_name
ORDER BY total_amount DESC limit 5;
2.4 将时间戳转化成日期(from_unixtime)
SELECT pay_time,
from_unixtime(pay_time,'yyyy-MM-dd hh:mm:ss')
FROM user_trade
WHERE dt='2019-04-09';
这个函数经常在数据分析中用到
from_unixtime(bigint unixtime, string format)
其中format有以下格式:
- yyyy-MM-dd hh:mm:ss
- yyyy-MM-dd hh
- yyyy-MM-dd hh:mm
- yyyyMMdd
当然也存在着把日期转化为时间戳的函数:unix_timestamp(string date)
2.5 计算时间间隔(datediff)
示例:用户的首次激活时间(距今2020年4月8号的时间)
SELECT user_name,
datediff('2019-05-01',to_date(firstactivetime))
FROM user_info
limit 10;
datediff(string enddate, string startdate):结束日期减去开始日期的天数。
当然还有其它常用的时间函数:
date_add(string startdate, int days)
date_sub (string startdate, int days)
2.6 条件函数(case when)
在实际工作环境中,可以说case when的使用频率和select、from、where差不多。比如通过条件定义一个新列等等
case when 有两种写法:
- 搜索case when,好处是每一次假设都可以指定不同的列
case when ... then ...
when ... then ...
when ... then ...
else ... # else可以不写,程序会默认为else null
end
2. 简单case when
case 列名
when 值 then ...
when 值 then ...
when 值 then ...
else ...
end
示例:统计以下四个年龄段20岁以下、20-30岁、30-40岁、40岁以上的用户数
select case when age<20 then '20岁以下'
when age>=20 and age<30 then '20-30岁'
when age>=30 and age<40 then '30-40岁'
else '40岁以上' end,
count(distinct user_id) user_num
from user_info
group by case when age<20 then '20岁以下'
when age>=20 and age<30 then '20-30岁'
when age>=30 and age<40 then '30-40岁'
else '40岁以上' end;
2.7 字符串函数 (substr)
SUBSTR函数是用来截取数据库某一列字段中的一部分。
substr(string A,int start,int len)
如果不指定截取长度,则从起始位一直截取到最后
示例:取出每个月的新增用户
select substr(firstactivetime,1,7) as month,
count(distinct user_id) user_num
from user_info
group by substr(firstactivetime,1,7);
2.8聚合统计函数
SQL中提供的聚合函数可以用来统计、求和、求最值等等。
–COUNT:统计行数量
–SUM:获取单个列的合计值
–AVG:计算某个列的平均值
–MAX:计算列的最大值
–MIN:计算列的最小值
示例:取出用户名为“ELLA”2018年的平均支付金额,以及2018年最大的支付日期和最小支付日期的间隔。
select avg(pay_amount) as avg_amount,
datediff(max(from_unixtime(pay_time,'yyyy-MM-dd')),min(from_unixtime(pay_time,'yyyy-MM-dd')))
from user_trade
where year(dt)='2018' and user_name='ELLA';
2.9 累计计算窗口函数
(1) sum(...)over(...)
SUM 窗口函数返回输入列值或表达式值的和。SUM 函数使用数值并忽略 NULL 值。
在数据分析做报表的时候,经常会用到计算截止某月的累计数值,如下表:
hive中可以利用窗口函数计算得到。
示例一:计算2018年每个月的支付总额和当年累计支付总额
select a.month,a.pay_amount,sum(a.pay_amount) over(order by a.month)from
(select month(dt) month,sum(pay_amount) pay_amount
from user_trade where year(dt)=2018 group by month(dt)) a;
示例二:计算2017-2018年每个月支付总额和当年累计支付总额
select a.year,a.month,a.pay_amount,sum(a.pay_amount) over(partition by a.year,a.month order by a.month)
from
(select year(dt) year,month(dt) month,sum(pay_amount) pay_amount from user_trade where year(dt) in (2017,2018)
group by year(dt),month(dt))a;
其中partition by起到分组的作用
(2) avg(...)over(...)
AVG 窗口函数返回输入表达式值的平均值(算术平均值)。AVG 函数使用数值并忽略 NULL 值。
示例:2018年每个月的近三个月平均支付金额
select a.month,a.pay_amount,avg(a.pay_amount) over (order by a.month rows between 2 preceding and current row)
from
(select month(dt) month,sum(pay_amount) pay_amount from user_trade where year(dt)=2018 group by month(dt))a;
“rows between 2 preceding and current row”该语句来限制计算移动平均的范围,即本行及前两行(近三个月)。
这里把相关函数说明一下:
- OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
- CURRENT ROW:当前行
- n PRECEDING:往前n行数据
- n FOLLOWING:往后n行数据
- UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点
- LAG(col,n,default_val):往前第n行数据
- LEAD(col,n, default_val):往后第n行数据
2.10 排序窗口函数
rank() over(....)
dense_rank() over(....)
row_number() over(....)
为了弄懂这几个函数,我先举个例子:
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表
得到结果:
rank函数:前3名是并列的名次,结果是:1,1,1,4。
dense_rank函数:前3名是并列的名次,结果是:1,1,1,2。
row_number函数:前3名是并列的名次,结果是:1,2,3,4。
注意:这几个函数后面跟着的括号内不需要加任何字段名称。
示例:2019年1月,用户购买商品品类数量的排名
select user_name,count(distinct goods_category),
rank() over(order by count(distinct goods_category)),
dense_rank() over(order by count(distinct goods_category)),
row_number() over( order by count(distinct goods_category))
from user_trade where substr(dt,1,7)='2019-01' group by user_name;
2.11 分组窗口函数
ntile(n) over(...)
把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。
示例:将2019年1月的支付用户,按照支付金额分成5组
2.12 偏移分析窗口函数
- LAG(col,n,default_val) over(...):往前第n行数据
- LEAD(col,n, default_val)over(...):往后第n行数据
在实际应用中,若用到今天和昨天的某个字段差值时,lag和lead函数的应用就显得尤为重要。
示例:支付时间间隔超过100天的用户数
select count(distinct user_name)
from
(select user_name,dt,lead(dt) over(partition by user_name order by dt) lead_dt
from user_trade where dt>'0')a
where a.lead_dt is not null and datediff(a.lead_dt,a.dt)>100;
三、总结
本文结合网上资料总结了工作中常用的函数,仍有其他比较重要的知识点因为篇幅没有涉及,比如表连接:inner join、left join、right join、 full join、 union all 等,当然还存在其它重要的知识点,仍需要在工作过程中不断学习更新。
欢迎关注、点赞、收藏、转发~
欢迎关注、点赞、收藏、转发~
欢迎关注、点赞、收藏、转发~