Oracle 查找哪些行是带窗口子句解析函数结果的一部分的方法

我正在做一个小的东西来演示解析函数的使用以及窗口子句是如何工作的。为此，我希望具有分析函数结果的每一行都知道该结果的一部分是所有行中的哪一行。

我在LiveSql上做了一个小脚本：https://livesql.oracle.com/apex/livesql/s/cfppbobpjp4lgmqp4i9ioflh9

我也希望能做这样的事情：

select department_id, employee_id, last_name, salary
     , sum(salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) range_sum
     , listagg(employee_id,',') within group (
          order by salary
       ) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) who_in_range
  from hr.employees
 where department_id in (30,90)
 order by department_id, salary
/

ORA-30487失败：此处不允许使用，我希望返回此选项：

DEPARTMENT_ID EMPLOYEE_ID LAST_NAME  SALARY  RANGE_SUM WHO_IN_RANGE
------------- ----------- ---------- ------ ---------- ------------
           30         119 Colmenares   2500       2500 119
           30         118 Himuro       2600       5100 119,118
           30         117 Tobias       2800       7900 119,118,117
           30         116 Baida        2900      10800 119,118,117,116
           30         115 Khoo         3100      13900 119,118,117,116,115
           30         114 Raphaely    11000      24900 119,118,117,116,115,114
           90         102 De Haan     17000      34000 101,102
           90         101 Kochhar     17000      34000 101,102
           90         100 King        24000      58000 101,102,100

或者更棒的是，我希望这个能成功：

create type employees_table_type as table of number
/

select department_id, employee_id, last_name, salary
     , sum(salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) range_sum
     , cast(collect(employee_id order by salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) as employees_table_type) who_in_range
  from hr.employees
 where department_id in (30,90)
 order by department_id, salary
/

但它在ORA-06553中失败了： PLS-306 ：调用“SYS_NT_COLLECT”时的参数数目或类型错误

我提出了一个“有点”的解决方案：

select department_id, employee_id, last_name, salary
     , sum(salary) over (
          partition by department_id
          order by salary
          range between unbounded preceding and current row
       ) range_sum
     , employees_table_type(
          nth_value(employee_id,1) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,2) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,3) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,4) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,5) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,6) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,7) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,8) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
        , nth_value(employee_id,9) over (
             partition by department_id
             order by salary
             range between unbounded preceding and current row
          )
       ) who_in_range
  from hr.employees
 where department_id in (30,90)
 order by department_id, salary
/

对于我的目的来说，它是“可行的”，因为我的演示设置确切知道行数，所以我可以在有9个NTH_VALUE调用的情况下这样做。

但我一直在想应该有更好的办法，希望有人能想出点什么办法来。

重要的一点是，我想要的是，它可以接受和调用一样的窗口子句，然后给我哪些行是该窗口的一部分。所以我可以更改窗口子句（在调用(Sum (）和“something”中) ，仍然可以得到给出每个单独和的行。

有什么想法吗？

谢谢
金伯格汉森

虽然窗口子句不适用于listag ，但它适用于Tom Kyte的前身，用于此straggg ：

create or replace type stragg_type as object
(
  string varchar2(4000),

  static function ODCIAggregateInitialize
    ( sctx in out stragg_type )
    return number ,

  member function ODCIAggregateIterate
    ( self  in out stragg_type ,
      value in     varchar2
    ) return number ,

  member function ODCIAggregateTerminate
    ( self        in  stragg_type,
      returnvalue out varchar2,
      flags in number
    ) return number ,

  member function ODCIAggregateMerge
    ( self in out stragg_type,
      ctx2 in     stragg_type
    ) return number
);
/

create or replace type body stragg_type
is

  static function ODCIAggregateInitialize
  ( sctx in out stragg_type )
  return number
  is
  begin

    sctx := stragg_type( null ) ;

    return ODCIConst.Success ;

  end;

  member function ODCIAggregateIterate
  ( self  in out stragg_type ,
    value in     varchar2
  ) return number
  is
  begin

    self.string := self.string || ',' || value ;

    return ODCIConst.Success;

  end;

  member function ODCIAggregateTerminate
  ( self        in  stragg_type ,
    returnvalue out varchar2 ,
    flags       in  number
  ) return number
  is
  begin

    returnValue := ltrim( self.string, ',' );

    return ODCIConst.Success;

  end;

  member function ODCIAggregateMerge
  ( self in out stragg_type ,
    ctx2 in     stragg_type
  ) return number
  is
  begin

    self.string := self.string || ctx2.string;

    return ODCIConst.Success;

  end;

end;
/

create or replace function stragg
  ( input varchar2 )
  return varchar2
  deterministic
  parallel_enable
  aggregate using stragg_type;
/

select employee_id, salary,
       sum(salary) over (
         partition by department_id 
         order by salary range between unbounded preceding and current row
       ) tot_all_prev , 
       stragg(employee_id) over (
         partition by department_id 
         order by salary range between unbounded preceding and current row
       ) str_all_prev,
       sum(salary) over (
         partition by department_id 
         order by salary rows between 2 preceding and current row
       ) tot_2_prev , 
       stragg(employee_id) over (
         partition by department_id 
         order by salary rows between 2 preceding and current row
       ) str_2_prev
from   hr.employees
where  department_id in (30,90)
order  by department_id, salary;

EMPLOYEE_ID     SALARY TOT_ALL_PREV STR_ALL_PREV              TOT_2_PREV STR_2_PREV    
----------- ---------- ------------ ------------------------- ---------- ---------------
        119       2500         2500 119                             2500 119            
        118       2600         5100 119,118                         5100 119,118        
        117       2800         7900 119,118,117                     7900 119,118,117    
        116       2900        10800 119,118,117,116                 8300 118,117,116    
        115       3100        13900 119,118,117,116,115             8800 117,116,115    
        114      11000        24900 119,118,117,116,115,114        17000 116,115,114    
        102      17000        34000 102,101                        17000 102            
        101      17000        34000 102,101                        34000 102,101        
        100      24000        58000 102,101,100                    58000 102,101,100

下面是LiveSQL中的内容：

https://livesql.oracle.com/apex/livesql/file/content_CFRK6W2PJ04EQTSVJQ2E1G4FI.html