Note: SPL is the scripting language used by esProc. DFX is the script file, which is equivalent to an external stored procedure. Report uses the JDBC interface to pass in SPL statements to realize structured calculation or script execution.
| 1 | select max(continuousDays)-1 |
| 2 | from (select count(*) continuousDays |
| 3 | from (select sum(changeSign) over(order by tradeDate) unRiseDays |
| 4 | from (select tradeDate, |
| 5 | case when closePrice>lag(closePrice) over(order by tradeDate) |
| 6 | then 0 else 1 end changeSign |
| 7 | from stock) ) |
| 8 | group by unRiseDays) |
Can you do it in a more natural way of thinking?
| A | |
|---|---|
| 1 | =stock.sort(tradeDate) |
| 2 | =0 |
| 3 | =A1.max(A2=if(closePrice>closePrice[-1],A2+1,0)) |
Syntax suitable for describing a natural way of thinking!
Using stored procedure to implement data preparation algorithm will cause the coupling between report and database.
Switch database dynamically according to parameters
${pds}.query("select * from T where F=?",pF)
Generate algorithm dynamically. Concat dynamic SQL.
| A | B | |
|---|---|---|
| 1 | =sums.array().("sum("+~+") as "+~).string() | /sum(a) as a, sum(b) as b |
| 2 | =db.query("select G,"+A1+" from T group by G") |
Capacity control for result set
| A | B | C | |
|---|---|---|---|
| 1 | =db.cursor("select * from T") | =A1.fetch(1000) | |
| 2 | if B1.fetch@0(1) | >B1.insert(0,"Continue") | /Insert markers when uncompleted |
| 3 | >A1.close() | return B1 |
Databases generally have a bad JDBC performance, but the reporting performance relies heavily on the data retrieval performance. esProc can create multiple database connections to retrieve data in segments by using multithreaded parallel processing, and the performance can be increased by several times.
| A | B | C | |
|---|---|---|---|
| 1 | fork 4 | =connect(db) | /There are four threads, and each connection is established separately. |
| 2 | =B1.query@x("select * from T where part=?",A1) | /Retrieve each segment separately | |
| 3 | =A1.conj() | /Merge the results |
... Connection con = null; Class.forName("com.esproc.jdbc.InternalDriver"); con= DriverManager.getConnection("jdbc:esproc:local://"); // Calling stored procedures ，CountName is the file name of dfx st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call CountName()"); // Execute stored procedures st.execute(); //Get result set ResultSet rs = st.getResultSet(); ...
...
Connection con = null;
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
// Calling stored procedures ，CountName is the file name of dfx
st =(com. esproc.jdbc.InternalCStatement)con.createStatement();
//Query files using SQL,get result set
ResultSet rs = st.executeQuery("$select name,count(*) from
/home/user/duty.txt group by name");
...