按最大日期选择值 [英] Select Value by Max Date

问题描述

我在PostgreSQL数据库中有一个表，其中的数据如下：

id  customer_id   item       value     timestamp

 1  001           price       1000     11/1/2021
 2  001           price       1500     11/2/2021
 3  001           condition   good     11/3/2021
 4  002           condition   bad      11/4/2021
 5  002           condition   good     11/5/2021
 6  002           price       1000     11/6/2021
 7  001           condition   good     11/7/2021
 8  001           price       1400     11/8/2021
 9  002           price       1500     11/9/2021
10  001           condition    ok      11/10/2021
11  002           price       1600     11/11/2021
12  002           price       1550     11/12/2021

在此表中，我希望按日期查询最新值并将其转换为表，如下所示。

customer_id   price   condition
   001        1400      ok
   002        1550     good

为了获得这种表，我尝试使用下面的查询，但当有太多数据时，它不能很好地工作。(类似最小和最大到文本和数字的操作)

我在pgAdmin 4中进行了测试：

SELECT customer_id,
MAX (Case WHEN item='price' THEN value END) price,
MAX (Case WHEN item='condition' THEN value END) condition

FROM table_name GROUP BY customer_id

我想按更新数据的最新日期查询值。

推荐答案

您的关系设计可能会得到改进。在同一列中混合不同类型的数据是一种反模式。

在使用给定设置时，使用DISTINCT ON和FULL OUTER JOIN的两个子查询执行此工作：

SELECT customer_id, p.value AS price, c.value AS condition
FROM  (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'condition'
   ORDER  BY customer_id, timestamp DESC
   ) c
FULL JOIN (
   SELECT DISTINCT ON (customer_id)
          customer_id, value
   FROM   tbl
   WHERE  item = 'price'
   ORDER  BY customer_id, timestamp DESC
   ) p USING (customer_id)

小提琴here

请参阅：

• Select first row in each GROUP BY group?

假设timestamp定义NOT NULL，否则您需要NULLS LAST。

根据未披露的基数和值分布，可能会有(快得多)的查询变体。
如果存在具有不同customer_id的customer表，则可以使用更快的查询样式。

这些部分、多列索引在任何情况下都能完美地提高速度：

CREATE INDEX tbl_condition_special_idx ON tbl (customer_id, timestamp DESC, value) WHERE item = 'condition';
CREATE INDEX tbl_price_special_idx     ON tbl (customer_id, timestamp DESC, value) WHERE item = 'price';

请参阅：

• Optimize GROUP BY query to retrieve latest row per user

这篇关于按最大日期选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！