JPH1078968A - Statistic data base system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a statistic data base system which inputs an inquiry statement given from a user to decide a table that has a least accumulation value and also can accumulate information designated by an inquiry statement even if the information is not stored in a data base as it is. SOLUTION: The syntax of an inquiry statement sent from an application program is analyzed at a syntax analysis part 11, and it's decided whether the result of the analyzed inquiry statement can be obtained from a table. An optimization degree calculation part 14 calculates the optimization degree to show the optimum table to be retrieved out of a specific data base. Then a statement is generated in response to the table of the inquiry destination that is decided based on the calculated optimization degree and sent to a data base 10 where the data are actually stored for its retrieval. Then this retrieval result is sent back to the application program of a user.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a statistical database system in which information specified by a query sentence by a user in an application program by a user is totaled from a database and output to a user-side application program as a search result. The present invention also relates to a statistical database system that can aggregate data even if information specified by a query statement is not stored in the database as it is.

[0002]

2. Description of the Related Art Statistical data management is required for managing populations and monitoring traffic flowing through networks. When statistical data is managed, original data (for example, information about one call in the case of a telephone) is aggregated, so that data with a higher degree of aggregation (for example, in the case of a telephone, Tokyo and Deriving the amount of calls flowing to Osaka) is complicated. However, when dealing with a large amount of primitive data, this aggregation process consumes a great deal of computer resources. Therefore, in many cases, the data is not totalized every time the user inquires about the data, but is totalized in advance and stored in the secondary storage, and an inquiry is made on the data whose stored and totalized degree is high.

[0003] There is a concept of a data warehouse in which information in a company is aggregated, a shared database is constructed, and data derived through statistical processing or the like is used to make management decisions or the like. In a data warehouse, it is easy to accumulate a number of statistical data for the same object at various aggregation levels. For example, in the case of population management, data such as data for each country, data for each prefecture, and data for each city are mixed. Since the data warehouse is used by users other than the one who designed the data, in that case, the user has a hard time deciding which data to use to most efficiently obtain the necessary information.

Until now, database management systems having various models have been developed (hierarchical models, network models, relational models, object-oriented models). However, in order to retrieve the data, they had to explicitly write down all procedures for obtaining the desired data.

[0005] As a method of accessing without being aware of the data structure, an access interface based on a universal relationship has been studied. In this method, when only a condition and an attribute to be specified are specified, a table containing data is actually joined to derive desired data. However, although this method joins tables, it does not automatically aggregate data.

[0006] Also, as a technique for automatically summarizing,
There is a multidimensional database. However, this requires the user to know the data structure, and from multiple tables,
The user must decide which table is most efficient to use, and explicitly write it in the program.

[0007]

If the procedure for retrieving data must be explicitly written, it becomes difficult for the user to obtain desired information from a large amount of data whose structure is not well known.

[0008] When information of various aggregation levels (for example, by prefecture or city) is managed for a certain object (for example, population), which information is correct and efficient to determine from which information is obtained. You need to judge for yourself. And if you have coded your search logic in your application program, you need to change your application to access the new data when additional data is added that can be searched faster than previously searched. There is.

[0009] The present invention has been made in view of the above,
The purpose is to input a query sent from the user, determine the table that minimizes the total amount, and calculate the table even if the information specified in the query is not stored as it is in the database. It is to provide a statistical database system that can perform the above.

[0010]

To achieve the above object, according to the present invention, there is provided a syntax analyzing section for analyzing a syntax of a query sent from an application program, and a syntax analyzing section for analyzing the syntax. A derivability determining unit that determines whether a result of the inquiry can be obtained from the table; an optimality calculating unit that calculates an optimality indicating which table of which database is optimal to be searched; A query conversion unit that generates a query sentence according to the table of the inquiry destination determined based on the optimality calculated by the optimality calculation unit, and stores the converted query sentence in a database in which data is actually stored. A database access unit that sends and searches and receives the search results, and converts the query results depending on the database to the original query results A search result processing unit for shaping the data into a suitable form, a database registration processing unit for registering a database to be searched by the present system, and creating metadata that is data representing the format and contents of the included data, and a database to be searched The gist of the present invention is to have a metadata storage unit that stores the structure of the table, a database management system that manages the searched data, and a database that stores the searched data.

