Select Value by Max Date

Question

我在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

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

Answer 1

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

在使用给定设置时，使用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)

db<gt；小提琴这里

参见：

• 选择每个GROUP BY组中的第一行

这假设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';

参见：

• 优化GROUP BY查询以检索每个用户的最新行

Answer 2

SELECT X.CUSTOMER_ID,X.PRICE,X.CONDITION
FROM
(
SELECT A.CUSTOMER_ID,A.PRICE,A.CONDITION,
ROW_NUMBER()OVER(PARTITION BY A.CUSTOMER_ID ORDER BY A.TIMESTAMP DESC)XCOL
FROM YOUR_TABLE A
)X WHERE X.XCOL=1 

你能试试上面的是否适合你吗