如何在Postgresql中pivot和聚合，同时在新行中保持原始id ?

Question

我有这样一个查询，它做一个pivot聚合来存储数据库中每个公司的最新事件(模式在问题的末尾):

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";

但是我还需要存储最新的事件id。我只能使用这个讨厌的临时jsonb对象来完成它:

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;

有更好的方法吗?

模式:

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
);

Answer 1

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";