时间戳

首页 > 解决方案 > 在 Oracle 中比较查询结果中的字段

oracle - 在 Oracle 中比较查询结果中的字段

问题描述

我在一个场景中从一个特定记录的 FIRSTNAME 和 LASTNAME 相同但 BIRTHDATE 大于或等于 15 年的表中获取所有记录。

考虑我的表看起来像：

_______________________________________________________________________________
| PRIMARY_ID | UNIQUE_ID | FIRSTNAME | LASTNAME | SUFFIX | BIRTHDATE          |
_______________________________________________________________________________
| 12345      | abcd      | john      | collin   | Mr     | 1975-10-01 00:00:00|
| 12345      | cdef      | john      | collin   | Mr     | 1960-10-01 00:00:00|
| 12345      | efgh      | john      | collin   | Mr     | 1975-10-01 00:00:00|
| 12345      | ghij      | john      | collin   | Mr     | 1960-10-01 00:00:00|
| 12345      | aaaa      | john      | collin   | Mr     | 1975-10-01 00:00:00|
| 12345      | bdfs      | john      | collin   | Mr     | 1975-10-01 00:00:00|
| 12345      | asdf      | john      | collin   | Mr     | null               |
| 12345      | dfgh      | john      | collin   | Mr     | null               |
| 23456      | ghij      | jeremy    | lynch    | Mr     | 1982-10-15 00:00:00|
| 23456      | aaaa      | jacob     | lynch    | Mr     | 1945-10-12 00:00:00|
| 23456      | bdfs      | jeremy    | lynch    | Mr     | 1945-10-12 00:00:00|
| 23456      | asdf      | jacob     | lynch    | Mr     | null               |
| 23456      | dfgh      | jeremy    | lynch    | Mr     | null               |
_______________________________________________________________________________

在此表中，对于 PRIMARY_ID 12345，FIRSTNAME 和 LASTNAME 都相同，但 UNIQUE_ID 之间的 BIRTHDATE 差异如果 15 年。所以这个PRIMARY_ID需要被拉出来。其中对于PRIMARY_ID 23456，所有UNIQUE_ID记录的FIRSTNAME不一样，所以一定不能拉出来。

该表可能包含 BIRTHDATE 的 NULL 值，应将其忽略。

这是我到目前为止所尝试的：

SELECT
  /*PARALLEL(16)*/
  PRIMARY_ID,
  UNIQUE_ID,
  FIRSTNAME,
  LASTNAME,
  SUFFIX,
  BIRTHDATE,
  RANK() OVER ( ORDER BY FIRSTNAME, LASTNAME, SUFFIX, BIRTHDATE) "GROUP"
FROM TABLE;

我已要求形成单独的组以按 FIRSTNAME、LASTNAME 和 BIRTHDATE 进行区分。我不知道如何进一步处理这个问题。

有人可以帮忙吗？

注意：BIRTHDATE 字段是 varchar 数据类型，我使用 Oracle 12C。

标签： oracle

解决方案

据我了解，目标是返回共享相同且相隔 15 年以上的primary_id相邻（按字母顺序）的不同集合。据我了解，应该中断比较（并被视为不匹配（否则，primary_id 23456 也将在此处匹配伪相邻+ ）。unique_idfirstnamelastnameNULLbdfsghij

还有其他方法可以做到这一点，但 12c 中可用的一种方法是使用模式匹配。下面是一个例子。该示例仅使用 5478 天的差异来表示 15 年，但如果需要更精确的闰日等，可能会有细微差别。

SELECT DISTINCT PRIMARY_ID
FROM THE_TABLE
    MATCH_RECOGNIZE (
        PARTITION BY PRIMARY_ID
        ORDER BY UNIQUE_ID
        ONE ROW PER MATCH
        AFTER MATCH SKIP PAST LAST ROW
        PATTERN(FIFTEEN_DIFF)
        DEFINE FIFTEEN_DIFF AS
            (FIFTEEN_DIFF.FIRSTNAME = PREV(FIFTEEN_DIFF.FIRSTNAME)
                AND FIFTEEN_DIFF.LASTNAME = PREV(FIFTEEN_DIFF.LASTNAME)
                AND (ABS(EXTRACT( DAY FROM (TO_TIMESTAMP(FIFTEEN_DIFF.BIRTHDATE,'YYYY-MM-DD HH24:MI:SS') - PREV(TO_TIMESTAMP(FIFTEEN_DIFF.BIRTHDATE,'YYYY-MM-DD HH24:MI:SS'))))) >= 5478)));

