PostgreSQL 自定义周数 - 包含 2 月 1 日的第一周

Question

我是 SQL 函数的新手，正在尝试创建一个显示自定义周数的日历表，每周从周六开始到周五结束。每年的第一周总是包含当年的 2 月 1 日。例如，如果特定年份的 2 月 1 日是星期二，则该年的第一周是从 1 月 29 日到 2 月 4 日 .

我已经为这个问题苦苦挣扎了几天，我能想到的唯一解决方案如下:

首先，我创建了一个日历表，其中包含一个名为“CustomizedWeekNo”的列，以反射(reflect)从包含 2 月 1 日的那一周开始的年循环。但是每周的第一天是星期一。

Create Table Calendar
(CalendarDate Date, WeekNo smallInt, WeekDayNo text, CustomizedWeekNo smallInt)

Create or Replace Function CustomizeWeekNumber() 
Returns void
as $$
Declare beginDate Date :='2015-01-31'; endDate Date := '2017-01-27';
Begin 
While beginDate <= endDate loop
Insert Into Calendar (CalendarDate, WeekNo, WeekDayNo, CustomizedWeekNo)
Select
beginDate As CalendarDate    
,DATE_PART('week', beginDate::timestamp)::smallint As WeekNo
,(Case When DATE_PART('isodow', beginDate::timestamp)::smallint = 6
          Then 'Sat'
     When DATE_PART('isodow', beginDate::timestamp)::smallint = 7
          Then 'Sun'
     When DATE_PART('isodow', beginDate::timestamp)::smallint = 1
          Then 'Mon'
     When DATE_PART('isodow', beginDate::timestamp)::smallint = 2
          Then 'Tue'
     When DATE_PART('isodow', beginDate::timestamp)::smallint = 3
          Then 'Wed'
     When DATE_PART('isodow', beginDate::timestamp)::smallint = 4
          Then 'Thur'
     Else 'Fri'
     End) As WeekDayNo;
,(Case When beginDate < '2016-01-04' 
          Then DATE_PART('week', beginDate::timestamp)::smallint - 5 
     When beginDate >= '2016-01-04' and beginDate < '2016-01-30' 
          Then (date_part('week', '2016-01-03'::timestamp)::smallint - 5 + date_part('week', beginDate::timestamp)::smallint)
     When beginDate >= '2016-01-30' and beginDate < '2017-01-02'
          Then date_part('week', beginDate::timestamp)::smallint - 4
     Else
          date_part('week', '2017-01-01'::timestamp)::smallint - 4 + date_part('week', beginDate::timestamp)::smallint
     End) As CustomizedWeekNo; 
Select (beginDate + interval'1 day') into beginDate;
End loop;
End; $$
language plpgsql;  

# Run the function
select CustomizeWeekNumber()

接下来，我更新“CustomizedWeekNo”列

-- My customized week starts from every Saturday and ends on every Friday
 update calendar
 set CustomizedWeekNo = CustomizedWeekNo + 1 
 where WeekDayNo in ('Sat', 'Sun');

最后，我创建了另一个函数来返回我需要的信息。我还重新格式化了“CustomizedWeekNo”的值以包括特定年份。

create or replace function update_CustomizedWeek(date, date)
returns table(Calendar_Date Date, Week_No int, WeekDay_No text, Customized_Week_No int)
as $$
begin
  return query
  select t.CalendarDate, t.WeekNo, t.WeekDayNo, 
    case when t.CustomizedWeekNo <= 9 
         then (date_part('year', t.CalendarDate::timestamp)::text||'0'||t.CustomizedWeekNo::text)::int 
    else (date_part('year', t.CalendarDate::timestamp)::text||t.CustomizedWeekNo::text)::int 
    end
  from Calendar t 
  where t.CalendarDate >= $1 and t.CalendarDate <= $2
  order by t.CalendarDate;
end; $$
language plpgsql;

--Example
select * from update_CustomizedWeek('2015-01-30', '2015-02-10')

最终结果如下:

Calendar_Date | Week_No | WeekDay_No | Customized_Week_No
------------- | ------- | ---------- | -------------------
2015-01-31    |   5     |   Sat      |  201501
2015-02-01    |   5     |   Sun      |  201501
2015-02-02    |   6     |   Mon      |  201501
2015-02-03    |   6     |   Tue      |  201501
2015-02-04    |   6     |   Wed      |  201501
2015-02-05    |   6     |   Thur     |  201501
2015-02-06    |   6     |   Fri      |  201501
2015-02-07    |   6     |   Sat      |  201502
2015-02-08    |   6     |   Sun      |  201502
2015-02-09    |   7     |   Mon      |  201502
2015-02-10    |   7     |   Tue      |  201502

如您所见，我在这里使用了大量的“硬编码”。我希望能够生成日期范围以及任意年份的自定义周数，而不仅仅是 2016 年或 2017 年。非常感谢任何帮助。

Answer 1

date_trunc()截断到前一个星期一。您仍然可以通过将 2 天添加到输入(星期六和星期一之间的差异)，然后从输出中减去 2 天来使用它。完美运行。

此查询完全产生您想要的输出:

SELECT d::date                     AS "Calendar_Date"
     , EXTRACT('WEEK' FROM d)::int AS "Week_No"
     , to_char(d, 'Dy')            AS "WeekDay_No"
     , base_nr + (rn::int - 1) / 7 AS "Customized_Week_No"
FROM  (
   SELECT date_trunc('week', feb3) - interval '2 days' AS day1 -- subtract 2 days
        , EXTRACT('year' FROM feb3)::int * 100 + 1     AS base_nr
   FROM  (SELECT timestamp '2015-02-03') input(feb3)           -- add 2 days, so Feb 3 (!)
   ) t, generate_series (day1
                       , day1 + interval '1 year - 1 day' 
                       , interval '1 day') WITH ORDINALITY AS d(d, rn);

只需提供相应年份的 2 月 3 日:timestamp '2015-02-03'。