PostgreSQL how to generate a partition row_number() with certain numbers overridden-CodePudding

I have an unusual problem I'm trying to solve with SQL where I need to generate sequential numbers for partitioned rows but override specific numbers with values from the data, while not breaking the sequence (unless the override causes a number to be used greater than the number of rows present).

I feel I might be able to achieve this by selecting the rows where I need to override the generated sequence value and the rows I don't need to override the value, then unioning them together and somehow using coalesce to get the desired dynamically generated sequence value, or maybe there's some way I can utilise recursive.

I've not been able to solve this problem yet, but I've put together a SQL Fiddle which provides a simplified version:

http://sqlfiddle.com/#!17/236b5/5

The desired_dynamic_number is what I'm trying to generate and the generated_dynamic_number is my current work-in-progress attempt.

Any pointers around the best way to achieve the desired_dynamic_number values dynamically?

Update:

I'm almost there using lag:

http://sqlfiddle.com/#!17/236b5/24

CodePudding user response：

Below query gives exact result, but you need to verify with all combinations

select c.*,COALESCE(c.override_as_number,c.act) as final FROM
(
select b.*, dense_rank() over(partition by grouped_by order by grouped_by, actual) as act from
(
select a.*,COALESCE(override_as_number,row_num) as actual FROM  
(
select grouped_by , secondary_order_by  ,      
dense_rank() over ( partition by grouped_by order by grouped_by, secondary_order_by ) as row_num                             
,override_as_number,desired_dynamic_number                                                                                   from fiddle    
) a
) b 
) c ;

column "final" is the result

grouped_by | secondary_order_by | row_num | override_as_number | desired_dynamic_number | actual | act | final 
------------ -------------------- --------- -------------------- ------------------------ -------- ----- -------
 AAA        | a                  |       1 |                  1 |                      1 |      1 |   1 |     1
 AAA        | b                  |       2 |                    |                      2 |      2 |   2 |     2
 AAA        | c                  |       3 |                  3 |                      3 |      3 |   3 |     3
 AAA        | d                  |       4 |                  3 |                      3 |      3 |   3 |     3
 AAA        | e                  |       5 |                    |                      4 |      5 |   4 |     4
 AAA        | f                  |       6 |                    |                      5 |      6 |   5 |     5
 AAA        | g                  |       7 |                999 |                    999 |    999 |   6 |   999
 XYZ        | a                  |       1 |                    |                      1 |      1 |   1 |     1
 ZZZ        | a                  |       1 |                    |                      1 |      1 |   1 |     1
 ZZZ        | b                  |       2 |                    |                      2 |      2 |   2 |     2
(10 rows)

Hope this helps!