# week4-homework

**Repository Path**: zero-night-01/week4-homework

## Basic Information

- **Project Name**: week4-homework
- **Description**: No description available
- **Primary Language**: SQL
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-03-27
- **Last Updated**: 2022-03-27

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# week4-homework

#### 作业说明

`Hive` 所用文件已存放于 `HDFS` 上的 `/user/fuwuchen/week4` 目录，运行结果均来自集群。

准备工作

```sql
create database fuwuchen;
use fuwuchen;
CREATE EXTERNAL TABLE t_user (
    userid int,
    sex string,
    age int,
    occupation int,
    zipcode int
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE
LOCATION '/user/fuwuchen/week4/users';

CREATE EXTERNAL TABLE t_movie (
    movieid int,
    moviename string,
    movietype string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE
LOCATION '/user/fuwuchen/week4/movies';

CREATE EXTERNAL TABLE t_rating (
    userid int,
    movieid int,
    rate int,
    times TIMESTAMP
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="::")
STORED AS TEXTFILE
LOCATION '/user/fuwuchen/week4/ratings';
```

题目一

思路

在评分表筛选出指定 id 的电影，连接年龄所在表进行聚合

SQL

```sql
SELECT u.age age,
       AVG(r.rate) avgrate
  FROM t_rating r
  JOIN t_user u
    ON r.userid = u.userid
   AND r.movieid = 2116
 GROUP BY u.age
;
```

运行结果

![WX20220327-112637@2x](https://testpicp.oss-cn-shanghai.aliyuncs.com/uPic/WX20220327-112637@2x.png)

题目二

思路

筛选出指定条件的电影，再连接电影表取表名（减少 join 表的大小）

SQL

```sql
WITH total_and_rate AS (
  SELECT u.sex sex,
         r.movieid movieid,
         COUNT(u.userid) total,
         AVG(r.rate) avgrate
    FROM t_rating r
    JOIN t_user u
      ON r.userid = u.userid
     AND u.sex = "M"
   GROUP BY u.sex, r.movieid
  HAVING total > 50 
)
SELECT t.sex,
       m.moviename name,
       t.avgrate avgrate,
       t.total total
  FROM total_and_rate t
  JOIN t_movie m
    ON t.movieid = m.movieid
  SORT BY avgrate DESC
 LIMIT 10
;
```

补充：根据要求的不同，`SORT BY` 可以改为 `ORDER BY`

运行结果

![WX20220327-112737@2x](https://testpicp.oss-cn-shanghai.aliyuncs.com/uPic/WX20220327-112737@2x.png)

题目三

思路

较为直白，期待简化思路。

1. 先找到影评次数最多的女士 id，再用该 id 筛选评分表中的评分，并找到 top 10；
2. 计算 top 10 电影的评分，并和 步骤1 表、电影表一起 join，得到结果

SQL

```sql
WITH top_user AS (
  SELECT u.userid userid,
         count(*) total_rate
    FROM t_user u
    JOIN t_rating r
      ON u.userid = r.userid
     AND u.sex = "F"
   GROUP BY u.userid
   ORDER BY total_rate DESC
   LIMIT 1
), top_movieid AS (
  SELECT r.movieid movieid,
         r.rate
    FROM t_rating r
   WHERE r.userid IN (
     SELECT userid
       FROM top_user
    )
   ORDER BY r.rate DESC
   LIMIT 10
), movie_avgrate AS (
  SELECT r.movieid movieid,
         avg(r.rate) avgrate
    FROM t_rating r
   GROUP BY r.movieid
   ORDER BY r.movieid
)
SELECT m.moviename moviename,
       a.avgrate avgrate
  FROM (
    SELECT t.movieid movieid, 
           m.avgrate avgrate
      FROM top_movieid t
      JOIN movie_avgrate m
        ON t.movieid = m.movieid
  ) a
  JOIN t_movie m
    ON a.movieid = m.movieid
;
```

补充：
1. 根据要求的不同，`ORDER BY` 可以改成 `SORT BY` 求解分区内的值
2. `movie_avgrate` 中根据 `movieid` 排序非必须，仅为了再现截图中的效果

运行结果

![WX20220327-113152@2x](https://testpicp.oss-cn-shanghai.aliyuncs.com/uPic/WX20220327-113152@2x.png)