JPH08329101A - Data base system - Google Patents

Abstract

PURPOSE: To quickly execute totalizing processing for items to be frequently used by providing this data base system with a data base storing part, a data base definition information storing part and a retrieval route determining means. CONSTITUTION: At first which index item in an unsummarized data table (sales data 1 table 211) is used for a summarized data table is inputted by an operator and the inputted information is stored in a data base definition information file 220. Then the summarized data table is prepared based upon the file 220. At the time of receiving a data base retrieval instruction from an application program, a keyboard or the like, a retrieval route determining means 13 refers to the file 220 by using a specified index item name as a key and determines a route to be retrieved most quickly. Then a retrieving means 14 retrieves the data base based upon the determined retrieval route.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a process for obtaining a total value (for example, a sum) of numerical items for a specified index item from a data table having a plurality of index items and numerical items in a database system.

[0002]

2. Description of the Related Art When obtaining a total value of data from a data table having a plurality of index items and numerical items as described above, the same search is performed again from one data table even if the data is frequently aggregated. Was.

[0003]

Since the operator knows in advance which items are frequently used in the totaling process, it is necessary to perform the totalizing process of frequently used items at high speed. Is desired.

[0004]

[Means for Solving the Problems] The above-mentioned problems are solved by using an arbitrary index item of a plurality of index items of the data table for an aggregation source data table composed of a plurality of index items and numerical data items. A database storage unit that stores aggregation destination data tables with different degrees, a database definition information storage unit that stores the relationship between index items in the plurality of data tables, and a search request for the numerical data item with the index item as a key. On the other hand, the problem is solved by the search route determining means for obtaining the search route of the data table stored in the database storage unit based on the database definition information storage unit.

[0005]

[Function] A data table having different degrees of aggregation is created in advance using frequently used index items as keys, and a plurality of tables having different degrees of aggregation and their definition information are stored as a table (file), The search route determining means searches the database for the optimum search route based on this definition information.

[0006]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS FIG. 1 is a diagram showing the configuration of an embodiment of the present invention. Reference numeral 1 is a processing device, 3 is an input device, and 4 is a display device. Reference numeral 2 denotes an external storage device that stores a database and the like, and stores an organization master table 200, a sales data 1 table 211, a sales data 2 table 212, a sales data table 213, and a database definition information file 220. . Organization master table 200
The definition information of the organization is stored in. Sales data 1
The table 211 is a data table including index items and numerical items of organization information, and the sales data 2 table 21.
2 and the sales data 3 table 213 are data tables created based on the sales data 1 table 211 and having different degrees of integration. The database definition information file 220 stores the relationship with each index item and the relationship with each sales data table.

Reference numeral 11 is a means for creating / updating the database definition information file, 12 is an aggregate data generating means for producing tables with different degrees of integration in the external storage device 2, and 13 is a database definition information file. Reference numeral 14 is a search route determining means for obtaining an optimum search route based on the information stored in 220, and 14 is a database (sales data table or the like) based on the search route information designated by the search route determining means 13. Is a search means for searching. Reference numeral 5 is an application program for accessing a database in the external storage device 2. Accessing the database is a request for a search using the item name of a required data item.
The database system of the present embodiment receives a request for tabulation from an application using an item name as a key, obtains a data table and a processing operation to be processed in database access from the item name, and then applies the corresponding SQL. Generate a command and use the SQL command to access the database. Then, the result is notified to the application program requesting the tabulation.

FIG. 2 is a diagram for explaining the data format of the organization master / sales data table. Reference numeral 211 denotes a sales data 1 table, which is the sales data of the aggregation source in FIG. 2, in which sales data for each person in charge is stored. In addition to each person in charge and sales data, identification information of the section, branch, or branch to which the person in charge belongs is stored. It is stored in the same record of sales data. Responsible,
As the relationship between the division, the branch and the branch is stored in the organization master table 200, the branch, the branch, the section and the person in charge have a hierarchical relationship. That is, the branch office "101" has branch offices "2001" and "2002", and the branch office "2001".
Indicates that there are sections “301” and “302”, and that section “301” has persons “401”, “402”, and “403” in charge.

Further, 212 and 213 are sales data 1
It is a sales data 2 table and a sales data 3 table in which the table 211 is integrated. Sales data 2 table 212
Is an aggregation source of the sales data 1 table 211 for each branch office and each section. The aggregation destination data table 213 is aggregated for each branch and each section.

FIG. 3 shows an operation flow in one embodiment of the present invention. That is, first, the operator is made to input which index item is used from the data table before aggregation (sales data 1 table 211) to use the aggregated data table, and the information entered by the operator is used as the database definition information. It is stored in the file 220 (S41). In this case, the operator selects a frequently used index item.

