#sql #sql-server
#sql #sql-сервер
Вопрос:
Я работаю в проекте, и мне нужна помощь для конкретного запроса в SQL (я работаю в SQL Server Management Studio). У меня есть таблица; входные данные таблицы — это журнал местоположения судна. Я хочу знать, сколько рейсов совершает конкретный корабль за определенный промежуток времени.
Таблица имеет следующий формат:
--------- ------------------- --------- ---------------------
| ID (PK) | Date (dd/mm/yyyy) | Ship ID | Destination ID (FK) |
--------- ------------------- --------- ---------------------
| 5 | 02-02-2019 | 5 | 6 |
| 6 | 02-02-2019 | 6 | 3 |
| 7 | 03-02-2019 | 5 | 2 |
--------- ------------------- --------- ---------------------
Таблица назначения имеет тип атрибута:
1, если это начальная точка
2 if — точка доставки.
Я выполнил формулу в Excel, которая проверяет, была ли предыдущая точка началом (type = 1), а текущая точка — доставкой (type = 2). в этом случае это один цикл, я не знаю, как я могу это сделать с помощью SQL-запроса
У вас есть несколько советов по выполнению этого запроса?
Комментарии:
1. Что, если конечная точка периода находится между отправлением и прибытием? Это учитывается или нет?
2. Этого не происходит. Корабль покидает начальную точку и через некоторое время он должен вернуться в начальную точку, это цикл. Мне нужно подсчитать, сколько циклов выполняет корабль.
3. Да, но что, если конечная точка периода, в котором вы хотите подсчитать циклы, находится между временем отправления и временем прибытия? Таким образом, «половина» цикла находится в периоде. Что делать тогда?
4. На самом деле этого не произошло, когда Корабль покидает стартовую точку, он должен направиться в пункт доставки. Вот пример (я не знаю, как добавить таблицу jeje 😅, перейдите по этой ссылке: pastebin.com/qSFjW4CS ) Я не знаю, является ли «Циклы» правильной работой для использования 🤔 Это могут быть «круги»
5. Итак, сколько циклов в период с 03-02-2019 по 06-02-2019?
Ответ №1:
Мне кажется, что вы действительно хотите подсчитать все случаи, когда пункт назначения имеет тип 1 и где существует другой пункт назначения с типом 1 для того же корабля.
Сначала соедините пункты назначения и ограничьте данные типом 1 и требуемым периодом. Для удобства я поместил это в CTE. Из этого получите все записи, в которых существует предыдущая запись для того же корабля. Сгруппируйте результат по кораблю и получите количество.
Вы должны получить что-то похожее на:
WITH x
AS
(
SELECT t1.id,
t1.date,
t1.ship_id,
t1.destination_id
FROM table1 t1
INNER JOIN table2 t2
ON t2.id = t1.destination_id
WHERE t2.type = 1
AND t1.date >= '2019-01-01'
AND t1.date < '2020-01-01'
)
SELECT x1.ship_id,
count(*)
FROM x x1
WHERE EXISTS (SELECT *
FROM x x2
WHERE x2.ship_id = x1.ship_id
AND (x2.date < x1.date
OR x2.date = x1.date
AND x2.id < x1.id))
GROUP BY x1.ship_id;
Ответ №2:
У корабля есть начальная точка. Он отправляется в пункт назначения, затем, возможно, в другие пункты назначения, затем возвращается к начальной точке. Один круг. Затем он снова уходит… пока он не вернется снова. Второй круг и т.д.
Если мы посмотрим на определенный диапазон дат, может случиться так, что мы увидим начальную точку в первый день или корабль уже находится в пути, и мы его отсюда не видим.
Итак, чтобы увидеть, как часто корабль возвращался в диапазоне дат (т. Е. Завершил цикл), мы должны подсчитать начальные точки корабля на следующий день после первого дня диапазона дат.
select ship_id, count(*)
from location
where location_date between dateadd(day, 1, @startdate) and @enddate
and destination_id in (select destination_id from destination where type = 1)
group by ship_id;