主页 / dba / 问题 / 153338
Accepted
klx123
Asked: 2016-10-26 22:31:42 +0800 CST

使用 hiveql 的累积和

  • 772

我在 Hive 中有一张桌子，看起来像：

col1       col2
b           1
b           2
a           3
b           2
c           4
c           5

我如何使用 hiveql 将col1元素组合在一起，对它们求和，按总和排序，以及基于总和创建累积总和 (csum)？

id       sum_all     csum
a         3           3
b         5           8
c         9           17

我只设法提出了分组和总和，但对累积总和一无所知。Hive 不支持相关子查询

select col1 as id
      sum(col2) as sum_all
from t
group by col1
order by sum_all

结果如下：

id       sum_all
a         3
b         5
c         9
hive

1 个回答

  1. Best Answer

    由于不允许关联子查询，请尝试使用派生表，然后将它们连接起来。

    select 
    a.id,
    a.sum_all,
    sum(b.sum_all) as csum
from
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  a
    join
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  b
     on
        ( b.sum_all < a.sum_all )
     or ( b.sum_all = a.sum_all and b.id <= a.id )
group by
    a.sum_all, a.id
order by 
    a.sum_all, a.id ;

    这本质上是派生的 group-by 表上的自联接。首先将分组结果保存到临时表中然后执行自连接可能更有效。

    根据手册，Hive 也有窗口聚合，所以你也可以使用它们：

    select 
    a.id,
    a.sum_all,
    sum(a.sum_all) over (order by a.sum_all, a.id
                         rows between unbounded preceding
                                  and current row)
        as csum
from
        ( select col1 as id,
                 sum(col2) as sum_all
          from t
          group by col1
        )  a
order by 
    sum_all, id ;

    或与：

    select 
    col1 as id,
    sum(col2) as sum_all,
    sum(sum(col2)) over (order by sum(col2), col1
                         rows between unbounded preceding
                                  and current row)
        as csum
from
    t
group by 
    col1
order by 
    sum_all, id ;
    • 5

相关问题