Data processing of PostgreSQL process language learning

Some time ago, when the company replaced the new PostgreSQL Data mart system, it downloaded the Postgresqlapi PDF file and researched the PostgreSQL data mart. Discover the use of PostgreSQL process language can greatly speed up the efficiency of their own processing of data, let's give an example! I believe that after you look, you will also like the PostgreSQL process language ....

First, a SQL script is given that queries the September 10, 2015 of the overdue data:

?

123456789

SELECT DISTINCTA.DAY_ID 统计日期,A.CHANNEL_NAME 支局名称,A.OWE_MONTH    欠费账期,SUM(B.current_charge) 日回收FROM 表1 A LEFT JOIN 表2 B ON A.SERV_ID=B.SERV_IDWHERE A.AREA_ID=27 AND B.mkt_area_ID=27 AND A.DAY_ID=20150910 AND A.OWE_MONTH =201508 AND B.ACCT_MONTH=201508GROUP BY A.DAY_ID,A.CHANNEL_NAME,A.OWE_MONTH

Although this script can extract a day's overdue data, then the question comes, if you want to extract the September 1 to 10 of the arrears of data, it is not to execute this script 10 times?

Of course, there is a better way to extract the arrears data, that is, the SQL script is written in the PostgreSQL process language, and then only need to execute a custom function to extract 10 days of overdue data, which greatly improve their efficiency.

Finally, the modified PostgreSQL process language function and Postgresqlapi PDF file (Chinese version amount!) are given. ）：

Finally, the modified PostgreSQL process language function and Postgresqlapi PDF file (Chinese version amount!) are given. ）：

?

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950

函数名wx_qf_hdb(统计日期, 01508);  格式: wx_qf_hdb(20150901, 201508);功能:提取目前欠费帐龄截止统计日期的各支局欠费回收总数*/--startCREATE FUNCTION wx_qf_hdb(day_id numeric,acct_month numeric) RETURNS TEXT AS $$DECLARELS_SQL         VARCHAR(5000);today          VARCHAR(8);qf_month        VARCHAR(6);month_l        INTEGER;num            numeric;BEGINtoday := TRIM(to_char(day_id,99999999));--截止日期转为字符串qf_month := TRIM(to_char(acct_month,999999));--欠费月份转为字符串num := to_number(substr(today,1,6),999999);--区字符串的前6位month_l := to_date(to_char(day_id+1,‘9999-99-99‘),‘yyyy-mm-dd‘)-to_date(to_char(num,‘9999-99‘)||‘-01‘,‘yyyy-mm-dd‘);--计算当月天数FOR i IN 1..month_l LOOP    LS_SQL := ‘create table temp_wuxi_qf‘||i||‘ as SELECT DISTINCTA.DAY_ID   统计日期,A.CHANNEL_NAME 支局名称,A.OWE_MONTH    欠费账期,SUM(B.current_charge) 日回收FROM 表1A LEFT JOIN 表2 B ON A.SERV_ID=B.SERV_IDWHERE A.AREA_ID=27 AND B.mkt_area_ID=27 AND A.DAY_ID=‘||to_char(day_id-month_l+i,99999999)||‘ AND A.OWE_MONTH =‘||qf_month||‘ AND B.ACCT_MONTH=‘||qf_month||‘GROUP BY A.DAY_ID,A.CHANNEL_NAME,A.OWE_MONTH‘;    EXECUTE LS_SQL;END LOOP;--提取日数据LS_SQL := ‘create table temp_wuxi_qf‘||qf_month||‘ as ‘;FOR i IN 1..month_l LOOP     LS_SQL := LS_SQL||‘SELECT * FROM temp_wuxi_qf‘||i||‘ UNION ‘;END LOOP;LS_SQL := LS_SQL||‘SELECT * FROM temp_wuxi_qf‘||month_l;EXECUTE LS_SQL;--汇总日数据FOR i IN 1..month_l LOOP    LS_SQL:=‘DROP TABLE temp_wuxi_qf‘||i;    EXECUTE LS_SQL;END LOOP;--删除所有临时表RETURN LS_SQL;END;$$ LANGUAGE plpgsql;--endselect wx_qf_hdb(20150903,201508); --运行函数wx_qf_hdb(统计日期,欠费帐期)DROP FUNCTION wx_qf_hdb(day_id numeric,acct_month numeric); --删除函数select * from temp_wuxi_qf欠费帐期;--查询表drop table temp_wuxi_qf欠费帐期; -- 删除表

Http://pan.baidu.com/s/1mg09t5Q (postgresqlapi file download

Interested friends can themselves try to study the PostgreSQL process language function, really for the long-term people engaged in extracting data help a lot

Data processing of PostgreSQL process language learning