大数据入门--Hive（四）数据查询

数据查询

语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

WHERE

与SQL类似
扩展RLIKE 支持正则表达式

排序

order by

全局排序，只有一个Reducer

sort by

每个reducer内部进行排序，每个reducer内部有序，全局不一定有序

distribute by

分区字段，控制特定行被输出到指定reducer,在sort by 前出现

cluster by

当distribute by 和sort by 字段一致时，可以用cluster by 代替，但是cluster by只能升序排列，不能指定asc或者desc

函数

函数说明
NVL给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。
CASE WHEN THEN ELSE ENDcase column when val1 then … when val2 then …else …end
case when condition1 then …when condition2 …else …end
CONCAT拼接多列，concat(str1, str2, … strN)
CONCAT_WSCONCAT升级版，CONCAT(separator, [string | array(string)]+)
EXPLODE(col)将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

NVL

假设有如下数据，成绩表，未参加考试者默认为null

张三 100
李四 
王五 80

统计所有同学成绩，如果未参加考试成绩为0

select name,NVL(score,0) as score from test10;

CASE WHEN THEN ELSE END

如下数据，统计各部门男女数量

name dept_id sex
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女

select
    dept_id,
    sum(case sex when '男' then 1 else 0 end) as man_count,
    sum(case sex when '女' then 1 else 0 end) as woman_count
from 
test11 
group by dept_id;

## case when 另一种写法
select 
    dept_id,
    sum(case when sex='男' then 1 else 0 end)as man_count,
    sum(case when sex='女' then 1 else 0 end) as woman_count
from 
    test11
group by 
    dept_id;

CONCAT/CONCAT_WS

继续使用case when 案例数据，需求：将每行数据拼接成如下格式数据，形成一行文本，各个列用,分割。

悟空,A,男
...

select CONCAT(name,',',dept_id,',',sex) from test11;
## 等价于
select CONCAT_WS(',',name,dept_id,sex) from test11;

EXPLODE/LATERAL VIEW

案例：
假设我们有一张表：描述电影信息

movie category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼 2》	战争,动作,灾难

建表语句

create table test12(
movie string,
category array<string>
)row format delimited
fields terminated by '\t'
collection items terminated by ','
lines terminated by '\n';

查询：炸裂单个字段

hive (study)> select explode(category) as category from test12;
OK
category
悬疑
动作
科幻
剧情
悬疑
警匪
动作
心理
剧情
战争
动作
灾难
Time taken: 0.225 seconds, Fetched: 12 row(s)

如果我们想将表数据的电影名称和分类列转行展示，则需要使用侧写表

select 
    movie,
    category_name
from 
    test12 
lateral view explode(category) movie_info_tmp as category_name;

开窗函数

over()
案例数据,订单数据

name orderdate cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

需求：
（1）查询在 2017 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的 cost 按照日期进行累加
（4）查询每个顾客上次的购买时间
（5）查询前 20%时间的订单信息
解题：
（1）查询在 2017 年 4 月份购买过的顾客及总人数

分析：重点在于需要求出2017年4月份购买的客户总数是聚合除重值，而购买过的客户是具体客户。如果我们先分组出重可以得到具体名单，然后使用over()整体开窗，得到总人数。

select 
    name,
    count(name) over() as total_count
from 
test13
where orderdate between '2017-04-01' and '2017-04-30'
group by name;

（2）查询顾客的购买明细及月购买总额

分析：当前表就是顾客的购买明细，月购买总额即每个顾客每月的购买累计。开窗是按照每个顾客每月进行开窗，求累计值

### 因为都是在同一年，所以可以month(orderdate)分区
select 
    name,orderdate,cost
    ,sum(cost) over(partition by month(orderdate)) as month_cost_sum
from test13;
## 标准答案
select 
    name,orderdate,cost
    ,sum(cost) over(partition by substring(orderdate,1,7)) as month_cost_sum
from test13;

（3）上述的场景, 将每个顾客的 cost 按照日期进行累加

select 
    name,orderdate,cost
    ,sum(cost) over(partition by name order by orderdate) as cost_sum
from test13;

延伸

select 
    *,
    --所有行相加
    sum(cost) over() as sample1,
    
    --按 name 分组，组内数据相加
    sum(cost) over(partition by name) as sample2,
    
    --按 name分组，组内数据累加
    sum(cost) over(partition by name order by orderdate) as sample3,
    
    --和 sample3 一样,由起点到当前行的聚合
    sum(cost) over(partition by name order by orderdate rows between 
    UNBOUNDED PRECEDING and current row ) as sample4 ,
    
    --当前行和前面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between 1 
    PRECEDING and current row) as sample5,
    
    --当前行和前边一行及后面一行
    sum(cost) over(partition by name order by orderdate rows between 1 
    PRECEDING AND 1 FOLLOWING ) as sample6,
    
    --当前行及后面所有行
    sum(cost) over(partition by name order by orderdate rows between current 
    row and UNBOUNDED FOLLOWING ) as sample7 
from
test13;

（4）查询每个顾客上次的购买时间

select 
    name,
    orderdate,
    lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate) as last_orderdate
    from 
test13;

（5）查询前 20%时间的订单信息

select * from (select 
    name,orderdate,cost 
    ,ntile(5) over(order by orderdate) sort
from test13)t1
where sort = 1;

Rank函数

函数说明
rank()排序相同时会出现重复，总数不变
dense_rank()排序相同时会出现重复，总数会减少
row_number()按顺序排列

数据：学科成绩表

