hive 子查询特别分析

布满荆棘的人生 2022-09-19 04:26 7阅读 0赞

http://blog.csdn.net/ls3648098/article/details/9630357

Hive只在FROM字句支持子查询。子查询必须给一个名字，因为每个表在FROM字句必须有一个名字。子查询的查询列表的列，必须有唯一的名字。子查询的查询列表，在外面的查询是可用的，就像表的列。子查询也可以一个UNION查询表达式.Hive支持任意层次的子查询。

Hive只支持在FROM子句中使用子查询，子查询必须有名字，并且列必须唯一：SELECT ... FROM(subquery) name ...

确认下是否一定要求列必须唯一？

建表语句：

create table  tb\_in\_base

(

id  bigint,

devid bigint,

devname string

) partitioned by (job\_time bigint) row format delimited fields terminated by ',';

create table  tb\_in\_up

(

id  bigint,

devid bigint,

devname string

) partitioned by (job\_time bigint) row format delimited fields terminated by ',';

场景一：单表子查询没有指定表别名

语句：select \* from (select id,devid,job\_time from tb\_in\_base) ;

执行过程：

![0F18449FE2824513AB79D2807E1CF8DF][]

提示需要指定子查询源。

加上表别名：

语句：select \* from (select id,devid,job\_time from tb\_in\_base) a;

执行过程：

![8D609DD94B4A4892863BEBCFD33187D8][]

加了表别名后可以正常输出子查询中的数据。

结果分析：在hive中若有子查询必须指定子查询的表别名

场景二：单表查询外围字段比子查询少一个

语句： select id,devid from (select id,devid,job\_time from tb\_in\_base) a;

执行过程：

![4CC0B09DAD2748BCB72C45D2D461127E][]

结果分析：输出外围指定字段的数据可以输出 。

场景三：两张表进行union all

语句：

select a.id,a.devid from (select a.id,a.devid,a.job\_time from tb\_in\_base a union all select b.id,b.devid,b.job\_time from tb\_in\_up b) a;

执行过程：

![7A73C37FBE904D24A546243845952F9B][]

结果分析：两张表进行union all 取相同的字段名称，可正常输出指定数据内容，且结果为两张表的结果集

场景四：两张表在子查询中进行union

语句：

select a.id,a.devid from (select a.id,a.devid,a.job\_time from tb\_in\_base a union  select b.id,b.devid,b.job\_time from tb\_in\_up b) a;

执行过程：

![0FD663C510CD4400B6C4F47C02B96927][]

结果分析：hive 不支持union

网上相关内容： [http://www.cnblogs.com/ggjucheng/archive/2013/01/15/2861574.html][http_www.cnblogs.com_ggjucheng_archive_2013_01_15_2861574.html]

场景五：外围使用count、sum 统计id

语句：

select count(a.id),sum(a.id)  from (select a.id,a.devid,a.job\_time from tb\_in\_base a union all select b.id,b.devid,b.job\_time from tb\_in\_up b) a;

执行过程：

![9B0114F688434293964B7B8C0F60752C][]

结果分析：两表在子查询中直接进行union all ，外围查询可以使用count、sum 等聚合函数

场景六：使用union all关联两张表，同时使用count、sum 、max等 聚合函数

![CD75455BCCF84CFDB6207BC210DEB436][]

结果分析：union all 时不能使用count、sum 、max等 聚合函数，单表可以进行聚合函数使用，如下图：

![62441C2C72F44630A058D740379432CD][]

场景七：left join 是否可以使用max、count、sum 等函数

语句：

select max(a.id),min(b.id),sum(a.job\_time),count(a.id) from tb\_in\_base a join tb\_in\_up b on (a.id=b.id);

执行过程：

![EA712F3F59A1420097E8F8C45D4289E6][]

![2B88D5D266C44E63AD999F1F4112CADD][]

结果分析：在left join 中可以使用max、count等聚合函数。

总结分析

1. 子查询相当于表名，使用 from 关键字需要指定真实表名或表别名。

2. hive 不支持union ，只支持union all

3. 子查询中使用union all 时，在子查询里不能使用count、sum 等 聚合函数

4. 两表直接进行union all 可以使用count、sum 等聚合函数

5. 两张表进行union all 取相同的字段名称，可正常输出指定数据内容，且结果为两张表的结果集

[0F18449FE2824513AB79D2807E1CF8DF]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/0F18449FE2824513AB79D2807E1CF8DF
[8D609DD94B4A4892863BEBCFD33187D8]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/8D609DD94B4A4892863BEBCFD33187D8
[4CC0B09DAD2748BCB72C45D2D461127E]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/4CC0B09DAD2748BCB72C45D2D461127E
[7A73C37FBE904D24A546243845952F9B]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/7A73C37FBE904D24A546243845952F9B
[0FD663C510CD4400B6C4F47C02B96927]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/0FD663C510CD4400B6C4F47C02B96927
[http_www.cnblogs.com_ggjucheng_archive_2013_01_15_2861574.html]: http://www.cnblogs.com/ggjucheng/archive/2013/01/15/2861574.html
[9B0114F688434293964B7B8C0F60752C]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/9B0114F688434293964B7B8C0F60752C
[CD75455BCCF84CFDB6207BC210DEB436]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/CD75455BCCF84CFDB6207BC210DEB436
[62441C2C72F44630A058D740379432CD]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/62441C2C72F44630A058D740379432CD
[EA712F3F59A1420097E8F8C45D4289E6]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/EA712F3F59A1420097E8F8C45D4289E6
[2B88D5D266C44E63AD999F1F4112CADD]: http://note.youdao.com/yws/public/resource/54f2d030a30d9a3b677b90df6f550efc/2B88D5D266C44E63AD999F1F4112CADD