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

Откуда:
Сообщений: 39
Есть таблица TABLE с полями ID, DATE, PAYMENT c данными:

ID DATE PAYMENT
_ ______ _______
1 17-JUN-18 100
2 18-JUN-18 50
2 19-JUN-18 50
3 20-JUN-18 45
4 21-MAY-18 100
4 22-MAY-18 50


Где ID-идентификатор клиента, DATE - дата платежа, PAYMENT - сумма платежа. Необходимо вывести последний платеж по каждому клиенту. Что бы получилось вот так:

ID DATE PAYMENT
_ ______ _______
1 17-JUN-18 100
2 19-JUN-18 50
3 20-JUN-18 45
4 22-MAY-18 50

Запрос:

select id, max(date), payment
from table
group by id, amount, payment
order by 1

выдает:

ID DATE PAYMENT
_ ______ _______
1 17-JUN-18 100
2 19-JUN-18 50
3 20-JUN-18 45
4 22-MAY-18 50
4 21-MAY-18 100

Как сделать, чтобы по 4-ому клиенту клиенту осталась только одна запись?
23 окт 18, 22:57    [21712849]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

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

Без payment получишь одну запись.
23 окт 18, 23:19    [21712865]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Piastry
Member

Откуда:
Сообщений: 39
SkilledJunior
Piastry,

Без payment получишь одну запись.


Это я знаю, но только мне нужны данные с payment.
23 окт 18, 23:21    [21712869]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

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

Пары уникальных значений ID, DATE получил, дальше по ним нужно получить payment, есть скалярные подзапросы, есть join, есть in, пробуй.

PS: а еще есть аналитические функции.
23 окт 18, 23:29    [21712873]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Piastry
Member

Откуда:
Сообщений: 39
Ошибочка в запросе:

select id, max(date), payment
from table
group by id, payment
order by 1
23 окт 18, 23:39    [21712882]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Piastry
Member

Откуда:
Сообщений: 39
Всем спасибо. Задача решена.
24 окт 18, 00:29    [21712907]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Elic
Member

Откуда:
Сообщений: 29979
SkilledJunior
Пары уникальных значений ID, DATE получил, дальше по ним нужно получить payment, есть скалярные подзапросы, есть join, есть in, пробуй.

PS: а еще есть аналитические функции.
Всё мимо.
24 окт 18, 07:29    [21712970]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Elic
Member

Откуда:
Сообщений: 29979
Piastry
group by id, payment
Piastry
Задача решена.
IQ ниже плинтуса.
24 окт 18, 07:30    [21712971]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Dshedoo
Member

Откуда:
Сообщений: 393
Select id, date, payment from table t1
where not exists (select 1 from table t2 where t1.id = t2.id and t2.date > t1.date)
24 окт 18, 09:13    [21713020]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1489
Piastry
... Необходимо вывести последний платеж по каждому клиенту. ... Как сделать ...


Любым из понравившихся способов
24 окт 18, 10:02    [21713090]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
julat21
Member

Откуда:
Сообщений: 48
Piastry,
with zap(id, dat, payment) as

(
select 1, to_date('17-01-1018', 'dd-mm-yyyy'), 100 from dual
union all
select 2, to_date('18-01-1018', 'dd-mm-yyyy'), 50 from dual
union all
select 2, to_date('19-01-1018', 'dd-mm-yyyy'), 50 from dual
union all
select 3, to_date('20-01-1018', 'dd-mm-yyyy'), 45 from dual
union all
select 4, to_date('21-05-1018', 'dd-mm-yyyy'), 100 from dual
union all
select 4, to_date('22-05-1018', 'dd-mm-yyyy'), 50 from dual
union all
select 4, to_date('22-05-1018', 'dd-mm-yyyy'), 75 from dual
union all
select 4, to_date('22-05-1018', 'dd-mm-yyyy'), 60 from dual
),
zap2(id, dat, payment) as
(select id, dat,   first_value(payment) over (partition by id order by dat desc)  from zap
)

select id, max(dat),payment from zap2
group by id, payment 
order by id


Еще так:
with zap(id, dat, payment) as
(
select 1, to_date('17-01-1018', 'dd-mm-yyyy'), 100 from dual
union all
select 2, to_date('18-01-1018', 'dd-mm-yyyy'), 50 from dual
union all
select 2, to_date('19-01-1018', 'dd-mm-yyyy'), 50 from dual
union all
select 3, to_date('20-01-1018', 'dd-mm-yyyy'), 45 from dual
union all
select 4, to_date('21-05-1018', 'dd-mm-yyyy'), 100 from dual
union all
select 4, to_date('22-05-1018', 'dd-mm-yyyy'), 50 from dual
union all
select 4, to_date('22-05-1018', 'dd-mm-yyyy'), 75 from dual
union all
select 4, to_date('22-05-1018', 'dd-mm-yyyy'), 60 from dual
)
select id, max(dat),  max(payment) keep(dense_rank first order by dat desc) from zap 
group by id


Единственное остается понять как поступать, когда один человек сделал два платежа, какой с них считать последним. Потому как первый выдаст 50 по ROWID первый, второй выдаст 75 как масимальный платеж за день, поэтому если такая ситуация возможна, то необходимо добавлять сортировку после order by dat desc, так как возможно надо брать 60???
24 окт 18, 11:05    [21713182]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

Откуда:
Сообщений: 303
julat21
Единственное остается понять как поступать, когда один человек сделал два платежа, какой с них считать последним. Потому как первый выдаст 50 по ROWID первый, второй выдаст 75 как масимальный платеж за день, поэтому если такая ситуация возможна, то необходимо добавлять сортировку после order by dat desc, так как возможно надо брать 60???

