我想获取Hive中一个表的第一个四分位数的汇总数据。下面是一个查询,用于获取每个四分位数中的最大视图数量:
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
结果?
发布于 2015-07-21 22:59:57
您不能将窗口函数放在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
;
https://stackoverflow.com/questions/31540469
复制相似问题