一周前计算

Question

我有一个表格，每周填充一次信息，其中填充日期为表中的单列。

我正在处理一个查询，该查询基本上仅将本周的数据与上周的数据进行比较，因此我需要知道上周是哪一周。这很容易通过以下方式完成：

WHERE TO_CHAR(LASTWEEK.DATE_GENERATED, 'IW') = TO_CHAR(SYSDATE, 'IW') -1
  AND TO_CHAR(LASTWEEK.DATE_GENERATED, 'YY') = TO_CHAR(SYSDATE, 'YY')

该表可能包含几年的数据，因此我还会检查年份是否相同。

当我们处于一年的第一周并且我们想与前一周进行比较时，问题就来了。这样我们就在第 1 周减去 1，它给了我们 0，但我希望它与去年的第 54 周进行比较。

有没有办法做到这一点？

我觉得 TRUNC 函数可能适用于此，但我不确定在这种情况下如何使用它，还要记住它不应该与同年进行比较，而是与去年进行比较。

提前感谢！

Answer 1

我会完全避免在这个事件中使用TO_CHAR......保持日期格式...更安全，尤其是在突破年份时.

 where trunc(LASTWEEK.DATE_GENERATED,'IW') = trunc(sysdate-7,'IW')

这个单一的条件应该给你:)

你可以在这里看到它在做什么：

    1  with w_data as (select sysdate-level d from dual connect by level <= 20 )
    2  select d, trunc(d, 'IW') w
    3* from w_data
  SQL> /
  D                    W
  -------------------- --------------------
  09-feb-2016 13:20:41 08-feb-2016 00:00:00
  08-feb-2016 13:20:41 08-feb-2016 00:00:00
  07-feb-2016 13:20:41 01-feb-2016 00:00:00
  06-feb-2016 13:20:41 01-feb-2016 00:00:00
  05-feb-2016 13:20:41 01-feb-2016 00:00:00
  04-feb-2016 13:20:41 01-feb-2016 00:00:00
  03-feb-2016 13:20:41 01-feb-2016 00:00:00
  02-feb-2016 13:20:41 01-feb-2016 00:00:00
  01-feb-2016 13:20:41 01-feb-2016 00:00:00
  31-jan-2016 13:20:41 25-jan-2016 00:00:00
  30-jan-2016 13:20:41 25-jan-2016 00:00:00
  29-jan-2016 13:20:41 25-jan-2016 00:00:00
  28-jan-2016 13:20:41 25-jan-2016 00:00:00
  27-jan-2016 13:20:41 25-jan-2016 00:00:00
  26-jan-2016 13:20:41 25-jan-2016 00:00:00
  25-jan-2016 13:20:41 25-jan-2016 00:00:00
  24-jan-2016 13:20:41 18-jan-2016 00:00:00
  23-jan-2016 13:20:41 18-jan-2016 00:00:00
  22-jan-2016 13:20:41 18-jan-2016 00:00:00
  21-jan-2016 13:20:41 18-jan-2016 00:00:00
  20 rows selected.

Answer 2

您可以使用 sysdate - 7 与一周前的日期进行比较，并与单个值组合：

WHERE TO_CHAR(LASTWEEK.DATE_GENERATED, 'IYYYIW') = TO_CHAR(SYSDATE - 7, 'IYYYIW')

请注意，我使用的是 IYYY 年模型来匹配 IW 年模型，否则你会得到奇怪的结果。

但正如@ditto所示，将 TRUNC 与 IW 格式掩码一起使用会更干净。正如@MaxU指出的那样，截断源列将阻止使用索引，除非您有一个匹配的基于函数的索引。您可以通过稍微修改同上的方法来避免这种情况：

WHERE LASTWEEK.DATE_GENERATED >= TRUNC(SYSDATE - 7, 'IW')
AND LASTWEEK.DATE_GENERATED < TRUNC(SYSDATE, 'IW');

作为演示：

with lastweek(date_generated) as (
  select sysdate - level from dual connect by level <= 50
)
SELECT LASTWEEK.DATE_GENERATED
FROM LASTWEEK
WHERE LASTWEEK.DATE_GENERATED >= TRUNC(SYSDATE - 7, 'IW')
AND LASTWEEK.DATE_GENERATED < TRUNC(SYSDATE, 'IW');
DATE_GENERATED
--------------
2016-02-07    
2016-02-06    
2016-02-05    
2016-02-04    
2016-02-03    
2016-02-02    
2016-02-01    

或者要看到年份的变化，让我们进一步回顾，再次作为演示：

with lastweek(date_generated) as (
  select sysdate - level from dual connect by level <= 50
)
SELECT LASTWEEK.DATE_GENERATED
FROM LASTWEEK
WHERE LASTWEEK.DATE_GENERATED >= TRUNC(SYSDATE - 42, 'IW')
AND LASTWEEK.DATE_GENERATED < TRUNC(SYSDATE - 35, 'IW');
DATE_GENERATED
--------------
2016-01-03    
2016-01-02    
2016-01-01    
2015-12-31    
2015-12-30    
2015-12-29    
2015-12-28    

Answer 3

来自

同上的查询将起作用，但它不会使用"LASTWEEK"上的索引。DATE_GENERATED"列，因为应用了函数（trunc），所以我会选择以下内容：

select
    next_day(to_date('2016-01-10','yyyy-mm-dd')-14, 'MONDAY'),
    next_day(to_date('2016-01-10','yyyy-mm-dd')-14, 'SUNDAY')
from dual;

输出：

12/28/2015        01/03/2016

因此，您的查询将是：

WHERE LASTWEEK.DATE_GENERATED
between next_day(to_date('2016-01-10','yyyy-mm-dd')-14, 'MONDAY')
and next_day(to_date('2016-01-10','yyyy-mm-dd')-14, 'SUNDAY')