Utilizzo di SQL per determinare le statistiche di conteggio parole di un campo di testo

Recentemente ho lavorato su alcune funzionalità di ricerca nel database e volevo ottenere alcune informazioni come le parole medie per documento (ad es. Campo di testo nel database). L’unica cosa che ho trovato finora (senza l’elaborazione in linguaggio di scelta al di fuori del DB) è:

SELECT AVG(LENGTH(content) - LENGTH(REPLACE(content, ' ', '')) + 1) FROM documents 

Questo sembra funzionare * ma hai altri suggerimenti? Attualmente sto usando MySQL 4 (spero di passare presto alla versione 5 per questa app), ma sono anche interessato a soluzioni generali.

Grazie!

* Posso immaginare che questo sia un modo abbastanza approssimativo per determinarlo poiché non tiene conto dell’HTML nel contenuto e simili. Va bene per questo particolare progetto, ma ancora ci sono modi migliori?

Aggiornamento: per definire cosa intendo per “migliore”: più preciso, più efficiente, più “corretto” (facile manutenzione, buona pratica, ecc.). Per il contenuto che ho a disposizione, la query sopra è abbastanza veloce ed è accurata per questo progetto, ma potrei aver bisogno di qualcosa di simile in futuro (così ho chiesto).

Le funzionalità di gestione del testo di MySQL non sono abbastanza buone per quello che vuoi. Una funzione memorizzata è un’opzione, ma probabilmente sarà lenta. La soluzione migliore per elaborare i dati in MySQL è aggiungere una funzione definita dall’utente . Se hai intenzione di creare comunque una nuova versione di MySQL, potresti anche aggiungere una funzione nativa .

Il modo “corretto” consiste nell’elaborare i dati al di fuori del DB poiché i DB sono destinati alla memorizzazione, non all’elaborazione e qualsiasi elaborazione pesante potrebbe sovraccaricare il DBMS. Inoltre, il calcolo del conteggio delle parole al di fuori di MySQL semplifica la modifica della definizione di ciò che conta come parola. Che ne dici di memorizzare il conteggio delle parole nel DB e aggiornarlo quando un documento viene modificato?

Esempio di funzione memorizzata:

 DELIMITER $$ CREATE FUNCTION wordcount(str LONGTEXT) RETURNS INT DETERMINISTIC SQL SECURITY INVOKER NO SQL BEGIN DECLARE wordCnt, idx, maxIdx INT DEFAULT 0; DECLARE currChar, prevChar BOOL DEFAULT 0; SET maxIdx=char_length(str); SET idx = 1; WHILE idx <= maxIdx DO SET currChar=SUBSTRING(str, idx, 1) RLIKE '[[:alnum:]]'; IF NOT prevChar AND currChar THEN SET wordCnt=wordCnt+1; END IF; SET prevChar=currChar; SET idx=idx+1; END WHILE; RETURN wordCnt; END $$ DELIMITER ; 

Questo è un po ‘più veloce, anche se solo leggermente meno accurato. Ho trovato il 4% di luce sul conteggio, che è OK per gli scenari di “stima”.

 SELECT ROUND ( ( CHAR_LENGTH(content) - CHAR_LENGTH(REPLACE (content, " ", "")) ) / CHAR_LENGTH(" ") ) AS count FROM documents 

Puoi usare l’UDF word_count() da https://github.com/spachev/mysql_udf_bundle . Ho portato la logica dalla risposta accettata con una differenza che il mio codice supporta solo il set di caratteri latin1. La logica dovrebbe essere rielaborata per supportare altri set di caratteri. Inoltre, entrambe le implementazioni considerano sempre un carattere non alfanumerico un delimitatore, che può non essere sempre auspicabile – ad esempio “libro dell’insegnante” è considerato come tre parole da entrambe le implementazioni.

La versione UDF è, ovviamente, molto più veloce. Per un test rapido ho provato entrambi su un set di dati di Project Guttenberg composto da 9751 record per un totale di circa 3 GB. L’UDF li ha fatti tutti in 18 secondi, mentre la funzione memorizzata impiega 63 secondi per elaborare solo 30 record (che l’UDF fa in 0,05 secondi). Quindi l’UDF è circa 1000 volte più veloce in questo caso.

UDF batterà qualsiasi altro metodo in velocità che non comporti la modifica del codice sorgente di MySQL. Questo perché ha accesso ai byte della stringa in memoria e può operare direttamente sui byte senza che debbano essere spostati. È anche compilato in codice macchina e viene eseguito direttamente sulla CPU.