Как выбрать последнее значение по дате и предыдущее в одной строке?

Question

[still_45]

Есть таблица:

id date balance

Ее примерное содержание:

id  date balance
1  2018-10-01  111
1  2018-10-02 115
12 2017-10-01 1134
... и тд

В таблице несколько миллионов записей, куда ежедневно заносится id (неуникален) клиента, дата и его баланс.

Надо сделать запрос, который покажет самое последние id клиента, дата его последнего изменения баланса, последний баланс и дата предпоследнего изменения баланса и предпоследний баланс, для каждого клиента в базе

id  date_current balance current_balance  date_previous_balance previous balance

Такой запрос выдает последнее изменение:

SELECT
    id,
    date,
    balance
 FROM (
   SELECT
        cb.*,
        row_number() OVER (PARTITION BY id ORDER BY  date DESC) r
      FROM client_balance cb
    ) t
  WHERE r = 1
;

Как к нему добавить предыдущее?

Answer 1

[Сергей c0re]

Вы по всей видимости так и не прочитали и не посмотрели как работает ROW_NUMBER из ответа на предыдущий вопрос !?

посмотрите вывод запроса с ROW_NUMBER() !

SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b

в данном случае row_number() нумерует в разбивке по id по убыванию даты и соответственно, предыдущая запись - с номером 2

Поэтому необходимо просто выбрать с r = 1 или r =2 соответственно !

для наглядности вынес подзапрос с row_number в WITH - обобщенное_табличное_выражение

WITH bal AS (
    SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b
)
SELECT id, balance_date, balance FROM bal WHERE r = 1 or r = 2
  ORDER BY id, balance_date desc
;

см. db<>fiddle

UPDATE:

Если выводить последнее и предыдущее значение в одной строке, то выбираем каждое подзапросом и объединяем подзапросы через LEFT JOIN (к последнему присоединяем предыдущее) по id. Через LEFT JOIN чтобы из левой таблицы (последние значения) выбирались все строки и присоединялись из правой (предыдущие значения) те строки какие есть, а если нет, то - NULL

см. ниже:

WITH
bal AS (
    SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b
),
last_bal AS (
  SELECT id, balance_date, balance FROM bal WHERE r = 1
),
prev_bal AS (
  SELECT id, balance_date, balance FROM bal WHERE r = 2
)
SELECT
    lb.id,
    lb.balance_date AS last_balance_date, lb.balance AS last_balance,
    pb.balance_date AS prev_balance_date, pb.balance AS prev_balance
  FROM last_bal lb
  LEFT JOIN prev_bal pb ON pb.id = lb.id
;

см. db<>fiddle №2

Comments

[still_45]

большое спасибо теперь почитал, но всерано не понял

этот запрос

WITH bal AS (
    SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b
)
SELECT id, balance_date, balance FROM bal WHERE r = 1 or r = 2
  ORDER BY id, balance_date desc
;

выдает так

а мне надо так

[Сергей c0re]

still_45, обновил.

какие сложности? просто выбираем строки с нужной нумерацией. нумерация ведется по убывающей дате (ORDER BY balance_date DESC) в разбивке по id (PARTITION BY id)
соответственно под номером 1 будет с самой последней датой (большей), с номером 2 будет меньше 1й, с номером 3 будет меньше 2й и т.д.

последняя - 1
предыдущая - 2
предпредыдущая 3

PS: посмотрите эту ссылку - Функция ROW_NUMBER

[still_45]

Сергей c0re, спасибо