MySQL: e ‘ possibile per ‘riempire’ una selezione con i valori senza una tabella?

Ho bisogno di visualizzare il totale di ‘ordini’ per ogni anno e per mese. Ma per alcuni mesi non ci sono dati, ma voglio visualizzare il mese (per un valore totale di zero). Potrei fare un helpertable ‘mesi’ con 12 record per ogni anno, ma c’è forse un modo per ottenere un intervallo di mesi, senza l’introduzione di una nuova tabella?

Qualcosa di simile:

SELECT [all year-month combinations between january 2000 and march 2011] 
FROM DUAL AS years_months

Qualcuno ha un idea di come fare questo? È possibile utilizzare SELEZIONARE con un qualche tipo di formula, di “creare” dati “al volo”?!

AGGIORNAMENTO:

Trovato questo di me:
generare giorni dalla data di gamma

Accettato di rispondere a questa domanda è un po quello che sto cercando. Forse non è il metodo più semplice, ma fa quello che voglio: riempire una selezione con i dati, sulla base di una formula….

Di “creare” un tavolo sul fly con tutti i mesi degli ultimi 10 anni:

SELECT CONCAT(MONTHNAME(datetime), ' ' , YEAR(datetime)) AS YearMonth,
       MONTH(datetime) AS Month,
       YEAR(datetime) AS Year 
FROM (
    select (curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH) as datetime
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    LIMIT 120
) AS t
ORDER BY datetime ASC 

Devo ammettere, questo è MOLTO esotico, ma funziona…

Posso usare questa unione con il mio ‘ordini’-tabella e ottenere i totali per ogni mese, anche quando non c’è nessun dato in un certo mese.

Ma usando un ‘numeri’ o ‘calendario’ la tabella è probabilmente l’opzione migliore, così ho intenzione di usare quella.

In SQL Server è possibile utilizzare la Cte ricorsiva o cross join Cte per fare questo. Suppongo che si potrebbe selezionare da un numero sufficientemente grande tavolo e utilizzare variabili utente.
Puoi dare qualche informazione in più su tavola(s) ?
Fondamentalmente c’è solo 1 tabella : ordini, che contiene un data-campo. Ora ho bisogno di un elenco di quanti ordini ci sono in ogni mese a partire da gennaio 2000. Ma per alcuni mesi, non ci sono gli ordini a tutti. Ma io voglio questi mesi per mostrare l’elenco. Quindi ho bisogno di una seconda tabella, che contiene tutti i mesi da gennaio 2000, e quindi partecipare a questo con gli ordini-tabella.

OriginaleL’autore Dylan | 2011-03-26

