JP6411232B2 - Sampling apparatus and sampling program - Google Patents

Description

  Embodiments described herein relate generally to a sampling device and a sampling program.

  In general, when random sampling is performed on records in a database, many outliers are mixed in, and the distribution situation is different from the original data, and there is a possibility that a correct analysis result cannot be obtained.

  Sampling at high speed using a B-Tree index with index information has been performed as a response to the request to sample data at high speed while maintaining the distribution of the original data. .

  When detecting outliers in one-dimensional data using B-Tree index, the distance between points (Key value of record) is farther than the distance between other points (Key value of record) (Key value of the record) is an outlier.

  In order to detect outliers strictly, a prior calculation (clustering) is required for all records. In general, since the index information is stored in the hard disk device due to its size, there is a problem that the read from the hard disk device and the calculation cost of O (nlogn) are necessary for clustering. It was.

Donald E. Knuth “The Art of Computer Programming” Volume 3 Sorting and Searching, 1973, 723p

  A problem to be solved by the present invention is to provide a sampling apparatus and a sampling program capable of performing random sampling at high speed without excluding outliers while maintaining the data distribution without classifying one-dimensional data by clustering or the like. Is to provide.

  The sampling apparatus according to the embodiment is stored in the record storage unit in addition to a record storage unit that stores a plurality of records constituting the database and a B-Tree index that has a hierarchical structure and holds index information as an index. Sparse density expressed by the average distance between keys for each leaf block, the average value of the sparse density of all leaf blocks, the standard deviation of the sparse density of all leaf blocks, and the number of records of child nodes B-Tree index storage unit for storing additional information, an application that makes a sampling request in response to a query that the user performs random sampling from the database, a sampling request from the application, and a sampling instruction From the request conversion unit to be converted and the request conversion unit When the sampling instruction is determined by interpreting the sampled sampling instruction and the sampling record is acquired from the record storage unit, the sparse density not less than a predetermined value greater than the average value of the sparse density of all the leaf blocks And a data operation unit that obtains a record from a leaf block that is not excluded.

