logo标签列表

Oracle SQL：检测连续间隔的中断

sqloracle
7
我有下面这张表格，我正在尝试检测其中产品跨度的中断。
Product     | unit_Cost | price start date |    price end date
--------------------------------------------------------------------------
product 1     15.00         01/01/2011      03/31/2011
product 1     15.00         04/01/2011      06/31/2011
product 1     15.00         07/01/2011      09/31/2011
product 1     15.00         10/01/2011      12/31/2011

product 2     10.00         01/01/2011      12/31/2011

product 3     25.00         01/01/2011      06/31/2011
product 3     25.00         10/01/2011      12/31/2011

这里我希望它返回product3，因为我们缺少span标签

07/01/2011 - 09/31/2011

有什么想法吗？

编辑：Oracle版本：10g

Create Table Statement

CREATE TABLE Sandbox.TBL_PRODUCT
(
  PRODUCT_ID        VARCHAR2(13 BYTE),   
  PRODUCT           VARCHAR2(64 BYTE),
  UNIT_COST         NUMBER,
  PRICE_START_DATE  DATE,
  PRICE_END_DATE    DATE
)

编辑 2 开始日期和结束日期不能重叠

编辑 3 跨度可以是任意两个日期，只要price_end_date>= price_start_date。包括相等的情况，因为产品可能打折一天。

1
我们需要您的查询，以及涉及到的表的“CREATE TABLE”语句，才能帮助您。还有Oracle的版本...为什么人们会点赞一个不完整的问题？ - OMG Ponies
开始日期和结束日期可以重叠吗？ - DCookie
你总是只关注一年的数据吗？如果我只有一年中的前六个月的数据，而年底的数据没有，这种情况是否可行？例如，假设产品3只有第一条记录，应该报告吗？ - DCookie
5个回答
2
尝试使用LEAD分析函数来实现以下内容：
SELECT *
  FROM (
                SELECT a.*, LEAD(price_start_date,1,NULL) OVER(PARTITION BY product ORDER BY price_end_date) next_start_date 
         FROM Product a
       )
WHERE (price_end_date + 1)<> next_start_date

