SQL ROW_NUMBER( ) OVER 在黑斑羚
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26223303/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ROW_NUMBER( ) OVER in impala
提问by user1189851
I have a use case where I need to use ROW_NUMBER() over PARTITION: Something like:
我有一个用例，我需要在 PARTITION 上使用 ROW_NUMBER()： 类似于：
SELECT
Column1 , Column 2
ROW_NUMBER() OVER (
PARTITION BY ACCOUNT_NUM
ORDER BY FREQ, MAN, MODEL) as LEVEL
FROM
TEST_TABLE
I need a workaround for this in Impala. Unfortunately Impala does not support sub queries and also does not support ROW_NUMBER() OVER functionality. Thank you for your help.
我需要在 Impala 中解决这个问题。不幸的是，Impala 不支持子查询，也不支持 ROW_NUMBER() OVER 功能。感谢您的帮助。
采纳答案by Gordon Linoff
Impala is rather limited for this type of query. With some assumptions, this query is possible:
Impala 对于这种类型的查询相当有限。有一些假设，这个查询是可能的：
- The four columns in the partitioning clause are never
NULL - The four columns in the partitioning clause uniquely identify a row
- 分区子句中的四列从不
NULL - 分区子句中的四列唯一标识一行
The query is rather ugly and expensive:
该查询相当丑陋且昂贵：
select tt.column1, tt.column2, count(*) as level
from test_table tt join
test_table tt2
on tt.account_num = tt2.account_num and
(tt2.freq < tt.freq or
tt2.freq = tt.freq and tt2.man < t.man or
tt2.freq = tt.freq and tt2.man = t.man and tt2.model <= t.model
)
group by tt.column1, tt.column2, tt.account_num, tt.freq, tt.man, tt.model;
回答by Tagar
ROW_NUMBER() OVER PARTITION was added in CDH 5.2:
CDH 5.2 中添加了 ROW_NUMBER() OVER PARTITION：
ROW_NUMBER() OVER([partition_by_clause] order_by_clause)
回答by Alexis.Rolland
Impala supports now the over clause. Syntax is the same as in the question.
Impala 现在支持 over 子句。语法与问题中的相同。
SELECT
Column1 , Column 2
ROW_NUMBER() OVER (
PARTITION BY ACCOUNT_NUM
ORDER BY FREQ, MAN, MODEL) as LEVEL
FROM
TEST_TABLE
Impala documentation: https://www.cloudera.com/documentation/enterprise/5-6-x/topics/impala_analytic_functions.html#over
Impala 文档：https: //www.cloudera.com/documentation/enterprise/5-6-x/topics/impala_analytic_functions.html#over
回答by Keng
Impala supports sub-queries. Both in parentheses and using the withfunction.
Impala 支持子查询。在括号中和使用with函数。
