How to pivot and aggregate in Postgresql, while keeping the original ids in the new rows?-CodePudding

I have this query which does a pivot a an aggregate to store the latest events for every company in the database (schema at the end of the question):

SELECT E."clientCompanyId",
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 1 ) task,
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 2 ) call,
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 3 ) meeting,
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 4 ) visit,
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 5 ) sms,
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 6 ) email,
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 7 ) animation,
       MAX(E."startDate") FILTER ( WHERE E."eventTypeId" = 8 ) promotion
FROM "Events" E
WHERE E."eventStatusId" = 2
  AND E."deletedAt" IS NULL
GROUP BY E."clientCompanyId";

But I also need to store the latest event id. I only managed to do it using this abomination with a temporary jsonb object:

SELECT "clientCompanyId",
       (object -> '1' ->> 'id')::int4          AS task_id,
       (object -> '1' ->> 'date')::timestamptz AS task,
       (object -> '2' ->> 'id')::int4          AS call_id,
       (object -> '2' ->> 'date')::timestamptz AS call,
       (object -> '3' ->> 'id')::int4          AS meeting_id,
       (object -> '3' ->> 'date')::timestamptz AS meeting,
       (object -> '4' ->> 'id')::int4          AS visit_id,
       (object -> '4' ->> 'date')::timestamptz AS visit,
       (object -> '5' ->> 'id')::int4          AS sms_id,
       (object -> '5' ->> 'date')::timestamptz AS sms,
       (object -> '6' ->> 'id')::int4          AS email_id,
       (object -> '6' ->> 'date')::timestamptz AS email,
       (object -> '7' ->> 'id')::int4          AS animation_id,
       (object -> '7' ->> 'date')::timestamptz AS animation,
       (object -> '8' ->> 'id')::int4          AS promition_id,
       (object -> '8' ->> 'date')::timestamptz AS promition
FROM (SELECT "clientCompanyId",
             JSONB_OBJECT_AGG("eventTypeId", JSONB_BUILD_OBJECT('id', id, 'date', "startDate")) AS object
      FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "clientCompanyId", "eventTypeId" ORDER BY "startDate" DESC) rn
            FROM "Events"
            WHERE "eventStatusId" = 2
              AND "deletedAt" IS NULL) E
      WHERE rn = 1
      GROUP BY "clientCompanyId") C;

Is there a better way to do this ?

This is the schema:

CREATE TABLE "Events"
(
    id                serial
        PRIMARY KEY,
    "userId"          integer                  NOT NULL,
    "companyId"       integer                  NOT NULL,
    "eventTypeId"     integer                  NOT NULL,
    title             varchar(255)             NOT NULL,
    "startDate"       timestamp WITH TIME ZONE,
    "endDate"         timestamp WITH TIME ZONE,
    "clientCompanyId" integer,
    "contactId"       integer,
    "opportunityId"   integer,
    "eventStatusId"   integer                  NOT NULL,
    "createdAt"       timestamp WITH TIME ZONE NOT NULL,
    "updatedAt"       timestamp WITH TIME ZONE,
    "deletedAt"       timestamp WITH TIME ZONE,
    incharge          integer,
    description       text,
    "eventTag"        integer
);

CodePudding user response：

try this :

SELECT E."clientCompanyId",
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 1 ))[1] task_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 1 ))[1] task,
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 2 ))[1] call_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 2 ))[1] call,
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 3 ))[1] meeting_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 3 ))[1] meeting,
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 4 ))[1] visit_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 4 ))[1] visit,
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 5 ))[1] sms_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 5 ))[1] sms,
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 6 ))[1] email_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 6 ))[1] email,
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 7 ))[1] animation_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 7 ))[1] animation,
       (array_agg(E."id" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 8 ))[1] promotion_id,
       (array_agg(E."startDate" ORDER BY E."startDate" DESC) FILTER ( WHERE E."eventTypeId" = 8 ))[1] promotion
FROM "Events" E
WHERE E."eventStatusId" = 2
  AND E."deletedAt" IS NULL
GROUP BY E."clientCompanyId";