HiveQL Select Group By

广告位

本章介绍了SELECT语句的GROUP BY子句。GROUP BY子句用于分类所有记录结果的特定集合列。它被用…

本章介绍了SELECT语句的GROUP BY子句。GROUP BY子句用于分类所有记录结果的特定集合列。它被用来查询一组记录。

语法

GROUP BY子句的语法如下:

  SELECT [ALL | DISTINCT] select_expr, select_expr, ...   FROM table_reference   [WHERE where_condition]   [GROUP BY col_list]   [HAVING having_condition]   [ORDER BY col_list]]   [LIMIT number];

示例

让我们以SELECT… GROUP BY子句为例。假设员工表有如下Id, Name, Salary, Designation, 和 Dept字段。产生一个查询以检索每个部门的员工数量。

  +------+--------------+-------------+-------------------+--------+   | ID   | Name         | Salary      | Designation       | Dept   |  +------+--------------+-------------+-------------------+--------+   |1201  | Gopal        | 45000       | Technical manager | TP     |   |1202  | Manisha      | 45000       | Proofreader       | PR     |   |1203  | Masthanvali  | 40000       | Technical writer  | TP     |   |1204  | Krian        | 45000       | Proofreader       | PR     |   |1205  | Kranthi      | 30000       | Op Admin          | Admin  |  +------+--------------+-------------+-------------------+--------+

下面使用上述业务情景查询检索员工的详细信息。

  hive> SELECT Dept,count(*) FROM employee GROUP BY DEPT;

成功执行查询后,能看到以下回应:

  +------+--------------+   | Dept | Count(*)     |   +------+--------------+   |Admin |    1         |   |PR    |    2         |   |TP    |    3         |   +------+--------------+

JDBC 程序

下面给出的是JDBC程序应用对给定的GROUP BY子句例子。

  import java.sql.SQLException;  import java.sql.Connection;  import java.sql.ResultSet;  import java.sql.Statement;  import java.sql.DriverManager;    public class HiveQLGroupBy {     private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";          public static void main(String[] args) throws SQLException {             // Register driver and create driver instance        Class.forName(driverName);                // get connection        Connection con = DriverManager.        getConnection("jdbc:hive://localhost:10000/userdb", "", "");                // create statement        Statement stmt = con.createStatement();                // execute statement        Resultset res = stmt.executeQuery(“SELECT Dept,count(*)  + FROM employee GROUP BY DEPT; ”);        System.out.println(" Dept t count(*)");                while (res.next()) {           System.out.println(res.getString(1) + " " + res.getInt(2));         }        con.close();     }  }

保存程序在一个名为HiveQLGroupBy.java文件。使用下面的命令来编译并执行这个程序。

  $ javac HiveQLGroupBy.java  $ java HiveQLGroupBy

输出:

   Dept     Count(*)   Admin       1   PR          2   TP          3

关于作者: 大数据教程学习

为您推荐