基于条件的sql动态帧过滤条件

4smxwvx5 于 2021-06-25 发布在 Hive

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

我们为条件/过滤器维护了单独的表。根据条件，在基表中应用过滤器。
以下是示例输入条件数据，以供参考

+-----------+-------------------+----------+------------+--------------+---------------+----------------+
val_range   |val_range_operator | val_From |  val_till  |   val_except  |  except_from |   except_till  |
+-----------+-------------------+----------+------------+---------------+--------------+----------------+
            |                   |          |            |               |              |                |
100         |  =                |          |            |               |              |                |
            |                   |          |            |               |              |                |   
            | between           |   200    |    300     |               |              |                |
            |                   |          |            |               |              |                |
            | between           |   410    |    620     | 450,600,610   |              |                |
            |                   |          |            |               |              |                |
            | between           |   800    |   999      | 810,820,850   |     890      |     930        |
            |                   |          |            |               |              |                |
            | between           |   1200   |   1500     |               |     1300     |      1399      |
+-----------+-------------------+----------+------------+---------------+--------------+----------------+

根据这个输入条件，滤波器的推导如下。

Select col* 
from transaction_tbl 
where 
    val_range = 100 
    OR val_range between 200 AND 300 
    OR val_range between 410 AND 620 AND val_range not in (450,600,610) 
    OR val_range between 800 AND 999 AND val_range not in (810,820,850) AND NOT BETWEEN 890 and 930 
    OR val_range between 1200 AND 1500 AND val_range NOT BETWEEN 1300 AND 1399

请帮我实现过滤查询

sql Hive apache-spark apache-spark-sql hiveql

来源：https://stackoverflow.com/questions/59419725/dynamically-frame-filter-condition-based-on-conditions

2条答案

按热度按时间

nwsw7zdq1#

下面的sparksql将帮助您构建where子句，

select 
  concat(
    '( ', 
    concat_ws(
      ') OR (', 
      collect_list(
        case when val_range_operator = '=' 
        and val_range is not null then concat_ws(' ', 'val_range', '=', val_range) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is null 
        and except_from is null 
        and except_till is null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till
        ) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is not null 
        and except_from is null 
        and except_till is null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till, 'AND', 'val_range', 
          'NOT', 'IN', '(', val_except, ')'
        ) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is not null 
        and except_from is not null 
        and except_till is not null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till, 'AND', 'val_range', 
          'NOT', 'IN', '(', val_except, ')', 
          'AND NOT BETWEEN', except_from, 
          'AND', except_till
        ) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is null 
        and except_from is not null 
        and except_till is not null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till, 'AND NOT BETWEEN', 
          except_from, 'AND', except_till
        ) end
      )
    ), 
    ' )'
  ) as filter_condition 
from 
  filter_tb

ps：场景是基于参考数据导出的。如果存在任何其他场景，请更新查询。

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

llycmphe2#

您可以使用stringbuilder在运行时根据输入形成查询，也可以使用querydsl之类的工具在运行时形成查询。
http://www.querydsl.com/

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