Alternative Oracle analysis function over (partition by ...) __ function

Alternative use of Oracle profiling functions:

Over (partition by ...) in the analysis function using case when;

Cases:

Sql> with Pctest as (
  2  Select 1 IDs, ' A1 ' name from dual
  3  Union
  4  SELECT 1 IDs, ' A2 ' name from  Dual
  5  Union
  6  Select 1 ID, ' A3 ' name from dual
  7  Union
  8  Select 2 ID, ' B1 ' name from Dual
  9  Union  Select 2 ID, ' B1 ' name from dual  Union  Select 2 ID, ' B2 ' n Ame from dual  the Union  Select 3 ID, ' B1 ' name from dual  Union  Select 4 ID, ' C1 ' name from dual  Union  SELECT 4 ID, ' C2 ' name from dual  SELECT * from Pctest  ;

        ID NAME
--------------
         1 A1
         1 A2
         1 A3
         2 B1
         2 b2
         3 B1
         4 C1
         4 C2

8 rows S Elected

Do not use case when effect:

Sql> with Pctest as (
  2  Select 1 IDs, ' A1 ' name from dual
  3  Union
  4  SELECT 1 IDs, ' A2 ' name from  Dual
  5  Union
  6  Select 1 ID, ' A3 ' name from dual
  7  Union
  8  Select 2 ID, ' B1 ' name from Dual
  9  Union  Select 2 ID, ' B1 ' name from dual  Union  Select 2 ID, ' B2 ' n Ame from dual  the Union  Select 3 ID, ' B1 ' name from dual  Union  Select 4 ID, ' C1 ' name from dual  Union  SELECT 4 ID, ' C2 ' name from dual  Select Id,row_number ()
 over  (partition by ID-ID) as RN from  pctest  ;

        ID         RN
--------------------
         1          1
         1          2
         1          3 2 1 2 2
         3          1
         4          1
         4          2

8 rows selected

Use the case-when effect (cases when you have a numeric example ID):

When you use the equals number in the case, you can also specify any other conditional expression in the case, and you can achieve the specific grouping effect you want.

Sql> with Pctest as (
  2  Select 1 IDs, ' A1 ' name from dual
  3  Union
  4  SELECT 1 IDs, ' A2 ' name from  Dual
  5  Union
  6  Select 1 ID, ' A3 ' name from dual
  7  Union
  8  Select 2 ID, ' B1 ' name from Dual
  9  Union  Select 2 ID, ' B1 ' name from dual  Union  Select 2 ID, ' B2 ' n Ame from dual  the Union  Select 3 ID, ' B1 ' name from dual  Union  Select 4 ID, ' C1 ' name from dual  Union  SELECT 4 ID, ' C2 ' name from dual  Select Id,row_number () (partition by case when  id=1 then 1 to  id=2 then 2 end order by ID) as RN From Pctest  ;

Results:

ID RN

---------- ----------

         1          1     a group of---- id=1
         1           2
         1           3
         2           a grouping of 1 ---- id=2
          2          2
          3          1 ----id= another grouping
          4          2
          4          3

8 Rows selected