按照不同的月周期统计数据 - sql

      现在有一个需求:每月26号到25号为一个企业月(即:2019年2月对应的起止日期是: 2019-01-26, 2019-02-25),一个产品分优等品、一等品、合格品、不合格;把一个企业月之内的每种等级的产品的产量的总和求出来。

      前台传来开始时间的年份月份,结束时间的年份月份,格式为:yyyy-MM。首先得到开始时间的26号,结束时间的25号。

      本身我的想法是:求每个月的四种等级产品的总和。

      即: 开始时间为2019-01,结束时间为2019-12,查12次。

      后来发现sql里有些函数可以帮忙处理,一次搞定。sql如下:

    select to_char(add_months(produce_date - 26,1)  ,  'yyyy-mm' ) || grade  date_grade,
        sum(yield) yield 
        from t_produce_info  
        WHERE produce_name = #{produceName} 
        and produce_date >= #{startDate,jdbcType=TIMESTAMP}
        and produce_date <= #{endDate,jdbcType=TIMESTAMP}
        group by to_char(add_months(produce_date - 26,1)  , 'yyyy-mm' ) || grade;

      解释一下,首先,produce_date是生产日期,Date减去一个数字意思是减去几日。

      生产日期 减 26,可以理解成把所有日期推前26天,只要是该企业月的日期,都会变成1日至31日之间。

      举个例子,2019-1-30按照企业月是2月,但-26以后是2019-01-04;2019-2-24按照企业月是2月,但-26以后是2019-01-30。

 

      ADD_MONTHS是运算函数,函数将一个日期上加上一指定的月份数,所以,日期中的日是不变的。经过 add_months(produce_date-26,1) 后, 日期就变成了其企业月对应的月中的日期。使用to_char,则忽略掉日期,只保留你年月,再拼接上等级,使用group by分类,则可以求企业月每种产品等级的统计数据。

      统计结果:

 

      唯一的缺憾是:若当月没有生产该种等级产品,则查询不到。若有人能解决,求联系,微信:1907971839

 

 

 

      PS:甲方又改需求了。。。 要求1月是1月1日到1月25日, 6月是5月26日到6月31日,7月是7月1日到7月25日,12月是11月26日到12月31日。 

      我还没想出来一条sql搞定的办法。。。若有人能解决,求联系,微信:1907971839

