合并postgres中的字段集合

Question

我正在尝试按name字段进行分组，但对于某行可能具有的其他值“合并聚合”。这里的数据：

name  | jurisdiction | db_from 
------------|-----------------|---------------- 
my station NULL    2017-06-21 8:01 
my station my jurisdiction 2017-06-21 8:00 
my station old data   2017-06-21 7:59 

我想拉闸：

name  | jurisdiction  
------------|----------------- 
my station my jurisdiction 

这里就是我想出来的：

WITH _stations AS (
    SELECT * FROM config.stations x ORDER BY x.db_from DESC 
)SELECT 
    x."name", 
    (array_remove(array_agg(x.jurisdiction), NULL))[1] as jurisdiction 
FROM _stations x 
GROUP BY 
    x."name"; 

一些jurisdiction字段的值可能为空，我试图采取最新的不是null。

我知道我不应该依赖于CTE的顺序，但它似乎现在工作。

Answer 1

我花了一点，如果挖掘找到这个，所以我希望它有帮助。让我知道这是不是解决问题的最好方法：

SELECT 
    x."name", 
    (array_remove(array_agg(x.jurisdiction ORDER BY x.db_from DESC), NULL))[1] as jurisdiction 
FROM config.stations x 
GROUP BY x."name" 

Answer 2

select distinct on (x."name") 
    * 
from 
    _stations x 
where 
    x.jurisdiction is not null 
order by 
    x."name", x.db_from desc 

https://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT