increasing value with condition on oracle

Question

How to add an increment value (not summarize) with some condition on another column?

I'm using Oracle-like DBMS, named Tibero, for simple example i want to produce this data

ROWNUM GRP_STRT GRP_NO SLBY
1      1        1      1
2      1        1      1
3      1        1      1
4      1        1      1
5      1        1      1
6      1        2      0
7      1        2      0
8      1        3      1
9      1        3      1
10     1        3      1
11     1        4      0
12     1        5      1

Column SLBY is for Buy/Sell code (0=Buy, 1=Sell) then every changing type of transaction, column GRP_NO increasing (but it's not grouping by SLBY column)

SELECT CASE
    WHEN ROWNUM = 1 THEN GRP_NO
    WHEN ROWNUM <> 1 AND SLBY = LAG(SLBY,1) over (ORDER BY ROWNUM) THEN LAG(GRP_STRT,1) over (ORDER BY ROWNUM) - 1
    WHEN ROWNUM <> 1 AND SLBY_DSTN_CD <> LAG(SLBY_DSTN_CD,1) over (ORDER BY ROWNUM) THEN LAG(GRP_STRT,1) over (ORDER BY ROWNUM) + 1
        END TARGET_GROUPING
        , A.* 
FROM SOME_TABLE

I tried with that query but instead of getting what i want like in the picture above, I produced a GRP_NO like 1 1 1 1 1 2 1 1 1 1 2 1 1 1 (first change SLBY only)

Apologies for my bad english and bad explanation, I'll explain more if need further information, thanks for your help!

Answer 1

As far as I understood your problem,

You are trying to calculate GRP_NO from ROWNUM, GRP_STRT, GRP_NO, and SLBY.

I have created the following query for you.

You can check the logic and apply it in your code accordingly:

SELECT
    RN,
    GRP_STRT,
    SUM(CASE
        WHEN PREV_SLBY_DSTN_CD IS NULL
             OR PREV_SLBY_DSTN_CD <> SLBY_DSTN_CD THEN 1
    END) OVER(
    ORDER BY
        RN
    ) AS GRP_NO,
    SLBY_DSTN_CD   AS SLBY
FROM
    (
        SELECT
            RN,
            LAG(SLBY_DSTN_CD) OVER(
                ORDER BY
                    RN
            ) AS PREV_SLBY_DSTN_CD,
            SLBY_DSTN_CD,
            GRP_STRT
        FROM
            (SELECT ROWNUM RN, .... FROM SOME_TABLE) A
    )

This code is to generate the output as shown in question:

ROWNUM GRP_STRT GRP_NO SLBY
1      1        1      1
2      1        1      1
3      1        1      1
4      1        1      1
5      1        1      1
6      1        2      0
7      1        2      0
8      1        3      1
9      1        3      1
10     1        3      1
11     1        4      0
12     1        5      1

Cheers!!