8 Replies
  1. 5

    Se possibile, cercare di stare lontano da generare dati al volo. Rende molto semplice query ridiculusly complesso, ma soprattutto: si confonde l’ottimizzatore a non finire.

    Se avete bisogno di una serie di numeri interi, utilizzare una tabella statica di numeri interi. Se avete bisogno di una serie di date, mesi o quello che è, utilizzare una tabella di calendario. A meno che non hai a che fare con alcuni di veramente straordinario requisiti, una tabella statica è la strada da percorrere.

    Ho dato un esempio su come creare una tabella di numeri e di un minimo di tabella di calendario(data) in questa risposta.

    Se si dispone di tali tabelle, diventa facile per risolvere la query.

    1. Aggregare i dati per ordine di un MESE.
    2. Right join alla tabella di mesi (o distinti MESE dalla tabella di date)
    Sono d’accordo che la mia “soluzione” è molto complesso… io penso di utilizzare un ‘numeri’ o ‘calendario’ tavolo quindi. Molto strano però che SQL dopo tutti questi anni non è in grado di creare una semplice serie di dati.

    OriginaleL’autore Ronnis

  2. 4

    Si può provare qualcosa di simile a questo

    select * from 
    (selezionare il 2000 come anno di unione 
    selezionare il 2001 come anno dell'unione 
    selezionare 2009 
    ) anni, 
    (selezionare 1 mese unione 
    selezionare 2 come mese dell'unione 
    selezionare 3 mese unione 
    selezionare 4 mese unione 
    selezionare 5 mese unione 
    selezionare 6 mese unione 
    selezionare 7 mese unione 
    selezionare 8 mese unione 
    selezionare 9 mese 
    ) 
    
    AS months 
    WHERE year between 2001 AND 2008 OR (year=2000 and month>0) OR (year = 2009 AND month < 4) 
    ORDER by year,month

    OriginaleL’autore danidacar

  3. 2

    Si può solo riempire i mesi mancanti dopo aver fatto la query nella logica dell’applicazione.

    OriginaleL’autore Matthew

  4. 1

    Si dovrebbe sicuramente fare questo nell’applicazione anziché il DB livello. Basta creare un array di date per l’intervallo di tempo, e unire i dati effettivi con il vuoto date che hai creato in precedenza. Vedere questa risposta a una domanda simile

    Io non sono d’accordo… io penso che è molto più pulito per fare questo in SQL (se possibile). Io sto usando un javascript standard griglia che prende dei dati da un normale script php che esegue la query e produce dati in formato JSON. Non ho intenzione di scrivere uno script php separato per il recupero dei dati per ogni query nella mia applicazione (ci sono centinaia di query nella mia applicazione)
    Perché avete bisogno di scrivere uno script separato per ogni query?
    Stavo esagerando. Stavo solo dicendo che non credo che è pulito usare PHP (o qualche altro tipo di serverside scripting) per manipolare i dati che si desidera visualizzare.
    Ma avete intenzione di utilizzare gli script langue, comunque, per eseguire la query, JSON codificare, e per produrre i risultati. A ciascuno il suo, ma nel DB livello è inutile come la costruzione di HTML tramite query SQL.
    Non è inutile a tutti. Nella mia soluzione posso usare lo stesso script più e più volte per visualizzare i dati. L’unica cosa che è diverso ogni volta che è il database, query, che ho memorizzare in un file separato.

    OriginaleL’autore code_burgar

  5. 1

    Se stai usando PostgreSQL, è possibile combinare entrambi date_trunc e generate_series per fare alcuni molto divertente il raggruppamento e la generazione di serie.

    Per esempio, si potrebbe usare questo per generare una tabella con tutte le date dell’ultimo anno:

    SELECT current_date - s.a as date 
    FROM generate_series(0,365,1) as s(a);

    Quindi, si potrebbe utilizzare date_trunc per afferrare i mesi e il gruppo che date_trunc autenticata campo:

    SELECT date(date_trunc('month', series.date)) as month, COUNT(*) as days 
    FROM (SELECT current_date - s.a as date 
    FROM generate_series(0,365,1) as s(a)) series 
    GROUP BY month;

    OriginaleL’autore Sia

  6. 0

    Creare una tabella (ad esempio tblMonths) che comprende tutti i 12 mesi e utilizzare un LEFT JOIN (o RIGHT JOIN) su di esso e il parziale di dati di origine.

    Check out il riferimento e questo tutorial per come funziona.

    Thanx, io so come fare join. Volevo solo sapere se è possibile in SQL per ‘creare’ dei dati in tempo reale utilizzando una formula, ma a quanto pare questo non è possibile. Strano…
    Certo, è utilizzare la tabella fittizia dual per creare i dati di utilizzo di formule, ma io non sono convinto che il modo più semplice per risolvere questo.

    OriginaleL’autore D.N.

  7. 0

    Vorrei fare qualcosa di simile a questo:

    SELECT COUNT(Order.OrderID)
    FROM Orders
    WHERE YEAR(Order.DateOrdered) > 2000
    GROUP BY MONTH(Order.DateOrdered)

    Questo vi darà il numero di ordini raggruppati per mese.
    Poi si domanda semplicemente assegnare un valore nullo per i mesi in cui non ci sono dati restituiti

    Spero che questo Aiuta

    Questo non funziona quando non c’è i dati in un determinato mese. Che mese non verrà visualizzata.
    So che è per questo che ho detto “si domanda semplicemente assegnare un valore nullo per i mesi in cui non ci sono dati restituiti”. IMO che è più pulita.

    OriginaleL’autore Jonny

  8. 0

    Faccio seguente query per generare mesi in un dato intervallo di tempo. Per il mio caso è di generare la lista di mese iniziato da maggio 2013 fino ad ora.

    SELECT date_format(@dt:= DATE_ADD( @dt, INTERVAL 1 MONTH),'%M %Y') date_string, 
       @dt as date_full 
    FROM (SELECT @dt := DATE_SUB(CAST(DATE_FORMAT('2013-05-01' ,'%Y-%m-01') AS DATE),
       INTERVAL 1 MONTH) ) vars,
       your_tables 
    WHERE @dt<NOW()

    La preoccupazione è, dovrebbe essere unito con la tabella contenente sufficiente righe per la fornitura di numero di mesi previsti. E. g. se avete bisogno di generare tutti i mesi in un anno particolare, avrete bisogno di un tavoli composti da un minimo di 12 righe.

    Per me è un po ‘ dritto in avanti. Ho iniziato io con la mia tabella di configurazione, composto da circa 370 righe. Così potrebbe generare mesi in un anno, o giorni in un anno se ne ho bisogno. Che cambia dal mese di intervallo in giorni di intervallo sarebbe facile, in quanto mi serve solo per modificare l’intervallo di MESE in GIORNO.

    OriginaleL’autore rama3i

Lascia un commento