Home > Software engineering >  Postgres Get all possible column combinations
Postgres Get all possible column combinations

Time:08-24

So I got a View of items in Postgres that looks like this:

id brand_id category_id color_id material_id pattern_id style_id
1 100 101 100 101 null 101
1 100 101 101 102 null 102
2 101 101 102 103 102 null
2 101 101 102 103 103 null
3 105 101 null 103 104 null
4 101 101 102 103 103 105

I need all DISTINCT possible combinations (excluding item_id) but forming groups that not always will include all columns.

Example of combinations for the First Row (but I need the same for all items without getting duplicated groups):

brand_id category_id color_id material_id pattern_id style_id
100 null null null null null
null 101 null null null null
null null 100 null null null
null null null 101 null null
null null null null null 101
100 101 null null null null
100 null 100 null null null
100 null null 101 null null
100 null null 101 null null
100 null null 101 null 101
100 101 100 null null null
100 101 null 101 null null
100 101 null null null 101

I tried a CROSS JOIN with the original categories tables but I get a enormous table of combinations and also I don't get the groups of 1, 2, 3, 4 or 5 categories, I get the combinations including ALL six categories

CodePudding user response:

You can use CUBE subclause for GROUP BY, which produces the following output for your first row:


    select 
100 brand_id,   101 category_id,    100 color_id,   
101 material_id,    null pattern_id,    101 style_id
    group by
    cube (brand_id, category_id,    color_id,   material_id ,pattern_id ,style_id);
brand_id category_id color_id material_id pattern_id style_id
100 100 101 101
100 101 101
101 101
101
100 100
100 101 100 101
100 100 101
100 100 101
101 100 101
101 100 101
101 101 101
101 100 101 101
100 101 100
100 101 100 101
101
100 101
100 100 101
100 101
100 101 101
100 100 101 101
101 101
101 100 101
101 100 101 101
101
101 101
100 101 100 101 101
100
100 101
100 101 100 101
101
101 101
101 100 101
100 101
100 101
100 101 101
101 101
101 101
101 101 101
100 101 101 101
101 100
101 100
100 101 100
100
100 101
100 101 101
100
100 101
100 101
100 101 101
101
100 101
100 101 101
100 101 101
101
100 101
100 101 101
100 101 101 101
100 101 100 101 101
100
100 100
100 100 101
100 101 100 101

View on DB Fiddle

  • Related