According to the first aspect of the present invention, the syntax of a query sent from an application program is analyzed, and it is determined whether or not the result of the analyzed query can be obtained from a table. Calculate the optimality that indicates which table in which database is best searched, generate a query sentence that is tailored to the table of the inquiry determined based on the calculated optimality, and generate the generated The inquiry sentence is sent to a database in which data is actually stored and searched, and the search result is passed to an application program on the user side.

The present invention according to claim 2 provides the invention according to claim 1.
In the described invention, the optimality calculating unit calculates, as the optimality, a totality indicating a small number of aggregations necessary for derivation and a selectivity which is a measure of a ratio of records selected for the entire table. Calculate and prioritize the aggregation degree as the optimal degree,
The gist of this is to select a table to be searched so as to minimize the total calculation amount.

According to the second aspect of the present invention, the totalization degree indicating the small amount of totalization necessary for derivation and the selectivity which is a measure of the ratio of records selected to the entire table are optimized. Calculated as a degree, giving priority to the degree of aggregation over the degree of selectivity,
Select the table to be searched so as to minimize the total calculation amount.

Further, the present invention according to claim 3 provides the invention according to claim 1.
In the invention described above, the derivability determining unit determines that the first condition that the attribute to be obtained is included in the table and the second condition that the value included in the condition clause of the query is included in the category list of the table The gist is to determine the derivability based on.

According to the third aspect of the present invention, it is checked that the attribute to be obtained is included in the table and that the value included in the conditional clause of the inquiry is included in the category list of the table. Then, the derivability is determined.

[0016]

Embodiments of the present invention will be described below with reference to the drawings.

FIG. 1 is a block diagram showing the configuration of a statistical database system according to one embodiment of the present invention. The statistical database system shown in the figure is a system that takes a query sentence specified by the user as input, determines the table with the least amount of aggregation, and automatically aggregates the data when aggregation is necessary. And a control unit 1 that controls the syntax of the query sent from the application program 23 by the interactive query processing unit 25 provided in the application program 23.
2. Derivability determining unit 13 that determines whether a result for the query sentence analyzed by syntax analysis unit 11 can be obtained from a table stored in the database, and which table in which database is optimal to search An optimality calculating unit 14 for calculating an optimality indicating whether there is a query, and a query converting unit 15 for generating a query sentence according to a query destination table determined based on the optimality calculated by the optimality calculating unit 14 Processing unit 2 consisting of
1, a query sentence converted by the query conversion unit 15 is sent to a database in which data is actually stored to be searched, and a database access unit 16 receiving the search result; And a database registration processing unit 18 that registers a database searched by the present system and creates metadata that is data representing the format and content of the data included. A metadata storage unit 19 for storing a table structure of a database to be searched, a database management system 20 for managing data to be searched, and a database 10 for storing data to be searched.

The statistical database system configured as described above is generally used by being called from an application program written by a user who wants to use the present system. The application program may be, for example, an inquiry response system having an interactive interface, or may be a general application program for performing data processing.

The statistical database system receives an inquiry section sent from an application program 23 in the form of a function call argument or the like by an interactive inquiry processing section 25 provided on the user side. The grammar of the query sentence is obtained by omitting the from clause from SQL, which is the query language of the relational database. Specifically, the format is select (list of attributes) where (conditional expression). The conditional expression has a grammar similar to SQL (attribute = “value”).

The statistical database system determines from the query which data the query processing unit 21 has to access, and temporarily converts the data into the SQL language as a search language for the relational database. After that, if necessary, the data is converted into a form of an inquiry to the database management system 20 for managing the database 10 in which data is actually stored. Various methods have been proposed for converting the SQL language into another language, and will be used here. Therefore, from here on, as the database 10 for actually storing data,
Let's assume a function database. The converted query is passed to the database management system 20 by the database access unit 16 to obtain a search result.
If necessary, the obtained search result is shaped so as to conform to the original query, and is returned to the application program 23 on the user side.

