hadoop - 在 where 子句中使用 Hive tile 结果

Question

我想获取 Hive 表的第一个四分位数的汇总数据。下面是获取每个四分位数中最大 View 数的查询:

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

此查询是获取第一个四分位数中所有人的姓名:

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

我在两个查询中都遇到了这个错误:

Invalid table alias or column reference 'quartile'

如何在 where 子句或 group by 子句中引用 ntile 结果？

Answer 1

您不能将窗口函数放在 where 子句中，因为如果有复合谓词，它会产生歧义。所以使用子查询。

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
;

和

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