按天、周、月统计的问题

lvxiao715 2010-12-12 09:26:09
根据字段time统计chinese和rate的值，chinese累加值，rate取平均值
------------------------------
按天统计：执行结果为
2010-9-30 3 0.25
2010-10-1 7 0.25
2010-10-2 5 0.5
2010-10-3 6 0.6
2010-10-4 7 0.4
2010-10-5 8 0.3
...
------------------------------
按周统计（'2010-9-30'<=time<='2010-10-17'）：执行结果为
2010-10-4~2010-10-10 35 0.32
2010-10-11~2010-10-17 21 0.18
...
------------------------------
按月统计：执行结果为
2010-9 chinese累加 rate/30
2010-10 chinese累加 rate/31
...
------------------------------


create database testDB;

use testDB;

CREATE TABLE [dbo].[test](

	[id] [bigint] IDENTITY(1,1) NOT NULL,

	[time] [datetime] NOT NULL,

	[chinese] [int] NOT NULL,

	[rate] [decimal](18, 2) NOT NULL

) ON [PRIMARY]

insert into test(time, chinese,rate)

select '2010-9-30 11:00:00',1,0.2

union 

select '2010-9-30 15:00:00',2,0.3

union 

select '2010-10-1 12:00:00',3,0.4

union 

select '2010-10-1 16:00:00',4,0.1

union 

select '2010-10-2 11:00:00',5,0.5

union 

select '2010-10-3 11:00:00',6,0.6

union 

select '2010-10-4 11:00:00',7,0.4

union 

select '2010-10-5 11:00:00',8,0.3

union 

select '2010-10-6 11:00:00',9,0.7

union 

select '2010-10-7 11:00:00',1,0.3

union 

select '2010-10-8 11:00:00',2,0.3

union 

select '2010-10-9 11:00:00',3,0.2

union 

select '2010-10-10 10:00:00',5,0.1

union 

select '2010-10-11 9:00:00',7,0.3

union 

select '2010-10-12 9:00:00',1,0.1

union 

select '2010-10-13 9:00:00',2,0.1

union 

select '2010-10-14 9:00:00',3,0.2

union 

select '2010-10-15 9:00:00',5,0.3

union 

select '2010-10-16 9:00:00',1,0.2

union 

select '2010-10-17 9:00:00',2,0.1
...全文
294 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
renwenli07461 2010-12-14
  • 打赏
  • 举报
 回复 
--按周

SET DATEFIRST 1

select Convert(varchar(10),[time]-DATEPART(WEEKDAY,time)+1,120) as sdate

,convert(varchar(10),[time]-DATEPART(WEEKDAY,time)+7,120) as edate

,sum(chinese) as chinese

,avg(rate) as rate

from [test]

group by 

Convert(varchar(10),[time]-DATEPART(WEEKDAY,time)+1,120),convert(varchar(10),[time]-DATEPART(WEEKDAY,time)+7,120)

按照周一为周的第一天，就可以简化这样写
dawugui 2010-12-13
  • 打赏
  • 举报
 回复
经测试后发现不管你将周日或周一设置为一周的第一天,都会出现53周的情况.所以周的算法不准确.
SET DATEFIRST 7

select datepart(week,'2010-12-31')

/*

            

----------- 

53



（所影响的行数为 1 行）

*/



SET DATEFIRST 1

select datepart(week,'2010-12-31')

/*

            

----------- 

53



（所影响的行数为 1 行）

*/
dawugui 2010-12-13
  • 打赏
  • 举报
 回复 
CREATE TABLE [test](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [time] [datetime] NOT NULL,

    [chinese] [int] NOT NULL,

    [rate] [decimal](18, 2) NOT NULL

) ON [PRIMARY]

insert into test(time, chinese,rate)

select '2010-9-30 11:00:00',1,0.2

union 

select '2010-9-30 15:00:00',2,0.3

union 

select '2010-10-1 12:00:00',3,0.4

union 

select '2010-10-1 16:00:00',4,0.1

union 

select '2010-10-2 11:00:00',5,0.5

union 

select '2010-10-3 11:00:00',6,0.6

union 

select '2010-10-4 11:00:00',7,0.4

union 

select '2010-10-5 11:00:00',8,0.3

union 

select '2010-10-6 11:00:00',9,0.7

union 

select '2010-10-7 11:00:00',1,0.3

union 

select '2010-10-8 11:00:00',2,0.3

union 

select '2010-10-9 11:00:00',3,0.2

union 

select '2010-10-10 10:00:00',5,0.1

union 

select '2010-10-11 9:00:00',7,0.3

union 

select '2010-10-12 9:00:00',1,0.1

union 

select '2010-10-13 9:00:00',2,0.1

union 

select '2010-10-14 9:00:00',3,0.2

union 

select '2010-10-15 9:00:00',5,0.3

union 

select '2010-10-16 9:00:00',1,0.2

union 

select '2010-10-17 9:00:00',2,0.1

go



--按天

select convert(varchar(10),t.time,120) time, sum(chinese) chinese , avg(rate) rate from [test] t group by convert(varchar(10),t.time,120) order by time

