在Oracle SQL中计算手动周数

Question

我有一个日期列，同时我需要在oracle中计算另一列他们一年的周数，周应该是从周日到周六，从一年的第一天开始。

例如本年度

Week 1 : 1 Jan 2020 (Wednesday) - 4 Jan 2020(Saturday)

Week 2 : 5 Jan 2020 (Sunday)        - 11 Jan 2020(Saturday)
. . . . .

Week 5 : 26 Jan 2020 (Sunday) - 1 Feb 2020 (Saturday)

等等…

Answer 1

您需要使用层次结构查询编写自己的逻辑。

类似以下内容：

SQL> SELECT WEEKNUMBER,
2  WEEK_START,
3  CASE WHEN WEEKNUMBER = 1 THEN FIRST_WEEKEND ELSE WEEK_START + 6 END AS WEEK_END
4  FROM
5  (SELECT
6      CASE
7          WHEN LEVEL = 1 THEN FIRST_DAY
8          ELSE FIRST_WEEKEND + ( LEVEL - 2 ) * 7 + 1
9      END AS WEEK_START,
10      FIRST_WEEKEND,
11      LEVEL AS WEEKNUMBER
12  FROM
13      ( SELECT
14              TRUNC(SYSDATE, 'YEAR') FIRST_DAY,
15              NEXT_DAY(TRUNC(SYSDATE, 'YEAR'), 'SATURDAY') FIRST_WEEKEND
16          FROM DUAL )
17  CONNECT BY
18      CASE WHEN LEVEL = 1 THEN FIRST_DAY
19          ELSE FIRST_WEEKEND + ( LEVEL - 2 ) * 7 + 1
20      END < ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12));
WEEKNUMBER WEEK_STAR WEEK_END
---------- --------- ---------
1 01-JAN-20 04-JAN-20
2 05-JAN-20 11-JAN-20
3 12-JAN-20 18-JAN-20
4 19-JAN-20 25-JAN-20
5 26-JAN-20 01-FEB-20
6 02-FEB-20 08-FEB-20
7 09-FEB-20 15-FEB-20
8 16-FEB-20 22-FEB-20
9 23-FEB-20 29-FEB-20
10 01-MAR-20 07-MAR-20
11 08-MAR-20 14-MAR-20
.......
.......
53 27-DEC-20 02-JAN-21

干杯！！

Answer 2

另一种选择是

with t as
(
select trunc(sysdate,'yyyy')+level-1 as day, to_char(trunc(sysdate,'yyyy')+level-1,'DY','NLS_DATE_LANGUAGE=AMERICAN') as weekday, level - 1 as lvl
from dual
connect by level <= 366
), t1 as
(
select case
when lvl = 0 then
day
else
case
when weekday = 'SUN' then
day
end
end as day1,       
lvl
from t
), t2 as
(
select case
when weekday = 'SAT' then
day
end as day2,
lvl
from t
)
select concat( 'Week ', wk1) as week, day1, day2  
from 
(
select row_number() over (order by lvl) wk1, day1
from t1 where day1 is not null ) t1 
left join    
(
select row_number() over (order by lvl) wk2, day2
from t2 where day2 is not null ) t2  
on wk2 = wk1  

使用CCD_ 1语法和CCD_。

演示