when语句的hive-sql查询

pgx2nnw8 于 2021-06-26 发布在 Hive

关注(0)|答案(2)|浏览(283)

我有一个具有以下模式的表

id  data  rank  usage
 1  hi     19    H
 2  he     14    M
 3  me      9    L
 4  ke     23    H
 5  jo     11    M
 6  km      2    L
 7  lo      4    L
 8  no     12    M
 9  my     20    H

我想做一些像 If max(rank) where usage='L'>=25 Then Select all records where usage='H' Else If max(rank) where usage='M'>=25 Select all records where usage='L' and records where usage='M' ELSE Select all records sql不支持以下语句： SELECT CASE WHEN (select max(rank) from my_table where usage='L') >= 100 那么，我应该如何编写这个查询呢？

sql Hive hiveql

来源：https://stackoverflow.com/questions/44537909/sql-query-with-complex-case-when-statement

2条答案

按热度按时间

hgb9j2n61#

您可以在过滤器中应用逻辑。此查询应提供您所需的：

select *
  from table1
 where 
       ((select max(rank) from table1 where usage = 'L') >= 25 and usage = 'H')
       or ((select max(rank) from table1 where usage = 'L') < 25 
            and (select max(rank) from table1 where usage = 'M') >= 25 
            and usage = 'L')
       or ((select max(rank) from table1 where usage = 'L') < 25
           and (select max(rank) from table1 where usage = 'M') < 25)

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

ajsxfq5m2#

select  id,data,rank,usage

from   (select  *
               ,max(case usage when 'L' then rank end) over () max_rank_L   
               ,max(case usage when 'M' then rank end) over () max_rank_M

        from    mytable 
        ) t

where   usage rlike case
                        when max_rank_L >= 25 then '^(H)$'
                        when max_rank_M >= 25 then '^(L|M)$'
                        else                       '()'
                    end

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

我来回答

when语句的hive-sql查询

2条答案

相关问题

热门标签

最新问答