Запрос Oracle SQL: получение последних значений для каждой группы в зависимости от времени

У меня есть следующая таблица в Oracle DB

id     date              quantity
1      2010-01-04 11:00  152
2      2010-01-04 11:00  210
1      2010-01-04 10:45  132
2      2010-01-04 10:45  318
4      2010-01-04 10:45  122
1      2010-01-04 10:30  1
3      2010-01-04 10:30  214
2      2010-01-04 10:30  5515
4      2010-01-04 10:30  210

теперь я хотел бы получить последнее значение (и его время) для каждого идентификатора. Пример вывода:

id     date              quantity
1      2010-01-04 11:00  152
2      2010-01-04 11:00  210
3      2010-01-04 10:30  214
4      2010-01-04 10:45  122

Я просто не могу понять, как поместить это в запрос...

Кроме того, следующие параметры были бы приятными:

Вариант 1: запрос должен возвращать только значения, которые относятся к последним XX минутам.

Вариант 2: идентификатор должен быть объединен с текстом из другой таблицы, в которой есть id и idname. вывод для id должен выглядеть следующим образом: id-idname (например, 1-testid1).

большое спасибо за любую помощь!

Ответ 1

Учитывая эти данные...

SQL> select * from qtys
  2  /

        ID TS                      QTY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         1 2010-01-04 10:45        132
         2 2010-01-04 10:45        318
         4 2010-01-04 10:45        122
         1 2010-01-04 10:30          1
         3 2010-01-04 10:30        214
         2 2010-01-04 10:30       5515
         4 2010-01-04 10:30        210

9 rows selected.

SQL>

... следующий запрос дает то, что вы хотите...

SQL> select x.id
  2         , x.ts as "DATE"
  3         , x.qty as "QUANTITY"
  4  from (
  5      select id
  6             , ts
  7             , rank () over (partition by id order by ts desc) as rnk
  8             , qty
  9      from qtys ) x
 10  where x.rnk = 1
 11  /

        ID DATE               QUANTITY
---------- ---------------- ----------
         1 2010-01-04 11:00        152
         2 2010-01-04 11:00        210
         3 2010-01-04 10:30        214
         4 2010-01-04 10:45        122

SQL>

Что касается ваших дополнительных требований, вы можете применить дополнительные фильтры к внешнему предложению WHERE. Аналогичным образом вы можете присоединить дополнительные таблицы к встроенному виду, как к любой другой таблице.

Ответ 2

Здесь приведен полный пример.

CREATE TABLE tbl1 (ID NUMBER, dt DATE, quantity NUMBER);

DELETE FROM tbl1;
insert into tbl1 values (1,to_date('2010-01-04 11:00','YYYY-MM-DD HH24:MI'), 152);
insert into tbl1 values (2,to_date('2010-01-04 11:00','YYYY-MM-DD HH24:MI'), 210);
insert into tbl1 values (1,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 132);
insert into tbl1 values (2,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 318);
insert into tbl1 values (4,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 122);
insert into tbl1 values (1,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 1);
insert into tbl1 values (3,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 214);
insert into tbl1 values (2,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 5515);
insert into tbl1 values (4,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 210);

SELECT t.ID
     , t.DT
     , t.QUANTITY
  FROM tbl1 t
     ,( SELECT ID
             , MAX(dt) dt
          FROM tbl1
       GROUP BY ID ) t2
  WHERE t.id = t2.id
    AND t.dt = t2.dt

Результаты:

1   1/4/2010 11:00:00 AM    152
2   1/4/2010 11:00:00 AM    210
3   1/4/2010 10:30:00 AM    214
4   1/4/2010 10:45:00 AM    122

Если вы хотите получить записи за последние 20 минут, вы можете сделать это (я использую 500 минут в этом примере, замените 500 на все, что вы пожелаете):

   SELECT t.ID
        , t.DT
        , t.QUANTITY
     FROM tbl1 t
        ,( SELECT ID
                , MAX(dt) dt
             FROM tbl1
            WHERE dt >= SYSDATE - (500 / 1400)
          GROUP BY ID ) t2
     WHERE t.id = t2.id
       AND t.dt = t2.dt;