How to optimize SQL computing by the set calculator (3) sequence computation _ MySQL

Early cross-row references SQL statements do not directly support cross-row references. it is extremely cumbersome to generate sequence numbers and then JOIN them. The SQL statement after the window function is introduced can easily reference other rows of data, but the statement is still not concise. the code will be very long when there are multiple cross-row reference items. As mentioned above, window functions are referenced in other cross-row

Early SQL statements do not directly support cross-row references. it is extremely cumbersome to generate sequence numbers and then JOIN them. The SQL statement after the window function is introduced can easily reference other rows of data, but the statement is still not concise. the code will be very long when there are multiple cross-row reference items. As mentioned above, window functions are implemented on the basis of other calculation result sets, and the re-reference of window function calculation values must be written as subqueries, which is still cumbersome.

MySQL does not support window functions, but supports using variables in SQL. you can reference the preceding rows, but cannot reference the following rows.

The set calculator provides a convenient and natural cross-row reference syntax.

The monthly sales table structure of each product is: product, month, and sales. now we need to find a record showing that the sales volume is 10% more than the previous month.

	A
1	= Db. query ("select * from Sales Table order by product, month ")
2	= A1.select (if (product = product [-1], sales/sales [-1])> 1.1)

After sorting, you can simply use [-1] to reference the data of the previous month, and directly filter the data based on the calculated cross-row value. Use SQL window functions to use subqueries, and define two temporary variables for MySQL.

Then, calculate the average sales volume of each month before and after each month in the table:

	A
1	= Db. query ("select * from Sales Table order by product, month ")
2	= A1.derive (if (product = product [-1] & Product = product [1], sales volume {-}. avg (): mobile average)

Calculating the moving average involves backward reference and set reference. [1] can be used to reference the next row of data. {-} can be used to reference the field value set from the previous row to the next row. Similarly, SQL window functions also require subqueries to calculate the corresponding rows before moving the average. MySQL variables cannot be referenced backward, which makes it difficult to calculate them directly.

Let's look at another example. the simplified event table structure is: sequence number, moment ,...; The time must be incremental with the serial number, but there may be errors. you need to find records that are not incremental with the serial number at the time.

	A
1	= Db. query ("select * from Event table order by No ")
2	= A1.select (time! = Max (moment {: 0}) | moment! = Min (moment {0 :}))	Comparison with all records before and after

You can also retrieve the set from the beginning or end. SQL window functions also support similar expressions, but the two comparisons must be sorted in two different directions. of course, subqueries must be used.

Ordered grouping

SQL only provides equivalent groups irrelevant to the order, but sometimes the key value of the group cannot be found in each record, but is related to the record order. in this case, SQL also requires the use of window functions (or other more troublesome means) to create a sequence number.

The set calculator provides an order-related grouping mechanism to facilitate computing related to continuous intervals.

The structure of the income and expenditure table is month, income, and expenditure. records of the months with consecutive losses of March or more are found.

	A
1	= Db. query ("select * from income and expenditure table order by month ")
2	= A1.group @ o (revenue> expenditure). select (~. Revenue

Group @ o indicates that only adjacent records are compared during grouping. if the adjacent values change, a new group is formed. In this way, the income and expenditure records can be divided into profit, loss, profit ,... Such a group, and then take out the loss and members not less than 3 groups and then combine.

In this table, we hope that the computing revenue will grow continuously for a few months at most. You can design such a grouping mechanism: the revenue growth period and the previous month are divided into a group. when the income decreases, the group is divided into a new group. Finally, the maximum number of group members is counted.

	A
1	= Db. query ("select * from income and expenditure table order by month ")
2	= A1.group @ I (revenue <收入[-1]).max(~.len())< p>

Group @ I will create a new group when the conditions change, that is, when the income decreases.

With the support of window functions, SQL can also implement the ideas of this example and the above example, but the writing is very difficult.

Interval merge is also a common ordered grouping operation. The event occurrence interval table T has fields: S (start time) and E (end time ); now we need to remove the overlapping parts of these ranges and then calculate the actual total duration of the event.

	A
1	$ Select S, E from T order by S
2	= A1.select (E> max (E {:-1 }))	Remove included entries
3	= A2.run (max (S, E [-1]): S)	Remove overlapping time periods
4	= A2.sum (interval @ s (max (S, E [-1]), E ))	Total computing duration
5	= A2.run (if (S	Merged overlapping time periods

Here we provide a variety of target processing methods, making full use of the features of cross-row operations and ordered grouping. SQL can no longer achieve this operation simply by using window functions, which requires recursive queries that are hard to understand.

Location Access

For ordered sets, we sometimes need to access members directly with serial numbers. SQL uses the unordered set concept in mathematics and generates sequence numbers and then uses conditional filtering to access members at the specified position, which causes a lot of trouble for many operations.

The set calculator uses an ordered set mechanism to allow users to access members directly with serial numbers. This type of operation is much more convenient.

For example, in economic statistics, the median is often used to find out a large number of prices:

	A
1	= Db. query @ I ("select Price from T order by Price ")
2	= A1 ([(A1.len () + 1) \ 2, A1.len () \ 2 + 1]). avg ()

Location can also be used for grouping. The event table has two types of structures: sequence number, time, and action. The action has two types: start and end. now we need to count the total duration of the event, that is, the sum of the time between the start and end of each pair.

	A
1	= Db. query @ I ("select moment from Event table order by Moment ")
2	= A1.group (#-1) \ 2). sum (interval @ s (~ (1 ),~ (2 ))

# Indicates the record sequence number. group (#-1) \ 2 divides each of the two data into one group, and then calculates the total length of each group.

Adjacent cross-row references can also be made based on the location. The stock price table structure is as follows: trading day and closing price. The list shows the trading day and the increase of the stock price over 100 yuan.

	A
1	= Db. query ("select * from stock price table order by trading day ")
2	= A1.pselect @ a (closing price> 100). select (~> 1)
3	= A2.new (A1 (~). Transaction Day: Transaction Day, A1 (~). Closing price-A1 (~ -1). Closing price: increase)

The pselect function returns the member positions that meet the conditions. using these positions, you can easily calculate the increment, instead of filtering all the increment values by using the window function.

--- Restore content end ---

The preceding figure shows how to optimize SQL computing (3) SQL computing _ MySQL. For more information, see PHP Chinese website (www.php1.cn )!