It is a block diagram showing a schematic structure of a sampling device concerning an embodiment of the present invention. It is a figure explaining the outlier accompanying random sampling. It is a figure explaining the leaf block to exclude. It is a figure explaining the normal distribution map and the ratio to exclude. It is the figure which showed the structure of the B-Tree index which added sparse density. It is a figure explaining the uneven distribution of the record to a specific leaf block. It is a figure explaining random sampling in B-Tree in consideration of selection probability. It is a figure explaining the update procedure of B-Tree, and the range of recalculation. It is a figure explaining the update procedure of B-Tree, and the range of recalculation. It is a figure explaining the update procedure of B-Tree, and the range of recalculation. It is a figure explaining the update procedure of B-Tree, and the range of recalculation. It is a figure explaining the update procedure of B-Tree, and the range of recalculation. It is a flowchart explaining the flow of the B-Tree construction process in a sampling device. It is a flowchart explaining the flow of the random sampling process in a sampling device. It is a figure explaining the process with respect to the sampling request | requirement in an Example. It is a figure explaining calculation of the standard deviation of sparse density in an Example. It is a figure explaining the determination of the sampling record in an Example. It is a figure explaining determination of the sampling record and exclusion of a leaf block in an Example.

  Hereinafter, an embodiment of the present invention will be described with reference to the drawings. In the drawings, the same portions are denoted by the same reference numerals, and redundant description is omitted.

  First, main terms used in the present embodiment will be described.

  A “record” is one of the units constituting a database and is one record of data.

  “Root node” refers to the node at the top of the B-Tree.

  A “leaf node” is a node connected to a leaf block at the bottom of the B-Tree.

  “Branch node” refers to a node between a root node and a leaf node.

  In the B-Tree index, “leaf block” refers to a set of a key value for searching and an address to a record corresponding to the key, connected to a leaf node. Some leaf blocks hold multiple sets.

  “Child node” means a node from which a branch comes out. The node above you is the parent node.

  “Sampling” refers to extracting a part of records for data analysis because the data is huge. Random sampling is a random sampling. In the present embodiment, records are randomly sampled from leaf blocks using a B-Tree index.

  An “outlier” is data that is significantly different from other values and may cause a tendency of data to be uncertain depending on the calculation. In this embodiment, the B-Tree index is used to detect outliers in one-dimensional data. Compared to the distance between other points (record key values), the distance between points (record key values) The point where the distance of is far (the key value of the record) is an outlier.

  “Cursor” refers to the pointer of the currently operated node on the B-Tree. In B-Tree, the root node and branch node have pointers to child nodes, and the cursor moves to the child nodes based on this value.

  “Result set” refers to a group of records (a set of records) extracted from a database. Returned when a command such as SELECT is returned to the database.

  In this embodiment, information on the average distance between keys for each leaf block from the B-Tree is calculated as sparse density, and leaf blocks with relatively large sparse density values are regarded as including outliers and excluded. Thus, random sampling is speeded up without strictly calculating outliers.

  FIG. 1 is a block diagram showing a schematic configuration of a sampling apparatus according to an embodiment of the present invention. This apparatus is realized by using a general-purpose computer (for example, a personal computer (PC) or the like) and software operating on the computer. The computer includes an engineering workstation (EWS) suitable for CAD (Computer Aided Design) and CAE (Computer Aided Engineering). In this embodiment, in such a computer, in addition to a record storage unit that stores a plurality of records constituting the database and a B-Tree index that has a hierarchical structure and holds index information as an index, the record storage unit The sparse density expressed by the average distance between keys for each leaf block, the average value of the sparse density of all leaf blocks, the standard deviation of the sparse density of all leaf blocks, and the number of child node records according to the stored records In response to a query that the user performs random sampling from the database to a sampling device that has a B-Tree index storage unit that stores additional information, and a sampling request function, and converts the sampling request into a sampling command Function and interpret the sampling instruction to determine the sampling record When acquiring sampling records, leaf blocks with a sparse density greater than a predetermined value that is larger than the average value of the sparse density of all leaf blocks are considered to contain outliers, and excluded from the leaf blocks that were not excluded It can also be implemented as a sampling program for realizing the function of acquiring records.

  As shown in FIG. 1, the sampling apparatus 1 according to the present embodiment mainly includes an application 11, a request conversion unit 12, a data operation unit 13, a record storage unit 14, and a B-Tree index storage unit 15.

  The record storage unit 14 includes a record storage area 1, a record storage area 2,..., And the like, and stores a plurality of records constituting the database. An example of the database is a relational database, but is not limited thereto. The record can be expressed in a table format, for example. A table is a collection of one-dimensional data represented by rows and columns, and each row of data is a “row (or row)” relative to a “column (or column)” that represents the title of the data. One row is one data, and the column represents the value of each attribute. One piece of data is a record.

  When a record storage area for performing random sampling is designated from the data operation unit 13, a record is acquired from the corresponding record storage area of the record storage unit 14. Details of record acquisition will be described later.

  The application 11 is software that directly has a function of executing a work that the user of the sampling device 1 wants to execute on the computer. In this embodiment, for example, the sampling device 1 performs sampling acquisition and record update work. It is something to be made.

  In response to the query that the user performs random sampling from the database, the application 11 makes a sampling request to the request conversion unit 12 when acquiring the sampling record. When sampling is requested, (1) the target record storage area name, (2) the ratio (%) of the number of records to be acquired with respect to the table, and (3) the ratio (%) of leaf blocks to be excluded are specified. Here, the target record storage area name is specified as a set of records to be sampled among a plurality of record groups stored in the record storage unit 14. For example, it is named “TEST”.

  The ratio (%) of the record to be acquired to the table specifies the number of records to be sampled by the ratio of the designated record storage area.

  The ratio (%) of leaf blocks to be excluded designates what percentage is excluded from the top of the normal distribution, assuming that the distribution of sparse density (detailed later) of the leaf blocks is a normal distribution. For example, the user issues a request of 0.1% when it is desired to exclude only a leaf block that is significantly off, and 5% when it is desired to exclude an outlier. Note that the number of records to be acquired may be specified instead of the ratio (%) of the number of records to be acquired to the table. Furthermore, the number of leaf blocks to be excluded may be specified instead of the ratio (%) of the leaf blocks to be excluded.

  For example, to acquire a sampling record, a user describes a sampling request as follows in a SELECT statement that is a command of SQL that is a database language for performing data manipulation and definition in a database management system. .

