iT邦幫忙

4

使用plpython 自製函數範例

create function pymul(lst_x integer[]) 
returns integer as
$$
from functools import reduce
import operator
return reduce(operator.mul, lst_x, 1)
$$ LANGUAGE plpython3u;

create table ithelp190724 (
  id INT GENERATED BY DEFAULT AS IDENTITY
, category char(1) not null
, kind char(1) not null
, val int not null
);

insert into ithelp190724 (category, kind, val) values
('A', 'X', 2), ('A', 'Y', 3), ('A', 'Z', 4),
('B', 'X', 3), ('B', 'Y', 5),
('C', 'X', 4), ('C', 'Y', 2), ('C', 'S', 1), ('C', 'T', 3);

-- as Window Function

with t1 as (
select id
     , category
     , kind
     , array_agg(val) over (partition by category order by kind
        rows between unbounded preceding
                 and current row) as arragg
  from ithelp190724
)
select *
     , pymul(arragg)
  from t1
;

+----+----------+------+-----------+-------+
| id | category | kind |  arragg   | pymul |
+----+----------+------+-----------+-------+
|  1 | A        | X    | {2}       |     2 |
|  2 | A        | Y    | {2,3}     |     6 |
|  3 | A        | Z    | {2,3,4}   |    24 |
|  4 | B        | X    | {3}       |     3 |
|  5 | B        | Y    | {3,5}     |    15 |
|  8 | C        | S    | {1}       |     1 |
|  9 | C        | T    | {1,3}     |     3 |
|  6 | C        | X    | {1,3,4}   |    12 |
|  7 | C        | Y    | {1,3,4,2} |    24 |
+----+----------+------+-----------+-------+
(9 rows)

-- as Aggregate Function

select category
     , pymul(array_agg(val))
  from ithelp190724
 group by category;

+----------+-------+
| category | pymul |
+----------+-------+
| B        |    15 |
| C        |    24 |
| A        |    24 |
+----------+-------+
(3 rows)


尚未有邦友留言

立即登入留言