如何在配置单元中编写以下查询

m1m5dgzv 于 2021-06-02 发布在 Hadoop

关注(0)|答案(2)|浏览(359)

我在配置单元中实现了以下查询：

SELECT title, rating FROM 
( 
    SELECT m.title as title, variance(r.rating) as var, r.rating as     rating, r.time_stamp as time_stamp
    FROM movies m JOIN ratings r ON m.movieid = r.movieid
    DISTRIBUTE BY m.title, r.rating
    GROUP BY m.title
    SORT BY m.title, r.rating
) A
WHERE year(from_unixtime(time_stamp)) = '2015'
GROUP BY title
LIMIT 10;

但我得到以下错误：

Error while compiling statement: FAILED: ParseException line 6:4 missing ) at 'GROUP' near 'GROUP' line 6:10 missing EOF at 'BY' near 'GROUP'

sql hadoop Hive

来源：https://stackoverflow.com/questions/42055519/how-to-write-the-following-query-in-hive

2条答案

按热度按时间

vdzxcuhz1#

帕特里克，还是sql。
-不能选择不属于分组依据的列。

YEAR 返回整数（p.s.评级未分区？）。
-你应该有充分的理由 DISTRIBUTE BY 以及 SORT BY ，从Hive的乞讨时间起的技术条款。

select      m.title
           ,r.var
from                   (select      r.movieid
                                   ,variance(r.rating)  as var
                        from        ratings as r
                        where       year(from_unixtime(time_stamp)) = 2015
                        group by    r.movieid
                        order by    var desc
                        limit       10
                        ) as r
            join        movies as m
            on          m.movieid   =
                        r.movieid
;

展开查看全部

赞(0）回复(0）举报 2021-06-02

flseospp2#

我想这就是你想要的：

SELECT m.movieid, m.title, variance(r.rating) as var
FROM movies m JOIN
     ratings r
     ON m.movieid = r.movieid
WHERE year(from_unixtime(time_stamp)) = 2015
GROUP BY m.movieid, m.title
ORDER BY var DESC
LIMIT 10;

赞(0）回复(0）举报 2021-06-02

我来回答

如何在配置单元中编写以下查询

2条答案

相关问题

热门标签

最新问答