SQL Group By на выходе из пользовательской функции
Возможно ли в Oracle группировать данные на выходе определенной пользователем функции? Когда я пытаюсь это сделать, я получаю ошибки, и это лучше всего иллюстрируется приведенным ниже примером:
Я пытаюсь запросить результаты в структуре таблицы, аналогичной приведенной ниже:
id | data
1000 | {abc=123, def=234, ghi=111, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=123, def=234, ghi=222, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=123, def=434, ghi=333, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=123, def=434, ghi=444, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=123, def=634, ghi=555, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=923, def=634, ghi=666, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=923, def=434, ghi=777, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=923, def=434, ghi=888, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=923, def=234, ghi=999, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
1000 | {abc=923, def=234, ghi=000, jkl=456, mno=567, pqr=678, stu=789, vwx=890, yza=901}
Есть и другие столбцы, просто не показанные. Столбец id может иметь разные значения, но в этом примере это не так. В столбце данных различаются только поля abc, def и ghi, все остальные совпадают. Опять же, это только иллюстративно для этого примера данных.
Я написал функцию для извлечения значения, назначенного для полей в столбце данных, и он используется в следующем запросе:
select id
,extract_data(data,abc) as abc
,extract_data(data,def) as def
from table
давать результаты:
id | abc | def
1000 | 123 | 234
1000 | 123 | 234
1000 | 123 | 434
1000 | 123 | 434
1000 | 123 | 634
1000 | 923 | 634
1000 | 923 | 434
1000 | 923 | 434
1000 | 923 | 234
1000 | 923 | 234
В целях отчетности я хотел бы иметь возможность отображать количество записей каждого типа. В приведенном выше примере есть 6 типов, и в идеале результат будет:
id | abc | def | count
1000 | 123 | 234 | 2
1000 | 123 | 434 | 2
1000 | 123 | 634 | 1
1000 | 923 | 634 | 1
1000 | 923 | 434 | 2
1000 | 923 | 234 | 2
Я ожидал, что достигну этого, написав SQL следующим образом (и я уверен, что делал это раньше):
select id
,extract_data(data,abc) as abc
,extract_data(data,def) as def
,count(1)
from table
group by id
,abc
,def
Это, однако, не будет работать. Oracle дает мне ошибку:
ORA-00904: "ABC": неверный идентификатор 00904. 00000 - "%s: неверный идентификатор"
Из моего первоначального исследования "Google" я понял, что, возможно, мне следует сгруппировать столбец, который я передаю в свою пользовательскую функцию. Это может быть связано с тем, что SQL требует, чтобы все столбцы, не являющиеся частью агрегатной функции, должны были быть частью предложения group by.
Это будет работать для некоторых записей, однако в моем примере данных поле ghi в столбце данных различно для каждой записи, что делает столбец данных уникальным и разрушает предложение group by, так как для каждой записи дается число 1,
Я использовал sybase и db2 в прошлом, и (настраивая себя на падение здесь...) Я почти уверен, что я смог сгруппировать по выводу пользовательской функции.
Я подумал, что может быть проблема с именами столбцов и как группа может ссылаться на них? Ссылка по номеру столбца не сработала.
Я пробовал различные комбинации того, что у меня есть, и не могу заставить его работать, поэтому я был бы благодарен за любую информацию, которую вы, ребята, могли бы дать.
Если вам нужна дополнительная информация, я буду редактировать по мере необходимости или уточнять в комментариях.
Спасибо, GC.
3 ответа
Вы должны быть в состоянии группировать по самим функциям, а не по псевдонимам
select id
,extract_data(data,abc) as abc
,extract_data(data,def) as def
,count(*)
from table
group by id
,extract_data(data,abc)
,extract_data(data,def)
Обратите внимание, что это обычно не включает выполнение функции несколько раз. Вы можете увидеть это с помощью простой функции, которая увеличивает счетчик в пакете каждый раз, когда он вызывается.
SQL> ed
Wrote file afiedt.buf
1 create or replace package pkg_counter
2 as
3 g_cnt integer := 0;
4* end;
SQL> /
Package created.
SQL> create or replace function f1( p_arg in number )
2 return number
3 is
4 begin
5 pkg_counter.g_cnt := pkg_counter.g_cnt + 1;
6 return mod( p_arg, 2 );
7 end;
8 /
Function created.
Есть 16 строк в EMP Таблица
SQL> select count(*) from emp;
COUNT(*)
----------
16
поэтому, когда мы выполняем запрос, который включает группировку по вызову функции, мы надеемся, что функция будет выполнена только 16 раз. И это, собственно, то, что мы видим.
SQL> select deptno,
2 f1( empno ),
3 count(*)
4 from emp
5 group by deptno,
6 f1( empno );
DEPTNO F1(EMPNO) COUNT(*)
---------- ---------- ----------
1 1
30 0 4
20 1 1
10 0 2
30 1 2
20 0 4
10 1 1
0 1
8 rows selected.
SQL> begin
2 dbms_output.put_line( pkg_counter.g_cnt );
3 end;
4 /
16
PL/SQL procedure successfully completed.
Попробуй это:
select id, abc, def, count(1)
from
(
select
id,
extract_data(data,abc) as abc,
extract_data(data,def) as def
from table
)
group by id, abc, def
Ты пытался:
SELECT
id,
extract_data(data, abc) as abc,
extract_data(data, def) as def,
COUNT(1)
FROM
table
GROUP BY
id,
extract_data(data, abc)
extract_data(data, def)