如何在 PL/SQL 中迭代日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/987610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to iterate over a date range in PL/SQL
提问by Adam Carr
I need to write a report that generates summary totals against a table with date ranges for each record.
我需要编写一个报告，根据每个记录的日期范围生成汇总表。
table data:
option start_date end_date
opt1 6/12/2009 6/19/2009
opt1 6/3/2009 6/13/2009
opt2 6/5/2009 6/6/2009
What I want out is basically this:
我想要的基本上是这样的：
date option count
6/1/2009 opt1 0
6/1/2009 opt2 0
6/2/2009 opt1 0
6/2/2009 opt2 0
6/3/2009 opt1 0
6/3/2009 opt2 1
I am having a hard time figuring out how to iterate over a date range. I am sure this is some simple cursor that could be created for this but I am at a loss. Preferably in PL/SQL
我很难弄清楚如何迭代一个日期范围。我确信这是一些可以为此创建的简单游标，但我不知所措。最好使用 PL/SQL
UPDATE:
更新：
I ended up using the example hereto accomplish what I wanted to do. This creates a function that generates a table of dates.
我最终使用这里的示例来完成我想做的事情。这将创建一个生成日期表的函数。
回答by Vincent Malgrat
You will need some sort of calendar to loop through a range of date. I have built one using the connect by leveltrick. You can then join the calendar with your data (cross join since you want a row even when there is no option for that day):
您将需要某种日历来遍历日期范围。我已经使用按级别连接技巧构建了一个。然后，您可以将您的数据加入日历（交叉加入，因为即使当天没有选项，您也需要一行）：
SQL> WITH calendar AS (
2 SELECT to_date(:begin_date, 'mm/dd/yyyy') + ROWNUM - 1 c_date
3 FROM dual
4 CONNECT BY LEVEL <= to_date(:end_date, 'mm/dd/yyyy')
- to_date(:begin_date, 'mm/dd/yyyy') + 1
5 )
6 SELECT c_date "date", d_option "option", COUNT(one_day)
7 FROM (SELECT c.c_date, d.d_option,
8 CASE
9 WHEN c.c_date BETWEEN d.start_date AND d.end_date THEN
10 1
11 END one_day
12 FROM DATA d, calendar c)
13 GROUP BY c_date, d_option
14 ORDER BY 1,2;
date option COUNT(ONE_DAY)
----------- ------ --------------
01/06/2009 opt1 0
01/06/2009 opt2 0
02/06/2009 opt1 0
02/06/2009 opt2 0
03/06/2009 opt1 1
03/06/2009 opt2 0
04/06/2009 opt1 1
04/06/2009 opt2 0
05/06/2009 opt1 1
05/06/2009 opt2 1
06/06/2009 opt1 1
06/06/2009 opt2 1
12 rows selected
回答by drakkanraz
One solution that I use for this is to convert the date range into an integer range that you can use in a for loop, then convert back to a date to do stuff with it. You can't do any joins or anything this way, but it's a much smaller solution that those already posted:
我为此使用的一种解决方案是将日期范围转换为可以在 for 循环中使用的整数范围，然后转换回日期以对其进行处理。您不能以这种方式进行任何连接或任何操作，但它是一个比那些已经发布的解决方案小得多的解决方案：
declare
start_date number;
end_date number;
business_date varchar2(8);
begin
start_date := to_number(to_char(to_date('2013-04-25', 'yyyy-MM-dd'), 'j'));
end_date := to_number(to_char(to_date('2013-05-31', 'yyyy-MM-dd'), 'j'));
for cur_r in start_date..end_date loop
business_date := to_char(to_date(cur_r, 'j'), 'yyyy-MM-dd');
dbms_output.put_line(business_date);
end loop;
end;
回答by Sarah Vessels
Just as an addition to the other techniques, one way I iterate over dates is the following:
作为对其他技术的补充，我迭代日期的一种方法如下：
/* List of days for the past year, starting with today at midnight */
SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 365
回答by bozone
Here is an answer based on an answer above: It uses a start and end date:
这是基于上述答案的答案：它使用开始和结束日期：
It lists all of the days of 07/01/2013 to 07/31/2013. Easily adaptable to any date range.
它列出了 07/01/2013 到 07/31/2013 的所有日子。轻松适应任何日期范围。
SELECT to_date('07/01/2013', 'mm/dd/yyyy') + LEVEL - 1 AS today
FROM dual
CONNECT BY LEVEL <= to_date('07/31/2013', 'mm/dd/yyyy') - to_date('07/01/2013', 'mm/dd/yyyy') + 1;
回答by Yasser Zilal
declare
v_curr_date date;
for i in to_number(to_char(p_date_from ,'j')) .. to_number(to_char(p_date_to
,'j')) loop
v_curr_date = to_date(to_char(i),'j');
--make any operation on v_curr_date (like insert into table)
end loop;
end;
回答by Steve Broberg
This type of query is best handled if you have a second "utility" table, which you can use for just about any query where you need to convert ranges into specific buckets. The utility table is nothing more than a list of numbers:
如果您有第二个“实用程序”表，则最好处理这种类型的查询，您可以将其用于几乎任何需要将范围转换为特定存储桶的查询。效用表只不过是一个数字列表：
CREATE TABLE Iterator (Counter NUMBER);
COUNTER
-------
0
1
2
3
...
100 (or however many rows you want to include)
IF we assume that you want to display 30 days, e.g.
如果我们假设您想显示 30 天，例如
SELECT TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter thedate
, i.My_option
, count(y.My_option)
FROM ( SELECT DISTINCT
i2.Counter
, y.My_option
FROM iterator i2
, YourTable y
WHERE i2.Counter < 5
) i
LEFT OUTER JOIN yourtable y
ON TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter
>= y.start_date
AND TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter
< y.end_date
AND y.My_option = i.My_option
GROUP BY TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter
, i.My_option
ORDER BY 1
, 2;
The idea is that you create a Cartesian product between your iterator table and your table with the range, then filter out all the cases where your range conditions aren't met. You can use this in many places, and is one of the best examples why it is better to model your data with ranges as opposed to discrete intervals - because you can always convert easily to discrete intervals using this technique.
这个想法是在迭代器表和具有范围的表之间创建笛卡尔积，然后过滤掉不满足范围条件的所有情况。您可以在许多地方使用它，这是最好的例子之一，为什么最好使用范围而不是离散间隔对数据进行建模 - 因为您始终可以使用此技术轻松转换为离散间隔。
edit: I really shouldn't use BETWEEN for date range queries - I changed it to >= <
编辑：我真的不应该将 BETWEEN 用于日期范围查询 - 我将其更改为 >= <
