Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Добрый день, помогите реализовать запрос.
Есть таблица с полями FlightNumber, OperationTime, BorderRoutingID, что соответственно Номер авто, время операции и направление движения (въезд или выезд).
Нужно вытащить все номера авто, которые два и более раз подряд выехали и между этими выездами не было въезда.
27 янв 18, 16:18    [21143807]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
invm
Member

Откуда: Москва
Сообщений: 8490
Функция LAG (или LEAD) вас спасет.
27 янв 18, 16:59    [21143840]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28178
k.I.titov
Есть таблица с полями FlightNumber, OperationTime, BorderRoutingID, что соответственно Номер авто, время операции и направление движения (въезд или выезд).
Нужно вытащить все номера авто, которые два и более раз подряд выехали и между этими выездами не было въезда.
Хм, запрос то простой, учебная задача?
select *
from [Есть таблица] as t1
    cross apply (
        select top 1 *
        from [Есть таблица] as t2
        where t2.FlightNumber = t1.FlightNumber
                and t2.OperationTime > t1.OperationTime
        order by t2.OperationTime ASC
    ) as t2
where t1.BorderRoutingID = Выехали
    and t2.BorderRoutingID = Выехали
27 янв 18, 16:59    [21143841]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Сейчас попробую. Нет не учеба, на работе) sql запросам не обучался, но начальник задачи ставит. Большую часть в состоянии выполнить, в большинстве случаев перевожу все нужные данные в отдельные временые таблицыи их, сравниваю, а тут так не выходит...
27 янв 18, 17:35    [21143869]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
alexeyvg,
Тут еще нужно учесть что было два выезда подряд, без въезда... и задать промежуток времени, так как база у меня в сотни миллионов таких машин и если без ограничения по времени запущу, повешу базу. Еще добавил with (nolock)
27 янв 18, 17:41    [21143874]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Пока так
+
use packdb
Select * from spk_Vpassenger_WithoutMV as t2 with (nolock)
Where t2.FlightNumber = t1.FlightNumber and t2.OperationTime>t1.OperationTime order by t2.OperationTime ASC)
as t2 where t1.OperationTime >'2018-01-27 17:00:00.000' and t2.OperationTime>'2018-01-27 17:00:00.000' and t1.BorderCrossingID =1 and t2.BorderCrossingID =2
27 янв 18, 17:53    [21143893]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
На выходе пустой результат...
27 янв 18, 17:55    [21143899]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Убрал время в конце, так же пустой результат
27 янв 18, 17:55    [21143900]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Ошибочка,как вот так сейчас
+
use packdb
Select * from spk_Vpassenger_WithoutMV as t1 with (nolock)
cross apply (select top 1 * from spk_Vpassenger_WithoutMV as t2 with (nolock) where t2.FlightNumber = t1.FlightNumber and t2.OperationTime>t1.OperationTime order by t2.OperationTime ASC)
as t2 where t1.OperationTime >'2018-01-27 17:00:00.000' and t2.OperationTime>'2018-01-27 17:00:00.000' and t1.BorderCrossingID =1 and t2.BorderCrossingID =2
27 янв 18, 18:00    [21143910]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28178
k.I.titov
alexeyvg,
Тут еще нужно учесть что было два выезда подряд, без въезда... и задать промежуток времени, так как база у меня в сотни миллионов таких машин и если без ограничения по времени запущу, повешу базу. Еще добавил with (nolock)
Мой запрос как раз показывает 2 выезда подряд, без въезда.
Промежуток времени можно задать, добавив условие для таблицы t1
Скорость будет зависеть от наличия правильного индекса и от указанного диапазона, и не будет зависеть от того, сколько всего миллиардов записей в таблице.
27 янв 18, 18:23    [21143937]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Хорошо. Буду пробовать уже в понедельник, спасибо за помощь)
27 янв 18, 18:29    [21143948]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28178
k.I.titov
Ошибочка,как вот так сейчас
Да, в общем правильно.
Единственно, условие t2.OperationTime > ... лишнее, запутает сервер и вас.
Для скорости лучше сделать индекс по FlightNumber asc, OperationTime desc
И ещё один фильтрованный по OperationTime asc where BorderCrossingID = 1
27 янв 18, 18:33    [21143954]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
alexeyvg,
Сейчас на работе, выполняю так каки указано выше, убрал t2.operationtime, и все равно результат нулевой... убираю время совсем, все равно результат ноль....
29 янв 18, 10:23    [21146268]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
alexeyvg,

