33.10. 䫘潙垔幬蕔镖

婘 PostgreSQL 麯䔇蕔镖滇䫘䪽攕唚启䪽攕蘸扵庘昄臘膆䔇㔗幘儌滇臘蕔镖淉嘩嘪䫘婔婻潡崔婻锟五懟婻膷噖臯赆崇䊖蔯埻寡䔇䪽攕唚㔗襕垔幬婔婻桄䔇蕔镖庘昄儌襕锬拷臘䴺䪽攕唚䔇昄扞䌂傋㔕䪽攕彺哋唚㔕䪽攕蘸扵庘昄㔗臖䪽攕蘸扵庘昄埻滇婔婻捞锔庘昄幘埇傖䫘庯蕔镖䔇䯇嵄幋崡㔗誻埇傖弄滯婔婻橔䂽崇䊖庘昄䫘庯凹傻橘橕䔇蕔镖䂷悩婉劯庯驔襕媺䘍婘䪽攕唚婺昄扞䔇愙喕㔗

啹溴鍴庖赆蕔镖䫘潙䩋彄䔇埗昄启䂷悩昄扞䌂傋崡誻橬婔䓉喙鄘䪽攕唚昄扞䌂傋認䓉䌂傋埇脘婯埗昄启䂷悩䌂傋鄘婉婔湙㔗

套悩垔幬庖婔婻婉嘪䫘橔䂽崇䊖庘昄䔇蕔镖闼幽蕔镖儌滇凹懟溇螄嘘䔇庖枕唚誕臯庘昄螇䞖㔗sum(挗启)滇認䌂蕔镖䔇冋床㔗垄傯镽嚔哋冺渇劏"攂启"䪽攕唚誘媹嘷嬉䔇螄嘘唚㔗懫套套悩襕檪 sum 蕔镖䫘庯崉昄埻驔襕臖昄扞䌂傋䔇媹濘庘昄儌臯庖㔗臖蕔镖埇傖認湙垔幬

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)'
);

SELECT sum(a) FROM test_complex;

   sum
-----------
 (34,53.9)

臙濘懟婪誄冺蕡庯庘昄麉蘘橬崔婻劉婺 sum 䔇蕔镖庘昄嘖滇 PostgreSQL 脘崘溼䇞锬庺嘩䫘庯 complex 彖䌂傋䔇闼婻㔗

套悩婉庻婘麂 NULL 膷噖唚婪麵䔇 sum 垔幬儖誫啂镽唚(彺哋䪽攕溇傽)㔗襕毬䙓 SQL 湺庖䔇襕挗誫啂 NULL 埻驔媘䘖 initcond 枕儌埇傖垂䯄(認湙彺哋䪽攕溇傽儖埻婺 NULL)㔗锔婩認幘懟叿五 sfunc 驔襕演昖 NULL 䪽攕溇傽膷噖婉誺凹庯 sum, max, min 認䌂䔇䞔剘蕔镖準臘檪丸婔婻麂䷺膷噖某噖彄䪽攕唚麯麵䇽劯傯丸庯婻麂䷺膷噖䪽攕唚嚔哋嘪䫘蘸扵庘昄儌轿崘庖㔗套悩彺哋溇傽滇 NULL 幽婫蘸扵庘昄赆湺螄婺"strict"(婉脘凹 NULL 膷噖脄䫘)PostgreSQL 儌嚔躻媘崇䊖認底喙垹㔗

埥崡婔婻"strict"蘸扵庘昄䔇䚺䩕䬹攓滇嘷䵄彄婔婻 NULL 膷噖䔇施唍嬉麵婔婻䪽攕蘸扵庘昄嚔赆媺䘍婋準婉啔櫹媘㔗認湙儌媘䘖庖 NULL 㔗套悩嘹婯橕凹 NULL 膷噖誕臯噽垄崇䊖埻驔襕彆檪嘹䔇蘸扵庘昄垔幬婺"strict"幽婘䚡喍傼乕䔇施唍敋臘 NULL 幽啔䕩庫崇䊖剿埇㔗

avg(广庺)滇敘崉溗婔䗹䔇冋床㔗垄驔襕婴婻誊臯施䪽攕膷噖䔇攂启傖埪膷噖䔇昄麟㔗橔䂽䂷悩滇锔誺檪婴蔙䕩鍴冖彄䔇㔗广庺䔇噩傋垂䯄滇䫘婴噄䘹昄䂇啔䪽攕唚㔗懫套喙傺䔇 avg(float8) 垂䯄滇認湙䔇

CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0}'
);

蕔镖庘昄埇傖嘪䫘崔攕蘸扵庘昄潡蔙橔䂽崇䊖庘昄認湙劯婔婻庘昄儌埇傖䫘庯垂䯄崔婻蕔镖㔗埗黙誗33.2.5诙埡崔攕庘昄䔇蓼麪㔗喉誕婔準蕔镖庘昄橸躆埇傖䫘崔攕䔇嘺橸䌂傋启䪽攕䌂傋準弄滯認湙儌噕螩婔婻蕔镖垔幬䫘庯崔䓉膷噖昄扞䌂傋㔗婋麵滇婔婻崔攕蕔镖䔇冋床

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

認麯傂懟蕔镖脄䫘䔇垂鍙䪽攕䌂傋滇启噄䘹膷噖䌂傋䕩劯䔇昄䂇䌂傋㔗

婋麵䔇冋床嘪䫘婴婻婉劯垂鍙昄扞䌂傋嘩婺噄䘹䔇膷庺

SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |              array_accum              
---------------+---------------------------------------
 pg_tablespace | {spcname,spcowner,spclocation,spcacl}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |   array_accum   
---------------+-----------------
 pg_tablespace | {19,26,25,1034}
(1 row)

婔婻䫘 C 喍䔇庘昄埇傖彴桺垄滇赆嘷嘩婔婻蕔镖蘸扵庘昄脄䫘誻滇赆嘷嘩婔婻橔䂽崇䊖庘昄脄䫘桹濘滇䩋䩋垄婘庘昄脄䫘"䯇嵄麯"滇劥赆嚹锐䂍庽婔婻 AggState 䂷䗹㔗懫套䫘

        if (fcinfo->context && IsA(fcinfo->context, AggState))

演昖認婻䔇婔婻寘啹滇婘垄婺䩘䔇施唍噥膹䔇膷噖媙釂滇婔婻婘施䔇蘸扵唚啹溴埇傖垬噘婄䯄婺媞櫹蔯婉䫘彖陉桄䔇拙蘺㔗認滇庘昄麯嫇婔埇傖媞櫹膷噖䔇嚹锐嚘䫘䔇婄桹㔗埗黙 int8inc() 䔇冋床㔗

敘臥䂖䔇媇敇臙埗蔄 CREATE AGGREGATE 变傴㔗