英文:
How to convert monthly data to weekly data in SQL (SSMS)?
问题
以下是已翻译的内容:
我有一个表格,其中包含每月的数据,例如:
日期 | 值 |
---|---|
2023-02-01 | 1 |
2023-03-01 | 2 |
2023-04-03 | 3 |
如果月初的第一天落在周末,那么该月的第一个星期一被视为日期。这就是为什么四月份是3日而不是1日的原因。
我需要将这些每月的数据转换为每周的数据,方式如下:对于每个月的每个星期一,将插入一行新数据,如下所示:
日期 | 值 |
---|---|
2023-02-06 | 1 |
2023-02-13 | 1 |
2023-02-20 | 1 |
2023-02-27 | 1 |
2023-03-06 | 2 |
2023-03-13 | 2 |
2023-03-20 | 2 |
2023-03-27 | 2 |
2023-04-03 | 3 |
2023-04-10 | 3 |
2023-04-17 | 3 |
2023-04-24 | 3 |
我知道我应该使用某种递归函数来获得结果,但我不确定如何操作。任何帮助都将不胜感激。
英文:
I have a table in which I have monthly data for example:
Date | Value |
---|---|
2023-02-01 | 1 |
2023-03-01 | 2 |
2023-04-03 | 3 |
If the 1st of the month falls on the weekend, then the first Monday of the month is considered as date. This is the reason for which in April I have the 3rd instead of the 1st.
I need to convert this monthly data to a weekly data in the following way: for each Monday in each month a new line will be inserted such as:
Date | Value |
---|---|
2023-02-06 | 1 |
2023-02-13 | 1 |
2023-02-20 | 1 |
2023-02-27 | 1 |
2023-03-06 | 2 |
2023-03-13 | 2 |
2023-03-20 | 2 |
2023-03-27 | 2 |
2023-04-03 | 3 |
2023-04-10 | 3 |
2023-04-17 | 3 |
2023-04-24 | 3 |
I know I should use some type of recursive function to obtain the result but I am not sure how. Any help is much appreciated.
答案1
得分: 0
你可以生成一个类似日历表的小表格,放在一个通用表达式(Common Table Expression,CTE)中,列出日期和足够的信息,以便选择你想要的日期,然后从该CTE中进行选择。
在我的SQL Fiddle示例http://sqlfiddle.com/#!18/2ff909/12中,我使用sys.columns表来生成一个小的Tally表(仅包含31个连续的整数),然后将其添加到日期中。我计算它们的月份和星期几。然后,我根据你的星期一条件从CTE中进行选择,并确保新日期是同一个月的。如果你要一次处理超过1年的数据,你还需要包括年份值。
insert Dates (theDate, theValue)
values ('20230201',1)
, ('20230301',2)
, ('20230403',3);
with Tally as (
select top(31) row_number() over (order by column_id) as N
from sys.columns
), ExpandedDates as (
select Dates.theDate
, Dates.theValue
, datepart(month,Dates.theDate) as dateMonth
, dateadd(day,tally.N-1,Dates.theDate) as newDate
, datepart(month,dateadd(day,tally.N-1,Dates.theDate)) as newMonth
, datepart(weekday,dateadd(day,tally.N-1,Dates.theDate)) as dayOfWeek
from Dates
cross join Tally
)
select theDate
, newDate as Monday
, theValue
from ExpandedDates
where dayOfWeek = 2
and dateMonth = newMonth
order by theDate, newDate
英文:
What you can do is generate a small calendar-like table inside a Common Table Expression that lists out your dates and enough information to select just the ones you want, then select from that CTE.
In my SQL Fiddle example http://sqlfiddle.com/#!18/2ff909/12 I use the sys.columns table to generate a small Tally table (just 31 sequential integers) which I add to the dates. I calculate their month and day of week. Then I select from the CTE based on your Monday criteria, and making sure the new date is for the same month. If you were going to do more than 1 year at a time, you would need to include the year value as well.
insert Dates (theDate, theValue)
values ('20230201',1)
, ('20230301',2)
, ('20230403',3);
with Tally as (
select top(31) row_number() over (order by column_id) as N
from sys.columns
), ExpandedDates as (
select Dates.theDate
, Dates.theValue
, datepart(month,Dates.theDate) as dateMonth
, dateadd(day,tally.N-1,Dates.theDate) as newDate
, datepart(month,dateadd(day,tally.N-1,Dates.theDate)) as newMonth
, datepart(weekday,dateadd(day,tally.N-1,Dates.theDate)) as dayOfWeek
from Dates
cross join Tally
)
select theDate
, newDate as Monday
, theValue
from ExpandedDates
where dayOfWeek = 2
and dateMonth = newMonth
order by theDate, newDate
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论