The data stored in the database 10 will be described. Since the database 10 is assumed to be a relational database, it manages tabular data (tables). The table has several attributes, but it is assumed that the table has two types of attributes: an attribute corresponding to an item for classifying a statistical value and an attribute for storing a statistical value. The former is called a classification attribute, and the latter is called a statistical attribute. The value that the classification attribute takes is called a category. FIG. 2 shows an example of a table stored in the database 10. The world population table in FIG.
And “sex” are classification attributes, and “population” is a statistical attribute. “Japan” which is a country value is an example of a category.

In order to perform the totaling automatically, it is assumed that the data in the table satisfies the constraint that "categories do not intersect semantically". The reason is that when we do the tally,
This is to prevent unnecessary counting of overlapping targets. For example, in the example of FIG. 2, the attribute of "country" is that its category is "Japan", "USA", etc., and because of population management, it is not Japanese and not American, so the country is We do not talk about the statistical attribute "population". On the other hand, it is assumed that there is a classification attribute of “region”, which takes a value such as “Japan” or “Kanagawa Prefecture”. In this case, the category is semantically intersected because it is possible to be a Japanese and a Kanagawa citizen. This is unsuitable because when counting is performed, counting is repeated. Such tables are not allowed.

The system stores information (metadata) on the tables and their values in the metadata storage unit 19 in order to select the table that minimizes the inquiry time at the time of execution. FIG. 3A shows a metadata management format and an example.

In the metadata management table, for each table actually stored in the database 10, the classification attribute and the statistical attribute of the table are managed, and the classification attribute has the value (called a category) of the attribute. Has a list of For example, in the case of the example in FIG.
It has two tables, the world population and the Japanese population. The world population table has classification attributes of country and gender, and manages all values. It also has one statistical attribute called population. The Japanese population table has classification attributes of country, gender, and prefecture, and the country manages values only for Japan. The Japanese population table does not have the attribute of country. However, the metadata management table has an attribute including one value of Japan. This means that although the database has an attribute of country, it is omitted because it all takes the value of Japan. Therefore, a classification attribute having only one category has no attribute in an actual database.
It is determined by the administrator of the statistical database system whether or not to register the classification attribute of country as metadata in the Japanese population table in this example.

As shown in FIG. 3B, the category management table manages a list of possible values for a certain classification attribute. For example, in the case of the example of FIG. 2, the classification attribute of country can take 100 categories as values, and the values are Japan, the United States, and so on.

In the aggregation function management table, as shown in FIG. 3C, an expression derived when aggregation is performed is managed for statistical attributes. This is because the derivation method differs depending on the statistical attribute. For example, for population
It is sufficient to simply calculate the sum, but in the case of the average age, it is necessary to calculate the average by weighting according to the population instead of simply calculating the average. This aggregation function management table has a format of a combination of a statistical attribute and its calculation formula, as shown in FIG.

Next, the details of the inquiry process will be described.

The query sentence is parsed by the parsing unit 11, and if there is a mistake in the syntax, an error value is set so that the application can refer to it. If there is no error, the syntax analysis result is passed to the query processing unit 21.

The processing in the inquiry processing section 21 will be described with reference to the flowchart shown in FIG. FIG.
First, one table t is picked up in order to perform the following processing for all the tables (step S11), and whether or not the table t remains or the maximum value Omax of the optimality calculated in the following processing is determined. It is determined whether or not it is the largest limit value (step S12), and all parameters are initialized (steps S11, S12).

Then, it is determined whether or not the table can be derived (step S13). If the derivation is not possible, the process returns to step S12 to repeat the same process. If the derivation is possible, the optimality Ot of the table t is calculated (step S14). Then, it is checked whether or not the calculated optimum value Ot is larger than the maximum value Omax of the optimum degree calculated so far (step S15). If not, the process returns to step S11 and the same processing is repeated for the next table. If it is larger than the maximum value Omax, this maximum value Omax is substituted for the optimality Ot (step S16), and step S1 is performed.
1, the same process is repeated for the next table. The above processing is repeated for all the tables, and if there is no table t to be further processed, the process proceeds to step S17.