“SELECT * FROM TEST, RANDSAMPLE (10,5);”
In this SELECT statement, the target record storage area to be randomly sampled is a record storage area named “TEST”, the ratio of the number of records to be sampled is 10%, and the ratio of excluding outliers is 5%. .

  The application 11 issues a record update request to the request conversion unit 12 at the time of record update in response to a query that the user performs record update on the database. The record update is a request for newly inserting a record with a Key value = 6 into the target record storage area, for example.

  When acquiring the sampling record, the request conversion unit 12 receives the sampling request designated as described above from the application 11 and then converts it into a sampling command. The converted sampling command is sent to the data operation unit 13.

  At the time of record update, the request conversion unit 12 receives a record update request from the application 11 and then converts it into a record update command.

  When acquiring the sampling record, the data operation unit 13 interprets the sampling command received from the request conversion unit 12 and determines the sampling record. The determined sampling record is acquired from the designated record storage area of the record storage unit 14. The data operation unit 13 constructs a B-Tree read from the B-Tree index storage unit 15 on its own memory (not shown), and performs a process of determining a record to be read while moving at random.

  As will be described later, sampling records belonging to other than leaf blocks determined to be excluded according to sparseness are sent to the application 11 as a result set which is a record group.

  At the time of record update, the data operation unit 13 interprets the record update command received from the request conversion unit 12 and determines a record storage area for executing record update. The update record is written in the designated record storage area of the record storage unit 14.

  The data operation unit 13 generates data for updating the B-Tree according to the update record. Data for updating the B-Tree is sent to the B-Tree index storage unit 15.

  The B-Tree index storage unit 15 is a place for permanently storing the B-Tree, and is preferably configured by a hard disk storage device, for example. The B-Tree index storage unit 15 stores all the root nodes, branch nodes, leaf nodes, and leaf blocks of the B-Tree.

  In this embodiment, at the time of record update, according to the data for updating the B-Tree received from the data operation unit 13, (1) sparse density (average value of leaf block distance), (2) all leaf blocks It stores a B-Tree index obtained by adding information of the average value of sparse density, (3) standard deviation of sparse density of all leaf blocks, and (4) number of records of child nodes to B-Tree. In the initial state, (1) sparse density (average value of leaf block distance) corresponding to the records stored in the record storage area 1, record storage area 2,. ) An average value of sparse density of all leaf blocks, (3) standard deviation of sparse density of all leaf blocks, and (4) additional information on the number of records of child nodes.

  When using a B-Tree, data is temporarily read from a B-Tree index storage unit 15 onto a memory (not shown), and a B-Tree is constructed and processed. However, if all B-Trees are built in memory, there are size restrictions, and all leaf blocks may not be read into memory. Therefore, only the root node, branch node, and leaf node are constructed on the memory, and the leaf block is read from the B-Tree index storage unit 15 as necessary. In the conventional method, since it is necessary to calculate the information of all the records, all the leaf blocks are read from the hard disk storage device. However, according to the present embodiment, only the leaf block moved at random is read. As a result, the number of times the hard disk storage device is read can be reduced.

<Outlier>
When performing random sampling, many outliers are selected, and the distribution status differs from the original data, which may cause the analysis result to be strange.

  FIG. 2 is a diagram for explaining outliers associated with random sampling. As shown in FIG. 2, when many outliers are sampled, there are cases where the distribution situation differs greatly from the actual data. Therefore, it is important to obtain a portion excluding outliers while maintaining the structure of the one-dimensional data to some extent.

  In this embodiment, by using the B-Tree index, one-dimensional data (record) in which the distance between points (records) is larger than the distance between other points (records) is used as an outlier. Therefore, leaf blocks having a large value of sparse density (described later) are excluded from sampling targets.

  FIG. 3 is a diagram for explaining leaf blocks to be excluded. The root node manages the Key value and the pointer to the lower branch node. When the Key value is specified, it is understood which branch node should be advanced next. In the branch node, the Key value is further divided and the pointer to the leaf node in the lower layer is managed. The leaf node is the lowest layer node and manages the physical position of the record storage area in which the key value and the record corresponding to the key value are stored.

  In the example shown in FIG. 3, the root node manages a pointer to the left branch node whose Key value is 61 and the Key value is 29, and the right branch node whose Key value is 81. If the search key value is smaller than 61, the process branches to the left branch node. If the search key value is greater than 61, the process branches to the right branch node. The left branch node with Key value = 29 manages pointers to the left leaf node with Key value 3 to 8 and the right leaf node with Key value 52 to 57.

  If the search key value is smaller than 29, the process branches to the left leaf node. If the search key value is greater than 29, the process branches to the right leaf node. In the leftmost leaf node shown in FIG. 3, the number of records when the search key value is 3 or less, the number of records when the search key value is between 3 and 8, and the number of records when the search key value is 8 or more. Is managed. The same applies to other leaf nodes. The leaf node is connected to a leaf block including a pair of a key value and an address to a record corresponding to the key.

  In the present embodiment, if the average distance between the points of the leaf block is larger than that of the other leaf blocks, it is highly likely that an outlier is included, and therefore it is excluded. In the example illustrated in FIG. 3, the range of leaf blocks when the Key value is 8 or more is larger than the range of other leaf blocks, and thus may include outliers and are excluded. Therefore, no record is acquired from the leaf block when the Key value is 8 or more.

<Sparse density>
In the present embodiment, “average distance between keys for each leaf block” is defined as sparse density. Specifically, the sparse density is obtained by dividing (the maximum value of the key in the leaf block−the minimum value of the key in the leaf block) by (the number of records in the leaf block−1). Note that if there is only one record in the leaf block, the density determination is not performed.

  If the sparse density value itself is small (the average distance between keys is short), the sparse density is high and dense, and if the sparse density value itself is large (the average distance between keys is long), the sparse density is low. Sparse. Since the B-Tree is sorted, the larger the sparse density itself, the more likely it is to include points that are more distant from each other.

<Determining the exclusion of leaf blocks>
In the present embodiment, leaf blocks that are relatively sparse compared to other leaf blocks are excluded. To exclude points that are far apart from each other compared to the distance between other points, exclude the leaf block that has a large value of (Maximum value of key-Minimum value of key) from multiple leaf blocks. To do.

  In other words, those having a large sparse density value are sparse and therefore excluded from the leaf block to be sampled, and those having a small sparse density value itself are dense and are not excluded from the leaf block to be sampled.

  The user can specify what percentage of the top sparse leaf blocks to exclude from the normal distribution as a percentage. Specifically, leaf blocks having a sparse density greater than a certain value are excluded from the overall average value of the sparse density.

For example, the threshold for exclusion determination is expressed by the following equation.

  Leaf blocks having a sparse density equal to or greater than the above-described exclusion determination threshold are excluded from the leaf blocks to be sampled. The coefficient Q is obtained from a percentage specified by the user, and is calculated by an existing method assuming that the sparse density of the leaf blocks is a normal distribution. For example, it is preferable to use a statistical “percentage point”, and in the normal distribution, the percentage point at which the upper probability is 2.5% is 1.96.

