3/16/2016

Handling 10 SQL Problems with R-like Languages

SQL is a sophisticated and all-around database programming language, making most instances of structured-data computing a painless experience. Yet there are still some instances that are difficult to handle in SQL in computer programming.

Here’s an example. duty is a MySQL table of shift schedule, in which an employee works several weekdays continuously before another employee takes his place. The task is to find out the beginning and ending of every shift based on the duty table. Below is the source table:
workday
name
2016-02-05
Ashley
2016-02-08
Ashley
2016-02-09
Ashley
2016-02-10
Johnson
2016-02-11
Johnson
2016-02-12
Johnson
2016-02-15
Ashley
2016-02-16
Ashley
2016-02-17
Ashley
Here’s the desired table:
name
begin
end
Ashley
2016-02-05
2016-02-09
Johnson
2016-02-10
2016-02-12
Ashley
2016-02-15
2016-02-17
An intuitive approach is to sort records by date, then group them by name, and finally retrieve the first and last records of each group. But it’s hard to implement it in SQL. As for MySQL, it doesn’t support window functions. So you have to shift your thinking to take the long way around: invent 4 temporary tables as the data manipulation basis, with a number field added to each, and perform joins between them by associating records with different number field values, and finally group, aggregate and sort the resulting joining table.


select name, begin, end from(
SELECT a.name, a.workday begin, MIN(c.workday) end
  FROM (SELECT *,@a:=@a+1 rank FROM t,(SELECT @a:=0) vars ORDER BY workday) a
  LEFT JOIN (SELECT *,@b:=@b+1 rank FROM t,(SELECT @b:=0) vars ORDER BY workday) b
    ON b.name = a.name AND b.rank = a.rank - 1
  LEFT JOIN (SELECT *,@c:=@c+1 rank FROM t,(SELECT @c:=0) vars ORDER BY workday) c
    ON c.name = a.name AND c.rank >= a.rank
  LEFT JOIN (SELECT *,@d:=@d+1 rank FROM t,(SELECT @d:=0) vars ORDER BY workday) d
    ON d.name = a.name AND d.rank = c.rank + 1
 WHERE b.workday IS NULL AND c.workday IS NOT NULL AND d.workday IS NULL
 GROUP BY a.name,a.workday
  ) e order by begin

This piece of SQL code is long and difficult to understand. The code would be easier if the database supports window functions, but still it wouldn’t be simple enough.

Actually, this isn’t the only scenario where SQL performs badly. Other cases include handling the subgroups, order-related calculations, related computing and multi-step calculation. SQL’s inability is a result of incomplete set orientation, as well as the lack of explicit sets, ordered sets, and support for individual records and step-by-step calculation.

Facing these SQL weaknesses, you should choose R-like languages that supports explicit sets, ordered sets, individual records and step-by-step calculation to import the data before manipulating it. The R-like languages include Python(Pandas), esProc, R and others.Among them esProc is the most integration-friendly and convenient-to-use.

Here’s the esProc code of implementing the above task:

The group function groups records without performing aggregation (while SQL forces an aggregation). The @o option working with it forbids a re-sorting when performing the group operation, and creates a new group only when the value of the grouping criterion changes (but SQL forces a re-sorting). The new function creates a two-dimensional table based on each group of the grouped table; ~.m(1) stands for the first record of each group and ~.m(-1) stands for the last record of each group.

But you should be aware that the scripting language you use can’t replace SQL, but that it is responsible for transforming difficult SQL queries to a form of simple SQL statement plus simple script. It is suitable for being used to handle data computing tasks involving small data but complex logic, or heterogeneous data sources. It’s not a good choice when handling big data computing with simple logic, because the IO cost of importing data would be very high.

Here are some examples of those SQL problems and their solutions in esProc. All of them are simplified and adapted from the real world cases posted on the internet.

Manipulating column data dynamically
The physicalExam table contains PE grades with the following fields: Athlete, Dash, Long-distance running, Long jump, Shot put… There are four grades for each event – excellent, good, pass and fail. The task is to find out the number of people for each grade in all events.

SQL believes column is a static attribute of table data, and doesn’t provide set-oriented operations for column data. It will be extremely cumbersome to handle the task in SQL. esProc, however, supports dynamic column access with simple code:

