 | esProcPure Java ✓ Lightweight ✓ Embedded, no independent server ✓ JVM，JDK1.8 or above ✓ VM/Container/Android |
Report objective: To query the status of major customers and orders that account for half of the total sales revenue
SELECT CUSTOMER, AMOUNT, SUM_AMOUNT
FROM (SELECT CUSTOMER, AMOUNT,
SUM(AMOUNT) OVER(ORDER BY AMOUNT DESC) SUM_AMOUNT
FROM (SELECT CUSTOMER, SUM(AMOUNT) AMOUNT
FROM ORDERS GROUP BY CUSTOMER))
WHERE2* SUM_AMOUNT < (SELECTSUM(AMOUNT) TOTAL FROM ORDERS)| A | B | |
|---|---|---|
| 1 | =db.query("select customer,amount from orders order by amount desc") | |
| 2 | =A1.sum(amount)/2 | =0 |
| 3 | =A1.pselect((B1+=amount)>=A2) | return A1.to(A3) |
Report objective: To track the retention of new users for the next day on a daily basis
WITH first_login AS ( SELECT userid, MIN(TRUNC(ts)) AS first_login_date FROM login_data GROUP BY userid),
next_day_login AS (
SELECT DISTINCT(fl.userid), fl.first_login_date, TRUNC(ld.ts) AS next_day_login_date
FROM first_login fl LEFT JOIN login_data ld ONfl.userid=ld.useridWHERE TRUNC(ld.ts) =fl.first_login_date+1),
day_new_users AS (
SELECT first_login_date,COUNT(*) AS new_user_num FROM first_login GROUP BY first_login_date),
next_new_users AS (
SELECT next_day_login_date, COUNT(*) AS next_user_num FROM next_day_login GROUP BY next_day_login_date),
all_date AS (
SELECT DISTINCT(TRUNC(ts)) AS login_date FROM login_data)
SELECTall_date.login_date+1AS dt,dn. new_user_num,nn. next_user_num,
(CASE WHEN nn. next_day_login_date IS NULL THEN 0 ELSE nn.next_user_num END)/dn.new_user_numAS ret_rate
FROM all_date JOIN day_new_users dn ONall_date.login_date=dn.first_login_dateLEFT JOIN next_new_users nn ONdn.first_login_date+1=nn. next_day_login_date
ORDER BYall_date.login_date;| A | |
|---|---|
| 1 | =file("login_data.csv").import@tc() |
| 2 | =A1.group(userid;fst=date(ts):fst_login,~.(date(ts)).pos(fst+1)>0:w_sec_login) |
| 3 | =A2.groups(fst_login+1:dt;count(w_sec_login)/count(1):ret_rate) |
 |  Creating complex reports is simpler than BIRT/JasperReport |
 | ✓ Access data sources directly through the native interface, without predefined mapping, while preserving their features ✓ Lightweight, avoiding heavy logical data warehouses |
✓ AHistorical cold data is calculated and read from the AP database ✓ Transaction hot data is read from the TP database in real time ✓ Mixed computing to implement real-time report of whole dat |  |
One SQL statement that works across all databases, allowing migration without altering the report.
| SQL | SELECT EID, NAME, BIRTHDAY, ADDMONTHS(BIRTHDAY,10) DAY10 FROM EMP |
| ⇩ esProc conversion ⇩ | |
| ORACLE | SELECT EID, NAME, BIRTHDAY, BIRTHDAY+NUMTOYMINTERVAL(10,'MONTH') DAY10 FROM EMP |
| SQLSVR | SELECT EID, NAME, BIRTHDAY, DATEADD(MM,10,BIRTHDAY) DAY10 FROM EMP |
| DB2 | SELECT EID, NAME, BIRTHDAY, BIRTHDAY+10 MONTHS DAY10 FROM EMP |
| MYSQL | SELECT EID, NAME, BIRTHDAY, BIRTHDAY+INTERVAL 10 MONTH DAY10 FROM EMP |
| POSTGRES | SELECT EID, NAME, BIRTHDAY, BIRTHDAY+interval '10 months' DAY10 FROM EMP |
| TERADATA | SELECT EID, NAME, BIRTHDAY, ADD_MONTHS(BIRTHDAY, 10) DAY10 FROM EMP |
 |  |
 | ✓ "Report data sources are delivered as microservices, simplifying extension and migration ✓ esProc scripts are interpreted, enabling hot swapping by default ✓ Adapt to the ever-changing reporting business. |
 | ✓ Move stored procedures outside the database, making them independent and easily migratable ✓ Reduce coupling between applications ✓ No need for compile privileges on stored procedures, enhancing security and reliability |
 | ✓ Move intermediate tables to files for storage and processing, easing the database load ✓ The tree-structured file system simplifies management and reduces coupling between applications |
 Multi threaded parallel data retrieval, compatible with both homogeneous and heterogeneous databases, boosts query performance | Single table parallel data retrieval:  Parallel data retrieval from multiple tables (databases):  |
An option to enable parallelism can significantly enhance computing performance
 | ✓ Faster file system IO ✓ Binary format does not require parsing ✓ High performance support: compression, columnar storage, indexing … |
