sql – 你怎么做无视年份的日期数学？

发布时间：2020-12-12 16:31:31 所属栏目：MsSql教程来源：网络整理

导读：我想在未来14天内选择有周年纪念日.如何选择不包括年份的日期？我已经尝试过如下. SELECT * FROM eventsWHERE EXTRACT(month FROM "date") = 3AND EXTRACT(day FROM "date") EXTRACT(day FROM "date") + 14 这个问题是几个月包装. 我宁愿做这样的事情,但我不

我想在未来14天内选择有周年纪念日.如何选择不包括年份的日期？我已经尝试过如下.

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);

年份范围自动从表中推算出来 – 从而最大限度地减少了生成的年份.
如果您的年龄有差距,您甚至可以进一步提炼现有年份的清单.

有效性依赖于日期的分布.几年中有很多行,使我的解决方案更有用.许多年,几行都使它不太有用.

Simple SQL Fiddle玩.

黑魔法版

后来我有一个这个全新的方法的想法.
更新了02.2016,从解决方案中删除不必要的“生成列”,这将阻止H.O.T更新,并使用更简单更快的功能.

简单的SQL函数从模式’MMDD’中计算一个整数.

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.

一个PL / pgSQL表函数来统治它们

叉到两个查询之一来覆盖年底.

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;

H – a_horse_with_no_name

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()变得非常便宜,具有索引.两者都比其他不能使用索引的要快得多.>我的“黑魔法”最快,有或没有索引.而且很简单.更新版本(在基准测试之后)还要快一些.>在现实生活中,一个指数将会有更大的差异.更多的列使表更大,顺序扫描更昂贵,而索引大小保持不变.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!