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

It is tedious to implement the GroupBy grouping aggregation operation in Java code similar to that in SQL. It is usually necessary to declare the data structure (Java entity class), then loop through the Java collection, and finally add it to a subset according to the grouping conditions. Java 8 is much simpler with Lambda (stream) code. Grouping is often followed by aggregation operations, which still need to write aggregate functions sum (), count (*), topN (), and so on. These are also the most conventional grouping and aggregation operations, encountered with bitwise grouping, enumerated grouping, multiple grouping and other unconventional grouping plus other aggregation functions (FIRST,LAST …) The code becomes very verbose and ungeneric If there is a middleware that specializes in this kind of calculation, it would be nice to use a similar SQL script to describe the algorithm and call the script directly in Java and return the result set. Java version of the aggregator and SPL script, is such a mechanism, the following examples to illustrate how to use.

Implementation of conventional grouping by SPL

Everyone's overtime records are kept in the duty.xlsx file:

Workday

Name

2016-02-05

Ashley

2016-02-08

Ashley

2016-02-09

Ashley

2016-02-10

Johnson

2016-02-11

Johnson

2016-02-12

Johnson

2016-02-15

Ashley

2016-02-16

Ashley

2016-02-17

Ashley

Summarize the number of days on duty for each person:

A

one

= file ("/ Users/test/duty.xlsx") .importxls@tx ()

two

= A1.groups (name;count (name): count)

Save the script file CountName.dfx (used for embedding Java)

Each group of TopN

Take the overtime records of each month, everyone and the first three days.

A

one

= file ("/ Users/test/duty.xlsx") .importxls@tx ()

two

= A1.group (month (workday): mon,name;~.top (3): top3)

Save the script file RecMonTop3.dfx (used for embedding Java)

Java call

It is very convenient for SPL to be embedded in the Java application. The file CountName.dfx saved in groups is loaded by calling the stored procedure method through JDBC. The example calls are as follows:

... Connection con= null; Class.forName ("com.esproc.jdbc.InternalDriver"); con= DriverManager.getConnection ("jdbc:esproc:local://"); / / call the stored procedure, where CountName is the file name of dfx st = (com. Esproc.jdbc.InternalCStatement) con.prepareCall ("call CountName ()"); / / execute stored procedure st.execute (); / / get result set ResultSet rs = st.getResultSet ();. Connection con= null; Class.forName ("com.esproc.jdbc.InternalDriver"); con= DriverManager.getConnection ("jdbc:esproc:local://"); / / call the stored procedure, where CountName is the file name of dfx st = (com. Esproc.jdbc.InternalCStatement) con.prepareCall ("call CountName ()"); / execute the stored procedure st.execute (); / / get the result set ResultSet rs = st.getResultSet ();

It's the same thing to replace it with RecMonTop3.dfx, just call RecMonTop3 (), or return two result sets at the same time. Only Java snippets are used here to roughly explain how to embed SPL. For detailed steps, see how Java invokes the SPL script, which is also very simple and won't go into detail. At the same time, SPL also supports ODBC drivers, integrated into languages that support ODBC, and the embedding process is similar.

Expansion of excerpts

There is no relevant summary before, in fact, with regard to data grouping, there are actually many kinds of subdivision, alignment grouping, enumerating grouping, multiple grouping. There are summaries and examples in the SPL official forum of the dry College, two of which are excerpted here.

SPL alignment grouping

Example 1: list the number of countries that use Chinese, English, and French as official languages in order

MySQL8: with t (name,ord) as (select 'Chinese',1 union all select' English',2 union all select 'French',3) select t.name, count (countrycode) cnt from t left join world.countrylanguage s on t.name=s.language where s isomerization T' group by name,ord order by ord MySQL8: with t (name,ord) as (select 'Chinese',1 union all select' English',2 union all select 'French',3) select t.name, count (countrycode) cnt from t left join world.countrylanguage s on t.name=s.language where s isomerization T' group by name,ord order by ord

Note: the character set of the table should be consistent with that of the database session.

(1) show variables like 'character_set_connection' to view the current session character set

(2) show create table world.countrylanguage view the character set of the table

(3) set character_set_connection= [character set] updates the current session character set

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from world.countrylanguage where isofficial='T'")

three

[Chinese,English,French]

four

= A2.align@a (A3Magic language)

five

