我是SQL函数的新手,试图创建一个显示自定义星期数的日历表,每个星期从星期六开始,在星期五结束。每年的第一周始终包含该年的2月1日。例如,如果特定年份的2月1日星期几是星期二,则该年的第一周是1月29日到2月4日。
我已经为这个问题苦苦挣扎了几天,我唯一能想到的解决方案如下:
首先,我创建了一个日历表,其中包含名为“ CustomizedWeekNo”的列,以反映从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)的自定义星期数。非常感谢您的帮助。
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'
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句