JP2005135221A - Method and device for joining spreadsheet data and program - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To join a plurality of spreadsheet data without actually creating a join table. <P>SOLUTION: First and second spreadsheet data represented as sequences of records including item values corresponding to information items are prepared. The information items belonging to both spreadsheet data are determined as key items. Key item values corresponding to the key items of the records to be processed which are included in the first spreadsheet data are obtained. From the records included in the second spreadsheet data, a group of records including the item values that meet predetermined matching requirements with respect to the key item values about the key items is specified. A pair of the records to be processed and a record group are outputted to create a virtual join table. <P>COPYRIGHT: (C)2005,JPO&NCIPI

Description

  The present invention relates to a data processing method and data processing apparatus for processing a large amount of data using an information processing apparatus such as a computer, and more particularly to a method, apparatus and program for combining a plurality of tabular data.

  Databases are used for various purposes, but in medium to large-scale systems, the use of relational databases (RDB) that can eliminate logical contradictions is the mainstream. For example, RDB is used in a system such as airplane seat reservation. In this case, by specifying a key item, it is possible to quickly search for a target (in many cases, one), or to confirm, cancel or change a reservation. In addition, since the number of seats for each flight is at most several hundred, it is possible to obtain the number of vacant seats for a specific flight.

  However, if this RDB is used to perform a specific calculation (for example, calculation of boarding rate) every year, every day of the week, every month, every route, every time zone or every model, etc., it takes a very long time. This is known. That is, RDB is excellent in realizing processing without contradiction, but has a low performance of searching, counting or sorting a considerable number of records.

  Therefore, in recent years, it has become common to construct a database called a data warehouse (DWH) in the system separately from the RDB for searching and tabulation. In other words, an extremely large database with a specific data format and data item name is constructed according to the specific purpose of the end user, and the end user can use this to perform a specific search and aggregation. It has become.

  However, in addition to RDB, providing other DWHs, that is, providing a plurality of databases, is essentially different from the ideal form in which RDB was devised because of the ability to centrally manage data. From this, various problems as described below have arisen, for example.

(1) Since the DWH is fixed, it is difficult to search and tabulate items other than those previously provided in the DWH.
(2) Since a fixed DWH is provided in addition to the RDB, the data capacity becomes extremely large, and the RDB cannot be updated.

In order to solve these problems, it has been proposed to combine, that is, join, a plurality of tabular data (tables). A tabular data join is an item that becomes a join key for each of multiple tabular data, and when the key satisfies the relationship, the records included in each tabular data are associated with each other, and a new To create tabular data (join table). Such tabular data join is an operation required in various situations when handling data of a large-scale system.
JP 7-253991 A

  However, since the joining of the plurality of tabular data is an AND operation, a huge join table is frequently generated explosively, resulting in a problem of insufficient memory and disk device areas. In addition, since the generated new join table is not provided with an index such as B-Tree or bitmap index that is advantageous for search, there is a problem that it is difficult to perform high-speed search and sort. Even in the technique described in Patent Document 1, a table that is actually merged is created, and thus a huge table must be created.

The present invention provides a method and apparatus for combining a plurality of tabular data without creating a join table that may become explosively huge and is not suitable for performing high-speed searching and sorting. With the goal.
Another object of the present invention is to provide a search method and a sort method for a plurality of tabular data based on the above-described method for combining a plurality of tabular data.

The principle of the present invention that achieves the above object is not to actually create a join table, but to virtually create a join table using a search.
An object of the present invention is to prepare first tabular data and second tabular data represented as an array of records including item values corresponding to information items, and the first tabular data and the first tabular data A procedure for determining an item of information belonging to the second tabular data as a key item;
Obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
A procedure for identifying a record group including an item value that satisfies a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
Outputting a pair of the record to be processed and the record group included in the second tabular data, the processing target record included in the first tabular data;
Is achieved by a method of combining tabular data having

According to a preferred embodiment of the present invention, the second tabular data is tabular data in which an index is assigned to the key item,
The procedure for specifying the record group specifies the record group by a search using the index assigned to the key item of the second tabular data.

According to a preferred embodiment of the present invention, the tabular data combination method includes, for each item value corresponding to the key item with respect to the second tabular data, prior to the procedure of specifying the record group, The method further includes generating index data specifying a record including the item value from the item value,
In the procedure of specifying the record group, the record group is specified by a search using the index data of the second tabular data.

