取样统计的SQL语句怎么写?

[fang1229]

关于抽样统计的问题：
一个表里面有三个字段：测试项TestItem、测试时间TestTime和测试值TestValue
现在要做抽样统计：在存储过程中按照给定的时间段@starttime和@stoptime，将这个时间段分成25个等长时间段， 然后在25个时间段中每个时间段内抽取5条记录， 再对每个时间段中的5条记录取他们的测试值的最大与最小的差值， 最后对这25个差值取平均值。
请问这个抽样统计用SQL怎么写，因为具体到实践中还需要统计一个类似的数据，所以考虑要用表变量，或者临时表，不知道对不对？

[fang1229]

[Quote=引用 2 楼 的回复:]
1、25个等长的时间段，这个是按什么计算的，小时 分钟 秒数 ？
2、每个时间段抽取5条记录，抽取方式是怎样的？随机？
[/Quote]

1.时间段按分钟算的， 2.抽取方式是随机order by newid()

[开着拖拉机泡妞]

[Quote=引用 6 楼 的回复:]

引用 2 楼 的回复:
1、25个等长的时间段，这个是按什么计算的，小时 分钟 秒数 ？
2、每个时间段抽取5条记录，抽取方式是怎样的？随机？


1.时间段按分钟算的， 2.抽取方式是随机order by newid()
[/Quote]

你给点你的测试数据和你要的结果，大家看了更加明白，而且更加方便测试

[fang1229]

