Сброс счетчика sql

Question

Есть подобная таблица

table(
user_id bigint,
value uint,
date date
)

select *,
       row_number() OVER (PARTITION BY user_id ORDER BY date)
from table
where date_trunc('month', date) = date_trunc('month', current_date)
order by user_id, date desc;

Как добиться сбрасывания row_number() на 1, если value < определенного значения (пусть для наглядности будет 1000) ?

Пробовал в порядке бреда через

CASE WHEN value < 1000 THEN 1
            ELSE row_number() OVER (PARTITION BY user_id ORDER BY date )
        END

Само собой счетчик на эти строки ставится единица, но дальше продолжает считать в старом порядке.

UPD Пример есть набор

user_id; value; date;
2;   1100;      2021-03-25 18:30:04;
2;   99;      2021-03-03 18:29:04;
2;   999;      2021-03-03 18:28:04;
2;   44;      2021-03-02 20:30:04;
2;   99;      2021-03-02 18:33:04;
2;   1000;    2021-03-02 18:30:04;
2;   444;     2021-03-01 17:30:04;
2;   999;     2021-03-01 16:30:04; 
2;   2323;   2021-02-10 21:19:05;
2;   1314;   2021-01-28 21:19:05;
2;   10000; 2021-01-25 20:30:11;

Должно быть

user_id; value; date; row_number
2;   1100;      2021-03-25 18:30:04;    2 
2;   99;      2021-03-03 18:29:04;    1
2;   999;      2021-03-03 18:28:04;    1
2;   44;      2021-03-02 20:30:04;    1
2;   99;      2021-03-02 18:33:04;    1
2;   1000;    2021-03-02 18:30:04;    2
2;   444;     2021-03-01 17:30:04;    1
2;   999;     2021-03-01 16:30:04;   1
2;   2323;   2021-02-10 21:19:05;   3
2;   1314;   2021-01-28 21:19:05;   2
2;   10000; 2021-01-25 20:30:11;   1

При

    CASE WHEN value < 1000 THEN 1
                ELSE row_number() OVER (PARTITION BY user_id,CASE WHEN value < 1000 THEN 1 ELSE 0 END  ORDER BY date )
END

получается

user_id; value; date; row_number
2;   1100;      2021-03-25 18:30:04;    5 
2;   99;      2021-03-03 18:29:04;    1
2;   999;      2021-03-03 18:28:04;    1
2;   44;      2021-03-02 20:30:04;    1
2;   99;      2021-03-02 18:33:04;    1
2;   1000;    2021-03-02 18:30:04;    4
2;   444;     2021-03-01 17:30:04;    1
2;   999;     2021-03-01 16:30:04;   1
2;   2323;   2021-02-10 21:19:05;   3
2;   1314;   2021-01-28 21:19:05;   2
2;   10000; 2021-01-25 20:30:11;   1

Answer 1

Ну если делать "влоб", то

WITH RECURSIVE
cte1 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY `date`) rn
          FROM test ),
cte2 AS ( SELECT *, 1 cnt
          FROM cte1 
          WHERE rn = 1
        UNION ALL
          SELECT cte1.*, CASE WHEN cte1.value > 1000 THEN cte2.cnt + 1 ELSE 1 END
          FROM cte1
          JOIN cte2 ON cte2.rn = cte1.rn - 1 )
SELECT * FROM cte2 ORDER BY `date` DESC;

fiddle

БЛОГ НА HUSL

Сброс счетчика sql

Ответы (1 шт):