Non-equi-grouping
It’s common to group records by certain intervals, such as grade ranges (excellent, good …) and age groups (young people, middle-aged people…). It’s always a complicated job to perform grouping by these intervals in SQL. SQL can perform grouping by a few fixed ranges using the conditional statement case when; but it needs to create temporary tables and perform non-equi-joins when there are many intervals or the conditional intervals are continuous but dynamic. 

esProc penum function can return the sequence numbers of the enumerated conditions. Other times the pseg function can be conveniently used to find out the sequence number of one of a series of continuous intervals specified in the form of a sequence, and together with the groups function, can easily realize the non-equi-grouping:

A related scenario is sorting data in a specified order, which means that you specify the exact order, rather than use the standard order, to display the data manipulation results. For example, the capital city is always put in the first place when arranging data by administrative regions. SQL handles this type of sorting in a similar way as it handles grouping by conditional intervals. When the sorting criterion is simple, decode function is used; but if the criterion is long or dynamic, temporary tables need to be created and joins would be performed to generate sequence numbers.

esProc specifically provides align@s function for this type of sorting in alignment. The code is simple:

Equi-grouping doesn’t produce an empty subset, but non-equi-grouping may have one. For the latter, some tasks require that the result be continuous ranges. Therefore the missing values of the empty subset should be supplied. To do this in SQL, you need to create continuous conditional ranges manually for the grouping, and then perform left join on it and the data table, during which the complicated subqueries are necessary. The whole process is awfully cumbersome. esProc align function is specific to this requirement with convenient method of preparing the interval to which the table is aligned, making the process much simpler.

Here’s a transaction records table – transaction, whose structure is code, date and amount. The task is to calculate the accumulated transaction amount by week, with weeks having no transaction records listed.

Handling subgroups
Because SQL lacks an explicit set data type, it will force an aggregation after data grouping. But sometimes you also want the subset of the set of groups, like finding out the grade records of the students whose total scores are above 500.

With SQL, you would first group records to calculate the total score of each student and find out those whose scores are above 500, and then perform join between the name list and the achievement table or use the IN operator to specify the selected names in the achievement table. The implementation is indirect and data will be retrieved repeatedly:
with t as (select student from achievement group by student having sum(score)>500)
select tt.* from t left join achievement tt on t.student =tt.student

Having set data type and group functions that can return the subgroup, esProc is able to handle effortlessly the subgroups generated by data grouping:

Even if only the aggregate values are desired, the subgroups are worth keeping for further handling because the current aggregate operation may be too special to be performed by simple aggregate functions.

Here’s such an example. The user access table, access, has two fields – user (ID) and login (access time). The task is to find out the last login time per ID and the number of this ID’s logins within 3 days before the last time.

A SQL approach will first group the records to find out the last login time per ID, then join with the original table and get the records in the specified time period and again perform grouping and aggregation. It is roundabout and inefficient.
with t as (select "user",max(login) last from access group by "user")
select t."user",t.last,count(tt.login) from t left join access tt on t."user"=tt."user"
where t.last-tt.login<=3 group by t."user",t.last

Since esProc can retain the subgroups, it’s easy to realize a step-by-step calculation:

In the code, “~” stands for the subgroup generated from grouping records by user.

A more efficient method could be used to get the result if the records are already sorted:
Order-related aggregation
Finding top N from each group and getting record with the maximum value are also common in data handling. For example, the user access table access has the following structure: user, login time, IP…. The task is to list the first login record per user.

SQL will use a window function to generate the sequence numbers for records in each group and get all records whose sequence numbers are 1. As the window function works on the basis of a result set, you should first create a subquery before performing filtering, making the code a little complex:
select * from (select rank() over(partition by "user" order by login) rk,t.* from access t) a
where a.rk=1;

But this expression doesn’t apply to the SQL dialects that don’t support window functions.

The esProc method is using group@1 to get the first member from each group straightforwardly.

The following example is about finding Top N from each group. The structure of the stock price table transaction is code, transaction date and closing price. The task is to calculate the latest growth rate of each stock.

The growth rate calculation involves the records of the last trading days. SQL would use two levels of window functions to separately perform the intra-group inter-row calculation and then get the first row of the intermediate table. The expression is difficult to understand:
with tt as (select rank() over(partition by share order by date desc)  rk,t.* from transaction t)
select share,(select tt.closingPrice from tt where tt.share =ttt.share and tt.rk=1)-
(select tt.closingPrice from tt where tt.share =ttt.share and tt.rk=2) increase from transaction ttt group by share

