hive获取月份_《一文掌握工作中常用的hive查询》

最新推荐文章于 2023-12-26 15:40:51 发布

weixin_39924584

最新推荐文章于 2023-12-26 15:40:51 发布

阅读量834

点赞数

文章标签： hive获取月份

文章目录

一、数据准备
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 等，当然还存在其它重要的知识点，仍需要在工作过程中不断学习更新。

欢迎关注、点赞、收藏、转发~

weixin_39924584

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
hive获取月份_《一文掌握工作中常用的hive查询》

文章目录一、数据准备 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 计算...
复制链接

扫一扫