Regexp_Substr以筛选SQL中的行

Question

对于下面的表结构和数据，我试图为具有由模式分隔的字符串组的列获取在过去15个月内至少包含1个日期的行。

CREATE TABLE TEMP(nbr NUMBER, dt VARCHAR2(4000));
INSERT INTO TEMP VALUES(1,'22/05/2016');
INSERT INTO TEMP VALUES(2,'22/05/2020##22/01/2020##22/10/2019');
INSERT INTO TEMP VALUES(3,'25/05/2020##22/07/2019##22/11/2019');
INSERT INTO TEMP VALUES(4,'25/05/2015##22/01/2017##22/06/2018');

我写的查询是：

select nbr,dt from temp
WHERE MONTHS_BETWEEN(SYSDATE,TO_DATE(REGEXP_SUBSTR(dt,'[^##]+',1,LEVEL)))<15
CONNECT BY LEVEL<=REGEXP_COUNT(dt,'[^##]+');

对于第2行和第3行，属性dt的值使得至少1个子字符串的日期小于15个月。预期结果为：

nbr    dt
2   22/05/2020##22/01/2020##22/10/2019
3   25/05/2020##22/07/2019##22/11/2019

我得到的结果是：https://i.stack.imgur.com/rZ1tp.jpg

我确信这个查询缺少了一些东西，因此我得到了所有那些重复的行。这是我第一次尝试这种东西。有人能向我指出我需要做什么来纠正这一点吗？

Answer 1

您可以简单地将EXISTS过滤器与相关的分层子查询一起使用：

SELECT *
FROM   temp t
WHERE  EXISTS (
SELECT 1
FROM   DUAL
WHERE  TO_DATE( REGEXP_SUBSTR( t.dt, '[^#]+', 1, LEVEL ), 'DD/MM/YYYY' )
>= ADD_MONTHS( TRUNC(SYSDATE), -15 )
CONNECT BY
LEVEL <= REGEXP_COUNT( t.dt, '[^#]+' )
)

对于您的样本数据，输出：

NBR|DT--：|：---------------------------------2020年5月22日| 2020年1月22日#2019年10月22日2020年5月3日| 2019年7月22日#2019年11月22日

db<gt；小提琴这里

Answer 2

如果我没有提到需要一个合适的数据模型，并且数据应该在从电子表格中引入时转换为该数据模型，那我就太失职了。这样你就不会像现在这样，为了得到你想要的东西而试图跳过重重关卡。当然，你可以得到你想要的，但有了代码，其他人很难在你身后维护。作为练习，我鼓励你这样做，即使你可能会得到一个适合你当前情况的解决方案。亲眼看看如何花时间创建一个正确规范化的数据模型，并将电子表格数据移动到其中，使您能够创建更高效、更易于维护的代码。

好的，也就是说，考虑一下这个。这里的WITH子句就像临时表一样用于创建要从中进行选择的数据集。查询使用CONNECT BY在字符串的每个元素中移动，其中元素后面跟着分隔符"##"或行的末尾(使用这种形式的regex处理NULL列表元素(。请注意，我在选择列表中包含了LEVEL，这样您就可以看到哪个元素符合条件。如果您不关心这一点，请删除它，并在select语句中添加DISTINCT，以获得您在原始文章中期望的输出。

WITH tbl(item_nbr, purchase_date) AS (
SELECT 1, '22/05/2016' FROM dual UNION ALL
SELECT 2, '22/05/2020##22/01/2020##22/10/2019' FROM dual UNION ALL
SELECT 3, '25/05/2020##22/07/2019##22/11/2019' FROM dual UNION ALL
SELECT 4, '25/05/2015##22/01/2017##22/06/2018' FROM dual
)
SELECT --DISTINCT
item_nbr, LEVEL AS ELEMENT, purchase_date
FROM tbl
WHERE MONTHS_BETWEEN(SYSDATE, TO_DATE(REGEXP_SUBSTR(purchase_date, '(.*?)(##|$)', 1, LEVEL, NULL, 1), 'DD/MM/YYYY' ) ) < 15
CONNECT BY LEVEL <= REGEXP_COUNT(purchase_date,'##') + 1
AND PRIOR item_nbr = item_nbr
AND PRIOR SYS_GUID() IS NOT NULL;

ITEM_NBR    ELEMENT PURCHASE_DATE                     
---------- ---------- ----------------------------------
2          1 22/05/2020##22/01/2020##22/10/2019
2          2 22/05/2020##22/01/2020##22/10/2019
2          3 22/05/2020##22/01/2020##22/10/2019
3          1 25/05/2020##22/07/2019##22/11/2019
3          3 25/05/2020##22/07/2019##22/11/2019
5 rows selected.

Answer 3

我喜欢使用标准的递归查询。这是一种可移植的语法，适用于可能有细微变化的数据库，因此这是一件很有用的学习内容。另一个好处是，我们可以使用简单的字符串函数，而不是正则表达式。

我们可以使用以下递归查询将字符串元素解析为一系列包含相应date:的行

with cte (nbr, dt, dt_new, dt_rest) as (
select nbr, dt, null, dt || '##' from temp
union all
select nbr, dt,
to_date(
substr(dt_rest, 1, instr(dt_rest, '##') - 1)
default null on conversion error,
'dd/mm/yyyy'
),
substr(dt_rest, instr(dt_rest, '##') + 2) 
from cte
where instr(dt_rest, '##') > 0
)
select * from cte 
where dt_new is not null 
order by nbr, dt_new;

从那时起，我们可以简单地汇总并查看每组的最新日期：

with cte (nbr, dt, dt_new, dt_rest ) as (...)
select nbr, dt, max(dt_new) as max_dt_new
from cte
group by nbr, dt
having months_between(sysdate, max(dt_new)) < 15

DB Fiddle上的演示：

NBR|DT|MAX_DT_NEW--：|：-------------------------------|：---------2020年5月22日| 2020年1月22日2020年5月25日| 2019年7月22日#2019年11月22日| 5月20日