从按多列分组的行组中选择具有最大值的行（PSQL）

本文介绍了从按多列分组的行组中选择具有最大值的行（PSQL）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张交易数据表，这些数据是对未来的预测。因此，随着时间的流逝和重新发送的预测变得更加准确，可以多次读取由相同日期，类型，位置和产品标识的相同预测。

I have a table of transactional data, which are forecasts for the future. The same forecasts, identified by same date, type, location and product, are thus read in multiple times as the forecasts become more accurate as time goes by and are resent.

我想创建一个查询，对相同类型，相同位置，产品和日期的交易进行分组，然后从这些组中仅选择具有最新时间戳的交易。

I would like to create a query that would group the transactions that are of the same type and for the same location, product and date and then select from these groups only the ones that have the newest updated timestamps.

该表现在有成百上千的行，随着时间的流逝，数百万，因此，一个合理有效的解决方案将不胜感激：）

The table has now hundreds of thousands of rows and as time goes by, millions, so a reasonably efficient solution would be appreciated :)

表格示例：

date | location_code | product_code | quantity | type | updated_at ------------+------------------+---------------+----------+----------+------------ 2013-02-04 | ABC | 123 | -26.421 | TRANSFER | 2013-01-12 2013-02-07 | ABC | 123 | -48.1 | SALE | 2013-01-10 2013-02-06 | BCD | 234 | -58.107 | SALE | 2013-01-11 2013-02-06 | BCD | 234 | -60 | SALE | 2013-01-10 2013-02-04 | ABC | 123 | -6.727 | TRANSFER | 2013-01-10

所需结果：

date | location_code | product_code | quantity | type | updated_at ------------+------------------+---------------+----------+----------+------------ 2013-02-04 | ABC | 123 | -26.421 | TRANSFER | 2013-01-12 2013-02-07 | ABC | 123 | -48.1 | SALE | 2013-01-10 2013-02-06 | BCD | 234 | -58.107 | SALE | 2013-01-11

我尝试例如：

SELECT t.date, t.location_code, t.product_code, t.quantity, t.type, t.updated_at FROM transactions t INNER JOIN ( SELECT MAX(updated_at) as max_updated_at FROM transactions GROUP BY product_code, location_code, type, date ) s on t.updated_at=max_updated_at;

但这似乎需要很长时间，而且似乎不起作用。

But this seems to take ages and doesn't seem to work.

谢谢您的帮助！

推荐答案

select distinct on ("date", location_code, product_code, type) "date", location_code, product_code, quantity, type, updated_at from transactions t order by t."date", t.location_code, t.product_code, t.type, t.updated_at desc