Shulou(Shulou.com)06/01 Report--

This article mainly explains "how data analysts deal with offline analysis after database count". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "data analysts how to deal with the database after the offline analysis" it!

Due to the lack of the support of easy-to-use tools, the offline analysis actions of data analysts always start with a complex query written in advance or the data derived from a temporary table after multi-step processing, and then complete with vlookup () and filter in Excel. When the data is updated or the scope is expanded, it is necessary to re-fetch the data and re-operate in Excel. If there is a desktop tool that can read single table data in real time each time, it can not only complete the above complex query and multi-step calculation to temporary table, but also replace the calculation action in Excel and avoid repetitive work, which will be a good news for many data analysts.

If you can write SQL or VBA, and care very much about the flexibility of multiple calculations after getting the data, rather than want to use a graphical interface tool to complete limited calculations, and do not want to spend a lot of time learning Python, aggregators may be a good choice.

For example, there are three tables in the database:

SQL Query 1 result: (T1)

Col1 Col2 A 1B 2

Query 2 result: (T2)

Col3 Col4 1 # 2 *

Query 3 result: (T3)

Col5 1 3

You need to complete the association operation similar to the following SQL for the above three 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 Col4A #

If the amount of data is not large, the SPL code of the aggregator is calculated directly in memory 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)

T1, T2, T3 can also come from different databases or data files.

If the amount of data is large, you can use the aggregator's cursor, which is similar to the database cursor, 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 result of the final calculation to make a chart in Excel, it is also convenient to export to Excel.

File ("Result.xlsx") .xlsexport (Result)

The aggregator is installed and used, and it is very handy to deal with the above problems. The aggregator also supports saving big data's cost files first, and then reading these types of determined and compressed data for efficient calculation, which can reduce the load on the database and make local computing more efficient.

At this point, I believe you have a deeper understanding of "data analysts how to deal with offline analysis after database count". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.