JP2006053798A - Query deriving method - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a method for deriving a sub-database selected by a user without clear awareness by the user and a query creating an approximate sub-database. <P>SOLUTION: A query is performed for all the items of an original database, a single item query satisfying a predetermined condition is detected and allocated as a root node; using the root node as a starting point, the single item query satisfying the predetermined condition is recursively performed till when a terminal condition is detected; a tree structure is created; for all the paths of the completed tree structure, a logical product is produced from the root node to a terminal node; and the logical sum of all the logical products is made as a query deriving an approximate database. <P>COPYRIGHT: (C)2006,JPO&NCIPI

Description

  The present invention relates to a query (hereinafter referred to as a query) for obtaining a sub-database extracted from the original database consisting of a large amount of data having a plurality of attribute values without the user's explicit awareness and a sub-database approximated by the user. ).

  Data mining is a technology that finds useful information from an enormous amount of data. In recent years, data for customers has been constantly input due to the introduction of POS at retail stores, infrastructure development, and development of database technology, and a huge amount of data has been accumulated. Obtaining useful knowledge from this enormous amount of data is not easy, but obtaining useful knowledge from this enormous amount of data leads to great benefits.

  As a typical method of data mining, 1) classification (classifcation), that is, regularity that classifies existing data into several predetermined classes is discovered, and unknown data is discriminated based on it 2) Clustering, that is, identifying a set of a finite number of categories that describe the data. A method of automatically classifying into groups, 3) Association, that is, a method of learning correlations between relevance extraction data attributes and searching for combination patterns of strongly related data, for example, finding association rules ,and so on.

However, any of these methods requires preprocessing of selecting an appropriate variable from a large number of variables. Moreover, there is a problem that the characteristic patterns obtained by these methods cannot be utilized as effective knowledge as they are.
“Masayuki Kawano:“ Current Status and Trends of Knowledge Discovery from Databases ”, Journal of Artificial Intelligence, VoL12, No, 4, PP497-504 (1997)” `` Blake, C, L, & Merz, CJ ,,, UClRepository of ma £ hineleamingdatabases, httpl // www, ics, ucLedu / mleam / MLRepository, htm1, lrvine, CAlUniversityofCa1ifomia, DepartmentoflnformationandComputerScience (1998) '' As a rule generation method, a technique for creating a correlation rule useful for a user by generating only a correlation rule having a correlation coefficient equal to or greater than a predetermined value based on a correlation coefficient between attributes included in a database is disclosed. Has been.

However, this technique is intended to extract only the relationship between attributes and make a rule. Therefore, for example, when the user's intention is formed by the values of two uncorrelated attributes, such a technique is used. However, there is a problem that the user's intention cannot be estimated. That is, in the technique disclosed in Patent Document 1, it is possible to infer the user intention that a person who is tall and has a light weight is looking for, even if a tall person can rule the tendency that he / she is heavy. I can't.
“Data Mining Device and Data Mining Method: JP 2001-265596 A”

  Therefore, an object of the present invention is to provide a query derivation method for deriving a query necessary for obtaining a sub database that is selected from a vast number of original databases, for example, without the user's explicit awareness, and a sub database that is approximated. There is to do.

