Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Вывести положительную сумму на последнюю даты  [new]
o.makarov
Member

Откуда:
Сообщений: 37
Всем привет,

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

Исходные данные следующие:
Есть множество аккаунтов, у каждого аккаунта есть начисления в каждом месяце по определённым услугам, например:

Аккаунт 555 имеет начисление 500 на дату 01.01.2019 по услуге 1, начисление 500 на дату 01.02.2019 по услуге 1 и -500 по услуге 2.

То есть в итоге мне нужно получить сумму 500 на 01.01.2019, поскольку сумма на 01.02.2019 будет равна 0.

Поэтому условие where sum > 0 не подходит, условие having sum(sum) > 0, last тоже не подходит, поскольку тогда данный аккаунт будет исключён из итоговой выборки.

Может, кто подскажет, что почитать и как это можно осуществить.

Для конкретного аккаунта запрос работает (приведу ниже). Но для всего списка нет

Вот запрос, который писал я, он работает, только если лайкать по конкретному аккаунту, а не по всем

select distinct
account_number,
data
from (
select
c1.account_number,
trunc(c1.pay_date,'month') data,
sum(c1.sum) summa
from exchange.charge_groups_sd c1
where
c1.account_number = account_number and
c1.account_number like '2000000341779' and
pay_date < to_date('01.03.2019','dd.mm.yyyy')
having
sum(c1.sum) > 0
group by
c1.account_number,
trunc(c1.pay_date,'month')
order by
trunc(c1.pay_date,'month') desc) t
where
data in (select
max(data)
from(
select
c1.account_number,
trunc(c1.pay_date,'month') data,
sum(c1.sum) summa
from exchange.charge_groups_sd c1
where
c1.account_number = account_number and
c1.account_number like '2000000341779' and
pay_date < to_date('01.03.2019','dd.mm.yyyy')
having
sum(c1.sum) > 0
group by
c1.account_number,
trunc(c1.pay_date,'month')
order by
trunc(c1.pay_date,'month') asc) t)
order by
data
10 апр 19, 12:16    [21858144]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
Dshedoo
Member

Откуда:
Сообщений: 323
with q (acc, amt, dat, serv) as (
select 555, 500, to_date('01.01.2019','DD.MM.YYYY'), 1 from dual union all
select 555, 500, to_date('02.01.2019','DD.MM.YYYY'), 1 from dual union all
select 555, -500, to_date('03.01.2019','DD.MM.YYYY'), 2 from dual union all
select 444, 500, to_date('01.01.2019','DD.MM.YYYY'), 1 from dual union all
select 444, 1500, to_date('02.01.2019','DD.MM.YYYY'), 3 from dual union all
select 444, -500, to_date('03.01.2019','DD.MM.YYYY'), 2 from dual union all
select 111, 500, to_date('01.01.2019','DD.MM.YYYY'), 1 from dual union all
select 111, -500, to_date('02.01.2019','DD.MM.YYYY'), 3 from dual union all
select 111, -500, to_date('03.01.2019','DD.MM.YYYY'), 2 from dual union all
select 111, 500, to_date('14.01.2019','DD.MM.YYYY'), 1 from dual union all
select 111, -500, to_date('15.01.2019','DD.MM.YYYY'), 2 from dual union all
select 111, 750, to_date('17.01.2019','DD.MM.YYYY'), 1 from dual)
, w as (select acc, sum(amt) OVER (partition by acc order by dat) as amt, dat from q)

select * from w where amt > 0 and not exists (select 1 from w w2 where w2.amt > 0 and w2.dat > w.dat and w.acc=w2.acc)
10 апр 19, 12:30    [21858168]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
AmKad
Member

Откуда:
Сообщений: 4992
Ты ведь уже спрашивал.
10 апр 19, 12:30    [21858169]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
o.makarov
Member

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

да, спасибо за ответ, но через last у меня ничего не получилось, ну или просто я туплю и не могу понять, как им грамотно пользоваться
10 апр 19, 12:39    [21858179]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
AmKad
Member

Откуда:
Сообщений: 4992
o.makarov,

Давай подумаем о том, какие у тебя варианты. Либо дальше бицца головой об стену, либо все-таки представить на суд общественности репрезентативные тестовые данные с описанием желаемого результата.
10 апр 19, 12:44    [21858186]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
o.makarov
Member

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

account    |   charge   |  service  | date
505              700             1           01/07/2018
505              200             1           01/09/2018
505              400             1           01/01/2019
505              400             1           01/02/2019
505              -400            2           01/02/2019
555              500             1           01/11/2018
555              500             1           01/03/2019
555              -500            2           01/03/2019
555              400             3           01/03/2019
777              700             1           01/02/2019
777              -700            2           01/02/2019
777              700             1           01/03/2019
888              200             1           01/08/2014
888              -200            1           01/08/2014
888              10               4           01/08/2014
888              200             1           01/12/2018
888              -200            2           01/12/2018


