将系统的数据库从MySQL 5.5迁移到PostgreSQL 9.1(三)
oup_no,
09
SUM(liquid_injected_input_num) AS total_input,
10
SUM(short_circuit_num) AS total_short,
11
COUNT(DISTINCT(lot_no)) AS month_num_of_product_days
12
,ROUND(SUM(liquid_injected_input_num) / COUNT(DISTINCT(lot_no))) AS sample_size_n
13
,ROUND(SUM(short_circuit_num) / SUM(liquid_injected_input_num), 4) AS nonconforming_rate_mean_p
14
FROM hipot, (SELECT @rownum := 0) AS r WHERE liquid_injected_input_num!=0
15 www.2cto.com
GROUP BY `model_no`, group_no, year_and_month
16
;
17
18
END//
19
DELIMITER ;
PostgreSQL
view sourceprint
01
DROP FUNCTION IF EXISTS calcUlclp();
02
03
CREATE OR REPLACE FUNCTION calcUlclp()
04
RETURNS SETOF record AS
05
$BODY$
06
declare
07
-- sql varchar;
08
rownum int;
09
v_rc record;
10
BEGIN
11
for v_rc in
12 www.2cto.com
SELECT (rownum = rownum + 1) AS id, to_char(lot_no_to_date, 'yyyyMM') AS year_and_month,
13
model_no, group_no,
14
SUM(liquid_injected_input_num) AS total_input,
15
SUM(short_circuit_num) AS total_short,
16
COUNT(DISTINCT(lot_no)) AS month_num_of_product_days
17
,ROUND(SUM(liquid_injected_input_num) / COUNT(DISTINCT(lot_no))) AS sample_size_n
18
,ROUND(SUM(short_circuit_num) / SUM(liquid_injected_input_num), 4) AS nonconforming_rate_mean_p
19
FROM hipot, (SELECT rownum = 0) AS r WHERE liquid_injected_input_num!=0
20
GROUP BY model_no, group_no, year_and_month
21
loop
22
return next v_rc;
23
end loop;
24
25
END;
26
$BODY$
27
LANGUAGE 'plpgsql' VOLATILE;
28 www.2cto.com
29
-- 调用存储过程
30
/*
31
SELECT * from calcUlclp() as
32
t(id_ boolean, year_and_month text, model_no varchar, group_no varchar,
33
total_input bigint, total_short numeric, month_num_of_product_days bigint,
34
sample_size_n double precision, nonconforming_rate_mean_p numeric);
35
*/
作者 leeoo