根据SQL中的时间条件排除特定行Oracle

bqjvbblv 于 2023-04-29 发布在 Oracle

关注(0)|答案(2)|浏览(144)

我真的不明白为什么我的前一个问题被关闭，因为至少有一个用户MT 0正确理解了它，所以它没有看到我没有正确描述我的问题。
他解在这里https://dbfiddle.uk/DZNetwsh
MT 0做得很好，但你的解决方案有一个问题，我提供的记录没有存储在表中，而是在WITH子句中选择，其中有许多表连接m distinct和group by。因此，我在您提供的这行代码中得到一个Ora错误“AND t。ROWID = e.rid“。
我又把唱片贴了一遍，加了一些新的行。在我们的生产表中，有许多日期为01-01-9999的行，这意味着订单仍然有效，就像品牌PPP一样。

CREATE TABLE table_name (brand, type, start_date, end_date) AS
  SELECT 'abc', 'W', DATE '2020-01-01', DATE '2020-06-30' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2020-07-01', DATE '2020-08-31' FROM DUAL UNION ALL
  SELECT 'abc', 'W', DATE '2020-09-01', DATE '2020-09-30' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'A', DATE '2023-07-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'W', DATE '2021-01-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'A', DATE '2019-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'W', DATE '2022-01-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'A', DATE '2023-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'A', DATE '2023-01-02', DATE '2023-09-30' FROM DUAL UNION ALL
  SELECT 'ppp', 'A', DATE '2023-01-01', DATE '9999-01-01' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2022-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2021-09-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2022-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2023-09-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'A', DATE '2023-01-02', DATE '2023-08-31' FROM DUAL;

如果我只得到对我的用例有效的类型A的行，就足够了。
类型W的记录总是具有优先级，这意味着它们总是有效的，并且应该在输出中。如果不可能，则类型A的有效记录也可以。
如果A类型的记录在某个时间点没有单独运行，则它们是无效的。例如，对于品牌mmm或zzz，类型A被类型W的一个记录完全覆盖。对于品牌qqq，其中记录首先被类型W的两个记录部分覆盖，在它们之间没有空间。但是对于品牌uuu，A型的记录将通过，因为它被部分覆盖，但是从1开始有时间框架。7.2022-31.8.2023，其中类型A的记录单独运行，因此它将通过。
在来自用户MT 0的用于品牌PPP的解决方案中存在一个问题。日期为01-01-9999的打开记录将正确通过，但正如我上面提到的，所有W类型的记录都必须通过。
我不知道这个解决方案是否适用于30 mil记录，其中有许多记录的日期为01-01-9999，因为我尝试了可以检查A类型记录的每一天的功能，如果有至少一个W类型记录的匹配时间范围，但这个解决方案需要大量的PL和SQL切换，是最差的方法。另一种解决方案是脚手架，它基于日期范围的天数生成相同的A类记录，但这会生成如此多的行，特别是对于日期为01-01-9999的开放记录。这将耗尽临时表空间。
还有什么办法呢？

oracle

来源：https://stackoverflow.com/questions/76080885/exclude-specific-rows-based-on-time-conditions-in-sql-oracle

2条答案

按热度按时间

lrpiutwd1#

您可以连接所有连续类型W范围，并检查A类型的行是否完全包含在W范围中，并且这些行不被传递，然后传递所有其他行：

WITH continuous_w_ranges (brand, start_date, end_date) AS (
  SELECT brand, start_date, end_date
  FROM   (
    SELECT *
    FROM   table_name
    WHERE type = 'W'
  )
  MATCH_RECOGNIZE(
    PARTITION BY brand
    ORDER BY start_date, end_date
    MEASURES
      FIRST(start_date) AS start_date,
      MAX(end_date)     AS end_date
    PATTERN ( continuing_range* next_range )
    DEFINE
      continuing_range AS MAX(end_date) + 1 >= NEXT(start_date) 
  )
)
SELECT brand,
       type,
       start_date,
       end_date,
       CASE
       WHEN type = 'A'
       AND  EXISTS(
              SELECT 1
              FROM   continuous_w_ranges w
              WHERE  w.brand = a.brand
              AND    w.start_date <= a.start_date
              AND    a.end_date <= w.end_date
            )
       THEN 'not pass'
       ELSE 'pass'
       END AS pass
FROM   table_name a
ORDER BY brand, start_date;

其中，对于样本数据：