The present invention relates to an original database composed of tuples having a plurality of attributes, that is, a set of tables extracted from a table (table) having a plurality of attributes as rows and data as columns, in other words, in other words, Provided is a query derivation method for deriving a query necessary for generating a database that approximates a sub database obtained by executing an unknown query (query) in the original database.

  In the query derivation method, all terms in the sub-database, that is, columns of each attribute are defined as unary terms, and a query (unary query) is executed for every term in the sub-database. Thereby, a unary query that satisfies a predetermined condition is detected, and the unary query is assigned as a root node. Here, the predetermined condition is that a random database obtained by randomly extracting n tuples from an original database and a unary query database generated by executing a unary query on the original database are defined as: In the relationship, the probability of the same relevance rate is equal to or less than a predetermined risk rate, and the recall is the highest among the single-term queries executed for all terms.

  A unary query that satisfies the predetermined condition is determined, and a unary query that satisfies the predetermined condition is executed on the sub-database, thereby generating an affirmative database. Further, a negative unary query that negates a query of a unary query that satisfies the predetermined condition is similarly executed on the sub-database, thereby generating a negative database.

  Next, the positive database or the negative database is regarded as the original database, and an overlapping part between the positive database and the sub database is set as the sub database, or an overlapping part between the negative database and the sub database is set as the overlapping part. Considering the sub database, the affirmative query or the negative query is executed for the overlapping portion of the negative database and the sub database.

  As a result, a positive unary query or negative unary query that satisfies the predetermined condition is determined, and a tree structure is created so that the positive unary query or negative unary query becomes a node that is a binary tree from the root node.

  This is recursively executed until a unary query that satisfies the predetermined condition cannot be detected, thereby completing the tree structure.

  Next, the logical product of all the unary queries that pass through the path from the root node to the terminal node is created, and all the logical sums of the logical products obtained for each path are derived as the approximate database query. .

  In the following, an embodiment for deriving a query for extracting an approximate sub-database that approximates a sub-database selected by the user from a vast number of original databases without a clear awareness will be described in detail.

1. Definition of terms The definitions of terms used in this specification are as follows.

  A table is a database and is a table as shown in FIG. A table is a set of tuples.

  A tuple indicates a row of the table shown in FIG. 1, and is a collection of attribute values for attributes.

The attributes are the price, size, color, etc. shown in FIG. 1, and include the characteristics and properties of the thing.

  The attribute values are the attribute values shown in FIG. 1, and are, for example, the attribute “price” “high”, the attribute “size” “small”, and the attribute “color” “red”.

  A term is, for example, a “size” column shown in FIG. 1, and one column is a single term.

  When a table shown in FIG. 1 is given, a unary query has a single attribute such as “search for tuples whose“ attribute ”is price: A and“ attribute value ”is normal: a”. In this specification, the unary query is described as (A: a).

  A node refers to a branch point of each tree in the tree structure.

  A path refers to a path connecting nodes in a tree structure.

  A negative query is a query that says “Find a tuple whose attribute A is not a”. In this specification, the description of (A: ≠ a) is a unary query (A: a), and this unary query is affirmed. It represents a negative unary query, also called a unary query.

  The product format (Conjunction) is an expression describing the product of two unary queries (A: a) and (B: b) as (A: a) × (B: b).

  The sum form (Disjunction) is an expression describing the sum of two unary queries (A: a) and (B: b) as (A: a) + (B: b).

The recall is a unary query (X) for the table R given the original database (hereinafter, table R) and the sub-table of the table R (hereinafter, table S) as shown in FIG. : x) is applied to derive a subset S _x of the table R, the relationship between S _x and S is as shown on the left side of FIG. The ratio of the portion (S∩S _x ) where the sub database S and the sub database S _x overlap with the sub database S is the recall rate. Recall is a measure of whether S _x is approximated S, recall (Recall) _{is, | S∩S x | / | S} | is represented by the reproduction rate much of S by S _x Indicates whether the cover was completed.

The precision is a precision ratio = | S∩S _x | / | S _x |, and the precision is an index indicating the ratio of S that can be approximated by S _x .

  In this specification, when | X | is described, it represents the number of elements included in the set X.

  The risk factor is a probability serving as a criterion for determining whether or not to reject the null hypothesis in a statistical hypothesis test (hereinafter referred to as test). A very small probability that the event of the test is considered to rarely occur is adopted and is also called a significance level. In this specification, the risk rate is assumed to be 1% to 10%, but is not limited to this. What percentage of the risk rate is used depending on the type of target table R and the precision of the query to be derived.

  The test is an act of making a hypothesis for an event to be examined and obtaining an occurrence probability when the hypothesis is correct by using a statistical quantity based on an appropriate statistical distribution. Whether or not the occurrence probability rarely occurs is judged by comparing with a predetermined risk rate. If the probability of occurrence is smaller than the risk rate, it is determined that it has rarely occurred.

