for flooders
:: Главная :: Решения :: Статьи :: Сайт М. Дроздова :: Файловый архив :: Книга по VFP 9 :: Русский Help Online :: OFF-LINE Форум
   Л и с о в о д ы   в с е х   с т р а н,  о б ъ е д и н я й т е с ь !!!  

Список Форумов  :: Не фоксом единым
   :: Помощь сайту :: 

Нужна помощь с запросом (аналитические функции, Oracle)
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 28.12.17 10:19:30ОтветитьЦитировать
Всем привет.
Помогите пожалуйста с запросом. Недосып и борьба со змеем не дают сосредоточиться, а работу работать надо.
Есть таблица договоров, предположим, contracts с пк id_contract.
Есть таблица графика платежей, пусть будет schedules, фк - id_contract, sched_date - дата платежа по графику, sched_sum - ожидаемая сумма.
Есть таблица платежей, например, payments. ФК - id_contract, paym_date - дата платежа, paym_sum - сумма платежа.
Даты графика и реальных платежей могут не совпадать. Один платеж может быть меньше или больше суммы ожидаемого платежа. График и платежи между собой связаны только через id_contract.
Необходимо выбрать записи из графика платежей с неполной оплатой или просрочкой платежа на определенную дату.
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
pasha_usue

Сообщений: 2928
Откуда: Е-бург
Дата: 28.12.17 10:25:07ОтветитьЦитировать
Я бы связочку по FIFO пробросил всё-таки. Хотя бы какой регламентной процедурой.
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 28.12.17 11:45:12ОтветитьЦитировать
pasha_usue
Я бы связочку по FIFO пробросил всё-таки
Это не по моей части, за это архитекторы отвечают. :al:

В черновом варианте получается нечто вроде:
  
  select  
    listagg(sched_date, '; ') within group(order by sched_date) sched_dates  
  from (  
        select  
            sched_date  
          , sum(sched_sum) over(order by sched_date rows between unbounded preceding and current row) accum_sum  
        from schedules  
        where id_contract = :p_id_contract  
        )  
  where accum_sum > nvl((select sum(paym_sum) from payments where id_contract = :p_id_contract), 0);
Всем спасибо за помощь.
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
Igor Korolyov

Сообщений: 32006
Дата: 28.12.17 12:16:28ОтветитьЦитировать
Надо было бы тестовых данных дать (содержимое и ожидаемый результат)...
По логике вопрос есть...
Нужно для КАЖДОГО платежа по графику определять была ли на его дату просрочка/недоплата? Или только для КРАЙНЕГО с датой меньше указанной?
Т.е. если "график" был (в условных "днях от старта", это не суть важно)
1 100
5 100
10 100
15 50
20 50
25 50
30 50

а оплаты были
1 100 -- вовремя вся сумма
4 80 -- аванс не полный
6 30 -- просрочка на 1 день, сумма с переплатой
10 40 -- неполная оплата
15 50 -- если считать как доплата к 10-му, то на 15 просрочка, если как оплату по 15-му, то остаётся недоплата по 10-му
16 50 -- погашены все долги до 15-го
20 40 -- неполная оплата за 20-е
26 60 -- погашены долги за 20-е и 25-е, но с просрочкой на 1 день
31 50 -- просто просрочка на 1 день

То какой результат должен быть для разных "отсекающих" дат? Пример для одного договора...

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

Если интересует лишь "состояние на дату" (есть ли на такое то число просроченная задолженность или нет) - тогда аналитика не нужна - достаточно посуммировать все "плановые" до этой даты и все "фактические" и сравнить их.


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 28.12.17 13:53:46ОтветитьЦитировать
Это да. Если бы я был по ту сторону экрана, меня, наверное, интересовала бы более широкая статистика, в т.ч. выделение просроченных на момент расчетной даты платежа, но погашенных на данный момент. Но мне, похоже, повезло. Фильтр даты влияет только на даты договоров, график неоплаченных платежей нужно выбрать на текущий момент (дату расчета). Последний платеж покрывает сначала более ранние просрочки, остатком закрывает текущий платеж, т.е. логика пока простейшая. Иными словами, из графика платежей нужно убрать все записи, которые полностью покрыты общей суммой платежей с сортировкой по датам.
Возможно, потом будет задача на доработку, но пока что так.