FIG. 4 is a diagram for explaining the normal distribution chart and the excluded ratio. As shown in FIG. 4, the area of the normal distribution map is the percentage of leaf blocks in which the sparse density exists within the range. The normal distribution diagram shown in FIG. 4 indicates that the numerical value of the sparse density is larger (the average distance between the keys is longer) as it goes to the right of the average value of the overall sparse density that is the median value. When the numerical value of the sparse density is small (the average distance between keys is short), it is unlikely that an outlier is included, and it is not necessary to exclude it from the sampling target.

<B-Tree index structure with sparse density>
FIG. 5 is a diagram showing the structure of a B-Tree index to which sparse density is added. As shown in FIG. 5, the root node manages the Key value, the number of records of child nodes, and pointers to the lower branch nodes. When the Key value is specified, it is understood which branch node should be advanced next. In the branch node, the Key value is further divided and the number of records of the child node and the pointer to the leaf node in the lower layer are managed. A leaf node is a lowermost node, and is stored in a record storage area in which a key value, the number of records of a child node (leaf block), a sparse density for each child node (leaf block), and a record corresponding to the key value are stored. The physical position is managed.

  In the example shown in FIG. 5, the key value of the root node is 61, the number of records in the left branch node is 10, the number of records in the right branch node is 7, and the number of records in the child nodes is 17 in total. It has become. If the search key value is smaller than 61, the process branches to the left branch node. If the search key value is greater than 61, the process branches to the right branch node.

  Similarly, in the left branch node, Key value = 29, the number of records in the left leaf node is 6, the number of records in the right leaf node is 4, and the number of records in the child nodes is 10 in total. It has become. If the search key value is smaller than 29, the process branches to the left leaf node. If the search key value is greater than 29, the process branches to the right leaf node. In the leftmost leaf node shown in FIG. 5, the number of two records when the Key value is 3 or less, the number of two records when the Key value is between 3 and 8, and the Key value is 8 or more These two records are managed. The same applies to other leaf nodes. In this embodiment, the sparse density representing the average distance between keys is managed for each leaf node. In the leftmost leaf node, the sparse density is 2 when the Key value is 3 or less, the sparse density is 3 when the Key value is between 3 and 8, and the sparse density is 20 when the Key value is 8 or more. It is managed to be. Further, a leaf block consisting of a pair of a key value and an address to a record corresponding to the key is connected to the leaf node.

<Random sampling with B-Tree>
In B-Tree, the number of branches of branch nodes and the number of records in leaf blocks differ depending on the tree. Therefore, if the movement (branch direction) is random at the time of sampling, the obtained records are biased.

  FIG. 6 is a diagram for explaining the uneven distribution of records in a specific leaf block. In FIG. 6, the number of records and sparse density of child nodes are not shown. In the B-Tree shown in FIG. 6, the number of leaves hanging from the left branch node of Key value = 29 is 6 and the number of leaves hanging from the right branch node is 4, so that the number of records of the leaf nodes varies. Therefore, if records are acquired by moving randomly from the root node to the right leaf node of Key value = 52 to 57 via the left branch node of Key value = 29, the number of records to be acquired is biased. .

  FIG. 7 is a diagram for explaining random sampling in the B-Tree in consideration of the selection probability. In FIG. 7, sparse density is not shown. As shown in FIG. 7, the root node and the branch node hold the number of child node records. That is, the branch node holds the number of records of the child node for each branching range, the leaf node holds the current number of records, and the probability of moving to each child node when performing a random walk to the child node It is preferred to calculate and determine. Thereby, it becomes possible to average the selection probability of each record.

