HDP平台Hive查询：男女用户最常观看的电影流派统计

嘿，针对你这个需求，核心难点是把movies表中列形式存储的流派标识转换成行结构，这样才能按流派分组统计。咱们一步步来实现：

步骤1：将流派列转行 #

movies表的最后19个字段是流派的0/1标识，我们需要把这些列转换成「流派名称-是否属于」的行数据。这里用LATERAL VIEW EXPLODE()来实现，先构造一个包含所有流派名称和对应字段的数组，再展开成多行：

SELECT 
  m.movie_id,
  genre_name,
  genre_flag
FROM movies m
LATERAL VIEW EXPLODE(ARRAY(
  struct('unknown', unknown),
  struct('action', action),
  struct('adventure', adventure),
  struct('animation', animation),
  struct('childrens', childrens),
  struct('comedy', comedy),
  struct('crime', crime),
  struct('documentary', documentary),
  struct('drama', drama),
  struct('fantasy', fantasy),
  struct('noir', noir),
  struct('horror', horror),
  struct('musical', musical),
  struct('mystery', mystery),
  struct('romance', romance),
  struct('sci_fi', sci_fi),
  struct('thriller', thriller),
  struct('war', war),
  struct('western', western)
)) exploded AS genre_info
LATERAL VIEW INLINE(ARRAY(genre_info)) g AS genre_name, genre_flag
WHERE genre_flag = 1; -- 只保留电影所属的流派

这段代码会把每部电影的所有所属流派拆成单独的行，比如一部同时属于action和comedy的电影会生成两行数据。

步骤2：关联所有表并统计流派观看次数 #

接下来把上面的结果和ratings、users表关联，按性别和流派分组，统计每个性别下各流派的观影次数（这里用COUNT(*)统计评分记录数，代表观看次数）：

WITH genre_movies AS (
  SELECT 
    m.movie_id,
    genre_name
  FROM movies m
  LATERAL VIEW EXPLODE(ARRAY(
    struct('unknown', unknown),
    struct('action', action),
    struct('adventure', adventure),
    struct('animation', animation),
    struct('childrens', childrens),
    struct('comedy', comedy),
    struct('crime', crime),
    struct('documentary', documentary),
    struct('drama', drama),
    struct('fantasy', fantasy),
    struct('noir', noir),
    struct('horror', horror),
    struct('musical', musical),
    struct('mystery', mystery),
    struct('romance', romance),
    struct('sci_fi', sci_fi),
    struct('thriller', thriller),
    struct('war', war),
    struct('western', western)
  )) exploded AS genre_info
  LATERAL VIEW INLINE(ARRAY(genre_info)) g AS genre_name, genre_flag
  WHERE genre_flag = 1
)
SELECT
  u.gender,
  gm.genre_name,
  COUNT(*) AS watch_count
FROM genre_movies gm
JOIN ratings r ON gm.movie_id = r.movie_id
JOIN users u ON r.user_id = u.user_id
GROUP BY u.gender, gm.genre_name
ORDER BY u.gender, watch_count DESC;

步骤3：获取每个性别最常观看的流派 #

如果需要直接得到每个性别排名第一的流派（包括并列情况），可以用窗口函数RANK()：

WITH genre_movies AS (
  SELECT 
    m.movie_id,
    genre_name
  FROM movies m
  LATERAL VIEW EXPLODE(ARRAY(
    struct('unknown', unknown),
    struct('action', action),
    struct('adventure', adventure),
    struct('animation', animation),
    struct('childrens', childrens),
    struct('comedy', comedy),
    struct('crime', crime),
    struct('documentary', documentary),
    struct('drama', drama),
    struct('fantasy', fantasy),
    struct('noir', noir),
    struct('horror', horror),
    struct('musical', musical),
    struct('mystery', mystery),
    struct('romance', romance),
    struct('sci_fi', sci_fi),
    struct('thriller', thriller),
    struct('war', war),
    struct('western', western)
  )) exploded AS genre_info
  LATERAL VIEW INLINE(ARRAY(genre_info)) g AS genre_name, genre_flag
  WHERE genre_flag = 1
), genre_watch_counts AS (
  SELECT
    u.gender,
    gm.genre_name,
    COUNT(*) AS watch_count
  FROM genre_movies gm
  JOIN ratings r ON gm.movie_id = r.movie_id
  JOIN users u ON r.user_id = u.user_id
  GROUP BY u.gender, gm.genre_name
)
SELECT
  gender,
  genre_name,
  watch_count
FROM (
  SELECT
    gender,
    genre_name,
    watch_count,
    RANK() OVER (PARTITION BY gender ORDER BY watch_count DESC) AS rank
  FROM genre_watch_counts
) ranked
WHERE rank = 1;

关键说明 #

• 用LATERAL VIEW EXPLODE()处理列转行是Hive 1.2.x版本中处理这类宽表的常用方式，比多次UNION ALL更高效；
• RANK()会保留并列第一的流派，如果只需要一个唯一结果，可以换成ROW_NUMBER()，但会随机选取一个并列项。

内容的提问来源于stack exchange，提问作者uhlik