Исправлено: Pekpytep, 28.12.17 13:55
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
Igor Korolyov

Сообщений: 32006
Дата: 28.12.17 15:17:15ОтветитьЦитировать
Т.е. тебе нужно сначала взять из графика все платежи до указанной даты, а потом последовательно выкидывать их до достижения текущей суммы оплаты? Получится что останутся только неоплаченные и, возможно, одна частично оплаченная? При том сумма оплаты не ограничивается (ну по крайней мере в твоём запросе этого нет) датами - т.е. берутся ВСЕ оплаты, так?
Если делать это по одному договору, т.е. как у тебя в примере - с параметром p_id_contract, то по идее твой запрос вполне подходит под такую логику. Если потребуется по всем договорам (ну или по части, но не по одному) - придётся вносить patrition by в подзапрос и ограничивающее условие переделать (лучше прописать одним подзапросом подсчёт сумм оплат по договорам, а потом соединить обе части "хитрым способом")...
P.S. только сейчас заметил что и в запросе к "графику" ты не ограничиваешь ничего по дате Т.е. всё работает по полному массиву данных - грубо говоря "на текущий момент времени"...


------------------
WBR, Igor




Исправлено: Igor Korolyov, 28.12.17 15:18
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 28.12.17 15:45:07ОтветитьЦитировать
Igor Korolyov
Т.е. тебе нужно сначала взять из графика все платежи до указанной даты, а потом последовательно выкидывать их до достижения текущей суммы оплаты?
Yep, почти. Список договоров уже ограничен фильтрами дат, подразделений и т.д. и т.п. График и платежи по ТЗ датами не ограничены, т.е. туда попадут в том числе, даты большие сегодняшней, если они есть. Если понадобится ограничить по дате, то глобальных переделок не понадобится - в двух местах добавить в where фильтр по верхней границе даты. Я умышленно все максимально упростил для более легкого понимания. Приведенный кусок кода является коррелирующим подзапросом, а полный текст запроса over 700 строк.
По partition by - согласен, по соединению "хитрым способом" - не очень понятно. Можно подробнее?
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
Igor Korolyov

Сообщений: 32006
Дата: 29.12.17 12:24:11ОтветитьЦитировать
Ну там надо соединить обе части по id_contract, а потом ещё и по твоему условию accum_sum > sum_paym_sum. И не забыть про договора для которых вообще ещё нет оплат - т.е. второй подзапрос по такому id_contract будет пуст - т.е. потребуется LEFT JOIN.
Хотя, в реальном коде второй подзапрос может быть составлен так что вернёт и записи для договоров без платежей - скажем если он связывает таблицу договоров с таблицей платежей по LEFT JOIN - тогда запись по суммарной оплате будет, только через NVL(SUM(paym_sum),0) sum_paym_sum её считать, чтобы с NULL-ом по сумме не мучиться. Тогда можно применить INNER JOIN, и разбить условие - id_contract=id_contract отсавить в ON, а фильтр accum_sum > sum_paym_sum вынести в WHERE - зачастую так оно легче читается/воспринимается... Тут главное не забывать что "смешивание" условий в ON и WHERE "логично" работает лишь для INNER JOIN - для LEFT JOIN по хорошему только в ON нужно все условия писать, ну или городить огороды типа WHERE t1.f1 > t2.f2 OR (t1.f1 > 0 AND t2.f2 IS NULL)... Что и многословно, и чревато ошибками, да и на оптимизатор может негативно повлиять...


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Нужна помощь с запросом (аналитические функции, Oracle)
Гулин Федор

Сообщений: 3940
Откуда: Минск
Дата: 29.12.17 15:01:00ОтветитьЦитировать
оффтоп
как-то 1 мой знакомый раскзаывал как он решил блеснуть английским и в переписке с заказчиками использовал не Yes
а почти Yep - но только он там 1 букву спутал написал Yap ( трепло
context.reverso.net)
долго потом удивлялся чего это чел с ним потом не хотел общаться
1 буква а как меняет смысл
Ratings: 0 negative/0 positive



Извините, только зарегистрированные пользователи могут писать в этом форуме.

On-line: 39 Аспид Alsim  and Guests: 37


© 2000-2018 Fox Club 
Яндекс.Метрика