Hive支持的传统关系型数据库语法

概述

Hive支持如下传统关系型数据库语法：

• Grouping
• EXCEPT、INTERSECT

Grouping

语法简介：

• 当Group by语句带with rollup/cube选项时，Grouping才有意义。
• CUBE生成的结果集显示了所选列中值的所有组合的聚合。
• ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。
• Grouping：当用CUBE或ROLLUP运算符添加行时，附加的列输出值为1；当所添加的行不是由CUBE或ROLLUP产生时，附加列值为0。

例如，Hive中有一张表“table_test”，表结构如下所示：

+----------------+-------------------+--+
| table_test.id  | table_test.value  |
+----------------+-------------------+--+
| 1              | 10                |
| 1              | 15                |
| 2              | 20                |
| 2              | 5                 |
| 2              | 13                |
+----------------+-------------------+--+

执行如下语句：

select id,grouping(id),sum(value) from table_test group by id with rollup;

得到如下结果：

+-------+-----------------+------+--+
|  id   | groupingresult  | sum  |
+-------+-----------------+------+--+
| 1     | 0               | 25   |
| NULL  | 1               | 63   |
| 2     | 0               | 38   |
+-------+-----------------+------+--+

EXCEPT、INTERSECT

语法简介

• EXCEPT返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。
• INTERSECT返回两个结果集的交集（即两个查询都返回的所有非重复值）。

例如，Hive中有两张表“test_table1”、“test_table2”。

“test_table1”表结构如下所示：

+-----------------+--+
| test_table1.id  |
+-----------------+--+
| 1               |
| 2               |
| 3               |
| 4               |
+-----------------+--+

“test_table2”表结构如下所示：

+-----------------+--+
| test_table2.id  |
+-----------------+--+
| 2               |
| 3               |
| 4               |
| 5               |
+-----------------+--+

• 执行如下的EXCEPT语句：

  select id from test_table1 except select id from test_table2;

  显示如下结果：

  +--------------+--+
  | _alias_0.id  |
  +--------------+--+
  | 1            |
  +--------------+--+

• 执行INTERSECT语句：

  select id from test_table1 intersect select id from test_table2;

  显示如下结果：

  +--------------+--+
  | _alias_0.id  |
  +--------------+--+
  | 2            |
  | 3            |
  | 4            |
  +--------------+--+