Hacking della funzione JSON_ARRAYAGG di MySQL per creare dimensioni dinamiche e multi-valore

Hacking JSON_ARRAYAGG function in MySQL for dynamic and multi-value dimensions

Colmare una delle meno conosciute lacune di MySQL

Introduzione

Immaginiamo di essere membri del team Data di un’azienda di scatole a abbonamento. In un database MySQL, i record delle transazioni di acquisto vengono scritti in una tabella chiamata subscriptions. Escludendo i metadati, la tabella contiene un campo customer_id e un campo subscription, e assomiglia a qualcosa del genere:

Nel nostro scenario di esempio, un cliente può avere più abbonamenti. L’unicità di ogni record è definita sia dall’ID del cliente che da un abbonamento, cioè nessun cliente può avere lo stesso abbonamento due volte. Se vuoi caricare questi dati di test nel tuo database, puoi trovare il codice per farlo qui.

Come azienda di scatole a abbonamento, il nostro obiettivo è vendere più scatole a abbonamento. A tal fine, il team di prodotto ha recentemente fatto notare che tutti i nostri clienti attuali hanno più di un abbonamento. Sono curiosi di cosa ciò dica sul comportamento dei clienti. Chiedono al nostro team un modello dati che mostri le combinazioni di abbonamenti acquistati dagli utenti, nonché le combinazioni più comuni.

Anche il team di marketing si è mostrato interessato a questo modello. Credono che i risultati possano essere utili per promozioni di prodotti raggruppati, per creare profili dei clienti e per le campagne di email marketing. Per le stesse ragioni, chiedono anche di vedere il numero totale più comune di abbonamenti acquistati da ciascun cliente.

• Gli autori si uniscono contro le aziende di intelligenza artificiale cercano rispetto e pagamento per il lavoro con copyright
• Ingegnere dei dati vs Scienziato dei dati Quale carriera scegliere?
• Genera idee per magneti di lead con ChatGPT

In breve, il modello dati richiesto spera di rispondere ad alcune domande importanti che idealmente porteranno a una maggiore vendita di scatole a abbonamento. La domanda è: come eseguirlo esattamente?

In questo articolo, affronteremo una sfida unica di modellazione dei dati colmando una delle meno conosciute lacune di MySQL. Affronteremo l’aggregazione qualitativa, il tipo di dati JSON e come forzare MySQL a ordinare una lista di valori in modo da ottenere dimensioni multiple distinte.

Indice

• Aggregazioni come dimensioni
• Una breve panoramica del tipo di dati JSON in MySQL
• JSON_ARRAYAGG
• Utilizzo di ROW_NUMBER per forzare l’ordinamento dei valori
• Per riassumere

Aggregazioni come dimensioni

Concettualmente, ciò che dobbiamo fare è relativamente semplice: dobbiamo raggruppare i nostri abbonamenti per cliente. Poi dobbiamo esaminare questi gruppi e vedere quali sono i più comuni e quanti abbonamenti contengono.

In termini di modellazione dei dati, stiamo guardando una forma di aggregazione: specificamente, l’aggregazione degli abbonamenti per cliente.

È normale pensare alle funzioni di aggregazione in senso quantitativo (SUM, COUNT, ecc.), e questo è in gran parte perché è ciò che la maggior parte delle funzioni di aggregazione fa in SQL. Ma possiamo anche aggregare valori di stringhe concatenate in stringhe più lunghe, simili a liste.

La sfida in questo caso sta nel poter accedere, manipolare o valutare in altro modo i valori all’interno di queste stringhe concatenate. MySQL tratterà il valore foo, bar, hello, world non come una lista, ma come testo.

Perché è rilevante? Principalmente perché nel nostro scenario ipotetico vogliamo contare il numero di abbonamenti in ogni combinazione. Non vogliamo una lunga stringa separata da virgole, vogliamo qualcosa che si avvicini di più a una vera lista.

Risolvere questo problema in Python sarebbe semplice: usando pandas, forse polars, o anche solo strutture dati native di Python. Ma ci sono molti casi in cui questa non è un’opzione. Forse il team Data utilizza solo dbt; o, forse più comunemente, lavori in un’azienda in cui il dipartimento IT ha severe restrizioni sugli ambienti locali.

In ogni caso, se hai solo SQL a tua disposizione, hai bisogno di una soluzione che ti fornisca il codice più leggibile e i risultati più flessibili. Ottenere questo risultato non è intuitivo. Ad esempio, il mio primo istinto quando ho incontrato questo problema è stato quello di utilizzare GROUP_CONCAT, la funzione che concatena le stringhe in base al raggruppamento definito:

CON  sottoscrizioni_raggruppate COME (  SELECT    id_cliente,    GROUP_CONCAT(sottoscrizione) AS sottoscrizioni  FROM     sottoscrizioni  GROUP BY id_cliente  )SELECT  sottoscrizioni,  COUNT(*) AS num_accountFROM sottoscrizioni_raggruppateGROUP BY sottoscrizioni;