Я понял... я не совсем точно объяснил. В этой таблице содержится много машин и мне нападет найти такие которые два раза подряд выехали, но между ними сможет бывать уйма машин. Если я правильно понимаю принцип работы этого запроса, то он сравнивает каждую строку со следующей, по этому и результат ноль. А мне нужно, что бы он нашел два номера с направлением въезд без выезда между ними, с учетом того, что между этими выездами может пройти любое время
29 янв 18, 10:28    [21146278]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Извиняюсь за опечатки, печатаю с телефона
29 янв 18, 10:29    [21146284]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
Добрый Э - Эх
Guest
k.I.titov,

ну так и сравнивай машину с самой собой в подзапросе.... а не со всему имеющимися в таблице
29 янв 18, 10:30    [21146289]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Добрый Э - Эх,

А как это реализовать?
29 янв 18, 10:53    [21146380]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
petre
Member

Откуда: Кривой Рог
Сообщений: 41
k.I.titov,

В запросе уже сравнивается машина сама с собой:
t2.FlightNumber = t1.FlightNumber

Только не понятно что за поля:
t1.BorderCrossingID =1 and t2.BorderCrossingID =2
?
В начале ведь говорилось о направлении движения BorderRoutingID
Если BorderCrossingID так же определяет направление движения, то необходимо чтобы t1.BorderCrossingID и t2.BorderCrossingID были одинаковы и определяли событие выезда.
29 янв 18, 11:07    [21146455]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
petre,

Я попутал... верно borderroutingid, 1 выезд 2 въезд
29 янв 18, 11:10    [21146474]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
petre,
И у себя в sql так же опечатался.... Сейчас проверяю.
29 янв 18, 11:12    [21146486]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
petre,

Запустил, результат есть, но проверка показала что номера которые есть в результате, то есть машины въезжали между выездами...
29 янв 18, 11:21    [21146550]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
Добрый Э - Эх
Guest
k.I.titov,

так можно долго "тыкать пальцем в небо"....
приводи репрезентативный юзабильный набор тестовых данных, свой вариант запроса и "проблемные" данные, которые твой запрос отрабатывает не так, как ты того ожидал....
29 янв 18, 11:24    [21146566]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
То есть он выдает просто номера, которые имеют два выезда вне зависимости от наличия въезда
29 янв 18, 11:27    [21146579]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
k.I.titov
Member

Откуда:
Сообщений: 17
Добрый Э - Эх
k.I.titov,

так можно долго "тыкать пальцем в небо"....
приводи репрезентативный юзабильный набор тестовых данных, свой вариант запроса и "проблемные" данные, которые твой запрос отрабатывает не так, как ты того ожидал....

Я мог бы с радостью это сделать, но с на это можно. Попробую так объяснить. Грубо говоря в таблице три записи все трис номером А 111 А 123, время у первой записи 2018.01.01 10:00:00.000, у второй 2018.01.01 11:00:00.000, у третьей 2018.01.01 12:00:00.00, направление соответственно выезд, въезд и выезд то, есть 1,2,1. Запустив этот запрос он увидит что номер А 111 А 123 выехал два раза и выдаст его в результате, не учитывая того, что между ними был въезд. А мне нужно так, что бы он выдал его только при таком раскладе, если у них будет последовательность направлений 1,1,2 соответственно выезд, выезд и въезд
29 янв 18, 11:40    [21146626]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
Добрый Э - Эх
Guest
k.I.titov,

ну, на нет - и суда нет. Тогда можно лишь сказать, что ошибка в 17-й строке. А правильный ответ - 42...
29 янв 18, 11:47    [21146660]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
S_A_V_e
Member

Откуда:
Сообщений: 176
Попробуйте вот такой вариант:
declare @carData table(Id int,FlightNumber int, OperationTime datetime, BorderRoutingID int)

insert into @carData
select 1,1,'2018-01-01',1
union all
select 2,1,'2018-01-02',1
union all
select 3,1,'2018-01-03',2
union all
select 4,1,'2018-01-04',1
union all
select 5,2,'2018-01-01',1
union all
select 6,2,'2018-01-02',2
union all
select 7,2,'2018-01-03',1
union all
select 8,2,'2018-01-04',2

;with
outs as(select * from @carData where BorderRoutingID = 1  ), -- только выезды
ins as( select * from @carData where BorderRoutingID = 2 ), -- только въезды
inOutData as( select 
			   o.*,
			   ( select min(oi.OperationTime) -- ближайший выезд (более поздний по времени)
				 from outs oi 
				 where 
				 oi.FlightNumber = o.FlightNumber
				 and
				 oi.OperationTime > o.OperationTime) as nearestOut,
			   ( select min(i.OperationTime) -- ближайший въезд (более поздний по времени)
				 from ins i
				 where 
				 i.FlightNumber = o.FlightNumber
				 and
				 i.OperationTime > o.OperationTime) as nearestIn
			   from outs o -- проход по всем выездам      
			   ),
result as( select 
           * 
           from inOutData 
		   where 
		   nearestOut < nearestIn -- берём записи где выезд раньше въезда
		   )
select * from result
29 янв 18, 14:25    [21147305]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
invm
Member

Откуда: Москва
Сообщений: 8490
k.I.titov,

Ответы не пробовали читать?
+
declare @carData table(Id int, FlightNumber int, OperationTime datetime, BorderRoutingID int);

insert into @carData
select 1,1,'20180101',1
union all
select 2,1,'20180102',1
union all
select 3,1,'20180103',2
union all
select 4,1,'20180104',1
union all
select 5,2,'20180101',1
union all
select 6,2,'20180102',2
union all
select 7,2,'20180103',1
union all
select 8,2,'20180104',2;

with t as
(
 select
  FlightNumber, OperationTime, BorderRoutingID,
  lag(BorderRoutingID) over (partition by FlightNumber order by OperationTime) as PrevBorderRoutingID
 from
  @carData
)
select distinct
 FlightNumber
from
 t
where
 BorderRoutingID = 1 and
 PrevBorderRoutingID = 1;
29 янв 18, 14:43    [21147376]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28178
k.I.titov
petre,

Запустил, результат есть, но проверка показала что номера которые есть в результате, то есть машины въезжали между выездами...
Дык покажите запрос то.
Я вам дал правильный готовый запрос сразу же, а у вас там появляются какие то "t1.BorderCrossingID =1 and t2.BorderCrossingID =2"
k.I.titov
Я мог бы с радостью это сделать, но с на это можно. Попробую так объяснить. Грубо говоря в таблице три записи все трис номером А 111 А 123, время у первой записи 2018.01.01 10:00:00.000, у второй 2018.01.01 11:00:00.000, у третьей 2018.01.01 12:00:00.00, направление соответственно выезд, въезд и выезд то, есть 1,2,1. Запустив этот запрос он увидит что номер А 111 А 123 выехал два раза и выдаст его в результате, не учитывая того, что между ними был въезд. А мне нужно так, что бы он выдал его только при таком раскладе, если у них будет последовательность направлений 1,1,2 соответственно выезд, выезд и въезд
Да поняли все задачу.
Мой запрос как раз учитывает, что между двумя выездами не должно быть въездов.
Вы очевидно что то напутали в запросе.
Покажите же его.
29 янв 18, 15:11    [21147495]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, искал похожую тему и наткнулся на Ваш запрос. Возник вопрос - в какой части Вашего запроса исключается въезд между выездами? Насколько я понимаю оператор cross apply выполняет соединение двух таблиц, при этом каждая строка из левой таблицы сочетается с каждой строкой из правой. Если не трудно, поясните, пожалуйста.
7 фев 19, 19:03    [21803807]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28178
masql60
alexeyvg, искал похожую тему и наткнулся на Ваш запрос. Возник вопрос - в какой части Вашего запроса исключается въезд между выездами? Насколько я понимаю оператор cross apply выполняет соединение двух таблиц, при этом каждая строка из левой таблицы сочетается с каждой строкой из правой. Если не трудно, поясните, пожалуйста.
Ну, тут лучше не говорить в терминах "соединение", "левой", "правой". Я от этой математики сразу тупею :-)

