when(配置单元)中使用查询表的列总数

zfciruhq 于 2021-06-25 发布在 Hive

关注(0)|答案(3)|浏览(293)

简化示例：
在 hive 里，我有一张table t 有两列：

Name, Value
Bob, 2
Betty, 4
Robb, 3

我想做一个 case when 使用值列的总和：

Select
    Name
    , CASE 
        When value>0.5*sum(value) over () THEN ‘0’
        When value>0.9*sum(value) over () THEN ‘1’
        ELSE ‘2’
        END as var
From table

我不喜欢这样的事实 sum(value) over () 计算两次。有没有办法只计算一次。添加了twist，我想在一个查询中实现这一点，因此不需要声明用户变量。
我在考虑标量查询：

With total as
 (Select sum(value) from table)
Select 
    Name
    , CASE 
         When value>0.5*(select * from total) THEN ‘0’
         When value>0.9*(select * from total)THEN ‘1’
         ELSE ‘2’
         END as var 
From table;

但这行不通。
tldr：有没有一种方法可以在没有用户变量的情况下简化第一个查询？

sql Hive case

来源：https://stackoverflow.com/questions/59631391/using-the-total-of-a-column-of-the-queried-table-in-a-case-when-hive

3条答案

按热度按时间

4szc88ey1#

交叉联接将总和提取到表中的子查询：

Select
    t.Name
    , CASE 
        When t.value>0.9*tt.value THEN '1'
        When t.value>0.5*tt.value THEN '0'
        ELSE '2'
        END as var
From table t cross join (select sum(value) value from table) tt

并更改case表达式中when子句的顺序，因为这样，第二个case就永远不会成功。

赞(0）回复(0）举报 2021-06-26

p1iqtdky2#

别担心这个。让优化器来操心吧。但是，如果不想重复表达式，可以使用子查询或cte：

select Name,
       (case when value > 0.5 * total then '0'
             when value > 0.9 * total then '1' 
             else '2'
        end) as var
From (select t.*, sum(value) over () as total
      from table t
     ) t;

赞(0）回复(0）举报 2021-06-26

9gm1akwq3#

由于i/o是减慢配置单元查询速度的主要因素，因此我们应该努力减少阶段数以获得更好的性能。
所以最好不要在这里使用子查询或cte。
使用全局窗口子句尝试此sql：

select 
    name,
    case
        when value > 0.5*sum(value) over w then '0'
        when value > 0.9*sum(value) over w then '1'
        else '2'
        end as var
from my_table
window w as (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

在这种情况下 window clause 是减少代码重复的推荐方法。
Windows和Windows sum 聚合将只计算一次。你可以跑了 explain select... ，确认只会启动一个有意义的mr阶段。
编辑：
1.一个简单的 select 子查询上的子句不值得担心。它可以下推到子查询的最后一个阶段，以避免额外的mr阶段。
2.驻留在同一查询块中的两个相同聚合将只计算一次。所以不要担心潜在的重复计算。

赞(0）回复(0）举报 2021-06-26

我来回答

when(配置单元)中使用查询表的列总数

3条答案

相关问题

热门标签

最新问答