[SQL] determinare ID feature corrispondenti ai valori risultanti della funzione di aggregazione max

[pigreco]

Ho un insieme di dati caratterizzati da un id univoco (pk_uid) e devo determinare il valore massimo, raggruppando secondo l'attributo (nome), di due altri attributi lg e ln: come determinare l'ID (pk_uid) corrispondente ai valori massimi risultanti?

qui un esempio del dataset:

pk_uid	nome	lg	ln
0	4791	47.9	41.1
1	4791	43.5	37.5
2	4791	41.5	35.7
3	4791	43.9	38.2
4	4791	47.7	41.9
5	7307	40.6	35.0
6	7307	44.1	38.4
7	7307	40.4	34.7
8	7307	44.0	38.4
9	7307	34.9	29.2
10	7307	35.4	29.2
11	7307	38.5	32.8
12	7307	37.6	31.8
13	7307	39.1	33.4
14	7307	38.3	32.5
15	7307	43.4	37.8
16	7307	45.0	38.3
17	7724	58.1	49.5
18	7724	57.2	49.2
19	7724	52.8	45.9
20	7724	49.8	42.9

La seguente query determina i valori massimi aggregati per nome degli attributi lg e ln

SELECT
  nome,
  max(lg) AS lg_max,
  max(ln) AS ln_max
FROM
  dataset
GROUP BY
  1

che restituisce:

NOME	lg_max	ln_max
4791	47.9	41.9
7307	45.0	38.4
7724	58.1	49.5

a me interessa capire come scrivere una query che restituisca questo:

NOME	lg_max	ln_max	id_lg_max	id_ln_max
4791	47.9	41.9	0	4
7307	45.0	38.4	16	8
7724	58.1	49.5	17	17

dove:

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

Un modo sarebbe quello di utilizzare le espressioni dentro la funzione max, ecco un esempio:

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

otterrei la tabella sotto, che risulta confusionaria:

NOME	lg_max	ln_max
4791	47.9-0	41.9-4
7307	45.0-16	38.4-8
7724	58.1-17	49.5-17

[aborruso]

@pigreco scrivo senza fare test.

Ma non puoi fare due left join tra l'output di

SELECT
  nome,
  max(lg) AS lg_max,
  max(ln) AS ln_max
FROM
  dataset
GROUP BY
  1

e la tabella di input?
Prima per nome e valore lg e poi per nome e valore ln e ti prendi l'ID?

[pigreco]

Ma non puoi fare due left join

è quello che sto facendo, ma se ci fossero due valori max uguali (esempio pk_uid 6 e 8 per il campo ln) li prenderebbe entrambi

ecco esempio:

SELECT pk_uid, t.nome
FROM 
(
SELECT
  nome,
  max(lg) AS lg_max,
  max(ln) AS ln_max
FROM
  dataset_test
GROUP BY
  1
) t
LEFT JOIN
dataset_test
ON ln_max = ln

[aborruso]

@pigreco una piccola correzione: il JOIN con doppia condizione, può essere semplice JOIN e non LEFT

[aborruso]

Una piccola nota carina di uso di Miller.

Se arrivi all'output SQL di base

NOME,lg_max,ln_max,IDLG,IDLN
4791,47.900000,41.900000,0,4
7307,45.000000,38.400000,16,6
7307,45.000000,38.400000,16,8
7724,58.100000,49.500000,17,17

e vuoi unire in due record 7307, avendo un cella con i due valor di IDLN, puoi lanciare

mlr --csv reshape -r "ID" -o i,v then \                                                                                         130 ↵
filter -x '$v==""' then \
uniq -a then \
nest --ivar ";" -f v then \
reshape -s i,v then \
unsparsify input.csv

e avere

NOME	lg_max	ln_max	IDLG	IDLN
4791	47.900000	41.900000	0	4
7307	45.000000	38.400000	16	6;8
7724	58.100000	49.500000	17	17

Il passaggio fondamentale è passare da wide a long con reshape -r "ID" -o i,v

NOME	lg_max	ln_max	i	v
4791	47.900000	41.900000	IDLG	0
4791	47.900000	41.900000	IDLN	4
7307	45.000000	38.400000	IDLG	16
7307	45.000000	38.400000	IDLN	6
7307	45.000000	38.400000	IDLG	16
7307	45.000000	38.400000	IDLN	8
7724	58.100000	49.500000	IDLG	17
7724	58.100000	49.500000	IDLN	17

Poi rimuovo eventuali valori di v nulli, rimuovo righe duplicate, poi implodo le righe in cui a parità di altri campi hanno lo stesso valore di v e poi in ultimo di nuovo da long a wide.

[pigreco]

Una soluzione al quesito:

WITH calcolo_max AS (
  SELECT
    NOME,
    max(lg) AS lg_max,
    max(ln) AS ln_max
  FROM
    dataset_test
  GROUP BY
    NOME
)
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

[pigreco]

Aggiungo screenshot per la ricetta:

[pigreco]

ricetta fatta e pubblicata

https://tansignari.opendatasicilia.it/ricette/query/id_valori_max/

[pigreco]

riapro perché nel caso in cui esistano due punti diversi con stessi valori di lg e ln, la query fa il prodotto cartesiano

[aborruso]

non sono sicuro di avere capito tutto, ma non puoi fare un distinct alla fine?

[pigreco]

non sono sicuro di avere capito tutto, ma non puoi fare un distinct alla fine?

ho riaperto per tenerne traccia
appena posso faccio alcuni test

[pigreco]

questa query mi aiuta molto nel ragionamento

SELECT
  pk_uid,
  nome,
  max_lg,
  max_ln
FROM
  (
    SELECT
      a.pk_uid,
      a.nome,
      a.lg,
      a.ln,
      lg_max,
      ln_max,
      a.lg = lg_max AS max_lg,
      a.ln = ln_max AS max_ln
    FROM
      dataset_test a
      LEFT JOIN (
        SELECT
          NOME,
          max(lg) AS lg_max,
          max(ln) AS ln_max
        FROM
          dataset_test
        GROUP BY
          NOME
      ) USING (NOME)
  ) k
WHERE
  max_lg | | max_ln != 0 | | 0
ORDER BY
  nome

dove:

• 1 è vero
• 0 è falso

ovvero:
calcola i valori massimi LG e LN, poi nella stessa tabella di INPUT aggiungo due campi popolandoli con il confronto tra il valore massimo dell'aggregazione e i vari record, dove l'uguaglianza è vera metterà 1 altrimenti 0.

In QGIS e con le espressioni, il tutto sarebbe:

maximun("lg","nome")= "lg" e maximun("ln","nome")= "ln"