sql - 如何获取每个设备的第一个和最后一个元素？

Question

我正在尝试找到关于在给定时间间隔内获取第一个元素和最后一个元素的最有效方法的答案。我有一个与 device 表相关的表 interval_data(包括物联网数据)。我想获得每个设备的第一个和最后一个元素的结果。

区间数据表:

    id           device_id          created_at           value
    15269665      1000206      2018-07-21 00:10:00    5099.550000
    15270533      1000206      2018-07-21 00:20:00    5099.610000
    15271400      1000206      2018-07-21 00:30:00    5099.760000
    15272269      1000206      2018-07-21 00:40:00    5099.850000
    15273132      1000206      2018-07-21 00:50:00    5099.910000
    15274040      1000206      2018-07-21 01:00:00    5099.970000
    15274909      1000206      2018-07-21 01:10:00    5100.030000
    15275761      1000206      2018-07-21 01:20:00    5100.110000
    15276629      1000206      2018-07-21 01:30:00    5100.160000
    15277527      1000206      2018-07-21 01:40:00    5100.340000
    15278351      1000206      2018-07-21 01:50:00    5100.400000
    15279219      1000206      2018-07-21 02:00:00    5100.450000
    15280085      1000206      2018-07-21 02:10:00    5100.530000
    15280954      1000206      2018-07-21 02:20:00    5100.590000
    15281858      1000206      2018-07-21 02:30:00    5100.640000
    15282724      1000206      2018-07-21 02:40:00    5100.750000
    15283627      1000206      2018-07-21 02:50:00    5100.870000
    15284495      1000206      2018-07-21 03:00:00    5100.930000
      ...           ...                ...                ...

我尝试了一些查询，例如:

select created_at, value from interval_data i inner join
(select min(created_at) minin, max(created_at) maxin, d.device_id from device 
d
inner join interval_data i on i.device_id = d.device_id
where d.device_id in (1000022, 1000023, 1000024)
and i.created_at between '2018-01-01 00:00:00' and '2019-01-01 00:00:00' 
group by d.device_id) s
on s.device_id = i.device_id and (s.minin = i.created_at or s.maxin = 
i.created_at)

但是当设备数量增加时，响应时间会变长。你有什么建议吗？如何更快地找到每个设备的第一个和最后一个元素？

Answer 1

最有效的查询取决于您的设置细节。 通常，具有两个LATERAL 子查询的查询应该是最快的:

SELECT *  -- or just the columns you need
FROM device d
LEFT JOIN LATERAL (
   SELECT id AS first_intv_id, created_at AS first_created_at, value AS first_value
   FROM   interval_data
   WHERE  device_id = d.id
   ORDER  BY created_at
   LIMIT  1
   ) f ON true
LEFT JOIN LATERAL (
   SELECT id AS last_intv_id, created_at AS last_created_at, value AS last_value
   FROM   interval_data
   WHERE  device_id = d.id
   ORDER  BY created_at DESC  -- NULLS LAST if column isn't NOT NULL
   LIMIT  1
   ) l ON true;

fiddle

Postgres 可以将其转换为仅对大表 interval_data 进行快速索引扫描的查询计划。

关于横向:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

确保在 interval_data(device_id, created_at) 上有一个索引。如果您只需要结果中的一组有限列，则可能需要将更多列附加到该索引以从中获取仅索引扫描。

LEFT JOIN ... ON true 保留结果中没有间隔数据的设备。

要限制给定的一组设备 ID，请附加到查询中:

...
WHERE  d.id IN (1000022, 1000023, 1000024);

并且在 device(id) 上有一个索引——无论如何这都是典型的情况。

假设当前的 Postgres 版本和这样的设置:

CREATE TABLE device (
   id     serial PRIMARY KEY
 , device text NOT NULL
);

CREATE TABLE interval_data (
   id         serial PRIMARY KEY
 , device_id  int NOT NULL
 , created_at timestamp NOT NULL
 , value      numeric NOT NULL
 , CONSTRAINT device_fkey FOREIGN KEY (device_id) REFERENCES device (id)
);

如果一些涉及的列没有定义NOT NULL，你可能需要调整细节。

对于此解决方案，FK 约束是可选的。

备选方案的详细解释和讨论:

• Select first row in each GROUP BY group?
• Optimize GROUP BY query to retrieve latest row per user
• PostgreSQL: running count of rows for a query 'by minute'

一小组给定设备 ID 的替代方案

使用带有自定义窗口框架的窗口函数可以在没有单独的表设备的情况下完成，并且对于一小组ID可能更快:

SELECT DISTINCT ON (device_id)
       device_id
     , first_value(created_at) OVER w AS first_created_at
     , first_value(value)      OVER w AS first_value
     , last_value (created_at) OVER w AS last_created_at
     , last_value (value)      OVER w AS last_value
FROM   interval_data
WHERE  device_id IN (1000022, 1000023, 1000024)
WINDOW w AS (PARTITION BY device_id ORDER BY created_at
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

fiddle

与上面的第一个查询相同:

• 对于不存在的已传递设备 ID，没有结果。

但是与上面的第一个查询不同:

• 对于确实存在但没有任何间隔数据的传递设备 ID，没有结果。

关于窗框:

• PostgreSQL query with max and min date plus associated id per row
• How to use a ring data structure in window functions