JP3552339B2 - Database system - Google Patents

Description

[0001]
[Industrial applications]
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]
[Prior art]
As described above, when calculating a total value of data from a data table having a plurality of index items and numerical values, the same search is executed again from one data table even in a frequently performed totaling process.
[0003]
[Problems to be solved by the invention]
Since the operator knows in advance which items are frequently used in the tallying process, it is desired to perform the tallying process for frequently used items at high speed.
[0004]
[Means for Solving the Problems]
The above-described problem is that, for an aggregation source data table including a plurality of index items and numerical data items, an aggregation destination data table having a different degree of aggregation is stored using an arbitrary index item among the plurality of index items of the data table. A database definition information storage unit that stores a relationship between index items in the plurality of data tables; and a database definition information storage unit that responds to a search request for the numerical data item using the index item as a key. Search route determining means for obtaining a search route of the data table stored in the database storage unit based on the search route, and a search means for searching the data table based on the search route information designated by the search route determining means. Will be resolved.

[0005]
[Action]
Data tables with different degrees of aggregation are created in advance using frequently used index items as keys, and a plurality of tables with different degrees of aggregation and their definition information are stored as a table (file) to determine a search route. The means searches the database for the optimum search route based on the definition information.
[0006]
【Example】
FIG. 1 is a diagram showing the configuration of an embodiment of the present invention. 1 is a processing device, 3 is an input device, and 4 is a display device. Reference numeral 2 denotes an external storage device storing a database and the like, in which 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 are stored. . The organization master table 200 stores organization definition information. The sales data 1 table 211 is a data table including index items and numerical values of organization information. The sales data 2 table 212 and the sales data 3 table 213 are data created based on the sales data 1 table 211 and having different degrees of aggregation. It is a table. The database definition information file 220 stores the relationship between each index item and each sales data table.
[0007]
Reference numeral 11 denotes a unit for creating / updating the database definition information file, reference numeral 12 denotes an aggregated data generation unit for generating tables having different degrees of aggregation in the external storage device 2, and reference numeral 13 denotes a unit stored in the database definition information file 220. Search route determining means for finding an optimal search route based on the information entered is searched for in a database (sales data table or the like) based on the search route information specified by the search route determining means 13. Search means. Reference numeral 5 denotes an application program for accessing a database in the external storage device 2, and for accessing the database, a search request is made using an item name of a necessary data item. The database system according to the present embodiment receives a request for aggregation using an item name as a key from an application, obtains a data table to be processed in a database access and a processing operation from the item name, and obtains a corresponding SQL. A command is generated, and the database is accessed using the SQL command. Then, the result is notified to the application program requesting the tally.
[0008]
FIG. 2 is a diagram illustrating the data format of the organization master / sales data table. Reference numeral 211 denotes a sales data 1 table which is sales data of the aggregation source in FIG. 2, and stores sales data for each charge. Stored in the same record of sales data. As the relationship among the responsible, section, branch, and branch is stored in the organization master table 200, the branch, branch, section, and responsible are in a hierarchical relationship. That is, the branch office “101” has branches “2001” and “2002”, the branch “2001” has sections “301” and “302”, and the section “301” is responsible for “401” and “402”. , "403".
[0009]
Reference numerals 212 and 213 denote a sales data 2 table and a sales data 3 table in which the sales data 1 table 211 is consolidated. The sales data 2 table 212 is a collection of the aggregation source sales data 1 table 211 for each branch office and each section. The aggregation destination data table 213 is an aggregation for each branch and each section.
[0010]
FIG. 3 shows an operation flow in one embodiment of the present invention. That is, first, the operator is required to input which index item is to be used from the data table (sales data 1 table 211) before aggregation to use the aggregated data table, and the information input by the operator is entered into the database definition information. It is stored in the file 220 (S41). In this case, the operator selects a frequently used index item.
[0011]
Next, an aggregate data table is created based on the created database definition information file 220 (S42). Also, when a change occurs in the structure of the data table before aggregation, similarly to S41, the database definition information file is updated, and the aggregated data table is created again.
Next, a database search instruction from an application program or a keyboard is received. The database search instruction specifies which numerical item corresponding to which index item name in the pre-aggregation data table is desired (S43). Using the specified index item name as a key, the database definition information file is referred to, and a search route that can be searched earliest among the possible search routes is determined (S44). Then, the database is searched using the search route determined in S44 (S45).
[0012]
FIG. 6 shows the data of the database definition information file 220 storing the relationship between the organization master table 200 and the data tables (sales data 1 data table 211, sales data 2 data table 212, sales data 3 data table 213) in FIG. Format.
The first column from the left in FIG. 6 stores the name of the table. The second column stores the number of rows of each table (other than the organization master table), indicating that the pre-aggregation data table 211 has 7,500 rows (records) and the post-aggregation data tables 212 and 213 have 250 rows. I have. This column is automatically updated when data is created or data is updated.
[0013]
The third and subsequent columns are information for each item in each table, the third indicates the item name, and the fourth indicates whether or not the item in the organization master table has lower-level information. The fifth “column” indicates the position of each item in each table.
The sixth “transit-to” column indicates from which item of the organization master table the item can be obtained indirectly. For example, data of a branch office may be obtained from branch information (“C2”). It is shown.
[0014]
The seventh “link destination” column stores table names and column names that can be used when extracting data (sales data) of the item. For example, the first line contains sales data for each branch office. This indicates that extraction can be performed based on the column C1 of the pre-aggregation data table. The information in this column is created by comparing the item name of the sales data with the item name of the organization master table, and storing the corresponding table name and column name when there is the same item. . Further, instead of the automatic setting, the operator may set the information by himself.
[0015]
The eighth “type” column indicates the data type of each data item, “X” indicates a character item, and “9” indicates a numerical item. The ninth column stores the number of digits of each data item, and indicates, for example, that the branch information in the first row is three-digit data.
The definition of the post-aggregation data table is stored when the operator uses the screen shown in FIG. 7 to define which index item should be used to create the aggregation destination data table.
[0016]
FIG. 7 shows a designation screen in a process of causing the operator to designate which index item is used to create the aggregated data table. Items that can be specified are displayed corresponding to the aggregation source data table stored in the database definition information file 220 shown in FIG. 6, and if there is an already defined aggregation data table, the specified item is displayed. "O" is displayed.
[0017]
The horizontal column in the figure indicates the identification number of the aggregated data table, “01” indicates the sales data 1 table 211 that is the data table of the aggregation source, and “02” and “03” indicate A sales data 2 table 212 and a sales data 3 table 213, which are data tables at the aggregation destination, are shown. For example, “02” indicates that consolidated data of “sales amount” is created in the items of “branch office” and “section”. The contents specified here are stored in the database definition information file 220 of FIG.
[0018]
The data table creation process (S42 in FIG. 3) will be described with reference to the data table creation process flowchart in FIG. 4 based on the database definition information file 220 created or updated by the operator's designation. If it is determined whether 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 extracted, that is, information on which items should be totaled. Then, the records in the data table of the aggregation source (sales data 1 table 211) are sorted by the extracted items, the sorted records are sequentially read, and the sum of the sales data of the records having the same data in the same items is calculated. Then, records of a new data table (data table of an aggregation destination) are created.
[0019]
Next, processing after S43 in FIG. 3 will be described. A case in which sales data for each branch is obtained 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 related to the organization master table that defines a branch is obtained.
[0020]
Then, two search routes (“sales data 1 / C2” and “sales data 3 / C1”) are obtained from the “link destination table name / column name” column in this record. From this, the SQL commands shown in FIGS. 8A and 8B are obtained.
Next, an item name that can be obtained indirectly is obtained from the “transit destination” column. The item “section” is selected because “C3” is included in the “transit destination”, and three search routes (“sales data 1 / C3”) are selected from the “link destination table name / column name” column of the selected item. , “Sales data 2 / C2” and “sales data 3 / C2”). From this, the SQL commands (c), (d), and (e) of FIG. 8 are obtained.
[0021]
Further, an item name that can be obtained indirectly is obtained from the column of “section” of “section”. Since "C4" is displayed in "Destination", the item "Responsible" is selected, and one search route ("Sales data 1") is selected from the "Link destination table name / column name" column of the selected item. Ask for. From this, the SQL command shown in FIG. 8 (f) is obtained.
[0022]
Further, the database definition information file 220 is searched for a record corresponding to the organization master table that matches the table name and column name described in the “link destination table name / column name” column of the record defining the branch. And both item names match. That is, if they match, it is not necessary to use the organization master, so that the total data can be extracted only from the sales data table. In this example, (g) and (h) in FIG. 8 are obtained from (a) and (b) in FIG.
[0023]
An optimum route is determined from the obtained search routes based on the following criteria.
The following are arranged in descending order of reference priority. (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 used.)
(1) Direct search.
[0024]
(2) The number of links between tables is small (3) The number of columns in the table is small (4) The table with few rows (5) The table with short row length (6) The capacity of the table is small (7) The key on the table Uses a table with a small number of items (8) Uses a table with a small number of key items below the lowest master item among the master items used.
Hereinafter, each standard will be described.
(1) The direct search, which is a direct search, means whether or not a search can be performed using only the data table without using the organization master. In the example of FIG. 5, (g) and (h) correspond.
(2) The number of links between tables is small. For example, “→” in “organization master / branch → sales data 1 / branch” in FIG. 8A corresponds to the link, and FIG. b) is one, (c), (d), and (e) are two, (f) is three, and (g) and (h) are zero. In (g) and (h), the number of links cannot be reduced to one.
(3) The number of items in each table can be determined by referring to the item name column of the database definition information file 220 having a small number of columns in the table. In this example, since the column of (g) is 5 and (h) is 3, (h) is selected as the search route.
(4) Tables with a Small Number of Rows Also for this criterion, refer to the row number row of the database definition information file 220 to see how many rows each table is composed of. The sales data 1 table 211 has 7,500 rows, and the sales data 2 table 212 has 250 rows.
(5) Refer to the column of the number of digits in the table database definition information file 220 having a short line length, and calculate the total number of digits of each item in each table.
(6) The capacity of the table is obtained by multiplying the results of (4) and (5) where the capacity of the table is small.
(7) A table with a small number of key items is used. In the example of FIG. 5, there are a plurality of key items (index items) but only one numerical item. Considering a certain case, (3) is the total number of items, whereas this is the number of key items.
(8) A table is used in which the number of lower-level key items is lower than that of the lowest-ranked master item.
When it is found that multiple aggregation tables can be searched, the lower the number of key items below the used master item (in this example, the branch), the higher the degree of aggregation (the number of records on the table is small) Since there is a lot of possibility, search is performed using the table.
[0025]
【The invention's effect】
As described above, it is not necessary to modify the conventional application program only by modifying the file defining the database structure, and the data can be extracted with the optimal search route.
[Brief description of the drawings]
FIG. 1 is a block diagram of an embodiment of the present invention. FIG. 2 is a data format of an organization master / sales data table. FIG. 3 is an operation flow in an embodiment of the present invention. FIG. 5 is a flowchart of a search route determination process. FIG. 6 is a data format of a database definition information file. FIG. 7 is an example of a designation screen of an aggregation table. FIG. 8 is a diagram for explaining generated SQL commands.
REFERENCE SIGNS LIST 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 determination 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)

An aggregation source data table including a plurality of index items and numeric data items, and at least one or more index items among the plurality of index items of the aggregation source data table, and aggregation having a different aggregation degree from the aggregation source data table. A database storage unit for storing a destination data table;
A database definition information storage unit that stores a relationship between a plurality of data tables and index items stored in the database storage unit,
In response to a search request for the numerical data item using the index item as a key, a search for finding an optimal search route among search routes of a data table stored in the database storage unit based on the database definition information storage unit Route determination means and
A search unit for searching a data table based on the search route information specified by the search route determination unit . 2. The database system according to claim 1, wherein the database definition information storage unit stores a relationship between the plurality of index items, and the search route determination unit determines a search route based on the information. Aggregate data generation means for generating the aggregation destination data table based on the specification of the aggregation source data and its index item from the input device, and registering the configuration information of the generated aggregation destination data table in the database definition storage unit The database system according to claim 1, further comprising:

Images