I have a similar question as: Numbering long chains of repeating values in a column of a table in PostgreSQL using window functions , but then for Oracle.
So, I want to rewrite the following PostgreSQL sql to Oracle sql.
select id, col,
count(*) filter(where col is distinct from lag_col) over(order by id) grp
from (
select t.*, lag(col) over(order by id) as lag_col
from mytable t
) t order by id
From Oracle 12, you can use MATCH_RECOGNIZE
:
SELECT id, col, grp
FROM mytable
MATCH_RECOGNIZE(
ORDER BY id
MEASURES MATCH_NUMBER() AS grp
ALL ROWS PER MATCH
PATTERN (same_col+)
DEFINE same_col AS FIRST(col) = col
);
Or, in earlier version you can use the LAG
and SUM
analytic functions:
SELECT id, col,
SUM(has_changed) OVER (ORDER BY id) AS grp
FROM (
SELECT t.*,
CASE LAG(col) OVER (ORDER BY id) WHEN col THEN 0 ELSE 1 END AS has_changed
FROM mytable t
) t
Which, for the sample data:
CREATE TABLE mytable (id, col) AS
SELECT 1, 0 FROM DUAL UNION ALL
SELECT 2, 0 FROM DUAL UNION ALL
SELECT 3, 0 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL UNION ALL
SELECT 6, 1 FROM DUAL UNION ALL
SELECT 7, 0 FROM DUAL UNION ALL
SELECT 8, 0 FROM DUAL UNION ALL
SELECT 9, 1 FROM DUAL UNION ALL
SELECT 10, 0 FROM DUAL;
Both output:
ID | COL | GRP |
---|---|---|
1 | 0 | 1 |
2 | 0 | 1 |
3 | 0 | 1 |
4 | 1 | 2 |
5 | 1 | 2 |
6 | 1 | 2 |
7 | 0 | 3 |
8 | 0 | 3 |
9 | 1 | 4 |
10 | 0 | 5 |
Note: the second method also works in PostgreSQL fiddle.