需求:由于要求PostgreSQL表内的数据需要根据前端传递的参数过滤后再聚合出结果,考虑到前端工作量,所以决定由前端传递过滤条件,后端创建存储过程来直接返回计算后的表,因为PG9.5没有存储过程功能,故用函数来代替;
目标:这里就不说公司真实的需求了,就计算一个产品达标率吧,要求分别按照客户、产品、年月周日维度计算一次
注意点
- 有时候前端传的参数只有开始日期和结束日期(这两个必传),其他参数不传,这时候要把不传的参数从wehre条件中去除掉
- 有时候一个参数会传递多个值,比如产品参数会传产品1、产品2…传递形式为
'产品1,产品2'
,需要字符切割,在where条件过滤的时候,这两个产品都应该留下来
方法:postgreSQL中把字符串split切割成数组,网上说找不到这个方法,实际查看官方文档是有的,方法名regexp_split_to_array
,判断字符串在不在这个数组中使用以下方法,可以实现where条件中 in 的效果
元素 = any(regexp_split_to_array(字符串,','))
SQL语句如下:
前端传入四个参数,得到一张表,其中后两个参数可以为null,也可以为用逗号连接的字符串
CREATE FUNCTION get_rate (varchar, varchar, varchar, varchar) RETURNS TABLE(dim text,dim_val varchar,pass_qty int8, total_qty int8, pass_rate text) AS
$$
DECLARE
val_start_date ALIAS FOR $1;
val_end_date ALIAS FOR $2;
val_prod_code ALIAS FOR $3;
val_cust_name ALIAS FOR $4;
BEGIN
RETURN QUERY
select case when "cust_name" is not null then '客户'
when "prod_code" is not null then '产品编码'
when "yr" is not null then '年'
when "yr_mth" is not null then '月'
when "yr_wk" is not null then '周'
when "yr_mth_day" is not null then '日'
else 'ALL'
end as dim
,coalesce(cust_name,prod_code,yr,yr_mth,yr_wk,yr_mth_day,'ALL') AS dim_val
,count(is_prod_pass = '是' or null) AS pass_qty
,count(*) AS total_qty
,concat( round(count(is_prod_pass = '是' or null)::numeric / count(*) * 100,2),'%') AS pass_rate
from table_name
where yr_mth_day >= val_start_date and yr_mth_day <= val_end_date
and coalesce(prod_code = any(regexp_split_to_array(val_prod_code,',')),1=1)
and coalesce(cust_name = any(regexp_split_to_array(val_cust_name,',')),1=1)
group by grouping sets((cust_name),(prod_code),(yr),(yr_mth),(yr_wk),(yr_mth_day));
END;
$$
LANGUAGE plpgsql;