仍旧是关于sql 固定分组的问题，谢谢！

[jxpeter]

假设有一表，字段是日前，姓名，单日销售额，如下：
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
....


可以吗？谢谢啊~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[CN_SQL]

每4天怎么确定?
数据是否跨月?
数据库是2000还是2005?

[fcuandy]

DECLARE @t TABLE(day  DATETIME,        name  VARCHAR,   day_total INT)
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) + '个四天'

[fcuandy]

如果跨年或跨月,
将语句中的 '2008-1-1' 改为 (convert(varchar(7),day,120) + '-1')

[jxpeter]

1.就是从选中的那天算起，也可以从第一天算起
2.是跨月的，数据较大
3.数据库是2000的

谢谢啊~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[-晴天]

select name,cast(day as int)/4, avg(day_total) from tb group by name,cast(day as int)/4 order by name

[等不到来世]

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,day

select 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
*/

[jxpeter]

日期有很多都是不连贯的，能不能不管日期连不连贯，都能统计出4天的平均销售量？（就是说这个4天可能不是连贯的4天）

谢谢啊

[等不到来世]

[Quote=引用 7 楼 jxpeter 的回复:]
日期有很多都是不连贯的，能不能不管日期连不连贯，都能统计出4天的平均销售量？（就是说这个4天可能不是连贯的4天）

谢谢啊
[/Quote]

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