postgreSQL可以按任意时间段对时间间隔进行分组

我需要这个SQL查询的帮助。我有一张大桌子，上面有以下模式：

CCD_ 1（时间戳）-测量的开始时间
CCD_ 2（双）-测量持续时间（秒）
CCD_ 3（int）-类型1的测量事件数
count_event2（int）-类型2的测量事件数

我保证无行将重叠-在SQL talk中，没有两行time_start1 < time_start2 AND time_start1 + duration1 > time_start2。

我想设计一个高效的SQL查询，它将按任意时间段（我称之为group_period）对测量值进行分组，例如3小时。我已经尝试过这样的东西：

SELECT
    ROUND(time_start/group_period,0) AS time_period,
    SUM(count_event1) AS sum_event1,
    SUM(count_event2) AS sum_event2 
FROM measurements
GROUP BY time_period;

然而，似乎存在一个问题。如果有一个测量值的duration大于group_period，我希望这种测量值被分组到它所属的所有时间段中，但由于从不考虑持续时间，它只被分组到第一个时间段中。有办法解决这个问题吗？

性能是我关心的问题，因为随着时间的推移，我预计表的大小将大幅增长，达到数百万，可能是数千万或数亿行。您对索引或任何其他优化有什么建议以提高此查询的速度吗？

根据Timekiller的建议，我提出了以下查询：

-- Since there's a problem with declaring variables in PostgreSQL,
-- we will be using aliases for the arguments required by the script.
-- First some configuration:
--   group_period = 3600   -- group by 1 hour (= 3600 seconds)
--   min_time = 1440226301 -- Sat, 22 Aug 2015 06:51:41 GMT
--   max_time = 1450926301 -- Thu, 24 Dec 2015 03:05:01 GMT
-- Calculate the number of started periods in the given interval in advance.
--   period_count = CEIL((max_time - min_time) / group_period)
SET TIME ZONE UTC;
BEGIN TRANSACTION;
-- Create a temporary table and fill it with all time periods.
CREATE TEMP TABLE periods (period_start TIMESTAMP)
    ON COMMIT DROP;
INSERT INTO periods (period_start)
    SELECT to_timestamp(min_time + group_period * coefficient)
    FROM generate_series(0, period_count) as coefficient;
-- Group data by the time periods.
-- Note that we don't require exact overlap of intervals:
--   A. [period_start, period_start + group_period]
--   B. [time_start, time_start + duration]
-- This would yield the best possible result but it would also slow
-- down the query significantly because of the part B.
-- We require only: period_start <= time_start <= period_start + group_period
SELECT
    period_start,
    COUNT(measurements.*) AS count_measurements,
    SUM(count_event1) AS sum_event1,
    SUM(count_event2) AS sum_event2
FROM periods
LEFT JOIN measurements
ON time_start BETWEEN period_start AND (period_start + group_period)
GROUP BY period_start;
COMMIT TRANSACTION;

它正是我想要的，所以任务完成了。然而，如果有人能给我一些关于以下条件下该查询性能的反馈，我将不胜感激：

我预计measurements表将有大约5到8亿行
time_start0列是主键，它具有唯一的btree索引
我对min_time和max_time没有任何保证。我只知道组周期会被选择为500 <= period_count <= 2000

（这对于注释来说太大了，所以我将把它作为答案发布）。

在我对你的回答的评论中，你可能应该先得到最好的结果，然后再优化，如果结果很慢的话。

至于性能，我在使用数据库时学到的一件事是，您无法真正预测性能。高级DBMS中的查询优化器很复杂，在小型和大型数据集上的行为往往不同。你必须用一些大样本数据填充你的表，对索引进行实验，并读取EXPLAIN的结果，没有其他方法了。

有一些建议，尽管我比Postgres更了解Oracle优化器，所以其中一些可能不起作用。

如果您要检查的所有字段都包含在索引中，情况会更快。由于您正在执行左联接，并且periods是基础，因此可能没有理由对其进行索引，因为无论哪种方式，它都将被完全包含。duration应该包含在索引中，但是，如果要使用适当的间隔重叠——这样，Postgres就不必获取行来计算联接条件，索引就足够了。它甚至可能根本不会获取表行，因为除了索引中存在的数据之外，它不需要其他数据。我认为如果它被作为time_start索引的第二个字段包含，它会表现得更好，至少在Oracle中是这样，但IIRC Postgres能够将索引连接在一起，所以也许第二个索引会表现得更好——你必须用EXPLAIN来检查它。
索引和数学不能很好地结合在一起。即使duration包含在索引中，也不能保证它会在duration0中使用——不过，还是先看看EXPLAIN。如果不使用它，请尝试创建一个基于函数的索引（即将time_start + duration作为字段包含在内），或者稍微更改表的结构，使time_start + duration成为一个单独的列，并对该列进行索引。
如果您真的不需要左联接（也就是说，您可以忽略空的句点），那么就使用内部联接——优化器可能会从一个更大的表（测量值）开始，并对其使用联接句点，可能会使用散列联接而不是嵌套循环。如果您这样做了，那么您也应该以同样的方式索引您的周期表，也许可以以同样的方法对其进行重组，使其显式地包含开始和结束周期，因为优化器在不必对列执行任何操作时有更多的选择。
也许最重要的是，如果您有max_time和min_time，请在加入之前使用它来限制measurements的结果！你的电视机越小，工作就越快。

相关内容

最新更新

热门标签：