Come puoi vedere, l’aggregazione ha funzionato tecnicamente, ma non ha funzionato in base alla nostra logica aziendale. Guarda la prima e l’ultima riga. La combinazione di sottoscrizioni “international_snacks, self_care” è la stessa combinazione di “self_care, international_snacks”. (Lo stesso vale per la 2a e la 4a riga.)

Possiamo utilizzare una clausola ORDER BY all’interno di GROUP_CONCAT per risolvere questo particolare problema:

CON  sottoscrizioni_raggruppate COME (  SELECT    id_cliente,    GROUP_CONCAT(sottoscrizione ORDER BY sottoscrizione) AS sottoscrizioni  FROM     sottoscrizioni  GROUP BY 1  )SELECT  sottoscrizioni,  COUNT(*) AS num_accountFROM sottoscrizioni_raggruppateGROUP BY sottoscrizioni;

Ma questo continua a lasciarci con il problema di conteggiare quante sottoscrizioni ci sono in ogni combinazione.

C’è un modo per farlo. Ma non solo è inutilemente complesso e poco leggibile, secondo me, ma presenta anche alcuni problemi non così ovvi.

Una rapida ricerca su come contare il numero di valori in una stringa delimitata da virgole in MySQL restituisce una soluzione da StackOverflow che, tradotta per i nostri scopi, si riduce a questo (CTE sottoscrizioni_raggruppate escluso):

SELECT  sottoscrizioni,  LENGTH(sottoscrizioni) - LENGTH(REPLACE(sottoscrizioni, ',', '')) + 1 AS num_sottoscrizioni,  COUNT(*) AS num_accountFROM sottoscrizioni_raggruppateGROUP BY sottoscrizioni;

Che fondamentalmente conta il numero di virgole e poi aggiunge 1 al risultato. Questo funziona. Ma non solo è difficile da capire a prima vista, ma introduce anche potenziali errori: la funzione LENGTH e CHAR_LENGTH non contano la stessa cosa.

Come puoi probabilmente immaginare, questo articolo illustra gli ostacoli che ho incontrato sul lavoro quando mi sono trovato in una situazione simile.

In definitiva, la soluzione era in un workaround un po’ “hack-y”, ma straordinariamente comprensibile, che utilizzava il tipo di dato JSON nativo di MySQL.

Una breve panoramica del tipo di dato JSON in MySQL

Il tipo di dato JSON in MySQL è stato aggiunto nella versione 5.7.8 e fornisce molte utilità utili sia per lo storage che per la modellazione.

Nell’ambito del tipo di dato JSON (ufficialmente chiamato “documenti JSON”) ci sono due diverse strutture dati: gli array JSON e gli oggetti JSON.

Un array JSON può essere semplicemente considerato come un array (una lista, se sei un Pythonista): valori racchiusi tra parentesi quadre [ ] e separati da virgole.

• Un esempio di valore array JSON MySQL: ["foo", "bar", 1, 2]

Un oggetto JSON può essere considerato come una tabella hash (o, ancora una volta in termini Python, un dizionario): coppie chiave-valore, separate da virgole e racchiuse tra parentesi graffe { }.

• Un esempio di valore oggetto JSON MySQL: {"foo": "bar", 1: 2}

MySQL ha una serie di funzioni che possono essere utilizzate per gestire entrambi questi formati, quasi nessuna delle quali esegue alcun tipo di aggregazione.

Fortunatamente, però, ce ne sono due che lo fanno. E entrambi restituiscono documenti JSON, il che significa che possiamo utilizzare le funzioni integrate di MySQL per accedere ai valori al loro interno.

JSON_ARRAYAGG

La funzione MySQL JSON_ARRAYAGG funziona molto come GROUP_CONCAT. La differenza più grande è che restituisce un array JSON, che, ancora una volta, ha diverse utili funzioni integrate collegate sopra.

Il tipo di dato JSON array risolve uno dei nostri due problemi con una semplicità sorprendente: il problema di conteggiare in modo affidabile il numero di sottoscrizioni in una combinazione. Ciò viene realizzato utilizzando la funzione JSON_LENGTH. La sintassi è meravigliosamente semplice:

SELECT JSON_LENGTH(JSON_ARRAY("foo", "bar", "hello", "world"));-- La funzione JSON_ARRAY viene utilizzata qui solo per creare rapidamente un array di esempio

Il risultato di questa istruzione è 4, poiché ci sono 4 valori nell’array JSON generato.

Ma torniamo alla combinazione di sottoscrizioni. Purtroppo, JSON_ARRAYAGG non dispone della funzionalità di ordinamento di GROUP_CONCAT. L’ordinamento dei valori di sottoscrizione, anche in una CTE prima della query di base, non restituisce i risultati desiderati:

WITH  subscriptions_ordered AS (  SELECT    customer_id,    subscription  FROM subscriptions  ORDER BY subscription   )  , subscriptions_grouped AS (  SELECT    customer_id,    JSON_ARRAYAGG(subscription) AS subscriptions,    JSON_LENGTH(JSON_ARRAYAGG(subscription)) AS num_subscriptions  FROM     subscriptions_ordered  GROUP BY customer_id  )SELECT  subscriptions,  COUNT(*) AS num_accounts  num_subscriptionsFROM subscriptions_groupedGROUP BY subscriptions;

