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

Although SQL has the concept of set, the support for set operation, especially ordered set operation, is very limited, and it is often completed with inexplicable ideas, and the computational efficiency is not good. On the other hand, the aggregator SPL is much more intuitive and can write operations according to natural thinking habits. Here, a comparison is made between SQL and the aggregator SPL in terms of set operation and line number correlation operation.

1. Sum set

Example 1: calculate the total number of days contained in multiple time periods when the overlapping part is not repeated.

MySQL8:

With recursive t (start,end) as (select date'2010-01-07 dating 2010-01-9'

Union all select date'2010-01-15 date date 2010-01-16'

Union all select date'2010-01-07 date date 2010-01-12

Union all select date'2010-01-08 Journal dates 2010-01-11')

T1 (dend) as (select start,end from t)

Union all select dudes 1 end from T1 where d

Select count (distinct d) from T1

Explanation: this example first converts each time period to the corresponding date of all days in the time period, and then calculates the number of different dates.

Aggregator SPL:

A3: construct a date sequence from start to end for each time period in A2

A4: find the sum of all date sequences in A3

A5: find the number of dates that are not repeated in A4

2. Difference set

Example 1: list countries with English-speaking and French-speaking populations both exceeding 5%

MySQL8:

With T1 (lang) as (select 'English' union all select' French')

Select name from world.country c

Where not exists (select * from T1 where lang not in (select language from world.countrylanguage where percentage > = 5 and countrycode=c.code))

Note: this SQL only demonstrates that the difference set is empty through double negation.

Aggregator SPL:

A4: select the group whose difference between "English", "French" and the language set of this group is empty, which means to select the group whose language set contains English and French.

3. Intersection

Example 1: list country codes with English-speaking, French-speaking and Spanish-speaking populations exceeding 0.3%, 0.2% and 0.1%, respectively

MySQL8:

With T1 as (select countrycode from world.countrylanguage where language='English' and percentage > 0.3)

T2 as (select countrycode from world.countrylanguage where language='French' and percentage > 0.2)

T3 as (select countrycode from world.countrylanguage where language='Spanish' and percentage > 0.1)

Select countrycode

From T1 join T2 using (countrycode) join T3 using (countrycode)

Explanation: this example only demonstrates how to solve the intersection of multiple sets.

Aggregator SPL:

A3: query the country codes of more than 0.3% of English, 0.2% of French and 0.1% of Spanish in order, and convert them into a sequence.

The intersection of all sequences in A5: A3

4. Fetch data according to the line number

Example 1: calculate the trading information of China Merchants Bank (600036) on the 3rd trading day and the penultimate trading day in 2017.

MySQL8:

With t as (select *, row_number () over (order by tdate) rn from stktrade where sid='600036' and tdate between '2017-01-01-01' and '2017-12-31')

Select tdate,open,close,volume from t where rn=3

Union all

Select tdate,open,close,volume from t where rn= (select max (rn)-2 from t)

Aggregator SPL:

A3: sum of Article 3 records and penultimate Article 3 records

Example 2: calculate the average closing price of China Merchants Bank (600036) for the last 20 trading days

MySQL8:

With t as (select *, row_number () over (order by tdate desc) rn from stktrade where sid='600036')

Select avg (close) avg20 from t where rn=25

Aggregator SPL:

A3: find the first record position with a closing price of 25 yuan after going there.

Example 2: calculate the 2017 increase of Gree Electric Appliances (000651) (consider suspension)

MySQL8:

With t as (select * from stktrade where sid='000651')

T1 (d) as (select max (tdate) from t where tdate

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.