According to a preferred embodiment of the present invention, the procedure for generating the index data includes a procedure for sorting the index data based on the item values corresponding to the key item values.
According to a preferred embodiment of the present invention, the procedure of generating the index data includes a procedure of assigning an index to the item value portion of the index data generated for each item value corresponding to the key item. .

According to a preferred embodiment of the present invention, the index data is a pointer to a record.
According to a preferred embodiment of the present invention, the index data is a record number.
According to a preferred embodiment of the present invention, the index data is associated with an address on a memory where a record is stored.

  The tabular data joining method of the present invention can be used to search a join table. Therefore, the first tabular data is prepared by retrieving a record including item values that meet the search condition from initial tabular data represented as an array of records including item values corresponding to information items. Tabular data. According to the present invention, it is possible to obtain the same result as when the join table is searched by searching the first tabular data in advance.

  By using this principle, it is possible to replace a general join table search by a table search before a join and a table join. Therefore, a tabular data search method according to the present invention is as follows.

The first tabular data is prepared by retrieving records including item values that meet the search condition from initial tabular data represented as an array of records including item values corresponding to information items. Preparing a second tabular data represented as an array of records containing item values corresponding to the items;
A procedure for determining, as a key item, an item of information belonging to both the first tabular data and the second tabular data;
Obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
A procedure for identifying a record group including an item value that satisfies a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
A procedure for generating a pair of the record to be processed and the record group included in the second tabular data included in the first tabular data;
Have

  Furthermore, the tabular data joining method of the present invention can be used to sort join tables. Therefore, the first tabular data is prepared by rearranging the records of the initial tabular data represented as an array of records including item values corresponding to the information items with respect to a predetermined item. It is. According to the present invention, the same result as when the join table is sorted can be obtained by sorting the first tabular data in advance.

By using this principle, it is possible to replace the general sort of the join table by the search of the table before the join and the join of the table. Therefore, a method for sorting tabular data according to the present invention is as follows.
First tabular data is prepared by rearranging records of initial tabular data represented as an array of records including item values corresponding to information items with respect to a predetermined item, and items corresponding to information items Preparing a second tabular data represented as an array of records containing values;
A procedure for determining, as a key item, an item of information belonging to both the first tabular data and the second tabular data;
Obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
A procedure for identifying a record group including an item value that satisfies a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
A procedure for generating a pair of the record to be processed and the record group included in the second tabular data included in the first tabular data;
Have