Result:

  PRIMARY_ID
       12345


1 row selected.

上面的查询执行以下操作：
PARTITIONs 单独查看每个PRIMARY_ID组，

然后ORDERs 通过UNIQUE_ID，因此只比较按字母顺序相邻的记录。

然后将每条记录与最后一条记录进行比较，如果它们共享FIRSTNAME和LASTNAME，并且它们BIRTHDATE的 s 相差 15 年以上，则它们被计为 a MATCH，并返回一条记录以表明这一点。

找到任何匹配后，它会跳到下一行并继续比较。

由于只需要不同的匹配项，因此 aDISTINCT包含在 select 语句中。

编辑：

针对后续问题，添加两个附加示例。

备选方案 1：预过滤NULL 这将带来不同UNIQUE_ID的接近度，给出不同的匹配。

SELECT DISTINCT PRIMARY_ID
FROM (SELECT PRIMARY_ID, UNIQUE_ID, FIRSTNAME, LASTNAME, SUFFIX, BIRTHDATE
      FROM THE_TABLE
      WHERE BIRTHDATE
          IS NOT NULL)
    MATCH_RECOGNIZE (
        PARTITION BY PRIMARY_ID
        ORDER BY UNIQUE_ID
        ONE ROW PER MATCH
        AFTER MATCH SKIP PAST LAST ROW
        PATTERN (FIFTEEN_DIFF)
        DEFINE FIFTEEN_DIFF AS
            (FIFTEEN_DIFF.FIRSTNAME = PREV(FIFTEEN_DIFF.FIRSTNAME)
                AND FIFTEEN_DIFF.LASTNAME = PREV(FIFTEEN_DIFF.LASTNAME)
                AND (ABS(EXTRACT(DAY FROM (TO_TIMESTAMP(FIFTEEN_DIFF.BIRTHDATE , 'YYYY-MM-DD HH24:MI:SS') -
                                           PREV(TO_TIMESTAMP(FIFTEEN_DIFF.BIRTHDATE , 'YYYY-MM-DD HH24:MI:SS'))))) >= 5478)));

结果（现在包括PRIMARY_ID23456，因为删除 NULL 会使两个UNIQUE_IDs 相隔 15 年以上）：

  PRIMARY_ID
       12345
       23456

2 rows selected.

备选方案 2：将 NULL 计数为匹配项

SELECT DISTINCT PRIMARY_ID
FROM THE_TABLE
    MATCH_RECOGNIZE (
        PARTITION BY PRIMARY_ID
        ORDER BY UNIQUE_ID
        ONE ROW PER MATCH
        AFTER MATCH SKIP PAST LAST ROW
        PATTERN (FIFTEEN_DIFF)
        DEFINE FIFTEEN_DIFF AS
            (FIFTEEN_DIFF.FIRSTNAME = PREV(FIFTEEN_DIFF.FIRSTNAME)
                AND FIFTEEN_DIFF.LASTNAME = PREV(FIFTEEN_DIFF.LASTNAME)
                AND ((ABS(EXTRACT(DAY FROM (TO_TIMESTAMP(FIFTEEN_DIFF.BIRTHDATE , 'YYYY-MM-DD HH24:MI:SS') -
                                           PREV(TO_TIMESTAMP(FIFTEEN_DIFF.BIRTHDATE , 'YYYY-MM-DD HH24:MI:SS'))))) >= 5478)
                OR (LEAST(FIFTEEN_DIFF.BIRTHDATE,PREV(FIFTEEN_DIFF.BIRTHDATE)) IS NULL
                        AND COALESCE(FIFTEEN_DIFF.BIRTHDATE,PREV(FIFTEEN_DIFF.BIRTHDATE)) IS NOT NULL))));

结果（这也返回 both PRIMARY_ID，因为NULL现在算作匹配）：

  PRIMARY_ID
       12345
       23456


2 rows selected.

推荐阅读