<think>我们参考了用户提供的引用内容,特别是引用[3]中按周统计的方法,以及引用[1]和引用[2]中关于期处理的技巧。用户需求是“按每月每周统计”,即需要同时考虑份和周次。由于周可能跨,我们需要注意如何处理跨周的问题。考虑两种方案:方案1:将周划分到其开始期所在的份(即按每周的起始所在的份归类)。方案2:将一周的数据完全归属到该周结束期所在的份(或开始期所在份),但这样可能导致跨周的数据归属有争议。通常,业务上更倾向于按周起始(如周一)所在的份作为该周所属份。但用户的需求可能不同,因此我们提供两种方法供选择:方法A:按周起始(周一)所在的份分组方法B:按自然周(7天)的期范围来分组,并显示该周跨的情况(但这样统计结果将不再按分组,而是按周分组,可能不符合需求)鉴于用户要求“按每月每周”,我们理解为先按份分组,再按该包含的周分组。这样就会产生一个问题:跨周的数据如何归属?比较合理的做法是:将每周的数据归属到该周的每一天所在的份。也就是说,同一周的数据可能出现在两个(甚至两个以上)的分组中。但这样统计会导致一周的数据被拆分到不同份,不符合周统计的完整性。另一种做法:将整周归属到该周中占多数的份(即4天以上的部分)。但实现复杂。考虑到实现的简便性和业务常用性,我们假设用户希望按周起始(周一)所在的份来归属该周的所有数据。这样,跨周的数据将归属于起始所在的份。步骤:1.计算每周的起始(周一)的期。2.从该期中提取年份和份,作为分组的依据(年、)。3.同时,我们还需要一个周标识(可以是第几周,也可以是起始)。具体实现:首先,我们需要一个函数来将期调整到当周的周一。由于不同地区对一周起始的定义不同(中国习惯周一为起始,美国习惯周),我们按中国习惯(周一为一周的第一天)处理。计算周一的公式(假设一周第一天为周一):DATEADD(DAY,1-DATEPART(WEEKDAY,OrderDate),OrderDate)--当DATEFIRST设置为7(星期为第一天)时,WEEKDAY返回值:星期=1,星期一=2,...,星期六=7但注意:WEEKDAY的设置会影响计算。我们可以先设置SETDATEFIRST7,保证计算正确。但为了不依赖全局设置,我们可以使用一个技巧:使用@@DATEFIRST获取当前设置,然后调整计算。或者,使用ISO_WEEK来处理(ISO标准周一是第一天),但是ISO周没有直接提供周一期。另一种可靠方法:不管SETDATEFIRST设置,计算周一:SELECTDATEADD(DAY,(DATEDIFF(DAY,'19000101',OrderDate)/7)*7,'19000101')这种方法不实用。更通用的方法:使用以下表达式计算周一(将期调整到周一):SELECTDATEADD(DAY,-(DATEPART(WEEKDAY,OrderDate)+@@DATEFIRST-2)%7,OrderDate)这种方法较复杂。我们简化:假设我们设置一周的第一天为周一(即SETDATEFIRST1),那么:周一:1,周二:2,...,周:7则周一=DATEADD(DAY,1-DATEPART(WEEKDAY,OrderDate),OrderDate)但为了避免修改会话设置,我们可以在计算时临时设置:...SETDATEFIRST1;--设置一周的第一天为周一然后计算:WeekStart=DATEADD(DAY,1-DATEPART(WEEKDAY,OrderDate),OrderDate)但是,这样会影响整个会话,可能对其他操作有影响。我们可以通过子查询避免设置,使用表达式调整:WeekStart=DATEADD(DAY,(1-(DATEPART(WEEKDAY,OrderDate)+@@DATEFIRST-1)%7-1)%7,OrderDate)这个表达式太复杂。更简单:使用已知的基准期。例如,已知一个周一的期,然后计算期差整除7天。实际上,我们可以这样:计算从基准周一(比如'1900-01-01',正好是周一)到当前期的天数差,然后求余数,再减去余数得到最近的周一。WeekStart=DATEADD(DAY,0-(DATEDIFF(DAY,'19000101',OrderDate)%7),OrderDate)但是,这个计算是相对于'19000101'的,如果'19000101'不是周一呢?实际上,1900-01-01是星期一(通过查询可知)。因此,我们可以这样计算周一:WeekStart=DATEADD(DAY,-(DATEDIFF(DAY,'19000101',OrderDate)%7),OrderDate)然后,根据WeekStart来提取份(这样WeekStart就是周一,所以该周的每一天都使用这个WeekStart来代表,而WeekStart所在的份就是该周归属的份)。步骤:1.计算每一行的WeekStart(该周周一的期)。2.按WeekStart分组(即按周分组)。3.同时,我们从WeekStart中获取年和,用于按年、分组(因为按每月每周,所以年、、周都要分组)。但是,注意:WeekStart可能属于上个(如果该周跨),但按照我们的归属规则,整周属于WeekStart所在的份。最后,按年和以及WeekStart分组,然后聚合数据。查询模板:假设表为Sales,期列为SaleDate,数值列为Amount。步骤:(1)计算每周的开始期(周一)WeekStart(2)按WeekStart分组,同时提取WeekStart的年份和份(3)在结果中,可以展示年、、周起始,以及该周的聚合值注意:我们按WeekStart分组就已经将同周的数据分在一起,但为了按年展示,我们额外需要按份分组。然而,由于WeekStart已经确定了份,所以我们可以直接按份分组(实际上,同一个WeekStart不可能跨越两个,所以按WeekStart分组和按WeekStart所在份分组并不冲突,但我们要按用户要求“每月每周”,所以我们需要在分组时同时使用年份、份和WeekStart(或者周序号))。问题:同一份的不同周,周起始不同,所以按WeekStart分组即可。但用户要求按“”和“周”统计,所以我们可以这样:GROUPBYYEAR(WeekStart),MONTH(WeekStart),WeekStart或者,为了显示周序号,我们可以使用该中的第几周(注意:这可能是该的第1周,但可能包含上个的天数)。由于我们按WeekStart分组,同一个WeekStart只会出现在一个组中。如果我们想按ISO周的年和?这比较复杂。我们提供以下示例:方法:先计算周一的期,然后按照年(Year)、(Month)、周一的期(WeekStart)分组。注意:如果用户希望按照周在内的顺序(比如某的第一周、第二周)来显示,我们也可以计算该第几周:内第几周=DATEDIFF(week,DATEADD(MONTH,DATEDIFF(MONTH,0,WeekStart),0),WeekStart)+1但是注意:每月的第一天不一定在同一个周。这个计算可能不准确(因为每月的第一周可能少于7天)。因此,我们建议在结果中直接显示周起始期(WeekStart)和周结束期(WeekStart+6)来表示该周,同时显示该周所在的年、。示例SQL:假设我们设置周一为一周的第一天:```sql--不改变会话设置,采用已知基准(1900-01-01是周一)来计算周一SELECTYEAR(WeekStart)AS[Year],MONTH(WeekStart)AS[Month],WeekStart,--这周的周一期DATEADD(DAY,6,WeekStart)ASWeekEnd,--这周的周期SUM(Amount)ASWeeklyAmountFROM(SELECTDATEADD(DAY,-(DATEDIFF(DAY,'19000101',SaleDate)%7),SaleDate)ASWeekStart,AmountFROMSales)ASSourceGROUPBYYEAR(WeekStart),MONTH(WeekStart),WeekStartORDERBYWeekStart;```但是,这个计算方法是否正确?我们验证:例如,2023年11是星期。计算2023-01-01的WeekStart:先计算和1900-01-01的天数差:4498744987%7=44987-7*6426=5(因为44987/7=6426余5)?实际上,2023-01-01减去5天:2022-12-27(周二),显然不对。为什么会出错?因为1900-01-01(周一)到2023-01-01(周)的天数:44987天。44987除以7的余数是0吗?因为整周结束在周,所以从周一到周是7天,所以每个周到1900-01-01的天数应该是7的倍数?实际上,1900-01-01是周一,那么下一个周是1900-01-07,相差6天。因此,余数如果是0,那么就是1900-01-01?不对。让我们重新考虑:1900-01-01(周一),2023-01-01(周),相差44987天,除以7:44987÷7=6426余5(因为7×6426=44982,44982+5=44987)。所以我们要减去5天?那么2023-01-01减去5天是2022-12-27(周二),但我们期望的周起始应该是前一个周一(即2022-12-26,周一)。所以上述计算方法有误。正确方法:我们要找到前一个周一的期。由于1900-01-01是周一,任意期与它的天数差除以7的余数n,表示该期距离上一个周一的偏移量?实际上,如果余数为0,那么该期就是周一;余数为1,就是周二,...,余数为6,就是周。因此,我们减去余数天,就得到该周的周一。但是,2023-01-01(星期):余数是5(因为44987%7=5)?那么减去5天:2022-12-27(周二)?但是2023-01-01应该是星期,应该减去6天得到周一(2022-12-26)才对。问题出在哪里?因为余数从0到6,分别对应周一到周。因为:1900-01-01(周一)是基准,所以1900-01-01+0天:周一1900-01-01+1天:周二...1900-01-01+5天:周六1900-01-01+6天:周所以,余数0:周一;余数1:周二;...余数5:周六;余数6:周。那么,对于2023-01-01(星期),应该是余数6,但实际上我们计算余数5?因为从1900-01-01到2023-01-01的天数差是44987天,除以7余数确实是5(不是6)?为什么呢?因为1900-01-01到2023-01-01的实际天数差需要确认。实际上,我们可以用DATEDIFF(DAY,'19000101','20230101')来验证:在SQLServer中运行得到44987,然后44987%7=5。因此,我们的对应关系错了?实际上,1900-01-01加上5天(44987%7=5)得到1900-01-06(周六),但是2023-01-01是星期,所以我们的对应关系应该是:余数0对应1900-01-01(周一),所以2023-01-01应该是星期,所以余数应该为6?这里矛盾了。原因是:从1900-01-01(周一)到2023-01-01(周)实际上应该是6mod7?而44987mod7=5,说明我们的基准期1900-01-01在SQLServer中的期计算可能有所不同。实际上,1900-01-01在SQLServer中是一个特殊期(因为1900年是闰年?但实际1900不是闰年)。为了避免基准期带来的问题,我们采用更安全的方法:利用DATEPART(WEEKDAY,...)并结合@@DATEFIRST计算当前期是一周中的第几天(然后推算出本周一的期)。标准方法(推荐):SETDATEFIRST1;--设置周一为每周第一天,然后WEEKDAY:1为周一,2为周二,...,7为周然后:周一=DATEADD(DAY,1-DATEPART(WEEKDAY,SaleDate),SaleDate)为了保证在不改变当前会话设置的情况下使用,我们可以动态调整:SELECTWeekStart=DATEADD(DAY,(8-(DATEPART(WEEKDAY,SaleDate)+@@DATEFIRST-1)%7-1)%7,SaleDate)这个表达式比较复杂。另一种方法:使用临时设置,但在同一批处理中不影响后续执行(如果只有这个查询的话,因为批处理结束设置会恢复?不会,SET是会话级别的,会影响之后的查询)。为了安全,我们可以在查询中动态计算:SELECTWeekStart=DATEADD(DAY,CASEWHENDATEPART(WEEKDAY,SaleDate)+@@DATEFIRST-2>=0THEN1-(DATEPART(WEEKDAY,SaleDate)+@@DATEFIRST-1)ELSE1-(DATEPART(WEEKDAY,SaleDate)+@@DATEFIRST-1)+7END,SaleDate)这个表达式更复杂。考虑到复杂度和性能,我们选择简单方法:在查询前先设置DATEFIRST,然后查询后再恢复。但如果多个用户同时使用会有问题吗?不会,因为SETDATEFIRST只在当前会话有效。因此,我们可以这样写:```sql--保存当前设置DECLARE@OriginalDateFirstINT;SET@OriginalDateFirst=@@DATEFIRST;SETDATEFIRST1;--设置周一为每周第一天SELECTYEAR(WeekStart)AS[Year],MONTH(WeekStart)AS[Month],WeekStart,DATEADD(DAY,6,WeekStart)ASWeekEnd,SUM(Amount)ASWeeklyAmountFROM(SELECTDATEADD(DAY,1-DATEPART(WEEKDAY,SaleDate),SaleDate)ASWeekStart,AmountFROMSales)ASSourceGROUPBYYEAR(WeekStart),MONTH(WeekStart),WeekStartORDERBYWeekStart;--恢复原来的设置SETDATEFIRST@OriginalDateFirst;```如果我们不想设置DATEFIRST,还有一个方案:使用ISO_WEEK,但ISO周没有直接提供周一期,但我们可以用以下方法(ISO周总是从周一开始):SELECTWeekStart=DATEADD(DAY,1-DATEPART(ISO_WEEKDAY,SaleDate),SaleDate)注意:ISO_WEEKDAY的值:1(周一)到7(周)。所以:周一:1->加上1-1=0天->同一天周:7->加上1-7=-6天->往前推6天(周一)所以这个计算是正确的。所以更推荐用ISO_WEEKDAY(不受SETDATEFIRST影响):```sqlSELECTYEAR(WeekStart)AS[Year],MONTH(WeekStart)AS[Month],WeekStart,DATEADD(DAY,6,WeekStart)ASWeekEnd,SUM(Amount)ASWeeklyAmountFROM(SELECTDATEADD(DAY,1-DATEPART(ISO_WEEKDAY,SaleDate),SaleDate)ASWeekStart,AmountFROMSales)ASSourceGROUPBYYEAR(WeekStart),MONTH(WeekStart),WeekStartORDERBYWeekStart;```这种方法不会改变DATEFIRST设置,更可靠。因此,最终采用这种基于ISO_WEEKDAY的计算。但是注意:WeekStart所在的份就是我们归属的份,同一周的数据(即使部分期在下个)也会被归到这个份。如果用户要求:将一周的数据按天所在的份拆分,那么就需要更复杂的处理(按天分组然后合并到所属份,但是这样周就不完整了)。所以,我们按照整周归属到周起始所在的份,这是常见的业务处理方式。因此,我们将按周一的期所在的年、分组。另外,用户可能需要显示份名称、周在中的序号等。我们可以扩展查询:中的第几周:我们可以计算该周一的期是该的第几周(按该中包含的完整的周数来划分第一周、第二周,但第一周可能是从该1号开始,但是1号不一定在周一开始,所以通常第一周可能从1号或上个期开始)。所以我们无法直接得到按的周序号(因为周的划分是连续的)。我们可以这样计算中的周序号:(DATEPART(DAY,WeekStart)-1)/7+1但是这样,1号到7号都在第1周,8号到14号在第2周,...,但这样划分和实际周的划分不一致(因为1号可能是周五,那么这一周只有2天在该)。所以这个序号并不对应实际业务周。因此,我们建议直接显示周起始到结束,或者使用周起始作为标识,而避免使用内第几周。综合以上,我们提供如下查询:```sql--每月每周汇总(整周归属该周起始所在份)SELECTYEAR(WeekStart)AS[Year],MONTH(WeekStart)AS[Month],WeekStart,WeekEnd=DATEADD(DAY,6,WeekStart),WeeklyAmount=SUM(Amount)FROM(SELECTWeekStart=DATEADD(DAY,1-DATEPART(ISO_WEEKDAY,SaleDate),SaleDate),AmountFROMSales--可以添加WHERE条件限制期范围)ASSourceGROUPBYYEAR(WeekStart),MONTH(WeekStart),WeekStartORDERBYWeekStart;```如果需要显示份名称,可以使用DATENAME(MONTH,WeekStart)。如果用户一定要得到内第几周(尽管不精确),可以添加:该第几周=DATEPART(WEEK,WeekStart)-DATEPART(WEEK,DATEADD(DAY,1,DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,WeekStart),0)))+1但这个计算非常复杂且容易错,不推荐。因此,我们推荐使用上述查询。注意:如果数据量很大,可以在SaleDate上创建索引,并考虑持久化计算列(如WeekStart)来提高性能。最后,我们给出一个完整的示例(假设Sales表):扩展:显示份名称和年标签。示例结果格式:年名称周开始期周结束期周销售额20231January2023-01-022023-01-08100020231January2023-01-092023-01-151500...查询:```sqlSELECTYEAR(WeekStart)AS[Year],MONTH(WeekStart)AS[Month],DATENAME(MONTH,WeekStart)AS[MonthName],WeekStart,DATEADD(DAY,6,WeekStart)ASWeekEnd,SUM(Amount)ASWeeklyAmountFROM(SELECTDATEADD(DAY,1-DATEPART(ISO_WEEKDAY,SaleDate),SaleDate)ASWeekStart,AmountFROMSales)ASSourceGROUPBYYEAR(WeekStart),MONTH(WeekStart),DATENAME(MONTH,WeekStart),WeekStartORDERBYWeekStart;```注意:DATENAME分组是必要的吗?其实只要年、、WeekStart分组,MonthName可以从WeekStart推导,所以也可以不在GROUPBY中包含MonthName(因为MonthName和Month+Year是等价的),但为了确保分组正确,我们加上去(其实GROUPBY中WeekStart确定了年、份名,所以可以不加?但是严格来说,为了遵守SQL标准,SELECT中非聚合列必须出现在GROUPBY中)。因此,我们将其包含在GROUPBY中。另外,如果SaleDate有索引,且数据量大,可以将子查询内的计算列持久化(建立计算列并存储)或者使用索引视图。这就是按每月每周统计的方法。</think> 在 SQL Server 中按**每月每周**统计数据,需要结合份和周次进行分组。以下是三种推荐方法(假设表名为 `Sales`,期列为 `SaleDate`,统计列为 `Amount`): --- ### 方法1:基于ISO周标准(推荐,自动处理跨周) ```sql SELECT DATEPART(YEAR, WeekStart) AS [Year], DATEPART(MONTH, WeekStart) AS [Month], CONCAT('第', DATEPART(ISO_WEEK, SaleDate), '周') AS ISO_Week, -- ISO周序号 WeekStart, DATEADD(DAY, 6, WeekStart) AS WeekEnd, SUM(Amount) AS WeeklyAmount FROM ( SELECT DATEADD(DAY, 1 - DATEPART(ISO_WEEKDAY, SaleDate), SaleDate) AS WeekStart, Amount FROM Sales ) AS SubQuery GROUP BY DATEPART(YEAR, WeekStart), DATEPART(MONTH, WeekStart), DATEPART(ISO_WEEK, SaleDate), WeekStart ORDER BY WeekStart; ``` - **优点** ✅ 使用 `ISO_WEEKDAY` 确保周从周一开始 ✅ `ISO_WEEK` 自动处理跨周归属问题 ✅ 显示每周期范围(周一 ~ 周--- ### 方法2:按内周序号分组 ```sql SELECT YEAR(SaleDate) AS [Year], MONTH(SaleDate) AS [Month], (DATEPART(DAY, SaleDate) - 1) / 7 + 1 AS WeekInMonth, -- 计算内第几周 MIN(SaleDate) AS WeekStartDate, MAX(SaleDate) AS WeekEndDate, SUM(Amount) AS WeeklyAmount FROM Sales GROUP BY YEAR(SaleDate), MONTH(SaleDate), (DATEPART(DAY, SaleDate) - 1) / 7 + 1 ORDER BY Year, Month, WeekInMonth; ``` - **逻辑说明** `(DATEPART(DAY, SaleDate) - 1) / 7 + 1` 计算规则: 1~7 → 第1周 8~14 → 第2周 15~21 → 第3周 22~28 → 第4周 29~31 → 第5周(如有) --- ### 方法3:带份标签的周统计 ```sql SELECT FORMAT(WeekStart, 'yyyy-MM') AS YearMonth, --标签 CONCAT('第', DENSE_RANK() OVER ( PARTITION BY YEAR(WeekStart), MONTH(WeekStart) ORDER BY WeekStart), '周') AS MonthWeek, -- 内周序号 WeekStart, WeekEnd, SUM(Amount) AS WeeklyAmount FROM ( SELECT DATEADD(DAY, 1 - DATEPART(WEEKDAY, SaleDate), SaleDate) AS WeekStart, DATEADD(DAY, 6, DATEADD(DAY, 1 - DATEPART(WEEKDAY, SaleDate), SaleDate)) AS WeekEnd, Amount FROM Sales SET DATEFIRST 1 -- 确保周一作为周起始 ) AS SubQuery GROUP BY YearMonth, WeekStart, WeekEnd; ``` --- ### 关键说明 1. **跨周处理**: - 方法1(ISO周)自动将跨周归属到**该周多数期所在的份**[^1] - 方法2/3直接按期的物理份分组 2. **性能优化**: ```sql -- 添加期索引 CREATE INDEX IX_Sales_SaleDate ON Sales(SaleDate) INCLUDE (Amount); -- 使用计算列持久化周起始 ALTER TABLE Sales ADD WeekStart AS DATEADD(DAY, 1 - DATEPART(ISO_WEEKDAY, SaleDate), SaleDate) PERSISTED; ``` 3. **周起始配置**: - 通过 `SET DATEFIRST {1-7}` 控制起始(1=周一,7=周- 使用 `DATEPART(ISO_WEEKDAY)` 无需配置始终从周一开始 > **建议**:业务统计首选方法1(ISO周),财务周期统计可选方法2(按分割周) ---
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值