Using Hive ntile results in a sentence

Question

I want to get summary data of the first quartile for a table in Hive. Below is a request for the maximum number of views in each quartile:

SELECT NTILE(4) OVER (ORDER BY total_views) AS quartile, MAX(total_views) FROM view_data GROUP BY quartile ORDER BY quartile; 

And this query is to get the names of all the people who are in the first quartile:

 SELECT name, NTILE(4) OVER (ORDER BY total_views) AS quartile FROM view_data WHERE quartile = 1 

I get this error for both queries:

 Invalid table alias or column reference 'quartile' 

How can I refer to ntile results in a where clause or group by clause?

Answer 1

You cannot put the windowing function in the where clause because this will create ambiguity if there are compound predicates. So use a subquery.

 select quartile, max(total_views) from (SELECT total_views, NTILE(4) OVER (ORDER BY total_views) AS quartile, FROM view_data) t GROUP BY quartile ORDER BY quartile ; 

and

 select * from (SELECT name, NTILE(4) OVER (ORDER BY total_views) AS quartile FROM view_data) t WHERE quartile = 1 ;