sql - 聚合时间序列的高效子查询

Question

我想从某个日期开始每天构建一个时间序列，并为每一天计算一些统计数据。但是这个查询非常慢......有什么办法可以加快速度吗？ (例如，在子查询中选择表一次并计算该表每天的各种统计信息)。

在代码中这看起来像

for i, day in series:
    previous_days = series[0...i]
    some_calculation_a = some_operation_on(previous_days)
    some_calculation_b = some_other_operation_on(previous_days)

这是一个时间序列的示例，用于查找截至该日期有 <= 5 条消息的用户:

with
days as
(
select date::Timestamp with time zone from generate_series('2015-07-09', 
  now(), '1 day'::interval) date
),

msgs as 
(
select days.date, 
    (select count(customer_id) from daily_messages where sum < 5 and date_trunc('day'::text, created_at) <= days.date) as LT_5,
    (select count(customer_id) from daily_messages where sum = 1 and date_trunc('day'::text, created_at) <= days.date) as EQ_1
from days, daily_messages
where date_trunc('day'::text, created_at) = days.date
group by days.date
)

select * from msgs;

查询分割:

CTE Scan on msgs  (cost=815579.03..815583.03 rows=200 width=24)
  Output: msgs.date, msgs.lt_5, msgs.eq_1
  CTE days
    ->  Function Scan on pg_catalog.generate_series date  (cost=0.01..10.01 rows=1000 width=8)
          Output: date.date
          Function Call: generate_series('2015-07-09 00:00:00+00'::timestamp with time zone, now(), '1 day'::interval)
  CTE msgs
    ->  Group  (cost=6192.62..815569.02 rows=200 width=8)
          Output: days.date, (SubPlan 2), (SubPlan 3)
          Group Key: days.date
          ->  Merge Join  (cost=6192.62..11239.60 rows=287970 width=8)
                Output: days.date
                Merge Cond: (days.date = (date_trunc('day'::text, daily_messages_2.created_at)))
                ->  Sort  (cost=69.83..72.33 rows=1000 width=8)
                      Output: days.date
                      Sort Key: days.date
                      ->  CTE Scan on days  (cost=0.00..20.00 rows=1000 width=8)
                            Output: days.date
                ->  Sort  (cost=6122.79..6266.78 rows=57594 width=8)
                      Output: daily_messages_2.created_at, (date_trunc('day'::text, daily_messages_2.created_at))
                      Sort Key: (date_trunc('day'::text, daily_messages_2.created_at))
                      ->  Seq Scan on public.daily_messages daily_messages_2  (cost=0.00..1568.94 rows=57594 width=8)
                            Output: daily_messages_2.created_at, date_trunc('day'::text, daily_messages_2.created_at)
          SubPlan 2
            ->  Aggregate  (cost=2016.89..2016.90 rows=1 width=32)
                  Output: count(daily_messages.customer_id)
                  ->  Seq Scan on public.daily_messages  (cost=0.00..2000.89 rows=6399 width=32)
                        Output: daily_messages.created_at, daily_messages.customer_id, daily_messages.day_total, daily_messages.sum, daily_messages.elapsed
                        Filter: ((daily_messages.sum < '5'::numeric) AND (date_trunc('day'::text, daily_messages.created_at) <= days.date))
          SubPlan 3
            ->  Aggregate  (cost=2001.13..2001.14 rows=1 width=32)
                  Output: count(daily_messages_1.customer_id)
                  ->  Seq Scan on public.daily_messages daily_messages_1  (cost=0.00..2000.89 rows=96 width=32)
                        Output: daily_messages_1.created_at, daily_messages_1.customer_id, daily_messages_1.day_total, daily_messages_1.sum, daily_messages_1.elapsed
                        Filter: ((daily_messages_1.sum = '1'::numeric) AND (date_trunc('day'::text, daily_messages_1.created_at) <= days.date))

Answer 1

除了非常低效之外，我怀疑查询也不正确。假设当前的 Postgres 9.6，我有根据的猜测:

SELECT created_at::date
     , sum(count(customer_id) FILTER (WHERE sum < 5)) OVER w AS lt_5
     , sum(count(customer_id) FILTER (WHERE sum = 1)) OVER w AS eq_1
FROM   daily_messages m
WHERE  created_at >= timestamptz '2015-07-09'  -- sargable!
AND    created_at <  now()                     -- probably redundant
GROUP  BY 1
WINDOW w AS (ORDER BY created_at::date);

可能不需要所有那些相关的子查询。我将其替换为结合聚合 FILTER 子句的窗口函数。您可以在聚合函数上使用窗口函数。相关答案及更多解释:

• Postgres window function and group by exception
• Conditional lead/lag function PostgreSQL?

CTE 也无济于事(不必要的开销)。您只需要一个子查询 - 或者甚至不需要，只需要集合返回函数 generate_series() 的结果。 generate_series() 可以直接传递timestamptz。不过，请注意其含义。您查询取决于 session 的时区设置。详情:

• Ignoring timezones altogether in Rails and PostgreSQL

再三考虑，我完全删除了 generate_series()。只要您有 INNER JOIN 到 daily_messages，结果中只会保留实际行的天数。根本不需要 generate_series()。对 LEFT JOIN 有意义。问题中的信息不足。

解释“sargable”的相关答案:

• Return Table Type from A function in PostgreSQL

您可以将 count(customer_id) 替换为 count(*)。问题中的信息不足。

可能会进一步优化，但没有足够的信息在答案中更具体。

在结果中包括没有新条目的天数

SELECT day
     , sum(lt_5_day) OVER w AS lt_5
     , sum(eq_1_day) OVER w AS eq_1
FROM  (
   SELECT day::date
   FROM   generate_series(date '2015-07-09', current_date, interval '1 day') day
   ) d
LEFT   JOIN (
   SELECT created_at::date AS day
        , count(customer_id) FILTER (WHERE sum < 5) AS lt_5_day
        , count(customer_id) FILTER (WHERE sum = 1) AS eq_1_day
   FROM   daily_messages m
   WHERE  created_at >= timestamptz '2015-07-09'
   GROUP  BY 1
   ) m USING (day)
WINDOW w AS (ORDER BY day);

1. 在子查询 m 中汇总每日总和。
2. 在子查询 d 中生成时间范围内所有日期的系列。
3. 使用 LEFT [OUTER] JOIN 保留结果中的所有日期，即使当天没有新行也是如此。