Выборка агрегированных значений с учетом null?

Question

[silverxak]

Всем привет!

Есть таблица перемещений книг с полки на полку допустим:

create table moves (
    moves_id int not null identity,
    book_id int not null,           --Книга
    rack_id int not null,           --Стеллаж
    board_id int not null,          --Полка
    date_from datetime not null,    --Дата поступления на стеллаж и полку (равно предыдущему date_to)
    date_to datetime null,          --Дата забора со стеллажа и полки (равно предыдущему date_from)
    primary key (moves_id)
)

Нужно для каждой книги вывести:

book_id     - Книгу
rack_id     - Стеллаж
date_from   - дату поступления на стеллаж вне зависимости от полки
date_to     - дату забора со стеллажа вне зависимости от полки

При этом нужно учесть что перемещения книг могут осуществляться как со стеллажа на стеллаж, так и в рамках одного стеллажа: с полки на полку.

Если date_to равно null - значит книга до сих пор лежит на этом стеллаже и полке.

Вот тестовые данные на примере одной книги:

insert into moves (book_id, rack_id, board_id, date_from, date_to) values
(8000, 56, 20, '01.02.2018 13:40:20.000', '21.02.2018 14:30:00.000'),
(8000, 56, 21, '21.02.2018 14:30:00.000', '10.03.2018 19:39:00.000'),
(8000, 56, 22, '10.03.2018 19:39:00.000', '15.03.2018 12:20:00.000'),
(8000, 60, 40, '15.03.2018 12:20:00.000', '16.03.2018 14:00:00.000'),
(8000, 60, 41, '16.03.2018 14:00:00.000', '19.03.2018 17:30:00.000'),
(8000, 60, 42, '19.03.2018 17:30:00.000', '01.04.2018 10:40:00.000'),
(8000, 56, 23, '01.04.2018 10:40:00.000', '05.04.2018 20:10:00.000'),
(8000, 56, 24, '05.04.2018 20:10:00.000', null)

Пробовал по разному агрегировать, и через cte и иначе не выходит, зашел в тупик, подскажите решение, заранее спасибо.

Answer 1

[alexalexes]

Сможете воспроизвести Oracle выражение Rank() over (partition by ... order by ...) в MS SQL, то будет легко.

select last_move.book_id, last_move.rack_id, last_move.board_id,  -- последнее местонахождение книги
           first_move.date_from, -- дата первого появления на складе
           last_move.date_to -- дата последнего перемещения
from
-- подзапрос, получаем первые записи появления каждой книги на складе
(select *
   from (select t.*, rank() over (partition by t.book_id order by t.date_from) rnk
              from table t)
where rnk = 1) first_move

join
-- подзапрос, получаем последние записи перемещения каждой книги на складе
(select *
   from (select t.*, rank() over (partition by t.book_id order by t.date_from DESC) rnk
              from table t)
where rnk = 1) last_move

-- сопоставляем записи
on first_move.book_id = last_move.book_id

Comments

[Евгений]

Rank() = ROW_NUMBER()

[Freeman]

В MS SQL 2008 уже появились аналитические функции, если не путаю.

[d-stream]

Vapaamies, с 2005