By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE balance
(
id INTEGER,
balance_date DATETIME,
balance INTEGER,
);
insert into balance values ( 1, '2018-10-01', 111 );
insert into balance values ( 1, '2018-10-02', 115 );
insert into balance values ( 12, '2017-10-01', 1134 );
insert into balance values ( 12, '2017-09-01', 159 );
insert into balance values ( 1, '2018-09-02', 130 );
insert into balance values ( 5, '2018-09-02', 150 );
6 rows affected
select * from balance;
| id | balance_date | balance |
|---|---|---|
| 1 | 2018-10-01 00:00:00.000 | 111 |
| 1 | 2018-10-02 00:00:00.000 | 115 |
| 12 | 2017-10-01 00:00:00.000 | 1134 |
| 12 | 2017-09-01 00:00:00.000 | 159 |
| 1 | 2018-09-02 00:00:00.000 | 130 |
| 5 | 2018-09-02 00:00:00.000 | 150 |
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
;
| id | last_balance_date | last_balance | prev_balance_date | prev_balance |
|---|---|---|---|---|
| 1 | 2018-10-02 00:00:00.000 | 115 | 2018-10-01 00:00:00.000 | 111 |
| 5 | 2018-09-02 00:00:00.000 | 150 | null | null |
| 12 | 2017-10-01 00:00:00.000 | 1134 | 2017-09-01 00:00:00.000 | 159 |
-- ПОСМОТРИТЕ как работает функция ROW_NUMBER() !!!
SELECT
b.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
FROM balance b
| id | balance_date | balance | r |
|---|---|---|---|
| 1 | 2018-10-02 00:00:00.000 | 115 | 1 |
| 1 | 2018-10-01 00:00:00.000 | 111 | 2 |
| 1 | 2018-09-02 00:00:00.000 | 130 | 3 |
| 5 | 2018-09-02 00:00:00.000 | 150 | 1 |
| 12 | 2017-10-01 00:00:00.000 | 1134 | 1 |
| 12 | 2017-09-01 00:00:00.000 | 159 | 2 |