1

I am working in PostgreSQL 9.6.6 on a query that involves the use of a second table to return additional data points. The table - "events" - is associated with many entries from the second - "activities" - via a foreign key.

Events

| id | name   |
|----|--------|
| 1  | event1 |
| 2  | event2 |
| 3  | event3 |

Activities

| id | name      | startDate              | endDate                | status    | eventId |
|----|-----------|------------------------|------------------------|-----------|---------|
| 1  | activity1 | 2018-08-27 05:00:00+00 | 2018-08-28 05:00:00+00 | Draft     | 1       |
| 2  | activity2 | 2018-09-27 05:00:00+00 | 2018-09-27 10:00:00+00 | Submitted | 1       |
| 3  | activity3 | 2018-08-25 05:00:00+00 | 2018-08-27 10:00:00+00 | Draft     | 1       |
| 4  | activity4 | 2018-08-21 05:00:00+00 | 2018-08-24 05:00:00+00 | Approved  | 2       |
| 5  | activity5 | 2018-09-27 05:00:00+00 | 2018-09-29 05:00:00+00 | Draft     | 2       |
| 6  | activity6 | 2018-10-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Approved  | 3       |
| 7  | activity7 | 2018-08-27 05:00:00+00 | 2018-08-27 10:00:00+00 | Approved  | 3       |

The data points being collected via the event are a startDate, endDate & status based on the associated activities.

  • startDate => The least startDate of all associated activities.
  • endDate => The greatest endDate of all associated activities.
  • status => Status equals "Pending" unless all associated activities have a status of "Approved" (then status equals "Complete").

An example returned query would look like:

| id | name   | startDate              | endDate                | status    |
|----|--------|------------------------|------------------------|-----------|
| 1  | event1 | 2018-08-25 05:00:00+00 | 2018-09-27 10:00:00+00 | Pending   |
| 2  | event2 | 2018-08-21 05:00:00+00 | 2018-09-29 05:00:00+00 | Pending   |
| 3  | event3 | 2018-08-27 05:00:00+00 | 2018-10-28 05:00:00+00 | Completed |

I have formulated the following with multiple subqueries, but the performance is terrible.

Any suggestions as to how can I improve this to limit (or even remove) the use of subqueries?

SELECT DISTINCT ON("startDate", "event"."name") 1, "event"."id", "event"."name", (
  SELECT "activities"."startDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."startDate" ASC
  LIMIT 1
) AS "startDate", (
  SELECT "activities"."endDate"
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."endDate" DESC
  LIMIT 1
) AS "endDate", (
  SELECT DISTINCT ON("event"."id")
    CASE "activities"."status"
    WHEN 'Draft' THEN 'Pending'
    WHEN 'Submitted' THEN 'Pending'
    ELSE 'Complete'
    END
  FROM "events"
  INNER JOIN "activities" AS "activities" ON "event"."id" = "activities"."eventId"
  WHERE "event"."id" = "activities"."eventId"
  ORDER BY "event"."id" ASC, "activities"."status" DESC
) AS "status"
FROM "events" AS "event"
ORDER BY "startDate" DESC, "event"."name" ASC
LIMIT 20
OFFSET 0;
0
0

Test:

SELECT e.name, 
       MIN(a.startDate) startDate, 
       MAX(a.endDate) endDate, 
       CASE WHEN SUM(a.Status != 'Approved')=0 
              THEN 'Completed' 
              ELSE 'Pending' 
         END status
FROM Events e
JOIN Activities a ON e.id = a.eventId
GROUP BY e.name

If fail replace SUM(a.Status != 'Approved') with SUM(CASE WHEN a.Status != 'Approved' THEN 1 ELSE 0 END).

2
  • Replaced SUM(a.Status != 'Approved') with SUM(CASE WHEN a.Status != 'Approved' THEN 1 ELSE 0 END) provided in the answer and the query worked flawlessly. This dropped the query execution time from 60 seconds to 2 seconds with the entire data set I am working with! – proph3t Aug 29 '18 at 13:17
  • @proph3t Simply I don't remember which DBMSs allows this simplified syntax with implicit conversion from boolean to number... so I gives 2 variants. – Akina Aug 29 '18 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.