PostgreSQL中透视聚合时如何保留原事件ID？

我写了一个透视聚合查询，用来统计数据库中各公司各类事件的最新日期（表结构见文末）：

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;

请问有没有更高效、更简洁的实现方式？

Events表结构 #

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

方法一：窗口函数标记最新记录后直接条件聚合 #

先通过ROW_NUMBER()窗口函数筛选出每个公司、每个事件类型的最新记录，再用条件聚合直接提取对应ID和日期，避免JSONB序列化/反序列化的额外开销：

SELECT 
    "clientCompanyId",
    MAX(id) FILTER (WHERE "eventTypeId" = 1) AS task_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 1) AS task,
    MAX(id) FILTER (WHERE "eventTypeId" = 2) AS call_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 2) AS call,
    MAX(id) FILTER (WHERE "eventTypeId" = 3) AS meeting_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 3) AS meeting,
    MAX(id) FILTER (WHERE "eventTypeId" = 4) AS visit_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 4) AS visit,
    MAX(id) FILTER (WHERE "eventTypeId" = 5) AS sms_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 5) AS sms,
    MAX(id) FILTER (WHERE "eventTypeId" = 6) AS email_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 6) AS email,
    MAX(id) FILTER (WHERE "eventTypeId" = 7) AS animation_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 7) AS animation,
    MAX(id) FILTER (WHERE "eventTypeId" = 8) AS promotion_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 8) AS promotion
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY "clientCompanyId", "eventTypeId" ORDER BY "startDate" DESC) rn
    FROM "Events"
    WHERE "eventStatusId" = 2 AND "deletedAt" IS NULL
) t
WHERE rn = 1
GROUP BY "clientCompanyId";

这种方式逻辑直观，可读性强，性能优于JSONB方案。

方法二：使用PostgreSQL特有的DISTINCT ON筛选最新记录 #

利用PostgreSQL的DISTINCT ON特性快速获取每个分组的最新记录，再进行条件聚合：

SELECT 
    "clientCompanyId",
    MAX(id) FILTER (WHERE "eventTypeId" = 1) AS task_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 1) AS task,
    MAX(id) FILTER (WHERE "eventTypeId" = 2) AS call_id,
    MAX("startDate") FILTER (WHERE "eventTypeId" = 2) AS call,
    -- 其余字段按相同逻辑补充
FROM (
    SELECT DISTINCT ON ("clientCompanyId", "eventTypeId") *
    FROM "Events"
    WHERE "eventStatusId" = 2 AND "deletedAt" IS NULL
    ORDER BY "clientCompanyId", "eventTypeId", "startDate" DESC
) t
GROUP BY "clientCompanyId";

DISTINCT ON会按指定分组字段排序后，保留每个分组的第一条记录，适合快速筛选最新数据。

性能优化建议 #

• 创建复合索引：CREATE INDEX idx_events_client_event_date ON "Events" ("clientCompanyId", "eventTypeId", "startDate" DESC);，能让数据库直接通过索引定位到每个分组的最新记录，避免全表扫描。
• 若过滤条件固定，可扩展索引：CREATE INDEX idx_events_status_deleted_client_event_date ON "Events" ("eventStatusId", "deletedAt", "clientCompanyId", "eventTypeId", "startDate" DESC);，进一步提升查询效率。

内容的提问来源于stack exchange，提问作者DontBreakAlex