Implementazione di "Elementi di tendenza" utilizzando la regressione lineare in SQL Server

Naviga le tue risposte
2

Sto costruendo un sito Web MVC .NET in cui le persone possono valutare e recensire i film (simili a IMDb). Sto utilizzando SQL Server per il back-end.

Implementerò una funzione in cui la home page mostra film di tendenza, cioè film che sono stati valutati positivamente in un determinato periodo di tempo (diciamo ultimi 7 giorni).

Userò una procedura memorizzata che implementa la regressione lineare per ottenere ciò. Le mie tabelle SQL rilevanti sono le seguenti:

  • Movie - PK Movie_ID, memorizza le informazioni sul film
  • User - PK ID utente, memorizza le informazioni utente
  • Rating : memorizza le istanze di valutazione sotto forma di:

PK Rating_ID | FK Movie_ID | FK User_ID | RatingNo | Updated_Dt

  1             5            10          7     15/12/2014 16:04:24

La riga precedente verrebbe tradotta in "Utente con ID 10 che ha assegnato un filmato con ID 5 a un punteggio di 7/10 il 15/12/2014 alle 16:04:24".

Ho anche una funzione per calcolare il punteggio medio di un film sotto forma di AVG_Rate = sum of all ratings (for a given movie) / count of ratings (for a given movie) .

La mia domanda è questa: vista la struttura della tabella fornita sopra, quale sarebbe il modo migliore per implementare "i film di tendenza di questa settimana"? Posso pensare a due modi al momento:

  • Crea una tabella permanente per eseguire la regressione lineare su:

Movie_ID | AVG_Rate | Updated_Dt

Ogni volta che una nuova classifica viene aggiunta in Rating , inserisci anche una nuova riga nella nuova tabella, ma invece di memorizzare la valutazione effettiva, memorizza invece il nuovo AVG_Rate calcolato:

Movie_ID | AVG_Rate | Updated_Dt

1           7.8    15/12/14 17:00:00
1           7.6    15/12/14 17:01:00
1           7.9    15/12/14 17:03:00

... E così via. Posso quindi usare la nuova tabella per controllare la regressione lineare di un determinato film. In termini di tabelle che diventano troppo grandi per un periodo di tempo, posso avere la tabella che contiene solo 7 giorni di dati in un dato momento (ad es. Backup ogni 7 giorni e cancellazione di dati più vecchi di una settimana).

  • Crea una tabella temporanea per eseguire la regressione lineare su:

Stessa logica di cui sopra, tranne per il fatto che la tabella viene creata temporaneamente al volo ogni volta che viene richiamata la stored procedure per ottenere film di tendenza. Le variazioni di AVG_Rate per ogni film possono essere calcolate direttamente dalla tabella Rating .

Sono scettico su quale approccio avrebbe funzionato al meglio (non sono avanzato in SQL ) e non sono sicuro che nessuna di queste due soluzioni sia la strada da percorrere.

Qualsiasi aiuto e amp; l'orientamento sarebbe molto apprezzato. Grazie!

    
posta globetrotter 15.12.2014 - 00:15
fonte

2 risposte

2

Probabilmente lo farei in un terzo modo, ovvero aggiungendo una colonna alla tua tabella di valutazione il cui valore è la valutazione media dopo l'applicazione di tale valutazione. Ciò significa che questa fase del calcolo dovrà essere preformata una sola volta anziché tutte le volte che viene eseguita la stored procedure, con un conseguente miglioramento delle prestazioni. Dal punto di vista tecnico, il tuo database non viene completamente normalizzato, ma dal momento che sono solo i dati storici che non devono essere modificati che sono denormalizzati, non considererei un problema enorme.

Questo è simile al tuo primo suggerimento, ovviamente, ma evita la duplicazione delle informazioni in una tabella separata.

    
risposta data 15.12.2014 - 12:41
fonte
1

Credo che questo sia un caso d'uso perfetto per una vista , in altre parole una "tabella virtuale" definita da una query SQL su tabelle reali nel database.

Di default viene generata una vista dai dati reali ogni volta che una query deve essere eseguita su di essa, ma alcuni RDMS supportano le viste con la memorizzazione nella cache / precomputing / ecc. Oracle chiama queste "viste materializzate" e SQL Server le chiama "viste indicizzate" .

Per essere un po 'più specifici, Miglioramento delle prestazioni con le viste indicizzate di SQL Server 2008 dice che:

Indexed views can increase query performance in the following ways:

  • Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
  • Tables can be prejoined and the resulting data set stored.
  • Combinations of joins or aggregations can be stored.

Potresti non aver bisogno degli enormi miglioramenti delle prestazioni che sostengono, ma questo ci dà un'idea concreta di quali siano le viste indicizzate di SQL Server, quindi penso sia giusto affermare che una vista indicizzata è funzionalmente equivalente alla tua opzione N. 1 di creazione di una tabella aggiuntiva, mentre una vista non indicizzata è funzionalmente equivalente all'opzione n. 2 di creazione di una tabella temporanea ogni volta. Tranne che è probabilmente molto più facile passare da una vista normale a una vista indicizzata piuttosto che passare da una tabella permanente a una tabella temporanea, quindi per lo meno probabilmente ti risparmierai un po 'di tempo a sperimentare le tue opzioni in questo modo .

Questa risposta SO contiene altri link utili sull'argomento.

    
risposta data 14.05.2015 - 20:26
fonte

Leggi altre domande sui tag

Gestisce lato server / lato client quando per mostrare le informazioni mancanti come segnaposto Cos'è questo dialetto Lisp, usato negli interni GCC?