The object of the present invention can also be achieved by a combining device that implements the tabular data combining method of the present invention. The tabular data combining device according to the present invention comprises:
First tabular data and second tabular data represented as an array of records including item values corresponding to information items are prepared, and the first tabular data and the second tabular data are prepared. Means for determining as a key item information items belonging to
Means for obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
Means for specifying a record group including an item value satisfying a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
Means for outputting a pair of the record to be processed included in the first tabular data and the record group included in the second tabular data;
Have

  The object of the present invention is also achieved by a program for causing a computer to execute the above-described procedure of the tabular data combining method of the present invention.

  According to the present invention, since a plurality of tables are logically joined and a huge join table is not actually generated, the consumption of the storage area of the memory or the disk device is reduced. In addition, according to the present invention, a search or sort of a virtual join table is performed by logically joining a plurality of tables after performing a search or sorting on a table to which an index or the like has been assigned. Can be implemented.

  Hereinafter, embodiments of the present invention will be described with reference to the accompanying drawings. FIG. 1 is a block diagram showing a hardware configuration of a computer system that can realize tabular data join, search, and sort according to an embodiment of the present invention. As shown in FIG. 1, this computer system 10 has the same configuration as a normal one. A CPU 12 that controls the entire system and individual components by executing a program, a RAM (Random) that stores work data, and the like. (Access Memory) 14, a ROM (Read Only Memory) 16 for storing programs, a fixed storage medium 18 such as a hard disk, a CD-ROM driver 20 for accessing the CD-ROM 19, a CD-ROM driver 20 and an external network (FIG. An interface (I / F) 22 provided between an external terminal and a connected external terminal, an input device 24 including a keyboard and a mouse, and a CRT display device 26. The CPU 12, RAM 14, ROM 16, external storage medium 18, I / F 22, input device 24, and display device 26 are connected to each other via a bus 28.

  The CD-ROM 19 stores a program for joining (joining) tabular data, a program for creating a table (view) of predetermined items from the tabulated data, a search program, and a sort program according to the present embodiment. It may be read by the CD-ROM driver 20 or stored in advance in the ROM 16. Further, what is once read from the CD-ROM 19 may be stored in a predetermined area of the external storage medium 18. Alternatively, the program may be supplied from the outside via a network (not shown), an external terminal, and the I / F 22.

  Next, joining of a plurality of tabular data will be described. In the following description, the tabular data is simply referred to as “table”. In the virtual join according to the present invention, an item to be a join key is specified for each of a plurality of tables, and when the key satisfies a predetermined relationship, a new table is virtually associated with the records of each table. Is to create. FIG. 2 is an explanatory diagram of a method for virtually creating a new join table from two tables, a baseball club member table and a game schedule table. The baseball club member table is composed of three records including “member name” and “support team” as items. For example, the item value corresponding to the item “member name” of the top record is “TANAKA”, and the item value corresponding to the item “support team” is “Giants”. On the other hand, the game schedule table is composed of four records including “ball team” and “game date” as items. For example, in the first record, the item value corresponding to the item “ball team” is “Tigers”. The item value corresponding to the item “game day” is “May 10”. Here, the item “supporting team” in the baseball enthusiast membership table and the item “ball team” in the game schedule table include common elements in the set of item values corresponding to the items, although the item names are different. Therefore, the item “support team” in the baseball enthusiast membership table and the item “ball team” in the game schedule table are determined as key items common to both tables, and records in which the item values of the key items match are combined. The virtual table shown in FIG. 2 is generated.

  For example, when a record whose item value “Giants” matches the item value “Giants” of the first record (= record 0) of the baseball enthusiast member table is searched for in the game schedule table, Since the record on the second line (= record 1) and the record on the fourth line (= record 3) are found, the record 0 in the baseball club member table has two records, record 1 and record 3 in the game schedule table. Corresponds. Newly (virtually) generated records by this association are shown in record 0 and record 1 of the virtually generated table.

  Here, the virtual table means that the virtual table is not actually developed on the memory but is logically related. In the conventional join method, the join table is actually generated and stored in the memory or the disk device.

  In the example of FIG. 2, the predetermined relationship satisfied by the key is a “matching” relationship, but the predetermined relationship in the present invention is not limited to the “matching” relationship, and “<”, “ > ”,“ <= ”, And“> = ”.

  In the following, based on the example of the virtual join of the baseball lover association member table (= first table) and the game schedule table (= second table) shown in FIG. explain. FIG. 3 is a flowchart of an embodiment of the tabular data combination method of the present invention. In step 301, a first table and a second table are prepared, and key items are determined. For example, the first table and the second table are expanded on the memory, and “support team” and “ball team” are designated as key items. Next, in step 302, the key item value of the processing target record in the first table is acquired. For example, the record in the first row of the first table is set as a process target record, and the item value “Giants” of the “support team” item is extracted. Subsequently, in step 303, a record group satisfying a predetermined matching condition is specified from the records in the second table. For example, the record having the “Grants” item value is searched from the “ball team” item in the second table, and the record group of the record 1 and the record 3 is specified. Finally, in step 304, a pair of a processing target record in the first table and a record group in the second table is output. In the present embodiment, the pair of the record to be processed and the record group is output to, for example, the display device 26 in FIG. 1 or output to an external device via the I / F 22 and an external terminal.

In the present embodiment, pairs of record groups can be sequentially output by sequentially proceeding with the processing target records in the first table. FIG. 4 is an explanatory diagram of processing for sequentially outputting record group pairs. In the figure, (A) represents a pair of record groups in the second table corresponding to record 0 in the first table, and (B) represents records in the second table corresponding to record 1 in the first table. (C) represents a pair of record groups in the second table corresponding to record 2 in the first table. Thereby, the virtual join table is completed.
Thus, according to the present embodiment, it is possible to logically join the first table and the second table and present the join result without actually creating the join table.

  In step 303 in FIG. 3, in order to specify a record group satisfying a predetermined matching condition, a record having a specific item value in the “ball team” item is searched in the second table. In order to speed up this search, according to a preferred embodiment of the present invention, the second table is provided with an index such as B-Tree or bitmap index in the “Team” item. This index may be assigned in advance when preparing the second table, or may be assigned after the key item is determined. By assigning the index in this way, in step 303 for identifying the record group, the record group can be identified by a search using the index assigned to the “ball team” item in the second table.

  Alternatively, in another embodiment of the present invention, in order to search for the “ball team” item in the second table at high speed, for each item value corresponding to the “ball team” item, from this item value, this item value Index data for designating a record including “” is generated in advance, and a record group is specified by a search using the generated index data. Further, in order to speed up the search, the index data is sorted in advance based on the item value of the key item, or an index is assigned to the item value portion of the index data.

  FIG. 5 is an explanatory diagram of an example in which a pointer to a record is used as index data for speeding up a search for a record group according to another embodiment of the present invention. A list of pointers pointing to the record containing that value from the “Team” item in the second table, eg, [Dragons], [Giants] and [Tigers] is sorted in the value part of the “Team” item. Prepared as a pointer list. Alternatively, the list of pointers pointing to this record may add an index to the value part of the “Team” item.

  In another embodiment of the present invention, a record number is used as index data. In the example of FIG. 2, the record number of the first row record of the second table is 0, the record number of the second row record is 1, and so on. The index data based on the record number can be sorted or indexed, similarly to the index data based on the pointer to the record.

  Furthermore, in another embodiment of the present invention, a value associated with an address on a memory storing a record is used as index data. For example, each record may start at every 1000th address, and record 0, record 1, record 2... May be specified using values 1000, 2000, 3000. The index data based on this value can also be sorted and indexed as in the index data based on the pointers to the records described above.

  The tabular data joining method according to the present invention can be extended to join table retrieval and sorting. As described above, in a conventional join that actually creates a join table, even if an index is assigned to a table to be joined, the index is not reflected in the newly created join table. Therefore, it has not been possible to search and sort at high speed using the joined table after the join. On the other hand, in the tabular data joining method based on the principle of virtual join according to the present invention, the first table that is the basis of the join is searched, the first table and the second table after being narrowed down and narrowed down. By joining these tables, the search in the join table can be virtually realized. Also, sorting in the join table can be realized virtually by sorting the first table from which the join is based in advance and combining the sorted first and second tables.

  FIG. 6 is an explanatory diagram of a search method using the tabular data combination method according to the embodiment of the present invention. FIG. 7 is a flowchart of the tabular data search method according to the embodiment of the present invention. In the present embodiment, first, a record having a member name whose member name starts with S is searched from the baseball lover group table (step 701), and then, a baseball lover group table including only the record of the search result, That is, when the first table and the second table, which is the game schedule table, are virtually joined by the “support team” and the “ball team” (steps 702 to 705), a table virtually generated by the virtual join is obtained. . This table coincides with the result of searching the virtual join table in FIG. 2 for records having member names whose member names begin with S. As described above, according to the embodiment of the present invention, by executing a virtual join using a result searched in advance, the same result as that obtained when a search is performed on a join table generated by the join can be obtained. The table before the virtual join can be pre-assigned with an index such as a B-Tree or a bit mac index, or can be assigned a tag for speeding up other searches. According to this, it becomes possible to realize the search of the virtual join table at high speed.

  FIG. 8 is an explanatory diagram of a sorting method using the tabular data combining method according to the embodiment of the present invention. FIG. 9 is a flowchart of the tabular data sorting method according to the embodiment of the present invention. In the present embodiment, first, records are sorted by sorting the member names in alphabetical order from the baseball club member table (step 901), and then the baseball club members table in which the record order is rearranged, that is, When the first table and the second table, which is the game schedule table, are virtually joined by the “support team” and the “ball team” (steps 902 to 905), a table virtually generated by the virtual join is obtained. This table matches the result of rearranging the virtual join table of FIG. 2 so that the member names are in alphabetical order. As described above, according to the embodiment of the present invention, by executing a virtual join using a result that has been sorted in advance, the same result as that obtained when sorting is performed on the join table generated by the join can be obtained. The table before the virtual join can be pre-assigned with an index such as a B-Tree or a bit mac index, or can be assigned a tag for speeding up other sorts. According to this, it is possible to realize sorting of the virtual join table at high speed.

It is a block diagram which shows the hardware constitutions of the computer system which implements this invention. It is explanatory drawing of the virtual table production | generation by this invention. It is a flowchart of embodiment of the combination method of the tabular data of this invention. It is explanatory drawing of the process which outputs sequentially the pair of the object record and record group by embodiment of this invention. It is explanatory drawing of the index data for speeding up the search of the record group by other embodiment of this invention. It is explanatory drawing of the search method using the combination method of the tabular data by embodiment of this invention. It is a flowchart of the search method of tabular data by embodiment of this invention. It is explanatory drawing of the sorting method using the combination method of the tabular data by embodiment of this invention. It is a flowchart of the sort method of the tabular data by embodiment of this invention.

Explanation of symbols

10 Computer system 12 CPU
14 RAM
16 ROM
18 Fixed storage device 20 CD-ROM driver 22 I / F
24 input device 26 display device

Claims (14)

First tabular data and second tabular data represented as an array of records including item values corresponding to information items are prepared, and the first tabular data and the second tabular data are prepared. A procedure for determining items of information belonging to the same as key items,
Obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
A procedure for identifying a record group including an item value that satisfies a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
Outputting a pair of the record to be processed and the record group included in the second tabular data, the processing target record included in the first tabular data;
A method for joining tabular data having The second tabular data is tabular data in which an index is given to the key item,
The procedure for specifying the record group specifies the record group by a search using the index assigned to the key item of the second tabular data.
The tabular data combination method according to claim 1. Prior to the procedure for specifying the record group, a procedure for generating index data for designating a record including the item value from the item value for each item value corresponding to the key item with respect to the second tabular data Further comprising
The procedure for specifying the record group is to specify the record group by a search using the index data of the second tabular data.
The tabular data combination method according to claim 1. The method of combining tabular data according to claim 3, wherein the procedure of generating the index data includes a procedure of sorting the index data based on the item values corresponding to the key item values. The combination of tabular data according to claim 3, wherein the step of generating the index data includes a step of adding an index to the item value portion of the index data generated for each item value corresponding to the key item. Method. 6. The tabular data combination method according to claim 3, wherein the index data is a pointer to a record. 6. The tabular data combination method according to claim 3, wherein the index data is a record number. 6. The tabular data combination method according to claim 3, wherein the index data is associated with an address on a memory in which a record is stored. The first tabular data is prepared by retrieving a record including item values that meet the search condition from initial tabular data represented as an array of records including item values corresponding to information items. The tabular data combination method according to any one of claims 1 to 8, which is tabular data. The first tabular data is tabular data prepared by rearranging records of initial tabular data represented as an array of records including item values corresponding to information items with respect to a predetermined item. 9. A method for combining tabular data according to any one of claims 1 to 8. The first tabular data is prepared by retrieving records including item values that meet the search condition from initial tabular data represented as an array of records including item values corresponding to information items. Preparing a second tabular data represented as an array of records containing item values corresponding to the items;
A procedure for determining, as a key item, an item of information belonging to both the first tabular data and the second tabular data;
Obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
A procedure for identifying a record group including an item value that satisfies a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
A procedure for generating a pair of the record to be processed and the record group included in the second tabular data included in the first tabular data;
Search method for tabular data having First tabular data is prepared by rearranging records of initial tabular data represented as an array of records including item values corresponding to information items with respect to a predetermined item, and items corresponding to information items Preparing a second tabular data represented as an array of records containing values;
A procedure for determining, as a key item, an item of information belonging to both the first tabular data and the second tabular data;
Obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
A procedure for identifying a record group including an item value that satisfies a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
A procedure for generating a pair of the record to be processed and the record group included in the second tabular data included in the first tabular data;
Sorting method of tabular data having First tabular data and second tabular data represented as an array of records including item values corresponding to information items are prepared, and the first tabular data and the second tabular data are prepared. Means for determining as a key item information items belonging to
Means for obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
Means for specifying a record group including an item value satisfying a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
Means for outputting a pair of the record to be processed included in the first tabular data and the record group included in the second tabular data;
A tabular data combining device having: First tabular data and second tabular data represented as an array of records including item values corresponding to information items are prepared, and the first tabular data and the second tabular data are prepared. A procedure for determining items of information belonging to the same as key items,
Obtaining a key item value corresponding to the key item of the processing target record included in the first tabular data;
A procedure for identifying a record group including an item value that satisfies a predetermined matching condition with the key item value with respect to the key item, from records included in the second tabular data;
Outputting a pair of the record to be processed and the record group included in the second tabular data, the processing target record included in the first tabular data;
A program that causes a computer to execute.

Images