Как получить ближайшее значение к параметру запроса в SQL?
У меня есть таблица (shop_staff_relationvm), которая сохраняет отношения между персоналом и магазином.
Он имеет 3 столбца: id_shop, id_staff и change_date.
id_staff=> внешний ключ персоналаid_shop=> внешний ключ магазинаchange_date==> всякий раз, когда создается новое отношение персонала к магазину, я сохраняю дату настройки (магазин персонала может меняться несколько раз)
3 столбца выше представляют встраиваемый первичный ключ для таблицы shop_staff_relationvm.
В запросе у меня есть 2 параметра запроса: idShop и дата.
Я пытаюсь получить список сотрудников, принадлежащих определенному магазину (idShop) в определенном месяце (requestMonth).
Поэтому мне нужно получить список сотрудников, когда requestMonth находится между первой датой отношений между персоналом и магазином и последней датой отношений.
Вот пример:
STAFF | SHOP | CHANGE DATE
----------+----------+-------------
Staff A | Shop 1 | 10-01-2019
Staff A | Shop 2 | 10-03-2019
Staff A | Shop 3 | 10-05-2019
Предполагая, что у нас есть requestMonth = 02-2019
Тогда: в магазине 1 будет персонал А, в магазине 2 не будет персонала, а в магазине 3 не будет персонала.
Предполагая, что у нас есть requestMonth = 03-2019
Тогда: в магазине 1 не будет персонала, в магазине 2 будет персонал А, а в магазине 3 не будет персонала.
Предполагая, что у нас есть requestMonth = 06-2019
Тогда: в магазине 1 не будет персонала, в магазине 2 не будет персонала, а в магазине 3 будет персонал А.
Вот формула, которую я придумал:
first date of relation <= requestMonth <= first date of next relation
У меня нет проблем с получением первой даты отношения, но у меня есть проблема с получением первой даты следующего отношения (которое я пришел к выводу, что это следующее ближайшее значение к первой дате отношения).
PS: я использую собственный запрос JPA
Я попытался использовать order by (change_date - first date of relation), чтобы получить первое ближайшее значение к первой дате отношения. но это всегда давало мне максимальное значение (в приведенном выше примере я всегда получал 05-2019 месяц)
Я также пытался использовать LEAD, чтобы получить следующее ближайшее значение. Однако, используя приведенный выше пример:
For
requestMonth = 02-2019: the LEAD was equal to 03-2019 => correctFor
requestMonth = 03-2019: the LEAD was equal to 01-2019 => wrong => It should be05-2019
Это весь запрос:
SELECT s.* FROM staffvm s INNER JOIN shop_staff_relationvm ss ON ss.id_staff = s.id_staff WHERE ss.id_shop = 2 and
(CASE WHEN ((SELECT MAX(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff) > to_date('2019-02-16', 'yyyy-MM')
and (SELECT MIN(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff) <= to_date('2019-02-16', 'yyyy-MM') )
THEN
((SELECT ((LEAD (to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) OVER (ORDER BY ss.id_staff DESC)) > to_date('2019-02-16', 'yyyy-MM') )
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop !=2 LIMIT 1)
and
(SELECT (MIN(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) <= to_date('2019-02-16', 'yyyy-MM'))
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop = 2) )
ELSE
(SELECT (to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM') <= to_date('2019-02-16', 'yyyy-MM'))
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop = 2 )
END);
Часть запроса, с которой у меня проблема:
((SELECT ((LEAD (to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) OVER (ORDER BY ss.id_staff DESC)) > to_date('2019-02-16', 'yyyy-MM') )
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop !=2 LIMIT 1)
and
(SELECT (MIN(to_date(to_char(ss.change_date, 'yyyy-MM'), 'yyyy-MM')) <= to_date('2019-02-16', 'yyyy-MM'))
FROM shop_staff_relationvm ss WHERE ss.id_staff = s.id_staff and ss.id_shop = 2))
да, потому что мне тоже нужны данные из таблицы персонала
Ответы 1
Если вы хотите получить посох на конкретную дату, вы можете использовать distinct on.
select distinct on (ss.id_staff) ss.*
from shop_staff_relationvm ss
where ss.change_date <= '2019-02-01'::date
order by ss.id_staff, ss.change_date desc;
С индексом shop_staff_relationvm(id_staff, change_date) это, вероятно, лучшее из возможных решений.
Чтобы определить, кто находится в конкретном магазине, используйте подзапрос:
select s.*
from (select distinct on (ss.id_staff) ss.*
from shop_staff_relationvm ss
where ss.change_date <= '2019-02-01'::date
order by ss.id_staff, ss.change_date desc
) s
where s.shop_id = 2;
Ваш вопрос касается одной таблицы, но ваш код включает другие таблицы.