sql - 带有子字符串的棘手 SQL

Question

我有一个带有 varchar 字段的表 (postgres)，其内容结构如下:

".. John;Smith;uuid=7c32e9e1-e29e-4211-b11e-e20b2cb78da9 .."

uuid 可以出现在多个记录中。但根据业务规则，[givenname];[surname] 的组合不得超过一种。

也就是说，如果表中存在上面的 John Smith 示例，那么如果 uuid 7c32e9e1.. 出现在任何其他记录中，则该记录中的字段大多数还包含“.. John;Smith; ..”

问题是，由于某些错误，违反了此业务规则。而且我想知道表中有多少行包含一个 uuid，这样它出现在多个地方，具有 [givenname];[surname] 的不同组合。

如果有人可以帮助我使用 SQL 来完成此任务，我将不胜感激。

Answer 1

使用正则表达式从字符串中提取 UUID 和名称。然后按 UUID 聚合并计算不同的名称或比较最小和最大名称:

select
  substring(col, 'uuid=([[:alnum:]]+)') as uuid,
  string_agg(distinct substring(col, '([[:alnum:]]+;[[:alnum:]]+);uuid'), ' | ') as names
from mytable
group by substring(col, 'uuid=([[:alnum:]]+)')
having count(distinct substring(col, '([[:alnum:]]+;[[:alnum:]]+);uuid')) > 1;

演示:https://dbfiddle.uk/?rdbms=postgres_12&fiddle=907a283a754eb7427d4ffbf50c6f0028

如果你只想数:

select
 count(*) as cnt_uuids,
 sum(num_names) as cnt_names,
 sum(num_rows) as cnt_rows
from
(
 select
 count(*) as num_rows,
 count(distinct substring(col, '([[:alnum:]]+;[[:alnum:]]+);uuid')) as num_names
 from mytable
 group by substring(col, 'uuid=([[:alnum:]]+)')
 having count(distinct substring(col, '([[:alnum:]]+;[[:alnum:]]+);uuid')) > 1
) flaws;

但正如已经提到的:这不是数据库的使用方式。