{{announcement.body}}
{{announcement.title}}

How Do Analysts Deal With Offline Analyses More Quickly?

DZone 's Guide to

How Do Analysts Deal With Offline Analyses More Quickly?

See how analysts deal with offline analyses after retrieving data from databases.

· Database Zone ·
Free Resource

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)

Java
 




x


 
1
Col1 Col2 
2
A    1
3
B    2



Query 2 result: (T2)

Java
 




xxxxxxxxxx
1


 
1
Col3 Col4 
2
1    #
3
2    *



Query 3 result: (T3)

Java
 




xxxxxxxxxx
1


 
1
Col5   
2
1     
3
3



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

Java
 




xxxxxxxxxx
1


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



The result is:

Java
 




xxxxxxxxxx
1


 
1
Col1  Col4
2
A    #



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

Java
 




xxxxxxxxxx
1


 
1
T1=DB.query("select Col1,Col2 from T1")
2
T2=DB.query("select Col3,Col4 from T2")
3
T3=DB.query("select Col5 from T3")
4
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:

Java
 




xxxxxxxxxx
1


 
1
CS1=DB.cursor("select Col1,Col2 from T1 orderby Col2")
2
CS2=DB.cursor("select Col3,Col4 from T2 orderby Col3")
3
S3=DB.query@i("select Col5 from T3")
4
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.

Java
 




xxxxxxxxxx
1


 
1
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.

Topics:
analysis and report, database, dba, excel, sql, tutorial

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}