设置示例

        CREATE TABLE PRODUCT
          (
            PRODUCT   VARCHAR2(100 BYTE),
            UNIT_COST NUMBER,
            START_DATE DATE,
            END_DATE DATE
          );

        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('01/01/2011','MM/DD/RRRR'),TO_DATE('03/31/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('04/01/2011','MM/DD/RRRR'),TO_DATE('06/30/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('07/01/2011','MM/DD/RRRR'),TO_DATE('09/30/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 1','15.00',TO_DATE('10/01/2011','MM/DD/RRRR'),TO_DATE('12/31/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 2','10.00',TO_DATE('01/01/2011','MM/DD/RRRR'),TO_DATE('12/31/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 3','25.00',TO_DATE('01/01/2011','MM/DD/RRRR'),TO_DATE('06/30/2011','MM/DD/RRRR'));
        INSERT INTO Product VALUES('product 3','25.00',TO_DATE('10/01/2011','MM/DD/RRRR'),TO_DATE('12/31/2011','MM/DD/RRRR'));

SELECT *
  FROM (
                SELECT a.*, LEAD(start_date,1,NULL) OVER(PARTITION BY product ORDER BY start_date) next_start_date 
                 FROM Product a
              )
WHERE (end_date + 1)<> next_start_date

编辑:更新查询以考虑下一个start_date和当前end_date，以避免数据分布问题。

你为什么要在开始日期上加3个月？你是假设数据按季度组织的吗？我不确定这种假设是否合理。假设产品3在缺失的间隙中有一个以上的记录呢？你的查询仍然会将其报告为缺失。我认为你的方法很好，只需要微调一下。我认为你应该在每个end_date上加一天，并将其与next_start_date进行比较。 - DCookie
@DCookie：是的，我假设数据是按季度组织的。由于OP谈到了时间跨度，应该有一个预定义的时间段可以使用。如果OP能提供更多关于时间跨度的信息，查询就可以轻松地进行调整以适应这些跨度。 - Chandu
除非您知道数据不是以这种方式组织的，因为产品2整年只有一条记录，而产品3半年有一条记录。 - DCookie
插入到产品中的值为('product 4','35.00',TO_DATE('01/01/2011','MM/DD/RRRR'),TO_DATE('06/30/2011','MM/DD/RRRR'))； 插入到产品中的值为('product 4','35.00',TO_DATE('7/01/2011','MM/DD/RRRR'),TO_DATE('12/31/2011','MM/DD/RRRR'))；即使它不是，也会被检测为断点，我将更新问题。 - Scott
也许更好的做法是写明下一个开始日期必须在结束日期之后的那一天？ - araqnid
显示剩余2条评论
1

您还可以使用这种技术。它使用内部查询（chronological_record）为TBL_PRODUCT表中的每个记录分配一个排名（在每个product内按start_date排序）。

WITH
  chronological_record AS
  (
    SELECT
      product,
      unit_cost,
      start_date,
      end_date,
      (DENSE_RANK() OVER (PARTITION BY product ORDER BY start_date))
          AS chronological_order
    FROM
      TBL_PRODUCT
  )

SELECT
  earlier.product,
  (earlier.end_date + 1) AS missing_period_start_date,
  (later.start_date - 1) as missing_period_end_date
FROM
  CHRONOLOGICAL_RECORD earlier
  INNER JOIN
  CHRONOLOGICAL_RECORD later
    ON
        earlier.product = later.product
      AND
        (earlier.chronological_order + 1) = later.chronological_order
WHERE
  (earlier.end_date + 1) <> later.start_date

在您的示例中，子查询（chronological_record）将产生类似于以下内容： 
产品     | 单位成本 | 开始日期   | 结束日期    | 时间顺序
--------------------------------------------------------------------------
产品1    15.00      01/01/2011   03/31/2011    1
产品1    15.00      04/01/2011   06/31/2011    2
产品1    15.00      07/01/2011   09/31/2011    3
产品1    15.00      10/01/2011   12/31/2011    4
产品2    10.00      01/01/2011   12/31/2011    1
产品3    25.00      01/01/2011   06/31/2011    1
产品3    25.00      10/01/2011   12/31/2011    2
主查询的INNER JOIN有效地将早期记录与它们的下一个（按时间顺序）记录匹配。
当然可行，但与使用lead(...)或lag(...)相比似乎有些绕路？ - araqnid
@araqnid：说实话，今天之前我不知道LEAD或LAG。谢谢你的提示！ - Adam Paynter
1
假设您的表名为products，开始日期列名为s，结束日期列名为e:
create view max_interval as 
select product, 
max(e) - min(s) as max_interval 
from products group by product;


create view total_days as 
select product, 
sum( e - s ) + count(product) - 1 as total_days 
from products group by product  ;

然后这个查询会给你所有带有“missing”跨度的产品：

select a.*, b.*
from max_interval a 
left outer join total_days b 
on (a.product = b.product)
where a.max_interval <> b.total_days;

由于两个视图中的 group by 子句相同，因此它们可以合并为一个查询，尽管这可能会使解决方案变得不太清晰：

select product, 
max(e) - min(s) as max_interval, 
sum( e - s ) + count(product) - 1 as total_days 
from products group by product  
having max(e) - min(s) <> sum( e - s ) + count(product) - 1;

但正如Stephanie Page所指出的那样，这是一种过早的优化；你很少会经常扫描连续跨度中的断点。
2
有趣的方法，尽管这意味着需要两次扫描产品表。不过它们不能合并成一个查询吗？ - araqnid
2
你见过很多亿万行的产品表格吗？那么对于完全缓存的表格进行 TWO 次扫描有什么大不了的呢？ "Gap" 分析会被运行多少次？更新 "product" 表格的频率是多少？ - Stephanie Page
0

您可以使用 exists 子句来过滤存在先前行的行，而使用 not exist 子句则可找到上一行未在当前行加一天后结束的行。例如：

select  *
from    TBL_PRODUCT t1
where   exists
        (
        select  *
        from    TBL_PRODUCT t2
        where   t2.PRODUCT = t1.PRODUCT
                and t2.PRICE_END_DATE < t1.PRICE_START_DATE
        )
        and not exists
        (
        select  *
        from    TBL_PRODUCT t3
        where   t3.PRODUCT = t1.PRODUCT
                and t3.PRICE_END_DATE + 1 = t1.PRICE_START_DATE
        );

这将打印：

PRODUCT          UNIT_COST PRICE_STA PRICE_END
----------------------- ---------- --------- ---------
product 3           25 01-OCT-11 31-DEC-11
0

假设您在示例集中修正了错误的日期，您可以对范围进行一些数学比较：

SELECT PRODUCT
FROM Sandbox.TBL_PRODUCT
HAVING SUM(PRICE_END_DATE - PRICE_START_DATE + 1) < MAX(PRICE_END_DATE) - MIN(PRICE_START_DATE) + 1
GROUP BY PRODUCT

这将返回：

PRODUCT                                                                         
-----------------
product 3                                                                       
1 row selected
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接