T-SQL e decili

mercoledì 27 gennaio 2010 - 10.44

volperubbia Profilo | Senior Member

Ciao a tutti,
vorrei segmentare il risultato di una select in 10 decili ... ovvero un group by
su un group by.

SELECT
...
...
SUM(VALORE)
INTO #TMP
FROM TBL
GROUP BY ...
ORDER BY SUM(VALORE) DESC

Al momento metto tutto in una tabella temporanea, vedo quante righe ha
e poi, ad ogni passo, lavoro sulle prime 10 e le elimino ...

; WITH TMP AS (
SELECT TOP(@COUNT) * FROM #TMP
)
SELECT ...

DELETE FROM #TMP

Altri modi, magari più puliti, per ottenere lo stesso risultato?

Grazie per l'attenzione,
Davide

alx_81 Profilo | Guru

>Ciao a tutti,
ciao!

>vorrei segmentare il risultato di una select in 10 decili ...
se hai sql server 2005 o successive potresti usare la NTILE:
http://msdn.microsoft.com/en-us/library/ms175126%28SQL.90%29.aspx

Qui un post che cerca di risolvere lo stesso problema:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/5b6495d0-9774-463d-b6a6-07b68028f1a5

>Grazie per l'attenzione,
di nulla!

--

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://www.alessandroalpi.net
http://blogs.dotnethell.it/suxstellino
http://mvp.support.microsoft.com/profile/Alessandro.Alpi

volperubbia Profilo | Senior Member

Ciao Alessandro ,
grazie mille, penso possa tornarmi utile ... c'è ancora però qualcosa che non mi torna.
Ti faccio un esempio.

Il mio GROUP BY "originale" torna 823 righe e quindi mi aspetterei:
82 + 82 + 82 + 82 + 82 + 82 + 82 + 82 + 82 + 85.

Invece ottengo:
83 + 83 + 83 + 82 + 82 + 82 + 82 + 82 + 82 + 82.

In pratica mi spalme i 3 che avanzano sui primi decili, anzichè
sull'ultimo (come avevo realizzato nell'altro modo).

Bisogna capire se c'è la possibilità di impostare questa possibilità,
... hai qualche idea?

Davide

alx_81 Profilo | Guru

>Bisogna capire se c'è la possibilità di impostare questa possibilità,
>... hai qualche idea?
Puoi postarmi la tabella di esempio con qualche dato e la query che lanci?

--

Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT

http://www.alessandroalpi.net
http://blogs.dotnethell.it/suxstellino
http://mvp.support.microsoft.com/profile/Alessandro.Alpi

volperubbia Profilo | Senior Member

Ciao Alessandro ...
eccoti due alternative, ho ridotto all'osso il tutto (#INPUT la creo per l'esempio, ma
va bene qualsiasi altra tabella che hai).

; WITH TMP AS (
SELECT 1 AS [ID], 823 AS [Value]
UNION ALL
SELECT ([ID] + 1), [Value] - 1 FROM TMP WHERE ([ID] + 1) < 824
)
SELECT * INTO #INPUT FROM TMP
OPTION (MAXRECURSION 824)

-- PRIMA POSSIBILITA'
; WITH TMP AS (
SELECT [ID], [Value] FROM #INPUT
), TMP_DECILE AS (
SELECT
NTILE (10) OVER (ORDER BY [Value] DESC) AS [Decile]
FROM TMP
)
SELECT [Decile], COUNT(*) AS [Rows] FROM TMP_DECILE
GROUP BY [Decile]
ORDER BY [Decile]

-- SECONDA POSSIBILITA'
; WITH TMP AS (
SELECT [ID], [Value] FROM #INPUT
), TMP_DECILE AS (
SELECT
[ID],
CEILING(ROW_NUMBER() OVER (ORDER BY [Value] DESC) /
((SELECT COUNT(*) + 1 FROM TMP) / 10.00) ) AS [Decile]
FROM TMP
)
SELECT
[Decile],
COUNT(*) AS [Rows]
FROM TMP_DECILE
GROUP BY [Decile]
ORDER BY [Decile]

DROP TABLE #INPUT

Nessuna delle due alternative mi restituisce quello che vorrei.

Su http://msdn.microsoft.com/it-it/library/ms175126(SQL.90).aspx,
c'è questa osservazione (che poi è quello che fa):
> Se il numero di righe in una partizione non è divisibile per integer_expression,
> verranno creati gruppi di due dimensioni che differiscono per un membro.
> I gruppi più grandi precedono i gruppi più piccoli nell'ordine specificato dalla clausola OVER.
> Se ad esempio il numero totale di righe è 53 e il numero di gruppi è 5, i primi 3 gruppi
> includeranno 11 righe e i 2 gruppi rimanenti 10 righe ognuno.
> Se invece il numero totale delle righe è divisibile per il numero di gruppi,
> le righe verranno distribuite uniformemente tra di essi.
> Se ad esempio il numero totale di righe è 50 e sono disponibili 5 gruppi,
> ogni gruppo conterrà 10 righe.

Davide
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5