0

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
Wouter
  • 3
  • 2

1 Answers1

0

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

fiddle

Note: the second method also works in PostgreSQL fiddle.

MT0
  • 143,790
  • 11
  • 59
  • 117