Streamline row-to-row calculations for SQL-based computing

In database application development, we often need to face complex SQL-type calculations, such as row calculation is one of them, such as the monthly sales divided by the last month's sales, the last period, or the monthly sales divided by the same month sales, to seek the same time ratio. Some databases do not provide SQL2003 standard window functions (or incomplete support), the completion of the row calculation will need to change the idea of using connection (join) operation substitution, not only difficult to understand and inefficient operation. SQL statements are lengthy even if you can use window functions to still face problems such as nested subqueries. The use of the collector can be more simple and intuitive code to achieve the row calculation, the following is illustrated with an example.

Table Sales stores the order data for many years, based on the table to calculate the sales compared to the previous period and the same period for each month in the specified time period. Some of the source data are as follows:

The Collector code:

A1: Querying data from a database by time period, begin and end are external parameters, such as begin= "2011-01-01 00:00:00″,end=" 2014-07-08 00:00:00″. Some of the query results are as follows:

A2=a1.groups (Year (OrderDate): Y,month (OrderDate): M; sum (Amount): Mamount)
This code groups orders by year and month, and summarizes the monthly sales. The function groups can be grouped together, its parameters are divided into two parts, the semicolon is a grouping expression, that is:Year (OrderDate): Y,month (OrderDate): M, the semicolon is followed by a summary expression, which issum (Amount): Mamount. The summarized field is named Mamount. Some of the results are as follows:

A3=a2.derive (MAMOUNT/MAMOUNT[-1]:LRR)

This code adds a new field LRR based on the A2, which is the month-to-date, whose expression is mamount/mamount[-1]. The collector can use [n] or [-n] to express relative to the nth record after the current record, or the previous nth record, so the code Mamount represents the sales in the period, mamount[-1] represents the previous sales. The calculation results are as follows:

It is important to note that the first month's ratio of the previous period value is empty (that is, January 2011).

A4=a3.sort (m)
This code will be A3 by month, year, in order to calculate the same time ratio. The complete code should be: =a3.sort (m,y), because A3 is originally sorted by year, so you can achieve the goal by the month sort, namely A3.sort (m), so the performance is also high. Some of the results are as follows:

A5=a4.derive (if (m==m[-1],mamount/mamount[-1],null): YoY)

This code adds a new field YoY based on the A4, that is, the monthly sales of the same time ratio, whose expression is if (m==m[-1],mamount/mamount[-1],null), which means that the month is the same as the calculation of the same period. The function if has three arguments, where m==m[1-] is a Boolean expression, and when the condition is true, returns mamount/mamount[-1], when the condition is false, returns NULL. It is important to note that the period of the initial year (that is, 2011) is empty for each month. Some of the results are as follows:

< Span style= "Color:rgb (62,62,62); Font-family: Song body; font-size:14px; line-height:22px ">    If the observation inconvenience, you can add a line of code A6 =a5.sort (y:-1,m)

In addition, the collector can be called by the Report Tool or Java program, the method called is similar to the normal database, using the JDBC interface it provides to the Java main program to return the ResultSet form of calculation results, specific methods can refer to the relevant documents.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Streamline row-to-row calculations for SQL-based computing