Using SQL in esProc (II)

5 Comparison between common SQL statements and esProc syntax

1)  Select * from

Query results are as follows:

2) Select … from 

Get designated fields from the table. Both A2 and A3 have the same query results as follows:

3) As

Compute FULLNAME according to NAME and SURNAME, and meanwhile, compute AGE according to BIRTHDAY. Basically, both A2 and A3 have the same query results as follows:

Note that AGE is computed in A3 by subtracting years and exact computations will be more complicated since SQL hasn't functions to directly compute age.

4) Where

Query employees who are younger than 30 years old, then compute in esProc using existed results. Query results of A3 are as follows:

Query the same results in A4 with SQL but the syntax is much more complicated. As the process of computing age is inexact, errors occur in the results.

5) Count, sum, avg, max and min 

Query the total number of employees who are younger than 30 years old, compute in esProc using existed results. Query result of A3 is as follows:

This time, A4 uses a more exact method to compute AGE and gets a query result that is consistent with that of A3. But it cannot use the existed results and statements are more complicated.

The usage of SQL functions, such as sum, avg, max and min, is similar to that of count.

6) Distinct

Query which departments does the employee information come from.Both A2 and A3 have the same results. Query results are as follows: 

7)  Order by 

Query employees who are younger than 30 years old, sort them by age in descending order; meanwhile, sort employees of the same age by full name in ascending order. Both A3 and A4 have the same query results as follows:

Since it is complicated to compute age with SQL and existed results cannot be used, this time A4 simplifies statements with nested query. However, the process is still complex.

8) And、or、not and<> 

Query employees who are younger than 30 years old and whose initials of full names are S. Results are as follows:

It can be seen that and is represented in esProc by the operator && and two equal signs == are used to judge whether things are equal or not. These are in line with the customs of many program languages. Similarly, in esProc, or is represented by the operator "||", not by"!", and<>by"!=".

9) Like 

Query full names of employees whose names are ended by a. Query results are as follows:

In using like function, different databases use different wildcard characters. In this example, for instance, percent "%" is used to represent zero or multiple arbitrary characters; while in some other databases, asterisk "*" is used to represent the same things. But with esProc, syntax of any database is the same.

10) Group 

If grouping by departments employees work for, group function can be used in esProcto group records as follows:

It can be seen that the result of grouping with esProc is that records are divided into multiple groups. These groups can be used to perform further computations as required in esProc.

A3 directly computes grouping and summarizing with esProc function while A4 does the job with SQL. They get the same results. SQL doesn’t have the real "group" concept, so it can only perform aggregate computations in query by groups. Results are as follows: 

Using SQL in esProc (I)

In esProc, we can use not only the SQL to retrieve data from databases, but also the preliminary database query results to perform further analyses and operations to solve some complicated problems which are difficult to deal with only with SQL. 

1. Database connection and disconnection
When using SQL to access databases, it should first connect to the designated database. Usually, there are two ways to connect to the database: direct connection in the data source manager or calling functions to connect in cellsets. 

In practice, the database accessed in the data source manager can be called by directly using data source name and are valid while the connection is on; for database accessed through functions, the connection objects it created will be stored as cell values,and it can be called by using cell names and are valid before the called connection objects close.

Similarly, there are two ways for database disconnection as well. Except for calling db.close() function shown in the above example,another way is to close the selected data source in data source manager:

2. Use of simple SQL

Using db.query() function can execute SQL orders in designated database.SQL statements may contain various query clauses and database functions.

A2 executes query on states whose abbreviations begin with N, and sort them by population in descending order. Results are as follows:

SQL statements can also use other data in cellsets as parameters:

A4 executes query on states whose abbreviations fall into designated sequences and sort them by area in ascending order. Results are as follows:

3. SQL that returns no results

If SQL that returns no results is used to access a database, like the use of SQL statements:create, update, delete and so on, then db.execute() function is needed in execution. Meanwhile, since it is not necessary to assign value to cells, expressions begin with ">" instead of "=". For example:

After statements in A2 modifies records in database table STATES, query results of A3 are as follows：

SQL that returns no results can use parameters, too: 

Statements in A4 restore modified records in database STATES to their original values, and query results of A5 are as follows:

4. Use of query results of SQL

Query results of SQL can be used in esProc to perform operations, like filtering, sorting and combination, etc.in order to increase query efficiency or deal with some complicated problems.

In the following examples, executions are performed by connecting to data source demo in data source manager and based on query results of cell A1:

For example, filtering data and searching data of states of designated abbreviations:

Also, aggregation computations can be performed on data. For example, count up the number of states whose abbreviations begin with C:

