选择此选项可搜索"分组依据"查询中的列

Question

我有一个名为价格的表格，它有从表产品到product_id列的参考。我想要一个查询，该查询选择按product_id分组的价格，并 个 id 为价格分组，并通过一个 id 为价格分组的选择获取 start_date 的值。

我尝试使用以下查询，但开始日期值错误。很奇怪，因为结果子查询返回多行，即使我在 where 子句上使用价格 id。因为我对查询设置了限制，但它是错误的。

select prices.produto_id, prices.id, 
MAX(CASE WHEN prices.finish_date IS NULL THEN COALESCE(prices.finish_date,'9999-12-31') ELSE prices.finish_date END) as finish_date, 
(select start_date from prices where prices.id = prices.id limit 1) 
as start_date from prices group by prices.product_id, prices.id

如何获取分组行中价格 ID 的相对开始日期？我正在使用postgresql。

查看我想要的查询内容的示例：

数据：

ID | PRODUCT_ID | START_DATE          | FINISH_DATE
1    1689         2018-01-19 02:00:00   2019-11-19 23:59:59
2    1689         2019-10-11 03:00:00   2019-10-15 23:59:59
3    1689         2019-01-11 03:00:00   2019-05-15 23:59:59
4    1690         2019-11-11 03:00:00   2019-12-15 23:59:59
5    1690         2019-05-11 03:00:00   2025-12-15 23:59:59
6    1691         2019-05-11 03:00:00   null

我想要这个结果：

ID | PRODUCT_ID | START_DATE          | FINISH_DATE
1    1689         2018-01-19 02:00:00   2019-11-19 23:59:59
5    1690         2019-05-11 03:00:00   2025-12-15 23:59:59
6    1691         2019-05-11 03:00:00   9999-12-31 23:59:59

开始日期应与分组依据之前的行值相同。

Answer 1

我建议在Postgres中DISTINCT ON：

select distinct on (p.product_id) p.*
from prices p
order by p.product_id,
p.finish_date desc nulls first;

NULL值被视为大于任何其他值，因此降序排序将它们放在第一位。 但是，我包括nulls first只是为了明确。

DISTINCT ON是一个非常方便的Postgres扩展，您可以在文档中了解更多信息。

Answer 2

试试这个

with data as (
SELECT id, product_id,
max(COALESCE(finish_date,'9999-12-31')) as finish_date from prices group by 1,2)  
select d.*, p.start_date from data d join prices p on p.id = d.id;

它肯定不是最优雅的解决方案，但它应该有效。