none
Script SQL Problema di Logica sul TOP RRS feed

  • 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 Novembre

     

    Oppure 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 Dicembre

     

    Come è 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.

    giovedì 27 gennaio 2011 09:57

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

    giovedì 27 gennaio 2011 23:12
    Moderatore

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

    giovedì 27 gennaio 2011 23:12
    Moderatore
  • 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

    lunedì 31 gennaio 2011 08:52