esProc has topN function for performing aggregation on a set of records (grouped records).
The desired records can be obtained efficiently according to sequence numbers if the original records are already sorted:

Both finding the record with the maximum value and getting the first/last record are special cases of finding top N.

Inverse grouping
The inverse grouping is at the opposite end of the spectrum from the grouping and aggregation. It splits each of aggregated records into multiple detail records.

The installment table installment includes these fields – number, total amount, beginning date, number of periods. The task is to divide each loan into a number of payment records having the structure of number, period, payment date and amount. The total amount will simply be distributed evenly to every period of one month.

It’s easy to summarize data, but it’s difficult to do the opposite. The common ways in which SQL expands a record are performing a join with a sequence number table and using recursive query. Both are roundabout. By contrast, esProc enables writing the code in an intuitive way:

Cross-row reference
The sales table holds the monthly sales amount for each product. Its fields are product, month and amount. The task is to find out the records in which the amount increases by 10% compared with the previous month.

The calculation involves cross-row reference. The early SQL standards don’t support cross-row reference. Sequence numbers need to be generated for performing a join. The procedure is achingly complicated and difficult. Thanks to the introduction of window functions, SQL is able to make the cross-row reference more conveniently, but far from concisely due to the subquery. The code will be lengthy when there are multiple referenced items.
with t as (select a.*, nvl(volume/lag(volume) over(partition by product order by month,0) "relative"
from sales a) select * from t where relative>1.1

esProc provides the intuitive and convenient-to-use cross-row reference syntax.
Conveniently, [-1] can be used to reference a value of the previous month after data is sorted. The syntax also makes it possible to filtering data based on the results of inter-row calculations.

Then based on the above sales table, we calculate the moving averages of the sales amounts across a period of time including the current month, its previous month and the following month.

The moving average calculation involves forward reference and the reference of sets. A MySQL variable can’t express a forward reference, making a direct calculation impossible. Window functions can make it but they produce bloated and unintelligible code by using subqueries:
with b as(select a.*,lag(volumn) over(partition by product order by month) f1,
lead(volumn) over (partition by product order by month) f2 from sales a)
select product, month, volumn,
(nvl(f1,0)+nvl(f2,0)+volumn)/(decode(f1,null,0,1)+decode(f2,null,0,1)+1) MA from b

esProc uses [1] to reference a value from the next record and {-1:1} to reference a set consisting values of the previous, the current and the next months. With them the code is easy to write:

Order-related grouping
The budget table records incomes and expenses with three fields – month, income and expense. The task is to find out the records where the income is less than the expense for at least 3 continuous months.

In this case the key value based on which records are grouped can’t be obtained directly from the records. It is related to the order of the records. SQL would do it by creating sequence numbers, producing bloated code. esProc provides the grouping method for order-related calculations, facilitating those aiming at finding continuous records. Here’s the esProc code:

The expression group@o means grouping records by comparing only the neighboring records. By comparing the values of income and expense the records can be divided into groups of budget surplus, budget deficit, budget surplus… Then get the budget deficit groups that have at least 3 members and concatenate them.

You can also try to find out the biggest number of consecutive months when the income increases. For this the following grouping strategy can be used: group the current record with the previous one when income increases, and put it into a new group when income decreases. Finally you can get the number of members in the group where income increases continuously.

The SQL code for implementing this grouping algorithm is hard to understand:
select max(continuousMonthNnumber) from(select count(*)continuousMonthNnumber    from (select sum(isIncrease) over(order by month) notIncreaseMonthNumber from
(select month,income,case when income >lag(income) over(order by month) then 0 else 1 end isIncrease from budget))group by notIncreaseMonthNumber)

But esProc can create a new group when the grouping criterion changes (i.e. when income decreases) using group@i, generating clear and easy-to-understand code:
Merging intervals is another type of order-related group operation. Here’s an example. The event table T has two fields S (starting time) and E (ending time). The task is to merge the overlapped intervals to find out the total duration of the event.
The code offers methods of achieving different computing goals which capture the essence of cross-row operation and order-related operation and express them in a most intuitive way. It’s impossible to implement both operations in SQL using merely the window functions. The more sophisticated recursive query is required.

Record accessing by sequence number

Median price is frequently needed during the analysis of economic statistics. The value is easily calculated if you can access the records by sequence numbers. But you should create the sequence numbers and then perform filtering with SQL used. The code is tedious and inefficient:
select avg(price) from
(select price,row_number() over (order by price) r from priceTable ) t
where r=trunc((select count(price)+1 from priceTable)/2) or r=trunc((select count(price) from priceTable)/2)+1

With support for ordered sets, esProc can generate simple code:
The sequence numbers are useful in data grouping. The event table event has the following structure – number, time, action (including starting time and ending time). The task is to calculate the total duration of the whole event. That is, summing up the duration between each pair of starting action and ending action.

SQL inherits the mathematical concept of the unordered sets, stipulating that sequence numbers be created and conditional filtering be performed in order to access members in the specified positions:
with t as (select a.*,trunc((row_number() over (order by ID)-1)/2) r from event a)
select ROUND(TO_NUMBER(max(time)-min(time)) * 24 * 60) from t group by r

esProc, however, performs member accessing with simple and clear code:
The sign “#” represents the sequence number of a record. group((#-1)\2) place every two records into one group. After that esProc calculates the duration for each group and sums up the results.
        
Sequence numbers are easy to use when referencing a value from a neighboring row. The transaction table contains stock prices in two fields trading date and closing price. The task is to list the trading days when the stock prices are above ¥100 and calculate the growth rate each of those trading days.

You can’t calculate the growth rate after filtering records by the condition closing price>100. So with SQL you should first calculate all the growth rates and perform a filtering with the window function. This makes the code difficult to understand:
with t as (select date, closingPrice, closingPrice-lag(closingPrice) over(order by date) increase from transaction)
select * from t where closingPrice >100 and increase is not null

The esProc solution is using pselect function to find out the sequence numbers of those records that satisfying the condition. With the sequence numbers, you can easily calculate the desired growth rates, without having to calculate all growth rates and then perform a filtering as the window function does.
Using sets for string handling
A class table classtb include three fields – class, male students, and female students. The task is to change its structure and make it a student table that includes these fields – class, name, and gender.

SQL has group_concat for concatenating strings. But as with this case, strings need to be split to form records. Lacking explicit sets, SQL resorts to recursive query or a join with an alignment table. Both methods are complex. Being a dynamic language supporting explicit sets, esProc can handle this type of inverse grouping operation in a much easier way:
At times the object of string splitting is to perform set operations. Here’s a book table named book with the following structure – title and author. Each author value includes multiple people separated by the comma and with an unfixed order. The task is to find out the records where the names of the authors appear in at least two books.
Split all the author strings to form a set and sort them, then use members of the ordered set as grouping key values to perform the subsequent operations.

Using sets for date handling
Like string handling, the database is capable of dealing with normal, single date values. But it has difficulty in splitting a time interval or in generating a sequence of dates due to SQL’s vital weakness of incomplete set orientation.

The travel table contains travelling logs in multiple fields – name, starting date, ending date…. The task is to find out the 5 peak days in terms of the number of tourists.

The task requires splitting the interval defined by the starting date and the ending date into a set of single dates. esProc offers special function for doing this with the support of set data type, and thus can handle the task effortlessly:
Generating a sequence of dates is intrinsically difficult, particularly when it involves an inverse grouping and especially in SQL.

The event table T has three fields – I (event), S (starting date) and E (ending date). The task is to split the interval between the starting dates and ending dates to generate multiple intervals by month. The first month starts from the starting date and the last month ends at the ending date. The months between them have all their dates.

The pdate function works with @m option and @me options separately to get the first day and the last day of each month. after@m gets the date which is a number of months after a certain date. It will automatically adjust the new date to the last day of the corresponding month and is useful in generating a monthly interval. 

2/23/2016

A Standard Way of Realizing Dynamic Data Sources for Report Creation

Sometimes you need to reference data sources dynamically through a parameter, merge data sources into one, or dynamically pass data source name to a subreport/Table control. Often reporting tools - especially those with support for single data source, such as BIRT and Jasper – have to use a high-level language to accomplish these requirements, or trade security for reduced complexity.

esProc encapsulates rich functions for handling structured data, as well as supports dynamically parsing expressions and handling multiple/heterogeneous data sources, and realizing dynamic data sources with simple scripts. Therefore, it is suitable for serving as the computing tool for preparing data needed for building reports. The reporting tool executes an esProc script as it executes a database stored procedure, passes parameters to the script and gets the returned esProc result through JDBC. Learn more from How to Use esProc to Assist Reporting Tools.

Now let’s look at the typical problems about dynamic data sources and their esProc solutions:

Switching between data sources dynamically

myDB1 and oraDB are data sources that point to different databases. Each holds a sOrder table with the same structure. The report requires connecting to data sources dynamically via a parameter, querying sOrder for orders whose amounts are greater than 1,000, and displaying them.

Below is a selection from the sOrder table in myDB1:
OrderID
Client
SellerId
Amount
OrderDate
1
WVF
5
440
2009-02-03
2
UFS
13
1863
2009-07-05
4
JFS
27
671
2009-07-08
5
DSG
15
3730
2009-07-09
6
JFE
10
1445
2009-07-10
7
OLF
16
625
2009-07-11
8
PAER
29
2491
2010-07-12
9
DY
20
518
2010-07-15
10
JDR
17
1120
2010-07-16
Here is a selection from the sOrder table in oraDB:
OrderID
Client
SellerId
Amount
OrderDate
101
WAN
22
396
2010-11-07
102
SPL
15
142
2010-11-08
103
LA
23
713
2010-11-11
104
ERN
5
5678
2010-11-11
105
FUR
28
154
2009-11-12
106
BSF
27
10742
2009-11-13
107
RHD
4
569
2009-11-14
108
BDR
12
480
2010-11-15
109
OFS
17
1106
2009-11-18
esProc script:
=${pSource}.query("select * from sOrder where Amount>?",pAmount)
Both pSource and pAmount are report parameters. pSource represents the data source name; ${…} indicates parsing a string or a string variable into an expression. pAmount stands for the order amount.


When pSource=“myDB1”, A1 has the following result:

When pSource=“oraDB”, A1 gets this result:

Performing a multi-data-source pre-join

The mySQL database stores a Sales table holding orders from different sellers per day. Its SellerID field contains seller numbers. In MSSQL database there is an emp table of seller information in which EID field contains seller numbers. Create a report to display order numbers, order dates, order amounts, seller names and their departments, based on the condition that the orders should be within the last N days (say 30 days) or belong to certain important departments (say Marketing and Finance). Below are selections of the original tables:

Database table sales
OrderID
Client
SellerId
Amount
OrderDate
1
WVF Vip
1
440
2014-11-03
2
UFS Com
1
1863
2015-01-01
3
SWFR
2
1813
2014-11-01
4
JFS Pep
2
671
2015-01-01
5
DSG
1
3730
2015-01-01
6
JFE
1
1445
2015-01-01
7
OLF
3
625
2015-01-01
8
PAER
3
2490
2015-01-01

Database table emp
EId
State
Dept
Name
Gender
Salary
Birthday
2
New York
Marketing
Ashley
F
11001
1980-07-19
3
New Mexico
Sales
Rachel
F
9000
1970-12-17
4
Texas
HR
Emily
F
7000
1985-03-07
5
Texas
R&D
Ashley
F
16000
1975-05-13
6
California
Sales
Matthew
M
11000
1984-07-07
7
Illinois
Sales
Alexis
F
9000
1972-08-16
8
California
Marketing
Megan
F
11000
1979-04-19
1
Texas
HR
Victoria
F
3000
1983-12-07

esProc script:

A1, A2: Database queries. myDB1 and myDB2 point to MySQL and MSSQL respectively.
A3: Replace A1’s SellerID field with the corresponding records in A2 according to the key field EID. The result is as follows (the data items in blue contain sub-members):

By default, when there is not a corresponding record in A2 for a SellerID value, switch function retains the A1’s record while the SellerID shows an empty value. The effect is like a left join. Use @i option if you want to perform an inner join. The code is A1.switch@i(SellerId,A2:EId) .

A4: Filter on the result of join. The first filtering criterion is that orders are within the last N days (this corresponds to parameter days), whose expression is OrderDate>=after(date(now()),days*-1). The second one is that orders belong to several important departments (this corresponds to parameter depts), whose expression is depts.array().pos(SellerId.Dept). The operator || denotes the logical OR operation.

after function calculates the relative time duration. array function splits a string into a set using delimiters. pos function locates a member in a set. SellerId.Dept means Dept field in the record corresponding to SellerID field.

Both days and depts are parameters transferred from the reporting tool. Suppose their values are respectively 30 and “Marketing,Finance”, then A4’s result is as follows:

A5: Get the fields of interest from A4. Here is the final result:

Combining result sets with union

Result sets ds1 and ds2 have the same structure, but they come from different data sources – MySQL and a text file respectively. Now concatenate them and display the result in a cross table. Below is the original data:
ds1
ds2
id    name        time
1    name1        2010-07-22 11:01:02.903
2    name2        2010-07-22 11:01:02.903
3    name3        2010-07-22 11:01:02.903
id    name        time
1    t2_name1    2010-07-22 11:01:02.903
2    t2_name2    2010-07-22 11:01:02.903
3    t2_name3    2010-07-22 11:01:02.903
1    t3_name1    2010-07-22 11:01:02.920
2    t3_name2    2010-07-22 11:01:02.920
3    t3_name3    2010-07-22 11:01:02.920


esProc script:

A3: Concatenate the two data sets. The reporting tool’s work is just to create a simple cross table based on the resulting one data set.

Handling different data sources for main report and subreport

For a reporting tool that can support only one data source, if the reporting requires different data sources for the main report and the subreport, it needs to pass in the database URL explicitly or use Java classes to combine the different data sources into one. The former approach is vulnerable to security problems and the latter one produces complicated code. esProc, however, is able to cope easily. Here is an example.

Build a report with a subreport to display order information of sellers whose salaries are within a certain range. The main report’s data source is an emp table (in MySQL database) and the subreport’s data comes from a sales table (MSSQL database).

esProc scripts:

empEsProc.dfxfor the main report

A1: Query the emp table in MySQL database by the salary range.

salesEsProc.dfxfor the subreport

A1: Rrtrieve orders records from the sales table in MSSQL database according to employee IDs. Suppose eid=1, the result would be:

You can see that the two data sources have been joined into one source with esProc. The reporting tool just needs to call the corresponding esProc script for the main report and the subreport.

Some reporting systems do support multiple data sources. But it is hard to handle reports with different and changeable data sources. In that case, using esProc to generate a single data source can make the handling work easier.

For the same reason, another reporting problem can be solved through esProc’s single-data-source solution. That is the “multiple subreports, multiple data sources” problem, which means there are many subreports (or table controls) within one report and each has its own data source. 

Performing dynamic join between main report and its subreports

A main report may use many subreports whose data sources come from multiple databases. The reporting needs to display the result of dynamic join between these data sources and the one the main report uses. esProc implements the task in a simple way. For example:

The main table org is stored in the data source Master. Every record of the org table corresponds a subtable that has a separate data source. For example when org.org_id=“ORG_S”, the record’s subtable is the User able in the data source S_odaURL; when org.org_id=“ORG_T”, the record’s subtable is the User able in the data source T_odaURL. There are more subtables with names all being User. You need to join all subtables dynamically with the main table and display the result data set in a report. Below is the logical relationship between these tables:

esProc script:

A1: Execute the SQL statement to retrieve data from the org table in Master data source. arg1 is a parameter passed from the reporting tool. When arg1=”ORG”, the result would be:

A2: Loop through A1’s records to associate one subtable each time, concatenating each result of join into B2. esProc uses the natural indentation to represent the loop statement’s working range. Here the loop body is B2-B7 where A2 is used to reference a loop variable and #A2 is used to reference the loop number.

B2: Get the data source name for each record according to its org_id field. The result during the first loop is “S_odaURL”.

B3: This is the explicit connection to corresponding data source.

B4: Filter on data in the User table.

B5: Append three columns, which derive from the main table, to B4’s subtable. The result during the first loop is:

B6: Concatenate B5’s result into B1. The operator “|” is equivalent to union function. When the loop is over, B1 will have collected all data the reporting needs, as shown below:

B7: Close data source connection explicitly.

A8: Return B1 to the reporting tool explicitly. The default execution is to return the result of the last cell.

Displaying data by different time units specified by parameter

Here is a reporting task that requires using a line graph to show the change of sales over a recent period. unitType is a report parameter, representing time units. If unitType="hour", show sales amount every five minutes in the past one hour. If unitType="day", show sales amount per hour during the past day. And show sales amount per day over the past one week if unitType="week". The data originates from the orders table in which Amount field contains order amount. t is used to reference the order time.

esProc script:

A1: An empty result set used to store the time sequences generated from B2-B4.

A2-B4: Generate different time sequences according to the report parameter unitType. B2 generates 12 time points, with an interval of 5 minutes between each other. B3 generates time points in one day, and B4 generates time points during one week.

A5: Loop through A1 to calculate the sales amount of each time interval. “~” represents the current member and “~[-1] " represents the previous one. In the case of unitType="day", a one-field result set containing 12 records will be generated. Then you can plot the chart to show the result set.