数据累加

[qq_36769719]

X_ID X_WaveLength X_Strength X_Time
17429 146.389662260571 36 2018-11-12 10:11:34.000
17430 146.40408994652 31 2018-11-12 10:11:34.000
17431 146.418516935926 32 2018-11-12 10:11:34.000
17432 146.432943228897 33 2018-11-12 10:11:34.000
17433 146.44736882554 41 2018-11-12 10:11:34.000

每15行数据为一组 一组中的15个X_Strength数据累加之后再求每一组数据的平均数

[qq_36769719]

[

引用 2 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[X_ID] INT
,[X_WaveLength] DECIMAL(28,16) 
,[X_Strength] INT
,[X_Time] DATETIME	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'17429',N'146.389662260571',N'36',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17430',N'146.40408994652',N'31',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17431',N'146.418516935926',N'32',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17432',N'146.432943228897',N'33',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17433',N'146.44736882554',N'41',N'2018-11-12 10:11:34.000')

--定义多少行一组，为了方便对照，这里取 2 。你实际应用改为15
DECLARE @n INT
SET @n=2

;WITH cte AS (
	SELECT ROW_NUMBER() OVER (ORDER BY x_id) AS rid,* FROM t
)
SELECT 
	(rid+@n-1)/@n AS groupId
	,SUM(X_Strength) AS X_Strength_sum 
	,AVG(X_Strength*1.0) AS X_Strength_avg
FROM cte 
GROUP BY (rid+@n-1)/@n
/*
groupId              X_Strength_sum X_Strength_avg
-------------------- -------------- ---------------------------------------
1                    67             33.500000
2                    65             32.500000
3                    41             41.000000
 */

在我这里输出结果有点不对，能看看是哪里出现了问题吗

[qq_36769719]

引用 1 楼 RINK_1 的回复:

SELECT SEQ,SUM(X_Strength) AS TOTAL,AVG(X_Strength) AS AVERAGE
FROM
(SELECT *,(ROW_NUMBER() OVER (ORDER BY X_ID)-1)/15 AS SEQ FROM TABLE) AS A
GROUP BY SEQ

第一个对了，可是后面的不对，能看看是什么问题吗

[吉普赛的歌]

引用 3 楼 qq_36769719 的回复:

[ [quote=引用 2 楼 yenange 的回复:]

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[X_ID] INT
,[X_WaveLength] DECIMAL(28,16) 
,[X_Strength] INT
,[X_Time] DATETIME	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'17429',N'146.389662260571',N'36',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17430',N'146.40408994652',N'31',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17431',N'146.418516935926',N'32',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17432',N'146.432943228897',N'33',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17433',N'146.44736882554',N'41',N'2018-11-12 10:11:34.000')

--定义多少行一组，为了方便对照，这里取 2 。你实际应用改为15
DECLARE @n INT
SET @n=2

;WITH cte AS (
	SELECT ROW_NUMBER() OVER (ORDER BY x_id) AS rid,* FROM t
)
SELECT 
	(rid+@n-1)/@n AS groupId
	,SUM(X_Strength) AS X_Strength_sum 
	,AVG(X_Strength*1.0) AS X_Strength_avg
FROM cte 
GROUP BY (rid+@n-1)/@n
/*
groupId              X_Strength_sum X_Strength_avg
-------------------- -------------- ---------------------------------------
1                    67             33.500000
2                    65             32.500000
3                    41             41.000000
 */

在我这里输出结果有点不对，能看看是哪里出现了问题吗[/quote] 我的代码不可能有问题， 总共才 4 行数据。不可能弄那么多 groupId 出来。 再说了，即使你增加了数据量，但你的 groupId 都不连续的？ 你自己把代码改过了吧？ 如果是未排序导致， 可以加一行排序试下：

GROUP BY (rid+@n-1)/@n
ORDER BY 1 --增加排序

[qq_36769719]

引用 5 楼 yenange 的回复:

[quote=引用 3 楼 qq_36769719 的回复:]
[

[quote=引用 2 楼 yenange 的回复:]

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[X_ID] INT
,[X_WaveLength] DECIMAL(28,16) 
,[X_Strength] INT
,[X_Time] DATETIME	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'17429',N'146.389662260571',N'36',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17430',N'146.40408994652',N'31',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17431',N'146.418516935926',N'32',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17432',N'146.432943228897',N'33',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17433',N'146.44736882554',N'41',N'2018-11-12 10:11:34.000')

--定义多少行一组，为了方便对照，这里取 2 。你实际应用改为15
DECLARE @n INT
SET @n=2

;WITH cte AS (
	SELECT ROW_NUMBER() OVER (ORDER BY x_id) AS rid,* FROM t
)
SELECT 
	(rid+@n-1)/@n AS groupId
	,SUM(X_Strength) AS X_Strength_sum 
	,AVG(X_Strength*1.0) AS X_Strength_avg
FROM cte 
GROUP BY (rid+@n-1)/@n
/*
groupId              X_Strength_sum X_Strength_avg
-------------------- -------------- ---------------------------------------
1                    67             33.500000
2                    65             32.500000
3                    41             41.000000
 */

在我这里输出结果有点不对，能看看是哪里出现了问题吗[/quote]

我的代码不可能有问题， 总共才 4 行数据。不可能弄那么多 groupId 出来。
再说了，即使你增加了数据量，但你的 groupId 都不连续的？
你自己把代码改过了吧？

如果是未排序导致， 可以加一行排序试下：

GROUP BY (rid+@n-1)/@n
ORDER BY 1 --增加排序

[/quote]
多谢，的确是因为没有排序，不止四行数据

[二月十六]

楼主你把yenange版主的rid+@n 改成X_ID+@rn了，另外RINK_1的代码我试了一下应该也行，你直接执行一下这个试试

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([X_ID] int,[X_WaveLength] FLOAT,[X_Strength] FLOAT,[X_Time] Date)
Insert #T
select 1,146,86,'2018-11-12' union all
select 2,146,78,'2018-11-12' union all
select 3,146,73,'2018-11-12' union all
select 4,146,80,'2018-11-12'
Go
--测试数据结束
SELECT SEQ,SUM(X_Strength) AS TOTAL,AVG(X_Strength) AS AVERAGE
FROM
(SELECT *,(ROW_NUMBER() OVER (ORDER BY X_ID)-1)/2 AS SEQ FROM #T) AS A
GROUP BY SEQ

[吉普赛的歌]

引用 6 楼 qq_36769719 的回复:

多谢，的确是因为没有排序，不止四行数据

不客气， 没事就结贴吧

[RINK_1]

SELECT SEQ,SUM(X_Strength) AS TOTAL,AVG(X_Strength) AS AVERAGE
FROM
(SELECT *,(ROW_NUMBER() OVER (ORDER BY X_ID)-1)/15 AS SEQ FROM TABLE) AS A
GROUP BY SEQ

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[X_ID] INT
,[X_WaveLength] DECIMAL(28,16) 
,[X_Strength] INT
,[X_Time] DATETIME	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'17429',N'146.389662260571',N'36',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17430',N'146.40408994652',N'31',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17431',N'146.418516935926',N'32',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17432',N'146.432943228897',N'33',N'2018-11-12 10:11:34.000')
INSERT INTO dbo.[t] VALUES(N'17433',N'146.44736882554',N'41',N'2018-11-12 10:11:34.000')

--定义多少行一组，为了方便对照，这里取 2 。你实际应用改为15
DECLARE @n INT
SET @n=2

;WITH cte AS (
	SELECT ROW_NUMBER() OVER (ORDER BY x_id) AS rid,* FROM t
)
SELECT 
	(rid+@n-1)/@n AS groupId
	,SUM(X_Strength) AS X_Strength_sum 
	,AVG(X_Strength*1.0) AS X_Strength_avg
FROM cte 
GROUP BY (rid+@n-1)/@n
/*
groupId              X_Strength_sum X_Strength_avg
-------------------- -------------- ---------------------------------------
1                    67             33.500000
2                    65             32.500000
3                    41             41.000000
 */