用户在给定的月份和年份中最喜欢的sql歌曲

xt0899hw  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(284)

我试图找到一个用户在一年中某个月第一次听到的歌曲
我使用以下数据

user-id       date           song
.......      .......        ......
1           2017-9-30        song1
1           2017-9-10        song1
1           2017-3-12        song2
1           2017-9-01        song1
1           2017-12-31       song1
1           2017-09-12       song3
1           2017-10-11       song1
1           2017-09-09       song5
1           2017-10-08       song4
2           2017-07-12       song1
2           2017-12-31       song3
2           2017-10-12       song5
3           2017-10-11       song1
3           2017-10-09       song7
3           2017-10-08       song2
3           2017-07-12       song2

我试图得到以下输出

user-id     year       month       song     count
   .......    ......      ......      .....    ......
      1        2017        9          song1       3

我尝试了下面的查询,但是我得到的所有数据都是降序的,我无法选择最上面的数据

SELECT YEAR(date) AS year, MONTH(date) AS month,song,count(song) c
FROM table where id=1 and year(dt)=2017 and month(dt)=9
GROUP BY YEAR(date), MONTH(date) ,song ORDER BY c DESC

我甚至试过用

SELECT d.*
FROM (SELECT id,year(date) y,month(date) m,count(song) as c,
            ROW_NUMBER() OVER (PARTITION BY month(date) ORDER BY COUNT(song) DESC) as seqnum
     FROM table where id=1 and month(date)=9
     GROUP BY month(date),year(date) song 
    ) d
WHERE seqnum = 1
ORDER BY c, dt DESC ;

它抛出错误作为
编译语句时出错:失败:parseexception行5:37外部输入“song”(应为)靠近“”

6ju8rftf

6ju8rftf1#

你可以用 LIMIT :

SELECT YEAR(date) AS year, MONTH(date) AS month,song,count(song) c
FROM table 
GROUP BY YEAR(date), MONTH(date) ,song 
ORDER BY c DESC
LIMIT 1

相关问题