Рассмотрим запрос:
select *
from [Есть таблица] as t1
    cross apply (
        select top 1 *
        from [Есть таблица] as t2
        where t2.FlightNumber = t1.FlightNumber
                and t2.OperationTime > t1.OperationTime
        order by t2.OperationTime ASC
    ) as t2
where t1.BorderRoutingID = Выехали
Логически (не будем вдаваться, как его выполняет сервер), он для каждой записи, у которой тип "Выехали", ищет ближайшую к ней (по OperationTime) запись, причём с любыми типами BorderRoutingID. И просто выводит список.

А потом мы, добавляя условие "t2.BorderRoutingID = Выехали", оставляем из результата только те пары записей, в которых вторая запись будет типа Выехали

Соответсвенно, въезд между выездами исключается как раз вот этим дополнительным условием.

Вот если бы условие "t2.BorderRoutingID = Выехали" я поставил внутри cross apply, то я просто нашёл бы для каждой строки ближайшую к ней запись "Выехали", независимо от того, если ли какие то другие записи между ними, или нет.
А вот когда я фильтрую снаружи, то уже фильтрую общий результат.

Единственно, замечу, что для реального применения нужно ещё внутрь добавить условие "t2.BorderRoutingID in ( Выехали, Въехали )", потому что там, наверное, будут другие операции, которые будут искажать результат.

Потому что, по сути, мой изначальный запрос не как у автора "Нужно вытащить все номера авто, которые два и более раз подряд выехали и между этими выездами не было въезда.", а "все номера авто, для которых после выезда сразу следует ещё один выезд", а это не одно и то же.

Итого, получается так:
select *
from [Есть таблица] as t1
    cross apply (
        select top 1 *
        from [Есть таблица] as t2
        where t2.FlightNumber = t1.FlightNumber
                and t2.OperationTime > t1.OperationTime
                and t2.BorderRoutingID in ( Выехали, Въехали )
        order by t2.OperationTime ASC
    ) as t2
where t1.BorderRoutingID = Выехали
    and t2.BorderRoutingID = Выехали
