如何将 SQL 结果存储在变量中并解析结果以识别可能的模式? [英] How to store SQL result in a variable and parse the result to identify a possible pattern?

问题描述

下面的查询

INSERT INTO temp 
SELECT esd, 
       'E' 
FROM   test_data_sovlp 
WHERE  esd IS NOT NULL 
UNION ALL 
SELECT td, 
       CASE is_db 
         WHEN 0 THEN 'S' 
         WHEN 1 THEN 'H' 
       END AS FLAG 
FROM   test_data_sovlp 
WHERE  td IS NOT NULL 

返回以下数据:

|----------|----------|
|  DT      |  FLAG    |
|----------|----------|
|  10      |  E       |
|  20      |  H       |
|  30      |  E       |
|  40      |  E       |
|  50      |  E       |
|  60      |  S       |
|  70      |  H       |
|  75      |  E       |
|  80      |  H       |
|  100     |  H       |
|----------|----------|

针对该表运行时:

|----------|----------|----------|----------|----------|
|    ID    |   ESD    |  TD      |   IS_DB  | TEST_SET |
|----------|----------|----------|----------|----------|
|    1     |  10      |  20      |    1     |    2     |
|    2     |  30      |  (null)  |    1     |    2     |
|    3     |  40      |  (null)  |    1     |    2     |
|    4     |  50      |  60      |    0     |    2     |
|    5     |  (null)  |  70      |    1     |    2     |
|    6     |  75      |  100     |    1     |    2     |
|    7     |  (null)  |  80      |    1     |    2     |
|----------|----------|----------|----------|----------|

注意:参见演示 此处或我之前的帖子此处了解更多详情.

Note: See the demo here or my previous post here for more details.

我感兴趣的是按照 DT 顺序连接上述查询返回的 FLAG 值.

What I'm interested in is to concatenate the FLAG value return by the query above, in the DT order.

因此对于上面的查询，串联(我们称之为 q_result)值为:q_result = EHEEESEHH.

So for the query above, the concatenation (let's call it q_result) value is: q_result = EHEEESEHH.

然后我想通过 2 个字符的块来解析 q_result 以检测以下任何序列的可能存在:

I want then to parse then q_result by block of 2 characters to detect the possible presence of any of the following sequence:

HH      EE      HS      SE

在解析过程中，如果模式匹配 q_result 中的任何位置，我想编写的过程必须返回 0.如果没有模式匹配，那么 proc 必须返回 1.

During the parse, if a pattern match anywhere in q_result, the proc I would like to write must return 0. If no pattern match then the proc must return 1.

问题

这是怎么做到的?

推荐答案

如果我理解正确，你可以这样做:

If I understand correctly, you can do something like this:

select count(*)
from (select listagg(flag) within group (order by dt) as flags
      from temp
     ) x
where not regexp_like(flags, 'HH|EE|HS|SE');

或者，您可以使用 lag():

select (case when count(*) = sum(case when flag2 not in ('HH', 'EE', 'HS', 'SE')
             then 1 else 0
        end) as return_value
from (select t.*,
             (lag(flag) over (order by dt) || flag) as flag2
      from temp
     ) t;

这篇关于如何将 SQL 结果存储在变量中并解析结果以识别可能的模式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！