쿠...sal: [컴][DB] PostgreSQL 에서 aggregate function

Aggregate function

곱하기에 대한 aggregate function 은 제공되지 않는다. 기본적인 aggregate function 은 만들어져 있다. 아래 글에서 작성하는 법을 확인할 수 있다.

multiply(num) aggregate function in postgresql - Stack Overflow

저장되는 schema

기본적으로 schema 를 지정하지 않으면 current schema 에 aggregate function 이 저장된다. 참고로 같은 schema 내에서만 aggregate function 을 사용할 수 있다.

함수 작성하기

보통 아래 2개 함수를 통해 aggregate function 을 만든다.

state transition function : sfunc( internal-state, next-data-values ) ---> next-internal-state
final calculation function : ffunc( internal-state ) ---> aggregate-value

여기서는 간단하게 sfunc 만 알아보자.

while True:
stype_var = sfunc(stype_var, current_value)
current_value = newval
ffunc()

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

pgAdmin III 에서 Aggregates function / type 보기
기본적으로 Aggregates 와 type 이 보이지 않는다. 아래처럼 option 에서 추가해 주면 된다.

File > Options > Aggregates 를 check , type 을 check > tree 를 refresh

User-defined Aggregate function 만들기

이미 만들어져 있는 aggregate 함수 보기

아래 경로에서 이미 만들어져 있는 aggregate function 들을 확인할 수 있다. 여기에 만들어져 있는 녀석을 참고해서 namh_avg 라는 함수를 새롭게 만들어 보자.

Databases > Catalogs > PostgreSQL > Aggregates

aggregate 함수 생성

아래 query 를 실행시켜 보자. 그러면 namh_avg 라는 이름의 aggregate function 이 만들어 진다.

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

그러면 아래처럼 aggregate function 이 보인다.

namh_avg 가 보인다.

sfunc

sfunc 에는 function 이름을 적어주면 된다. 기본적으로 poostgreSQL 에 이미 만들어져 있는 함수를 사용할 수도 있는데, 그것은 아래 경로에서 찾을 수 있다.

Databases > postgres > Catalogs > PostgreSQL > Functions

아래 float8_accum 의 정의를 보자. 이 정의는 아래경로에서 찾을 수 있다.

Databases > postgres > Catalogs > PostgreSQL > Functions
List of functions : function list 를 web 에서 볼 수 있다.

CREATE OR REPLACE FUNCTION float8_accum(double precision[], double precision)
  RETURNS double precision[] AS
'float8_accum'
  LANGUAGE internal IMMUTABLE STRICT
  COST 1;
ALTER FUNCTION float8_accum(double precision[], double precision)
  OWNER TO postgres;
COMMENT ON FUNCTION float8_accum(double precision[], double precision) IS 'aggregate transition function';

stype

aggregate function 에서는 여러번 반복해서 함수를 실행해야 하기에, sfunc 을 사용해서 계산한 결과를 저장할 임시변수가 필요하다. 이 임시변수는 DB(PostgreSQL) 에서 만드는데 type 은 우리가 정해줘야 한다. 그 방법이 stype (state-type)을 정해주는 것이다.

즉, 우리가 state 를 저장하는 임시변수의 type 을 aggregate function 을 만들 때 지정해 주게 된다.(위의 stype 을 확인하자.)

PostgreSQL 은 current internal state 를 저장하려고, stype 이라는 type 의 임시변수를 만든다. 그래서 이 변수에 current internal state 를 저장하고, 이 녀석을 sfunc 의 첫번째 argument 로 넘긴다.

while True:
    stype_var = sfunc(stype_var, current_value)
    current_value = newval
ffunc()

그러므로, 위에서 sfunc 로 쓰인 함수 "float8_accum" 의 return type 을 보고 stype 을 정하면 된다. 위에서 "double precision[]" 로 되어 있다. 그래서 type 중에 float8[] 을 이용한 것이다. float8[] 에 대해서는 아래 'type 확인방법' 을 보자.

이 type은 PostgreSQL 의 type 을 이용할 수도 있고, Create Type 을 통해 직접 만들 수도 있다.

type 확인 방법

이런 type 들은 아래 경로에서 확인할 수 있다. 위의 namh_avg 에서 쓰인 "float8[]" 도 아래 경로에서 찾을 수 있다.

Databases > postgres > Catalogs > PostgreSQL > Types

곱하기 aggregate 함수

-- multiply aggregate function
CREATE AGGREGATE namh_float8mul (float8)
(
    sfunc = float8mul,
    stype = float8
    
);


-- how to use
select namh_float8mul(mvalue) OVER (PARTITION BY type) FROM factors_table

쿠...sal

[컴][DB] PostgreSQL 에서 aggregate function