Поиск строк с экстремальными значениями с использованием SQL

Question

У меня есть таблица t1 с 4 столбцами:

key, cd, date, result_num

В SAS у нас есть следующий код:

PROC SQL;
    create table t2 AS
    select * from t1
    group by key
    having date = MAX(date)
    order by key, cd;
RUN;

У меня сложилось впечатление, что все столбцы, которые выбираются при использовании агрегатной функции, такой как MAX, должны быть либо в группе, либо к ним применена агрегатная функция. Моя цель - преобразовать этот код SAS в SQL, есть ли способ сделать это в SQL (более конкретно, hiveQL)?

Answer 1

Хитрость заключается в том, чтобы получить доступ к вашей входной таблице дважды: один раз, чтобы вычислить максимальную дату, и один раз, чтобы выбрать соответствующие данные.

Если вы ищите строки, в которых дата является самой высокой датой во всей таблице, то это

PROC SQL;
    create table t2 AS
    select * from t1
    where date = (select MAX(date) from t1)
    order by key, cd;
RUN;

Если вы ищете строки, для которых дата является самой высокой датой для того же ключа, то это

PROC SQL;
    create table t2 AS
    select * from t1 inner join 
    (  select MAX(date) as maxDate 
       from t1  
       group by key) as m1 
       on m1.key = t1.key and m1.maxDate = t1.date
    order by key, cd;
RUN;

Answer 2

Я не думаю, что ваш запрос делает то, что вы хотите в SAS .,, возможно это так. В стандартном SQL (и Hive) вы можете сделать:

create table t2 AS
    select *
    from (select t1.*,
                 row_number() over (partition by key order by date desc) as seqnum
          from t1
         ) t1
    where seqnum = 1
    order by key, cd;