ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg+distinct
14 сообщений из 39, страница 2 из 2
  2  все
listagg+distinct
    #38816681
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
RenVoldСоздал таблицы TRANSACT_TEST.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE transact_test
    (id                             NUMBER(11,0) NOT NULL,
    id_client                      NUMBER(11,0),
    id_message                     NUMBER(11,0),
    summa                          FLOAT(10),
    data_transact                  DATE,
    m_number                       NUMBER(11,0))



Наполнил ее.

 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
INSERT INTO transact_test 
VALUES(217248,20940,133588,-150,TO_DATE('2014-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),2126);
INSERT INTO transact_test 
VALUES(217742,20940,133588,150,TO_DATE('2014-09-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),2126);
INSERT INTO transact_test 
VALUES(214748,20940,139946,-180,TO_DATE('2014-09-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3107);
INSERT INTO transact_test 
VALUES(217800,20940,139946,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3107);
INSERT INTO transact_test 
VALUES(214749,20940,139947,-180,TO_DATE('2014-09-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3108);
INSERT INTO transact_test 
VALUES(217799,20940,139947,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3108);
INSERT INTO transact_test 
VALUES(214753,20940,139949,-180,TO_DATE('2014-09-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3109);
INSERT INTO transact_test 
VALUES(217798,20940,139949,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3109);
INSERT INTO transact_test 
VALUES(217523,13504,140265,200,TO_DATE('2014-09-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3190);
INSERT INTO transact_test 
VALUES(215075,13504,140265,-200,TO_DATE('2014-09-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3190);
INSERT INTO transact_test 
VALUES(217801,20940,140478,200,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3244);
INSERT INTO transact_test 
VALUES(215328,20940,140478,-200,TO_DATE('2014-09-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3244);
INSERT INTO transact_test 
VALUES(217524,13504,140655,200,TO_DATE('2014-09-24 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3279);
INSERT INTO transact_test 
VALUES(215484,13504,140655,-200,TO_DATE('2014-09-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3279);
INSERT INTO transact_test 
VALUES(217807,20940,141133,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3413);
INSERT INTO transact_test 
VALUES(216510,20940,141133,-180,TO_DATE('2014-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3413);
INSERT INTO transact_test 
VALUES(216518,20940,141134,-180,TO_DATE('2014-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3415);
INSERT INTO transact_test 
VALUES(217803,20940,141134,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3415);
INSERT INTO transact_test 
VALUES(216527,20940,141135,-180,TO_DATE('2014-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3417);
INSERT INTO transact_test 
VALUES(217802,20940,141135,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3417);
INSERT INTO transact_test 
VALUES(217804,20940,141136,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3419);
INSERT INTO transact_test 
VALUES(216532,20940,141136,-180,TO_DATE('2014-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3419);
INSERT INTO transact_test 
VALUES(217805,20940,141137,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3425);
INSERT INTO transact_test 
VALUES(216548,20940,141137,-180,TO_DATE('2014-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3425);
INSERT INTO transact_test 
VALUES(216561,20940,141138,-180,TO_DATE('2014-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3426);
INSERT INTO transact_test 
VALUES(217806,20940,141138,180,TO_DATE('2014-09-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3426);
INSERT INTO transact_test 
VALUES(217458,13504,141293,-200,TO_DATE('2014-09-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3491);
INSERT INTO transact_test 
VALUES(217459,13504,141294,-200,TO_DATE('2014-09-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3493);
INSERT INTO transact_test 
VALUES(217713,20940,141458,-180,TO_DATE('2014-09-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3851);
INSERT INTO transact_test 
VALUES(217714,20940,141459,-180,TO_DATE('2014-09-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),3852);



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE client_test
    (id                             NUMBER(*,0),
    fio                            NVARCHAR2(152),
    adres                          NVARCHAR2(125),
    telefon                        NVARCHAR2(50),
    c_comment                      VARCHAR2(4000 BYTE),
    c_balance                      FLOAT(10),
    olddolg                        NUMBER(1,0),
    mobile                         VARCHAR2(20 CHAR),
    netusers                       NUMBER)



Наполнил...

 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
INSERT INTO client_test 
VALUES(981,'Никифоров С. Г.','г. НеЗнайГде ул.Н-Чапаевская д.147 кв.','41511',NULL,0,0,NULL,NULL);
INSERT INTO client_test 
VALUES(10356,'Шатилова М. В.','г. НеЗнайГде ул.13 Линия д.63 кв.','52732',NULL,0,0,NULL,NULL);
INSERT INTO client_test 
VALUES(13504,'Бутакова Л. В.','г. НеЗнайГде ул.5 Линия д.14 кв.','50199',NULL,-150,0,NULL,NULL);
INSERT INTO client_test 
VALUES(983,'Цымбалюк В. С.','г. ЗнайГде ул.Н-Уральская д.62 кв.','44247',NULL,0,0,NULL,NULL);
INSERT INTO client_test 
VALUES(20940,'Андросов А. С.','г. ЗнайГде ул.Галактионова д.58 кв.6','24392',NULL,-510,0,NULL,NULL);



Задача:
Нужно найти всех должников и вывести через запятую только те позиции, которые не оплачены. Т.е. MS_NUMBER через запятую.

Если выполнить простой селект

Код: plsql
1.
SELECT*  FROM transact_test order by id_client,  m_number



Видим.

 
ID ID_CLIENT ID_MESSAGE SUMMA DATA_TRANSACT M_NUMBER217523 13504 140265 200 24-сен-2014 3190215075 13504 140265 -200 10-сен-2014 3190215484 13504 140655 -200 15-сен-2014 3279217524 13504 140655 200 24-сен-2014 3279217458 13504 141293 -200 23-сен-2014 3491 217459 13504 141294 -200 23-сен-2014 3493 217248 20940 133588 -150 19-сен-2014 2126217742 20940 133588 150 26-сен-2014 2126217800 20940 139946 180 27-сен-2014 3107214748 20940 139946 -180 05-сен-2014 3107217799 20940 139947 180 27-сен-2014 3108214749 20940 139947 -180 05-сен-2014 3108214753 20940 139949 -180 05-сен-2014 3109217798 20940 139949 180 27-сен-2014 3109217801 20940 140478 200 27-сен-2014 3244215328 20940 140478 -200 12-сен-2014 3244216510 20940 141133 -180 19-сен-2014 3413217807 20940 141133 180 27-сен-2014 3413216518 20940 141134 -180 19-сен-2014 3415217803 20940 141134 180 27-сен-2014 3415217802 20940 141135 180 27-сен-2014 3417216527 20940 141135 -180 19-сен-2014 3417217804 20940 141136 180 27-сен-2014 3419216532 20940 141136 -180 19-сен-2014 3419217805 20940 141137 180 27-сен-2014 3425216548 20940 141137 -180 19-сен-2014 3425216561 20940 141138 -180 19-сен-2014 3426217806 20940 141138 180 27-сен-2014 3426217713 20940 141458 -180 26-сен-2014 3851 217714 20940 141459 -180 26-сен-2014 3852


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

Т.е. хочу получить вот такой результат. (в столбце MS_NUMBER вместо точек - запятая)

CLIENT MS_NUMBER TR_DATA FIO TELEFON ADRES DOLG20940 3851. 3852 26.сен.14 Андросов А. С. 24392 г. НеЗнай Где ул.Галактионова д.58 кв.6 -36013504 3491. 3493 23.сен.14 Бутакова Л. В. 50199 г. ЗнайГде ул.5 Линия д.14 кв. -400

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT wm_concat(distinct tr.id_client) AS client,
       wm_concat (distinct tr.m_number) AS ms_number,
wm_concat (distinct trunc(tr.data_transact)) AS tr_data,       
clt.fio, clt.telefon,
       clt.adres,       
       sum(tr.summa) as dolg
  FROM transact_test tr, client_test clt 
WHERE (tr.data_transact BETWEEN TO_DATE ('01.09.2014', 'dd.mm.yyyy') AND TO_DATE ('30.09.2014', 'dd.mm.yyyy')) 
 and tr.id_client=clt.id                   
                   HAVING SUM (tr.summa) < 0
                   group by tr.id_client, clt.fio, clt.adres, clt.telefon
                   order by clt.fio



Возвращает мне вот такую фигню.... (хотя это может и не фигня, а фигня в моей голове)

CLIENT MS_NUMBER TR_DATA FIO TELEFON ADRES DOLG20940 2126.3107.3108.3109.3244.3413.3415.3417.3419.3425.3426.3851.3852 05-Сен-2014.12-Сен-2014.19-Сен-2014.26-Сен-2014.27-Сен-2014 Андросов А. С. 24392 г. ЗнайГде ул.Галактионова д.58 кв.6 -36013504 3190.3279.3491.3493 10-Сен-2014.15-Сен-2014.23-Сен-2014.24-Сен-2014 Бутакова Л. В. 50199 г. НеЗнайГде ул.5 Линия д.14 кв. -400


Меня это не устраивает. Как мне привести в тот вид, который показан в первой табличке?

1) сгрупировать (подзапрос)
group by id_client, M_NUMBER
HAVING SUM (tr.summa) < 0
2) group by id_client
listagg(M_NUMBER,',')

ps
хочу получить tr_data -> c датами не понятно если не в один день 3851. 3852

.....
stax
...
Рейтинг: 0 / 0
25.11.2014, 20:51
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816691
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
stax..,

ой
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT wm_concat(distinct tr.id_client) AS client,
       wm_concat (distinct tr.m_number) AS ms_number,
wm_concat (distinct trunc(tr.data_transact)) AS tr_data,
clt.fio, clt.telefon,
       clt.adres,
       sum(tr.summa) as dolg
  FROM 
(   select id_client,m_number,sum(summa) summa,min(data_transact) data_transact
    from transact_test 
    WHERE (data_transact BETWEEN TO_DATE ('01.09.2014', 'dd.mm.yyyy') AND TO_DATE ('30.09.2014', 'dd.mm.yyyy'))
    group by id_client, m_number
    HAVING SUM (summa) < 0
) tr,
 client_test clt
WHERE 
 tr.id_client=clt.id
 group by tr.id_client, clt.fio, clt.adres, clt.telefon
 order by clt.fio
/



ююююю
stax
...
Рейтинг: 0 / 0
25.11.2014, 21:02
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816785
Фотография MonteCarlos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MonteCarlos
Участник
ПроктологъMonteCarlosПроктологъ,
давай, чувак, путай его, чем дольше RenVold пишет запрос, тем больше времени у Андросова и Бутаковой достать деньгиПутать будеш свою бабушку объясняя куда девалась ее пенсия, а разжевывать и ротврот передавать кашицу - свои птенцам когда вырастешьЧувак, тебе надо было семейным доктором идти работать, ты не ту професссию выбрал ))

RenVold,
вариант с аналитикой, может и лучше, хз
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select c.id client,
       wm_concat (/*distinct не нужен*/ t.m_number) ms_number,
       wm_concat (distinct trunc(t.data_transact)) tr_data,
       c.fio, c.telefon, c.adres,
       sum(t.summa) as dolg      
from (select t.*, count(*) over (partition by id_client, m_number) n 
      from transact_test t
      where data_transact between to_date ('01.09.2014', 'dd.mm.yyyy') and to_date ('30.09.2014', 'dd.mm.yyyy')
      ) t, client_test c
where t.n = 1
  and c.id = t.id_client
group by c.id, c.fio, c.adres, c.telefon

еще по датам
если дата конца периода попала между долгом и погашением (т.е. долг фактически погашен, но позже)?
дата конца пусть будет 20.09.2014, m_number 3425 показывать ?
если нет, то так
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select c.id client,
       wm_concat (t.m_number) ms_number,
       wm_concat (distinct trunc(t.data_transact)) tr_data,
       c.fio, c.telefon, c.adres,
       sum(t.summa) as dolg      
from (select t.*, count(*) over (partition by id_client, m_number) n 
      from transact_test t
      ) t, client_test c
where t.n = 1
  and c.id = t.id_client
  and data_transact between to_date ('01.09.2014', 'dd.mm.yyyy') and to_date ('30.09.2014', 'dd.mm.yyyy')
group by c.id, c.fio, c.adres, c.telefon

...
Рейтинг: 0 / 0
25.11.2014, 23:31
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816786
Фотография MonteCarlos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MonteCarlos
Участник
и да, если data_transact включает время, в where ее тоже надо trunc-нуть
...
Рейтинг: 0 / 0
25.11.2014, 23:37
| Ответить | Цитировать | Написать  
listagg+distinct
    #38817045
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
stax..,

С датами в принципе, не так уж и важно. Но твой запрос реально работает и приводит к тому что мне нужно )))) Спасибо! Так просто!

Жму твою руку! )))
...
Рейтинг: 0 / 0
26.11.2014, 11:07
| Ответить | Цитировать | Написать  
listagg+distinct
    #38817076
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
MonteCarlos,

О! Ваш вариант тоже работает. ))) Мне тоже нравится. С использованием partition by смотрится... и сразу чувствуется, что писал спец с глубоким пониманием тай SQL )))

Правда, я не понял назначение динамического столбца N они принимают значения 1 и 2.

Поясни мне смысл.

И еще...

"если дата конца периода попала между долгом и погашением (т.е. долг фактически погашен, но позже)?
дата конца пусть будет 20.09.2014, m_number 3425 показывать ? "

По идее не показывать. ХЗ. Мне не ставили такое условие, но твое замечание очень логичное )))
P.S.
В примере я привел упрощенную модель данных. В принципе у клиента может за один и тот же выбранный период времени по одной и той же позиции образоваться несколько задолженностей. Не обязательно, что за выбранный период все будет погашено


Например.

ID_операции ID_клиента Номер_позиции Дата_транзакции Сумма 1 123 256 01.09.2014 -1502 123 256 07.09.2014 -1503 123 256 14.09.2014 -2004 123 256 16.09.2014 1505 123 256 17.09.2014 -1507 123 512 07.09.2014 -1808 123 512 11.09.2014 -200

и.д.
...
Рейтинг: 0 / 0
26.11.2014, 11:23
| Ответить | Цитировать | Написать  
listagg+distinct
    #38817181
K790
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K790
Участник
RenVold,

ответьте себе на вопрос 16904156

а потом пишите авторС использованием partition by смотрится... и сразу чувствуется, что писал спец с глубоким пониманием тай SQL )))

п.с. я не проктолог, если что, и даже не серый.
...
Рейтинг: 0 / 0
26.11.2014, 12:31
| Ответить | Цитировать | Написать  
listagg+distinct
    #38817232
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
K790RenVold,

ответьте себе на вопрос 16904156

а потом пишите авторС использованием partition by смотрится... и сразу чувствуется, что писал спец с глубоким пониманием тай SQL )))

п.с. я не проктолог, если что, и даже не серый.

Значит ты клон. ДА и просто заёба грешная
...
Рейтинг: 0 / 0
26.11.2014, 12:58
| Ответить | Цитировать | Написать  
listagg+distinct
    #38966522
dakeiras
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dakeiras
Участник
я не парился и написал свою аггрегатную функцию - listagg_unique.
Всё остальное - костыли.
...
Рейтинг: 0 / 0
22.05.2015, 15:51
| Ответить | Цитировать | Написать  
listagg+distinct
    #38966549
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dakeirasя не парился и написал свою аггрегатную функцию - listagg _unique .
Всё остальное - костыли.Ну естественно лучше наплодить сущностей без необходимости чем узнать, что в пользовательский агрегат можно вставить distinct.
...
Рейтинг: 0 / 0
22.05.2015, 16:08
| Ответить | Цитировать | Написать  
listagg+distinct
    #38995807
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
Поднимем тему: нужда заставила найти вариант без ограничения на 4K:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select ltrim(REGEXP_REPLACE(REPLACE(
         REPLACE(
           XMLAGG(
             XMLELEMENT("A",<поле>)
               ORDER BY <поле>).getClobVal(),
             '<A>',';'),
             '</A>',''),'([^;]*)(;\1)+($|;)',
           '\1\3'),';') from dual;
...
Рейтинг: 0 / 0
30.06.2015, 12:18
| Ответить | Цитировать | Написать  
listagg+distinct
    #38995845
хкуеры/хмлкаст ас цлоб
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хкуеры/хмлкаст ас цлоб
Гость
MazoHist,

много реплейсов.
...
Рейтинг: 0 / 0
30.06.2015, 12:35
| Ответить | Цитировать | Написать  
listagg+distinct
    #38995924
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
MazoHist,

17727498
...
Рейтинг: 0 / 0
30.06.2015, 13:22
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
listagg+distinct
    #39921162
AT2020
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AT2020
Гость
MazoHist,

чтобы работало корректно надо добавить пробел
select regexp_replace(listagg(<поле>, '; ') within group (order by <поле>),'([^;]+)(;\1)+', '\1') alias from table
...
Рейтинг: 0 / 0
31.01.2020, 20:15
| Ответить | Цитировать | Написать  
14 сообщений из 39, страница 2 из 2
  2  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg+distinct
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=38995845&tid=1881599]:
 0ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 50ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 56ms
get tp. blocked users:
 2ms
others: 274ms
total:  431ms
созд. / загр.: 431ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]