I'm working on a little something to demonstrate use of analytic functions and how window clause works. For that I wish for each row that has the result of an analytic function to know which of all the rows is part of that result.
I did a little script on LiveSql:
https://livesql.oracle.com/apex/livesql/s/cfppbobpjp4lgmqp4i9ioflh9 I would have liked to be able to do something like this:
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
/
It fails with ORA-30487: ORDER BY not allowed here, where I would have liked it to return this instead:
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
Or even better yet, I would have liked this to work:
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
/
But it fails with ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_NT_COLLECT'
I have made a "somewhat" solution:
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
/
For my purposes it is "doable" as my demonstration setup I know exactly the number of rows, so I can do like this where I have 9 NTH_VALUE calls.
But I keep thinking there ought to be a better way somehow and hope someone might come up with something ;-)
The important bit is, that what I want is something that can accept the identical window clause as the SUM() call, and then give me which rows are part of that window. So I can change the window clause (both in the SUM() call and in the "something") and still get which rows gave each individual sum.
Any ideas?
Thanks
Kim Berg Hansen
Although the window clause doesn't work with listagg, it does with Tom Kyte's predecessor for this stragg:
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,100And here it is in LiveSQL:
https://livesql.oracle.com/apex/livesql/file/content_CFRK6W2PJ04EQTSVJQ2E1G4FI.html