2. Preparation for generation of tree structure Figure 2 shows that table S is given by query (unknown query Q _x ) that the user does not clearly recognize from table R, and unary query (X: x) is applied to table R. When the table S _x is obtained by execution, the relationship between the table S _x and the table S in this case is shown. In FIG. 2, the recall (Recall) = | S∩S _x | / | S | can be defined, and if | S∩S _x | = k and | S _x | = n, then the precision (Precision) is P = K / n.

On the other hand, when one tuple is arbitrarily extracted from table R, the probability q that it is included in table S is q = | S | / | R |, so n tuples are randomly extracted from table R. In the set, the probability P (r) that r tuples are included in the table S is
P (r) = nCr q ^r (1-q) ^nr .

This is the recall when random sampling is performed to extract n tuples from the table R. The probability that this sampling result has the same recall rate as the execution result of the previous unary query is P (k) = nCk q ^k (1-q) ^nk .

  If P (k) is less than a predefined risk factor (eg 5%), this probability is less than 5%, so this is an extremely rare event. become. In other words, it can be said that the execution result of the unary query (A: a) approximates S.

3. Generation of Tree Structure FIGS. 3 to 6 show a procedure for deriving the unknown query Q _x shown in FIG. 2 as a tree structure. First, a unary query (X: x) is executed for all terms on the table R. That is, a unary query (A: a), then (B: b),... (X: x)... (N: n) are executed. From these unary queries (X: x), find a unary query (X ₁ : x ₁ ) whose risk rate is less than a predetermined percentage, for example, 5% or less and has the maximum recall, and route this Assign as a node.

FIG. 3B shows the relationship between the table S and the database obtained by executing the unary query (X ₁ : x ₁ ) on the table R as the table S ₁ . The overlapping portion of the table S and the table S ₁ is S∩S _x.

