Oracle SQL: Detecting breaks in continual spans
I have the following table and I'm trying to detect products that have a break in its spans.
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
So here I want it to report back product3 because we are missing the span
07/01/2011 - 09/31/2011
Any ideas on how I can do this?
EDIT: Oracle Ver: 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
)
EDIT 2 start dates and end dates cannot overlap
EDIT 3 a span can be any two dates as long as price_end_date >= price_start_date. Equal is included since a product can be on sale for one day.
Try this (using LEAD analytic function):
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
Example with 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
EDIT :Updated the query to consider the next start_date and current end_date to avoid issues with the distribution of the data.
You could also use this technique. It uses an inner query ( chronological_record ) to assign a rank to each record in the TBL_PRODUCT table (the rank being sorted on start_date within each product ).
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
In your example, the subquery ( chronological_record ) would yield something like this:
Product | unit_Cost | start date | end date | chronological_order -------------------------------------------------------------------------- product 1 15.00 01/01/2011 03/31/2011 1 product 1 15.00 04/01/2011 06/31/2011 2 product 1 15.00 07/01/2011 09/31/2011 3 product 1 15.00 10/01/2011 12/31/2011 4 product 2 10.00 01/01/2011 12/31/2011 1 product 3 25.00 01/01/2011 06/31/2011 1 product 3 25.00 10/01/2011 12/31/2011 2
The main query's INNER JOIN effectively matches earlier records up with their next (chronologically speaking) records.
Assuming your table is called products , your start date column is named s and your end date column is named 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 ;
Then this query gives you all products with "missing" spans:
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;
Since the group by is the same in both views, this of course can be combined into a single query, albeit making the solution a bit less clear:
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;
But as Stephanie Page points out, that's a premature optimization; it's unlikely that you'll be scanning for breaks in continual spans all that often.
链接地址: http://www.djcxy.com/p/50268.html上一篇: javascript array.sort具有未定义的值
下一篇: Oracle SQL：检测连续跨度的中断