Scorri per mostrare il menu

Nel capitolo precedente, abbiamo menzionato un oggetto chiamato vista durante la discussione sui privilegi utente. Ora consideriamo questo oggetto in modo più dettagliato.

Una vista in SQL è una tabella virtuale basata sul result-set di una query SQL. Contiene righe e colonne proprio come una tabella reale, e i campi di una vista sono campi provenienti da una o più tabelle reali nel database.
Le viste vengono utilizzate per diversi motivi, tra cui semplificare query complesse, aumentare la sicurezza limitando l'accesso a dati specifici e presentare i dati in un formato specifico.

Caratteristiche principali delle viste

Tabella virtuale: una vista non memorizza fisicamente i dati. Deriva i suoi dati dalle tabelle su cui si basa (chiamate tabelle di base);
Semplificazione di query complesse: le viste possono semplificare query SQL complesse racchiudendole in una singola vista. Questo rende più semplice lavorare con join e aggregazioni complesse;
Sicurezza: le viste possono limitare l'accesso a specifiche righe o colonne di dati, aumentando la sicurezza. Gli utenti possono ricevere l'accesso alla vista senza ottenere l'accesso alle tabelle di base sottostanti;
Coerenza: le viste possono presentare un'interfaccia coerente e invariata anche quando lo schema del database sottostante cambia.

Implementazione

Creeremo una vista chiamata account_activity_view basata sulle nostre tabelle BankAccounts e UserLogs che mostra il numero di conto, il nome dell'intestatario, il saldo e l'azione più recente effettuata sul conto.


              1234567891011121314151617181920212223242526
            
-- Create a view that shows account details along with their latest action
CREATE OR REPLACE VIEW account_activity_view AS
SELECT
    ba.account_number,               -- Select the account number from the bankaccounts table
    ba.account_holder,               -- Select the account holder's name from the bankaccounts table
    ba.balance,                      -- Select the balance from the bankaccounts table
    ul.action AS latest_action,      -- Select the latest action from the userlogs table (alias as latest_action)
    ul.timestamp AS latest_action_time -- Select the timestamp of the latest action (alias as latest_action_time)
FROM
    BankAccounts ba                 -- Main table: bankaccounts
LEFT JOIN
    (
        -- Subquery to get the most recent action for each account
        SELECT DISTINCT ON (account_number)
            account_number,          -- Select the account number
            action,                 -- Select the action
            timestamp               -- Select the timestamp
        FROM
            UserLogs
        ORDER BY
            account_number,          -- Order by account number
            timestamp DESC          -- Order by timestamp in descending order to get the latest action
    ) ul ON ba.account_number = ul.account_number; -- Join the subquery result with bankaccounts on account number

-- Get data from the view
SELECT * FROM account_activity_view;

Descrizione della query

CREATE VIEW account_activity_view AS

Questa istruzione crea una vista chiamata account_activity_view che combina le informazioni della tabella bankaccounts con l'ultima attività dalla tabella userlogs. Fornisce una panoramica dei titolari di conto, del saldo attuale e della loro azione più recente.

Clausola principale SELECT:
- ba.accountnumber: Seleziona il numero di conto dalla tabella bankaccounts.
- ba.accountholder: Seleziona il nome del titolare del conto dalla tabella bankaccounts.
- ba.balance: Seleziona il saldo dalla tabella bankaccounts.
- ul.action AS latest_action: Seleziona l'ultima azione dalla tabella userlogs e la rinomina come latest_action.
- ul.timestamp AS latest_action_time: Seleziona il timestamp dell'ultima azione e lo rinomina come latest_action_time.
Clausola FROM:
- bankaccounts ba: La tabella principale è bankaccounts, con alias ba.
Clausola LEFT JOIN:
- La sottoquery recupera l'azione più recente per ogni conto.
- SELECT DISTINCT ON (accountnumber): Garantisce numeri di conto distinti, selezionando solo la voce più recente per ogni conto.
- ORDER BY accountnumber, timestamp DESC: Ordina le voci per numero di conto e poi per timestamp in ordine decrescente per ottenere l'azione più recente.
Condizione di join:
- ul ON ba.accountnumber = ul.accountnumber: Unisce il risultato della sottoquery con la tabella bankaccounts in base al numero di conto.

Ora è possibile utilizzare questa vista in altre query senza dover ricreare la query complessa con join, semplicemente utilizzando la vista creata come una normale tabella.

Nota

Anche se possiamo utilizzare la vista come una tabella normale, non è realmente una tabella fisica! I dati nella vista non sono memorizzati su disco. Invece, ogni volta che utilizziamo la vista, la sua query viene eseguita e i dati risultanti vengono utilizzati nella query principale.

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 3. Capitolo 6

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Viste