时间戳

首页 > 解决方案 > postgres - 选择另一个表的一个特定行并将其存储为列

sql - postgres - 选择另一个表的一个特定行并将其存储为列

问题描述

我有一个 timeTracks 表，其中包含为另一个名为 project 的表记录的属性 startTime 和 stopTime。

通过以下聚合，我设法显示了属于一个项目的轨道数量的计数和总和。如果存在活动轨道（一个没有 stopTime），我想要另外 2 个名为“activeTrackId”和“activeTrackStartTime”的列。

SLECT
count(time_track."timeTrackId") AS "timeTracksTotalCount",
floor(date_part('epoch'::text, sum(time_track."stopTime" - time_track."startTime")))::integer AS "timeTracksTotalDurationInSeconds"
activeTimeTrackId ??
activeTimeTrackStartTime ??
...
FROM project
LEFT JOIN time_track on time_track."fkProjectId" = project."projectId"

从技术上讲，一次只能有一个活动轨道。但是为了以防万一，如果有两个没有 stopTime 的轨道，它应该只选择最新的活动轨道。

我怎么能在postgres中做到这一点？

标签： sqlpostgresql

解决方案

您可以使用聚合函数：

SELECT count(t."timeTrackId") AS "timeTracksTotalCount",
       floor(date_part('epoch'::text, sum(t."stopTime" - t."startTime")))::integer AS "timeTracksTotalDurationInSeconds"
       (array_agg(t."timeTrackId" order by t."startTime" desc) filter (where t."stopTime" is null))[1] as activeTimeTrackId,
       max(t."startTime") filter (where t."stopTime" is null) as activeTimeTrackStartTime 
...
FROM project p LEFT JOIN
     time_track t
     ON t."fkProjectId" = p."projectId"
GROUP BY ?;

请注意，Postgres 不提供first()聚合函数，因此它使用数组聚合然后获取第一个元素。

如果您使用的是旧版本的 Postgres，filter则可能无法使用。您可以将其替换为case表达式。

推荐阅读