Next, an integrated data table is created based on the created database definition information file 220 (S42). Also, when the structure of the data table before aggregation is changed, the database definition information file is updated and the aggregated data table is created again, as in S41. Next, the application program,
Receives database search instructions from the keyboard. The database search instruction specifies which numerical item corresponding to which index item name in the data table before aggregation is desired (S43). Using the designated index item name as a key, the database definition information file is referenced to determine the earliest possible search route among the possible search routes (S44). Then, the database is searched by the search route determined in S44 (S45).

FIG. 6 shows an organization master table 20 shown in FIG.
0 and data table (sales data 1 data table 21
1, sales data 2 data table 212, sales data 3
This is the data format of the database definition information file 220 that stores the relationship with the data table 213). The first column from the left in FIG. 6 stores the table names. Two
The second column stores the number of rows in each table (other than the organization master table), and the pre-aggregation data table 211 has 75 rows.
00 rows (records), data table 212 after aggregation
And 213 indicate that there are 250 lines. This field is automatically updated when the data is created or updated.

The third and subsequent columns are information for each item in each table, the third shows the item name and the fourth shows whether or not the item of the organization master table has lower information. The fifth “column” indicates the position of each item in each table. The sixth column of “route destination” indicates from which item of the organization master table the item can be indirectly obtained. For example, branch data may be obtained from branch information (“C2”). It is shown.

The 7th "link destination" column stores table names and column names that can be used when the data (sales data) for that item is extracted. For example, the first line shows each branch office. It is shown that the sales data can be extracted based on the column C1 of the data table before aggregation. The information in this column is created by comparing the item names of sales data with the item names of the organization master table and storing the corresponding table name and column name when there is the same item. . Further, the operator may set it by himself instead of setting it automatically.

The eighth "Type" column indicates the data type of each data item, "X" indicates that it is a character item, and "9" indicates that it is a numerical item. The ninth column stores the number of digits of each data item. For example, the branch office information on the first line indicates that it is three-digit data. For the definition of the data table after aggregation,
It is stored when the operator uses the screen shown in FIG. 7 to define for which index item the data table of the aggregation destination is created.

FIG. 7 shows the designation screen in the process of letting the operator designate which index item is used to create the aggregated data table. Items that can be specified are displayed corresponding to the data table of the aggregation source stored in the database definition information file 220 shown in FIG. 6, and if there is an already-defined aggregate data table, the items are specified. “○” is added and displayed.

The horizontal column in the drawing indicates the identification number of the aggregated data table, and "01" indicates the sales data 1 table 211 which is the aggregation source data table.
“02” and “03” respectively indicate the sales data 2 table 212 and the sales data 3 table 213 which are the data tables of the aggregation destination. For example, “02” indicates that aggregated data of “sales amount” is created for the items “branch” and “section”. The contents specified here are stored in the database definition information file 220 of FIG.

The data table creation process (S42 of FIG. 3) based on the database definition information file 220 created or updated by the operator's designation will be described with reference to the data table creation process flowchart of FIG. . If it is determined whether or not there is definition information (in the database definition information file 220) of the created or updated data table, the following processing is performed. The definition information of the created or updated data table is taken out, that is, the information of which item should be totaled is taken out. Then, the data table (sales data 1 table 2
The records in 11) are sorted, the sorted records are read in order, and the sales data of the records having the same data in the same item are summed up, so that the records in the new data table (the data table of the aggregation destination) are sorted. Create it.

Next, the processing after S43 in FIG. 3 will be described. A case of obtaining sales data for each branch will be described. When a request for sales data for each branch is received, a searchable route is calculated with reference to the database definition information file 220 shown in detail in FIG. First, in the database definition information file 220, a record that defines a branch is obtained from records related to the organization master table.

