PostgreSQL自定义周编号-包含2月1日的第一周 [英] PostgreSQL custom week number - first week containing Feb 1st

问题描述

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

I'm new to SQL functions and trying to create a calendar table that displays custom week numbers, with each week starting with Saturday and ending on Friday. The first week of each year always contains Feb.1st of that year. For example, if the day of the week of Feb. 1st for a particular year is Tuesday, then the first week for that year is from Jan. 29 to Feb. 4.

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

I've been struggling with this problem for a couple days and the only solution I can come up with is as follows:

首先，我创建了一个日历表，该表的列名为"CustomizedWeekNo"，以反映从2月1日开始的一周开始的年周期.但是每周的第一天是星期一.

First, I created a calendar table with a column called "CustomizedWeekNo" to reflect the year cycle starting from the week containing Feb. 1st. But the first day of each week is Monday.

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"列

Next, I update the "CustomizedWeekNo" column

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

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

Lastly, I create another function to return the information I need. I also reformat the value of the "CustomizedWeekNo" to include the specific year.

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)的自定义星期数.非常感谢您的帮助.

As you can see, I used a lot of "hard coding" here. I would like to be able to generate a date range along with the customized week number for any year, not just 2016 or 2017. Any help is really appreciated.

推荐答案

date_trunc() 会截断至上一个星期一.您仍然可以通过在输入中添加2天(周六和周一之间的差)，然后从输出中减去2天来使用它.完美运行.

date_trunc() truncates to the previous Monday. You can still use it by adding 2 days to the input (the difference between Sat and Mon), then subtract 2 days from the output. Works perfectly.

此查询精确地产生您想要的输出 :

This query produces your desired output exactly:

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'.

Just provide Feb 3 of the respective year: timestamp '2015-02-03'.

这篇关于PostgreSQL自定义周编号-包含2月1日的第一周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！