<Rebuilding B-Tree>
8 to 12 are diagrams for explaining the B-Tree update procedure and the range of recalculation. As shown in FIG. 8, in the B-Tree index structure to which sparse density is added, an insertion record with a Key value = 6 is added. As shown in FIG. 9, the insertion position is searched while adding the number of records of the child node of the movement destination. First, move from the root node to the left branch node with Key value = 29. As a result, the number of child node records held by the root node is 11. Similarly, since the branch node moves to the left leaf node, the number of child node records held by the branch node is seven. Since the insertion record has a Key value = 6, it falls between the Key value = 3 and the Key value = 8 of the leaf node, and is therefore inserted into the central block. As a result, as shown in FIG. 9, the number of leaf blocks connected to the central leaf node is three. When reconstructing a B-tree, the number of leaf blocks connected to an appropriate leaf node can be arbitrarily set by the user. In the example shown in FIG. 9, the number of leaf blocks connected to leaf nodes is limited to two in order to make the drawing easier to see.

  As shown in FIG. 10, when a leaf node is subdivided so that there are two leaf blocks connected to all leaf nodes, the number of child nodes (leaf blocks) connected to the leftmost leaf node is four. Furthermore, the sparse density also changes as the state of the leaf node changes. The user can arbitrarily set the appropriate number of child nodes (leaf blocks) when rebuilding the B-tree. In the example shown in FIG. 10, the number of child nodes (leaf blocks) connected to the leaf nodes is limited to three at maximum in order to make the drawing easy to see.

  Therefore, as shown in FIG. 11, it is broken down into leaf nodes with Key value = 3 and leaf nodes with Key value = 8. Along with this, the left branch node is also decomposed so as to be a branch of Key value = 6 and Key = 29. In this case, since the state of the branch node has changed, it is necessary to recalculate the number of records.

  FIG. 12 is a diagram for explaining a place where calculation by updating a record is necessary. In this embodiment, the place where the sparse density needs to be recalculated is the same as the normal B-tree update part. Therefore, the update calculation amount is log (n).

<B-Tree construction process flow>
A flow of B-Tree construction processing in the sampling device 1 configured as described above will be described. FIG. 13 is a flowchart for explaining the flow of B-Tree construction processing in the sampling device 1.

  First, the cursor is moved to the root node (step S131).

  Next, move the cursor to the node in the range of the key to be inserted. Before the movement, 1 is added to the number of records of the movement destination node (step S132).

  Subsequently, it is determined whether or not the cursor position is a leaf node (step S133). If the cursor position is not a leaf node (No in step S133), the process returns to step S132, and if the cursor position is a leaf node (Yes in step S133), the key and the address of the record are added to the leaf block (step). S134).

  Next, it is determined whether there are more than a predetermined number of records in the leaf block (step S135). Here, the specified number is a threshold value for determining whether or not to reconstruct the B-Tree. It is preferable to manage the number of records of child nodes in order to avoid the uneven distribution of many records in a specific leaf block and to average the selection probability of each record.

  If there are no more than the specified number of records in the leaf block (No in step S135), the sparse density of the added leaf block is recalculated, stored in the leaf node (step S136), and the B-Tree construction process is terminated. .

  On the other hand, if there are more than the specified number of records in the leaf block (Yes in step S135), B-Tree reconstruction is executed (step S137).

  Subsequently, the sparse density of each leaf block affected by the reconstruction and the number of records of the child nodes are recalculated and stored in the leaf nodes (step S138), and the B-Tree construction process is terminated.