Задача подразумевает уникальность id, date, поскольку более одного платежа по одному id в один день делают недостижимым требуемый результат - "Необходимо вывести последний платеж по каждому клиенту."
24 окт 18, 22:17    [21714298]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

Откуда:
Сообщений: 303
Elic
Всё мимо.


with t (id, paydate, payment) as
(
select 1, to_date('17-JUN-2018', 'DD-MON-YYYY'), 100 from dual union all
select 2, to_date('18-JUN-2018', 'DD-MON-YYYY'), 50  from dual union all
select 2, to_date('19-JUN-2018', 'DD-MON-YYYY'), 50  from dual union all
select 3, to_date('20-JUN-2018', 'DD-MON-YYYY'), 45  from dual union all
select 4, to_date('21-MAY-2018', 'DD-MON-YYYY'), 100 from dual union all
select 4, to_date('22-MAY-2018', 'DD-MON-YYYY'), 50  from dual
)


in
select id, paydate, payment
from t
where (id, paydate) in (select id, max(paydate) from t group by id)
order by id


скалярный подзапрос
select a.id, a.mpaydate, (select payment from t b where b.id = a.id and b.paydate = a.mpaydate)
from (select id, max(paydate) mpaydate
      from t
      group by id) a
order by id


join
select b.id, b.paydate, b.payment
from t b,
     (select id, max(paydate) mpaydate
      from t
      group by id) a
where b.id = a.id and b.paydate = a.mpaydate
order by b.id


PS:
select id, max(paydate), max(payment) KEEP (DENSE_RANK LAST ORDER BY paydate)
from t
group by id
order by id



Что из этого мимо?
24 окт 18, 22:35    [21714311]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Elic
Member

Откуда:
Сообщений: 29979
SkilledJunior
PS: аналитические функции
SkilledJunior
select id, max(paydate), max(payment) KEEP (DENSE_RANK LAST ORDER BY paydate)
from t
Учись, студент, лучше.
SkilledJunior
Что из этого мимо?
Всё бессмысленностью.
Рекомендую поменьше сидеть перед зеркалом в самолюбовании.
25 окт 18, 07:41    [21714438]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

Откуда:
Сообщений: 303
Elic
Учись, студент, лучше.

select DISTINCT id,
min(paydate) KEEP (DENSE_RANK LAST ORDER BY paydate) over(PARTITION BY id),
max(payment) KEEP (DENSE_RANK LAST ORDER BY paydate) over(PARTITION BY id)
from t
order by id

Так лучше?))

Elic
Всё бессмысленностью.

Marvin?
25 окт 18, 19:23    [21715295]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18342
SkilledJunior
select DISTINCT id,
min(paydate) KEEP (DENSE_RANK LAST ORDER BY paydate) over(PARTITION BY id),
...

Так лучше?))

Нет.
25 окт 18, 19:39    [21715315]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

Откуда:
Сообщений: 303
andrey_anonymous
Нет.

Зато называется аналитикой))
25 окт 18, 19:44    [21715320]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10043
SkilledJunior
Зато называется аналитикой))


Да ну? Пойди покури доку на предмет аналитика и аггрегация:

Aggregate functions return a single result row based on groups of rows.

Analytic functions compute an aggregate value based on a group of rows.

SY.

Сообщение было отредактировано: 25 окт 18, 20:10
25 окт 18, 20:09    [21715350]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

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

FIRST
If you omit the OVER clause, then the FIRST and LAST functions are treated as aggregate functions. You can use these functions as analytic functions by specifying the OVER clause. The query_partition_clause is the only part of the OVER clause valid with these functions. If you include the OVER clause but omit the query_partition_clause, then the function is treated as an analytic function, but the window defined for analysis is the entire table.
25 окт 18, 20:18    [21715358]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10043
A, не заметил у тебя две версии c KEEP.

SY.
25 окт 18, 21:50    [21715415]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
Elic
Member

Откуда:
Сообщений: 29979
SkilledJunior
Так лучше?))
Ты самовлюблённый дурак, если не понимаешь, что хуже.
26 окт 18, 07:27    [21715570]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
ultrasonic7
Member

Откуда:
Сообщений: 127
Такой селект устроит тописктартера?
create table payments(id number
                      , payment_date date
                      , payment number);
insert into payments values(1, TO_DATE('17.06.2018', 'dd.mm.yyyy'), 100);
insert into payments values(2, TO_DATE('18.06.2018', 'dd.mm.yyyy'), 50);
insert into payments values(2, TO_DATE('19.06.2018', 'dd.mm.yyyy'), 50);
insert into payments values(3, TO_DATE('20.06.2018', 'dd.mm.yyyy'), 45);
insert into payments values(4, TO_DATE('21.05.2018', 'dd.mm.yyyy'), 100);
insert into payments values(4, TO_DATE('22.05.2018', 'dd.mm.yyyy'), 50);

with maxdates as
(select id, max(payment_date) maxdate
from payments
group by id)
select p.id, p.payment_date, p.payment
from payments p join maxdates md
  on p.id = md.id and p.payment_date = md.maxdate
order by p.id;
26 окт 18, 09:59    [21715673]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
ultrasonic7
Member

Откуда:
Сообщений: 127
Хотя ТС, наверно, уже сюда не заглядывает, раз написал, что задача решена.
26 окт 18, 10:05    [21715678]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

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

join уже был выше.

Изящного варианта от Доброго Э - Эха еще не было
select *
  from (
         select t.*, row_number() 
                           over(partition by id 
                                    order by paydate desc) as rn
           from t
       )
where rn = 1
26 окт 18, 14:12    [21716135]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с группировкой  [new]
SkilledJunior
Member

Откуда:
Сообщений: 303
Elic
Ты самовлюблённый дурак, если не понимаешь, что хуже.

26 окт 18, 14:19    [21716147]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить