Data Processing-PostgreSQL process Language Learning

Data Processing-PostgreSQL process Language Learning

Some time ago, when the company switched to the new PostgreSQL data mart system, it downloaded the postgresqlAPI PDF file to study the PostgreSQL data mart. I found that using PostgreSQL process language can greatly speed up data processing efficiency. The following is an example! I believe you will also like PostgreSQL process language after reading it .......

First, an SQL script is provided to query the overdue data for September 10, 2015:

SELECT DISTINCT
A. DAY_ID statistical date
, A. CHANNEL_NAME branch name
, A. OWE_MONTH overdue payment period
, SUM (B. current_charge)
FROM table 1 a left join Table 2 B on a. SERV_ID = B. SERV_ID
Where a. AREA_ID = 27 and B. mkt_area_ID = 27 and a. DAY_ID = 20150910 and a. OWE_MONTH = 201508 and B. ACCT_MONTH = 201508
Group by a. DAY_ID, A. CHANNEL_NAME, A. OWE_MONTH

Although this script can extract the overdue data for one day, the problem arises. If you want to extract the overdue data for September 1-10, isn't it necessary to execute this script ten times?

Of course, there is a better way to extract the arrears data, that is, write the SQL script into the PostgreSQL process language, and then you only need to execute the custom function to extract the overdue data for ten days, this greatly improves your work efficiency.

Finally, the improved PostgreSQL process language function and PostgreSQL api pdf file (Chinese Version!) are provided !) :

Function Name wx_1__hdb (statistical date, 01508); format: wx_1__hdb (20150901,201 508 );
Function: extract the total number of accounts in Arrears collected by the current aging Period
*/
-- Start
Create function wx_cmd_hdb (day_id numeric, acct_month numeric) returns text as $
DECLARE
LS_ SQL VARCHAR (5000 );
Today VARCHAR (8 );
Pai_month VARCHAR (6 );
Month_l INTEGER;
Num numeric;
BEGIN
Today: = TRIM (to_char (day_id, 99999999); -- convert the end date to a string
Pai_month: = TRIM (to_char (acct_month, 999999); -- convert the month into a string
Num: = to_number (substr (today, 999999),); -- the first 6 digits of the zone string
Month_l: = to_date (to_char (day_id + 1, '2017-99-99 '), 'yyyy-mm-dd')-to_date (to_char (num, '2017-99 ') | '-01', 'yyyy-mm-dd'); -- calculates the number of days in the current month.
FOR I IN 1 .. month_l LOOP
LS_ SQL: = 'create table temp_wuxi_qf' | I |'
SELECT DISTINCT
A. DAY_ID statistical date
, A. CHANNEL_NAME branch name
, A. OWE_MONTH overdue payment period
, SUM (B. current_charge)
FROM table 1A left join Table 2 B on a. SERV_ID = B. SERV_ID
Where. AREA_ID = 27 and B. mkt_area_ID = 27 and. DAY_ID = '| to_char (day_id-month_l + I, 99999999) |' AND. OWE_MONTH = '| pai_month |' and B. ACCT_MONTH = '| pai_month |'
Group by a. DAY_ID, A. CHANNEL_NAME, A. OWE_MONTH ';
EXECUTE LS_ SQL;
End loop;
-- Extract daily data
LS_ SQL: = 'create table temp_wuxi_qf' | pai_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;
-- Collect daily data
FOR I IN 1 .. month_l LOOP
LS_ SQL: = 'drop TABLE temp_wuxi_qf' | I;
EXECUTE LS_ SQL;
End loop;
-- Delete all temporary tables
RETURN LS_ SQL;
END;
$ LANGUAGE plpgsql;
-- End
Select wx_1__hdb (20150903,201508); -- run the function wx_1__hdb (statistical date, overdue account)
Drop function wx_cmd_hdb (day_id numeric, acct_month numeric); -- delete a FUNCTION
Select * from temp_wuxi_qf overdue account; -- Query table
Drop table temp_wuxi_qf overdue account; -- delete a table

PostgreSQL API File Download

------------------------------------------ Split line ------------------------------------------

Free in http://linux.bkjia.com/

The username and password are both www.bkjia.com

The specific download directory is for PostgreSQL process language learning/26/

For the download method, see

------------------------------------------ Split line ------------------------------------------

If you are interested, you can try to study PostgreSQL process language functions by yourself. It is really helpful for those who have been engaged in data extraction for a long time.

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: