Creare una subquery in mysql

[ruttan]

Ciao a tutti,

ho un problema, ho questa tabella mysql:

Codice: Seleziona tutto

|col1         |id  |
--------------------
|_22_1_565_18_|1   |
|_22_1_18_    |2   |
|_77_18_      |3   |
|_22_1_55_45  |4   |
|_18_1_65_13_ |5   |
|_782_8_      |6   |
|_782_1_8_21_ |7   |
|_72_1_8_21_  |8   |
|_782_8_251_  |9   |
|_22_4_1_     |10  |
|_77_1_5_21_  |11  |
|_5_6_7_      |12  |

col1 contiene sempre numeri separati dal carattere "_" (uno prima e uno dopo come potete vedere). Devo scrivere una query che:

1) Trovi tutte le row che contengono "_18_" (nell'esempio sarebbero le row 1, 2, 3 e 5).

2) Trovi le 2 sequenze più frequenti nelle row contenenti "_18_" (nell'esempio "_22_" e "_1_").

3) Scelga tutte le row che, allo stesso tempo, non contengano "_18_" ma contengano le precedentemente individuate stringhe più frequenti "_22_" e "_1_" (nell'esempio le row 4 e 10).

4) Aggiunga alla scelta appena effettuata le 2 row precedenti e le 2 row successive a quelle individuate nello step 3, sempre escludendo quelle che contengono "_18_" (nell'esempio le row 6, 8, 9, 11 e 12).

Il risultato finale, nel presente caso dovrebbe dare le rows 4, 10, 6, 8, 9 11 e 12.

So che è incasinato ma devo agire in questo modo (inoltre non posso alterare i dati nel db).
Ringrazio in anticipo tutti quelli che risponderanno.

[Sir Xiradorn]

Iniziamo dal primo punto e vediamo se funge mano a mano quello che ti serve.
Usiamo le regex che ci possono venire in aiuto

Codice: Seleziona tutto

SELECT * FROM `tua_tabella` WHERE `col` REGEXP '_18_' 

Poi devi usare solo mysql o anche tipo php?

[ruttan]

Si per questo io usavo:

SELECT * FROM tabella WHERE col1 LIKE "%_18_%"

Devo usare solo mysql. La parte dopo?

[Sir Xiradorn]

Beh sia % che _ sono due caratteri speciali nella ricerca e non vorrei che ti potesse dare conflittualità quando usi il LIKE. Questi due significano rispettivamente:
% Caratteri generici
_ Esattamente un carattere

Quindi non vorrei ti creasse problemi. In ogni caso la quesry corretta è fatta tramite l'escape se vuoi usare il LIKE

Codice: Seleziona tutto

SELECT * FROM tabella WHERE col1 LIKE "%\_18\_%" 

Affrontiamo il secondo punto. Farlo solo con mysql non è per nulla banale. Ma credo che si possa usare una meccanica similare. Io non gli ho mai usato ne l'una ne l'altra cosa e sinceramente solo con mysql è la prima volta che mi imbatto nella cosa. Quindi proveremo insieme a tirarne fuori qualcosa.

Le mie scarse reminiscenze mi dicono che in MYSQL esistono anche i loop o cicli che dir si voglia. Quello che mi viene in mente e quello di fare una analisi per blocchi.
I passi sarebbero questi. Fare una prima query di estrazione dei blocchi col _18_ e da questa una sub query in cui si definisce il LOOP dove tramite regex o comparing si fa a comparazione. O forse si potrebbe fare anche la cosa inversa. Si comparano le stringe e la sotto query filtra poi quelle con _18_

Questa seconda forse è più semplice da fare ma richiede più risorse. Certo che per 10 o 20 campi parla di risorse è stupido ma comunque è bene sempre averne conto

Davvero una cosa non banale. Da questa verrà il punto 3

[ruttan]

Questo è solo una table di esempio in realtà si tratta di analizzare circa 50.000 rows (fortunatamente offline)

[Sir Xiradorn]

Ma non puoi dare in pasto la tabella a php? E' più semplice gestirla e controllarla e filtrarla. Faresti prima

[Micogian]

E' una query complessa, in particolare sono le precedenti 2 righe e le successive che sono l'ostacolo maggiore.
Comunque ha ragione Sir, in php si può fare, anche se bisogna operare in più query e la cosa non è comunque semplice.

[Sir Xiradorn]

Aggiungo che non deve essere per forza php eh. Noi lo usiamo qua ma tale procedura puo essere adattata a jsp come c++ come qualsiasi altra procedura usabile da qualsiasi linguaggio interfacciabile al database

[ruttan]

Ti ringrazio, hai ragione è meglio se faccio più query, scusa per il ritardo nella risposta.

[Sir Xiradorn]

Una soluzione in php sarebbero le query buffered. In soatanza fai una query e dici aspetta prima di finire fammi pure st'altra cosa.

Comunque per questo dovresti creare una procedura di LOOP. Ci sta qualche tutorial di sucuro per farlo nativamente in mysql. Personalmente la vedo come una soluzione mooooooolto scomoda e molto più complicata.

Un ottima spiegazione delle stored procedure sta qua: http://www.html.it/pag/32156/stored-pro ... functions/ e provvedo a leggera anche io appena ho tempo è un buono spunto di studio di questa cosa.

In ogni caso quello che dovrai fare è un po questo (non stare a guardate i tag perchè anche io non sono molto esperto)
- Crea procedura
- facciamo un primo filtraggio dove elimini tutte le colonne che non contengono il valore desiderato (e questa è la prima query già fatta)
- facciamo un secondo filtraggio ma questa volta hai una infinità di possibilità.
-- una possibile soluzione potrebbe essere questa provando a capire come risparmia risorse senno ti si impalla tutto
--- bisogna vedere se si può fare una sotto query che taglia tutti i valori selezionando il numero del primo spazio delimitato da _NUMERO_ (es _18_24_36_ mi darà _18_ e quindi 18 e questo ripetuto per tutte le righe)
--- poi si potrebbe usare while e contare tutti i valori estratti e esaminare quale ha un conteggio maggiore (quello cioè con maggio frequenza)
---- ricorsivamente lo si ripete con il primo + il secondo numero, così da vedere se ci sono sub strutture che si ripetono e quindi se ci sono valori frequenti. Se gli trova bene, altrimenti il counter avrà zero ovvero un bel false

* devi quindi usare i cursori mysql per usarli a mo di puntatore al valore del db e trovare quelli a cui i tuoi numeri fanno riferimento. ( cervello -> boooooom )

Non so se ho reso l'idea. E' una procedura davvero complicata e se hai bisogno di delucidazioni, chiedi pure.

Se poi puoi usare altri linguaggi a supporto meglio