Principale utente con più risposte
Script SQL Problema di Logica sul TOP

Domanda
-
Salve a tutti,
ho un problema nello script di sql in quanto presenta un comportamento davvero insolito nel restituirmi il valore.
TABELLA PrintPlanning
[date] [DD Month] FullName Weeknumber
Datetime Varchar(20) Varchar(50) int
….
2010-10-29 29 Ottobre pippo 44
2010-10-30 30 Ottobre pippo 44
2010-10-31 31 Ottobre pippo 44
2010-11-01 01 Novembre pippo 44
2010-11-02 02 Novembre pippo 44
2010-11-03 03 Novembre pippo 44
2010-11-04 04 Novembre pippo 44
2010-11-26 26 Novembre pippo 48
2010-11-27 27 Novembre pippo 48
2010-11-28 28 Novembre pippo 48
2010-11-29 29 Novembre pippo 48
2010-11-30 30 Novembre pippo 48
2010-12-01 01 Dicembre pippo 48
2010-12-02 02 Dicembre pippo 48
….
Il mio problema riguarda il risultato di queste select query
select top(1)[DD Month] from (select top(NNN)date, [DD Month] from PrintPlanning where WeekNumber = 44 and
FullName = 'pippo')pr order by date desc
RIS:
Se (NNN = 1) ottengo ==> 29 Ottobre
Se (NNN = 2) ottengo ==> 30 Ottobre
Se (NNN = 3) ottengo ==> 31 Ottobre
Se (NNN = 4) ottengo ==> 01 Novembre
Se (NNN = 5) ottengo ==> 02 Novembre
Se (NNN = 6) ottengo ==> 04 Novembre Comportamento insolito
Se (NNN = 7) ottengo ==> 04 NovembreOppure questa
select top(1)[DD Month] from (select top(NNN)date, [DD Month] from PrintPlanning where WeekNumber = 48 and
FullName = 'pippo')pr order by date desc
RIS:
Se (NNN = 1) ottengo ==> 26 Novembre
Se (NNN = 2) ottengo ==> 27 Novembre
Se (NNN = 3) ottengo ==> 28 Novembre
Se (NNN = 4) ottengo ==> 29 Novembre
Se (NNN = 5) ottengo ==> 02 Dicembre Comportamento insolito
Se (NNN = 6) ottengo ==> 02 Dicembre Comportamento insolito
Se (NNN = 7) ottengo ==> 02 DicembreCome è possibile questo comportamento che a prima vista sembra cambiare logica di funzionamento senza apparente motivo.
Sono dovvero interessato a capire questa logica
Grazie a tutti, in anticipo, per la vostra collaborazione.
Risposte
-
salve Michael,
il tutto parte da un principio fondamentale alla base della definizione di "tabella", che come ben sai consiste in un "insieme NON ordinato di righe".. con questo presupposto, sai gia' anche che, per ottenere un qualche cosa di ordinato, devi specificare un apposita clausola, ORDER BY... la tua subquery interna NON e' ordinata, quindi ritornera' un result set potenzialmente non definito per quanto riguarda la tua specifica di ricerca... se "n" righe soddisfano la specifica WHERE WeekNumber = x AND FullName = "y", la imposizione TOP(z) prendera' le prime z righe in tale risultato, che non necessariamente saranno ordinate e sono potenzialmente casuali quindi non prevedibili, questo perche', di nuovo, una proiezione NON ordinata e' il risultato di estrazione da una tabella (insieme NON ordinato).. semanticamente significa che non si deve "assolutamente" basarci su risultati ottenuti "casualmente" magari dipendenti da come le righe sono state inserite sequenzialmente o dalla presenza di un indice cluster... l'ordine non e' mai garantito sia il medesimo...
puoi quindi meglio avvalerti di altre funzionalita', le windowing functions, tra le quali ROW_NUMBER() in questo caso ci puo' aiutare... filtrando una subquery o common table expression dove gestirai ad esempio una colonna calcolata sull'ordine a te interessante, filtrando quindi in join la tabella principale su questa subquery/cte, come ad esempio trivialmente di seguito:
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.PrintPlanning ( [date] datetime, [DD Month] varchar(20), FullName varchar(50), Weeknumber int ); GO INSERT INTO dbo.PrintPlanning VALUES ('2010-10-29', '29 Ottobre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-10-30', '30 Ottobre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-10-31', '31 Ottobre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-01', '01 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-02', '02 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-03', '03 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-04', '04 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-26', '26 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-27', '27 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-28', '28 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-29', '29 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-30', '30 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-12-01', '01 Dicembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-12-02', '02 Dicembre', 'pippo', 48); GO DECLARE @nn int = 1; select top(1)[DD Month] from (select top(@nn)date, [DD Month] from PrintPlanning where WeekNumber = 44 and FullName = 'pippo') pr order by date desc PRINT 'e'' sbagliato in quanto la subquery interna corrisponde'; PRINT 'a SELECT TOP 1 FROM xxx; senza alcun ordine e quindi'; PRINT 'proietta la prima riga che incontra... nel caso specifico'; PRINT 'non c''e'' neanche una indice clustered e quindi fa quello che gli pare :)'; SELECT TOP 1 * FROM dbo.PrintPlanning; GO CREATE PROCEDURE dbo.usp_GetBy$WeekNumber$FullName ( @Position int, @WeekNumber int, @FullName varchar(50) ) AS BEGIN WITH cte AS ( SELECT p.[date], p.[DD Month], ROW_NUMBER() OVER (ORDER BY [date]) AS [r] FROM dbo.PrintPlanning p WHERE WeekNumber = @WeekNumber and FullName = @FullName ) SELECT @Position, p.[DD Month] FROM dbo.PrintPlanning p JOIN cte ON cte.[date] = p.[date] AND cte.r = @Position; END; GO EXECUTE dbo.usp_GetBy$WeekNumber$FullName 1, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 2, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 3, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 4, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 5, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 6, 44, 'pippo'; GO DROP PROCEDURE dbo.usp_GetBy$WeekNumber$FullName; DROP TABLE dbo.PrintPlanning; --<----------- DD Month -------------------- 04 Novembre e' sbagliato in quanto la subquery interna corrisponde a SELECT TOP 1 FROM xxx; senza alcun ordine e quindi proietta la prima riga che incontra... nel caso specifico non c'e' neanche una indice clustered e quindi fa quello che gli pare :) date DD Month FullName Weeknumber ----------------------- -------------------- -------------------------------------------------- ----------- 2010-10-29 00:00:00.000 29 Ottobre pippo 44 DD Month ----------- -------------------- 1 29 Ottobre DD Month ----------- -------------------- 2 30 Ottobre DD Month ----------- -------------------- 3 31 Ottobre DD Month ----------- -------------------- 4 01 Novembre DD Month ----------- -------------------- 5 02 Novembre DD Month ----------- -------------------- 6 03 Novembre
vorrei aggiungere una nota off topic nel contesto, ma rilevante nell'esecuzione, relativa al tuo codice...
hai utilizzato, spero solo nell'esempio, il nome di una tabella (PrintPlanning) senza referenziarla con lo schema di appartenenza (dbo.PrintPlanning); come ben sai, cio' implica che i piani di esecuzione della query non saranno riutilizzabili e che ad ogni esecuzione questa dovra' essere ricompilata, disperdendo quindi risorse, tempo e quant'altro..
saluti
http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de- Contrassegnato come risposta Lorenzo BenagliaModerator domenica 30 gennaio 2011 09:57
Tutte le risposte
-
salve Michael,
il tutto parte da un principio fondamentale alla base della definizione di "tabella", che come ben sai consiste in un "insieme NON ordinato di righe".. con questo presupposto, sai gia' anche che, per ottenere un qualche cosa di ordinato, devi specificare un apposita clausola, ORDER BY... la tua subquery interna NON e' ordinata, quindi ritornera' un result set potenzialmente non definito per quanto riguarda la tua specifica di ricerca... se "n" righe soddisfano la specifica WHERE WeekNumber = x AND FullName = "y", la imposizione TOP(z) prendera' le prime z righe in tale risultato, che non necessariamente saranno ordinate e sono potenzialmente casuali quindi non prevedibili, questo perche', di nuovo, una proiezione NON ordinata e' il risultato di estrazione da una tabella (insieme NON ordinato).. semanticamente significa che non si deve "assolutamente" basarci su risultati ottenuti "casualmente" magari dipendenti da come le righe sono state inserite sequenzialmente o dalla presenza di un indice cluster... l'ordine non e' mai garantito sia il medesimo...
puoi quindi meglio avvalerti di altre funzionalita', le windowing functions, tra le quali ROW_NUMBER() in questo caso ci puo' aiutare... filtrando una subquery o common table expression dove gestirai ad esempio una colonna calcolata sull'ordine a te interessante, filtrando quindi in join la tabella principale su questa subquery/cte, come ad esempio trivialmente di seguito:
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.PrintPlanning ( [date] datetime, [DD Month] varchar(20), FullName varchar(50), Weeknumber int ); GO INSERT INTO dbo.PrintPlanning VALUES ('2010-10-29', '29 Ottobre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-10-30', '30 Ottobre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-10-31', '31 Ottobre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-01', '01 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-02', '02 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-03', '03 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-04', '04 Novembre', 'pippo', 44); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-26', '26 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-27', '27 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-28', '28 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-29', '29 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-11-30', '30 Novembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-12-01', '01 Dicembre', 'pippo', 48); INSERT INTO dbo.PrintPlanning VALUES ('2010-12-02', '02 Dicembre', 'pippo', 48); GO DECLARE @nn int = 1; select top(1)[DD Month] from (select top(@nn)date, [DD Month] from PrintPlanning where WeekNumber = 44 and FullName = 'pippo') pr order by date desc PRINT 'e'' sbagliato in quanto la subquery interna corrisponde'; PRINT 'a SELECT TOP 1 FROM xxx; senza alcun ordine e quindi'; PRINT 'proietta la prima riga che incontra... nel caso specifico'; PRINT 'non c''e'' neanche una indice clustered e quindi fa quello che gli pare :)'; SELECT TOP 1 * FROM dbo.PrintPlanning; GO CREATE PROCEDURE dbo.usp_GetBy$WeekNumber$FullName ( @Position int, @WeekNumber int, @FullName varchar(50) ) AS BEGIN WITH cte AS ( SELECT p.[date], p.[DD Month], ROW_NUMBER() OVER (ORDER BY [date]) AS [r] FROM dbo.PrintPlanning p WHERE WeekNumber = @WeekNumber and FullName = @FullName ) SELECT @Position, p.[DD Month] FROM dbo.PrintPlanning p JOIN cte ON cte.[date] = p.[date] AND cte.r = @Position; END; GO EXECUTE dbo.usp_GetBy$WeekNumber$FullName 1, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 2, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 3, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 4, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 5, 44, 'pippo'; EXECUTE dbo.usp_GetBy$WeekNumber$FullName 6, 44, 'pippo'; GO DROP PROCEDURE dbo.usp_GetBy$WeekNumber$FullName; DROP TABLE dbo.PrintPlanning; --<----------- DD Month -------------------- 04 Novembre e' sbagliato in quanto la subquery interna corrisponde a SELECT TOP 1 FROM xxx; senza alcun ordine e quindi proietta la prima riga che incontra... nel caso specifico non c'e' neanche una indice clustered e quindi fa quello che gli pare :) date DD Month FullName Weeknumber ----------------------- -------------------- -------------------------------------------------- ----------- 2010-10-29 00:00:00.000 29 Ottobre pippo 44 DD Month ----------- -------------------- 1 29 Ottobre DD Month ----------- -------------------- 2 30 Ottobre DD Month ----------- -------------------- 3 31 Ottobre DD Month ----------- -------------------- 4 01 Novembre DD Month ----------- -------------------- 5 02 Novembre DD Month ----------- -------------------- 6 03 Novembre
vorrei aggiungere una nota off topic nel contesto, ma rilevante nell'esecuzione, relativa al tuo codice...
hai utilizzato, spero solo nell'esempio, il nome di una tabella (PrintPlanning) senza referenziarla con lo schema di appartenenza (dbo.PrintPlanning); come ben sai, cio' implica che i piani di esecuzione della query non saranno riutilizzabili e che ad ogni esecuzione questa dovra' essere ricompilata, disperdendo quindi risorse, tempo e quant'altro..
saluti
http://www.asql.biz - DbaMgr2k - DbaMgr and further SQL Tools http://www.hotelsole.com/ - http://www.hotelsolericcione.de- Contrassegnato come risposta Lorenzo BenagliaModerator domenica 30 gennaio 2011 09:57
-
salve Andrea,
grazie mille per la risposta più che dettagliata.
Ho applicato il consiglio e funziona perfettamente. In effetti sono abituato ad utilizzare così frequentemente comodità di SQLMS che dimentico i principi come questo realitivo alle tabelle come insieme di dati non ordinati.
Per quanto rigurada dbo. è stata solo una svista :)
Spero che il mio problema e il soprattutto il tuo post possano servire a molti altri.
Grazie ancora, a presto
Saluti, Michael