ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg+distinct
39 сообщений из 39, показаны все 2 страниц
  все  
listagg+distinct
    #38669757
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Работают вместе или только через подзапросы?
...
Рейтинг: 0 / 0
15.06.2014, 18:15
| Ответить | Цитировать | Написать  
listagg+distinct
    #38669774
29 Белых Котиков
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
29 Белых Котиков
Участник
andreymx,

Не работают. Надо писать свою агрегирующую функцию, либо использовать wm_concat, либо использовать подзапросы.
...
Рейтинг: 0 / 0
15.06.2014, 18:45
| Ответить | Цитировать | Написать  
listagg+distinct
    #38669795
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Расстроили вы меня, все 29-еро
...
Рейтинг: 0 / 0
15.06.2014, 19:11
| Ответить | Цитировать | Написать  
listagg+distinct
    #38669878
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
Можно попробовать regexp'ом (нашел на просторах инета)
Код: plsql
1.
select regexp_replace(listagg(<поле>, ';') within group (order by <поле>),'([^;]+)(;\1)+', '\1') alias from table
...
Рейтинг: 0 / 0
15.06.2014, 22:03
| Ответить | Цитировать | Написать  
listagg+distinct
    #38669880
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
MazoHistМожно попробовать regexp'ом (нашел на просторах инета)
Код: plsql
1.
select regexp_replace(listagg(<поле>, ';') within group (order by <поле>),'([^;]+)(;\1)+', '\1') alias from table

увеличивается возможность переполнения
...
Рейтинг: 0 / 0
15.06.2014, 22:10
| Ответить | Цитировать | Написать  
listagg+distinct
    #38669888
29 Белых Котиков
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
29 Белых Котиков
Участник
Вообще, distinct с order by даже в collect не работают

Вот так собирает различные элементы в группе

Код: plsql
1.
2.
3.
4.
5.
6.
select a, collect(distinct l) l 
from (
    select upper(substr(object_name,1,1)) as a, mod(object_id,10) as l 
    from all_objects
) test_source 
group by a



А вот так условие distinct игнорирует, зато сортирует

Код: plsql
1.
2.
3.
4.
5.
6.
select a, collect(distinct l order by l desc) l 
from (
    select upper(substr(object_name,1,1)) as a, mod(object_id,10) as l 
    from all_objects
) test_sourcec 
group by a
...
Рейтинг: 0 / 0
15.06.2014, 22:40
| Ответить | Цитировать | Написать  
listagg+distinct
    #38670490
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymx,

dist2
...
Рейтинг: 0 / 0
16.06.2014, 14:47
| Ответить | Цитировать | Написать  
listagg+distinct
    #38686865
йцу1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
йцу1
Гость
MazoHistМожно попробовать regexp'ом (нашел на просторах инета)
Код: plsql
1.
select regexp_replace(listagg(<поле>, ';') within group (order by <поле>),'([^;]+)(;\1)+', '\1') alias from table

кривой регексп
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SQL> with t as
  2  (
  3   select '170,171,172' txt from dual union all
  4   select '170,171,171,172' from dual
  5  )
  6  select txt, regexp_replace(txt, '([^,]+)(,\1)+', '\1') result from t;

TXT             RESULT
--------------- --------------------------------------------------------------------------------
170,171,172     170,17172
170,171,171,172 170,171,172
...
Рейтинг: 0 / 0
03.07.2014, 14:42
| Ответить | Цитировать | Написать  
listagg+distinct
    #38808178
demid-d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
demid-d
Гость
йцу1,
а вот так уже работает

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SQL> with t as
  2   (select '170, 171, 172' txt
  3      from dual
  4    union all
  5    select '170, 171, 171, 172'
  6      from dual)
  7  select txt,
  8         regexp_replace(txt, '([^,]+)(,\1)+', '\1') result
  9    from t;

TXT                         RESULT
------------------        ----------------------------------------------
170, 171, 172           170, 171, 172
170, 171, 171, 172    170, 171, 172
...
Рейтинг: 0 / 0
17.11.2014, 13:09
| Ответить | Цитировать | Написать  
listagg+distinct
    #38808193
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
demid-dйцу1,
а вот так уже работает

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SQL> with t as
  2   (select '170, 171, 172' txt
  3      from dual
  4    union all
  5    select '170, 171, 171, 172'
  6      from dual)
  7  select txt,
  8         regexp_replace(txt, '([^,]+)(,\1)+', '\1') result
  9    from t;

TXT                         RESULT
------------------        ----------------------------------------------
170, 171, 172           170, 171, 172
170, 171, 171, 172    170, 171, 172

это до тех пор, пока внутренний результат ограничивается 4-кб
...
Рейтинг: 0 / 0
17.11.2014, 13:14
| Ответить | Цитировать | Написать  
listagg+distinct
    #38808228
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
andreymxэто до тех пор, пока внутренний результат ограничивается 4-кба после тех пор результат listagg не ограничивается?
...
Рейтинг: 0 / 0
17.11.2014, 13:29
| Ответить | Цитировать | Написать  
listagg+distinct
    #38808495
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
йцу1кривой регексп


Жадный он а не кривой :

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SCOTT@orcl > with t as (
  2             select '170,171,172' txt from dual union all
  3             select '170,171,171,172' from dual
  4            )
  5  select  txt,
  6          regexp_replace(txt,'([^,]+)(,\1)+','\1') greedy_result,
  7          regexp_replace(txt,'([^,]+)(,\1)?+','\1') non_greedy_result
  8    from  t
  9  /