<Random sampling process flow>
A flow of random sampling processing in the sampling apparatus 1 configured as described above will be described. FIG. 14 is a flowchart for explaining the flow of random sampling processing in the sampling apparatus 1.

  First, the request conversion unit 12 determines a record sampling number and a coefficient Q necessary for leaf block exclusion determination from input designated by the user (step S1401).

  Next, the data operation unit 13 moves to all leaf nodes, and calculates the standard deviation σ of the sparse density of all leaf blocks and the average value of the sparse density of all leaf blocks (step S1402).

  Next, the cursor is moved to the root node (step S1403).

  Next, the cursor is moved to the child node (step S1404).

  Subsequently, it is determined whether or not the position of the cursor is a leaf node (step S1405).

  If the cursor position is not a leaf node (No in step S1405), the process returns to step S1404. If the cursor position is a leaf node (Yes in step S1405), one of the leaf blocks connected to the leaf node is randomly selected. (Step S1406).

  Next, it is determined whether or not the sparse density of the selected leaf block is smaller than a threshold value when excluding the leaf block (step S1407).

  If the sparse density of the selected leaf block is not smaller than the threshold value when excluding the leaf block (No in step S1407), the leaf block is excluded because it is not suitable for the sampling record, and the process proceeds to step S1403.

  On the other hand, if the sparse density of the selected leaf block is smaller than the threshold for excluding the leaf block (Yes in step S1407), one record to be acquired from the selected leaf block is randomly determined and inserted into the result set. (Step S1408).

  Next, it is determined whether or not the total number of records acquired in the result set has reached the designated sampling number (step S1409).

  If the specified number of samplings has not been reached (No in step S1409), the process proceeds to step S1403 to continue random sampling.

  On the other hand, if the designated sampling number has been reached (Yes in step S1409), the result set is returned to the application (step S1410), and the random sampling process is terminated.

[Example]
Next, examples of the present embodiment will be described. Here, an embodiment in which random sampling is executed in the B-tree index structure shown in FIG. 5 will be described. FIG. 15 is a diagram illustrating processing for a sampling request in the embodiment.

  The application 11 makes a sampling request as shown in FIG. 15 to the request conversion unit 12 in response to a query that the user performs random sampling from the database. Here, for the target record storage area named “TEST”, the number of records to be sampled is specified as 10% of the designated record storage area, and the ratio of leaf blocks to be excluded is specified as 5%.

Since the number of records of the child node of the root node (Key value = 61) is 10 + 7 = 17, the number of sampling records is determined to be 17 × 0.1≈2 records.

  FIG. 16 is a diagram for explaining calculation of the standard deviation of sparse density in the embodiment. As shown in FIG. 16, the sparse density is acquired by moving to all leaf nodes. Based on the acquired sparse density, the standard deviation and average value of the sparse density are calculated. In the embodiment, the threshold for leaf block exclusion determination is 6.0 + 1.6 × 6.0 = 15.6.

  FIG. 17 is a diagram illustrating determination of sampling records in the embodiment. As shown in FIG. 17, the threshold value for exclusion determination is compared with the sparse density. In the embodiment, since the threshold for leaf block exclusion determination is 15.6, one record is selected at random. If the total number of records acquired is not the number specified as the sampling number, the process moves to the root node.

  FIG. 18 is a diagram for explaining sampling record determination and leaf block exclusion in the embodiment. As shown in FIG. 18, the leaf block exclusion determination threshold is 15.6, whereas the selected leaf block has a sparse density of 20. Therefore, it is determined as a leaf block to be excluded. Therefore, the record connected to the leaf block does not become a sampling record but moves to the root node.

  The above is repeated until the total number of acquired records reaches the number specified as the sampling number.

  According to the present embodiment, when executing random sampling, if the number of records is n, the amount of calculation can be reduced to 1 / n compared to the case of strictly calculating an outlier value of Key.

  Although several embodiments of the present invention have been described, these embodiments are presented by way of example and are not intended to limit the scope of the invention. These novel embodiments can be implemented in various other forms, and various omissions, replacements, and changes can be made without departing from the scope of the invention. These embodiments and modifications thereof are included in the scope and gist of the invention, and are included in the invention described in the claims and the equivalents thereof.

DESCRIPTION OF SYMBOLS 1 ... Sampling device 11 ... Application 12 ... Request conversion part 13 ... Data operation part 14 ... Record storage part 15 ... B-tree index storage part

Claims (12)

