我知道在postgresql中有获取行的最后和第一个值的聚合函数
我的问题是,他们不能按我的需要工作.我可以使用一个postgresql向导的帮助.我正在使用postgresql 9.2 - 以防该版本提供解决方案easyyer.
询问
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
不
id, active, install_date, remove_date 1, TRUE, 2008-08-02 11:13:39, NULL
正如我所料
如果最后一行的值为null,而不是最后一个现有值,是否可以以某种方式更改聚合函数以产生NULL?
EDIT1
Roman Pekar为我的问题提供了替代解决方案,但这不符合我的需求.原因是 - 我简化了原始查询.但我运行的查询更复杂.我意识到可能有我的问题的替代解决方案 - 这就是为什么更新帖子以包括原始的,更复杂的查询.这是:
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
您可以使用窗口函数lead()
并lag()
检查第一条和最后一条记录,例如:
select max(a.id) as id, max(a.first) as first, max(a.last) as last from ( select v.id, case when lag(v.id) over(order by v.id, p.install_date) is null then p.install_date end as first, case when lead(v.id) over(order by v.id, p.install_date) is null then p.remove_date end as last from vehicle v left join period p on (v.id = p.car_id) where v.id = 1 ) as a
sql fiddle demo