Oracle SQL:检测连续跨度中的中断
Oracle SQL:检测连续跨度中的中断
提问于 2011-01-26 01:09:44
我有下面的表格，我正在尝试检测那些在其跨度中有中断的产品。
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，因为我们缺少跨度
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开始日期和结束日期不能重叠
EDIT 3一个跨度可以是price_end_date >= price_start_date的任意两个日期。Equal是包含在内的，因为产品可以销售一天。
回答 4
Stack Overflow用户
回答已采纳
发布于 2011-01-26 01:27:41
试试这个(使用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使用Setup的示例
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，以避免数据分布方面的问题。
Stack Overflow用户
发布于 2011-01-26 01:48:21
假设您的表名为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 ;然后，此查询将给出具有“缺失”跨度的所有产品：
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;但正如斯蒂芬妮·佩奇指出的那样，这是一个过早的优化；您不太可能经常在连续跨度中扫描中断。
Stack Overflow用户
发布于 2011-01-26 01:20:59
可以使用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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4796621
复制相关文章
相似问题
腾讯云开发者
