Interactive Query Computation

The decision support is based on the query computations on the existing database. The interactive query is the basic need of end users. The accurate and complete retrieval conditions can help user get the required data from the database.
At present, there are two kinds of interactive query solutions:

1. Regarding the tools for interactive query computation, the most common tool is SQL statement querying directly on the database. But, using SQL or stored procedure to query will bring about various troubles. First, users must grasp the database structure and know the name of every table and their relations, just like a database administrator. Second, SQL as a generic query language is quite awkward to deal with the complicated computation: Using SQL to carry out a programmed computation, the developer may be unable to carry through the computation or have to write a long string of complicated statements.
2. The interactive computation can also be implemented by programming directly. But this method will result in a much higher difficulty and great workload if compared with using SQL. Moreover, the program is not suitable to modify once programming is completed. You will be unable to handle it timely if the business logic changes or there is a new computation task

We may need to obtain some useful information by analyzing and computing from time to time, based on the raw data fragment in the database, such as:

• Is there any stock rising to the expected rate for three consecutive business days in a month?
• What are the top n clients achieving half of total sales?
• Is there any goods sold out for at least 3 times by PM 5 in a month?
• What is the average interval between the purchases of iPhone and later the iPad by a same customer?
• Is there any subscriber run out of credit to pay the telephone bill?
• …

If using SQL to solve these problems, then you may have to write the complicated nested statements to conduct such kind of queries. Only those having a relatively strong technical ability could take the tough job, and such SQL statements are hard to read and comprehend. Programming is not a suitable way to handle such highly flexible problems.
esProc can complete such jobs perfectly because it only requires relatively less codes to implement the complicated computation.
For example, from the stock exchange data of a month in a certain exchange, how to pick out the stocks whose increase amplitude is over 3% for three consecutive trading days?
Sort the trading records by the code(ISIN) and the transaction date(Date), and then group by ISIN. Then, the rise amplitude of a certain stock can be simplified as the rise amplitude resulting from the difference between the present closing price and that of the previous trading day. At last, you can simply pick out the stocks that rise over 0.03 for three consecutive trading days.



The query in esProc can be carried out by step, and the purpose of each step is clear and easy for user to look into. Through computing, the group of stocks rise over 0.03 in three consecutive trading days is found:

You can double click to have a look on the detailed record in the group:

In A8, only the ISIN serial numbers of stocks satisfying the conditions are listed:

In addition, you can also firstly compute the maximum consecutive rising days during which any stock rises over 0.03, and then filter:



With esProc, you can easily invoke the computation in the cells whenever necessary. This is quite beneficial for those computations to be done in steps. In A10, the data from A5 is reused to compute the maximum consecutive days during which every stock in the group rises over 0.03. Then, in A11, select the code ISIN of stock that rise for over 3 consecutive days. The result is the same as that of A8:

With esProc, you can add field and sort, group, filter, sum up, and perform other actions on data to complete the complicated interactive query computation perfectly.