More significantly, we can group data in databases according to certain requirements, for example, group according to initials of abbreviations:

A2 executes grouping according to the initial of each state's abbreviation. Double-click and see details of each group’s data.

It is thus clear that, different from the SQL grouping method that doesn't provide real grouping and summarizing function, grouping with esProc is the real one, on which further computations can be performed. For example, select groups that contain three or more states, compute the total number and population of the states in these groups:

The final results of A4 are:

Differences and Similarities between esProc TSeq and SQL Data Table

esProc TSeq and SQL data table are all the structured 2-dimensional data objects. The records, indexes, and primary keys can all be applied to the structured data computing. Although both of them can be applied to the structured data computing, their application scenarios differ distinctly. TSeq is fit for solving the complex computing problem involving not-so-big data volume, while data table is fit for the common computation involving possible big data volume.

Their differences are determined by the underlying mechanism.

TSeq is ordered. Each record and data in each column has a specific sequence number. TSeq supports the explicit set, and set operations can be performed directly between multiple TSeq; TSeq is also the generic set. The basic element can be numeric value, reference, or another set.

SQL data table does not have the above features. But SQL data table is transparent to the memory and external storage. A consistent syntax is used to access to the memory, external storage, or blended data.

In the below sections, we will further discuss their differences and similarities.

Common basic functions

Both esProc TSeq and SQL data table are the structured 2-dimensional data objects. In other words, they are row-style 2-dimensional table based on records with one row for one record. The 2-dimensional table together with multiple column names forms a complete data structure. Because the structures are largely similar, their basic usages are almost the same.

Example 1:Query the data in the object. Find the orders in which the number of Freight is greater than 100 and placed before 2013.

SQL: SELECT * FROM Orders WHERE Freight > 100 AND OrderDate<'2013-01-01'

TSeq: =Orders.select(Freight > 100 &&OrderDate< date("'2013-01-01"))

Note:In this example, the data object name is Orders, and another data object of Customers will be used in the subsequent sections.

Example 2: Sort. Sort the orders by EmployeeID in ascending order, then sort by Freight in reverse order.

SQL: SELECT * FROM Orders ORDER BY EmployeeID ,Freight DESC

TSeq: =Orders.sort(EmployeeID,Freight:-1)

Example 3:Grouping and summarizing. Group by employee, summarize the freight charge, and count the orders.

SQL: SELECT EmployeeID, COUNT(OrderID), SUM(Freight) FROM Orders GROUP BY EmployeeID

TSeq: = Orders.groups(EmployeeID;sum(Freight),count(OrderID))

Example 4:Join. Join the two data objects Orders and Customers to form a new data object. Use the left join and the join field is CustomerID.

SQL: Select * from Orders left join Customers on Orders. CustomerID =Customers. CustomerID

TSeq: =join@1(Orders:, CustomerID;  Customers:, CustomerID)

Besides the above several basic usages, esProc TSeq and SQL data table are very alike in the algorithms like distinct, count, sum up, average, maximum, and minimum, just a few example here.

Difference of being in order

Thanks to the ordered record set of TSeq, the order-related computing can be solved easily. SQL data table lacks the sequence number and sequence-number-related access method, making it a bit inconvenient to handle the order-related computing.

Example 1: As for the sales data object, compute the sales increment of this month compared with that of the previous month.

SQL:

select salesAmount, salesMonth,

     (case when

prev_price ! =0 then ((salesAmount)/prev_price)-1

else 0

end) compValue

from (select salesMonth, salesAmount,

lag(salesAmount,1,0) over(order by salesMonth) prev_price

from sales) t

TSeq:

sales.derive(salesAmount / salesAmount [-1]-1: compValue)

Comparison:

The monthly sales have nothing to do with orders. It is represented as salesAmount in the TSeq and the data table. The one related to the order is the Sales amount of previous month, which is equivalent to the salesAmount of the previous record relative to the current record. TSeq is ordered, sosalesAmount[-1] can be used to represent the sales of previous month. By comparison, SQL data table is not ordered. Since SQL2003 standard was introduced, the order-related features are added to the window functions. It is so tedious to use the unavoidable  complex method lag(salesAmount,1,0) over(order by salesMonth) to compute the sales of the previous month.

TSeq can also be used to represent the relative interval, for example, the total five months comprising the current month, the two months before, and two months after the current month. With TSeq, it can be represented like this: salesAmount{-2,2}. In SQL, the window function can also be used to represent the summarizing, but much more troublesome.

Example 2: For the sales data table, find the first 10 records with the highest sales for each product.

SQL:

select salesMan, product ,amount

