Optimizing PostgreSQL Query - Removing subquery

Question

I have the following query to get the value of a particular column : level1 at the starting time and ending time. I'm currently using subqueries to perform this, but would like to just complete it with a single SELECT query.

SELECT
    id,
    (SELECT
         b1.level1
     FROM
         table b1
     WHERE
        b1.id = b.id 
        and b1.start_time = MIN(b.start_time)) AS level1_at_start,
    (SELECT
         b1.level1
     FROM
         table b1
     WHERE
         b1.id = b.id 
     and b1.end_time = MAX(b.end_time)) AS level1_at_end
FROM
    table b
GROUP BY b.id
;

SQL Fiddle link : link

Sample input and output :

id    level1   start_time    end_time
-------------------------------------
i1    10     2              7
i1    50     5              10
i2    60     6              11
i2    20     1              6
i3    30     3              8
i3    40     4              9


id       level1_at_start   level1_at_end
----------------------------------------
i1       10                 50
i2       20                 60
i3       30                 40

@MaciejLos Hey man, I've updated my question with sample input and output tables. Thanks. — Suraj
– Suraj, Commented Jul 7, 2021 at 13:00
These are scalar subqueries. Try to avoid them; they complicate your code. — wildplasser
– wildplasser, Commented Jul 7, 2021 at 13:23

wildplasser · Accepted Answer · 2021-07-07 13:28:00Z

3

You can use window functions to extract values from within (differently ordered) groups:

CREATE TABLE ztable
        ( id text
        , level integer
        , start_time integer
        , end_time integer
        );

INSERT INTO ztable(id, level, start_time, end_time) VALUES
  ('i1', 10, 2, 7)
, ('i1', 50, 5, 10)
, ('i2', 60, 6, 11)
, ('i2', 20, 1,  6)
, ('i3', 30, 3,  8)
, ('i3', 40, 4,  9)
        ;

SELECT *
FROM    (
        SELECT id
        , first_value(level) OVER (PARTITION BY id ORDER BY start_time ASC ) AS first_level
        , first_value(level) OVER (PARTITION BY id ORDER BY end_time DESC) AS last_level
        , row_number() OVER (PARTITION BY id ) AS rn
        FROM ztable
        ) zzz
WHERE zzz.rn = 1
        ;

Output:

CREATE TABLE
INSERT 0 6
 id | first_level | last_level | rn 
----+-------------+------------+----
 i1 |          10 |         50 |  1
 i2 |          20 |         60 |  1
 i3 |          30 |         40 |  1
(3 rows)

answered Jul 7, 2021 at 13:28

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Suraj Over a year ago

Thanks @wildplasser, this works. But is it not possible to just implement this with a single SELECT query, like without the subquery.

wildplasser Over a year ago

Yes. I only added the outer query because I needed the row_number(). Could be done with DISTINCT ON(), too. (I don't think there will be any difference in performance)

Suraj Over a year ago

Thanks for the answer, and windowing functions is really useful. Forgot to accept it!

Collectives™ on Stack Overflow

Optimizing PostgreSQL Query - Removing subquery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related