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是包含在内的，因为产品可以销售一天。
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提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4796621
复制相关文章
相似问题
腾讯云开发者
