时间戳

首页 > 解决方案 > 加速查询，其中包含 count(*) = 0 的结果

sql - 加速查询，其中包含 count(*) = 0 的结果

问题描述

我有一张桌子squitters，其中包括一列parsed_time。我想知道过去两天每小时的记录数并使用了这个查询：

SELECT date_trunc('hour', parsed_time) AS hour , count(*) 
FROM squitters 
WHERE parsed_time > date_trunc('hour', now()) - interval '2 day' 
GROUP BY hour 
ORDER BY hour DESC;

这可行，但零记录的小时数不会出现在结果中。我想在结果中也有零记录的小时数为零，所以我使用generate_series函数编写了这个查询：

SELECT bins.hour, count(squitters.parsed_time)
FROM generate_series(date_trunc('hour', now() - interval '2 day'),  now(), '1 hour') bins(hour)
LEFT OUTER JOIN squitters ON bins.hour = date_trunc('hours', squitters.parsed_time) 
GROUP BY bins.hour
ORDER BY bins.hour DESC;

这有效，结果是计数为零的小时箱，但速度要慢得多。

如何使用第二个查询的 count=zero 结果获得第一个查询的速度？

（顺便说一句。有一个索引parsed_time）

标签： sqlpostgresqldategroup-byquery-optimization

解决方案

您可以尝试更改连接条件，以便在列上不应用日期函数parsed_time：

SELECT b.hour, COUNT(s.parsed_time) cnt
FROM generate_series(date_trunc('hour', now() - interval '2 day'),  now(), '1 hour') b(hour)
LEFT OUTER JOIN squitters s
    ON  s.parsed_time >= b.hour
    AND s.parsed_time <  b.hours + interval '1 hour'
GROUP BY b.hour
ORDER BY b.hour DESC;

或者，您也可以尝试使用相关子查询（或横向连接）而不是left join- 这避免了对外部聚合的需要：

SELECT 
    b.hour,
    (
        SELECT COUNT(*) 
        FROM squitters s 
        WHERE s.parsed_time >= b.hour AND s.parsed_time <  b.hours + interval '1 hour'
    ) cnt
FROM generate_series(date_trunc('hour', now() - interval '2 day'),  now(), '1 hour') b(hour)
ORDER BY b.hour desc

推荐阅读