name subject score
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78

分别对比一下上个函数的效果
rank()：排序相同时出现重复，总数不变。
成绩并列第6，没有第7。总数12，总数量保持不变。

hive (study)> select *,rank() over(order by score) from test14;
-------------------------
test14.name     test14.subject  test14.score    rank_window_0
大海    数学    56.0    1
宋宋    语文    64.0    2
婷婷    语文    65.0    3
孙悟空  英语    68.0    4
婷婷    英语    78.0    5
宋宋    英语    84.0    6
大海    英语    84.0    6
婷婷    数学    85.0    8
宋宋    数学    86.0    9
孙悟空  语文    87.0    10
大海    语文    94.0    11
孙悟空  数学    95.0    12
Time taken: 18.422 seconds, Fetched: 12 row(s)

dense_rank()：排序相同时出现重复，总数量减少。
并列第6，之后继续顺序排序，总数量由于存在并列，总数量减少。

hive (study)> select *,dense_rank() over(order by score) from test14;
-----------------------
test14.name     test14.subject  test14.score    dense_rank_window_0
大海    数学    56.0    1
宋宋    语文    64.0    2
婷婷    语文    65.0    3
孙悟空  英语    68.0    4
婷婷    英语    78.0    5
宋宋    英语    84.0    6
大海    英语    84.0    6
婷婷    数学    85.0    7
宋宋    数学    86.0    8
孙悟空  语文    87.0    9
大海    语文    94.0    10
孙悟空  数学    95.0    11

row_number()：顺序排列，不重复。
不会出现并列。

hive (study)> select *,row_number() over(order by score) from test14;
------------------
test14.name     test14.subject  test14.score    row_number_window_0
大海    数学    56.0    1
宋宋    语文    64.0    2
婷婷    语文    65.0    3
孙悟空  英语    68.0    4
婷婷    英语    78.0    5
宋宋    英语    84.0    6
大海    英语    84.0    7
婷婷    数学    85.0    8
宋宋    数学    86.0    9
孙悟空  语文    87.0    10
大海    语文    94.0    11
孙悟空  数学    95.0    12

需求：
计算每门学科成绩排名。

分析：每个学科，进行排名

select 
    name,subject,score,
    rank() over(partition by subject order by score) as rank 
from test14;

其他常用函数

日期函数

函数说明案例
unix_timestamp返回当前时间的unix时间戳 ，已弃用。用current_timestamp替代select unix_timestamp();
select unix_timestamp(“2020-10-28”,‘yyyy-MM-dd’);
from_unixtime时间戳转日期select from_unixtime(1603843200);
current_date获取当前日期select current_date()；
current_timestamp获取当前日期和时间select current_timestamp;
to_date抽取日期部分select to_date(‘2020-10-28 12:12:12’);
year抽取日期的年部分select year(‘2020-10-28 12:12:12’);
month抽取日期的月部分select month(‘2020-10-28 12:12:12’);
day抽取日期的日部分select day(‘2020-10-28 12:12:12’);
hour抽取日期的小时select hour(‘2020-10-28 12:12:12’);
minute抽取日期的分钟select minute(‘2020-10-28 12:13:14’);
second抽取日期的秒select second(‘2020-10-28 12:13:14’);
weekofyear指定日期是当前年份的第几周select weekofyear(‘2020-10-28 12:13:14’);
dayofmonth指定日期是当前月份的第几天select dayofmonth(‘2020-10-28 12:13:14’);
months_between两个日期间的月份select months_between(‘2020-04-01’,‘2020-10-28’);
add_months日期加减月select add_months(‘2020-10-28’,-3);
datediff两个日期相差的天数select datediff(‘2020-11-04’,‘2020-10-28’);
date_add日期加天数select date_add(‘2020-10-28’,4);
date_sub日期减天数select date_sub(‘2020-10-28’,-4);
last_day日期的当月的最后一天select last_day(‘2020-02-30’);
date_format格式化日期select date_format(‘2020-10-28 12:12:12’,‘yyyy/MM/dd HH:mm:ss’);

数值函数

函数说明案例
round四舍五入select round(3.14);
select round(3.54);
ceil向上取整select ceil(3.14);
floor向下取整select floor(3.14);

字符串操作函数

函数说明案例
upper转大写select upper(‘low’);
lower转小写select lower(‘low’);
length长度select length(“atguigu”);
trim前后去空格select trim(" atguigu ");
lpad向左补齐，到指定长度select lpad(‘atguigu’,9,‘g’);
rpad向右补齐，到指定长度select rpad(‘atguigu’,9,‘g’);
regexp_replace使用正则表达式匹配目标字符串，匹配成功后替换！SELECT regexp_replace(‘2020/10/25’, ‘/’, ‘-’);

集合函数

函数说明案例
size集合中元素的个数select size(friends) from test3;
map_keys返回map中的keyselect map_keys(children) from test3;
map_values返回map中的valueselect map_values(children) from test3;
array_contains判断array中是否包含某个元素select array_contains(friends,‘bingbing’) from test3;
sort_array将array中的元素排序select sort_array(friends) from test3;
grouping sets多维分析

grouping sets
可以理解为将多个group by 的SQL写法进行优化

select col1,col2,count(*) as count from tab1 group by col1,col2
union 
select col1,null,count(*) as count from tab1 group by col1;

## 等价于
select col1,col2,count(*) as count from tab1  group by col1,col2
grouping sets((col1,col2),(col1));
版权声明：本文为qq_18675693原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。