Generating a tree structure First, as shown in FIG. 3 (a), the root node (X _1: x ₁₎ as a starting point, unary query (X _2: x ₂₎ and the auxiliary query (X _2: ≠ x ₂₎ Create a binary tree of Here, the database obtained by executing the unknown query Q _x is the table S.

Creating such binary tree, consider a table S overlapping portions S∩S ₁ of Table S and Table S ₁ as shown in FIG. 4 (a), the table S ₁ and table R, unary with all terms The query (X: x) is executed, and a positive unary query (X ₂ : x ₂ ) having a predetermined condition, for example, a risk rate of 5% or less and a maximum recall rate is connected to the positive side of the root node. To do.

Also, a negative query (X ₁ : ≠ x ₁ ) is executed in the same manner, and a negative unary query (X ₂ : ≠ x ₂ ) that satisfies a predetermined condition is spliced. FIG. 4B shows the relationship between the table S ₁ and the table S _2, and the overlapping portion is S１S ₁ ∩S ₂ .

Next, the table S ₂ is regarded as the table R, the subset S∩S ₁ ∩S ₂ is regarded as the table S, and the unary query (X; x) is similarly executed for all the terms. For example, the risk rate is 5%. In the following, an affirmative unary query that maximizes the recall is obtained. However, if no unary query satisfying such conditions is found, ψ is joined to the unary query (X ₂ : x ₂ ). FIG. 5 shows the tree structure at that time.

When ψ is spliced, the negative unary query of the node with ψ spliced is regarded as the root, and all terms from the root are executed in the same way as the unary query (X; x), and ψ is spliced. Run recursively until it is treed. That is, a negative unary query (X ₂ : ≠ x ₂ ) is executed in the same manner for all terms to obtain a unary query (X ₃ ; x ₃ ) having a risk rate of 5% or less and a maximum recall rate, and a tree Generate a structure. However, if the unary query that satisfies the risk rate of 5% or less and satisfies the maximum recall rate is not found, ψ is joined. FIG. 6 shows the tree structure at that time.

  As shown in FIG. 6, when all the terminal nodes are replaced by ψ, the tree generation process is terminated, and the arrows directed to ψ and ψ are removed. Therefore, the end node where ψ is joined on both the positive node side and the negative node side becomes the end node of the tree.

The path from the root node of the obtained tree structure to all terminal nodes is traced, and a logical expression is generated by AND-joining the unary query that passes through. For example, in the tree of FIG. 6, the logical expression is (X ₁ : x ₁ ) × (X ₂ : x ₂ ) × (X ₃ : x ₃ ) + (X ₁ : x ₁ ) × (X ₂ : x ₂ ) _{× (X 3: ≠ x 3} ) + (X 1: x 1) × (X 2: ≠ x 2) × (X 3: x 3) + (X 1: x 1) × (X 2: ≠ x 2 ).

The query Q _x can be obtained by the above-described logical expression because, as a result of executing the unary query (X: x), the conforming portion X and the nonconforming portion (noise portion) Y and the leaked portion Z are included in the table S _1. And exist. Further expanding the positive part tree is a process of removing the noise part Y from the table S ₁ while preserving as many Xs as possible. At the end of this process, the matching part will dominate and no significant unary queries can be found, and the process ends. On the other hand, expanding the denial tree is a process for recovering the leaked portion Z, and is a process for finding the most advantageous unary query. Thus, a logical expression that can reproduce the tuple set closest to S can be found.
There are various ways to implement the above tree structure in memory. The most direct method is to prepare a memory stack corresponding to AND and OR.

  In addition, the above algorithm outline does not mention attributes that take consecutive numerical values. For such attributes, the method of segmenting in advance seems to be appropriate. Various methods for segmentation have been proposed so far, but the RWS (Random Walk Splitting) method developed by the inventor has received the best evaluation.

  Also, there is not always a single logical expression for the query that leads to the same result. For this reason, when two logical formulas having the same recall rate and matching rate are given, an index for evaluating which logical formula is better is required. In general, the one with a smaller number of unary queries connected by AND and OR is considered as a more general logical expression. Therefore, it is preferable to use the following index M.

M = -log (number of unary queries / number of logical result application tuples)
Example 2
Whether or not the unknown query Q _x described above is a useful technique can be determined using the UCl repository (Blake, C, L, & Merz, CJ ,,, UClRepositoryofma £ hineleamingdatabases ,,,
httpl // www, ics, ucLedu / mleam / MLRepository, htm1, lrvine, CAlUniversityofCalifomia, DepartmentoflnformationandComputerScience (1998)) was used as the database R. This database is a data set of 13 consecutive value attributes and 178 cases.

The query derivation method of the present invention was confirmed by the following evaluation. Unary query on table R (A
; a) is executed, thereby obtaining the table S. A query for generating a database approximating this table S was derived by the method of the first embodiment. The derived query is a query other than a unary query (A; a). Then, the derived query was applied to the table R, and the recall rate, application rate, and number of terms were evaluated for the derived results.

  FIG. 7 shows the evaluation results. First, the experiment was repeated while changing the risk factor of the test. FIG. 8 is a graph of the recall rate and the matching rate in FIG. From these results, good results were obtained with a recall rate and relevance rate of 90% or more when the risk rate was in the range of 1% to 10%. Note that the greater the risk factor, the more noise is included in the tuples extracted by the candidate query. In addition, by repeating the test to remove noise from the tuple extracted from the candidate query, the number of derived query items increases.

  Next, the table R was divided in half, the database of the half was regarded as the table R, and the query was similarly derived. FIG. 9 shows the evaluation results. FIG. 10 is a graph showing the recall rate and the matching rate, and FIG. 11 is a graph showing the item evaluation formulas. From these results, by setting the risk rate to 5%, both the recall rate and the conformity rate were 90% or better.

  Next, the table R was divided into quarters, and the query was similarly derived by regarding the quarter database as the table R. FIG. 12 shows the result of evaluation. FIG. 13 is a graph showing the recall rate and the matching rate, and FIG. 14 is a graph showing the item evaluation formulas. By setting the risk rate to 5%, both the recall rate and the conformity rate were 90% or better.

  According to the present invention, it is possible to derive a query necessary for obtaining, with high accuracy, a sub-database selected from, for example, a user without a clear awareness and an approximate sub-database from an enormous source database.

It is the figure which showed an example of the table demonstrated in a present Example.

FIG. 6 is a diagram illustrating a relationship among a sub database S, an approximate sub database Sx, a recall rate, and a precision rate generated by a query (unknown query Q _x ) that the user does not clearly recognize from the table R. It is a diagram of a binary tree of a unary query (X ₁ : x ₁ ) and a complementary query (X ₁ : ≠ x ₁ ) starting from the root (root). It is the figure which showed the tree structure etc. which performed the positive query and negative query from the root node, and created the unary query which satisfy | fills predetermined conditions as a node. A tree structure in which a unary query that satisfies a predetermined condition is not detected and the end of the tree is a terminal query (ψ) is shown. A diagram of the completed tree structure is shown. It is the figure which showed the recall and the precision which were obtained by applying the query derivation method of this invention to a sample database. FIG. 8 is a graph of the recall and the precision shown in FIG. It is the figure which showed the reproduction rate and the relevance rate which were obtained by applying the query derivation method of this invention about the database obtained by making sample database into 1/2. It is the figure which plotted the reproduction rate and the relevance rate which were obtained by applying the query derivation method of the present invention about the database obtained by setting the sample database to 1/2. FIG. 10 is a graph of the item evaluation formula of FIG. 9. It is the figure which showed the reproduction rate and the relevance rate which were obtained by applying the query derivation method of this invention about the database obtained by making the sample database into 1/4. It is the figure which plotted the reproduction rate and the relevance rate which were obtained by applying the query derivation method of the present invention about the database obtained by setting the sample database to 1/4. FIG. 13 is a graph of the item evaluation formula of FIG. 12.

Claims (3)

A query derivation method for deriving a query required to generate a database that approximates a sub database obtained by executing an unknown query in an original database composed of tuples having a plurality of attributes,
The query derivation method executes a unary query for all the terms of the sub-database, assigns a unary query that satisfies a predetermined condition as a root node,
Performing a unary query on the sub-database to satisfy the predetermined condition, thereby generating an affirmative database;
A negative unary query that negates the query condition of the unary query that satisfies the predetermined condition is executed on the sub-database, thereby generating a negative database;
Consider the positive database or the negative database as the original database,
Consider duplicate data between the positive database or the negative database and the sub database as the sub database, execute the positive query or negative query for all terms,
Detecting a positive unary query and a negative unary query that satisfy the predetermined condition, and creating a tree structure so that the positive unary query and the negative unary query are nodes that are binary trees from the root node;
This is recursively executed until the unary query that becomes the node becomes a positive unary query or negative unary query that does not satisfy the predetermined condition, and completes the tree structure.
Create a logical product of all unary queries that pass through the path from the root node to the end node,
A query derivation method, wherein the query is derived as a query necessary for generating a database that approximates all logical sums of logical products obtained for each pass.   The predetermined condition is a recall ratio in the relation of the random database obtained by randomly extracting n tuples from the original database in relation to the sub database, and a relation in relation to the sub database of the unary query database. The query derivation method according to claim 1, wherein a probability that the recall ratio is the same precision ratio is equal to or less than a predetermined risk ratio, and the recall ratio is the maximum among the unary queries executed for all the terms. The creation of the tree structure according to claim 1, wherein a logical expression of a unary query that satisfies the predetermined condition is stored in a memory cell, and an address is assigned to the memory cell as a root node,
Each time a positive unary query that satisfies the predetermined condition and a negative unary query that satisfies the predetermined condition are generated, the logical expression of each unary query is addressed as a node to the memory cell so that the relationship with the root node can be specified. To complete the tree structure,
Based on the address, the query is derived as a query necessary to generate a database that approximates all the logical sums of all the unary queries obtained through the path from the root node to the end node. Query derivation method.