Logo MurCode
Oracle Новое сообщение

Помогите составить запрос с Count по дублям в различных комбинациях

AlexAuto
Дата: 26.05.2006 07:55:28
Доброе время суток!
Есть примерно такая выборка из нескольких таблиц:
select Lname, Fname, Mname, string, ID from ....
ID может принимать только значения 1,2 и 3.

Которая возвращает:

Иванов Иван Иванович 123QWSWRRA 1
Иванов Иван Иванович 143QРАПРПАР 1
Иванов Иван Иванович 143QРАПРDDG 2
Петров Петр Петрович 133QРВМВАМ 3
Петров Петр Петрович 133QРВМВАМ 3
.......

Нужно составить запрос, который бы возвращал дубли по ID в разрезе "своего кода" и кода двух других ID.
Например:
ID COUNT_1 COUNT_2 COUNT_3
1 1300 200 0
2 0 3000 39
3 400 50 4
Владимор Конев
Дата: 26.05.2006 08:10:33
FAQ - транспонироавние строк в столбцы
zirex
Дата: 26.05.2006 08:13:17 
with tab as (
  select 'Ivanov' as name,  '123QWSWRRA' as code, 1 as id from dual union all
  select 'Ivanov' as name,  '143QÐÀÏÐÏÀÐ' as code, 1 as id from dual union all
  select 'Ivanov' as name,  '143QÐÀÏÐDDG' as code, 2 as id from dual union all
  select 'Ivanov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual union all
  select 'Ivanov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual 
)

select id, count(code1) cnt1, count(code2) cnt2, count(code3) cnt3 from 
(
    select id, 
           decode(id, 1, code) as code1, 
           decode(id, 2, code) as code2, 
           decode(id, 3, code) as code3
    from tab  
)
group by id    


   	ID	CNT1	CNT2	CNT3
1	1	2	0	0
2	2	0	1	0
3	3	0	0	2
AlexAuto
Дата: 26.05.2006 08:28:39
Спасибо. Но это немного не то, нужно же твойные записи (твойные и т.п.) посчитать, тоесть для иванова сколько ивановых с ID = 1, сколько с ID = 2 и сколько с ID = 3, также для петрова и потом эти данные сложить, мне кажется тут должна быть конструкция over (PArtition by .... order by)
AlexAuto
Дата: 26.05.2006 08:31:18
ДА, поле String с большой вероятностью уникально, но могут встречатсья и не уникальные значения
zirex
Дата: 26.05.2006 08:36:55
Извините... не проснулся :)
with tab as (
  select 'Ivanov' as name,  '123QWSWRRA' as code, 1 as id from dual union all
  select 'Ivanov' as name,  '143QÐÀÏÐÏÀÐ' as code, 1 as id from dual union all
  select 'Ivanov' as name,  '143QÐÀÏÐDDG' as code, 2 as id from dual union all
  select 'Ivanov' as name,  '143QÐÀÏÐDDG' as code, 2 as id from dual union all
  select 'Ivanov' as name,  '143QÐÀÏÐDDG' as code, 2 as id from dual union all
  select 'Petrov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual union all
  select 'Petrov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual union all
  select 'Petrov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual union all
  select 'Petrov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual union all
  select 'Petrov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual union all
  select 'Petrov' as name,  '133QÐÂÌÂÀÌ' as code, 3 as id from dual 
)
select name, sum(c1) c1, sum(c2) c2, sum(c3) c3 from 
(
  select id, 
         name,
         decode(id, 1, count(1), 0) c1, 
         decode(id, 2, count(1), 0) c2,
         decode(id, 3, count(1), 0) c3
  from tab  
  group by name, id
  having count(1) > 1
)
group by name


   	NAME	C1	C2	C3
1	Ivanov	2	3	0
2	Petrov	0	0	6
zirex
Дата: 26.05.2006 08:44:32
AlexAuto
String с большой вероятностью уникально, но могут встречатсья и не уникальные значения

тогда так
  group by name, id, code

AlexAuto

возвращал дубли по ID

за отображение именно дублей отвечает
having count(1) > 1

что надо? дубли или общее количество по ID
определитесь :)

отсюда
AlexAuto
нужно же твойные записи (твойные и т.п.) посчитать, тоесть для иванова сколько ивановых с ID = 1, сколько с ID = 2 и сколько с ID =3

сложно понять чего же вам хочется :)
AlexAuto
Дата: 26.05.2006 08:44:32
Извините, это я видимо не точно задачу поставил..
Иванов и петров это я для примера написал и далее поставил многоточие, т.е. в таблице около 100 тыс. строк. дубли строк считаются при совпадении имени, фамилии и отчества
AlexAuto
Дата: 26.05.2006 08:46:48
zirex

что надо? дубли или общее количество по ID
определитесь :)
отсюда
AlexAuto
нужно же твойные записи (твойные и т.п.) посчитать, тоесть для иванова сколько ивановых с ID = 1, сколько с ID = 2 и сколько с ID =3

сложно понять чего же вам хочется :)


Я привел таблицу которая должна получится в итоге
AlexAuto

ID COUNT_1 COUNT_2 COUNT_3
1 1300 200 0
2 0 3000 39
3 400 50 4

Т.е. результирующая таблица должна содержать всего 3 строки соответственно для ID 1, 2 и 3
Вадиман
Дата: 26.05.2006 08:47:29
Есть такая замечательная штука - ROLLUP и CUBE. ROLLUP является как бы подмножеством куба.

Синтаксис:

SELECT * FROM (
SELECT a, b, c, COUNT(d), GROUPING(a) grp_a, GROUPING(b) grp_b, GROUPING(c) grp_c
FROM table1
GROUP BY ROLLUP(a, b, c)
)
WHERE grp_a = 1 AND grp_b = 1

В данном случае ROLLUP сделает то же самое, что и GROUP BY, но также добавит в выборку результативные (итоговые строчки). Итоги будут такие: общие (по колонкам a, b и c), только по a, только по a и b. Что возвращает функция GROUPING() - догадаетесь сами после выборки, очень просто :). 0 или 1. Если 1, то данная строка содержит итог по этой колонке.

Если сделаете группировку по CUBE, то тут вообще супер. Оракл выдаст ту же группировку, как и в GROUP BY, но добавит итоговые строчки ПО ВСЕВОЗМОЖНЫМ СОЧЕТАНИЯМ между a, b и с. ROLLUP делает только линейные справа налево. Вот что может выдать куб:

итоги общие
итоги по a
итоги по b
итоги по c
итоги по a, b
итоги по a,c
итоги по b, c


Пользуйтесь