= A4.new (A3 (#): name, ~ .len (): cnt)

A1: connect to the database

A2: query the records of all official languages

A3: languages to be listed

A4: align all records to the corresponding position in A3 by Language

A5: construct an ordered table of the number of countries that use and use that language as the official language

Example 2: list the number of countries that use Chinese, English, French and other languages as official languages in order

MySQL8: with t (name,ord) as (select 'Chinese',1 union all select' English',2 union all select 'French',3 union all select' Other', 4), s (name, cnt) as (select language, count (countrycode) cnt from world.countrylanguage s where s.isocalypal roommates T'and language in ('Chinese','English','French') group by language union all select' Other', count (distinct countrycode) cnt from world.countrylanguage s where isofficial='T' and language not in ('Chinese','English') 'French')) select t.name, s.cnt from t left join s using (name) order by t.ord MySQL8: with t (name,ord) as (select 'Chinese',1 union all select' English',2 union all select 'French',3 union all select' Other', 4), s (name, cnt) as (select language, count (countrycode) cnt from world.countrylanguage s where s.isocalypal roommates T'and language in ('Chinese','English','French') group by language union all select' Other', count (distinct countrycode) cnt from world.countrylanguage s where isofficial='T' and language not in ('Chinese','English') 'French')) select t.name, s.cnt from t left join s using (name) order by t.ord

Aggregator SPL:

A

one

= connect ("mysql")

two

= A1.query@x ("select * from world.countrylanguage where isofficial='T'")

three

[Chinese,English,French,Other]

four

= A2.align@an (A3.to (3), Language)

five

= A4.new (A3 (#): name, if (# = s.start and t.population=s.start and t.population=2000000 and district in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select' Other&Big', count (*) from t where population > = 2000000 and district not in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select' Not Big', count (*) from t where population=2000000 and district in ('Shanghai','Jiangshu') 'Shandong','Zhejiang','Anhui','Jiangxi') union all select' Other&Big', count (*) from t where population > = 2000000 and district not in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select' Not Big', count (*) from t where population=2000000 & & A3.contain (? (2)),? (1) > = 2000000 & & A3.contain (? (2))]

five

[East&Big,Other&Big, Not Big]

six

= A2.enum@n (A4, [Population,District])

seven

= A6.new (A5 (#): class, A6 (#). Len (): cnt)

A5: enum@n stores records that do not meet all the conditions in A4 into the last appended group

Example 3: list the number of large cities in all regions, large cities in East China, and non-large cities

MySQL8: with t as (select * from world.city where CountryCode='CHN') select 'Big' class, count (*) cnt from t where population > = 2000000 union all select' East&Big' class, count (*) cnt from t where population > = 2000000 and district in ('Shanghai','Jiangshu','Shandong','Zhejiang','Anhui','Jiangxi') union all select' Not Big' class, count (*) cnt from t where population=2000000 union all select 'East&Big' class, count (*) cnt from t where population > = 2000000 and district in (' Shanghai','Jiangshu') 'Shandong','Zhejiang','Anhui','Jiangxi') union all select' Not Big' class, count (*) cnt from t where population=2000000,? (1) > = 2000000 & A3.contain (? (2))]

five

[Big, East&Big, Not Big]

six

= A2.enum@rn (A4, [Population,District])

seven

= A6.new (A5 (#): class, A6 (#). Len (): cnt)

A6: if the record in A2 satisfies multiple conditions in A4, enum@r will append it to each group.

Advantage summary

There is a library to write SQL, no library to write SPL

Using the Java program to directly summarize and calculate the data is still tiring, the code is very long and can not be reused, and in many cases the data is not in the database. With SPL, it is as convenient as using SQL in Java.

Commonly used worry-free, you can get the entry version of the right to use for life without spending money.

If the data to be analyzed is one-off or temporary, the dry aggregator provides a free trial license every month and can be recycled for free use. However, if you want to integrate with the Java application and deploy it to the server for long-term use, it is still troublesome to change the trial license on a regular basis. Moisturizer provides an entry version with lifetime access, which solves this worry. How to use the dry aggregator for free?

Technical documentation and community support

There are many ready-made examples in the official aggregator technical documentation itself, and solutions to common problems can be found in the documents. If you get the entry version, you can not only use the general functions of SPL, but also go to the dry college for consultation if you encounter any problems. The official community provides free technical support to entry version users.

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.