我有一个SQL查询,其中包含一个日期参数(如果我将其放入函数中),我需要在过去一年的每一天运行它。
如何生成最近365天的列表,以便我可以使用原始的SQL来完成此操作?
显然,生成0..364的列表也可以工作,因为我总是可以:
SELECT SYSDATE - val FROM (...);
我有一个SQL查询,其中包含一个日期参数(如果我将其放入函数中),我需要在过去一年的每一天运行它。
如何生成最近365天的列表,以便我可以使用原始的SQL来完成此操作?
显然,生成0..364的列表也可以工作,因为我总是可以:
SELECT SYSDATE - val FROM (...);
没有必要使用额外大的表或 ALL_OBJECTS 表:
SELECT TRUNC (SYSDATE - ROWNUM) dt
FROM DUAL CONNECT BY ROWNUM < 366
这样就可以解决问题。
最近我遇到了类似的问题,并使用以下简单的查询解决了它:
SELECT
(to_date(:p_to_date,'DD-MM-YYYY') - level + 1) AS day
FROM
dual
CONNECT BY LEVEL <= (to_date(:p_to_date,'DD-MM-YYYY') - to_date(:p_from_date,'DD-MM-YYYY') + 1);
例子
SELECT
(to_date('01-05-2015','DD-MM-YYYY') - level + 1) AS day
FROM
dual
CONNECT BY LEVEL <= (to_date('01-05-2015','DD-MM-YYYY') - to_date('01-04-2015','DD-MM-YYYY') + 1);
结果
01-05-2015 00:00:00
30-04-2015 00:00:00
29-04-2015 00:00:00
28-04-2015 00:00:00
27-04-2015 00:00:00
26-04-2015 00:00:00
25-04-2015 00:00:00
24-04-2015 00:00:00
23-04-2015 00:00:00
22-04-2015 00:00:00
21-04-2015 00:00:00
20-04-2015 00:00:00
19-04-2015 00:00:00
18-04-2015 00:00:00
17-04-2015 00:00:00
16-04-2015 00:00:00
15-04-2015 00:00:00
14-04-2015 00:00:00
13-04-2015 00:00:00
12-04-2015 00:00:00
11-04-2015 00:00:00
10-04-2015 00:00:00
09-04-2015 00:00:00
08-04-2015 00:00:00
07-04-2015 00:00:00
06-04-2015 00:00:00
05-04-2015 00:00:00
04-04-2015 00:00:00
03-04-2015 00:00:00
02-04-2015 00:00:00
01-04-2015 00:00:00
SELECT (sysdate-365 + (LEVEL -1)) AS DATES
FROM DUAL connect by level <=( sysdate-(sysdate-365))
如果将sysdate和sysdate-365替换为“from”和“to”日期,则输出结果将是从“from”到“to”日期之间的一系列日期。这是Oracle特有的功能,并且不依赖于预先存在的大型表或复杂的系统视图,也不依赖于数据字典对象。
SELECT c1 from dual
MODEL DIMENSION BY (1 as rn) MEASURES (sysdate as c1)
RULES ITERATE (365)
(c1[ITERATION_NUMBER]=SYSDATE-ITERATION_NUMBER)
order by 1
Oracle中经常使用的一种方法是这样的:
select trunc(sysdate)-rn
from
( select rownum rn
from dual
connect by level <= 365)
/
就我个人而言,如果一个应用程序需要一系列日期,则我会创建一个包含这些日期的表格,或者创建一个包含一百万个整数序列的表格,可用于此类事情。
日期范围为1996年12月31日至2020年12月31日
SELECT dt, to_char(dt, 'MM/DD/YYYY') as date_name,
EXTRACT(year from dt) as year,
EXTRACT(year from fiscal_dt) as fiscal_year,
initcap(to_char(dt, 'MON')) as month,
to_char(dt, 'YYYY') || ' ' || initcap(to_char(dt, 'MON')) as year_month,
to_char(fiscal_dt, 'YYYY') || ' ' || initcap(to_char(dt, 'MON')) as fiscal_year_month,
EXTRACT(year from dt)*100 + EXTRACT(month from dt) as year_month_id,
EXTRACT(year from fiscal_dt)*100 + EXTRACT(month from fiscal_dt) as fiscal_year_month_id,
to_char(dt, 'YYYY') || ' Q' || to_char(dt, 'Q') as quarter,
to_char(fiscal_dt, 'YYYY') || ' Q' || to_char(fiscal_dt, 'Q') as fiscal_quarter
--, EXTRACT(day from dt) as day_of_month, to_char(dt, 'YYYY-WW') as week_of_year, to_char(dt, 'D') as day_of_week
FROM (
SELECT dt, add_months(dt, 6) as fiscal_dt --starts July 1st
FROM (
SELECT TO_DATE('12/31/1996', 'mm/dd/yyyy') + ROWNUM as dt
FROM DUAL CONNECT BY ROWNUM < 366 * 30 --30 years
)
WHERE dt <= TO_DATE('12/31/2020', 'mm/dd/yyyy')
)
SELECT (date'2015-08-03' + (LEVEL-1)) AS DATES
FROM DUAL
where ROWNUM < 8
connect by level <= (sysdate-date'2015-08-03');
如果省略 ROWNUM,则只会获取50行数据,与值无关。
SELECT calendar_date
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date between '2010-01-01' (date) and '2010-01-03' (date)
select SYSDATE - ROWNUM
from shipment_weights sw
where ROWNUM < 365;
where shipment_weights 是任何大型表;
SELECT DAY, offset
FROM (SELECT to_char(SYSDATE, 'DD-MON-YYYY') AS DAY, 0 AS offset
FROM DUAL
UNION ALL
SELECT to_char(SYSDATE - rownum, 'DD-MON-YYYY'), rownum
FROM all_objects d)
where offset <= &No_of_days
我将上述结果集作为驱动视图,在涉及具有日期的表的其他视图中使用LEFT OUTER JOIN
。