hadoop - hive :如何将总行数输出为变量

Question

我有一个数据集，正在使用以下代码进行重复数据删除:

select session_id, sol_id, id, session_context_code, date
    from (
        select *, ROW_NUMBER() OVER (PARTITION BY session_id, sol_id, date) as rn,
        substr(case_id,2,9) as id

        from df.t1_data
         )undup
        where undup.rn =1 
        order by session_id, sol_id, date

我想添加一个变量来存储dedup之后的总行数，并尝试使用count(*):

select session_id, sol_id, id, session_context_code, date,count(*) as total
    from (
        select *, ROW_NUMBER() OVER (PARTITION BY session_id, sol_id,date) as rn,
        substr(case_id,2,9) as id

        from df.t1_data
         )undup
        where undup.rn =1 
        order by session_id, sol_id, date

我收到的错误:

ERROR: Execute error: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:44 Expression not in GROUP BY key 'session_id'

我只想将计数输出为一个变量，该变量在对行号进行重复数据删除之后，对session_id和sol_id的所有不同记录进行计数。如何将其合并到代码中？

根据Gomz的建议，但收到错误:

ERROR: Execute error: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:614 missing EOF at 'group' near 'nifi_date'

码:

select session_id, solicit_id, nifi_date,id, session_context_code,count(*) as total
    from (
        select *, ROW_NUMBER() OVER (PARTITION BY session_id, sol_id) as rn,
        substr(case_id,2,9) as id
        from df.t1_data
         )undup
        where undup.rn =1 and 
        session_context_code in ("4","3") and
        order by session_id, sol_id, nifi_date
        group by session_id, sol_id, nifi_date,id, session_context_code

Answer 1

具有COUNT(*)的Hive查询以及SELECT子句中的列应将这些列的末尾分组为GROUP BY。

一些样本:
SELECT COUNT(*) FROM employees;SELECT id, name, COUNT(*) FROM employees GROUP BY id, name;
在您的问题场景中，查询应如下所示，

select session_id, sol_id, id, session_context_code, count(*) as total
    from (
        select *, ROW_NUMBER() OVER (PARTITION BY session_id, sol_id,date) as rn,
        substr(case_id,2,9) as id

        from df.t1_data
         )undup
        where undup.rn =1 
GROUP BY session_id, sol_id, id, session_context_code
        order by session_id, sol_id, date

您可以阅读更多 HERE

更新:如果只想按session_id和sol_id计算所有不同的记录，则查询可以如下所示:

select session_id, sol_id, count(*) as total
    from (
        select *, ROW_NUMBER() OVER (PARTITION BY session_id, sol_id,date) as rn,
        substr(case_id,2,9) as id

        from df.t1_data
         )undup
        where undup.rn =1 
GROUP BY session_id, sol_id
        order by session_id, sol_id, date;

如前所述，您只能使用需要在SELECT和GROUP BY中计数的列。

如果您需要多于多列的结果而不是需要计数的结果，则可以创建一个仅包含被计数列的临时表，并将其与原始表连接。即，如果您需要表中的c，d，e，f列，即使您需要对a，b列进行计数，那么您也可以执行以下操作，

CREATE TABLE tmp AS 
SELECT a, b, count(*)
FROM table1
GROUP BY a,b;

在a，b列的tmp和table1之间进行JOIN

SELECT y.a, y.b, x.c, x.d, x.e, x.f
FROM tmp y, table1 x
WHERE y.a=x.a
AND y.b=x.b;

希望这可以帮助!