转载

HiveQL Select 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
   
正文到此结束
Loading...