我编写了查询,它为我提供了某个日期范围和间隔的时间序列,显示每个时间间隔的收入:
SELECT
interval_date,
coalesce(campaign_revenue,0) AS campaign_revenue,
FROM
-- generate_series helps fill the empty gaps in the following JOIN
generate_series(
$2::timestamp,
$3::timestamp,
$4) AS interval_date -- could be '1 day', '1 hour' or '1 minute'.
LEFT OUTER JOIN
-- This SELECT gets all timeseries rows that have data
(SELECT
date_trunc($4, s.created) AS interval,
SUM(s.revenue) campaign_revenue
FROM
sale_event AS s
WHERE
s.campaignid = $1 AND s.created BETWEEN $2 AND $3 AND s.event_type = 'session_closed'
GROUP BY
interval) results
ON
(results.interval = interval_date);
该查询获取表的每一行sale_event
,将创建的日期截断为某个时间间隔(将created
时间戳与所需的时间序列粒度对齐),按此时间间隔进行分组,并对行中的列revenue
求和。event_type
session_closed
这非常有效,并在指定的时间间隔内为我提供了收入。结果可能如下所示:
interval_date | campaign_revenue
------------------------------------
2018-08-05 | 0.0
2018-08-06 | 1.5
2018-08-07 | 0.0
2018-08-08 | 0.5
2018-08-09 | 1.0
当提供的范围是2018-08-05 - 2018-08-09
和时interval = '1 day'
。
我想将截至该日期的收入总和添加到结果中。因此,如果在2018-08-05
总收入为之前10.0
,结果将是:
interval_date | campaign_revenue | total_campaign_revenue
-----------------------------------------------------------------
2018-08-05 | 0.0 | 10.0
2018-08-06 | 1.5 | 11.5
2018-08-07 | 0.0 | 11.5
2018-08-08 | 0.5 | 12.0
2018-08-09 | 1.0 | 13.0
在一次扫描中从基础表中读取所有相关行通常更快。
您可以在同一
SELECT
.测试
EXPLAIN (ANALYZE, TIMING OFF)
以验证它实际上更快:自动排除子查询中的
JOIN
前导多余行。sum(sum(revenue)) OVER (ORDER BY date_trunc($4, created))
有效,因为引用手册:正是你需要的。
有关的:
剩余的弱点:没有收入的间隔缺少总数。如果这不可接受,我们可以使用此技术来修复:
由于增加了开销,我不确定它是否可以竞争。如果您的选择很小并且桌子很大,仍然可能。
小提示:
您不需要围绕连接条件的括号。
不要将您的时间戳称为“日期”。这是误导。我使用
interval_ts
而不是interval_date
.我宁愿不使用SQL 关键字
interval
作为列别名——即使 Postgres 允许这样做。使用相同的列别名
interval_ts
以允许更短的USING
语法 - 这确实需要括号。这仅将连接列的一个实例暴露interval_ts
给外部查询,因此非限定名称仍然没有歧义。不要省略
AS
列别名的关键字(虽然这对于表别名来说是可以的)。如果我做对了,您可以在查询之外添加一个窗口函数,例如:
另一种选择是直接应用窗口函数,并使用 FILTER 子句
campaign_revenue