在窗口函数 postgresql 中选择条件

Question

我正在使用postgresql并应用窗口函数。 以前我必须找到具有相同姓氏和地址(street_address和城市)的第一个 gid，所以我只需在窗口函数的子句中按子句将姓氏放入

。但现在我需要找到姓氏不一样g_id。 而地址相同 我该怎么做？

这就是我以前在做的事情。

SELECT g_id as g_id,
First_value(g_id) 
OVER (PARTITION BY lname,street_address , city , 
order by  last_date DESC NULLS LAST )as c_id,
street_address as street_address  FROM my table;

假设这是我的数据库

---

g_id | l_name | street_address | city | last_date
_________________________________________________
x1   | bar    | abc road       | khi  | 11-6-19
x2   | bar    | abc road       | khi  | 12-6-19
x3   | foo    | abc road       | khi  | 19-6-19
x4   | harry  | abc road       | khi  | 17-6-19
x5   | bar    | xyz road       | khi  | 11-6-19
_________________________________________________

在以前的场景中： 因为如果我运行第一行c_id，它应该返回"x2"，因为它考虑这些行：

_________________________________________________
g_id | l_name | street_address | city | last_date
_________________________________________________
x1   | bar    | abc road       | khi  | 11-6-19
x2   | bar    | abc road       | khi  | 12-6-19
_________________________________________________

并返回包含最新last_date的行。

我现在想选择这些行(具有相同street_address和城市但没有相同l_name的行)：

---

g_id | l_name | street_address | city | last_date
_________________________________________________
x1   | bar    | abc road       | khi  | 11-6-19
x3   | foo    | abc road       | khi  | 19-6-19
x4   | harry  | abc road       | khi  | 17-6-19
_________________________________________________

输出将为 x3。

不知何故，我想比较last_name列，如果它不等于姓氏的当前值，然后按地址字段分区。 如果没有行满足条件c_id应等于当前g_id

Answer 1

查看您的预期输出，不清楚每个组是最早还是最旧。您可以相应地更改此查询中last_date的ORDER BY，该查询使用DISTINCT ON

SELECT DISTINCT ON ( street_address, city, l_name) * 
FROM   mytable 
ORDER  BY street_address, 
city, 
l_name, 
last_date  --change this to last_date desc if you want latest

演示

Answer 2

在此聊天中讨论详细信息后：

演示：数据库<>小提琴

SELECT DISTINCT ON (t1.g_id) 
t1.*,
COALESCE(t2.g_id, t1.g_id) AS g_id
FROM
mytable t1
LEFT JOIN mytable t2
ON t1.street_address = t2.street_address AND t1.l_name != t2.l_name
ORDER BY t1.g_id, t2.last_date DESC

Answer 3

这是我如何使用子查询解决它的方法 创建示例表。

CREATE TABLE mytable
("g_id" varchar(2), "l_name" varchar(5), "street_address" varchar(8), "city" varchar(3), "last_date" date)

;

INSERT INTO mytable
("g_id", "l_name", "street_address", "city", "last_date")
VALUES
('x1', 'bar', 'abc road', 'khi', '11-6-19'),
('x2', 'bar', 'abc road', 'khi', '12-6-19'),
('x3', 'foo', 'abc road', 'khi', '19-6-19'),
('x4', 'harry', 'abc road', 'khi', '17-6-19'),
('x5', 'bar', 'xyz road', 'khi', '11-6-19')

;

查询以获取g_ids

SELECT * ,
(select b.g_id from mytable b where (base.g_id = b.g_id) or (base.l_name <> 
b.l_name and base.street_address = b.street_address and base.city = b.city ) 
order by b.last_date desc  limit 1)
from mytable base