Self inner join, aggregate and sum of set

user_5685496 0
Viewed 59

I've got the following schema:

CREATE TABLE aTable
    ("id" int, "idOfOperation" varchar(10), "uniqueName" varchar(6), "randomNumber" int)
;

INSERT INTO aTable
    ("id", "idOfOperation", "uniqueName", "randomNumber")
VALUES
    (1, 'operation1', 'model1', 3),
    (2, 'operation1', 'model2', 5),
    (3, 'operation2', 'model2', 43),
    (4, 'operation2', 'model3', 57),
    (5, 'operation2', 'model4', 65)
;

Constraints:

  • (idOfOperation, uniqueName) are unique.

Table contains results of different operations (operation1, operation2) and which operation has multiple sub models (model1, model2, model3, model4) with some unique values (randomNumber).

What I need to achieve:

  • Create a query which will find a sum of set of two operations (operation1, operation2) withe following output: 'model2', 5.18, 'model2', 43.2

I already tried the following inner join:

select r1.*, r2.*
from aTable r1
         inner join aTable r2
         on (r1."uniqueName" = r2."uniqueName" and r1."idOfOperation" != r2."idOfOperation")
where (r1."idOfOperation" = 'operation1' or r1."idOfOperation" = 'operation2')

But the result contains two rows instead of one (with swapped values):

id  idOfOperation   uniqueName  randomNumber    id  idOfOperation   uniqueName  randomNumber
3   operation2  model2  43  2   operation1  model2  5
2   operation1  model2  5   3   operation2  model2  43

Expected result is:

operation2, operation1, model2, 43, 5

I'd really appreciate any help.

postgresql sql
2 Answers

Please use below query,

select split_part(idOfOperation, ',', 2) as col1, split_part(idOfOperation, ',', 1) as 
col2,  uniqueName as col3, split_part(randomNumber, ',', 2) as col4, 
split_part(randomNumber, ',', 1) as col5 from
(select STRING_AGG ("idOfOperation", ',') as idOfOperation , "uniqueName" as 
uniqueName, STRING_AGG ("randomNumber"::text, ',') as randomNumber
from aTable  group by "uniqueName") qry where split_part(idOfOperation, ',', 2) != '';

Snipping of your expected result,

enter image description here

user_44736150

I understand that you are looking for transitions from operation1 to operation2. One option uses window functions: lead() and lag() let you access the next and previous operation, that you can then then use for filtering:

select "id", "idOfOperation", "uniqueName", "randomNumber"
from (
    select 
        t.*,
        lag("idOfOperation") over(order by id) lagIdOfOperation,
        lead("idOfOperation") over(order by id) leadIdOfOperation
    from aTable t
) t
where ('operation1', 'operation2') in (
    (t."idOfOperation",  leadIdOfOperation) , 
    (lagIdOfOperation, t."idOfOperation") 
)

Demo on DB Fiddle:

id | idOfOperation | uniqueName | randomNumber
-: | :------------ | :--------- | -----------:
 2 | operation1    | model2     |            5
 3 | operation2    | model2     |           43

Side notes:

  • avoid using double-quoted (case sensitive) identifiers: they just make things more complicated, for no obvious benefit

  • column randomNumber should probably be numeric rather than int

user_106767160
Related