Sélectionnez des groupes distincts de lignes en fonction de la moyenne

SQL n'est vraiment pas le meilleur outil pour ce type de problème.

Cependant, il est parfois amusant de baster des vis avec le marteau TSQL !! P>

Voici un effort qui obtient les éléments suivants sur vos données d'exemple de 75 lignes: p> xxx pré>

en dessous une seconde sur ma machine.
Juste une mise en garde: cette méthode comporte des défauts massifs, mais si vous avez besoin de le faire dans SQL, vous pouvez probablement gaffer bande sur eux un peu, je n'ai tout simplement pas eu le temps de. P>

-- **Expects data in table t_stats (id, ratio)**
if OBJECT_ID('tempdb..#data') is not null drop table #data
if OBJECT_ID('tempdb..#pairsets') is not null drop table #pairsets
if OBJECT_ID('tempdb..#pairseed') is not null drop table #pairseed
if OBJECT_ID('tempdb..#match') is not null drop proc #match
go

-- rather horrible routine using dsql to find either:
--   1) groups of values that sum to exactly @targetsum (only if @targetsum non null)
--   2) the group containing the least values that includes data id @includeid and where the sum is within +- @targetsumrange
create proc #match(@targetsum DECIMAL(8,4), @includeid int, @targetsumrange DECIMAL(8,4)) as
begin
  set nocount on
  declare @nearestmatch bit = 0
  if @targetsum is null set @nearestmatch = 1
  declare @combination table (value int, asstring varchar(10), alias varchar(50))  
  declare @savedpairseed int = (select pairseed from #pairseed)

  declare @stmtTemplate varchar(max) = 'declare @pairseed int = (select pairseed from #pairseed)
  declare @DistSum DECIMAL(8,4)
  <DeclareVars>
  declare candloop cursor for select <SelectList>, <DistanceSum> as Dist_sum from <TableList> where <IdCheck> <SumCheck>
  open candloop
  fetch next from candloop into <VarsList>, @DistSum
  while @@fetch_status = 0
  begin
    if (select count(*) from #data where id in (<VarsList>)) = <VarsCount>
    begin
      <DeleteData>
      <InsertPairs>
      set @pairseed = @pairseed + 1  
    end
    fetch next from candloop into <VarsList>, @DistSum
  end
  close candloop
  deallocate candloop
  update #pairseed set pairseed = @pairseed  '

  declare @combinations int = 1
  declare @maxcombinations int = 8
  while @combinations <= @maxcombinations
  begin
    insert @combination select @combinations, cast(@combinations as varchar(10)), char(ascii('a') + @combinations-1)
    declare @DeclareVars varchar(max) = ''
    declare @SelectList varchar(max) = ''
    declare @TableList varchar(max) = ''
    declare @IdCheck varchar(max) = ''
    declare @DistanceSum varchar(max) = ''
    declare @InsertPairs varchar(max) = ''
    declare @VarsList varchar(max) = ''
    declare @SumCheck varchar(max) = ''
    declare @DeleteData varchar(max) = 'delete #data where id in (<VarsList>)'

    select @DeclareVars = @DeclareVars + 'declare @id'+asstring+ ' int ' from @combination
    select @SelectList = @SelectList + alias +'.id, ' from @combination
    set @SelectList = SUBSTRING(@selectlist, 1, LEN(@SelectList)-1)
    select @TableList = @TableList + '#data '+alias+', ' from @combination
    set @TableList = SUBSTRING(@TableList, 1, LEN(@TableList)-1)
    select @IdCheck = @IdCheck + a.alias+'.id < '+b.alias+'.id and '
    from @combination a join @combination b on a.value+1 = b.value   
    if LEN(@IdCheck) > 4
      set @IdCheck = SUBSTRING(@IdCheck, 1, LEN(@IdCheck)-4) + ' and '
    select @DistanceSum = @DistanceSum + alias+'.targetdistance + ' from @combination
    set @DistanceSum = SUBSTRING(@DistanceSum, 1, LEN(@DistanceSum)-2)
    select @VarsList = @VarsList + '@id'+asstring+ ', ' from @combination
    set @VarsList = SUBSTRING(@VarsList, 1, LEN(@VarsList)-1)
    select @InsertPairs = @InsertPairs + 'insert #pairsets select @pairseed, @id'+asstring+ ', @DistSum'+ CHAR(10) from @combination
    set @SumCheck = @DistanceSum + ' = '+ cast(@Targetsum as varchar(20))   

    if @nearestmatch = 1 
    begin
      set @SumCheck = '('
      select @SumCheck = @SumCheck + alias+'.id = '+CAST(@includeid as varchar(10))+' or ' from @combination
      if LEN(@SumCheck) > 4
        set @SumCheck = SUBSTRING(@SumCheck, 1, LEN(@SumCheck)-3)
      set @SumCheck = @SumCheck + ')'
      set @DeleteData = ''
    end

    declare @stmt varchar(max)
    set @stmt = REPLACE(@stmtTemplate, '<DeclareVars>', @DeclareVars)
    set @stmt = REPLACE(@stmt, '<DeleteData>', @DeleteData)
    set @stmt = REPLACE(@stmt, '<SelectList>', @SelectList)
    set @stmt = REPLACE(@stmt, '<TableList>', @TableList)
    set @stmt = REPLACE(@stmt, '<IdCheck>', @IdCheck)
    set @stmt = REPLACE(@stmt, '<DistanceSum>', @DistanceSum)
    set @stmt = REPLACE(@stmt, '<InsertPairs>', @InsertPairs)
    set @stmt = REPLACE(@stmt, '<VarsList>', @VarsList)
    set @stmt = REPLACE(@stmt, '<VarsCount>', cast(@combinations as varchar(10)))
    set @stmt = REPLACE(@stmt, '<SumCheck>', @SumCheck)       
    exec (@stmt)    
    set @combinations = @combinations + 1
  end

  if @nearestmatch = 1
  begin
    -- above will have recorded all possible matches within range
    -- remove all but the closest and reindex the pair ids
    declare @bestmatch int
    select top 1 @bestmatch = pairid from #pairsets where pairid >= @savedpairseed and ABS(distsum) < @targetsumrange 
    delete #pairsets where pairid >= @savedpairseed and pairid <> ISNULL(@bestmatch, -1)
    delete #data where id in (select id from #pairsets where pairid = @bestmatch)
    update #pairsets set pairid = @savedpairseed where pairid = @bestmatch
    update #pairseed set pairseed = @savedpairseed+1
  end

end

go
set nocount on
-- set the parameters
declare @xmin DECIMAL(8,4) = 0.75
declare @xmax DECIMAL(8,4) = 0.85
declare @xrange DECIMAL(8,4) = @xmax - @xmin
declare @xtarg DECIMAL(8,4) = (@xmin+@xmax) / 2
declare @ngroups int = 3
declare @targetgroupsize int = 25

declare @maxbalancedpair int 

-- copy the ratio data (using 75 row data from updated question)
select *, ratio-@xtarg as targetdistance, abs(ratio - @xtarg) as targetdistanceabsolute into #data from t_stats
create table #pairseed (pairseed int)
create table #pairsets (pairid int, id int, distsum DECIMAL(8,4) )
insert #pairseed select 1

-- due to the 2 decimal points and distribution of the data we can find many n-tuples that sum to zero
exec #match 0, 0, 0

select @maxbalancedpair = pairseed-1 from #pairseed

declare @deviants table (id int)
declare @most_deviant int
while exists(select * from #data where id not in (select id from @deviants)) 
begin
  select top 1 @most_deviant = id from #data where id not in (select id from @deviants)  order by targetdistanceabsolute desc
  insert @deviants select @most_deviant
  exec #match null, @most_deviant, @xrange
end

-- in general there would have to be some backtracking here
-- now its a box-packing problem, but for simplicity just assign them round robin
declare  @output_group_pairs table (groupid int, pairid int)
declare @groupidx int = 1
declare @numgroups int = 3
declare @pairid int
select @pairid = pairseed-1 from #pairseed

while @pairid >= 0
begin
  insert @output_group_pairs select @groupidx, @pairid
  set @pairid = @pairid - 1
  set @groupidx = (@groupidx % @numgroups) + 1
end

-- wimpy effort at redistributing the groups evenly
-- todo: many cases will not work, should use a proper algorithm

declare @maxiter int = 100
declare @previouspairs table (pairid int)
declare @previousgroups table (groupid int)
while exists(select groupid from @output_group_pairs a join #pairsets b on a.pairid = b.pairid group by groupid having COUNT(id) < @targetgroupsize)
begin  
  set @maxiter = @maxiter-1
  if @maxiter = 0 break
  declare @groupid int = -1
  declare @amountout int
  select @groupid = groupid, @amountout = @targetgroupsize-COUNT(*) 
  from @output_group_pairs a join #pairsets b on a.pairid = b.pairid 
  where groupid not in (select groupid from @previousgroups)
  group by groupid having COUNT(*) < @targetgroupsize
  if @groupid = -1 break

  declare @targetpair int = -1  

  select @targetpair = a.pairid from @output_group_pairs a
  join (select pairid from #pairsets group by pairid having COUNT(*) <= @amountout) b on a.pairid = b.pairid
  join (select groupid, count(id) groupcount from @output_group_pairs a join #pairsets b on a.pairid = b.pairid group by groupid) group_counts on a.groupid = group_counts.groupid
  where a.pairid not in (select pairid from @previouspairs)
  order by abs(@amountout - groupcount) asc

  if @targetpair = -1
  begin
    insert @previousgroups select @groupid
  end
  else
  begin
    insert @previouspairs select @targetpair
    update @output_group_pairs set groupid = @groupid where pairid = @targetpair   
  end
end

set @maxiter = 100
delete @previouspairs
delete @previousgroups
while exists(select groupid from @output_group_pairs a join #pairsets b on a.pairid = b.pairid group by groupid having COUNT(id) > @targetgroupsize)
begin  
  set @maxiter = @maxiter-1
  if @maxiter = 0 break
  set @groupid = -1
  set @amountout = null
  select @groupid = groupid, @amountout = COUNT(*)-@targetgroupsize 
  from @output_group_pairs a join #pairsets b on a.pairid = b.pairid 
  where groupid not in (select groupid from @previousgroups)
  group by groupid having COUNT(*) > @targetgroupsize
  if @groupid = -1 break

  set @targetpair = -1 

  select @targetpair = a.pairid from @output_group_pairs a
  join (select pairid from #pairsets group by pairid having COUNT(*) <= @amountout) b on a.pairid = b.pairid
  join (select groupid, count(id) groupcount from @output_group_pairs a join #pairsets b on a.pairid = b.pairid group by groupid) group_counts on a.groupid = group_counts.groupid
  where a.pairid not in (select pairid from @previouspairs)
  order by abs(@amountout - groupcount) asc

  if @targetpair = -1
  begin
    insert @previousgroups select @groupid
  end
  else
  begin  
    insert @previouspairs select @targetpair
    delete @output_group_pairs where pairid = @targetpair
  end   
end

-- output groups and their stats
select GroupId, Id from @output_group_pairs a join #pairsets b on a.pairid = b.pairid order by 1, 2

select a.GroupId, AVG(c.ratio) as [Average] , count(*) as [Count]
from @output_group_pairs a
join #pairsets b on a.pairid = b.pairid
join t_stats c on b.id = c.id
group by a.groupid
go

drop table #data
drop table #pairsets
drop table #pairseed
drop proc #match