T-SQL : Une meilleure fonction/quête de distribution glissante

Question

J'ai besoin d'une approche de classement T-SQL similaire à celle fournie par NTILE(), sauf que les membres de chaque tuile seraient sur une distribution glissante de sorte que les tuiles de rang supérieur ont moins de membres.

Par exemple

CREATE TABLE #Rank_Table(
id int identity(1,1) not null,
hits bigint not null default 0,
PERCENTILE smallint null
)
--Slant the distribution of the data
INSERT INTO #Rank_Table (hits)
select CASE 
  when DATA > 9500 THEN DATA*30
  WHEN data > 8000  THEN DATA*5 
  WHEN data < 7000  THEN DATA/3 +1
  ELSE DATA
 END
FROM
 (select top 10000 (ABS(CHECKSUM(NewId())) % 99 +1) * (ABS(CHECKSUM(NewId())) % 99 +1 ) DATA
 from master..spt_values t1
  cross JOIN master..spt_values t2) exponential

Declare @hitsPerGroup as bigint
Declare @numGroups as smallint
set @numGroups=100

select @hitsPerGroup=SUM(hits)/(@numGroups -1) FROM #Rank_Table 

select @hitsPerGroup HITS_PER_GROUP

--This is an even distribution
SELECT  id,HITS, NTILE(@numGroups) Over (Order By HITS DESC) PERCENTILE 
FROM #Rank_Table 
GROUP by id, HITS

--This is my best attempt, but it skips groups because of the erratic distribution
select 
    T1.ID, 
    T1.hits, 
    T.RunningTotal/@hitsPerGroup + 1 TILE,
    T.RunningTotal
FROM    #Rank_Table T1
        CROSS APPLY ( Select SUM(hits) RunningTotal FROM #Rank_Table where hits <= T1.hits) T
order by T1.hits 

DROP TABLE #Rank_Table

Dans #Rank_table, NTILE(@numGroups) crée une distribution égale des groupes @numGroups. Ce dont j'ai besoin, ce sont des groupes @numGroups où la tuile 1 a le moins de membres, la tuile 2 en aurait un ou plus que la tuile 1, la tuile 3 en aurait un ou plus que la tuile 2 ... la tuile 100 en aurait le plus.

J'utilise SQL Server 2008. En pratique, cette opération sera exécutée sur une table permanente comportant potentiellement des millions de lignes afin de mettre à jour périodiquement la colonne PERCENTILE avec son percentile de 1 à 100.

Ma meilleure tentative ci-dessus saute les percentiles et donne de mauvais résultats. Il doit y avoir un meilleur moyen.

Answer 1

Une meilleure mise en œuvre de NTILE ? YMMV

Answer 2

Afin de créer une distribution plus linéaire, j'ai ajouté une colonne calculée au tableau de données, HITS_SQRT HITS_SQRT AS (CONVERT([int],sqrt(HITS*4),(0))) PERSISTED .

En utilisant cette colonne, vous pouvez calculer un nombre cible de "coups par percentile".

select @hitsPerGroup=SUM(HITS_SQRT)/(@numGroups -1)-@numGroups, @dataPoints=COUNT(*) FROM #Rank_Table 

Le script crée ensuite un tableau temporaire avec un ROW_NUMBER() ordonné par le nombre d'occurrences et itère les lignes par ordre décroissant en mettant à jour son percentile de 100 à 1. Un total courant du nombre d'occurrences est conservé, et lorsque la fonction @hitsPerGroup est passé, le percentile est abaissé de 100 à 99, de 99 à 98, etc.

Ensuite, la table de données source est mise à jour avec son percentile. Il y a un index de la table de travail temporaire pour accélérer la mise à jour.

script complet utilisant #Rank_Table comme table de données source.

--Create Test Data
CREATE TABLE #Rank_Table(
id int identity(1,1) not null,
hits bigint not null default 0,
PERCENTILE smallint NULL,
HITS_SQRT  AS (CONVERT([int],sqrt(HITS*4),(0))) PERSISTED
)
--Slant the distribution of the data
INSERT INTO #Rank_Table (hits)
select CASE 
  when DATA > 9500 THEN DATA*30
  WHEN data > 8000  THEN DATA*5 
  WHEN data < 7000  THEN DATA/3 +1
  ELSE DATA
 END
FROM
 (select top 10000 (ABS(CHECKSUM(NewId())) % 99 +1) * (ABS(CHECKSUM(NewId())) % 99 +1 ) DATA
 from master..spt_values t1
  cross JOIN master..spt_values t2) exponential

--Create temp work table and variables to calculate percentiles
    Declare @hitsPerGroup as int
    Declare @numGroups as int
    Declare @dataPoints as int
    set @numGroups=100

    select @hitsPerGroup=SUM(HITS_SQRT)/(@numGroups -1)-@numGroups, @dataPoints=COUNT(*) FROM #Rank_Table 

    --show the number of hits that each group should have
    select @hitsPerGroup HITS_PER_GROUP

    --Use temp table for the calculation
    CREATE TABLE #tbl (
        row int,
        hits int,
        ID bigint,
        PERCENTILE smallint null
    )
    --add index to row
    CREATE CLUSTERED INDEX idxRow ON #tbl(row) 

    insert INTO #tbl
    select ROW_NUMBER() over (ORDER BY HITS), hits_SQRT, ID, null from #Rank_Table

    --Update each row with a running total.
    --lower the percentile by one when we cross a threshold for the maximum number of hits per group (@hitsPerGroup)
    DECLARE @row as int
    DEClare @runningTotal as int
    declare @percentile int
    set @row = 0
    set @runningTotal = 0
    set @percentile = @numGroups

    while @row <= @dataPoints
    BEGIN
        select @runningTotal=@runningTotal + hits from #tbl where row=@row

        if @runningTotal >= @hitsPerGroup
        BEGIN

            update #tbl
            set PERCENTILE=@percentile
            WHERE PERCENTILE is null and row <@row

            set @percentile = @percentile - 1

            set @runningTotal = 0
        END

        --change rows
        set @row = @row + 1
    END

    --get remaining
    update #tbl
    set PERCENTILE=@percentile
    WHERE PERCENTILE is null

    --update source data
    UPDATE m SET PERCENTILE = t.PERCENTILE
    FROM #tbl t
    inner join #Rank_Table m on t.ID=m.ID

--Show the results
    SELECT PERCENTILE, COUNT(id) NUMBER_RECORDS, SUM(HITS) HITS_IN_PERCENTILE 
    FROM #Rank_Table 
    GROUP BY PERCENTILE
    ORDER BY PERCENTILE 

--cleanup
    DROP TABLE #Rank_Table
    DROP TABLE #tbl

Les performances ne sont pas exceptionnelles, mais elles permettent d'atteindre l'objectif d'une distribution glissante régulière.