Sometimes we have the requirement of dividing MongoDB documents into multiple segments by specified intervals and performing an aggregation over each segment. Now we have collection scores. We need to group it by intervals of scores and count the students in each interval for both Chinese and English. Below is related information:
The score intervals are [60, 70, 80, 90], which are [0,60), [60,70), [70,80), [80,90), and [90,).

The expected result:

As MongoDB doesn’t provide related interface, we use MapReduce to traverse each document and count students of each score interval for the two subjects respectively. The solution is complicated.

It would be easy if we could use esProc SPL to group records by subjects and score interval and then concatenate result.
Download esProc installation package HERE.

Directions:

1. Write SPL script score.dfx in esProc:

  2. Execute the script to return the final result:  

Pseg() function returns the sequence number of interval a score falls in and accumulate the number of students score by score according to the corresponding sequence number of interval.

esProc provides JDBC interface to let you integrate the script into a Java program conveniently:

public static void doWork() {

    Connection con = null;
    java.sql.Statement st;
   
    try{
        Class.forName("com.esproc.jdbc.InternalDriver");
        con = DriverManager.getConnection("jdbc:esproc:local://"); 
        // Call script score.dfx
        st=con.createStatement(); 
        ResultSet rst = st.executeQuery("call score");
        System.out.println(rst);
    } catch(Exception e){
        System.out.println(e);
    } finally{
        // Close database connection
        if (con!= null) {
            try {
                con.close();
            } catch(Exception e) {
                System.out.println(e);
            }
        }  
    }
}

Read How to Call an SPL Script in Java to learn more about integration of esProc SPL script into a Java program.