TXT             GREEDY_RESULT        NON_GREEDY_RESULT
--------------- -------------------- --------------------
170,171,172     170,17172            170,171,172
170,171,171,172 170,171,172          170,171,172

SCOTT@orcl >



SY.
...
Рейтинг: 0 / 0
17.11.2014, 15:32
| Ответить | Цитировать | Написать  
listagg+distinct
    #38808679
Den89
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Den89
Участник
29 Белых КотиковВообще, distinct с order by даже в collect не работают

Distinct с collect только в sql-контексте работают. В Pl/Sql уже нет, приходится execute immediate использовать.

Очень часто distinct-order-collect можно заменить на cast-multiset.
...
Рейтинг: 0 / 0
17.11.2014, 18:12
| Ответить | Цитировать | Написать  
listagg+distinct
    #38808762
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
-2-andreymxэто до тех пор, пока внутренний результат ограничивается 4-кба после тех пор результат listagg не ограничивается?имел в виду, что там, где какой-нить StrAgg(Distinct) мог бы отработать, regexp_replace(listagg( с такой же легкостью может свалиться
...
Рейтинг: 0 / 0
17.11.2014, 19:19
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816553
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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


Меня это не устраивает. Как мне привести в тот вид, который показан в первой табличке?
...
Рейтинг: 0 / 0
25.11.2014, 18:35
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816563
Проктологъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Проктологъ
Гость
RenVold, слово аналитика у тя с чемнить еще ассоциируется?
...
Рейтинг: 0 / 0
25.11.2014, 18:45
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816567
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
ПроктологъRenVold, слово аналитика у тя с чемнить еще ассоциируется?

Ассоциируется, ассоциируется! Но я не могу что то как то правильно составить . Помогли бы лучше, чем сарказм проявляли (((
...
Рейтинг: 0 / 0
25.11.2014, 18:51
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816588
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
Хотя бы подскажите в каком направлении копать. Какую аналитику использовать
...
Рейтинг: 0 / 0
25.11.2014, 19:12
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816600
Проктологъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Проктологъ
Гость
На форуме есть некто guest_smetanka. Она всем дает. Если же дождаться невтерпеж, то даю подсказку: вынеси мусор один единственный конкат во внешний подзапрос
...
Рейтинг: 0 / 0
25.11.2014, 19:28
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816607
Проктологъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Проктологъ
Гость
RenVoldКакую аналитику использоватьС аналитикой пожалуй будет излишне. Она будет считать все. А тут только долгами ограничиться хватит
...
Рейтинг: 0 / 0
25.11.2014, 19:34
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816612
Фотография MonteCarlos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MonteCarlos
Участник
Проктологъ,
давай, чувак, путай его, чем дольше RenVold пишет запрос, тем больше времени у Андросова и Бутаковой достать деньги
...
Рейтинг: 0 / 0
25.11.2014, 19:37
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816622
Проктологъ
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Проктологъ
Гость
MonteCarlosПроктологъ,
давай, чувак, путай его, чем дольше RenVold пишет запрос, тем больше времени у Андросова и Бутаковой достать деньгиПутать будеш свою бабушку объясняя куда девалась ее пенсия, а разжевывать и ротврот передавать кашицу - свои птенцам когда вырастешь
...
Рейтинг: 0 / 0
25.11.2014, 19:46
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816625
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
Жалко, что LISTAGG не дружит с DISTINCT ((( мне б проще задачу было бы решить.... Есть у кого какие мысли?

По идее надо какой то сложнющий запрос строить. Логика такая.

1 ) Находим все записи, которые попадают в период с 01.09.2014 по 30.09.2014.
2) Проходимся по каждому клиенту, группируем все записи клиента по id_message и суммируем SUM(SUMMA) по каждой группе id_message - если плюс на минус, не берем. Если минус - берем (если плюс - тоже не берем. только минусы)
3) Склеиваем через запятую все найденные записи
4) Также делаем автосумму по всем id_message, которые принадлежат одному клиенту.


Как то так... Такой набросок плана.

А вот как изящно и красиво написать селект ... чет никак ((( Хотя в PL/SQL Я вроде не чайник ((((
...
Рейтинг: 0 / 0
25.11.2014, 19:50
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816628
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
ПроктологъНа форуме есть некто guest_smetanka. Она всем дает. Если же дождаться невтерпеж, то даю подсказку: вынеси мусор один единственный конкат во внешний подзапрос

Щас попробую.... Ты наверное, имел ввиду "лишний конкат" - это ".... wm_concat (distinct trunc(tr.data_transact)) AS tr_data ..."
...
Рейтинг: 0 / 0
25.11.2014, 19:54
| Ответить | Цитировать | Написать  
listagg+distinct
    #38816629
RenVold
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RenVold
Гость
ПроктологъRenVoldКакую аналитику использоватьС аналитикой пожалуй будет излишне. Она будет считать все. А тут только долгами ограничиться хватит

Неет! Не только! Обязательно нужны через запятую все номера, которые заказал клиент (чтоб он видел за какие номера он платит), его фио, адрес (куда квитанцию прислать) ну и конечно же его общий долг за взятый период
...
Рейтинг: 0 / 0
25.11.2014, 19:56
| Ответить | Цитировать | Написать  
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
| Ответить | Цитировать | Написать  
39 сообщений из 39, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg+distinct
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1881599]:
 0ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 47ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 68ms
get tp. blocked users:
 1ms
others: 267ms
total:  433ms
созд. / загр.: 433ms
x
x
Закрыть


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