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

Today, I will talk to you about how SQL is grouped according to field changes in the time series. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The sorted data is grouped according to the changes in a field, that is, the value of the grouped field is compared with that of the previous line, and if it is the same, it is divided into the same group as the previous line, and a new group is created if it is not the same as the previous line.

This problem is very difficult to do with SQL!

The collection of SQL is unordered, and early SQL had no methods for referencing adjacent rows. Window function is added to the SQL2003 standard, which can reference adjacent rows, but grouping is still very difficult, so it is necessary to use subqueries to create grouping sequence numbers.

For example: query the city where the person is located for a certain period of time and list the start and end times. The existing database table footmark data is as follows:

NAMEFOOTDATECITYTom2020-01-02 08:30:00BeijingTom2020-01-03 08:30:00BeijingTom2020-01-04 13:30:05BeijingTom2020-01-04 16:36:00ChengduTom2020-01-05 08:30:00ChengduTom2020-01-06 12:30:00ChengduTom2020-01-06 17:30:25BeijingTom2020-01-07 09:30:05BeijingTom2020-01-09 16:30:00Beijing.

The results of the final grouping statistics are as follows:

NAMECITYSTARTDATEENDDATETomBeijing2020-01-02 08 12:30:00TomBeijing2020 3012 002020-01-04 13:30:05TomChengdu2020-01-04 16 Swiss 36 Freud 002020-01-06 12:30:00TomBeijing2020-01-06 17 purl 30 purl 252020-01-09 16:30:00

Take Oracle, for example, and write it in SQL as follows:

WITH An AS

(SELECT NAME, FOOTDATE, CITY

CASE WHEN CITY=LAG (CITY) OVER (PARTITION BY NAME ORDER BY FOOTDATE) THEN 0 ELSE ROWNUM END FLAG

FROM FOOTMARK ORDER BY NAME, FOOTDATE)

B AS

(SELECT NAME, FOOTDATE, CITY

MAX (FLAG) OVER (PARTITION BY NAME ORDER BY FOOTDATE) FLAG

FROM A)

C AS

(SELECT NAME, CITY, FLAG

MIN (FOOTDATE) STARTDATE

MAX (FOOTDATE) ENDDATE

FROM B

GROUP BY NAME, CITY, FLAG

ORDER BY NAME, FLAG)

SELECT NAME, CITY, STARTDATE, ENDDATE FROM C

The FLAG here is a man-made grouping sequence number, which is both difficult to write and difficult to understand.

For this sequential grouping, it would be much easier to use the SPL language of the aggregator, with only one line of code:

Connect ("mydb") .query ("SELECT * FROM FOOTMARK ORDER BY NAME,FOOTDATE") .groups @ o (NAME,CITY;min (FOOTDATE): STARTDATE,max (FOOTDATE): ENDDATE)

SPL is based on an ordered set implementation and provides the option @ o to group in order, which makes it easy to solve this problem.

SPL provides many grouping methods, such as equivalent grouping, ordered grouping, ordered conditional grouping, serial number grouping, nested grouping, big data ordered grouping, big data ordered conditional grouping and so on.

Aggregator SPL is a professional scripting language to solve SQL problems. It has simple syntax and accords with natural thinking. It is a natural step-by-step and clear process-oriented computing language. It uses a unified syntax independent of the database, and the algorithm can be seamlessly migrated between databases. It is a desktop-level computing tool, ready to use, simple configuration, perfect debugging functions, breakpoints can be set, single-step execution, and the results of each step can be viewed.

After reading the above, do you have any further understanding of how SQL is grouped according to field changes in the time series? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.