如何在SQL(SSMS)中将月度数据转换为周度数据?

huangapple go评论54阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年6月27日 21:44:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76565513.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定