from ( select salesMan, product ,amount, rank() over (partition by product order by amount desc ) ranking from sales)

where ranking <=10

TSeq:

= sales.group(product).(~.top(-amount;10))

Comparison:

In this example, the most intuitive thinking pattern to implement is to group data by product, and then perform the order-related computations in the groups. The simplest method is to retrieve the first ten records with the highest amount in the group. The intuitive method is to sort the data in the group in reverse order by amount, and then get the records whose sequence numbers in the group is from 1 to 10.

TSeq supports the order-related computation quite well. For TSeq, not only the function top can be used to implement the first algorithm, as demonstrated in the example, but also the sort function and the sequence number of record can be used to implement the second algorithm, as shown below: =sales.group(product).(~.sort(Amount:-1)).(~([to(10)])).

The record in the SQL data table is not ordered. We must compute out a sequence number or field to act as the sequence number, such as ranking. The algorithm adopted in the above example is to compute the data rankings in the group, and then get the records among the top 10. Obviously, SQL syntax is a bit zigzag and difficult to understand. SQL users will have to use the window function over (partition by…… order by……) and the sub-query which is hard to track and debug.

By comparison, TSeq is more straightforward and simpler for easy track and debug. For another example, programmers can firstly write =sales.group(product) code for test. This code represents that the data will be grouped. This code snippet can run and display the result independently. If the grouping results meet your expectation, then programmers can proceed to add the second paragraph of codes: sort the data in the group in reverse order, that is, .(~.sort(Amount:-1)). In which, the "." indicates the former computing result will be taken as a whole for further processing, "~" indicates the data in the current group, and “-1” indicates the reverse order. The current code line is =sales.group(product).(~.sort(Amount:-1)), which can run and display the result independently. If the grouping results meet your expectation, then programmers can proceed to add the third paragraph of codes: retrieve the records whose sequence numbers in the group is 1-10, i.e. .(~([to(10)])).

As can be seen, the computational procedure of TSeq can be pushed forward step by step. In solving the complex computing problems, the computation can be simplified to avoid errors. In facts, the above three continuous paragraphs of code can be written into three rows of codes step by step, so as to decompose the computing goal more clearly:

        

        

One thing worthy of noticing is that SQL cannot perform data sorting before order-related computing,even with the temporary table.The two steps must be combined into one step. The reason for this is that the data table does not support the explicit set and generic set. It is rather weak regarding its syntax representation.

In addition, although SQL uses the window function up to the ANSI standard, the database vendor does not implement it fully to the standard. The writing styles vary for various databases and some databases does not provide the window function at all. The function syntax of TSeq is independent of data source. For whatever data sources such as database, TXT file, Excel file, and binary file, programmers are not required to modify the code when using TSeq for computing.

Difference between explicit sets

Although SQL has the concept of set, the explicit set is not provided, and cannot be taken as the independent variable. The set operations can only be implemented with temporary table. TSeq is the true explicit set for implementing the set operation.

Example: Compute over the Contract data object. Suppose the contract with over 40 order quantity is the big contract, and the unit price over 2000 is the important contract. Please find the contract settled in this year which are both the big contract and the important contract, and all other contracts except these contracts.

SQL:

         select SellDate,Quantity,Amount,Client from Contract where to_char(SellDate,'yyyy')='2014' and quantity>=40 and AMOUNT>=2000

         select SellDate,Quantity,Amount,Client from Contract where not(to_char(SellDate,'yyyy')='2014' and quantity>=40 and AMOUNT>=2000)

TSeq:

         =thisYear= Contract.select(year(SellDate)=2014)

         =big= Contract.select(Quantity>40)

         =importance = Contract.select(AMOUNT>2000)

         =answer=thieYear^big^ importance

         =others= Contract\answer

Comparison and Analysis:

The typical natural thinking pattern is to find the contracts that are big contract and also the important contract of this year. The intersection operation is most intuitive. If the big contract is defined as big, the important contract is defined as importance, and the contract in the current year is defined as thisYear, then we can write the pseudocode easily: big∩importance∩thisYear. TSeq is the explicit set capable of representing the equivalent expression very intuitively, i.e. thieYear^big^importance. SQL does not allow for representing it with the variables for set. So, we have to find another way, for example, converting it to the Boolean condition, as demonstrated in the example: to_char(SellDate,'yyyy')='2014' and quantity>=40 and AMOUNT>=2000.

It is easy to solve the first problem because the respective development difficulty with these two tools are not great. With the further research into the problems, their differences become evident.