[Quote=引用 5 楼 的回复:]
SQL code
declare @i int,@a datetime,@b datetime
set @a='2012-03-28 09:00:00'
set @b='2012-03-28 09:50:00'
set @i=datediff(mi,@a,@b)*1.0/25
select @i
select dateadd(mi,number*@i,@a),dateadd(mi,(n……
[/Quote]

以下是我写的sql语句 我是用个while循环来做时间段内的统计的，得到的数据放在一个表变量里面，再对表变量的数据做统计得到结果 大家帮我看下

错误提示：
消息 156，级别 15，状态 1，过程 TestItem_SPCPID_DT，第 38 行
关键字 'select' 附近有语法错误。
消息 156，级别 15，状态 1，过程 TestItem_SPCPID_DT，第 43 行
关键字 'insert' 附近有语法错误。

IF OBJECT_ID ('TestwithHourAxis', 'V') IS NOT NULL
DROP VIEW TestwithHourAxis ;
GO
Create view TestwithHourAxis
as
select ProductTypeID,ti.ProductSN,ti.TestTime,TestItemName,
	TestTypeID,StartF,StopF,Max,Min,HighSpec,LowSpec,ti.IsPass,TestTypeName
from TestItem ti inner join Test t 
on ti.ProductSN=t.ProductSN and ti.TestTime=t.TestTime



IF OBJECT_ID ( 'TestItem_SPCPID_DT', 'P' ) IS NOT NULL 
    DROP PROCEDURE TestItem_SPCPID_DT;
GO
CREATE PROCEDURE TestItem_SPCPID_DT
	@ProductTypeID nvarchar(50) =null,
	@TestItemName nvarchar(50),
	@starttime datetime,
	@stoptime datetime
AS    
    begin
    declare @i int
    declare @timediff int
    --declare @pretime datetime
    --declare @posttime datetime
    declare @HighSpec decimal(18,4)
    declare @LowSpec decimal(18,4)
    declare @avgval decimal(18,4)
    declare @rangeval decimal(18,4)
    declare @TableVariable TABLE (avgval decimal(18,4),rangeval decimal(18,4))
    
    set @i=0
    set @timediff=datediff(mi,@starttime,@stoptime)/25
    set @HighSpec=(
    select top 1 HighSpec 
    from TestwithHourAxis 
    where ProductTypeID=@ProductTypeID and TestItemName=@TestItemName)
    set @LowSpec=(
    select top 1 LowSpec 
    from TestwithHourAxis 
    where ProductTypeID=@ProductTypeID and TestItemName=@TestItemName)
    
    while @i<25
    begin 
    --set @pretime=dateadd(mi,@i*@timediff,@starttime)
    --set @posttime=dateadd(mi,(@i+1)*@timediff,@starttime)
    select @avgval=avg(Max) from
    (select top 5 * from TestwithHourAxis 
    where TestTime between dateadd(mi,@i*@timediff,@starttime) and dateadd(mi,(@i+1)*@timediff,@starttime) 
    and ProductTypeID=@ProductTypeID and TestItemName=@TestItemName
    order by newid())
    select @rangeval=(max(Max)-min(Max)) from
    (select top 5 * from TestwithHourAxis  
    where TestTime between dateadd(mi,@i*@timediff,@starttime) and dateadd(mi,(@i+1)*@timediff,@starttime) 
    and ProductTypeID=@ProductTypeID and TestItemName=@TestItemName
    order by newid())
    insert @TableVariable values(@avgval,@rangeval) 
    set @i=@i+1
    end
    
    select avg(avgval),avg(rangeval),
    (case when avg(avgval) is null
		then null 
		else case when @HighSpec is null 
			then avg(avgval)+avg(rangeval)*0.58 
			else case when @HighSpec<avg(avgval)+avg(rangeval)*0.58
				then @HighSpec
				else avg(avgval)+avg(rangeval)*0.58 
				end
			end
		end) as UCLx,
	(case when avg(avgval) is null 
		then null 
		else case when @LowSpec is null
			then avg(avgval)-avg(rangeval)*0.58 
			else case when @LowSpec>avg(avgval)-avg(rangeval)*0.58 
				then @LowSpec 
				else avg(avgval)-avg(rangeval)*0.58 
				end 
			end 
		end) as LCLx,
	(case when @LowSpec is null 
		then avg(rangeval)*2.11 
		else case when @HighSpec-@LowSpec<avg(rangeval)*2.11 
			then @HighSpec-@LowSpec 
			else avg(rangeval)*2.11 
			end 
		end) as UCL,
	avg(rangeval)*0 as LCL 
	from @TableVariable
	end
go

 
	
exec TestItem_SPCPID_DT
	@ProductTypeID='AGX18'
	@TestItmeName ='Gain.(B-1AOut1)',
	@starttime ='2012-03-22 14:51:00.000',
	@stoptime ='2012-03-27 14:51:00.000'

[fang1229]

我的表结构：

CREATE TABLE [dbo].[Test](
	[TestID] [uniqueidentifier] NOT NULL,
	[ProductSN] [nvarchar](50) NOT NULL,
	[TestTime] [datetime] NOT NULL,
	[ProductTypeID] [nvarchar](50) NULL,
	[UserID] [nvarchar](20) NULL,
	[ENAID] [nvarchar](50) NULL,
	[IsPass] [nvarchar](10) NULL,
	[IsSwitch] [nvarchar](10) NULL)

CREATE TABLE [dbo].[TestItem](
	[TestItemID] [uniqueidentifier] NOT NULL,
	[TestItemName] [nvarchar](50) NULL,
	[ProductSN] [nvarchar](50) NULL,
	[TestTime] [datetime] NULL,
	[TestTypeID] [int] NULL,
	[StartF] [decimal](18, 4) NULL,
	[StopF] [decimal](18, 4) NULL,
	[Max] [decimal](18, 4) NULL,
	[Min] [decimal](18, 4) NULL,
	[HighSpec] [decimal](18, 4) NULL,
	[LowSpec] [decimal](18, 4) NULL,
	[IsPass] [nvarchar](10) NULL,
	[TestTypeName] [nvarchar](50) NULL)

[fang1229]

数据要怎么贴出来方便大家测试呢？

以下是我的算法中用到的部分数据
ProductTypeID ProductSN TestTime TestItemName Max Min HighSpec LowSpec
AGX18 ZM105031034 2012-3-23 15:15 Gain.(B-0BOut1) 15.25 14.24 15.58 14.1
AGX18 ZM120933566 2012-3-24 09:22 Gain.(B-0BOut1) 15.47 14.59 15.58 14.1
AGX18 ZM120933568 2012-3-24 11:08 Gain.(B-0BOut1) 15.39 14.54 15.58 14.1
AGX18 ZM120631038 2012-3-27 18:18 Gain.(B-0BOut1) 15.45 14.43 15.58 14.1
AGX18 ZM120631038 2012-3-27 18:18 Gain.(B-0BOut1) 15.45 14.43 15.58 14.1
AGX18 ZM120933560 2012-3-24 01:37 Gain.(B-0BOut1) 15.28 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933559-1 2012-3-25 08:55 Gain.(B-0BOut1) 15.43 14.41 15.58 14.1
AGX18 ZM120933594 2012-3-25 06:10 Gain.(B-0BOut1) 15.52 14.38 15.58 14.1
AGX18 ZM120933597 2012-3-25 06:15 Gain.(B-0BOut1) 15.51 14.5 15.58 14.1
AGX18 ZM120933616 2012-3-26 04:07 Gain.(B-0BOut1) 15.44 14.42 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933570 2012-3-24 05:57 Gain.(B-0BOut1) 15.45 14.36 15.58 14.1
AGX18 ZM120933599 2012-3-25 09:06 Gain.(B-0BOut1) 15.34 14.44 15.58 14.1
AGX18 ZM120933563 2012-3-24 11:13 Gain.(B-0BOut1) 15.27 14.38 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120631038 2012-3-27 18:18 Gain.(B-0BOut1) 15.45 14.43 15.58 14.1
AGX18 ZM120631038 2012-3-27 18:18 Gain.(B-0BOut1) 15.45 14.43 15.58 14.1
AGX18 ZM120933570 2012-3-24 05:57 Gain.(B-0BOut1) 15.45 14.36 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933572-2 2012-3-24 06:14 Gain.(B-0BOut1) 15.57 14.56 15.58 14.1
AGX18 ZM120933596 2012-3-25 06:01 Gain.(B-0BOut1) 15.56 14.56 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:35 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933632 2012-3-28 09:40 Gain.(B-0BOut1) 15.38 14.51 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933558 2012-3-24 02:03 Gain.(B-0BOut1) 15.54 14.42 15.58 14.1
AGX18 ZM120933558 2012-3-24 02:03 Gain.(B-0BOut1) 15.54 14.42 15.58 14.1
AGX18 ZM120933565 2012-3-24 06:19 Gain.(B-0BOut1) 15.19 14.24 15.58 14.1
AGX18 ZM120933573 2012-3-24 06:08 Gain.(B-0BOut1) 15.44 14.43 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933557 2012-3-24 01:29 Gain.(B-0BOut1) 15.5 14.53 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933587-1 2012-3-25 02:57 Gain.(B-0BOut1) 15.43 14.35 15.58 14.1
AGX18 ZM120933622 2012-3-27 06:00 Gain.(B-0BOut1) 15.22 14.33 15.58 14.1
AGX18 ZM120933607 2012-3-26 04:30 Gain.(B-0BOut1) 15.39 14.36 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:35 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1
AGX18 ZM120933613 2012-3-26 04:04 Gain.(B-0BOut1) 15.57 14.45 15.58 14.1
AGX18 ZM120933604 2012-3-26 03:52 Gain.(B-0BOut1) 15.56 14.47 15.58 14.1
AGX18 ZM120933600 2012-3-27 09:56 Gain.(B-0BOut1) 15.7 14.64 15.58 14.1
AGX18 ZM120933572-2 2012-3-24 06:14 Gain.(B-0BOut1) 15.57 14.56 15.58 14.1
AGX18 ZM120933623 2012-3-28 09:19 Gain.(B-0BOut1) 15.32 14.44 15.58 14.1
AGX18 ZM120933624 2012-3-27 18:36 Gain.(B-0BOut1) 15.55 14.21 15.58 14.1

[Vidor]

关键是你不知道怎么等分吧，参考

declare @a datetime = '2012-03-01'
declare @b datetime = '2012-03-02 01:00'
declare @minute int = datediff(minute,@a,@b)/25;
with cte as
(
	select group_id=datediff(minute,dt,@a)/@minute, * from tb where dt between @a and @b
)
select * into #tmp from cte t where [key] in (select top 5 [key] from tb where group_id=t.group_id order by newid())

select max(val)-min(val), avg(max(val)-min(val))over() from #tmp group by group_id

[开着拖拉机泡妞]

你给点测试数据吧

[AcHerat]

1、25个等长的时间段，这个是按什么计算的，小时 分钟 秒数 ？
2、每个时间段抽取5条记录，抽取方式是怎样的？随机？

[AcHerat]

楼主最好是结合测试表的结构 数据 和 统计结果来说明，会更直观。

[黄_瓜]

你这个时间段是按照什么单位来算的？

分钟还是小时还是？？？？

[黄_瓜]

declare @i int,@a datetime,@b datetime
set @a='2012-03-28 09:00:00'
set @b='2012-03-28 09:50:00'
set @i=datediff(mi,@a,@b)*1.0/25
select @i
select dateadd(mi,number*@i,@a),dateadd(mi,(number+1)*@i,@a) from master..spt_values 
where type='p'
and number<=25

计算时间段 放到一个临时表里面，这样在做连接查询 就好弄了

关键是你这个时间差如果不被25整出就会出现很大的差异，请注意一下