Блог

У меня есть таблица Athena с 4 столбцами (A, B, C, D) , и я хочу найти:

1. количество строк, связанных с каждой уникальной комбинацией A amp; B
2. значение C самой последней строки для той же пары A amp; B , где D — временная метка

Например, если это входные данные

  --- --- ----- ------------ 
| 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 |
 --- --- ----- ------------ 
  

Это желаемый результат

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

Я не силен в запросах, и моя лучшая попытка заключается в следующем:

Соедините два подзапроса, где один выполняет подсчет, а другой ранжирует каждую строку по времени. Затем при объединении выберите только те строки, которые имеют самый высокий ранг.

 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
  

В Presto есть несколько сложных функций агрегирования. Итак:

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

max_by() объясняется в документации.

Это может быть достигнуто с помощью оконных функций 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
  

В подзапросе оконные функции можно использовать для подсчета количества записей, имеющих одинаковый (a, b) кортеж, и ранжирования записей по убыванию d . Затем внешний запрос фильтрует самую последнюю запись в каждой группе.

Решение Гордона Линоффа в порядке. Другой вариант, если вы не хотите использовать 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
  

Вот демонстрация!