HIVE NTILE 报错

Question

有一张阅读登入表，包含字段： date，userid，bookid

现在想要提取每一天阅读人数前5%的书籍，想使用ntile函数将数据集切分成20份：

select date,
bookid,
count(distinct userid) as readpeoples,
ntile(20) over(partition by date order by count(distinct userid) desc) as rn
from table

但是一直报错：Invalid Function userid,

将

ntile(20) over(partition by date order by count(distinct userid) desc) as rn

换成

ntile(20) over(partition by date order by readpeoples desc) as rn

依然报错

求大神解答

Answer 1

select date,bookid,ntile(20) over(partition by date order by readpeoples desc) as rn
from (	
select date,
bookid,
count(distinct userid) as readpeoples
from table
group by date,bookid) t 

要先分组