How to optimize SQL computation in the set calculator (4) string and datetime MySQL

For string splitting and merge operations that do not involve a set, the SQL statements of each database have rich support, and even include complex analysis functions such as regular expressions. Of course, these function collectors are also available. as a dynamic language, the assembler also provides function strings that use strings for expression computation.

For string splitting and merge operations that do not involve a set, the SQL statements of each database have rich support, and even include complex analysis functions such as regular expressions. Of course, these functions are also available in analyticdb. as a dynamic language, analyticdb also provides the function of using strings as expressions for computation.

The troublesome string processing in SQL is mainly related to grouping inverse operations. A string is divided into several segments by delimiters. it must be separated to form multiple records or form a set for further computation. This type of operation is very difficult for SQL statements without explicit sets.

Comparatively speaking, it is easier to combine the field values into a string during grouping and Summarization. MySQL has the group_concat function to complete this function. other databases also generally have a slightly complex related function.

First, let's look at simple string tasks. The student table structure is as follows: Class, name, and gender. now you need to group the students and girls by class and separate them into comma separated strings. the string members are sorted by name.

	A
1	= Db. query ("select * from student table ")
2	= A1.group (class ;~. Select (Gender = 'male'). (name). sort (). string (): Boy ,~. Select (Gender = 'female '). (name). sort (). string (): Female)

The set calculator has set Data. you can combine various operations without the need for grouping-specific concatenation functions.

The conventional string splitting will generate multiple records. for the inverse operation in the above example, the structure is as follows: the table of classes, boys, and girls is then calculated as a table of students whose structure is class, name, and gender:

	A
1	= Db. query ("select * from class table ")
2	= Create (class, name, gender)
3	> A1.run (A2.insert (0: Boys. array (), A1. class ,~," Male "), A2.insert (0: Female. array (), A1. class ,~," Female "))

Split the string into a set and generate a record accordingly. you do not need to use recursive queries or JOIN the reference table as in SQL.

Sometimes the purpose of string splitting is to perform a set operation. There is a book table with the following structure: title and author. The authors are separated by commas. We need to find out the data of two or more books written by the same group of authors, and the human name order in the authors string is uncertain.

	A
1	= Db. query ("select * from book table ")
2	= A1.group (Author. array (). sort (). select (~. Len ()> 1). conj ()

Split the string into a set and then sort it. it can be used as the group key value. The rest are regular actions.

Date

Similar to strings, databases have no problem with conventional single-value date processing capabilities, but it is also troublesome when it comes to date splitting or date sequence generation. In fact, the root cause is that SQL collection is not thorough enough.

There is a travel record table structure: personnel, start day, end day ,...; Now we need to count which five days are traveling the most.

This problem requires that the date between the start and end days be split into a single date set and then grouped for statistics.

	A
1	= Db. query ("select start day, end day from travel table ")
2	= A1.conj (periods (start day, end day). groups (~ : Date, count (1): Number of persons)
3	= A2.sort (persons:-1). to (5)

The date sharding method can be provided based on the set data type by the set compute, which is easy to solve.

The complexity of generation of date sequences is mainly caused by the characteristics of the date itself, which is more troublesome when accompanied by inverse grouping operations.

The T structure of the event occurrence table is: I (event), S (start date), and E (end date). now, you need to split multiple time periods between two dates by month, the first month and the last month start with "S" and "E" respectively, and the Middle month is the complete month.

	A
1	= Db. query ("select I, S, E from T ")
2	= A1.news (interval @ m (a = S, B = E) + 1; I, max (a, pdate @ m (after @ m (,~ -1): S, min (B, pdate @ me (after @ m (,~ -1): E)

Pdate @ m and @ me can calculate the start and end dates of each month respectively. After @ m will generate the date after a certain date in a few months. This function will automatically target the end of the month and will be very useful when generating the monthly interval.

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