Determinare ID dei valori max

A cura di Totò Fiandaca  | issue #225 | guida/e Andrea Borruso Totò Fiandaca

Caso d’uso

Immaginiamo di avere degli oggetti, per esempio dei poligono che rappresentano degli edifici, e di voler determinare i valori massimi di due attributi (LG, LN) corrispondenti a delle misurazioni fatte per ogni lato dell’oggetto:

La determinazione dei valori massimi LG e LN per ogni poligono è abbastanza facile e immediata, sotto la query:

SELECT
  nome,
  max(LG) AS lg_max,
  max(LN) AS ln_max
FROM
  dataset_test
GROUP BY
  nome

il quesito diventa più interessante se volessimo determinare gli identificativi univoci dei punti in cui la misurazione risultasse la più alta: ed è questo il quesito della ricetta.

Prima soluzione

La soluzione proposta è la seguente query:

WITH calcolo_max AS (
  SELECT
    NOME,
    max(LG) AS lg_max,
    max(LN) AS ln_max
  FROM
    dataset_test
  GROUP BY
    NOME
) -- calcola i valori massimi dei due attributi
SELECT
  calcolo_maxdue.NOME,
  id_lg_max,
  lg_max,
  PK_UID AS id_ln_max,
  ln_max
 FROM
  (
    SELECT
      calcolo_max.NOME,
      lg_max,
      ln_max,
      PK_UID AS id_lg_max
    FROM
      calcolo_max
      JOIN dataset_test f ON calcolo_max.NOME = f.NOME
      AND calcolo_max.lg_max = f.LG
  ) calcolo_maxdue
  JOIN dataset_test f ON calcolo_maxdue.NOME = f.NOME
  AND calcolo_maxdue.ln_max = f.LN

Nel linguaggio umano la query di sopra fa:

Crea una tabella temporanea (calcolo_max) e la popola con i valori massimi dei due attributi (LG e LN), successivamente, la stessa tabella viene messa in JOIN due volte con la tabella di ingresso (dataset_test) con i relativi filtri (calcolo_max.NOME = f.NOME AND calcolo_max.lg_max = f.LG, per il primo attributo e calcolo_maxdue.NOME = f.NOME AND calcolo_maxdue.ln_max = f.LN per il secondo attributo) ovvero, deve cercare solo il nome e il valore massimo relativo.

Il risultato è quello atteso:

dove:

• id_lg_max è l’id del valore massimo dell’attributo lg
• id_ln_max è l’id del valore massimo dell’attributo ln

questo permetterà di localizzare, con precisione, quale degli enne punti è caratterizzato dal valore massimo.

NB: Se il valore massimo fosse presente in più punti, la query di sopra restituirebbe una riga per ogni valore massimo uguale.

Seconda soluzione

Una alternativa più veloce ma più complicata da gestire è quella di utilizzare le espressioni SQLite dentro la funzione di aggregazione max

SELECT
  nome,
  max(lg||'-'||pk_uid) AS lg_max,
  max(ln||'-'||pk_uid) AS ln_max
FROM
  dataset_test
GROUP BY
  1

output

il valore dell’identificativo univoco del valore massimo è aggiunto direttamente nella stessa cella: 45.0-16 e 38.4-8, dove il primo valore è il valore massimo, il secondo, separato da - è l’identificativo.

RIFERIMENTI

• SpatiaLite: https://www.gaia-gis.it/fossil/libspatialite/index
• SQLite: https://sqlite.org/index.html
• Fuzione aggregazione max: https://www.sqlitetutorial.net/sqlite-max/