SELECT * FROM events WHERE EXTRACT(month FROM "date") = 3 AND EXTRACT(day FROM "date") < EXTRACT(day FROM "date") + 14
这个问题是几个月包装.
我宁愿做这样的事情,但我不知道如何忽视这一年.
SELECT * FROM events WHERE (date > '2013-03-01' AND date < '2013-04-01')
如何在Postgres中完成这样的日期数学？
解决方法
目前所呈现的所有查询都是以not sargable的条件进行操作的 – 它们不能使用索引,并且必须为基表中的每一行计算表达式以找到匹配的行.用小桌子,这并不重要.然而,使用大桌子,这很重要.
给出以下简单表：
CREATE TABLE event ( event_id serial PRIMARY KEY,event_date date );
询问
版本1.和2.可以使用一个简单的索引形式：
CREATE INDEX event_event_date_idx ON event(event_date);
但是,没有索引,以下解决方案更快.
简单版本
SELECT * FROM ( SELECT ((current_date + d) - interval '1 year' * y)::date AS event_date FROM generate_series(0,14) d CROSS JOIN generate_series(13,113) y ) x JOIN event USING (event_date);
子查询x从两个generate_series()调用的CROSS JOIN计算在给定范围内的所有可能日期.选择是通过简单的等式连接完成的.
2.高级版本
WITH val AS ( SELECT extract(year FROM age(now()::date + 14,min(event_date)))::int AS max_y,extract(year FROM age(now()::date,max(event_date)))::int AS min_y FROM event ) SELECT e.* -- count(*) -- FROM ( SELECT ((current_date + d) - interval '1y' * y.y)::date AS event_date FROM generate_series(0,14) AS d) d,(SELECT generate_series(min_y,max_y) AS y FROM val) y ) x JOIN event e USING (event_date);
年份范围自动从表中推算出来 – 从而最大限度地减少了生成的年份.
如果您的年龄有差距,您甚至可以进一步提炼现有年份的清单.
有效性依赖于日期的分布.几年中有很多行,使我的解决方案更有用.许多年,几行都使它不太有用.
黑魔法版
后来我有一个这个全新的方法的想法.
更新了02.2016,从解决方案中删除不必要的“生成列”,这将阻止H.O.T更新,并使用更简单更快的功能.
CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS $$SELECT to_char($1,'MMDD')::int$$;
它必须是IMMUTABLE用于索引：
CREATE INDEX event_mmdd_event_date_idx ON event(f_mmdd(event_date),event_date);
多列索引 – 原因如下：
可以帮助ORDER BY或从给定的年份中选择.阅读here.索引几乎没有额外的费用.一个日期适合4个字节,否则将由于数据对齐而丢失到填充.阅读here.
另外,由于两个索引列都引用相同的表格列,所以没有关于H.O.T.的缺点.更新.阅读here.
叉到两个查询之一来覆盖年底.
CREATE OR REPLACE FUNCTION f_anniversary(date = current_date,int = 14)
RETURNS SETOF event AS
$func$
DECLARE
d int := f_mmdd($1);
d1 int := f_mmdd($1 + $2 - 1); -- fix off-by-1 due to including upper bound
BEGIN
IF d1 > d THEN
RETURN QUERY
SELECT *
FROM event e
WHERE f_mmdd(e.event_date) BETWEEN d AND d1
ORDER BY f_mmdd(e.event_date),e.event_date;
ELSE -- wrap around end of year
RETURN QUERY
SELECT *
FROM event e
WHERE f_mmdd(e.event_date) >= d OR
f_mmdd(e.event_date) <= d1
ORDER BY (f_mmdd(e.event_date) >= d) DESC,f_mmdd(e.event_date),event_date;
-- chronological across turn of the year
END IF;
END
$func$ LANGUAGE plpgsql ;
使用默认值调用：从“今天”开始14天：
SELECT * FROM f_anniversary();
从“2014-08-23”开始要求7天
SELECT * FROM f_anniversary('2014-08-23'::date,7);
SQL Fiddle比较EXPLAIN ANALYZE.
二月二十九日
在处理周年纪念日或“生日”时,您需要定义如何处理闰年2月29日的特殊情况.
当测试日期范围时,通常自动包含2月29日,即使本年度不是闰年.当天覆盖这一天,时间范围扩大了1.
另一方面,如果今年是一个闰年,而你想要寻找15天,如果您的数据来自非闰年,您可能会在闰年内获得14天的结果.
说,鲍勃出生于二月二十九日：
我的查询1.和2.包括2月29日只在闰年.鲍勃每年〜4年都有生日.
我的查询3.包括2月29日在范围内.鲍勃每年都有生日.
没有神奇的解决方案你必须为每种情况定义你想要的.
测试
为了证实我的观点,我对所有提出的解决方案进行了广泛的测试.我将每个查询改为给定的表,并在没有ORDER BY的情况下产生相同的结果.
好消息：所有这些都是正确的,并产生相同的结果 – 除了具有语法错误的Gordon查询和@年份外围(易于修复)的@ wildplasser的查询失败.
插入108000行,随机日期从20世纪,类似于一个活着的人(13岁或以上)的表.
INSERT INTO event (event_date) SELECT '2000-1-1'::date - (random() * 36525)::int FROM generate_series (1,108000);
删除〜8％创建一些死元组,使表更“现实生活”.
DELETE FROM event WHERE random() < 0.08; ANALYZE event;
我的测试用例有99289行,4012次点击.
C – Catcall
WITH anniversaries as ( SELECT event_id,event_date,(event_date + (n || ' years')::interval)::date anniversary FROM event,generate_series(13,113) n ) SELECT event_id,event_date -- count(*) -- FROM anniversaries WHERE anniversary BETWEEN current_date AND current_date + interval '14' day;
C1 – Catcall的想法重写
除了轻微的优化之外,主要的区别是仅添加date_trunc(‘year’,age(current_date 14,event_date))的确切年数,以获得今年的周年纪念,从而避免了对CTE的需求：
SELECT event_id,event_date
FROM event
WHERE (event_date + date_trunc('year',age(current_date + 14,event_date)))::date
BETWEEN current_date AND current_date + 14;
D – Daniel
SELECT * -- count(*) -- FROM event WHERE extract(month FROM age(current_date + 14,event_date)) = 0 AND extract(day FROM age(current_date + 14,event_date)) <= 14;
E1 – 欧文1
请参阅上面的“1.简单版本”.
E2 – 欧文2
请参阅上面的“2.高级版”.
E3 – 欧文3
见上面的“3.魔法版”.
G – Gordon
SELECT * -- count(*)
FROM (SELECT *,to_char(event_date,'MM-DD') AS mmdd FROM event) e
WHERE to_date(to_char(now(),'YYYY') || '-'
|| (CASE WHEN mmdd = '02-29' THEN '02-28' ELSE mmdd END),'YYYY-MM-DD') BETWEEN date(now()) and date(now()) + 14;
WITH upcoming as (
SELECT event_id,CASE
WHEN date_trunc('year',age(event_date)) = age(event_date)
THEN current_date
ELSE cast(event_date + ((extract(year FROM age(event_date)) + 1)
* interval '1' year) AS date)
END AS next_event
FROM event
)
SELECT event_id,event_date
FROM upcoming
WHERE next_event - current_date <= 14;
W – wildplasser
CREATE OR REPLACE FUNCTION this_years_birthday(_dut date) RETURNS date AS
$func$
DECLARE
ret date;
BEGIN
ret :=
date_trunc( 'year',current_timestamp)
+ (date_trunc( 'day',_dut)
- date_trunc( 'year',_dut));
RETURN ret;
END
$func$LANGUAGE plpgsql;
简化为所有其他回报：
SELECT *
FROM event e
WHERE this_years_birthday( e.event_date::date )
BETWEEN current_date
AND current_date + '2weeks'::interval;
W1 – 改写了wildplasser的查询
以上这些都有一些低效率的细节(超出了这个已经相当大的职位的范围).重写版本更快：
CREATE OR REPLACE FUNCTION this_years_birthday(_dut INOUT date) AS
$func$
SELECT (date_trunc('year',now()) + ($1 - date_trunc('year',$1)))::date
$func$LANGUAGE sql;
SELECT *
FROM event e
WHERE this_years_birthday(e.event_date)
BETWEEN current_date
AND (current_date + 14);
检测结果
我在Postgresql 9.1.7上运行了一个临时表的测试.
结果采用EXPLAIN ANALYZE收集,最好为5.
结果
Without index C: Total runtime: 76714.723 ms C1: Total runtime: 307.987 ms -- ! D: Total runtime: 325.549 ms E1: Total runtime: 253.671 ms -- ! E2: Total runtime: 484.698 ms -- min() & max() expensive without index E3: Total runtime: 213.805 ms -- ! G: Total runtime: 984.788 ms H: Total runtime: 977.297 ms W: Total runtime: 2668.092 ms W1: Total runtime: 596.849 ms -- ! With index E1: Total runtime: 37.939 ms --!! E2: Total runtime: 38.097 ms --!! With index on expression E3: Total runtime: 11.837 ms --!!
所有其他查询与索引执行或不使用索引相同,因为它们使用不可用的表达式.
Conclusio
>到目前为止,丹尼尔的查询是最快的.> @wildplassers(重写)方法也可以接受.> @ Catcall的版本就像我的相反方法.性能随着更大的桌子快速失控.虽然重写的版本表现不错,我使用的表达方式就像一个简单版本的@ wildplassser的this_years_birthday()函数.>我的“简单版本”即使没有索引也更快,因为它需要较少的计算.>使用索引,“高级版本”与“简单版本”一样快,因为min()和max()变得非常便宜,具有索引.两者都比其他不能使用索引的要快得多.>我的“黑魔法”最快,有或没有索引.而且很简单.更新版本(在基准测试之后)还要快一些.>在现实生活中,一个指数将会有更大的差异.更多的列使表更大,顺序扫描更昂贵,而索引大小保持不变.