In step S17, all tables t
Check whether it is not possible to derive. If all the tables cannot be derived, a response indicating that the data does not exist in the database is sent (step S18), and the process is terminated. In this case, an inquiry to the database is generated and executed for the table having the maximum degree of optimality calculated as described above (step S19), and the process ends.

The derivability is determined under the following two conditions.

(Condition 1) The attribute to be obtained is included in the table.

(Condition 2) The value included in the conditional clause of the inquiry is included in the category list of the table.

The above (condition 1) can be easily understood by comparing the list of attributes in the metadata management table with the list of required attributes. In order to obtain (condition 2), first, a list of category values to be obtained is obtained by applying the condition clause of the inquiry to the category management table. Next, it is sufficient to test whether the list is included in the list of category values in the metadata management table.

The optimality is calculated for the table for which the test of the derivability was OK. The optimality can be defined by a set of the totality and the selectivity.

The degree of aggregation is a numerical value indicating a small amount of aggregation required for derivation. The number of classification attributes selected in the inquiry is s, the number of classification attributes specified in the inquiry condition is c, Assuming that the number of classification attributes in the table is a, (totaling degree) = s / (ac). When the number of classification attributes selected in the inquiry matches the number of classification attributes in the table, there is no need to perform totaling, so the totaling degree is 1 at the maximum. The greater the number of classification attributes in the table than the number of classification attributes selected in the inquiry, the more aggregations are required, the lower the degree of aggregation.

The selectivity is a measure of the proportion of records selected for the entire table. Assuming that the number of categories specified in the query condition is C and the total number of categories included in the table is A for the classification attributes, the selectivity for one attribute is: (selectivity for one attribute) = C / A And the selectivity of the query for the entire table is s1, s2, ..., s
t, t2,..., t
Assuming that m, (selectivity) = (1-Π (1-ti)) Πsi (Π represents a product of n pieces and m pieces, respectively).

Since tabulation consumes much more computer resources than selection, tabulation is prioritized as the optimum degree. That is, when comparing the two optimalities (a1, b1) and (a2, b2) (ai is the aggregation degree, bi is the selectivity), a1
> A2, unconditionally (a1, b1)> (a2, b
2). When a1 = a2, if b1> b2,
It is assumed that (a1, b1)> (a2, b2).

When a table having the highest degree of optimality (here, t) is selected, an inquiry for the table is generated. It becomes the following inquiry.

[0041] Next, an example of an inquiry using the statistical database system of the present embodiment will be described with reference to FIG. As a table actually stored in the database, FIG.
It is assumed that there are two tables, a world population table and a Japanese population table shown in (a) and (b).

It is assumed that an inquiry such as "select gender, population where country = 'Japan'" representing the meaning of "determining the population of Japanese gender" is passed from the application program. First, the possibility of derivation is determined.

(Condition 1) The attribute to be obtained is included in the table.

This condition obviously satisfies both.

(Condition 2) The value included in the conditional clause of the inquiry is included in the category list of the table.

The condition clause of the inquiry is "country = 'Japan'"
However, by searching the metadata management table, the world population manages all the countries, and the Japanese population manages the country called "Japan", so both are satisfied.

Next, the calculation of the optimality is performed.

(Tally of world population) = s / (a−
c) = 1 / (2-1) = 1 (totality for Japanese population) = 1 / (2-0) = 0.5 (selectivity for world population) = C / A = 1/100 =
0.01 (selectivity with respect to the Japanese population) = 1/1 = 1 Since the count is prioritized over the selectivity, the world population is selected as the search target. Since the attribute selected in the query is equal to the attribute of t, there is no need to apply the aggregation function and no group by clause is required, so the final generated query statement is select gender, population from world population where country = 'Japan'.

Further, if only the table of Japanese population is provided, the attribute selected in the query is smaller than the attribute of t, and it is necessary to group the data. The query applied and generated is select gender, sum (population) from Japanese population group by gender.

