HiveQL Select-Group By



  • Hive 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,姓名,薪水,指定和部门字段。生成查询以检索每个部门的员工人数。
    
    +------+--------------+-------------+-------------------+--------+ 
    | 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.hive.jdbc.HiveDriver";
       
       public static void main(String[] args) throws SQLException {
          try {
              // Register driver and create driver instance
             Class.forName(driverName);
             
             // get connection
             Connection con = DriverManager.
             getConnection("jdbc:hive2://localhost:10000/userdb", "", "");
             
             // create statement
             Statement stmt = con.createStatement();
             
             // execute statement
             Resultset res = stmt.execute(“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();
          } catch (Exception e) {
             System.out.println(e.getMessage());
          }     
       }
    }
    
    
    将程序保存在名为HiveQLGroupBy.java的文件中。使用以下命令来编译和执行该程序。
    
    $ javac HiveQLGroupBy.java
    $ java HiveQLGroupBy
    
    
    输出:
    
     Dept     Count(*)
     Admin       1
     PR          2
     TP          3