I have a view:

col1

aaa 
aaa  
ccc  
ddd 

How can I add a column that counts when the value is changing?

Example:

col1 value_change_counter
aaa      1
aaa      1
ccc      2
ddd      3

And another case more complex. I am not sure this is possible. I want to count when col1 changes, but reset the counter when col2 changes

Example:

col1 col2 value_change_counter
aaa  ttt          1
aaa  ttt          1
bbb  ttt          2
ccc  yyy          1
ddd  uuu          1
eee  uuu          2
fff  uuu          3

CodePudding user response：

Use the DENSE_RANK analytic function:

SELECT t.*,
       DENSE_RANK() OVER (PARTITION BY col2 ORDER BY col1) AS value_change_counter
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name (col1, col2) AS
  SELECT 'aaa', 'ttt' FROM DUAL UNION ALL
  SELECT 'aaa', 'ttt' FROM DUAL UNION ALL
  SELECT 'bbb', 'ttt' FROM DUAL UNION ALL
  SELECT 'ccc', 'yyy' FROM DUAL UNION ALL
  SELECT 'ddd', 'uuu' FROM DUAL UNION ALL
  SELECT 'eee', 'uuu' FROM DUAL UNION ALL
  SELECT 'fff', 'uuu' FROM DUAL;

Outputs:

fiddle

CodePudding user response：

In SQL, results sets have a non-deterministic ordering (they are unordered) so you cannot rely on the rows being in any particular order and you would need a separate column to give those rows a particular order (if it does not correspond to alphabetically sorting by col1 and/or col2).

If you have such a column, for example:

CREATE TABLE table_name (id, col1, col2) AS
  SELECT  1, 'aaa', 'ttt' FROM DUAL UNION ALL
  SELECT  2, 'aaa', 'ttt' FROM DUAL UNION ALL
  SELECT  3, 'bbb', 'ttt' FROM DUAL UNION ALL
  SELECT  4, 'ccc', 'yyy' FROM DUAL UNION ALL
  SELECT  5, 'ddd', 'uuu' FROM DUAL UNION ALL
  SELECT  6, 'eee', 'uuu' FROM DUAL UNION ALL
  SELECT  7, 'fff', 'uuu' FROM DUAL UNION ALL
  SELECT  8, 'fff', 'ttt' FROM DUAL UNION ALL
  SELECT  9, 'ggg', 'ttt' FROM DUAL UNION ALL
  SELECT 10, 'fff', 'uuu' FROM DUAL;

Then, from Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing and generate the col2 partitions and then use a second pass to generate the orderings within each partition for col1:

SELECT id, col1, col2, value_change_counter
FROM   (
  SELECT *
  FROM   table_name
  MATCH_RECOGNIZE(
    ORDER BY id
    MEASURES
      MATCH_NUMBER() AS mno
    ALL ROWS PER MATCH
    PATTERN (same_col2 )
    DEFINE
      same_col2 AS col2 = FIRST(col2)
  )
)
MATCH_RECOGNIZE(
  PARTITION BY mno
  ORDER BY id
  MEASURES
    MATCH_NUMBER() AS value_change_counter
  ALL ROWS PER MATCH
  PATTERN (same_col1 )
  DEFINE
    same_col1 AS col1 = FIRST(col1)
)

Which outputs:

fiddle