I have a table with periods and values
I am trying to eliminate all pre-0 results when a 0 occurs in the table. I tried to apply some filters but failed
WITH PERIODS AS
(
SELECT TO_DATE('2020-01-01', 'YYYY-MM-DD') PERIOD, 1 value FROM dual UNION
SELECT TO_DATE('2020-01-02', 'YYYY-MM-DD') PERIOD, 2 value FROM dual UNION
SELECT TO_DATE('2020-01-03', 'YYYY-MM-DD') PERIOD, 3 value FROM dual UNION
SELECT TO_DATE('2020-01-04', 'YYYY-MM-DD') PERIOD, 4 value FROM dual UNION
SELECT TO_DATE('2020-01-05', 'YYYY-MM-DD') PERIOD, 5 value FROM dual UNION
SELECT TO_DATE('2020-01-06', 'YYYY-MM-DD') PERIOD, 0 value FROM dual UNION
SELECT TO_DATE('2020-01-07', 'YYYY-MM-DD') PERIOD, 6 value FROM dual UNION
SELECT TO_DATE('2020-01-08', 'YYYY-MM-DD') PERIOD, 7 value FROM dual UNION
SELECT TO_DATE('2020-01-09', 'YYYY-MM-DD') PERIOD, 8 value FROM dual UNION
SELECT TO_DATE('2020-01-10', 'YYYY-MM-DD') PERIOD, 9 value FROM dual
)
select * from PERIODS
full result set
01-JAN-20 1
02-JAN-20 2
03-JAN-20 3
04-JAN-20 4
05-JAN-20 5
06-JAN-20 0
07-JAN-20 6
08-JAN-20 7
09-JAN-20 8
10-JAN-20 9
Expected result
07-JAN-20 6
08-JAN-20 7
09-JAN-20 8
10-JAN-20 9
Another option (requires two table scans, though) would be
<snip>
23 SELECT *
24 FROM PERIODS
25 WHERE period > (SELECT period
26 FROM periods
27 WHERE VALUE = 0)
28 ORDER BY VALUE;
PERIOD VALUE
---------- ----------
2020-01-07 6
2020-01-08 7
2020-01-09 8
2020-01-10 9
SQL>
From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern matching:
SELECT *
FROM PERIODS
MATCH_RECOGNIZE(
ORDER BY period
ALL ROWS PER MATCH
PATTERN ( non_zero+ $ )
DEFINE non_zero AS value != 0
)
and, in earlier versions, you can use:
SELECT period, value
FROM (
SELECT period, value,
COUNT(CASE value WHEN 0 THEN 1 END) OVER (
ORDER BY period ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS num_zero
FROM periods
)
WHERE num_zero = 0
Which, for the sample data:
CREATE TABLE PERIODS (period, value) AS
SELECT DATE '2020-01-01', 1 FROM DUAL UNION ALL
SELECT DATE '2020-01-02', 2 FROM DUAL UNION ALL
SELECT DATE '2020-01-03', 3 FROM DUAL UNION ALL
SELECT DATE '2020-01-04', 4 FROM DUAL UNION ALL
SELECT DATE '2020-01-05', 5 FROM DUAL UNION ALL
SELECT DATE '2020-01-06', 0 FROM DUAL UNION ALL
SELECT DATE '2020-01-07', 6 FROM DUAL UNION ALL
SELECT DATE '2020-01-08', 7 FROM DUAL UNION ALL
SELECT DATE '2020-01-09', 8 FROM DUAL UNION ALL
SELECT DATE '2020-01-10', 9 FROM DUAL;
Both output:
| PERIOD | VALUE |
|---|---|
| 2020-01-07 00:00:00 | 6 |
| 2020-01-08 00:00:00 | 7 |
| 2020-01-09 00:00:00 | 8 |
| 2020-01-10 00:00:00 | 9 |
fiddle