基本上, 像您已经尝试过的那样,完成年度工作就可以了。您只是忘记了 也 将它应用于加入generate_series()
之前 生成的时间戳。以及其他一些小细节。
为了简化并提高性能和方便性,我建议使用此简单函数根据integer
给定的模式“ MMDD”来计算timestamp
。
CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int';
我to_char(time, 'MMDD')
刚开始使用,但是切换到上面的表达式,结果证明在各种测试中速度最快。
db <>在这里拨弄
由于已定义,因此可以在表达式索引中使用IMMUTABLE
。并且它仍然允许函数内联,因为它仅使用EXTRACT (xyz FROM date)
-通过内部IMMUTABLE
函数实现date_part(text, date)
。(请注意,datepart(text, timestamptz)
只是STABLE
)。
然后,这种查询就可以完成工作:
SELECT d.mmdd, COALESCE(ct.ct, 0) AS total_count
FROM (
SELECT f_mmdd(d::date) AS mmdd -- ignoring the year
FROM generate_series(timestamp '2018-01-01' -- any dummy year
, timestamp '2018-12-31'
, interval '1 day') d
) d
LEFT JOIN (
SELECT f_mmdd(time::date) AS mmdd, count(*) AS ct
FROM counties c
JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
WHERE cwa = 'MFR'
GROUP BY 1
) ct USING (mmdd)
ORDER BY 1;
由于time
(我将使用其他列名)是数据类型,timestamptz
因此强制类型转换time::date
取决于当前会话的时区设置。(“天”由您所在的时区定义。)要获得不可变(但较慢)的结果,请使用AT TIME ZONE
具有时区 名称 的结构,例如:
SELECT f_mmdd((time AT TIME ZONE 'Europe/Vienna')::date) ...
格式化mmdd
任何您喜欢的显示方式。
integer
对于此特定查询,强制转换为可选。但是,由于您打算进行各种查询,因此最终需要在表达式上添加索引:
CREATE INDEX ltg_data_mmdd_idx ON event(f_mmdd(time));
( 此 查询不需要。)integer
为此目的要快一些。
并且您需要(否则是可选的)
函数包装,因为
更新的表达式to_char()
它只是被定义的
STABLE
,但是我们需要
IMMUTABLE
索引。
(EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int
是IMMUTABLE
,但是函数包装器仍然很方便。