如何选择每个项目具有最大时间戳的行集? [英] How can I select the set of rows where each item has the greatest timestamp?

问题描述

使用Sqlite，我想获取具有最大时间戳的行集合.该表包含项的属性，即键-值对和时间戳.我想为每个属性选择最新的值.

Using Sqlite, I'd like to fetch the collection of rows each with the greatest timestamp. The table contains the properties of items, which are key-value pairs and timestamp. I'd like to select the most recent value for each property.

请考虑以下简化的架构和数据:

Consider the following simplified schema and data:

CREATE TABLE Properties (thing VARCHAR,
                         key VARCHAR,
                         value VARCHAR,
                         timestamp INT);
INSERT INTO Properties VALUES ("apple", "color", "red", 0);
INSERT INTO Properties VALUES ("apple", "taste", "sweet", 0);
INSERT INTO Properties VALUES ("apple", "size", "small", 0);
INSERT INTO Properties VALUES ("watermelon", "taste", "sweet", 0);
INSERT INTO Properties VALUES ("watermelon", "size", "large", 0);
INSERT INTO Properties VALUES ("watermelon", "color", "pink", 1);
INSERT INTO Properties VALUES ("watermelon", "color", "green", 0);

我想为thing="watermelon"编写一个查询，该查询返回:

I'd like to write a query for thing="watermelon" that returns:

taste|sweet
size|large
color|pink

请注意，有两行带有key="color"，查询将返回具有最大timestamp值的行.另外，一个属性的最大timestamp可能与另一属性不同.

Note that there are two rows with key="color", and the query returns the row with the greatest timestamp value. Also, the greatest timestamp for one property may be different from another property.

到目前为止我尝试过的内容包括:

获取thing="watermelon"的属性集:

SELECT DISTINCT(key) FROM Properties WHERE thing='watermelon';

获取thing="watermelon"的最新值key="color":

SELECT * 
FROM Properties
WHERE thing='watermelon'
  AND key='color'
ORDER BY timestamp DESC
LIMIT 1;

但是我不知道如何将两者结合起来.我可能是从命令式编程的角度来解决这个问题的，这就是为什么我希望获得帮助.

But I can't figure out how to combine the two. I'm probably coming at this from an imperative programming perspective, which is why I'd appreciate assistance.

推荐答案

在SQLite 3.7.11或更高版本中，您可以简单地使用MAX()从组中选择一行:

In SQLite 3.7.11 or later, you can simply use MAX() to select one row from a group:

SELECT key, value, MAX(timestamp)
FROM Properties
WHERE thing = 'watermelon'
GROUP BY key;

这篇关于如何选择每个项目具有最大时间戳的行集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！