# jk-hive-sql-sample

**Repository Path**: hgkdt/jk-hive-sql-sample

## Basic Information

- **Project Name**: jk-hive-sql-sample
- **Description**: HQL练习
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-08-06
- **Last Updated**: 2021-08-06

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# jk-hive-sql-sample

#### 介绍
HQL练习

#### HQL
1、展示电影ID为2116这部电影各年龄端的平均影评分

- sql
  ~~~roomsql
  select
    u.age,
	avg(t.rate)
  from
    hive_sql_test1.t_rating t
  join hive_sql_test1.t_user u on
    t.userid = u.userid
  where
    t.movieid = 2116
  group by
    u.age
  ~~~ 
- 执行结果：
  
  ![img_1.png](img_1.png)
  
2、找出男性评分最高且评分次数超过50次的10部电影，展示电影名，平均影评分和评分次数

- sql
  ```roomsql
  select 
    n.sex, 
    m.moviename, 
    n.avgrate, 
    n.total 
  from 
    hive_sql_test1.t_movie m 
  join ( 
    select 
        collect_set(u.sex)[0] as sex,
        r.movieid as movieid,
        avg(r.rate) as avgrate,
        count(1) as total 
    from 
        hive_sql_test1.t_rating r 
    join hive_sql_test1.t_user u on 
        r.userid = u.userid 
    where 
        u.sex = 'M' 
    group by 
        r.movieid 
    HAVING 
        total >= 50 
    order by 
        avgrate desc 
    limit 10 ) n on 
        m.movieid = n.movieid
  ```
- 执行结果
  
  ![img_2.png](img_2.png)
  
3、找出影评次数最多的女士所给出最高分的10部电影的平均影评分，展示电影名和平均影评分

- sql
   ```roomsql
   select 
        collect_set(m.moviename)[0] as moviename,
        AVG(r2.rate) as avgrate
    from
        hive_sql_test1.t_rating r2
    join (
        select
            DISTINCT r1.movieid,
            r1.rate
        from
            hive_sql_test1.t_rating r1
        join (
            select
                r.userid as userid ,
                count(r.movieid) as total
            from
                hive_sql_test1.t_rating r
            join hive_sql_test1.t_user u on
                r.userid = u.userid
            where
                u.sex = 'F'
            group by
                r.userid
            order by
                total desc limit 1
            ) tmp1 on
            r1.userid = tmp1.userid
            -- where r1.rate = 5
        order by r1.rate desc
        limit 10
        ) tmp2 on
        r2.movieid = tmp2.movieid
    join hive_sql_test1.t_movie m on
        r2.movieid = m.movieid
    group by
        r2.movieid
    order by
        avgrate desc
   ```
- 执行结果
  
  ![img.png](img.png)