Next, another example will be described with reference to FIG. In this case, as an inquiry, the average age of Japanese gender is obtained.

Select Gender, average age where country = 'Japan' In the derivation possibility determination, the world population table that does not have the average age attribute is excluded from the inquiry target. Since the attribute selected in the query is smaller than the attribute of t, it is necessary to group, apply the aggregate function registered in the aggregate function management table,

[Equation 1] select gender, sum (average age x population) / sum
(Population) from Japan population group by gender finally generate query.

As described above, in the present embodiment, even if the information specified by the query sentence by the user in the application program is not stored as it is, the information is totaled and derived, and the desired information is obtained as the search result. It can be passed to the application program on the user side. Further, when performing data search, it is possible to select a table to be searched so that the total calculation amount is minimized.
Further, the search sentence can be accessed from a SQL language, which is a query language of the relational database system, by a language having a form in which a form clause is omitted. In addition, totaling can be automatically performed by satisfying certain restrictions on tables accumulated in the database.

[0053]

As described above, according to the present invention,
Analyzes the syntax of the query sent from the application program, derives whether it is possible to obtain the result of the parsed query from the table, and determines which table in which database is best searched. Calculate the optimality shown, generate a query sentence according to the table of the inquiry destination determined based on the calculated optimality, and send the generated query sentence to the database where the data is actually stored. Since the search is performed and the search results are passed to the application program on the user side, it is possible for the user to access the database without being aware of the structure of the database, so that the optimum search can be selected and the access speed can be reduced. Due to the increase, it is necessary to change the application program even when adding data with higher aggregation No.

[Brief description of the drawings]

FIG. 1 is a block diagram illustrating a configuration of a statistical database system according to an embodiment of the present invention.

FIG. 2 is a diagram showing an example of data stored in a database of a statistical database system used in FIG.

FIG. 3 is a diagram showing a metadata management method and an example thereof in the statistical database system of FIG. 1;

FIG. 4 is a flowchart showing an inquiry process of the statistical database system of FIG. 1;

FIG. 5 is a diagram showing an example of an inquiry using the statistical database system of FIG. 1;

FIG. 6 is a diagram showing an example of an inquiry using the statistical database system of FIG. 1;

[Explanation of symbols]

 DESCRIPTION OF SYMBOLS 10 Database 11 Syntax analysis part 12 Control part 13 Derivability | determination part 14 Optimality calculation part 15 Query conversion part 16 Database access part 17 Search result processing part 18 Database registration processing part 19 Metadata storage part 20 Database management system 23 Application program 25 Interactive Query Processing Unit

Claims (3)

[Claims] 1. A syntax analyzer for analyzing the syntax of a query sent from an application program, and a derivability determiner for determining whether a result of the query analyzed by the syntax analyzer can be obtained from a table. And an optimality calculating unit for calculating an optimality indicating which table of which database is optimal to be searched, and a table of a query destination determined based on the optimality calculated by the optimality calculating unit A query conversion unit that generates a query sentence according to a query, a database access unit that receives the search result by sending the converted query sentence to a database in which data is actually stored, and a query dependent on the database A search result processing unit for shaping the result into a form in accordance with the result of the original query; A database registration processing unit that registers a database and creates metadata that is data representing the format and contents of the included data, a metadata storage unit that stores the structure of a database table to be searched, A statistical database system comprising: a database management system for managing; and a database for storing data to be searched. 2. The optimality calculating unit calculates, as the optimality, an aggregation degree indicating a small number of aggregations necessary for derivation and a selectivity which is a measure of a ratio of records selected for the entire table. 2. The statistical database system according to claim 1, wherein the calculation is performed, and the tallying degree is prioritized as the optimum degree, and a table to be searched is selected so as to minimize the tallying calculation amount. 3. The derivability determining unit includes a first condition that an attribute to be obtained is included in the table and a second condition that a value included in a condition clause of an inquiry is included in a category list of the table. The statistical database system according to claim 1, wherein the determination of the derivability is performed based on the following.