标记为表列名的数据的HiveQL查询

Question

我在HDP 2.6.5平台上使用Hive(1.2.1000.2.6.5.0-292)在一个简单的数据库中工作，该数据库基于以下数据:https://grouplens.org/datasets/movielens/100k/
。我有4个表，名为：类型、电影、收视率、用户，如下所示：

CREATE TABLE genre(genre string, genre_id int);
CREATE TABLE movies (movie_id INT, title STRING, rel_date DATE, video_rel_date STRING, 
imdb_url STRING, unknown INT, action INT, adventure INT, animation INT, childrens INT, 
comedy INT, crime INT, documentary INT, drama INT, fantasy INT, noir INT, horror INT, 
musical INT, mystery INT, romance INT, sci_fi INT, thriller INT, war INT, western INT) 
CLUSTERED BY (movie_id) INTO 12 BUCKETS STORED AS ORC;
CREATE TABLE ratings(user_id int, movie_id int, rating int, rating_time int);
CREATE TABLE users(user_id int, age int, gender char(1), occupation string, zip int);

我想写一个问题，返回哪种类型的电影最常被女性观看，哪种类型的电影最常被男性观看？但对我来说，问题是电影类型所在的电影表的结构：

1玩具总动员(1995)1995-01-01http://us.imdb.com/m/title-确切？玩具%20Story%20(1995)0001110000000000000

最后19个字段是类型，“1”表示电影属于该类型，“0”表示不属于该类型。另外，电影可以同时分为几种类型。性别在“用户”表中表示为“M”或“F”字符。所需的表可以很容易地联接，但如何返回和分组类型，即列名？

SELECT m.title, r.rating, u.gender
FROM movies m INNER JOIN ratings r ON (m.movie_id = r.movie_id) 
INNER JOIN users u ON (u.user_id = r.user_id);

Answer 1

使一个数组的类型列放置在与genre_id对应的顺序，爆炸数组和连接的位置数组与类型表。像这样（未测试）：

   select s.title, s.genre, s.gender, s.rating, s.cnt
    from
    (select s.title, s.gender, s.rating, s.cnt, s.genre,
           rank() over (partition by s.gender order by s.cnt desc) as rnk
     from
       (
        select m.title, u.gender, r.rating, g.genre, count(*) over(partition by u.gender) cnt
          from    
            (select m.movie_id, m.title, e.id+1 as genre_id
               from movies m
                  lateral view 
                  posexplode (array(--place columns in a positions corresponding their genre_id
                              unknown, action, adventure, animation, childrens, 
                              comedy, crime, documentary, drama, fantasy, 
                              noir, horror, musical, mystery, romance, 
                              sci_fi, thriller, war, western
                                   )
                             )e as id, val
              where e.val=1
             ) m
             INNER JOIN ratings r ON (m.movie_id = r.movie_id) 
             INNER JOIN users   u ON (u.user_id = r.user_id)
             INNER JOIN genre   g ON (g.genre_id = m.genre_id)
         ) s
    ) s where rnk = 1