从Oracle SQL上的周和年数字获取"start of the week"的日期

Question

我一直在使用一个按周对许多记录进行分组的查询。周数提取者为

to_char(reportdate, 'IW') as "Week",   

之后使用GROUP BY子句。"周"总是从星期一开始，如果 1 月 1 日不是星期一，第 1 周将从上一个星期一开始(或者我已经理解了这行代码(。

因此，如果获得如下表(对于三个示例行(

Week  Year  Sales  Visits
32   2017    22     55
33   2017    30     65
01   2019    55     103

我想添加一列，其中包含日期，以便它是一周的开始。我正在考虑使用分组查询作为子查询，然后简单地在后面添加一个列，但我无法确切地弄清楚如何将周和年编号映射到这样的日期。

我已经尝试了这一行来获取日期

trunc(next_day   (trunc(to_date(Yr,'yy'),'yy')  -1,'Mon') + (7*(Wk - 1))    -7)  

但每一行都不是这样。

任何为我指明正确方向的想法将不胜感激。

Answer 1

> ISO-8601 将一年的第一周计为该年大部分时间的第一周。要拥有该年的大部分时间，则必须包含该年内一周中的至少 4 天，并且必须包含 1 月 4 日。

您可以使用它来计算第一个 iso 周的开始时间，如下所示：

• TO_DATE( year, 'YYYY' )将给出1月1日;
• 然后，您可以增加3天的时间到达1月4日;
• 使用TRUNC( date_value, 'IW' )获取一年中第一个 iso 周的星期一
• 然后只需添加从第 1 周到第 N 周的正确周偏移量。

测试数据：

CREATE TABLE test_data ( Week, Year, Sales, Visits ) AS
SELECT '32', 2017, 22,  55 FROM DUAL UNION ALL
SELECT '33', 2017, 30,  65 FROM DUAL UNION ALL
SELECT '01', 2019, 55, 103 FROM DUAL;

查询：

SELECT t.*,
TRUNC( TO_DATE( year, 'YYYY' ) + 3, 'IW' ) + 7 * ( week - 1 ) AS week_start
FROM   test_data t

输出：

周 |年份 |销售 |参观 |WEEK_START :--- |---: |----: |-----: |:--------- 32 |2017 |   22 |    55 |07-八月-17 33 |2017 |   30 |    65 |14-八月-17 01 |2019 |   55 |   103 |31-DEC-18

db<>在这里摆弄

Answer 2

根据 ISO-8601，第一周是 1 月 4 日的一周。 我使用此函数从 ISO-Week 获取日期：

CREATE OR REPLACE FUNCTION ISOWeekDate(WEEK INTEGER, YEAR INTEGER) RETURN DATE DETERMINISTIC IS
res DATE;
BEGIN
IF WEEK > 53 OR WEEK < 1 THEN
RAISE VALUE_ERROR;      
END IF;
res := NEXT_DAY(TO_DATE( YEAR || '0104', 'YYYYMMDD' ) - 7, 'MONDAY') + ( WEEK - 1 ) * 7;
IF TO_CHAR(res, 'fmIYYY') = YEAR THEN
RETURN res;
ELSE
RAISE VALUE_ERROR;
END IF;
END ISOWeekDate;

请注意，没有年份的周数是不够的，因为 ISO 年份可能与实际年份不同。请参阅以下示例：

SELECT to_char(DATE '2019-12-31', 'IYYY-"W"IW') from dual;
TO_CHAR(DATE'2019-12-31','IYYY-"W"IW')
--------------------------------------
2020-W01                              

SELECT ISOWeekDate(1, 2019) from dual;
ISOWEEKDATE(1,2019)
----------------------------
2018-12-31 00:00:00         

当然，当您输入的周/年值可靠时，您可以简单地使用SELECT NEXT_DAY(TO_DATE( yr|| '0104', 'YYYYMMDD' ) - 7, 'MONDAY') + ( wk - 1 ) * 7 AS week_start