如何在Postgress中获得给定月份的周范围 [英] How can i get a week range for a given month in Postgress
问题描述
这是我当前的实现方式
SELECT
date_trunc('month', do_date::date)::date as starting_of_the_month,
(date_trunc('month', do_date::date) + interval '1 month' - interval '1 day')::date as ending_of_the_month,
case when 1 + FLOOR((EXTRACT(DAY FROM do_date) - 1) / 7) = 1
THEN date_trunc('week', do_date)::date || ' - ' ||
(date_trunc('week', do_date) + '6 days') ::date end as week1,
case when 1 + FLOOR((EXTRACT(DAY FROM do_date) - 1) / 7) = 2
THEN date_trunc('week', do_date)::date || ' - ' ||
(date_trunc('week', do_date) + '6 days') ::date end as week2,
case when 1 + FLOOR((EXTRACT(DAY FROM do_date) - 1) / 7) = 3
THEN date_trunc('week', do_date)::date || ' - ' ||
(date_trunc('week', do_date) + '6 days') ::date end as week3,
case when 1 + FLOOR((EXTRACT(DAY FROM do_date) - 1) / 7) = 4
THEN date_trunc('week', do_date)::date || ' - ' ||
(date_trunc('week', do_date) + '6 days') ::date end as week4,
case when 1 + FLOOR((EXTRACT(DAY FROM do_date) - 1) / 7) = 5
THEN date_trunc('week', do_date)::date || ' - ' ||
(date_trunc('week', do_date) + '6 days') ::date end as week5
FROM sales_dos
WHERE date_trunc('month', do_date::date)::date >= '2021-02-01' AND date_trunc('month', do_date::date)::date < '2021-02-28'
这是我现在的输出:
我希望输出显示如下:
第1周:2021-02-01-2021-02-07
Week 1 : 2021-02-01 - 2021-02-07
第2周:2021-02-08-2021-02-14
Week 2 : 2021-02-08 - 2021-02-14
第3周:2021-02-15-2021-02-21
Week 3 : 2021-02-15 - 2021-02-21
第4周:2021-02-22-2021-02-28
Week 4 : 2021-02-22 - 2021-02-28
第5周:-
推荐答案
因此，您正在寻找的是带有标准Calendar的混合ISO.您使用的是ISO周的开始和结束时间，但是您可能会截断第一周和/或最后一周，而不是将所有周都精确地定为7天.
对此需求的更改实际上并不广泛.对于初始查询，返回的是ISO周开始日期，而不是该月的1号.然后，主查询将检查第1周，如果是，则产生该月的1号.唯一的问题是确定ISO周的开始日期.为此，我只包含了我专门为此使用了一段时间的功能.对week_days函数的更改标记为-<<<.
So what you are looking for is a hybrid ISO with standard Calendar. You are taking the ISO week starting and ending period, but instead of all weeks being exactly 7 days you potentially truncate the 1st and/or last weeks.
The change to need for this is not actually extensive. For initial query returns the in the ISO week begin date instead of the 1st of the month. Then the main query then checks for week 1 and if so produces the 1st of the month. The only twist is determining the ISO week begin date. For this I've just included a function I have had for some time specifically for that. The change to the week_days function are marked --<<<.
create or replace function iso_first_of_week(date_in date)
returns date
language sql
immutable strict
/*
Given a date return the 1st day of the week according to ISO-8601.
I.e. Return the Date if it is Monday otherwise return the preceding Monday
*/
AS $$
with wk_adj(l_days) as (values (array[0,1,2,3,4,5,6]))
select date_in - l_days[ extract (isodow from date_in)::integer ]
from wk_adj;
$$;
create or replace
function week_dates( do_date_in date)
returns table (week_num integer, first_date date, last_date date)
language sql
immutable strict
as $$
with recursive date_list(week_num,first_date,terminate_date) as
( select 1
, iso_first_of_week(do_date_in)::timestamp --<<<
, (date_trunc('month', do_date_in) + interval '1 month' - interval '1 day')::timestamp
union all
select week_num+1, (first_date+interval '7 day'), terminate_date
from date_list
where first_date+interval '6 day' < terminate_date::timestamp
)
select week_num
, case when week_num = 1 --<<<
then date_trunc('month', do_date_in)::date --<<<
else first_date::date --<<<
end --<<<
, case when (first_date+interval '6 day')::date > terminate_date
then terminate_date::date
else (first_date+interval '6 day')::date
end last_date
from date_list;
$$;
----------原始回复
---------- Original Reply
您可以使用递归查询CTE获取指定月份中每个星期的星期数和第一个日期.主查询计算结束日期，如有必要，缩短最后一个日期.然后将其包装到SQL函数中，以返回每周的星期数和日期范围.参见示例.
You can use a recursive query CTE to get the week number and first date for each week of the month specified. The main query calculates the ending date, shorting the last if necessary. Then wrap that into a SQL function to return the week number and date range for each week. See example.
create or replace
function week_dates( do_date_in date)
returns table (ween_num integer, first_date date, last_date date)
language sql
immutable strict
as $$
with recursive date_list(week_num,first_date,terminate_date) as
( select 1
, date_trunc('month', do_date_in)::timestamp
, (date_trunc('month', do_date_in) + interval '1 month' - interval '1 day')::timestamp
union all
select week_num+1, (first_date+interval '7 day'), terminate_date
from date_list
where first_date+interval '6 day' < terminate_date::timestamp
)
select week_num
, first_date::date
, case when (first_date+interval '6 day')::date > terminate_date
then terminate_date::date
else (first_date+interval '6 day')::date
end last_date
from date_list;
$$;
响应:如何将输出与week1，week2，week3，week4和week5放在一行中".这本质上是不满足您想要的初始输出.这种类型动作的术语是PIVOT，并且通常被理解.它源于将行方向转换为列方向.这不是很困难，但是很混乱.
恕我直言，这是属于表示层的内容，不适合SQL .毕竟，您出于展示目的而重新排列了数据结构.让数据库服务器使用其自然格式，使用表示层重新格式化.当更改显示或需要相同数据的另一个视图时，这允许重用查询而不是重写查询.
如果您确实需要这样做，则只需使用初始查询，或查看来自的答案@波西米亚人.但是，以下显示了仅使用SQL即可解决此问题的方法(假设已创建week_dates函数).
Response to: "How can i put the output in a single row with week1, week2, week3, week4 and week5". This is essentially the initial output that did not satisfy what you wanted. The term for this type action is PIVOT and is generally understood. It stems from transforming row orientation to column orientation. It is not overly difficult but it is messy.
IMHO this is something that belongs in the presentation layer and is not suitable for SQL. After all you are rearranging the data structure for presentation purposes. Let the database server use its natural format, use the presentation layer to reformat. This allows reuse of the queries instead of rewriting when the presentation is changed or another view of the same data is required.
If you actually want this then just use your initial query, or see the answer from
@Bohemian. However the below shows how this issue can be handled with just SQL (assuming the function week_dates was created).
select week1s
, case when week5e is null
then week4e
else week5e
end "end of month"
, week1s || ' - ' || week1e
, week2s || ' - ' || week2e
, week3s || ' - ' || week3e
, week4s || ' - ' || week4e
, week5s || ' - ' || week5e
from ( select max(case when (week_num=1) then first_date else NULL end) as week1s
, max(case when (week_num=1) then last_date else NULL end) as week1e
, max(case when (week_num=2) then first_date else NULL end) as week2s
, max(case when (week_num=2) then last_date else NULL end) as week2e
, max(case when (week_num=3) then first_date else NULL end) as week3s
, max(case when (week_num=3) then last_date else NULL end) as week3e
, max(case when (week_num=4) then first_date else NULL end) as week4s
, max(case when (week_num=4) then last_date else NULL end) as week4e
, max(case when (week_num=5) then first_date else NULL end) as week5s
, max(case when (week_num=5) then last_date else NULL end) as week5e
from week_dates(current_date)
) w ;
像以前一样，我将以上内容包装在SQL函数中并提供了示例此处.
As before I have wrapped the above in a SQL function and provide an example here.
这篇关于如何在Postgress中获得给定月份的周范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
