我知道有聚合函数可以获取行的最后一个和第一个值PostgreSQL https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
我的问题是,它们不能按我的需要工作。我可以使用 postgresql 向导的帮助。我正在使用 postgresql 9.2 - 以防该版本使提供解决方案更容易。
Query
select v.id, v.active, v.reg_no, p.install_date, p.remove_date
from vehicle v
left join period p on (v.id = p.car_id)
where v.id = 1
order by v.id, p.install_date asc
返回 6 行:
id, active, reg_no, install_date, remove_date
1, TRUE, something, 2008-08-02 11:13:39, 2009-02-09 10:32:32
....
1, TRUE, something, 2010-08-15 21:16:40, 2012-08-25 07:44:30
1, TRUE, something, 2012-09-10 17:05:12, NULL
但是当我使用聚合查询时:
select max(id) as id, last(active) as active, first(install_date) as install_date, last(remove_date) as remove_date
from (
select v.id, v.active, v.reg_no, p.install_date, p.remove_date
from vehicle v
left join period p on (v.id = p.car_id)
where v.id = 1
order by v.id, p.install_date asc
) as bar
group by id
然后我得到
id, active, install_date, remove_date
1, TRUE, 2008-08-02 11:13:39, 2012-08-25 07:44:30
not
id, active, install_date, remove_date
1, TRUE, 2008-08-02 11:13:39, NULL
正如我所料
如果最后一行的值为空,而不是最后一个现有值,是否可以以某种方式更改聚合函数以产生 NULL?
EDIT1
罗曼·佩卡 https://stackoverflow.com/users/1744834/roman-pekar提供替代解决方案 https://stackoverflow.com/questions/20345859/first-and-last-value-aggregate-functions-in-postgresql-that-work-correctly-with/20346066#20346066解决我的问题,但这不符合我的需求。原因是 - 我简化了原始查询。但我运行的查询更复杂。我意识到我的问题可能有其他解决方案 - 这就是为什么要更新帖子以包含原始的、更复杂的查询。这是:
select partner_id, sum(active) as active, sum(installed) as installed, sum(removed) as removed
from (
select
pc.partner_id as partner_id,
v.id,
CASE WHEN v.active = TRUE THEN 1 ELSE 0 END as active,
CASE WHEN first(p.install_date) BETWEEN '2013-12-01' AND '2014-01-01' THEN 1 ELSE 0 END as installed,
CASE WHEN last(p.remove_date) BETWEEN '2013-12-01' AND '2014-01-01' THEN 1 ELSE 0 END as removed
from vehicle v
left join period p on (v.id = p.car_id)
left join partner_clients pc on (pc.account_id = v.client_id)
group by pc.partner_id, v.id, v.active
) as foo group by partner_id
正如您所看到的,我实际上需要获取几辆车的第一个和最后一个值,而不是一辆,最后汇总这些车辆的车主的这些车辆的数量。
/EDIT1