CREATE TABLE table_name (brand, type, start_date, end_date) AS
  SELECT 'abc', 'W', DATE '2020-01-01', DATE '2020-06-30' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2020-07-01', DATE '2020-08-31' FROM DUAL UNION ALL
  SELECT 'abc', 'W', DATE '2020-09-01', DATE '2020-09-30' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'A', DATE '2023-07-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'W', DATE '2021-01-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'A', DATE '2019-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'W', DATE '2022-01-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'A', DATE '2023-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'A', DATE '2023-01-02', DATE '2023-09-30' FROM DUAL UNION ALL
  SELECT 'ppp', 'A', DATE '2023-01-01', DATE '9999-01-01' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2022-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2021-09-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2022-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2023-09-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'A', DATE '2023-01-02', DATE '2023-08-31' FROM DUAL;

输出：
| 品牌介绍|类型|开始日期|结束日期|通过|
| - -------------|- -------------|- -------------|- -------------|- -------------|
| ABC|W型|2020 - 01 - 01 00：00：00|2020 - 06 - 30 00：00：00|传球|
| ABC|一个|2020 - 07 - 01 00：00：00|2020 - 08 - 31 00：00：00|传球|
| ABC|W型|2020 - 09 - 01 00：00：00|2020 - 09 - 30 00：00：00|传球|
| 嗯|W型|2019 - 01 - 21 00：00：00|2023 - 03 - 31 00：00：00|传球|
| 嗯|W型|2019 - 04 - 01 00：00：00|2023 - 12 - 31 00：00：00|传球|
| 嗯|一个|2023 - 07 - 01 00：00：00|2023 - 12 - 31 00：00：00|不通过|
| 人民党|W型|2021 - 09 - 01 00：00：00|2021 - 12 - 31 00：00：00|传球|
| 人民党|W型|2019 - 01 - 22 00：00：00|2024 - 12 - 31 00：00：00|传球|
| 人民党|一个|2019 - 01 - 21 00：00：00|2019 - 01 - 01 00：00：00|传球|
| qqq|W型|2019 - 01 - 21 00：00：00|2023 - 03 - 31 00：00：00|传球|
| qqq|一个|2023 - 01 - 02 00：00：00|2019 - 09 - 23 00：00：00|不通过|
| qqq|W型|2019 - 04 - 01 00：00：00|2023 - 12 - 31 00：00：00|传球|
| 乌乌|W型|2019 - 01 - 22 00：00：00|2019 - 06 - 22 00：00：00|传球|
| 乌乌|一个|2019 - 01 - 22 00：00：00|2019 - 08 - 21 00：00：00|传球|
| 乌乌|W型|2023 - 09 - 01 00：00：00|2023 - 12 - 31 00：00：00|传球|
| xyz|一个|2019 - 01 - 01 00：00：00|2024 - 12 - 31 00：00：00|传球|
| xyz|W型|2019 - 01 - 21 00：00：00|2021 - 12 - 31 00：00：00|传球|
| 兹兹|W型|2019 - 01 - 22 00：00：00|2023 - 12 - 31 00：00：00|传球|
| 兹兹|一个|2019 - 01 - 21 00：00：00|2019 - 06 - 22 00：00：00|不通过|
fiddle