Then, two search routes (“sales data 1 / C2” and “sales data 3 / C1”) are obtained from the column of “link destination table name / column name” in this record. From this, the SQL commands of FIGS. 8A and 8B are obtained. Next, the item name that can be indirectly obtained from the "route destination" field is obtained. "Circle destination" is "C
The item "Division" is selected because there is "3", and three search routes ("Sales data 1 / C3" and "Sales data 2 /" are selected from the "Link destination table name / Column name" column of the selected item. C2 "and" sales data 3 / C2 ").
From this, the SQL commands of (c), (d), and (e) of FIG. 8 are obtained.

Furthermore, an item name that can be indirectly obtained is obtained from the "route destination" column of "section". Since there is "C4" in the "route destination", the "charge" item is selected,
"Link destination table name / column name" of this selected item
One search route (“sales data 1”) is obtained from the column. From this, the SQL command of FIG. 8 (f) is obtained.

Further, in the database definition information file 220, a record that matches the table name and column name described in the "link destination table name / column name" column of the record defining the branch in the records related to the organization master table is stored in the database definition information file 220. If you search from the inside, both item names match. In other words, if they match, it is not necessary to use the organization master,
The aggregated data can be extracted only from the sales data table. In this example, from (a) and (b) of FIG.
(g) and (h) are obtained.

Of the obtained search routes, the optimum route is determined according to the following criteria. The following are listed in order of increasing priority of the criteria. (However, the priority can be changed according to the structure of the database, the amount of data, the characteristics of the code, etc., so that the characteristics of the database can be utilized.) (1) Direct search.

(2) The number of links between tables is small (3) The number of columns in the table is small (4) The table with a small number of rows (5) The table with a short row length (6) The capacity of the table is small (7) The table Uses a table with few key items above (8) Uses a table with a lower number of key items below the lowest master item among the master items used. Each standard will be described below. (1) Direct search Direct search means that it is possible to search using only the data table without using the organization master. In this example of FIG.
(g) and (h) are applicable. (2) The number of links between tables is small For example, “→” in “Organization master / branch → sales data 1 / branch” in FIG. 8 (a) corresponds to the link, and in FIG. 5 (a),
(b) is one, (c), (d), (e) is two, (f) is three
, (G), (h) becomes 0, and (g) selected in (1)
In (h), the number of links cannot be narrowed down to one. (3) The number of columns in the table is small. It can be obtained by referring to the item name column of the database definition information file 220 and determining how many items are in each table. In this example, (g) has 5 columns and (h) has 3, so (h) is selected as the search route. (4) Table with a small number of rows Database definition information file 2
See the row number column of 20 to see how many rows each table is composed of. Sales data 1 table 211
In the case of, there are 7500 rows, and sales data 2 table 2
In the case of 12, there are 250 rows. (5) Short row length table The column of the number of digits of the database definition information file 220 is referred to, and the total number of digits of each item in each table is calculated. (6) Table capacity is small The table capacity is obtained by multiplying the results of (4) and (5). (7) A table with a small number of key items on the table is used. Further, in the example of FIG. 5, there is a plurality of key items (index items), but there is only one numerical item. In consideration of a certain case, this is the number of key items, while (3) is the total number of items. (8) Among the master items used, a table is used in which the number of lower key items is smaller than that of the master item located at the lowest position. When it is found that multiple aggregation tables can be the search target, the smaller the number of key items lower than the master item used (branch in this example), the higher the degree of aggregation (the number of records on the table is small). Since there is a lot of possibility, the table is used for searching.

[0025]

As described above, it is not necessary to modify the conventional application program by only modifying the file that defines the database structure, and the data extraction can be performed with the optimum search route.

[Brief description of drawings]

FIG. 1 is a configuration diagram of an embodiment of the present invention.

[Figure 2] Organization master / sales data table data format

FIG. 3 is an operation flow in an embodiment of the present invention.

FIG. 4 is a flowchart of a data table creation process.

FIG. 5 is a flowchart of search route determination processing.

FIG. 6 Data format of database definition information file

[Fig. 7] Example of screen for specifying aggregation table

FIG. 8 is a diagram illustrating a generated SQL command.

[Explanation of symbols]

 1 Processing Device 2 External Storage Device 3 Input Device 4 Display Device 5 Application Program 11 Database Definition Means 12 Aggregated Data Creation Means 13 Search Route Determining Means 14 Search Means 200 Organization Master Table 211 Sales Data 1 Table 212 Sales Data 2 Table 213 Sales Data 3 table 220 database definition information file

Claims (3)

[Claims] 1. An aggregation source data table composed of a plurality of index items and numerical data items, and at least one or more index items among the plurality of index items of the aggregation source data table. A database storage unit that stores aggregation destination data tables having different degrees of integration, a database definition information storage unit that stores the relationship between a plurality of data tables stored in the database storage unit and index items, and the index items as keys In response to the search request for the numerical data item, a search route determining means for determining a search route of the data table stored in the database storage unit is provided based on the database definition information storage unit. Database system to do. 2. The database definition information storage unit stores the relationship between the plurality of index items, and based on the information,
The database system according to claim 1, wherein the search route determining means determines a search route. 3. The aggregation destination data table is generated based on the designation of the aggregation source data and its index item from an input device, and the configuration information of the generated aggregation destination data table is registered in the database definition storage unit. 3. The database system according to claim 1, further comprising an aggregated data generation unit for performing the above.