sql - SQL分组依据用户定义函数的输出

Question

在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不同，其他所有字段都相同。同样，这仅是该数据示例的说明。

我编写了一个函数来提取分配给data列中字段的值，并在以下查询中使用它:

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": invalid identifier 00904. 00000 - "%s: invalid identifier"

从我对“google”的初步研究中，我已经看到也许应该对传递给用户定义函数的列进行分组。这是由于SQL要求所有列(不是聚合函数的一部分)都需要成为group by子句的一部分。

这将对某些记录有效，但是在我的数据示例中，每个记录的data列中的字段ghi均不同，因此使数据列唯一，并破坏了group by子句，因为每个记录的计数为1 。

我过去曾经使用过sybase和db2，并且(在这里自己陷入了困境……)我很确定自己都能够对用户定义函数的输出进行分组。

我认为列的命名可能存在问题，并且group by如何引用它们？按列号引用无效。

我已经尝试过各种组合，但无法使用，因此，感谢您提供的任何见解。

如果您需要更多信息，我将根据需要进行编辑或在评论中进行澄清。

谢谢，
GC。

Answer 1

您应该能够按功能本身而不是别名进行分组

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.

EMP表中有16行

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.