赞(0）回复(0）举报 2023-04-29

txu3uszq2#

ROWID是一个伪列，它是对表中某行的唯一引用（实际上是一个指针）。如果你有一个没有绑定到表的结果集，并且不能使用ROWID，那么你所需要做的就是用一行的任何其他唯一标识符来替换它;如果不存在，则使用ROW_NUMBER解析函数生成一个。
例如：

WITH table_name (brand, type, start_date, end_date) AS (
  SELECT 'abc', 'W', DATE '2020-01-01', DATE '2020-06-30' FROM DUAL UNION ALL
  SELECT 'abc', 'A', DATE '2020-07-01', DATE '2020-08-31' FROM DUAL UNION ALL
  SELECT 'abc', 'W', DATE '2020-09-01', DATE '2020-09-30' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'mmm', 'A', DATE '2023-07-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'W', DATE '2021-01-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'xyz', 'A', DATE '2019-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'W', DATE '2022-01-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'zzz', 'A', DATE '2023-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-01-01', DATE '2023-03-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'W', DATE '2023-04-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'qqq', 'A', DATE '2023-01-02', DATE '2023-09-30' FROM DUAL UNION ALL
  SELECT 'ppp', 'A', DATE '2023-01-01', DATE '9999-01-01' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2022-01-01', DATE '2024-12-31' FROM DUAL UNION ALL
  SELECT 'ppp', 'W', DATE '2021-09-01', DATE '2021-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2022-01-01', DATE '2023-06-30' FROM DUAL UNION ALL
  SELECT 'uuu', 'W', DATE '2023-09-01', DATE '2023-12-31' FROM DUAL UNION ALL
  SELECT 'uuu', 'A', DATE '2023-01-02', DATE '2023-08-31' FROM DUAL
),
data (brand, type, start_date, end_date, rn) AS (
  SELECT brand,
         type,
         start_date,
         end_date + 1,
         ROW_NUMBER() OVER (ORDER BY brand, start_date)
  FROM   table_name t
)
SELECT brand,
       type,
       start_date,
       end_date - 1 AS end_date,
       CASE
       WHEN EXISTS(
         SELECT 1
         FROM   (
           SELECT brand,
                  type,
                  dt AS start_date,
                  CASE
                  WHEN SUM(CASE type WHEN 'W' THEN start_end END)
                         OVER (PARTITION BY brand ORDER BY dt) > 0
                  THEN LAST_VALUE(CASE type WHEN 'W' THEN rn END)
                         IGNORE NULLS OVER (PARTITION BY brand ORDER BY dt)
                  WHEN SUM(CASE type WHEN 'A' THEN start_end END)
                         OVER (PARTITION BY brand ORDER BY dt) > 0
                  THEN LAST_VALUE(CASE type WHEN 'A' THEN rn END)
                         IGNORE NULLS OVER (PARTITION BY brand ORDER BY dt)
                  END AS rn,
                  LEAD(dt) OVER (PARTITION BY brand ORDER BY dt) AS end_date,
                  SUM(CASE type WHEN 'W' THEN start_end END)
                    OVER (PARTITION BY brand ORDER BY dt) AS is_w,
                  SUM(CASE type WHEN 'A' THEN start_end END)
                    OVER (PARTITION BY brand ORDER BY dt) AS is_a
           FROM   data
           UNPIVOT (dt FOR start_end IN (start_date AS 1, end_date AS -1))
         ) e
         WHERE  ( is_w > 0 OR is_a > 0 )
         AND    start_date < end_date
         AND    t.rn = e.rn
       )
       THEN 'pass'
       ELSE 'not pass'
       END AS pass
FROM   data t

其输出：
| 品牌介绍|类型|开始日期|结束日期|通过|
| --------------|--------------|--------------|--------------|--------------|
| ABC|W型|2020-01-01 00：00：00|2020-06-30 00：00：00|传球|
| ABC|一个|2020-07-01 00：00：00|2020-08-31 00：00：00|传球|
| ABC|W型|2020-09-01 00：00：00|2020-09-30 00：00：00|传球|
| 嗯|W型|2019 -01- 21 00：00：00|2023-03-31 00：00：00|传球|
| 嗯|W型|2019 -04-01 00：00：00|2023-12-31 00：00：00|传球|
| 嗯|一个|2023-07-01 00：00：00|2023-12-31 00：00：00|不通过|
| 人民党|W型|2021-09-01 00：00：00|2021-12-31 00：00：00|传球|
| 人民党|W型|2019 -01- 22 00：00：00|2024-12-31 00：00：00|传球|
| 人民党|一个|2019 -01- 21 00：00：00|2019 -01-01 00：00：00|传球|
| qqq|W型|2019 -01- 21 00：00：00|2023-03-31 00：00：00|传球|
| qqq|一个|2019 -01- 22 00：00：00|2019 -09- 23 00：00：00|不通过|
| qqq|W型|2019 -04-01 00：00：00|2023-12-31 00：00：00|传球|
| 乌乌|W型|2019 -01- 22 00：00：00|2019 -06- 22 00：00：00|传球|
| 乌乌|一个|2019 -01- 22 00：00：00|2019 -08- 21 00：00：00|传球|
| 乌乌|W型|2023-09-01 00：00：00|2023-12-31 00：00：00|传球|
| xyz|一个|2019-01-01 00：00：00|2024-12-31 00：00：00|传球|
| xyz|W型|2019 -01- 21 00：00：00|2021-12-31 00：00：00|传球|
| 兹兹|W型|2019 -01- 22 00：00：00|2023-12-31 00：00：00|传球|
| 兹兹|一个|2019 -01- 21 00：00：00|2019 -06- 22 00：00：00|不通过|
fiddle

赞(0）回复(0）举报 2023-04-29

我来回答

根据SQL中的时间条件排除特定行Oracle

2条答案

相关问题

热门标签

最新问答