How do analysts deal with offline analysis more quickly?

Because of the lack of support of easy-to-use tools, the offline analysis of data analysts is always performed first by a written complex query or data exported from a temporary table after multi-step processing, and then by using vlookup() and filters in Excel. When the data is updated or the scope is expanded, it is necessary to retrieve the data again and operate in Excel again. If there is a desktop tool that can read single table data in real-time, it can not only complete the above complex query and the calculation from multi-step processing to a temporary table, but also replace the calculation action in Excel, and avoid repeated labor, it will be good news for many data analysts.

If you can write SQL or VBA, you are very concerned about the flexibility of multiple calculations after obtaining data, rather than using a graphical interface tool to complete limited calculations, and you don’t want to spend a lot of time to learn python, esProc may be a good choice.

For example, there are three tables in the database:

SQL Query 1 result: (T1)

Col1 Col2 
A 1
B 2

Query 2 result: (T2)

Col3 Col4 
1 #
2 *

Query 3 result: (T3)

Col5   
1
3

You need to perform the SQL join operations on the above T1, T2, and T3 tables:

Select T1.col1,T2.col4 
from T1,T2
where T1.col2=T2.col3
and T1.col2 in (Select T3.col5 from T3)

The result is:

Col1  Col4
A #

If the amount of data is small and the data is calculated directly in memory, the esProc SPL code is as follows:

T1=DB.query("select Col1,Col2 from T1")
T2=DB.query("select Col3,Col4 from T2")
T3=DB.query("select Col5 from T3")
Result=T1.join@i(Col2,T2:Col3,Col1,Col4).select(T3.(Col5).contain(Col2)).new(Col1,Col4)

If the data volume is large, you can use the cursor of esProc, which is similar to the database cursor. The code is as follows:

CS1=DB.cursor("select Col1,Col2 from T1 orderby Col2")
CS2=DB.cursor("select Col3,Col4 from T2 orderby Col3")
S3=DB.query@i("select Col5 from T3")
Result=joinx(CS1,Col2;CS2,Col3).select(S3.contain(#1.Col2)).new(#1.Col1,#2.Col4).fetch()

If you want to use the final calculated results to make a chart in Excel, it is also convenient to export to excel.

file("Result.xlsx").xlsexport(Result)

esProc is ready to use, and it is very handy to solve the above problems. esProc also supports saving big data into local files first and then reading these identified and compressed data for efficient calculation, which can reduce the load of the database and make the local calculation more efficient.

Products and resources that simplify hard data processing tasks. If you have any questions, send me a message.