Article summary:
The SQL syntax of KES is temporarily not compatible with the creation syntax and process of Oracle’s custom aggregate function, but it can be rewritten using the syntax that KES already supports.
This article organizes and briefly analyzes the principles of custom aggregate functions and interprets the sample code.
And rewritten according to customer code.
1. A brief analysis of oracle custom aggregate functions
Oracle’s custom aggregate function needs to implement four ODCIAggregate interface functions, which are declared and defined in an object type.
These functions define the operations that need to be implemented within any aggregate function.
These functions are initialization, iteration, merging (called when parallel aggregation enable parallel is defined) and termination. The following figure is the processing flow of a custom aggregate function:
1.Parallel process of custom aggregate function:
|--------------|
| |
\|/ |
ODCIAggregateInitialize -------> ODCIAggregateIterate
|
|
| ----->ODCIAGGREGATEMERGE ----->ODCIAGGREGATETERMINATE
|
|-------------|
| |
\|/ |
ODCIAggregateInitialize -------> ODCIAggregateIterate
Serial process of custom aggregate function (ODCIAGGREGATEMERGE will not be called in actual measurement):
|--------------|
| |
\|/ |
ODCIAggregateInitialize-------> ODCIAggregateIterate -----> ODCIAggregateTERMINATE
As can be seen from the above, ODCIAGGREGATEMERGE will be called in parallel, but ODCIAGGREGATEMERGE will not be called in non-parallel.
2. Instance analysis of oracle custom aggregate function
Take as an example the calculation of the second most valuable case
Interface definition:
create type SecondMaxImpl as object
(
--Customize the maximum value saved
max NUMBER,
--Customize to save the second largest value
secmax NUMBER,
--Initialization function, a method that must be implemented to initialize the context environment at the beginning of the aggregation operation
static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) return number,
--Iteration operation function, Oracle performs iteration operation based on this function,
--The first parameter self is the context of the aggregation operation,
--The second parameter value is the current value that needs to be processed, which can be number varchar2 and other types.
--During the iteration, if the current value is null, ignore the iteration
member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number) return number,
--(Oracle will choose to perform this step) This function is used to merge two contexts into one context, and may play a role in both parallel and serial environments.
member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN SecondMaxImpl) return number,
--This function is run in the last step of the aggregation operation and is used to process the results and return the processing results.
--The first parameter self is the context,
--The second parameter returnValue is the return value, which can be number, varchar2, etc.
--The third parameter flags is the identification bit
member function ODCIAggregateTerminate(self IN SecondMaxImpl, returnValue OUT number, flags IN number) return number
);
/
–Definition and interface implementation of aggregate functions
create or replace type body SecondMaxImpl is
static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)
return number is
begin
sctx := SecondMaxImpl(0, 0); --Initialization
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
self IN OUT SecondMaxImpl,
value IN number
)
return number
is
begin
if value > self.max then
self.secmax := self.max;
self.max := value;
elsif value > self.secmax then
self.secmax := value;
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self IN OUT SecondMaxImpl, --Merge two contexts (useful for parallel aggregation. Can it only be used for parallel aggregation?)
ctx2 IN SecondMaxImpl
)
return number
is
begin
if ctx2.max > self.max then
if ctx2.secmax > self.secmax then
self.secmax := ctx2.secmax;
else
self.secmax := self.max;
end if;
self.max := ctx2.max;
elsif ctx2.max > self.secmax then
self.secmax := ctx2.max;
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self IN SecondMaxImpl,
returnValue OUT number,
flags IN number
)
return number is
begin
returnValue := self.secmax; --Get the second largest value
--returnValue := self.max; --Get the maximum value
return ODCIConst.Success;
end;
end;
/
---Create aggregate functions
CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
–Construct test data to see the running effect
create table tt02(
id int ,
namevarchar(20),
sal number(6,2)
);
insert into tt02 values(12,'aaaa',1253.2);
insert into tt02 values(14,'aass',1965.3);
insert into tt02 values(13,'vvvv',2056.6);
insert into tt02 values(16,'aass',6562.1);
insert into tt02 values(15,'aass',4563.4);
insert into tt02 values(12,'aaaa',2532.2);
insert into tt02 values(14,'aass',1965.3);
insert into tt02 values(13,'vvvv',6556.6);
insert into tt02 values(16,'aass',8965.1);
insert into tt02 values(15,'aass',7854.4);
–Test verification (in line with expectations)
SQL> select id,SecondMax(sal) from tt02 group by id;
ID SECONDMAX(SAL)
---------- --------------
12 1253.2
13 2056.6
14 1965.3
16 6562.1
SQL> select /* + parallel */ id,SecondMax(sal) from tt02 group by id;
ID SECONDMAX(SAL)
---------- --------------
12 1253.2
13 2056.6
14 1965.3
16 6562.1
–Regenerate data
delete from tt02;
declare
vv1 int;
vv2 int;
vv3 varchar2(20);
begin
for i in 1..10 loop
select abs(mod(dbms_random.random,10000)) into vv1 from dual;
select abs(mod(dbms_random.random,5)) into vv2 from dual;
select dbms_random.string('u',5) into vv3 from dual;
insert into tt02 values(vv2,vv3,vv1);
end loop;
end;
SQL> select * from tt02;
ID NAME SAL
-------------------------------------------------- ----------
3 IJTIJ 5978
4 JUAWT 2087
0 FPBMN 2218
0 AMBNZ 618
2 VFXJD 37
4 PEXGM 7983
2 CDQJT 877
2ENQFX 3359
2 ICJFI 1220
0 XCGJX 1397
10 rows selected.
–Second verification (in line with expectations)
SQL> select id,SecondMax(sal) from tt02 group by id;
ID SECONDMAX(SAL)
---------- --------------
0 1397
2 1220
3 0
4 2087
SQL> select /* + parallel */ id,SecondMax(sal) from tt02 group by id;
ID SECONDMAX(SAL)
---------- --------------
0 1397
2 1220
3 0
4 2087
In fact, as far as rewriting is concerned, we already know the function of this function.
Second, KES syntax rewrites Oracle’s custom aggregate function
1, Oracle’s custom aggregate function definition and its analysis and verification
Oracle’s custom aggregate function sumc2 is as follows. Let’s first perform a simple analysis and functional verification on it:
--Object type header (interface declaration) CREATE OR REPLACE EDITIONABLE TYPE "TYPESUMVARCHAR2" as object ( vsum VARCHAR2(4000), --Customize the initialization settings of the aggregate function, start an aggregate function from here static function ODCIAggregateInitialize(sctx IN OUT TYPESUMVARCHAR2) return number, --Customize the aggregate function, the most important step. This function defines what our aggregate function does specifically. In the following example, it is to take the maximum value, minimum value, average value, or perform a connection operation. self is the current aggregate function Pointer, used to associate with previous calculation results member function ODCIAggregateIterate(self IN OUT TYPESUMVARCHAR2,value IN varchar2) return number, -- Terminate the processing of the aggregate function and return the result of the aggregate function processing member function ODCIAggregateTerminate(self IN TYPESUMVARCHAR2,returnValue OUT VARCHAR2, flags IN number) return number, --Used to merge the results corresponding to two different pointers of two aggregate functions. Users can merge data with different results, especially when processing parallel query aggregate functions. member function ODCIAggregateMerge(self IN OUT TYPESUMVARCHAR2,ctx2 IN TYPESUMVARCHAR2) return number );
Object type body (interface definition)
CREATE OR REPLACE EDITIONABLE TYPE BODY "TYPESUMVARCHAR2" IS
--Customize the initialization settings of the aggregate function, start an aggregate function from here
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(
SCTX IN OUT TYPESUMVARCHAR2
)
RETURN NUMBER IS
BEGIN
SCTX := TYPESUMVARCHAR2('');
--Initialize vsum
RETURN ODCICONST.SUCCESS;
END;
--Customized aggregate function, the most important step, this function defines what our aggregate function does specifically, the following examples,
--Whether to take the maximum value, minimum value, average value, or perform a connection operation. self is the pointer of the current aggregate function, used to associate with the previous calculation results
MEMBER FUNCTION ODCIAGGREGATEITERATE(
SELF IN OUT TYPESUMVARCHAR2,
VALUE IN VARCHAR2
) RETURN NUMBER IS
BEGIN
--SELF.vsum := substr(SELF.vsum || ',' || VALUE,0,2000);
IF instr(
SELF.vsum,
value
) = 0
OR SELF.vsum IS NULL THEN
SELF.vsum := substr(
SELF.vsum || ',' || VALUE,
0,
2000
) ;
ELSE
SELF.vsum := substr(
SELF.vsum || '',
0,
2000
) ;
END IF ;
RETURN ODCICONST.SUCCESS;
END;
-- Terminate the processing of the aggregate function and return the result of the aggregate function processing (the code is intercepted)
MEMBER FUNCTIONODCIAGGREGATETERMINATE(
SELF IN TYPESUMVARCHAR2,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER
) RETURN NUMBER IS
BEGIN
RETURNVALUE := SUBSTR(
SELF.vsum,
2
);
RETURN ODCICONST.SUCCESS;
END;
--Used to merge the results corresponding to two different pointers of two aggregate functions. Users can merge data with different results, especially when processing parallel query aggregate functions.
--No action is taken by default (merging is not performed)
MEMBER FUNCTION ODCIAGGREGATEMERGE(
SELF IN OUT TYPESUMVARCHAR2,
CTX2 IN TYPESUMVARCHAR2
)
RETURN NUMBER IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;
END;
–Create a custom aggregate function
CREATE OR REPLACE FUNCTION sumc2(input varchar2) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING TYPESUMVARCHAR2;
According to the actual measurement of the above tt02 table
SQL> select * from tt02;
ID NAME SAL
-------------------------------------------------- ----------
3 IJTIJ 5978
4 JUAWT 2087
0 FPBMN 2218
0 AMBNZ 618
2 VFXJD 37
4 PEXGM 7983
2 CDQJT 877
2ENQFX 3359
2 ICJFI 1220
0 XCGJX 1397
SQL> select id,sumc2(name) from tt02 group by id;
ID SUMC2(NAME)
-------------------------------------------------- ----------------------------------
0 FPBMN,XCGJX,AMBNZ
2 VFXJD,ICJFI,ENQFX,CDQJT
3 IJTIJ
4 JUAWT,PEXGM
Actual measurement of replacement table:
--teachers table is as follows: teacher_id | teacher_name | age | sal | gender | title | position | department ------------ + -------------- + ----- + ---------- + ----- --- + ---------- + ------------ + ------------ 10001001 | Chen Siyu | 46 | 15689.00 | Male | Special Teacher | Principal | Principal's Office 10001002 | Wen Qiang | 44 | 29942.00 | Male | Special Teacher | Vice Principal | Principal’s Office 10001003 | Wu Ling | 41 | 29142.00 | Female | Senior Teacher | Office Director | Office 10001004 | Zhang Li | 41 | 28242.00 | Female | Senior Teacher | Director of Academic Affairs Office | Academic Affairs Office 10001005 | Zhang Zhidong | 41 | 28242.00 | Male | Senior Teacher | Director of Finance Department | Finance Department 10001006 | Xiong Haoyu | 49 | 28356.00 | Female | First-level teacher | Director of Admissions Office | Admissions Office 10001007 | Zhu Wen | 49 | 24016.00 | Female | First-level teacher | Admissions office assistant | Admissions office 10001008 | Zhang Zhiqiang | 49 | 23964.00 | Female | First-level teacher | Assistant to Finance Department | Finance Department 10001009 | Zhu Guobin | 49 | 21974.00 | Male | Second level teacher | Assistant to Finance Department | Finance Department
oracle test results
select age,sumc2(title) from teachers group by age; 41 senior teachers 44 Special Teacher 46 Special Teacher 49 first-level teacher, second-level teacher
Through the actual measurement of the code and actual effects, the aggregation function sumc2 is used to remove duplication and obtain the splicing result after deduplication.
After understanding the customer’s native code and Oracle’s custom aggregation functions,
2, Basic principles of aggregate functions supported by KES
The syntax of aggregate functions (PLPGSQL) supported by KES is as follows:
CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
SFUNC = sfunc, ---iteration function, each row of data iteratively calls the calculation result
STYPE = state_data_type ----The data type returned by the aggregate function
[ , SSPACE = state_data_size ]
[ , FINALFUNC = ffunc ] ----The final calculation function of each group, optional
[ , FINALFUNC_EXTRA ]
[, COMBINEFUNC = combinefunc] ---Aggregation COMBINEFUNC function, after enabling it, PARALLEL can be enabled in parallel
[ , SERIALFUNC = serialfunc ]
[ , DESERIALFUNC = deserialfunc ]
[ , INITCOND = initial_condition ] ---INITCOND is the value passed to the first parameter when calling SFUNC for the first time and does not need to be written.
[ , MSFUNC = msfunc ]
[ , MINVFUNC = minvfunc ]
[ , MSTYPE = mstate_data_type ]
[ , MSSPACE = mstate_data_size ]
[ , MFINALFUNC = mffunc ]
[ , MFINALFUNC_EXTRA ]
[ , MINITCOND = minimal_condition ]
[ , SORTOP = sort_operator ]
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ] ----SAFE parallel aggregation
)
The key points to mention are:
The aggregate function is calculated independently for each group. For example, based on the age aggregation (GROUP BY age) of the above teachers table, it will be divided into 4 groups, and the 4 groups will be calculated internally.
--teachers table is as follows: teacher_id | teacher_name | age | sal | gender | title | position | department ------------ + -------------- + ----- + ---------- + ----- --- + ---------- + ------------ + ------------ 10001001 | Chen Siyu | 46 | 15689.00 | Male | Special Teacher | Principal | Principal's Office 10001002 | Wen Qiang | 44 | 29942.00 | Male | Special Teacher | Vice Principal | Principal’s Office 10001003 | Wu Ling | 41 | 29142.00 | Female | Senior Teacher | Office Director | Office 10001004 | Zhang Li | 41 | 28242.00 | Female | Senior Teacher | Director of Academic Affairs Office | Academic Affairs Office 10001005 | Zhang Zhidong | 41 | 28242.00 | Male | Senior Teacher | Director of Finance Department | Finance Department 10001006 | Xiong Haoyu | 49 | 28356.00 | Female | First-level teacher | Director of Admissions Office | Admissions Office 10001007 | Zhu Wen | 49 | 24016.00 | Female | First-level teacher | Admissions office assistant | Admissions office 10001008 | Zhang Zhiqiang | 49 | 23964.00 | Female | First-level teacher | Assistant to Finance Department | Finance Department 10001009 | Zhu Guobin | 49 | 21974.00 | Male | Second level teacher | Assistant to Finance Department | Finance Department
1), SFUNC iteration function, assuming it is customized as follows:
CREATE OR REPLACE FUNCTION YOUR_SFUNC_NAME (numeric, numeric, numeric......)
RETURNS numeric
as
begin
.......(omitted)
end
This function is the iteration function of each row of data
Parameter 1: $1, the calculation result of the last iteration;
Parameter two: $2, the first parameter of YOUR_AGGREGATE_NAME (if the aggregate function passes in a table column, it represents the current row data)
Parameter three: $3, the second parameter of YOUR_AGGREGATE_NAME (if it is a fixed value, such as the value 2, pass in 2 each time)
Parameter four: $4, the third parameter of YOUR_AGGREGATE_NAME
………..
2), FINALFUNC final function, assuming it is customized as follows:
CREATE OR REPLACE FUNCTION YOUR_FINALFUNC_NAME (numeric) ---There is only one parameter, the SFUNC function returns numeric, so here is numeric
RETURNS numeric ---Aggregation return type, so here is numeric
as
begin
.......(omitted)
end
After each group is calculated through the YOUR_SFUNC_NAME function, it is called one last time, which means that one final rule calculation can be performed.
3), YOUR_AGGREGATE_NAME aggregate function
The definition of the aggregate function will refer to the above-mentioned YOUR_SFUNC_NAME and YOUR_FINALFUNC_NAME functions to ultimately achieve the calculation of a specific goal.
A typical non-parallel usage example;
CREATE AGGREGATE YOUR_AGGREGATE_NAME(numeric, numeric) ---Accepts two parameters, which will be used as the last two parameters of the SFUNC function
(
INITCOND = xxxx, ---INITCOND is the first time the YOUR_SFUNC_NAME function is called, and the value xxxx is passed to the first parameter, which does not need to be written.
STYPE = numeric, ---The data type numeric returned by the aggregate function
SFUNC = YOUR_SFUNC_NAME, ---Custom iteration function for each group
FINALFUNC = YOUR_FINALFUNC_NAME --- Summary function for each group
);
After mastering the above basic syntax information, you can now write custom aggregate functions.
Referenced to this article:
Postgresql custom aggregate function entry case_pgsql custom aggregate function_Gao Mingjie’s blog-CSDN blog
DROP AGGREGATE myavg(integer);
CREATE TABLE t_taxi(trip_id int, km numeric);
insert into t_taxi values (1, 3.4);
insert into t_taxi values (1, 5.3);
insert into t_taxi values (1, 2.9);
insert into t_taxi values (2, 9.3);
insert into t_taxi values (2, 1.6);
insert into t_taxi values (2, 4.3);
trip_id | km
--------- + -----
1 | 3.4
1 | 5.3
1 | 2.9
2 | 9.3
2 | 1.6
2 | 4.3
----------------
CREATE OR REPLACE FUNCTION taxi_accum (numeric, numeric, numeric)
RETURNS numeric AS
$$
BEGIN
RAISE NOTICE 'prev:[%] curr:(%) outer:(%) return:(%)', $1, $2, $3, $1 + $2 * $3;
RETURN $1 + $2 * $3;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION taxi_final (numeric)
RETURNS numeric AS
$$
BEGIN
RAISE NOTICE 'final:(%) return:(%)', $1, round($1 + 5, -1);
RETURN round($1 + 5, -1);
END;
$$
LANGUAGE 'plpgsql';
CREATE AGGREGATE taxi(numeric, numeric)
(
SFUNC = taxi_accum,
STYPE = numeric,
FINALFUNC = taxi_final,
INITCOND = 3.50
);
--test
test=# SELECT trip_id, taxi(km, 2.20), 3.50 + sum(km)*2.2 AS manual FROM t_taxi GROUP BY trip_id;
test-#/
NOTICE: prev:[3.50] curr:(3.4) outer:(2.20) return:(10.980)
NOTICE: prev:[10.980] curr:(5.3) outer:(2.20) return:(22.640)
NOTICE: prev:[22.640] curr:(2.9) outer:(2.20) return:(29.020)--Perform SFUNC function iterative calculation for the group with trip_id 1
NOTICE: prev:[3.50] curr:(9.3) outer:(2.20) return:(23.960)
NOTICE: prev:[23.960] curr:(1.6) outer:(2.20) return:(27.480)
NOTICE: prev:[27.480] curr:(4.3) outer:(2.20) return:(36.940)--Perform SFUNC function iterative calculation for the group with trip_id 2
NOTICE: final:(29.020) return:(30)--Finally calculate the result returned by the SFUNC function (the first value of the parameter) for the group with trip_id 1
NOTICE: final:(36.940) return:(40)--Finally calculate the result returned by the SFUNC function (the first value of the parameter) for the group with trip_id 2
trip_id | taxi | manual
--------- + ------ + --------
1 | 30 | 29.02
2 | 40 | 36.94
(2 rows)
Time: 1.775 ms
--You can see that the basic aggregate function (non-parallel) process is
--1, use the SFUNC function to iteratively calculate each set of data
--2. After each set of data is calculated, use FINALFUNC to convert the SFUNC results of each set
3, KES custom aggregate function rewritten
After rewriting using plpgsql syntax (corresponding to the sumc2 function of Oracle above):
--Define the iteration function for each row of data
CREATE OR REPLACE FUNCTION agg(v1 varchar,v2 varchar)
RETURNS varchar IMMUTABLE
AS
$$
BEGIN
if v1 is null or LENGTH(v1) = 0 then
v1 := substr(v2, 0, 2000);
ELSIF v2 is null and v1 is not null then
v1 := substr(v1, 0, 2000);
ELSIF instr( v1, v2 ) = 0 then ---If it does not contain a relationship (then splice)
v1 := substr(v1 || ',' || v2 , 0 , 2000) ;
else
v1 := substr(v1, 0, 2000);
end if ;
return v1;
end;
$$LANGUAGE 'plpgsql';
--Define the final function for each group
CREATE OR REPLACE FUNCTION agg_final (varchar)
RETURNS varchar AS
$$
BEGIN
RETURN $1; ---This example does not require any additional processing
END;
$$
LANGUAGE 'plpgsql';
---Define aggregate functions
CREATE OR REPLACE AGGREGATE sumc2 (varchar)
(
SFUNC = agg, --- Custom iteration function for each group
STYPE = varchar, ---The data type returned by the aggregate function
FINALFUNC = agg_final, ---The final function of each group
INITCOND ='' --INITCOND is the first time the agg function is called, passing the value of the first parameter.
);
Verify the basic functions of the above rewritten aggregate function
kes test:
--Create table
CREATE table teachers (
teacher_id number(8) not null,
teacher_name varchar2 (40) not null,
age number(2),
sal number(8,2) default 0,
gender varchar2(10),
title varchar2(20),
POSITION varchar2(20),
department varchar2 (40) not null,
constraint teach_ch_gender check ( gender in ('male','female')),
constraint teach_ch_title check (title in ('Third-level teacher','Second-level teacher','First-level teacher','Senior teacher','Special teacher')),
primary key(teacher_id,teacher_name)
);
--Create data
insert into teachers values(10001001,'Chen Siyu',46,15689.00,'male','special teacher','principal','principal's office');
insert into teachers values(10001002,'Wen Qiang',44,14971.00,'Male','Special Teacher','Vice Principal','Principal's Office');
insert into teachers values(10001003,'Wu Ling',41,14571.00,'female','senior teacher','office director','office');
insert into teachers values(10001004,'Zhang Li',41,14121.00,'female','senior teacher','director of the academic affairs office','academic affairs office');
insert into teachers values(10001005,'Zhang Zhidong',41,14121.00,'Male','Senior Teacher','Director of Finance Department','Finance Department');
insert into teachers values(10001006,'Xiong Haoyu',49,14178.00,'female','first-level teacher','admissions office director','admissions office');
insert into teachers values(10001007,'Zhu Wen',49,12008.00,'female','first-level teacher','admissions office assistant','admissions office');
insert into teachers values(10001008,'Zhang Zhiqiang',49,11982.00,'Female','First-level teacher','Finance Department Assistant','Finance Department');
insert into teachers values(10001009,'Zhu Guobin',49,10987.00,'Male','Second Teacher','Finance Department Assistant','Finance Department');
--View table
test=# select * from teachers;
test-#/
teacher_id | teacher_name | age | sal | gender | title | position | department
------------ + -------------- + ----- + ---------- + ----- --- + ---------- + ------------ + ------------
10001001 | Chen Siyu | 46 | 15689.00 | Male | Special Teacher | Principal | Principal's Office
10001002 | Wen Qiang | 44 | 29942.00 | Male | Special Teacher | Vice Principal | Principal’s Office
10001003 | Wu Ling | 41 | 29142.00 | Female | Senior Teacher | Office Director | Office
10001004 | Zhang Li | 41 | 28242.00 | Female | Senior Teacher | Director of Academic Affairs Office | Academic Affairs Office
10001005 | Zhang Zhidong | 41 | 28242.00 | Male | Senior Teacher | Director of Finance Department | Finance Department
10001006 | Xiong Haoyu | 49 | 28356.00 | Female | First-level teacher | Director of Admissions Office | Admissions Office
10001007 | Zhu Wen | 49 | 24016.00 | Female | First-level teacher | Admissions office assistant | Admissions office
10001008 | Zhang Zhiqiang | 49 | 23964.00 | Female | First-level teacher | Assistant to Finance Department | Finance Department
10001009 | Zhu Guobin | 49 | 21974.00 | Male | Second-level teacher | Assistant to the Finance Department | Finance Department
(9 rows)
--test
test=# select age,sumc2(title) from teachers group by age;
test-#/
age|sumc2
----- + -------------------
49 | First-level teacher, second-level teacher
44 | Special Teacher
46 | Special Teacher
41 | Senior Teacher
(4 rows)
Time: 1.517 ms
Create a new test table and test again
drop table tt02;
create table tt02 (
id int,
namevarchar2(200)
);
--kes:
declare
v int;
begin
for i in 1..100000 loop
select ceil(random()*10000 + 1) into v from dual;
insert into tt02 values(v,'special teacher');
select ceil(random()*10000 + 1) into v from dual;
insert into tt02 values(v,'senior teacher');
end loop;
end;
--oracle:
declare
v int;
begin
for i in 1..1000 loop
select dbms_random.value(0,100000) into v from dual;
insert into tt02 values(v,'special teacher');
select dbms_random.value(0,100000) into v from dual;
insert into tt02 values(v,'senior teacher');
end loop;
end;
test=# select * from tt02;
id|name
---- + ----------
1 | Special Teacher
2 |
3 | Special Teacher
4 | Intermediate Teacher
5 | Special Teacher
6 |
7 | Special Teacher
8 | Intermediate Teacher
(8 rows)
test=# select sumc2(name) from tt02;
test-#/
sumc2
-------------------
special teacher,intermediate teacher
(1 row)
Therefore, the test results are consistent with Oracle and meet basic expectations.
Note: The above code content does not consider parallel conditions, and will be explained in alignment later.