sql - 为大型数据集中的每个项目选择最新条目

Question

所以，我有一个大约有 150 万行的表，看起来有点像这样:

name   | time       | data1 | data2  
--------------------------------------
 93-15 | 1337348782 |   11  | 60.791 
 92-02 | 1337348783 |   11  | 62.584 
 92-02 | 1337348056 |   11  | 63.281
 93-15 | 1337348068 |    8  | 65.849
 92-02 | 1337348117 |   11  | 63.271 
 93-15 | 1337348129 |    8  | 65.849 
 92-02 | 1337348176 |   10  | 63.258 
 93-15 | 1337348188 |    8  | 65.849 
 92-02 | 1337348238 |   10  | 63.245 
 93-15 | 1337348248 |    8  | 65.849  

...这些对应于需要监视的事物的历史状态更新。现在，我想做的是找到每个单元的当前状态。

在 stackoverflow 上很容易找到类似的问题，并且根据调查结果推断，我想出了这个查询:

SELECT * FROM vehicles v
  JOIN ( SELECT  MAX(time) as max, name
    FROM vehicles
    GROUP BY name)
  m_v
ON (v.time = m_v.max AND v.name = m_v.name);

但是鉴于我有大约 150 万行(并且还在增加)，是否有不同的方法可以加快查询速度？

Answer 1

WITH
  sequenced_data
AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY time DESC) AS sequence_id,
    *
  FROM
    vehicles
)
SELECT
  *
FROM
  sequenced_data
WHERE
  sequence_id = 1

(name, time) 的覆盖索引也会有帮助。

编辑:关于它如何工作的注释等。

PostgreSQL 具有所谓的窗口或分析功能。这些通常采用 some_function() OVER (PARTITION BY some_fields ORDER BY some_fields) 的形式。

在这种情况下，我使用了 ROW_NUMBER() OVER (PARTITION BY name ORDER BY time DESC)。

ROW_NUMBER() 为一组数据创建一个唯一的行号。 1 到 n 表示 n 条记录。

PARTITION BY name 表示此函数独立应用于不同的名称。每个 name 都是它自己的组/窗口/分区，ROW_NUMBER() 的结果对每个组/窗口再次从 1 开始/分区。

ORDER BY time DESC 获取一组/窗口/分区中的所有记录，并按 time 字段对它们进行排序，最大值在前，在 ROW_NUMBER() 函数被应用。

因此，对于您的示例数据，您会得到这个...

 name  | time       | data1 | data2  | row_number
--------------------------------------------------

 92-02 | 1337348783 |   11  | 62.584 | 1
 92-02 | 1337348238 |   10  | 63.245 | 2
 92-02 | 1337348176 |   10  | 63.258 | 3
 92-02 | 1337348117 |   11  | 63.271 | 4
 92-02 | 1337348056 |   11  | 63.281 | 5

 93-15 | 1337348782 |   11  | 60.791 | 1
 93-15 | 1337348248 |    8  | 65.849 | 2
 93-15 | 1337348188 |    8  | 65.849 | 3
 93-15 | 1337348129 |    8  | 65.849 | 4
 93-15 | 1337348068 |    8  | 65.849 | 5

因为排序是time DESC，最高值的time字段，在每个name组/窗口/分区中，总会有一个row_number，共 1。

在 (name, time) 上建立索引可以确保数据以友好的顺序排列，从而使优化器更容易。这意味着 ROW_NUMBER() 实际上并未应用于所有记录；一旦找到最高值的 time 记录，并分配 ROW_NUMBER() = 1，它就知道可以停止并继续下一个 name.