Il numero di sottoscrizioni in ogni combinazione è presente, grazie alla funzione JSON_LENGTH, ma le combinazioni che sono effettivamente le stesse vengono nuovamente erroneamente caratterizzate come distinte a causa del loro ordine.

Utilizzo di ROW_NUMBER per forzare l’ordinamento dei valori

ROW_NUMBER è una funzione di finestra che crea un indice. L’indice deve essere definito; cioè, devi dirgli dove iniziare, come incrementare (in direzione) e dove finire.

Possiamo vedere un rapido esempio di ciò applicando la funzione ROW_NUMBER e dicendogli di ordinare in base al campo sottoscrizione:

SELECT   customer_id,   subscription,   ROW_NUMBER() OVER(ORDER BY subscription) AS alphabetical_row_numFROM subscriptions;

Osserva attentamente i risultati. Anche se non abbiamo utilizzato una dichiarazione ORDER BY alla fine della nostra query, i dati sono comunque ordinati secondo l’ordinamento specificato nella clausola ORDER BY in OVER.

Ma ovviamente questo ancora non è esattamente ciò che vogliamo. Quello che dobbiamo fare ora è aggiungere una clausola PARTITION BY alla nostra funzione di finestra, in modo che l’ordinamento dei risultati sia correlato (e infatti limitato) a ciascun ID cliente. Come segue:

SELECT   customer_id,   subscription,   ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY subscription) AS alphabetical_orderFROM subscriptions;

Probabilmente puoi intuire dove stiamo andando.

Se eseguiamo la funzione JSON_ARRAYAGG su questi risultati in una CTE, vediamo che le combinazioni duplicate ora appaiono esattamente uguali, grazie alle sottoscrizioni forzate in un ordine alfabetico dalla funzione ROW_NUMBER:

WITH   subscriptions_ordered AS (  SELECT     customer_id,     subscription,     ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY subscription) AS alphabetical_order  FROM subscriptions  )SELECT  customer_id,  JSON_ARRAYAGG(subscription) AS subscriptionsFROM subscriptions_orderedGROUP BY 1ORDER BY 2;

Adesso tutto quello che dobbiamo fare è aggiungere il CTE di raggruppamento dopo quello che esegue ROW_NUMBER, e modificare la query di base:

WITH   subscriptions_ordered AS (  SELECT     customer_id,     subscription,     ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY subscription) AS alphabetical_order  FROM subscriptions  )  , subscriptions_grouped AS (  SELECT    customer_id,    JSON_ARRAYAGG(subscription) AS subscriptions,    JSON_LENGTH(JSON_ARRAYAGG(subscription)) AS num_subscriptions  FROM subscriptions_ordered  GROUP BY customer_id   )SELECT  subscriptions,  COUNT(*) AS num_customers,  num_subscriptionsFROM subscriptions_groupedGROUP BY subscriptionsORDER BY num_customers DESC;

Questo fornisce non solo combinazioni distinte e accurate di abbonamenti, ma anche il numero di clienti che hanno acquistato tali combinazioni e quanti abbonamenti ne fanno parte:

Voilà!

Per riassumere

• Volevamo sapere quanti clienti hanno acquistato diverse combinazioni di abbonamenti e quanti abbonamenti c’erano in ciascuna di queste combinazioni. Ciò ha presentato due problemi: come ottenere al meglio quest’ultimo e come generare combinazioni di abbonamenti accuratamente distinte.
• Per ottenere il numero di abbonamenti in ogni combinazione, abbiamo scelto di utilizzare una delle funzioni JSON di MySQL, JSON_ARRAYAGG. L’aggregazione risultante ci è stata restituita come tipo di dati JSON, consentendoci di utilizzare la funzione JSON_LENGTH.
• Poi abbiamo dovuto forzare l’ordinamento dei valori all’interno dell’array JSON in modo che le combinazioni duplicate non apparissero erroneamente distinte. Per fare ciò, abbiamo utilizzato la funzione di finestra ROW_NUMBER in un CTE prima della query di base, partizionando per l’ID del cliente e ordinando gli abbonamenti in ordine alfabetico (in ordine crescente).
• Ciò ci ha permesso di aggregare fino a combinazioni di abbonamenti accuratamente distinte; e con questo siamo stati in grado di utilizzare una semplice funzione COUNT per vedere quanti clienti avevano acquistato ciascuna combinazione.

Grazie per aver letto! 🤓

Spero che questo sia stato utile! Se conosci altri trucchi/rimedi intelligenti in SQL (indipendentemente dal dialetto), mi piacerebbe saperne di più. SQL è da tempo la lingua franca de facto per la trasformazione di dati strutturati, ma non è perfetto. Mi diverto sempre ad apprendere soluzioni innovative e/o intelligenti per sfide reali. 🔥

Scrivo periodicamente su argomenti di ingegneria dei dati e di analisi, con l’obiettivo di scrivere il più chiaramente e semplicemente possibile. Se qualcosa in questo articolo ti ha confuso, fammelo sapere nei commenti. E se sei interessato a leggere altri articoli come questo, sentiti libero di seguirmi e/o connetterti su LinkedIn.