Блог

Возможно ли написать SQL-запрос SELECT, который вернет последние значения, основанные на времени, сгруппированные в одну строку?

 time| id  |  a  |  b  | c  | d
---- ----- ----- ----- ---- ----
 1  |  1  |  a1 |     |    |
 2  |  1  |     |  b1 |    |
 3  |  1  |     |     | c0 |
 4  |  1  |  a3 |     | c3 |
 5  |  1  |  a0 |     |    |
  

Результат должен выглядеть следующим образом:

 id  |  a  |  b  | c  | d
---- ----- ----- ---- ----
 1  |  a0 |  b1 | c3 |
  

Используемая база данных — AWS Athena.

С FIRST_VALUE() помощью функции window:

 SELECT DISTINCT id,
  FIRST_VALUE(a) OVER (PARTITION BY id ORDER BY CASE WHEN a IS NULL THEN 1 ELSE 0 END, time DESC) a,
  FIRST_VALUE(b) OVER (PARTITION BY id ORDER BY CASE WHEN b IS NULL THEN 1 ELSE 0 END, time DESC) b,
  FIRST_VALUE(c) OVER (PARTITION BY id ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END, time DESC) c,
  FIRST_VALUE(d) OVER (PARTITION BY id ORDER BY CASE WHEN d IS NULL THEN 1 ELSE 0 END, time DESC) d
FROM tablename
  

Смотрите демонстрацию (для MySQL, но это стандартный SQL).
Результаты:

 > id | a  | b  | c  |    d
> -: | :- | :- | :- | ---:
>  1 | a0 | b1 | c3 | null
  

Вот это с данными CTE для иллюстрации (извините, я увидел ваше редактирование после ответа. Я не знаком с AWS Athena, используя синтаксис Postgresql). Работает более чем для одного id .

 with t("time", id, a, b, c, d) as 
(
 values
 (1, 1, 'a1', null, null, null),
 (2, 1, null, 'b1', null, null),
 (3, 1, null, null, 'c0', null),
 (4, 1, 'a3', null, 'c1', null),
 (5, 1, 'a0', null, null, null),
 (1, 2, 'a1', null, null, null),
 (2, 2, null, 'b1', null, null),
 (3, 2, null, null, 'c0', null),
 (4, 2, 'a3', 'b2', 'c1', 'd3'),
 (5, 2, null, null, 'c3', null)
),
u(id, name) as 
(
 values 
 (1, 'john'), 
 (2, 'paul'), 
 (3, 'mark') 
)
select ext.id, max(u.name) "name",
 (select a from t where a is not null and id = ext.id order by "time" desc limit 1),
 (select b from t where b is not null and id = ext.id order by "time" desc limit 1),
 (select c from t where c is not null and id = ext.id order by "time" desc limit 1),
 (select d from t where d is not null and id = ext.id order by "time" desc limit 1)
from t ext join u on ext.id = u.id group by ext.id;
  

Результат:

 id|name|a |b |c |d |
--|----|--|--|--|--|
 1|john|a0|b1|c1|  |
 2|paul|a3|b2|c3|d3|