A record storage unit for storing a plurality of records constituting the database;
In addition to the B-Tree index, which has a hierarchical structure and holds index information as an index, the sparse density represented by the average distance between keys for each leaf block according to the record stored in the record storage unit, and the total density An average value of the sparse density of leaf blocks, a standard deviation of the sparse density of all leaf blocks, and a B-Tree index storage unit that stores additional information on the number of records of child nodes;
An application that makes a sampling request in response to a query by a user to perform random sampling from the database;
A request conversion unit that receives a sampling request from the application and converts it into a sampling instruction;
When determining the sampling record by interpreting the sampling instruction received from the request conversion unit and obtaining the sampling record from the record storage unit, the predetermined value is greater than the average value of the sparse density of all the leaf blocks A data manipulation unit that obtains a record from a leaf block that is not excluded, excluding a leaf block having the sparse density of
A sampling device provided.   The sampling apparatus according to claim 1, wherein the database is a relational database.   The sampling apparatus according to claim 1, wherein the record relates to one-dimensional data and is represented in a table format.   In the sampling request, among the plurality of record groups stored in the record storage unit, the target record storage area name specified as a set of records to be sampled and the number of records to be sampled are the ratio of the target record storage area The sampling apparatus according to any one of claims 1 to 3, wherein a ratio of the number of records to be acquired represented by (2) to a table and a ratio of leaf blocks to be excluded are designated.   In the sampling request, among the plurality of record groups stored in the record storage unit, the target record storage area name specified as a set of records to be sampled, the number of records to be sampled, and the leaf block to be excluded The sampling device according to any one of claims 1 to 3, wherein a number is designated.   The sampling device according to claim 1, wherein the leaf block is a set of a key value to be searched and an address on the record storage unit to a record corresponding to the key.   7. The sparse density is calculated by dividing (maximum value of key in leaf block−minimum value of key in leaf block) by (number of records in leaf block−1). The sampling apparatus according to item 1.   The predetermined value at the time of exclusion determination of the leaf block is obtained by adding a value obtained by (coefficient × standard deviation of the sparse density of all leaf blocks) to the average value of the sparse density of all leaf blocks. The sampling apparatus according to any one of claims 1 to 7, wherein the sampling apparatus is used to represent a region to be excluded on the normal distribution when the sparse density distribution is assumed to be a normal distribution.   When the root node holds the number of child node records, the branch node holds the number of child node records for each branch range, the leaf node holds the current number of records, and a random walk to the child node The sampling apparatus according to claim 1, wherein the selection probability of each record is averaged by calculating a probability of moving to each child node.   When updating a record by inserting a new record into the database, the insertion position is searched while adding the number of records at the movement destination, the number of leaf blocks is suppressed to 2 or less, and the number of child nodes is 3 or less. The sampling apparatus according to any one of claims 1 to 9, wherein the insertion position is determined while suppressing the insertion position.   The sampling device according to any one of claims 1 to 10, wherein the data operation unit determines records to be randomly acquired from leaf blocks that are not excluded, and collects the results into a result set and returns them to the application. In addition to a record storage unit that stores a plurality of records that constitute a database, and a B-Tree index that has a hierarchical structure and holds index information as an index, a leaf corresponding to a record stored in the record storage unit B that stores additional information such as the sparse density expressed by the average distance between keys for each block, the average value of the sparse density of all leaf blocks, the standard deviation of the sparse density of all leaf blocks, and the number of records of child nodes -Sampling device equipped with an index storage unit,
In response to a query to perform random sampling from the database by the user, a function to make a sampling request;
A function of converting the sampling request into a sampling instruction;
When interpreting the sampling instruction to determine a sampling record and obtaining a sampling record, a leaf block having a sparse density greater than or equal to a predetermined value greater than the average value of the sparse density of all the leaf blocks is an outlier. With the ability to retrieve records from leaf blocks that were not included and excluded
Sampling program to realize.

Images