Domanda di progettazione logica per query SQL

Question

Domanda di progettazione logica per query SQL

#1 da (4 voti)
#2 da (0 voti)

4

Mi stavo chiedendo se potessi aiutarmi su un problema SQL che sto riscontrando.

Ho una serie di record di eventi in cui ogni evento ha un'ora di inizio e un'ora di fine. Nessun evento ha la stessa ora di inizio e l'ora di fine di ogni evento è maggiore o uguale all'evento precedente.

Quello che voglio essere in grado di fare è estrarre il set di record che non si sovrappongono - dove l'ora di inizio di un evento non è tra l'inizio / la fine di qualsiasi altro evento. Nell'esempio seguente, il tempo di inizio per B / C / D è tra l'inizio e la fine di A, quindi voglio mantenere A e sbarazzarmi di B, C, D. Quindi ricomincia da capo, mantenendo E, e sbarazzati di F, quindi ricomincia e mantieni G, sbarazzandosi di H / I, ecc.

Sembra che questo richiede un auto join ma non riesco a capire i criteri per identificare i record in questo modo. Qualche idea?

Event |  Start  | End
A             1            10 (keep)
B             2            11 (discard because start time of 2 < 10)
C             5            15 (discard because start time of 5 < 10)
D             9            12 (discard because start time of 9 < 10)
E             10           13 (keep -  this starts a new set because it’s the first record after A with start >= A end)
F             11           20 (discard because 11 < 13)
G             14           22 (keep -  this starts a new set because it’s the first record after E with start >= E)
H             15           22 (discard because 15 < 22)
I             17           27 (discard because 17 < 22)
J             22           27 (keep -  this starts a new set because it’s the first record after G with start >= G)

design sql database logic

posta Matt Pierce 27.08.2013 - 04:52

fonte

2 risposte

Leggi altre domande sui tag design sql database logic

Python GUI e MVC: ottieni la variabile da un altro modulo Test dell'unità di JavaScript - Stiamo testando la logica del codice o il comportamento di un browser

score 4 · Answer 1

A seconda della piattaforma, questo può o non può essere possibile in una singola query sql / senza l'uso di una stored procedure.

Questo problema equivale in modo efficace all'albero trasversale all'interno di una tabella con riferimenti principali. Puoi costruire la struttura ad albero attraverso un self join usando una query come questa:

SELECT 
    b. *,
    a.event as parent_event,
    a.start as parent_start,
    a.end as parent_end
FROM
    interval_test a
        join
    interval_test b ON b.start >= a.end
order by parent_start, start

Produrre un risultato simile a questo:

Nota:ilrisultatovienetroncatoperrisparmiarespazio,masiottienel'idea...

Ciascunodeipossibilisuccessoridiunevento(quelliconunend>=iniziodelpredecessore)puòesserepensatocomeunbambinonell'albero.Sivuoletrovareilpercorsodell'alberolungol'alberocheriducealminimoiltempodiavvioadognipassaggio.

Puoifarequestotipodiattraversamentodeglialberiusando ricorsivo query in SQL Server o utilizzando una query gerarchica in Oracle . Questo tipo di ricorsione non è supportato in MySQL e dovrai utilizzare una stored procedure se questa è la tua piattaforma.

Ad esempio, in SQL Server, la seguente query funzionerebbe:

        WITH min_path AS
          (SELECT RANK() OVER (ORDER BY a.start ASC) AS [rank], cast(NULL as CHAR(10)) as parent_event, 
               NULL as parent_start, NULL as parent_end, a.event, a.start, a.[end]
           FROM dbo.interval_test a
           WHERE a.start=
               (SELECT MIN(START)
                FROM dbo.interval_test)
           UNION ALL SELECT RANK() OVER (ORDER BY c.start ASC) AS [rank], b.event as parent_event, 
               b.start as parent_start, b.[end] as parent_end, c.event, c.start, c.[end]
           FROM min_path b
           JOIN dbo.interval_test c ON c.start>= b.[end]
           WHERE [rank]=1)
        SELECT event, start, [end] from min_path where [rank]=1

Produzione del seguente risultato:

Nota, ci sono probabilmente modi più semplici per scrivere la query sopra per SQL Server.

Dal punto di vista delle prestazioni, quando si lavora in un ambiente con potenzialmente miliardi di righe come menzionato nei commenti sopra, non ho un'idea chiara di come questo potrebbe funzionare. Se la query è limitata a un piccolo numero di eventi relativi a un singolo utente, potrebbe andare bene. Cercare di trovare l'intera serie di eventi rilevanti non sovrapposti probabilmente non funzionerebbe bene e potresti stare meglio usando un approccio procedurale.

score 0 · Answer 2

Qualcosa di simile è molto semplice in una stored procedure in cui si esegue il loop dei record, ma molto difficile in SQL semplice, perché concettualmente in SQL i record non sanno nulla di loro. Quindi basta scorrere i record e scartare quelli che non ti servono.