Должно получиться

account    |  date           |  charge
505            01/01/2019         400
555            01/03/2019         600       
777            01/03/2019         700
888            01/08/2014         10


То есть суммируется сумма по всем услугам и выводится с датой, когда сумма по всем услугам была больше 0 последний раз
10 апр 19, 13:44    [21858275]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
o.makarov
Member

Откуда:
Сообщений: 37
o.makarov,

Прошу прощения, по аккаунту 555 сумма 400, а не 600
10 апр 19, 13:45    [21858276]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1869
o.makarov
o.makarov,
Прошу прощения, по аккаунту 555 сумма 400, а не 600


555 500 1 01/11/2018
555 500 1 01/03/2019
555 -500 2 01/03/2019
555 400 3 01/03/2019

sql> select 500+500-500+400 from dual;

500+500-500+400
---------------
            900


зы
суміруете с нарастающим итогом, и выводите мах где больше 0

.....
stax
10 апр 19, 13:52    [21858287]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
123йй
Member

Откуда:
Сообщений: 1520
o.makarov
То есть суммируется сумма по всем услугам и выводится с датой, когда сумма по всем услугам была больше 0 последний раз

888 - с суммой согласен, с датой нет
777 - согласен
555 - 900, дату попал
505 - сумма не та, дата не та
10 апр 19, 13:53    [21858289]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
o.makarov
Member

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

Видимо не очень я объясняю.

Нужна сумма больше 0 за месяц,
То есть если есть следующие начисления
555 100 01/01/2019
555 100 01/02/2019
555 -100 01/02/2019
555 200 01/03/2019
555 100 01/03/2019
555 300 01/04/2019
555 -300 01/04/2019


Должно вывести
555 300 01/03/2019

Поскольку последний месяц, когда сумма была больше 0, это 01/03/2019.
10 апр 19, 13:59    [21858296]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1869
o.makarov
Stax,

Видимо не очень я объясняю.

Нужна сумма больше 0 за месяц,
То есть если есть следующие начисления
555 100 01/01/2019
555 100 01/02/2019
555 -100 01/02/2019
555 200 01/03/2019
555 100 01/03/2019
555 300 01/04/2019
555 -300 01/04/2019


Должно вывести
555 300 01/03/2019

Поскольку последний месяц, когда сумма была больше 0, это 01/03/2019.


допилите
with t(account,charge,service,date1) as (
select 505,              700,             1,to_date('           01/07/2018') from dual union all
select 505,              200,             1,to_date('           01/09/2018') from dual union all
select 505,              400,             1,to_date('           01/01/2019') from dual union all
select 505,              400,             1,to_date('           01/02/2019') from dual union all
select 505,              -400,            2,to_date('           01/02/2019') from dual union all
select 555,              500,             1,to_date('           01/11/2018') from dual union all
select 555,              500,             1,to_date('           01/03/2019') from dual union all
select 555,              -500,            2,to_date('           01/03/2019') from dual union all
select 555,              400,             3,to_date('           01/03/2019') from dual union all
select 777,              700,             1,to_date('           01/02/2019') from dual union all
select 777,              -700,            2,to_date('           01/02/2019') from dual union all
select 777,              700,             1,to_date('           01/03/2019') from dual union all
select 888,              200,             1,to_date('           01/08/2014') from dual union all
select 888,              -200,            1,to_date('           01/08/2014') from dual union all
select 888,              10,              4,to_date('           01/08/2014') from dual union all
select 888,              200,             1,to_date('           01/12/2018') from dual union all
select 888,              -200,            2,to_date('           01/12/2018') from dual 
)
select account,max(trunc(date1,'mm')) max_date1 
,max(ss) KEEP (DENSE_RANK LAST ORDER BY date1) s
from 
 (select account,date1,sum(charge) over (partition by account,trunc(date1,'mm') order by date1) ss from t)
where ss>0
group by account
order by 1
/
SQL> /

   ACCOUNT MAX_DATE          S
---------- -------- ----------
       505 01.01.19        400
       555 01.03.19        400
       777 01.03.19        700
       888 01.08.14         10


зы
тестовые данные желательно в форме "with"

.....
stax
10 апр 19, 14:20    [21858327]     Ответить | Цитировать Сообщить модератору
 Re: Вывести положительную сумму на последнюю даты  [new]
o.makarov
Member

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

Спасибо большое, вроде бы всё работает, как нужно, вы очень помогли)

По поводу тестовых данных учту на будущее
11 апр 19, 12:29    [21859211]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить