sql - 查询日期范围之间有多少周并向他们显示日期

Question

我需要计算有多少周，并将它们与各自的日期范围一起列在表格中。

所以我现在拥有的是

select countinous_weeks, decode(countinous_weeks-52,0,trunc(countinous_weeks),trunc(countinous_weeks)+1)
from (
select (TO_DATE('01-01-1995', 'DD/MM/YYYY') - TO_DATE('01-01-1994','DD/MM/YYYY'))/7 countinous_weeks
from dual) wks

它仅显示该范围内有多少周。我的目标是将它们显示在 53 行中并显示每周的日期范围。那么我们就说第一周吧

WEEK RANGE
1    01-01-1994 Until 07-01-1994 ... etc

请帮我解决这个问题..非常感谢

Answer 1

这很有趣。它涉及以下内容 -

1. 日期行生成器
2. 周数
3. ROW_NUMBER() 为每组周中的日期分配排名
4. 最后LISTAGG聚合从步骤 3 获取的行

让我们看看它的工作原理 -

SQL> WITH DATA AS
  2    (SELECT to_date('01/01/1994', 'DD/MM/YYYY') date1,
  3      to_date('31/12/1994', 'DD/MM/YYYY') date2
  4    FROM dual
  5    )
  6  SELECT the_week,
  7    listagg(the_date, ' until ') within GROUP (
  8  ORDER BY to_date(the_date, 'DD/MM/YYYY')) the_date_range
  9  FROM
 10    (SELECT the_week,
 11      the_date,
 12      row_number() over(partition BY the_week order by the_week, to_date(the_date, 'DD/MM/YYYY')) rn
 13    FROM
 14      (SELECT TO_CHAR(date1+level-1, 'WW') the_week ,
 15        TO_CHAR(date1      +level-1, 'DD/MM/YYYY') the_date
 16      FROM data
 17        CONNECT BY LEVEL <= date2-date1+1
 18      )
 19    )
 20  WHERE rn in( 1, 7)
 21  GROUP BY the_week
 22  /

TH THE_DATE_RANGE
-- ---------------------------------------------
01 01/01/1994 until 07/01/1994
02 08/01/1994 until 14/01/1994
03 15/01/1994 until 21/01/1994
04 22/01/1994 until 28/01/1994
05 29/01/1994 until 04/02/1994
06 05/02/1994 until 11/02/1994
07 12/02/1994 until 18/02/1994
08 19/02/1994 until 25/02/1994
09 26/02/1994 until 04/03/1994
10 05/03/1994 until 11/03/1994
11 12/03/1994 until 18/03/1994
12 19/03/1994 until 25/03/1994
13 26/03/1994 until 01/04/1994
14 02/04/1994 until 08/04/1994
15 09/04/1994 until 15/04/1994
16 16/04/1994 until 22/04/1994
17 23/04/1994 until 29/04/1994
18 30/04/1994 until 06/05/1994
19 07/05/1994 until 13/05/1994
20 14/05/1994 until 20/05/1994
21 21/05/1994 until 27/05/1994
22 28/05/1994 until 03/06/1994
23 04/06/1994 until 10/06/1994
24 11/06/1994 until 17/06/1994
25 18/06/1994 until 24/06/1994
26 25/06/1994 until 01/07/1994
27 02/07/1994 until 08/07/1994
28 09/07/1994 until 15/07/1994
29 16/07/1994 until 22/07/1994
30 23/07/1994 until 29/07/1994
31 30/07/1994 until 05/08/1994
32 06/08/1994 until 12/08/1994
33 13/08/1994 until 19/08/1994
34 20/08/1994 until 26/08/1994
35 27/08/1994 until 02/09/1994
36 03/09/1994 until 09/09/1994
37 10/09/1994 until 16/09/1994
38 17/09/1994 until 23/09/1994
39 24/09/1994 until 30/09/1994
40 01/10/1994 until 07/10/1994
41 08/10/1994 until 14/10/1994
42 15/10/1994 until 21/10/1994
43 22/10/1994 until 28/10/1994
44 29/10/1994 until 04/11/1994
45 05/11/1994 until 11/11/1994
46 12/11/1994 until 18/11/1994
47 19/11/1994 until 25/11/1994
48 26/11/1994 until 02/12/1994
49 03/12/1994 until 09/12/1994
50 10/12/1994 until 16/12/1994
51 17/12/1994 until 23/12/1994
52 24/12/1994 until 30/12/1994
53 31/12/1994

53 rows selected.

SQL>