Geek asks and answers

Select dynamic row pairs in SQL (PostgreSQL)

My goal is to make a dynamic group of strings (actually a product of TYPE and COLOR)

I don't know if this is possible with a single select query.

But: I want to create a group of rows (A PRODUCT - TYPE and COLOR) according to the column number_per_group, and I want to make this grouping depending on the date order (Order By DATE)

A single product with the number NB_PER_GROUP 2 is excluded from the final result.

Table :

-----------------------------------------------
NUM |  TYPE |   COLOR  | NB_PER_GROUP   | DATE
-----------------------------------------------
0   |  1    |   1      |    2           |  ...
1   |  1    |   1      |    2           |
2   |  1    |   2      |    2           |
3   |  1    |   2      |    2           |
4   |  1    |   1      |    2           |
5   |  1    |   1      |    2           |
6   |  4    |   1      |    3           |
7   |  1    |   1      |    2           |
8   |  4    |   1      |    3           |
9   |  4    |   1      |    3           |
10  |  5    |   1      |    2           |


Results :

------------------------
GROUP_NUMBER   |  NUM  | 
------------------------
0              |   0   |
0              |   1   |
~~~~~~~~~~~~~~~~~~~~~~~~
1              |   2   |
1              |   3   |
~~~~~~~~~~~~~~~~~~~~~~~~
2              |   4   |
2              |   5   |
~~~~~~~~~~~~~~~~~~~~~~~~
3              |   6   |
3              |   8   |
3              |   9   |

If you have another way to solve this problem, I agree with it.

+3
source share
1 answer

How about this?

select max(gn.group_number) group_number, ip.num
from products ip
join (
    select date, type, color, row_number() over (order by date) - 1 group_number
    from (
        select op.num, op.type, op.color, op.nb_per_group, op.date, (row_number() over (partition by op.type, op.color order by op.date) - 1) % nb_per_group group_order
        from products op
    ) sq
    where sq.group_order = 0
) gn
 on ip.type = gn.type
and ip.color = gn.color
and ip.date >= gn.date
group by ip.num
order by group_number, ip.num

, nb_per_group . , , , .

, , nb_per_group; 0 , 0 , nb_per_group.

0, , .

, , , .

0

More articles:


All Articles