我正在查询一个包含议程表的postgresql数据库:
agenda |> id (int) | start (timestamp) | end (timestamp) | facname | .....
我想以"时间表"的形式对一天进行一种总结,该时间表由每 15 分钟间隔的小图片组成:根据设施的可用性开/关。
现在,每 15 分钟查询一次数据库并检查是否存在保留并更改 img 源相对简单。
但是,如果您想概述 10 天和 5 种不同的设施,您最终会查询数据库
10(days) * 36(quaters a day) * 5 (facilities) = 1800 database querys/page load.
因此,这导致了非常沉重的工资负担。
有没有办法减少查询量,从而减少有效负载?
为了解决这个问题,我认为我们可以首先找到一种方法,给定一个时间戳,找到它属于哪一刻钟。例如,小时08:38
属于四分之一08:30
,08:51
属于08:45
,等等。
为此,我们可以使用这样的函数:
CREATE FUNCTION date_trunc_quarter(timestamp )
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
AS $$
SELECT * FROM
generate_series(
date_trunc('hour',$1),
date_trunc('hour',$1)+interval '1hour',
interval '15min'
) AS gen(quarter)
WHERE gen.quarter < $1
ORDER BY gen.quarter
DESC LIMIT 1
$$;
它使用 generate_series
函数在给定时间戳(例如 08:38)的同一小时内生成所有四个季度(例如 08:00、08:15、08:30 和 08:45),确实得到它使用众所周知的 date_trunc
函数的给定小时。然后,它只过滤比给定时间戳小的季度,对其进行排序并获得较大的季度。由于它最多只有四个值,因此对其进行排序不是一个大问题。
现在,有了它,您可以像这样轻松查询:
SELECT date_trunc_quarter(tstart) AS quarter, count(*)
FROM agenda
GROUP BY quarter
ORDER BY quarter;
我认为它足够快,为了使其更快,您可以在议程上创建一个表达式索引:
CREATE INDEX idx_agenda_quarter ON agenda ((date_trunc_quarter(tstart)));
看看这个小提琴,其中包含一个独立的测试用例。