Reputation: 85
I only found slightly different examples I couldn't adapt to my needs due to my very limited sql skills.
I have a table with 3 revelant columns:
ItemID Date Result
1 1.2.2014 A
5 6.4.2014 B
9 7.4.2014 A
1 8.4.2014 A
1 9.4.2014 A
1 10.4.2014 A
I want to find the Items that had a particular result (let's say A) 3 times consecutively. In the sample above it would be Item 1. The dates are not normally consecutive.
It should work in Oracle SQL.
Many thanks for the help!
Upvotes: 1
Views: 637
Reputation: 85
Thanks to user1578653 I figured it out. Maybe there is a more elegant way to do it, but it worked for about 500000 records in a few seconds:
SELECT ITEM_ID FROM(
SELECT ITEM_ID, DATECOL, RESULT,
LAG(ITEM_ID,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTITEM,
LAG(ITEM_ID,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTITEM2,
LAG(RESULT,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTCODE,
LAG(RESULT,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTCODE2
FROM
RESULTTABLE
)
WHERE
ITEM_ID = LASTITEM
AND ITEM_ID = LASTITEM2
AND RESULT = 'A'
AND RESULT = LASTCODE
AND RESULT = LASTCODE2;
I was looking for a result of 'A' that occured 3 times consecutively in this example.
I'm rather happy, thanks!
Upvotes: 0
Reputation: 49092
SQL> WITH DATA AS(
2 SELECT 1 ITEM_ID, TO_DATE('1.2.2014','DD.MM.YYYY') DT, 'A' RSLT FROM DUAL UNION ALL
3 SELECT 5, TO_DATE('6.4.2014','DD.MM.YYYY') , 'B' RSLT FROM DUAL UNION ALL
4 SELECT 9, TO_DATE('7.4.2014','DD.MM.YYYY') , 'A' RSLT FROM DUAL UNION ALL
5 SELECT 1, TO_DATE('8.4.2014','DD.MM.YYYY') , 'A' RSLT FROM DUAL UNION ALL
6 SELECT 1, TO_DATE('9.4.2014','DD.MM.YYYY') , 'A' RSLT FROM DUAL UNION ALL
7 SELECT 1, TO_DATE('10.4.2014','DD.MM.YYYY') , 'A' RSLT FROM DUAL)
8 SELECT ITEM_ID FROM(
9 SELECT A.*, ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY RSLT) RN
10 FROM DATA A)
11 WHERE RN =3
12 /
ITEM_ID
----------
1
SQL>
Upvotes: 1
Reputation: 5038
Not sure if this is the best way of of achieving it, but it works. I created the following table to put your example data into:
CREATE TABLE DATE_RESULT(
ITEM_ID INT,
DATE_COL DATE,
RESULT_COL VARCHAR2(255 CHAR)
);
Then ran this query:
SELECT ITEM_ID FROM(
SELECT
ITEM_ID,
TO_CHAR(DATE_COL,'DD-MON-YYYY') AS DATE_COL,
RESULT_COL,
LAG(ITEM_ID,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_ITEM_ID,
LAG(ITEM_ID,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_ITEM_ID2,
LAG(TO_CHAR(DATE_COL,'DD-MON-YYYY'),1,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_DATE,
LAG(TO_CHAR(DATE_COL,'DD-MON-YYYY'),2,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_DATE2,
LAG(RESULT_COL,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_RESULT,
LAG(RESULT_COL,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_RESULT2
FROM
DATE_RESULT
)
WHERE
ITEM_ID = LAST_ITEM_ID
AND ITEM_ID = LAST_ITEM_ID2
AND TO_DATE(DATE_COL)-1 = TO_DATE(LAST_DATE)
AND TO_DATE(DATE_COL)-2 = TO_DATE(LAST_DATE2)
AND RESULT_COL = LAST_RESULT
AND RESULT_COL = LAST_RESULT2;
The query uses Oracle's LAG() function to get the values from previous rows. So in this example, LAST_ITEM_ID is the item ID from the previous row, and LAST_ITEM_ID is the item ID from 2 rows previous.
In the WHERE clause I make sure that the ITEM_ID matches the previous two ITEM_IDs and that the RESULT_COL matches the previous two RESULT_COLs. I also make sure that the last two dates were consecutive.
Upvotes: 0