在每个位置的一个月内找到最佳用户

x33g5p2x 于 2021-06-26 发布在 Hive

关注(0)|答案(1)|浏览(292)

我正在努力寻找最好的用户在每年的每个月基于位置
我使用以下数据

user_id        location       date                Product_brought
........       .........      .....               ...............
1               ks            2017-08-17             prod1
1               ks            2017-08-17             prod2
2               ks            2017-08-17             prod1
2               ks            2017-01-17             prod5
2               ks            2017-01-17             prod4
1               ks            2017-01-17             prod2 
4               Tg            2017-08-17             prod4 
5               Tg            2017-08-17             prod5
5               Tg            2017-08-17             prod7
5               Tg            2017-08-17             prod4
5               Tg            2017-01-17             prod3
6               Tg            2017-01-17             prod4
6               Tg            2017-01-17             prod3 
6               Tg            2017-08-17             prod1 
1               ks            2017-08-17             prod1
1               ks            2017-08-17             prod2
2               ks            2017-08-17             prod1
2               ks            2017-01-17             prod5
2               ks            2017-01-17             prod4
1               ks            2017-01-17             prod2 
4               Tg            2017-08-17             prod4 
5               Tg            2017-08-17             prod5
5               Tg            2017-08-17             prod7
5               Tg            2017-08-17             prod4
5               Tg            2017-01-17             prod3
6               Tg            2017-01-17             prod4
6               Tg            2017-01-17             prod3 
6               Tg            2017-08-17             prod1

我期待以下输出

user         year             month        location         count
.....        ....             ......       .........        ......
1            2017                8             ks              2
2            2017                1             ks              2
5            2017                8             Tg              3
6            2017                1             Tg              2 
1            2016                8             ks              2
2            2016                1             ks              2
5            2016                8             Tg              3
6            2016                1             Tg              2

我尝试使用以下查询

SELECT ds.*
FROM (SELECT user,month(date),year(date), location, count(product_brought) as c,
            ROW_NUMBER() OVER (PARTITION BY month(date),year(date) ORDER BY COUNT(product) DESC) as seqnum
     FROM table
     GROUP BY month(date), year(date),location, user
    ) ds
WHERE seqnum = 1
ORDER BY c DESC ;

它没有得到预期的输出

sql Hive

来源：https://stackoverflow.com/questions/45642819/find-the-best-user-in-a-month-of-every-location

1条答案

按热度按时间

dffbzjpn1#

with cte as (
 select
    user_id
   ,year(date) 'year'
   ,month(date) 'month'
   ,location
   ,count(Product_brought) 'count'
   ,row_number() over (partition by user_id, year(date), month(date), location order by count(Product_brought) desc) 'seqnum'
 from TempTable
 group by
    user_id
   ,year(date)
   ,month(date)
   ,location
 )
 select *
 from cte
 where seqnum = 1

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

我来回答

在每个位置的一个月内找到最佳用户

1条答案

相关问题

热门标签

最新问答