/*

time       chinese     rate                                     

---------- ----------- ---------------------------------------- 

2010-09-30 3           .250000

2010-10-01 7           .250000

2010-10-02 5           .500000

2010-10-03 6           .600000

2010-10-04 7           .400000

2010-10-05 8           .300000

2010-10-06 9           .700000

2010-10-07 1           .300000

2010-10-08 2           .300000

2010-10-09 3           .200000

2010-10-10 5           .100000

2010-10-11 7           .300000

2010-10-12 1           .100000

2010-10-13 2           .100000

2010-10-14 3           .200000

2010-10-15 5           .300000

2010-10-16 1           .200000

2010-10-17 2           .100000



（所影响的行数为 18 行）

*/



--按月

select time,chinese, rate/datediff(day,time+'-01' , dateadd(mm,1,time+'-01') -1) rate from

(

select convert(varchar(7),t.time,120) time, 

       sum(chinese) chinese,

       sum(rate) rate

from (select convert(varchar(10),t.time,120) time, sum(chinese) chinese , avg(rate) rate from [test] t group by convert(varchar(10),t.time,120)) t

group by convert(varchar(7),t.time,120)

) m

/*

time    chinese     rate                                     

------- ----------- ---------------------------------------- 

2010-09 3           .008620

2010-10 74          .165000



（所影响的行数为 2 行）

*/



--按周(这个统计有争议,2010-9-30~2010-10-3的数据为什么要算在2010-10-04-2010-10-11这个统计段中?)

select 

convert(varchar(10),case datepart(weekday , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime))) 

       when 0 then dateadd(day , 1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 1 then dateadd(dd ,datepart(week,time)*7 , cast('2010-01-01' as datetime))

       when 2 then dateadd(day , -1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 3 then dateadd(day , -2 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 4 then dateadd(day , -3 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 5 then dateadd(day , -4 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 6 then dateadd(day , -5 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

end + 1 ,120) + '~' +

convert(varchar(10),case datepart(weekday , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime))) 

       when 0 then dateadd(day , 1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 1 then dateadd(dd ,datepart(week,time)*7 , cast('2010-01-01' as datetime))

       when 2 then dateadd(day , -1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 3 then dateadd(day , -2 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 4 then dateadd(day , -3 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 5 then dateadd(day , -4 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 6 then dateadd(day , -5 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

end + 1 + 7,120) time,sum(chinese) chinese, sum(rate) / 7 rate

from (select convert(varchar(10),t.time,120) time, sum(chinese) chinese , avg(rate) rate from [test] t group by convert(varchar(10),t.time,120)) m

group by 

convert(varchar(10),case datepart(weekday , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime))) 

       when 0 then dateadd(day , 1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 1 then dateadd(dd ,datepart(week,time)*7 , cast('2010-01-01' as datetime))

       when 2 then dateadd(day , -1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 3 then dateadd(day , -2 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 4 then dateadd(day , -3 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 5 then dateadd(day , -4 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 6 then dateadd(day , -5 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

end + 1 ,120) + '~' + 

convert(varchar(10),case datepart(weekday , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime))) 

       when 0 then dateadd(day , 1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 1 then dateadd(dd ,datepart(week,time)*7 , cast('2010-01-01' as datetime))

       when 2 then dateadd(day , -1 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 3 then dateadd(day , -2 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 4 then dateadd(day , -3 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 5 then dateadd(day , -4 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

       when 6 then dateadd(day , -5 , dateadd(dd , datepart(week,time)*7 , cast('2010-01-01' as datetime)))

end + 1 + 7,120)

/*

time                  chinese     rate                                     

--------------------- ----------- ---------------------------------------- 

2010-10-04-2010-10-11 15          .142857

2010-10-11-2010-10-18 36          .400000

2010-10-18-2010-10-25 24          .185714

2010-10-25-2010-11-01 2           .014285



（所影响的行数为 4 行）

*/



drop table [test]
-晴天 2010-12-12
  • 打赏
  • 举报
 回复 
CREATE TABLE [dbo].[test](

    [id] [bigint] IDENTITY(1,1) NOT NULL,

    [time] [datetime] NOT NULL,

    [chinese] [int] NOT NULL,

    [rate] [decimal](18, 2) NOT NULL

) ON [PRIMARY]

insert into test(time, chinese,rate)

select '2010-9-30 11:00:00',1,0.2

union 

select '2010-9-30 15:00:00',2,0.3

union 

select '2010-10-1 12:00:00',3,0.4

union 

select '2010-10-1 16:00:00',4,0.1

union 

select '2010-10-2 11:00:00',5,0.5

union 

select '2010-10-3 11:00:00',6,0.6

union 

select '2010-10-4 11:00:00',7,0.4

union 

select '2010-10-5 11:00:00',8,0.3

union 

select '2010-10-6 11:00:00',9,0.7

union 

select '2010-10-7 11:00:00',1,0.3

union 

select '2010-10-8 11:00:00',2,0.3

union 

select '2010-10-9 11:00:00',3,0.2

union 

select '2010-10-10 10:00:00',5,0.1

union 

select '2010-10-11 9:00:00',7,0.3

union 

select '2010-10-12 9:00:00',1,0.1

union 

select '2010-10-13 9:00:00',2,0.1

union 

select '2010-10-14 9:00:00',3,0.2

union 

select '2010-10-15 9:00:00',5,0.3

union 

select '2010-10-16 9:00:00',1,0.2

union 

select '2010-10-17 9:00:00',2,0.1

go

select convert(varchar(10),time,120),sum(chinese),avg(rate) from test group by convert(varchar(10),time,120)

/*

2010-09-30 3           0.250000

2010-10-01 7           0.250000

2010-10-02 5           0.500000

2010-10-03 6           0.600000

2010-10-04 7           0.400000

2010-10-05 8           0.300000

2010-10-06 9           0.700000

2010-10-07 1           0.300000

2010-10-08 2           0.300000

2010-10-09 3           0.200000

2010-10-10 5           0.100000

2010-10-11 7           0.300000

2010-10-12 1           0.100000

2010-10-13 2           0.100000

2010-10-14 3           0.200000

2010-10-15 5           0.300000

2010-10-16 1           0.200000

2010-10-17 2           0.100000



(18 行受影响)

*/

select datepart(wk,time)as 周次,sum(chinese),avg(rate) from test group by datepart(wk,time)

/*

周次

----------- ----------- ---------------------------------------

40          15          0.300000

41          36          0.400000

42          24          0.185714

43          2           0.100000



(4 行受影响)



*/

select t,s,r/datediff(dd,convert(varchar(7),t,120)+'-01',dateadd(mm,1,convert(varchar(7),t,120)+'-01')) from(

select convert(varchar(7),time,120)as t,sum(chinese) as s,sum(rate)as r from test group by convert(varchar(7),time,120)

)t

/*

t       s           

------- ----------- ---------------------------------------

2010-09 3           0.016666

2010-10 74          0.167741



(2 行受影响)

*/
飘零一叶 2010-12-12
  • 打赏
  • 举报
 回复
看下面这个
select Convert(varchar(10),[time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+1,120) as sdate

,convert(varchar(10),[time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+7,120) as edate

,sum(chinese) as chinese

,avg(rate) as rate

from [test]

group by 

Convert(varchar(10),[time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+1,120),convert(varchar(10),[time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+7,120)
飘零一叶 2010-12-12
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 josy 的回复:]
SQL code
--按天
select convert(varchar(10),[time],120) as [date],
sum(chinese) as chinese,
avg(rate) as rate
from test
group by convert(varchar(10),[time],120)

--按月
select convert(varchar(7)……
[/Quote]

补一个


--按周

select [time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+1 as sdate

,[time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+7 as edate

,sum(chinese) as chinese

,avg(rate) as rate

from [test]

group by 

[time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+1,[time]-DATEPART(WEEKDAY,time+@@DATEFIRST-1)+7
百年树人 2010-12-12
  • 打赏
  • 举报
 回复 
--按天

select convert(varchar(10),[time],120) as [date],

 sum(chinese) as chinese,

 avg(rate) as rate

from test

group by convert(varchar(10),[time],120)



--按月

select convert(varchar(7),[time],120) as [date],

 sum(chinese) as chinese,

 avg(rate) as rate

from test

group by convert(varchar(7),[time],120)

按周稍微复杂一点
hhwww168 2010-12-12
  • 打赏
  • 举报
 回复
新手，不怎么看的懂啊
高效实现统计分析（按日，月，周）查询功能
一个sql统计出指定时间范围内，按月，周，日的结果统计分析实现一个sql查询所有数据按天分最终效果按周分最终效果按月分最终效果 统计分析实现一个sql查询所有数据 业务场景：需要统计某个时间段内的所有数据，按照...
Impala SQL实现按天、自然周、月、季度、年份周期统计
在整理完了Oracle、MySQL中如何通过SQL实现按天、自然周、月、季度、年份周期统计时，忽然想起在hive与impala中，SQL的语法也是不尽相同，但又有共通之处。 俗话说，授人予鱼，不若授人以渔。学习方法、解决问题的...
oracle统计每周数量,Oracle按年、月、日、周等统计数据
在很多项目中都会有数据统计的功能，如按照年、月、周、日统计某个用户提交的数量；或者直接统计指定年、月、周或者日新增的数量。最近我接触的一个项目，客户就要求根据月和周统计每个单位提价提交的数量。按年统计...
sqlServer数据库，将一个月内的数据按周统计
今天遇到一个问题，需要统计一个月的数据，按照每周统计出来，在网上百度了一下，目前没有百度一下，我自己想了一下，再根据网上的一点资料做参考，可以直接实现。 网上有当前日期是当年的第几周，当前日期是这周的...
mysql每个月数据按天行转列_Mysql 中使用DATE_FORMAT函数按月、周统计数据
项目中的统计报表作的很多，需求中有按周、月统计数据的。查看了Mysql的API，发现Date_format是格式化日期的，看了Date_format()的具体说明后就用这个函数按周统计，sql大致如下：selectDATE_FORMAT(check_date ,'%X...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章