Строить хранимую процедуру для группировки данных в диапазоны с примерно равными результатами в каждом ковше

Я пытаюсь создать одну процедуру для получения большого объема данных и создать 5 диапазонов для отображения данных. диапазоны ведер должны быть установлены в соответствии с результатами.

Вот мой существующий SP

GO /****** Object: StoredProcedure [dbo].[sp_GetRangeCounts] Script Date: 03/28/2010 19:50:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_GetRangeCounts] @idMenu int AS declare @myMin decimal(19,2), @myMax decimal(19,2), @myDif decimal(19,2), @range1 decimal(19,2), @range2 decimal(19,2), @range3 decimal(19,2), @range4 decimal(19,2), @range5 decimal(19,2), @range6 decimal(19,2) SELECT @myMin=Min(modelpropvalue), @myMax=Max(modelpropvalue) FROM xmodelpropertyvalues where [email protected] set @myDif=(@[email protected])/5 set @[email protected] set @[email protected][email protected] set @[email protected][email protected] set @[email protected][email protected] set @[email protected][email protected] set @[email protected][email protected] select @myMin,@myMax,@myDif,@range1,@range2,@range3,@range4,@range5,@range6 select t.range as myRange, count(*) as myCount from ( select case when modelpropvalue between @range1 and @range2 then 'range1' when modelpropvalue between @range2 and @range3 then 'range2' when modelpropvalue between @range3 and @range4 then 'range3' when modelpropvalue between @range4 and @range5 then 'range4' when modelpropvalue between @range5 and @range6 then 'range5' end as range from xmodelpropertyvalues where [email protected]) t group by t.range order by t.range 

Это вычисляет значение min и max из моей таблицы, выдает разницу между ними и создает 5 ковшей. Проблема в том, что если есть небольшое количество очень высоких (или очень низких) значений, то ведра будут казаться очень искаженными – как в этих результатах …

 range1 2806 range2 296 range3 75 range5 1 

В основном я хочу перестроить SP, чтобы он создавал ведра с одинаковым количеством результатов в каждом. Я играл с некоторыми из следующих подходов, не прибивая его …

SELECT modelpropvalue, NTILE (5) OVER (ORDER BY modelpropvalue) FROM xmodelpropertyvalues ​​- это создает новый столбец с 1,2,3,4 или 5 в нем

  ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range1 and @range2 ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range2 and @range3 
  • или, может быть, я мог бы выделить каждую запись номера строки, а затем разделить на диапазоны от этого?

Вы можете использовать функцию ранжирования ntile для разделения набора результатов в равных частях. В этом примере создается таблица со значениями 1 … 100 и разделяется на 5 диапазонов:

 set nocount on declare @t table (value int) declare @i int set @i = 0 while @i < 100 begin insert into @t (value) values (@i) set @i = @i + 1 end select NTILE(5) over (order by value) as range , value from @t 

Используя ntile в подзапросе, вы можете ntile группы и ntile математику на диапазонах. Например, чтобы напечатать сумму чисел 1..19, 20..39, 40..59 и т. Д.

 select range, SUM(value) from ( select NTILE(5) over (order by value) as range , value from @t ) sub group by range 

Вы можете использовать top 20% чтобы получить первую пятую записи, которая даст вам конец первого диапазона:

 select @range1 = max(modelpropvalue) from ( select top 20% modelpropvalue from xmodelpropertyvalues where modelPropUnitDescriptionID = @idMenu order by modelpropvalue ) x 

Затем вы можете использовать это значение, чтобы исключить первый диапазон и использовать top 25% чтобы получить следующий диапазон:

 select @range2 = max(modelpropvalue) from ( select top 25% modelpropvalue from xmodelpropertyvalues where modelPropUnitDescriptionID = @idMenu where modelpropvalue > @range1 order by modelpropvalue ) x 

И так далее с 33.3333% и 50% для третьего и четвертого диапазонов.

Обратите внимание, что для получения правильного счета вы не должны использовать between . Поскольку начальное и конечное значения являются включительными, вы будете подсчитывать значения границ как для диапазона, где он заканчивается, так и для диапазона, в котором он начинается.

 select t.range as myRange, count(*) as myCount from ( select case when modelpropvalue <= @range1 then 'range1' when modelpropvalue <= @range2 then 'range2' when modelpropvalue <= @range3 then 'range3' when modelpropvalue <= @range4 then 'range4' else 'range5' end as range from xmodelpropertyvalues where [email protected]) t group by t.range order by t.range 

(Ну, на самом деле case защитит вас в этом случае, поскольку он выберет первое совпадение, но если вы будете считать их индивидуально, вы будете считать несколько записей дважды. Чтобы получить один диапазон, вы исключили бы первое значение и включили второе: where modelpropvalue > @range1 and modelpropvalue <= @range2 .)

Хорошо, я получил эту работу, используя оба подхода, но проблема, похоже, связана с моими данными. В определенных областях у меня есть огромное количество записей с той же ценностью на поле, которое я запрашиваю (это вес грузовиков и одна конкретная модель чрезвычайно популярны), поэтому нет никакого способа разделить это равномерно!

Я решил вернуться к исходной хранимой процедуре, которая рассчитала диапазоны, просто разделив минимальное и максимальное значение на 5, так как оно прошло намного быстрее. Однако, поскольку этот SP запускается до 8 раз на странице (для веса, топливной емкости, размера двигателя и т. Д.), Я мог бы сделать это с ускорением, так как для запуска требуется около 0,5 секунды. Вот полный СП – любые идеи о том, как я могу оптимизировать его скорость, будут очень благодарны …

 ALTER PROCEDURE [dbo].[sp_GetRangeCounts] @idMenu int, @myFilters varchar(5000), @myStore int, @myLabel varchar(50) OUTPUT, @myUnit varchar(50) OUTPUT, @range1 int OUTPUT, @range2 int OUTPUT, @range3 int OUTPUT, @range4 int OUTPUT, @range5 int OUTPUT, @range6 int OUTPUT, @range1count int OUTPUT, @range2count int OUTPUT, @range3count int OUTPUT, @range4count int OUTPUT, @range5count int OUTPUT AS declare @myMin int, @myMax int, @myDif int declare @myInfoTable table( myMin integer, myMax integer, myLabel varchar(50), myUnit varchar(50) ) insert @myInfoTable (myMin,myMax,myLabel,myUnit) exec('SELECT Min(ConvertedValue) as myMin, Max(ConvertedValue) as myMax,unitDescriptionTrans as myLabel,unitUnitTransDescription as myUnit FROM LenPropValsView where UnitDescriptionID='[email protected]+' and xStoreID='[email protected][email protected]+' group by unitdescriptionTrans,unitUnitTransDescription') select @myMin=myMin-1 from @myInfoTable select @myMax=myMax+1 from @myInfoTable select @myLabel=myLabel from @myInfoTable select @myUnit=myUnit from @myInfoTable set @myDif=(@[email protected])/5 set @[email protected] set @[email protected][email protected] set @[email protected][email protected] set @[email protected][email protected] set @[email protected][email protected] set @[email protected] select @myLabel,@myUnit,@myMin,@myMax,@myDif,@range1,@range2,@range3,@range4,@range5,@range6 declare @myData table( myRange varchar(50), myCount integer ) insert @myData(myRange,myCount) exec ('select t.range as myRange, count(*) as myCount from ( select case when ConvertedValue <='[email protected]+' then ''range1'' when ConvertedValue >'[email protected]+' and ConvertedValue<='[email protected]+' then ''range2'' when ConvertedValue >'[email protected]+' and ConvertedValue<='[email protected]+' then ''range3'' when ConvertedValue >'[email protected]+' and ConvertedValue<='[email protected]+' then ''range4'' else ''range5'' end as range from LenPropValsView where unitDescriptionID='[email protected]+' and xStoreID='[email protected][email protected]+') t group by t.range order by t.range') select @range1count=myCount from @myData where myRange='range1' select @range2count=myCount from @myData where myRange='range2' select @range3count=myCount from @myData where myRange='range3' select @range4count=myCount from @myData where myRange='range4' select @range5count=myCount from @myData where myRange='range5' select @range1count,@range2count,@range3count,@range4count,@range5count 
Interesting Posts
Давайте будем гением компьютера.