如何将SQL结果存储在变量中并解析结果以识别可能的模式？

The query below

下面的查询

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

return the following data:

返回以下数据：

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

when run against this table:

在针对此表运行时：

|----------|----------|----------|----------|----------|
|    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.

注意：有关详细信息，请参阅此处的演示或此前的帖子。

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

我感兴趣的是按DT顺序连接上面查询的FLAG值。

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

因此，对于上面的查询，连接（让我们称之为q_result）值为：q_result = EHEEESEHH。

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

我希望然后用2个字符的块解析q_result，以检测可能存在以下任何序列：

HH      EE      HS      SE

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.

在解析期间，如果模式匹配q_result中的任何位置，我想写的proc必须返回0.如果没有模式匹配则proc必须返回1。

Question

题

How can this be done ?

如何才能做到这一点？

1 个解决方案

#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');

Alternatively, you can use lag():

或者，您可以使用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;

#1