Shulou(Shulou.com)06/03 Report--

Migration of multi-table operations from SQL to SPL basic query syntax

In the previous article, we learned how to migrate data calculations from SQL queries to aggregators, or, more accurately, to the SPL centralized computing language used by collectors in the case of a single table. This migration process not only has the same concept, but also has different ideas.

Next, let's take a look at how aggregators and the SPL language take a greater advantage in the case of multiple tables.

JOIN connects two records

In the previous example, we got the sales of each employee, which is a little more complicated if we further want to know the minimum discount given by each employee. Because the discount information is in another order schedule. An impatient friend may come up and ask for two JOIN tables, and then aggregate two measure fields-- this is wrong! The right thing to do is for each measure field to aggregate the results by grouping before JOIN. This is because JOIN first may result in duplicate records in the results.

Follow this idea to write SQL and compare it with the SPL code in the aggregator:

SQL

Select t1.employeeId, salesAmount, lowestDiscount from (

Select employeeId, sum (money) salesAmount from order

Where orderDate > = '2012-01-01' and orderDate5000) T1

Left join (

Select employeeId, min (discount) lowestDiscount from order

Join orderDetail on order.orderId=orderDetail.orderId

Where orderDate > = '2012-01-01' and orderDate=date ("2012-01-01") & & orderDate5000) 6=A1.query ("select * from orderDetail") 7=join (A3 Vorderrep orderId) 8=A7.group) 8=A7.group (A7.order.employeeIdemployeeIdemployeeIdemployeeIdemployeeIdscape employee. Min (orderDetail.discount): lowestDiscount) 9=join (A56=A1.query r1 employee eIdboot A8 employees R2 employeeId) 10=A9.new (r1.employeeIdDiscountDiscountDiscountDlowestDiscountDiscountDiscountDiscountDiscountDiscountDiscountDiscountDlowestDiscountDiscountDlowestDiscountDlowestDiscountDiscountDiscountDlowestDiscountDiscountDlowestDiscountDiscountDlowestDiscountDiscountDlowestDiscountDiscountDlowestDiscountDlowestDiscountDlowestDiscountDiscountDiscountDiscountDlowestDiscountDiscountDlowestDiscountDiscountDiscountDlowestDiscountDiscountDlowestDiscountDlowestDiscountDiscountDiscountDiscountDiscountDiscoun

A5 is the aggregate we have done before, eligible sales by employee group.

A6 find out all the data in the order schedule

SPL in A7 uses the new function join for multi-table joins corresponding to SQL. The join function joins the two tables. Here, its parameters are as follows: add an alias order,A6 to the A3 ordered table and an alias orderDetail to the ordered table, and then use the respective orderId of the two tables as the associated field. The result of observing A7 is as follows:

As you can see, the order table formed after join has two fields order and orderDetail, and the values of the two fields point directly to the records of the two original order tables, that is to say, the values of the fields can be complex data types (including sequence table and sequence type), or you can nest data with multi-tier structure, which is not allowed in SQL syntax, and is also one of the features and advantages of SPL.

The following figure vividly illustrates this structure (rectangle represents ordinal table, oval represents record, triangle represents associated field):

A8 directly uses the group function in SPL to group and aggregate the ordered table of this complex structure to get the minimum discount that each employee has ever given.

It is also important to note that the A3 sequence table is used once by the following two different actions (A4 and A7) to achieve the effect of intermediate result reuse. This approach is often more effective for more complex calculations, and can play a similar role of "data modularization".

In A9, the two ordered tables A5 (sales over 5000) and A8 (minimum discount) are connected through the join function of SPL.

Finally, A10 uses the new function to generate a new ordinal structure, and the data comes from different levels of A9 complex structure records.

The final result of A10 is as follows:

In retrospect, for the last more complex SQL, if you are a SQL expert, you may see that the where in the second subquery is not necessary. It is retained because it is possible to narrow the scope of processing data, thereby improving some performance. This is another feature of SQL, on the one hand, it needs to express the whole query without breathing, on the other hand, it needs to consider the performance optimization factors at the same time, even if the optimization scheme is taken into account, it may not be easily and naturally described.

In the above example, it is not difficult to realize the importance of the aggregator and SPL to the step-by-step process, the results of each step can be observed at any time, and the results of the previous steps can be reused, while the execution steps can be freely customized by the programmer. The most direct advantage of these features is that it reduces the difficulty of learning and coding, and what is more essential is in line with human natural thinking, which lays a foundation for describing complex computing.

UNION et al merge two sets

There is also a class of operations for multiple sets (tables) in SQL, which are often referred to as union, union, intersection, difference (UNION, UNION ALL, INTERSECTION, MINUS), corresponding to which are expressed as &, |, ^,\ in SPL.

Set operations often need to determine whether a record is duplicated, and SQL is a little different from SPL in this detail. In SPL, because the values of the order table, record, and field are all regarded as an object, we can compare whether the element is the same object during the union operation, while the record of SQL is abstract, not an entity object, so we can only judge whether it is duplicated by comparing the field values of the two records one by one.

Let's take a look at a practical example:

Aggregator AB

1=connect ("hsqlDB")

2=A1.query ("select * from order")

3=A2.select (orderId > 10251 & & orderId10254 & & orderIdA1.close)

The results of the A3 sequence table:

The results of the B3 sequence table:

A3&B3, remove the repetition and merge together, and get the result of the A4 ordered table after the union operation:

A3 | B3, reserved and merged repeatedly. After the set operation, the result of the B4 ordered table is obtained:

A3 ^ B3, take the same records in A3 and B3, and get the result of A5 ordinal table after intersection operation:

The records existing in B3 are removed from A3\ B3, and the result of A5 ordered table is obtained after subtraction operation.

Other commonly used grammars

Finally, let's take a look at the two commonly used SQL function syntax and take a look at the implementation of SPL.

CASE WHEN...

Case when in SQL... Then... Else... End, using the if function in SPL, the syntax is if (condition, true value, false value). The following example is to put employeeId

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.