Problem 2: “Other contacts except for those big and important contracts” - it is also a typical thinking pattern - the computing goal can be achieved with difference set in one step. TSeq expression:Contract\answer, very intuitive. Using Boolean conditions, SQL can also be used to compute out the answer. But the expression writing style and business description vary greatly, as shown below: not(to_char(SellDate,'yyyy')='2014' and quantity>=40 and AMOUNT>=2000).

         With SQL, we can solve it using the set operation, and the algorithm is also quite intuitive. However, the code may appear very lengthy since the data table cannot be represented with the set variables.

(select SellDate,Quantity,Amount,Client from Contract)

minus

(Select select SellDate,Quantity,Amount,Client from Contract from(

    (select SellDate,Quantity,Amount,Client from Contract where to_char(SellDate,'yyyy')='2012')

    Intersect

    (select SellDate,Quantity,Amount,Client from Contract where quantity>=40)

    Intersect

    (select SellDate,Quantity,Amount,Client from Contract where AMOUNT>=2000))

Because the code is lengthy, many people would rather use Boolean condition to implement the set operations indirectly.

Undoubtedly, in most cases, it is more convenient for data table to use operation set than Boolean condition, for example, the set operations between multiple physical tables, or the set operations between multi-level sub-queries. In this case, the cost would be high if converting the set operation to the Boolean conditions, and programmers have to adopt the lengthy set operation.

Difference between generic sets

TSeq is the generic set to store both the physic data and the reference to the associated data. With this advantage, TSeq can implement the associated computing through the intuitive object reference. By comparison, the data table can only be used to store the physical data. The equivalent computation can only be completed using the associated complex statements.

Example:Please compute which department managers received the President Award, and of these managers, which subordinates are the annual outstanding employees. This involves two data object:department and employee, in which, the deptName field of department and the empDept field of employee is in the one-to-one relation. Still, the manager field in department and the empName field in employee is also the one-to-one relation. In addition, the code for president award is PA; and the code for annual outstanding employee is EOY.

Data table:

    SELECT A.*

    FROM employee A,department B,employee C 

    WHERE A.empDept=B.deptName AND B.manager=C.empName AND A.empHonor=‘EOY’ AND C.empHornor=‘PA’

TSeq:

    employee.select(empHonor: "EOY",empDept.manager.empHornor:"PA")

Comparison and Analysis:

The SQL solution can definitely work out.But the relevant associating statement is fairly complex that the average programmer could feel hard to comprehend. By comparison, TSeq solution is rather intuitive, empHonor:"EOY"is one of the conditions: “Who are Year Outstanding Employees”, while empDept.manager.empHornor indicates the “the department.the department manager.awards received by the department manager”. Obviously, if this value is PA, then the condition 2 in the problem is met: "Department managers who received the president award". This is the object reference by definition.

The object reference enables programmers to use "." operator to make reference to the related data. By this way, the business association relation can be translated into the computer language intuitively, so that multilevel relations can be represented conveniently, and the associating computation can be performed intuitively.

Differences regarding Memory and External Storage Transparencies

SQL

Because SQL data table does not support the generic and set data,no data will be lost when writing the data from memory into the external storage. Thus, it is transparent to the computation over memory and external storage. On accessing the data table for the first time, data are usually from the external storage; then, for the subsequent accesses to the same data table, data can be from the memory cache; for the data table with relatively large volume of data, part of the data can be from the external storage, and part from the memory. No matter the data is from memory or external storage, and data volume is big or small, the syntax for retrieving data table is always the same, and programmers need not to write different SQL statements for it.

TSeq

TSeq supports the generic type (reference in particular) and set data. Data could lost when writing the data from memory to the external storage, and can not always ensure to be loaded properly, which making the computation intransparent to the memory or external storage. TSeq is the pure memory data object, and is only capable of handling limited data volume; If the data volume is relatively big, then cursor (another data object of esProc) should be used for the computation in external storage, with different syntaxes for the cursor and the TSeq; esProc programmers also must convert the data between cursor and TSeq if they want to improve performance or handle the computation involving complex business logics.

Comparisons

TSeq is not transparent to the memory and external storage. esProc programmers need to write different codes to meet the requirement of computing in memory, external storage, or mixed computing. In addition, they need to modify the codes to meet the needs of data volume increase. Therefore, the workload for the initial design and post-maintenance is relatively great. SQL data table is transparent to the memory and external storage. Programmers only need to write one set of code to fit various scale of data. The designing and maintenance workloads are relatively small.

Through the above comparison, we can conclude: Characterized with the ordered data, explicit set, and generic set, TSeq can easily solve the order-related complex problem and reduce the complexity of set operations, with support for the intuitive object reference to handle the complex multi-table association. SQL data table is transparent to the memory and external storage, and SQL code is commoner.

The Disadvantages of SQL Computation (II)

In the above computation, we have used the keyword top. In fact there is not such a thing (it can be combined out by other computation computations) in the theory of relational algebra, and this is not the standard writing style of SQL.

Let us see how difficult it is to look for the top 10 places when there is no top.

Rough working thought: Seek out the number of members whose sales amount are higher than itself to rank the sales person, and then get the members whose places do not exceed 10, and the SQL is written as follows:

select sales

from ( select A.sales sales, A.product product,

(select count(*)+1 from sales_amount

where A.product=product AND A.amount<=amount) ranking

        from sales_amount A )

where product='AC' AND ranking<=10

or

select sales

from ( select A.sales sales, A.product product, count(*)+1 ranking

from sales_amount A, sales_amount B

where A.sales=B.sales and A.product=B.product AND A.amount<=B.amount

group by A.sales,A.product )

              where product='AC' AND ranking<=10

Professional technical personnel may not necessarily write such SQL statement well! And only the first ten places are computed.

To say the least, even if there is top, it only makes it easy to get the preceding part lightly. If we change the problem into getting the 6^th place to the 10^th place, or seeking the salesman whose sales amount is 10% more than that of the next one, the difficulty is still there.

The reason causing this phenomenon lies in the third important disadvantage of SQL: Lack the support of ordered set. SQL inherits the unordered set in mathematics, which directly causes the fact that the computations relating to sequence are rather difficult. And it can be imagined how common the computations relating to sequence (such as over the preceding month, over the same period last year, the first 20%, and rankings) will be.

The newly added window functions in SQL2003 standard provides some computation capabilities relating to sequence, which makes it possible to solve some problems in a relatively simple method and alleviate the problem of SQL to a certain extent. But the use of window functions is often accompanied by sub-query, and it cannot enable user to directly use the sequence number to access set member, so there are still many ordered computations that are difficult to solve. 

Now we want to pay attention to the gender proportion of the “good” salespersons that are computed out, that is, how many males and females there are respectively. Generally, the gender information is recorded in the employee table but not in the performance table, and it is simplified as follows:

employee	Employees table
name	Names of employees, suppose there is no repeated name.
gender	Genders of employees.

We have already computed out the name list of “good” salespersons, and the relatively natural idea is to seek out their genders from the employee table using name list, and count the number. But in SQL, it is necessary to use join operation to get information across tables . In this way, following the initial result, SQL will be written as:

select employee.gender,count(*)

from employee,

( ( select top 10 sales from sales_amount where product='AC' order by amount desc )

intersect

( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A

where A.sales=employee.name

group by employee.gender

With only an associated table more, it is made so over-elaborate and in reality there are rather more cross-table storages and they are often multi-layered. For example, for salespersons, there are departments where there are managers, and now we want to know by which managers these “good” salespersons are managed. Then there are three table joins, and it is indeed no easy job to write clear where and group in this computation.

This is just the fourth important disadvantage of SQL as we want to say: Lack of object reference, in relational algebra, the relations between objects completely depends on foreign key. This not only makes the efficiency very low in looking for relation, but also makes it impossible to directly treat the record pointed by foreign key as the attribute of primary record . Try thinking, can the above statement be written as this:

select sales.gender,count(*)

from (…)          // …is the SQL computing the “good” salespersons above

group by sales.gender

Evidently, this statement is not only clearer, and at the same time, the computation will also be more efficient (without join computation).

We have analyzed, through a simple example, the four important difficulties of SQL. We believe this is just the main reason why SQL fails to reach the original intention of its invention. The process of solving business problem based on a kind of computation system is in fact the process of translating business problems into formalized computation syntax (similar to the case in which a pupil solves application problem, translates the problem into formalized four arithmetic operations). Before overcoming these difficulties, SQL model system rather does not comply with people’s natural thinking habit, causing great barriers in translating problems, making it very difficult for SQL to be applied, on a large scale, in data computation for business problems.

For still another example which is easily understood by programmer, use SQL as data computation, which is similar to the case in which assembly language is used to complete four arithmetic operations. We very easily write out the calculation expression such as 3+5*7, but to use assembly language (take X86 as the example), it needs to be written as

mov ax,3

mov bx,5

mul bx,7

add ax,bx

In either writing or reading, such code is far inferior to 3+5*7 (it will be more troublesome if we come across decimal). Though it cannot be regarded as a big problem to a skilled programmer, to most people, however, this kind of writing is too hard to understand. In this sense, FORTRAN is really a great invention.