Так, стоп. а почему с 21:53?

потому что это оно вошло в фильтр по часу where dif<1

нет. вошло 22:27. у него разница меньше часа с 21:53. а у 21:53 с 12:23 разница 9 часов, оно ожидаемо не вошло

все верно, должны войти записи 24/07/2021 22:27:42 и 24/07/2021 21:53:52

12:23 и тем более 9:38 войти не должны

21:53 не должно войти входят записи у которых дата - лаг(дата) меньше часа дата=22:27 лаг(дата)=21:53 - меньше часа, входит дата=21:53 лаг(дата)=12:23 - девять часов, не входит

У вас тут dt без to_date

что верно? вы говорите что 21:53 должно войти я говорю вам что оно не дожно войти ровно по тому условию которое вы написали в запросе

где нужно to-date? в lag (dt ignore nulls,1,dt) ??? я просто после вашего замечания добавила в сортировку

Везде нужно

меньше часа, оно и вошло, что не так? я же не указала больше часа вытяните мне

сейчас попробую

вы выводите строки, у которых разница между текущей и предыдущей датой менее часа. у 22:27 она менее часа (предыдущая дата 21:53), поэтому строка попала в выборку. у 21:53 она более часа (предыдущая дата 12:23, разница 9 часов), поэтому строка не попала в выборку. я не знаю что еще тут сказать 🤷‍♂️

так мой вопрос как раз в ттом, почему входит 9:38 и как это можно обыграть

9-38 входит потому что она сравнивается с самой собой (потому что это самая ранняя дата; по умолчанию лаг вернул бы для нее нулл, но вы написали его так, что он вместо нулла возвращает саму дату 9-38). результат сравнения ноль минут ноль секунд, это меньше часа, строка попадает в выборку

я поняла, что она по умолчанию достает самую раннюю дату. я это в своем запросе и указала, но мой вопрос здесь заключался в том, можно ли это было как-то обыграть доставая граничную последнюю дату. это в принципе и было моим вопросом

нет, она не достает по умолчанию самую раннюю дату, она достает саму себя то что вы написали это аналог nvl(lag(dt) over(... ), dt)

а почему именно эту дату по умолчанию? потому что она самая первая так?

на пальцах lag(dt, 1, dt) over(partition by sender order by dt) читается так: "найди предыдущую dt для sender из текущей строки; если предыдущей dt нет, используй вместо предыдущей dt значение dt из текущей строки"

а что у нас является текущей строкой? когда если оставить lag(dt) без оффсета и заполнения по умолчанию выходит текущей строчкой только 348 24.07.2021 22:27:42 24.07.2021 21:53:52 по какой логике он по умолчанию принимает 24/07/2021 9:38:28? потому что она самая первая? или как ? ну в принципе как бы оно правильно не называлось, вопрос мой заключался как можно было обыграть установив по умолчанию значение которое нужно

да просто написать не <1 а >0 и <1

попробую границы выставить

тоже на пальцах у вас по строкам идет цикл, на каждом шаге цикла выполняется функция lag. для "текущей" строки (для той, на которой сейчас остановился цикл) функция lag находит "предыдущую" строку, и берет из нее значение dt. если "предыдущей" строки не существует (если "текущая" самая первая) - по умолчанию возвращается null. если задать для для функции lag третий аргумент dt, то в ситуации, когда "предыдущей" строки нет, она возьмет dt из "текущей". это то, что произошло у вас - для 9-38 "предыдщей" строки нет, поэтому dt взяли из "текущей". разница между ними - ноль, поэтому строка в выборке.

хорошо) это я поняла, в общем возвращаясь к своему вопросу. ничего с этим поделать таки нельзя?

с чем с этим?

с тем чтобы в результате вытянуть дату граничную и строку с данными ей соответствующую, тобишь 24/07/2021 21:53:52 тут дело вот в чем, мне как бы нужна эта граничная дата, так как мне нужно выловить подозрительные транзакции в периоде часа и сумму верхней границы тоже

да 3 параметр пусть уберет. а так и второй не нужен

чтобы вытянуть 21:53 вам нужно изменить условие, которое исключает 21:53. условие на разницу в час. забудьте о лаг и 9 утра, они тут ни причем вообще

да, лаг тут не помощник вероятно.. но чем тогда, чувствую, что код тогда увеличится и будет долгим

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

Ладно, я на pyspark в питоне как-нибудь обыграю. Спасибо

ну и результат будет "какой-нибудь"

Ну согласитесь, хотя вы со мной не согласитесь, оракл свой инструментал в оконных смещениях сжал, оконные границы со смещением unbounded и between тут не применительны, по умолчанию значение ставить с уточнением в смещении не позволяет…

я ещё раз повторю: покажите, как должны выглядеть данные на входе, и на выходе. при необходимости ответьте на уточняющие вопросы "почему именно так?", если они будут. после этого мы всем чятом подберём вам правильные инструменты для получения требуемого результата из исходного, и заодно ответим как на вопросы о применимости оконных границ со смещением, так и на все остальные. пока этого нет - вы разговариваете умными словами сама с собой. я вас не понимаю. рискну предположить что большинство присутствующих тоже.

Что значит "сжал инструментал" и что значит "по умолчанию значение ставить с уточнением в смещении"?

Почему нужна верхняя граница времени - потому что предположительно в это время проходит подозрительная транзакция с определенной суммой в записи, которая должна быть вычислена. Таблица со значениеми идёт со временем, с суммой и отправителем. Подозрительным отправитель считается отправляющий в периоде меньше часа и в сумме не превышающий заявленную от заказчика. Поэтому я здесь применила лаг, чтобы сначала вычислить разницу по времени, далее обнаружила, что сумма не учитывается у верхнего периода времени подозрительного отправителя, то есть он тупо не попадает в расчёт (хотя должен). Так и пришли к тому что в значении по умолчанию устанавливается только как вы говорите только «текущая дата». Вот, что ещё мне пояснить? Так получается задала вопрос, а в итоге оправдываюсь, что не знаю

Можно перефразирую? "Сумма транзакций, отстоящих друг от друга менее чем на час не должна превышать определенного предела." Так?

Она должна быть учтена, да То есть: Отправитель Время Сумма 7 21:53 328 7 22:47 348 Когда мы высчитываем используя лаг, запись с суммой 348 в расчёт попадает, грубо говоря, а 328 нет.

ура

А поскольку по дефолту lag(dt,1,dt) в dt попадает «текущая дата» - это дата, самая первая запись в таблице соответствующая данному отправителю и сумма соответствующая это строчке не та.

Ну так и вычислите для каждой своей записи сумму всех прочих, отстоящих не далее требуемого интервала. А потом профильтруйте по значению.

Ну извиняйте, если что-то не так пояснила

Теперь вы тут мне поясните, пожалуйста, что вы имели ввиду? Сумму посчитать несложно, если будут вытянуты соответсвующие ей записи. Вы какую функцию имели ввиду? Numstodinterval или как он там называется, я не помню уже

Вообще-то я имел в виду банальный join таблицы самой на себя.

А, ну там дальше еще рекурсивный запрос у меня, чтобы начало цепочки определить и конец, поэтому думаю и без джойна справимся

Нужен список всех транзакций, помеченных как входящих в цепочку подозрительных или просто констатация наличия такой цепочки?