7 фев 19, 19:37    [21803820]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, спасибо за пояснение. Я не учел то, что условие задаете вне оператора cross apply. Обрисую свою ситуацию. Есть две таблицы personal и operations. Таблица personal состоит из столбцов Id и FIO, таблица operations - Id, OperationName, OperationDate, FIOId. Из таблицы personal я вытягиваю FIO, из таблицы operations - OperationDate, OperationName, использую оператор left join при объединении. Так вот мне требуется выбрать только те записи, которые подпадают под следующее условие - сотрудник дважды вышел, но не входил. Приведу пример:
Иванов Иван Иванович Вход 01.01.2017 10:00
Петров Петр Петрович Вход 01.01.2017 10:05
Иванов Иван Иванович Выход 01.01.2017 13:00
Петров Петр Петрович Выход 01.01.2017 13:05
Петров Петр Петрович Вход 01.01.2017 14:00
Иванов Иван Иванович Выход 01.01.2017 18:00
Петров Петр Петрович Выход 01.01.2017 18:05
На выходе я хочу получить:
Иванов Иван Иванович Выход 01.01.2017 13:00
Иванов Иван Иванович Выход 01.01.2017 18:00
Использую Ваш запрос. Вот что получилось:
SELECT p.FIO, o.OperationName, o.OperationDate
FROM personal AS p LEFT JOIN operations AS o ON p.Id = o.FIOId
CROSS APPLY (SELECT TOP 1 p2.FIO, o2.OperationName, o2.OperationDate FROM personal AS p2 LEFT JOIN operations AS o2 ON p2.Id = o2.FIOId WHERE o2.OperationDate > o.OperatinDate AND p2.FIOId = p.FIOId
ORDER BY o2.OperationDate ASC) AS t2
WHERE o.OperationName = 'Выход' AND t2.OperationName = 'Выход' AND o.OperatinDate BETWEEN '01.01.2017 00:00' AND '01.01.2017 23:59'
Получаю все записи с выходом. Помогите, пожалуста, разобраться.
8 фев 19, 09:30    [21804026]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
zby
Member

Откуда:
Сообщений: 32
masql60,

Пожалуйста, форматируйте текст хоть как-нибудь,

а то прям аж кровь из глаз ))
8 фев 19, 09:56    [21804059]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
zby, я извиняюсь за неудобство. Пишу с телефона, не судите строго.
8 фев 19, 10:03    [21804067]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28178
masql60
Помогите, пожалуста, разобраться.
ИМХО вы напутали с Id и FIOId

И вообще, запрос нужно упростить, вместо первого LEFT JOIN достаточно JOIN, а в CROSS APPLY вообще достаточно одной таблицы.
SELECT p.FIO, o.OperationName, o.OperationDate
FROM personal AS p 
	JOIN operations AS o 
		ON p.Id = o.FIOId
	CROSS APPLY (
		SELECT TOP 1 o2.OperationName
		FROM operations AS o2 
		WHERE o2.OperationDate > o.OperatinDate 
			AND o2.FIOId = o.FIOId 
		ORDER BY o2.OperationDate ASC
	) AS t2 
WHERE o.OperationName = 'Выход' 
	AND t2.OperationName = 'Выход' 
	AND o.OperatinDate BETWEEN '01.01.2017 00:00' AND '01.01.2017 23:59'
8 фев 19, 23:18    [21804981]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, согласен с Вами, что объединение во встроенном запросе лишнее. Но что я напутал? Я так и не заметил разницы, помимо того, что Вы исключили одно объединение.
9 фев 19, 18:11    [21805343]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
Если вы имеете это - WHERE o2.OperationDate > o.OperatinDate AND p2.FIOId = p.FIOId, то это моя опечатка с телефона.
9 фев 19, 18:15    [21805344]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28178
masql60
Если вы имеете это - WHERE o2.OperationDate > o.OperatinDate AND p2.FIOId = p.FIOId, то это моя опечатка с телефона.
Да, имею в виду это.

Ну, что можно сказать? Когда будете у компа, запостьте сюда скрипт создания ваших таблиц, с вашими тестовыми данными "Иванов Иван Иванович Вход 01.01.2017 10:00" и т.п., отладим ваш запрос. Благо скрипт можно получить в SSMS, а тестовые данные вы уже тут запостили, так что это несложно.

А то получается "лечение по пересказу", мало ли, что вы в телефоне набрали, может, там ещё есть какое то отличие в запросе, достаточно ведь не там скобку поставить.
9 фев 19, 23:33    [21805471]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, да, я прекрасно Вас понимаю. Тогда я в понедельник создам тестовую базу, и отправлю результат.
10 фев 19, 12:04    [21805593]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, учел все Ваши замечания, и получилось. Спасибо большое за помощь!
12 фев 19, 14:09    [21807396]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить