JP3627030B2 - Data extraction system and computer-readable recording medium therefor - Google Patents

Description

[0001]
BACKGROUND OF THE INVENTION
The present invention relates to a data extraction system and a recording medium storing a program therefor, and in particular, searches a database based on a query (for example, a query described later) composed of an extraction target name and an extraction condition item. Thus, the present invention relates to a data extraction system for extracting data corresponding to the inquiry.
[0002]
When extracting desired data from a large amount of information stored in a data base such as a data warehouse, multiple queries for data extraction are used, and these extraction condition item values are real-time. Usually, data extraction based on new inquiry contents is executed for a predetermined time, and the results are analyzed and managed in a multifaceted manner. The change of the extraction condition item value is also performed as a query editing operation.
[0003]
Therefore, it is possible to change the extraction condition item value across multiple queries efficiently with a simple operation.In addition, the estimated execution time of the query after the change processing is displayed in advance to improve the efficiency of workers' time use. Therefore, the present invention meets such a demand.
[0004]
[Prior art]
Conventionally, in a database extraction device such as a database query support tool, when changing the extraction condition of a corresponding query at the time of new continuous execution of multiple queries or editing of the entire query, Each query was selected and displayed individually, and its contents were changed.
[0005]
That is, the first target query is taken out of the definition part and displayed on the display device, where the content of the target query is changed and the definition part is updated with the changed content, and then the next target query is updated from the definition part. The process of taking out and executing the display, change, and update processes in the same manner as in the first target query was repeated for all target queries.
[0006]
In addition, when extracting data based on multiple target queries, the extraction condition item value change, query execution, and execution result confirmation cycle are often repeated in a trial and error manner in real time. It was difficult to assume the time until the execution result for the target query was obtained.
[0007]
FIG. 10 is an explanatory diagram showing a conventional query continuous execution definition (macro) example and a query editing example.
-In the macro example (a), the execution of the query selected from the query list on the operation screen is instructed by clicking the execution button, etc.
-In the query editing example in (b), editing of one query selected from the query list on the operation screen (for example, “sales status”) is instructed by clicking the edit button, etc., and the definition content of the selected query is displayed on the screen. Then, on this screen, the extraction conditions such as “year” and “store name” are changed as necessary.
[0008]
[Problems to be solved by the invention]
As described above, when the extraction conditions of a plurality of queries corresponding to the above-described query are changed in the conventional data extraction system, each query is individually selected and displayed, and the contents are displayed. Because it is not possible to compare the extraction conditions such as “store name” common to “sales status” and “purchase status” that are different queries at the same time. There was a problem that thought was interrupted and the process of changing the common extraction condition between the selected queries could not be performed efficiently.
[0009]
In addition, since it is difficult for the operator to assume the time required for continuous execution of a plurality of queries, there is a problem that the worker is restrained in front of the terminal for an unspecified time until the continuous execution ends.
[0010]
Therefore, in the present invention, a plurality of selected queries are displayed in a list in units of extraction target names and extraction condition items that are the respective constituent elements, and further, individual change / batch change for each extraction condition item on the list display By specifying, it is possible to select the comparison target of the extraction condition across different queries and the change format, and to improve the efficiency of the modification process of the extraction condition when editing multiple queries or during continuous execution With the goal.
[0011]
In addition, by displaying a list of past execution times for multiple queries together, it is possible to make the workers estimate the required time prior to new continuous execution after changing the multiple query extraction conditions. The purpose is to reduce the unnecessary restraint time of workers at the site.
[0012]
[Means for Solving the Problems]
The present invention solves this problem as follows.
(1) In a data extraction system for extracting execution result data of a query by searching a database based on a query composed of a search target name and an extraction condition item for the search target . A query management unit that acquires and holds the content data corresponding to each of the plurality of selected search target names from a query definition unit in which content data is described in units of the search target names; and the query A display unit that displays a list of a plurality of the content data held in the management unit for each extraction condition item, and an input unit that executes a change input process for the content data displayed as a list on the display unit are provided.
(2) In the above (1), a time management table is provided that stores the time required for execution for each of the inquiries, and the inquiry management unit sets the time required for execution for each of the inquiries to the time management table. And is displayed on the display unit.
(3) In the above (1) and (2), the inquiry management unit performs a process of changing the extraction condition item data for a plurality of the inquiries based on individual / collective change instructions from the input unit. Execute.
[0013]
According to the present invention, as described in (1) above, the extraction condition items of a plurality of selected queries are managed, and the queries are listed in units of extraction target names and extraction condition items. The extraction condition changing process can be performed accurately in a short time while viewing this list display.
[0014]
In this list display, one query content is displayed in the same row and the same extraction condition item is displayed in the same column in a matrix format, and further, a blank cell that does not have an extraction condition item value is distinguished from other existing cells. For example, by displaying a blank cell in a shaded manner or in a specific color, the list of extraction conditions can be improved.
[0015]
In addition, as shown in (2) above, since the past execution times of each of the plurality of queries are also displayed in a list, the worker can set the time required for new continuous execution after the change of the extraction conditions of the plurality of queries. You can give a guide.
[0016]
Further, as described in (3) above, the batch change process of the extraction condition item values can be performed for each extraction condition item, so that the extraction condition change operation is simplified and the change processing time can be shortened. Furthermore, convenience can be further enhanced by directly specifying an extraction condition cell to be changed and changing its value.
[0017]
Further, when the continuous execution of a plurality of inquiries is instructed, the search using the extraction condition item data of the inquiries already managed by the inquiry management unit is continuously performed .
[0018]
The present invention is also directed to a computer-readable program recording medium storing a program for causing a computer to realize these functions.
[0019]
DETAILED DESCRIPTION OF THE INVENTION
An embodiment of the present invention will be described with reference to FIGS.
In the following description using these figures, for convenience of explanation, SQL (structured query language) is used as the database language, and a query as an example of the above query is used.
[0020]
FIG. 1 is an explanatory diagram illustrating an example of a display screen in a matrix format.
In this display screen example, read from a query definition section (see FIG. 2) described later,
・ Contents of service status, sales status, and purchase status queries (see Fig. 3) (extraction condition items such as reason for return, sales year, store name), past execution time of the query, and The total time is displayed in a matrix format.
[0021]
In addition, the past execution time and the extraction condition item for each query name unit are displayed on the same line, and the reason for return, the sales year, the store name, etc. for each extraction condition item unit are displayed in the same column. A blank cell is a cell without an extraction condition value.
[0022]
For example, for the query “Sales Status”
・ "First half of 1994" as sales year
・ "Main store" as a store
Extraction conditions are set, and no extraction condition regarding the reason for return is set. Further, “2 minutes 10 seconds” of the time required for executing the past database search based on this extraction condition is also displayed.
[0023]
As a button on the screen,
・ Switch specification button 1 to select individual change of specific cell and batch change of extraction condition item unit
・ Addition to display the query continuous execution list and query definition list (hereinafter referred to as “query continuous execution list” as necessary in the following explanation) on the screen, delete to delete the matrix display part shown in the row unit And an edit button 2 having various functions such as ending to confirm the entire contents of the extraction condition change
Holding for storing the extracted extraction condition in the query definition unit 10 (see FIG. 2), which will be described later, and execution for shifting to continuous execution of each query (database search processing) based on the value of the extraction condition Operation buttons 3 with various functions such as
Etc.
[0024]
The query continuous execution list is a combination list of a plurality of query names that have been continuously executed in the past, and the query definition list is a list of defined query names (see FIG. 10). 2). Further, a plurality of query names at the time of continuous execution of individual queries so far are held in the query definition unit 10 (see FIG. 2).
[0025]
For example, when the store name of “sales status” and “purchase status” is to be changed to “Nagoya store” on the display screen of FIG. 1, an operation for switching the store name switching designation button 1 to “batch” as shown in the figure. And an operation of updating and inputting “Nagoya store” in an arbitrary cell in which the store name is set may be performed in an arbitrary order.
[0026]
In addition, when it is desired to change the store name of “Purchase Status” to “Sapporo Store” and “Nagoya Store”, the operation of switching the store name switch designation button 1 to “individual” contrary to the illustration. The operation of additionally inputting “Nagoya store” in the store name cell of “purchase status” may be performed in an arbitrary order.
[0027]
FIG. 2 is an explanatory diagram showing the overall configuration of the data extraction system,
10 is a query definition part describing the contents of each query (see FIG. 3),
20 is a data extraction device,
21 is a matrix management part of the query selected by the user,
22 is a matrix management table storing the query name and extraction condition item of each selected query,
23 is a query execution unit for instructing continuous execution of selection queries and monitoring of execution time of each of the selection queries,
24 is a time management table for storing the time required for executing a query;
25 is a result output unit for holding the execution result of the selection query;
30 is a time monitoring device for obtaining a query execution time based on an instruction from the query execution unit 23;
40 is a DBMS (database management system) that executes a query based on an instruction from the query execution unit 23;
50 is an extraction target DB (database),
Reference numeral 60 denotes an input / output device such as a screen or keyboard corresponding to the matrix management unit 21;
70 is an output device such as a screen or printer corresponding to the result output unit,
Respectively.
[0029]
The user selects a query used for database search from a continuous query execution list or the like (not shown) displayed on the screen of the input / output device 60, and displays each definition content in a matrix on the screen ( Therefore, after performing the extraction condition item value changing process as described above, the DBMS 40 is caused to execute the query. A continuous query execution list and the like (a continuous query execution list and a query definition list) are held in the matrix management unit 21 as described above.
[0030]
Further, the execution result of the DBMS 40 is confirmed by the output device 70, and the input / output device 60 instructs the reflection of the changed extraction condition to the query definition unit 10. Of course, it is optional whether or not to update the query contents so far by reflecting the changed extraction condition in the query definition unit 10.
[0031]
In the data extraction system of FIG.
Including the query definition unit 10 and the time monitoring device 30 in the data extraction device 20,
-Common input / output device 60 and output device 70,
You may do it.
[0032]
FIG. 3 is an explanatory diagram of a definition example of a query.
Each query specifies an extraction item name after “select”, specifies a search target after “from”, and specifies an extraction condition item name and its value after “where”.
[0033]
FIG. 4 is an explanatory diagram showing an overview of the matrix management table 22, wherein 22a is a change designation flag, 22b is an extraction condition item name, 22c is a query name, 22d is an execution time, 22e is an extraction condition item value, Reference numeral 22f indicates the total value of the required execution times. The content shown corresponds to the display screen of FIG.
[0034]
FIG. 5 is an explanatory diagram showing an outline of the time management table 24.
The content shown in the figure is the past execution time of each query (service status, sales status, purchase status, etc.) in FIG.
[0035]
FIG. 6 is an explanatory diagram showing a processing procedure when a target query for data extraction is selected and displayed, and its contents are as follows. The order of steps (12) to (14) and the order of steps (15) and (16) are arbitrary.
(11) Select a target query from a query continuous execution list or the like (query continuous execution list or query definition list) on the operation screen, and proceed to the next step.
(12) The “query name” of each selected query is registered in the matrix management table 22, and the process proceeds to the next step.
(13) The contents of each selected query are read from the query definition unit 10 and held in the work area, and the process proceeds to the next step. If the selection in step (11) is a query continuous execution list, the query names corresponding to the selection are held in the query definition unit 10 as described above, so that their contents are read continuously. It will be.
(14) Substitute the total number of selected queries into the constant M, and substitute “1” into the variable N, and proceed to the next step.
(15) For all the extraction condition items of the Nth query,
If it is an item that has already been described, put the value of the item in the cell of the item column of the matrix management table 22,
If it is the first item, add a new item row to the matrix management table 22 and enter the item name and its value there.
Proceed to the next step.
(16) The time required for execution of the Nth query is obtained from the time management table 24 and entered in the execution time cell 22d of the query of the matrix management table 22, and the process proceeds to the next step.
(17) It is determined whether or not “N ≧ M”. If “YES”, the process proceeds to step (19), and if “NO”, the process proceeds to the next step.
(18) Return “N + 1 → N” and return to step (15).
(19) The values stored in the execution time cells 22d of the matrix management table 22 are added to the execution time total cell 22f, and the process proceeds to the next step.
(20) The contents of the matrix management table 22 are displayed on the input / output device 60 on the screen, and the series of processes is terminated.
[0036]
FIG. 7 is an explanatory diagram showing a processing procedure when the data extraction condition is changed, and the contents thereof are as follows. Note that the process of step (31) may be executed after step (33).
(31) The individual / batch change is designated by a click operation on the switch designation button 1, and the contents are reflected in the change designation flag 22a of the matrix management table 22, and the process proceeds to the next step.
(32) The position of the cell to be changed is designated with a pointing device or a cursor movement key, and the process proceeds to the next step.
(33) The change contents of the data extraction conditions are input from the input / output device 60, and the process proceeds to the next step.
(34) It is determined whether the change designation flag 22a of the column is set individually or collectively. If “individual”, the process proceeds to the next step, and if “changed”, the process proceeds to step (36).
(35) The contents of the change target cell of the matrix management table 22 are updated with the change value of step (33), and the series of processes is terminated.
(36) The column to which the change target cell belongs, that is, the extraction condition item is obtained, and the process proceeds to the next step.
(37) The contents of each cell (excluding blank cells) in the corresponding column of the matrix management table 22 are updated with the changed value in step (33), and the series of processes is terminated.
[0037]
FIG. 8 is an explanatory diagram showing a processing procedure when a query is executed, and its contents are as follows. The order of steps (54) and (55) is arbitrary.
(51) After changing the data extraction conditions, click “execute” of the operation button 3 to proceed to the next step.
(52) Substitute the total number of selected queries into the constant M, and substitute “1” into the variable N, and proceed to the next step.
(53) With respect to the Nth query, the time monitoring device 30 is requested to monitor the time required for execution, and the DBMS 40 is instructed to execute the execution, and the process proceeds to the next step.
(54) The execution result of the query is received from the DBMS 40, held in the result output unit 25, and proceeds to the next step.
(55) The time required for executing the query is received from the time monitoring device 30 and stored in the corresponding cell of the time management table 24, and is stored in the corresponding cell of the matrix management table 22, and the next step is performed. move on.
(56) It is determined whether or not “N ≧ M”. If “YES”, the series of processing ends, and if “NO”, the process proceeds to the next step.
(57) As “N + 1 → N”, the process returns to the step (53).
[0038]
FIG. 9 is an explanatory diagram showing an outline of a computer system that reads and executes a program from a computer-readable recording medium. 80 is a computer system, 81 is a main body unit incorporating a CPU, a disk drive device, and the like. , 82 is a display for displaying an image in accordance with an instruction from the main body 81, 83 is a display screen, 84 is a keyboard for inputting various information to the computer system 80, and 85 is an arbitrary on the display screen 83. A mouse for specifying the position of the computer, 86 is an external database (line-destination memory such as DASD), 87 is a modem for accessing the external database 86, 88 is a CD-ROM, floppy disk, etc. Each of these portable recording media is shown.
[0039]
As a recording medium for storing the program,
-Database 86 on the program provider side (destination memory)
・ Portable recording medium 88
Any of the memories on the main body 81 side or the like may be used, and the program is loaded into the main body 81 and executed on the main memory.
[0040]
【The invention's effect】
In this way, the present invention manages the extraction condition items of a plurality of selected queries and displays a list of each of the queries in units of extraction target names and extraction condition items. The extraction condition changing process can be performed accurately in a short time while observing.
[0041]
In this list display, one query content is displayed in the same row and the same extraction condition item is displayed in the same column in a matrix format, and further, a blank cell that does not have an extraction condition item value is distinguished from other existing cells. For example, by displaying a blank cell in a shaded manner or in a specific color, the list of extraction conditions can be improved.
[0042]
In addition, since the past execution time of each of the plurality of queries is also displayed in a list, the operator can set a guideline for the new continuous execution time after changing the query extraction condition.
[0043]
In addition, since the batch change process of the extraction condition item values can be performed for each extraction condition item, the extraction condition change operation is simplified and the change processing time can be shortened. Furthermore, convenience can be further enhanced by directly specifying an extraction condition cell to be changed and changing its value.
[0044]
In addition, when instructing continuous execution of a plurality of queries, it is possible to perform continuous execution using the extraction condition item value of the query already managed by the query management means.
[Brief description of the drawings]
FIG. 1 is an explanatory diagram illustrating an example of a display screen in a matrix format according to the present invention.
FIG. 2 is an explanatory diagram showing an overall configuration of a data extraction system according to the present invention.
FIG. 3 is an explanatory diagram showing an example of query definition according to the present invention.
FIG. 4 is an explanatory diagram showing an outline of a matrix management table according to the present invention.
FIG. 5 is an explanatory diagram showing an outline of a time management table according to the present invention.
FIG. 6 is an explanatory diagram showing a processing procedure when selecting and displaying a target query for data extraction according to the present invention.
FIG. 7 is an explanatory diagram showing a processing procedure when changing data extraction conditions according to the present invention.
FIG. 8 is an explanatory diagram showing a processing procedure when executing a query according to the present invention;
FIG. 9 is an explanatory diagram showing an overview of a computer system that reads and executes a program from a computer-readable recording medium according to the present invention.
FIG. 10 is an explanatory diagram illustrating a conventional query continuous execution definition (macro) example and a query editing example.
[Explanation of symbols]
1 and 2,
1: Individual change / batch change switch designation button 2: Edit button 3: Operation button 10: Query definition unit 20: Data extraction device 21: Query matrix management unit 22: Matrix management table 23: Query execution unit 24: Time management table 25: Result output unit 30: Time monitoring device 40: DBMS (database management system)
50: DB (database)
60: Input / output device such as a screen keyboard 70: Output device such as a screen or a printer

Claims (6)

In the data extraction system that extracts the execution result data of the query by searching the database based on the query composed of the search target name and the extraction condition item about the search target ,
A query management unit that acquires and stores the content data corresponding to each of the plurality of selected search target names from a query definition unit in which the content data of the query is described in units of the search target names; ,
A display unit that displays a list of the plurality of content data held in the inquiry management unit for each extraction condition item;
An input unit for executing a change input process of the content data listed in the display unit;
A data extraction system characterized by comprising: Provide a time management table that stores the time required for execution of each inquiry so far,
The inquiry management unit takes out the execution required time for each inquiry from the time management table and displays the time on the display unit.
The data extraction system according to claim 1. The inquiry management unit executes a change process of the extraction condition item data based on an individual / collective change instruction from the input unit for a plurality of the inquiries,
The data extraction system according to claim 1 or 2, characterized in that A recording medium storing a program used in a data extraction system for extracting the execution result data of a query by searching a database based on a query composed of a search target name and an extraction condition item for the search target In
The program is stored in a computer.
The content data corresponding to each of the plurality of selected search target names is acquired from the query definition unit in which the content data of the query is described in units of the search target names, and held in the query management unit Function and
A function of displaying a list of the plurality of content data held in the inquiry management unit for each extraction condition item on a display unit;
A function of executing a change process of the content data displayed in a list on the display unit based on an instruction from the input unit;
A computer-readable program recording medium characterized by the above. The program realizes, together with a computer, a function of taking out the required execution time from the time management table storing the required execution time until each inquiry and displaying it on the display unit .
The computer-readable program recording medium according to claim 4. The program realizes, together with a computer, a function for executing the change processing of the extraction condition item data in the inquiry management unit based on individual / collective change instructions from the input unit for a plurality of the inquiries. Is what
6. A computer-readable program recording medium according to claim 4 or 5.

Images