2
votes

Comment puis-je obtenir le nombre et l'enregistrement le plus récent pour chaque paire de colonnes?

J'ai une table Athena avec 4 colonnes (A, B, C, D) et je veux trouver:

le nombre de lignes associées à chaque combinaison unique de A & B
la valeur de C de la ligne la plus récente pour la même paire A & B où D est un horodatage

Par exemple, s'il s'agit des données d'entrée

WITH t1 AS (
    SELECT A, B, count(*)
    FROM data
    GROUP BY A, B
),
t2 AS (
    SELECT A, B, C, RANK() OVER (PARTITION BY A, B ORDER BY D DESC) AS rank
    FROM data
)
SELECT t1.A, t1.B, t2.newest_C, t1.count
FROM t1 LEFT JOIN t2 ON t1.A = t2.A AND t1.B = t2.B
WHERE rank = 1

Ceci est la sortie souhaitée

+---+---+----------+-------+
| A | B | newest_C | count |
+---+---+----------+-------+
| 1 | 1 | 'a'      |     2 |
| 1 | 2 | 'c'      |     1 |
| 1 | 3 | 'd'      |     1 |
| 2 | 2 | 'f'      |     2 |
+---+---+----------+-------+

Je ne suis pas très doué pour les requêtes et ma meilleure tentative est la suivante:

Rejoignez deux sous-requêtes où l'une fait un décompte et l'autre classe chaque ligne en fonction du temps. Ensuite, lors de la jointure, sélectionnez uniquement les lignes qui ont le rang le plus élevé.

+---+---+-----+------------+
| A | B |  C  |     D      |
+---+---+-----+------------+
| 1 | 1 | 'a' | 2019-04-04 |
| 1 | 1 | 'b' | 2019-04-03 |
| 1 | 2 | 'c' | 2019-04-02 |
| 1 | 3 | 'd' | 2019-04-01 |
| 2 | 2 | 'e' | 2019-04-03 |
| 2 | 2 | 'f' | 2019-04-04 |
+---+---+-----+------------+

sql presto amazon-athena

0 commentaires

3 Réponses :

1
votes

Ceci pourrait être réalisé en utilisant Fonctions de fenêtre Presto :

SELECT a, b, c AS newest_c, cnt
FROM (
    SELECT 
        t.*,
        COUNT(*)     OVER(PARTITION BY a, b) AS cnt,
        ROW_NUMBER() OVER(PARTITION BY a, b ORDER BY d DESC) AS rn
    FROM mytable t
) x WHERE rn = 1

Dans la sous-requête, les fonctions de fenêtre peuvent être utilisées pour compter le nombre d'enregistrements ayant le même tuple (a, b) et classer les enregistrements par ordre décroissant d . Ensuite, la requête externe filtre l'enregistrement le plus récent de chaque groupe.

1 commentaires

Merci pour la suggestion! C'était nettement plus rapide que ma requête. Curieux de savoir pourquoi l'utilisation des fonctions de fenêtrage est plus rapide que les jointures? J'aurais pensé que faire un calcul de fenêtre serait plus lent que de faire un groupe par.

1
votes

Presto a des fonctions d'agrégation sophistiquées. Donc:

select a, b, count(*) as cnt,
       max_by(c, d)
from t
group by a, b;

max_by () est expliqué dans le documentation .

0 commentaires

0
votes

La solution de Gordon Linoff est correcte. Une autre alternative si vous ne souhaitez pas utiliser max_by:

SELECT t1.a, t1.b, t1.c, t2.count
FROM data AS t1 
INNER JOIN
  (SELECT a, b, count(*) AS count, max(d) AS d 
  FROM data 
  GROUP BY a,b) AS t2
ON t1.a = t2.a AND t1.b = t2.b AND t1.d = t2.d

Voici une démo!

2 commentaires

Merci pour la suggestion! Curieux de savoir pourquoi ce serait mieux que ce que Gordon Lindoff a suggéré?

N'est-ce pas mieux! Haha, c'est seulement SQL plus standard que je pense.