假设有一表,字段是日前,姓名,单日销售额,如下:
day name day_total
2008-01-01 a 1000
2008-01-01 b 1100
2008-01-01 c 900
2008-01-01 d 1020
2008-01-01 e 810
......
2008-01-31 a 1310
2008-01-31 b 1000
2008-01-31 c 1600
2008-01-31 d 1010
2008-01-31 e 990 我想统计每个人每4天的平均销售量,同时仍旧在同一列,例如:
name avg_4_total
a 1xxx //每4天的平均量
a 1xxx
a 9xx
...
b 1xxx
b 1xxx
b 1xxx
....
c 9xx
c 1xxx
c 1xxx
....
可以吗?谢谢啊~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
day name day_total
2008-01-01 a 1000
2008-01-01 b 1100
2008-01-01 c 900
2008-01-01 d 1020
2008-01-01 e 810
......
2008-01-31 a 1310
2008-01-31 b 1000
2008-01-31 c 1600
2008-01-31 d 1010
2008-01-31 e 990 我想统计每个人每4天的平均销售量,同时仍旧在同一列,例如:
name avg_4_total
a 1xxx //每4天的平均量
a 1xxx
a 9xx
...
b 1xxx
b 1xxx
b 1xxx
....
c 9xx
c 1xxx
c 1xxx
....
可以吗?谢谢啊~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INSERT @t SELECT '2008-01-01', 'a' , 1000
UNION ALL SELECT '2008-01-01', 'b' , 1100
UNION ALL SELECT '2008-01-01', 'c' , 900
UNION ALL SELECT '2008-01-01', 'd' , 1020
UNION ALL SELECT '2008-01-01', 'e' , 810
UNION ALL SELECT '2008-01-03', 'a', 600
UNION ALL SELECT '2008-01-04', 'a', 700
UNION ALL SELECT '2008-01-05', 'a', 600
UNION ALL SELECT '2008-01-06', 'a', 900
UNION ALL SELECT '2008-01-15', 'b', 600
UNION ALL SELECT '2008-01-31', 'a' , 1310
UNION ALL SELECT '2008-01-31', 'b' , 1000
UNION ALL SELECT '2008-01-31', 'c' , 1600
UNION ALL SELECT '2008-01-31', 'd' , 1010
UNION ALL SELECT '2008-01-31', 'e', 990
SELECT name,'第' + RTRIM(FLOOR(datediff(dd,'2008-1-1',day)/4.0)+1) + '个四天',avg(day_total) day_4_avg FROM @t group by name,'第' + RTRIM(FLOOR(datediff(dd,'2008-1-1',day)/4.0)+1) + '个四天'
将语句中的 '2008-1-1' 改为 (convert(varchar(7),day,120) + '-1')
create table tb(day nvarchar(10),name nvarchar(10),day_total int)
insert tb select '2008-01-01','a',1500 union select '2008-01-01','b',1300 union select '2008-01-02','a',1530 union select '2008-01-02','b',1260
union select '2008-01-03','a',1500 union select '2008-01-03','b',1300 union select '2008-01-04','a',1530 union select '2008-01-04','b',1260
union select '2008-01-05','a',1500 union select '2008-01-05','b',1300 union select '2008-01-06','a',1520 union select '2008-01-06','b',1280
select * from tb order by name,dayselect name,avg_4_total=avg(day_total) from tb
group by name,(day(day)-1)/4
order by name/*
name avg_4_total
------------------
a 1515
a 1510
b 1280
b 1290
*/
select name,avg(day_total) from
(select *,rn=ROW_NUMBER() over(partition by name order by day) from tb) t
group by name,(rn-1)/4
order by name