A data table foreign key determination method, electronic equipment and storage medium

Abstract

The application provides a data table foreign key determination method, an electronic device and a storage medium, and relates to obtaining semantic similarity η between an annotation ZA corresponding to any field name TA of a first data table and an annotation ZB of any field name TB of a second data table AB ; obtaining attribute data similarity λ between an attribute data list corresponding to the TA and an attribute data list corresponding to the TB AB ; obtaining an initial field name pair list C; obtaining a matching degree and an inflation rate; obtaining a historical query proportion corresponding to each field name in each initial field name pair in the C; the query proportion is determined according to a historical query statement; determining a correlation degree of each initial field name pair; determining the initial field name pair with a correlation degree greater than a preset correlation degree threshold as a foreign key of the first data table and the second data table; and effectively solving the problem of low manual determination efficiency when the number of data tables is large.

Description

Technical Field

[0001] This invention relates to the field of foreign key determination technology for data tables, and in particular to a method, electronic device, and storage medium for determining foreign keys in data tables. Background Technology

[0002] With the ongoing advancement of digital transformation, enterprises have generally deployed multiple ERP systems to support business operations such as procurement and sales. However, most existing ERP systems are unable to effectively integrate and analyze historical procurement and sales data, resulting in insufficient overall analysis of supply chain efficiency within enterprises, delayed decision-making, and low operational efficiency.

[0003] In the data management process of ERP systems, it is often necessary to integrate numerous data tables such as purchase orders and sales orders. For example, the data from these data tables needs to be merged into a single wide table. When there are a large number of data tables involved, it is first necessary to determine the relationships between the data tables to determine whether they can be merged. When determining the relationships between tables, it is usually necessary to manually select the related field names, i.e., foreign keys. This is not only inefficient, but also prone to judgment errors due to human factors, making it difficult to meet the enterprise's requirements for high efficiency and accuracy in data integration and analysis. Summary of the Invention

[0004] To address the aforementioned technical problems, the technical solution adopted by this invention is as follows:

[0005] According to a first aspect of this application, a method for determining foreign keys in a data table is provided, the method comprising the following steps:

[0006] S100, obtain the semantic similarity η between the annotation ZA corresponding to any field name TA in the first data table and the annotation ZB of any field name TB in the second data table. AB The first and second data tables each contain several rows and several columns. Each row corresponds to one piece of material information, each column corresponds to a field name, and each field name corresponds to the attribute data of each material.

[0007] S200, obtain the attribute data similarity λ between the attribute data list corresponding to TA and the attribute data list corresponding to TB. AB .

[0008] S300, if η AB >η0 or λ AB If η > λ0, then TA and TB are determined as the initial field name pair, resulting in the initial field name pair list C; η0 is the preset semantic similarity threshold, and λ0 is the preset attribute data similarity threshold.

[0009] S400, obtain the matching degree between the first data table and the second data table, and the expansion rate corresponding to each initial field name pair in C; the matching degree is determined based on the number of field names that exist simultaneously in the first data table and the second data table; the expansion rate is determined based on the number of attribute data corresponding to TA that exist in the second data table.

[0010] S500 retrieves the historical query percentage corresponding to each field name in each initial field name pair in C; the query percentage is determined based on the historical query statements.

[0011] S600, determine the correlation of each initial field name pair based on the matching degree between the first data table and the second data table, the expansion rate corresponding to each initial field name pair in C, and the proportion of historical queries.

[0012] S700: Initial field name pairs with a correlation degree greater than a preset correlation degree threshold are identified as foreign keys in the first and second data tables.

[0013] According to another aspect of this application, a non-transitory computer-readable storage medium is also provided, wherein at least one instruction or at least one program is stored in the storage medium, and the at least one instruction or at least one program is loaded and executed by a processor to implement the foreign key determination method of the data table described above.

[0014] According to another aspect of this application, an electronic device is also provided, including a processor and the aforementioned non-transitory computer-readable storage medium.

[0015] The present invention has at least the following beneficial effects:

[0016] The foreign key determination method for data tables in this invention achieves automated determination of foreign keys between data tables through a two-dimensional initial screening using annotation semantic similarity and attribute data similarity, combined with multi-dimensional correlation verification based on inter-table matching degree, field expansion rate, and historical query ratio. This method effectively replaces traditional manual foreign key screening, solving the problem of low efficiency in manual judgment when dealing with a large number of data tables. Simultaneously, through multi-layered verification using semantic features, data features, and historical usage scenarios, it reduces judgment errors caused by human factors, improving the accuracy of foreign key determination. Furthermore, the solution provides reliable correlation basis for integrating multiple data tables such as purchase orders and sales orders, helping to solve the problem of existing ERP systems struggling to effectively integrate historical procurement and sales business data, and meeting enterprises' needs for high efficiency and accuracy in data integration and analysis. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0018] Figure 1 A flowchart of a method for determining foreign keys in a data table provided in an embodiment of the present invention. Detailed Implementation

[0019] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0020] It should be noted that, based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Furthermore, this device and / or practice the method can be implemented using other structures and / or functionalities besides one or more of the aspects set forth herein.

[0021] Example 1:

[0022] The following will refer to Figure 1 The flowchart shown illustrates a method for determining foreign keys in a data table, introducing one such method.

[0023] The method for determining foreign keys in this data table may include the following steps:

[0024] S100, obtain the semantic similarity η between the annotation ZA corresponding to the specified field name TA in the first data table and the annotation ZB of the specified field name TB in the second data table. AB The first and second data tables each contain several rows and several columns. Each row corresponds to one piece of material information, each column corresponds to a field name, and each field name corresponds to the attribute data of each material.

[0025] In this embodiment, the scenario where the method is applied is multiple data tables in an ERP system. For example, in the civil aviation field, the procurement, sales, invoicing, etc. of materials are usually involved, and each link corresponds to several data tables to record the procurement and sales data of materials. In order to facilitate the unified management of numerous data, it is necessary to fuse the above different data tables and establish a data warehouse. It is necessary to determine whether two data tables can be fused.

[0026] For the first data table (such as the ERP procurement data table) and the second data table (such as the ERP sales data table), from the metadata information of the data table, extract the annotation ZA (assuming "the code that uniquely identifies the procurement material, consisting of the department code + serial number, a total of 10 digits") corresponding to the specified field name TA (such as "material code" in the procurement table) of the first data table, and the annotation ZB (assuming "the unique number that marks the identity of the sold goods, in the format of classification code + serial number, with a length of 10 digits") corresponding to any field name TB (such as "goods number" in the sales table) of the second data table. The specified field name can be any field name in the corresponding data table.

[0027] Perform cleaning operations on ZA and ZB respectively to remove punctuation marks and meaningless modifiers (such as "of", "by"), and obtain the core semantic text - after processing, ZA becomes "unique identifier of procurement material, code, department code, serial number, 10 digits", and after processing, ZB becomes "mark the identity of sold goods, unique number, classification code, serial number, 10 digits".

[0028] Semantic vector conversion: Adopt a lightweight pre-trained semantic model (such as BERT-tiny) to convert the processed ZA and ZB into semantic feature vectors with a fixed dimension (such as 768 dimensions) respectively. The model captures the core association between "material code" and "goods number" - both pointing to "the unique identifier of materials" - through learning the context association of the words in the text.

[0029] Similarity calculation: Use the cosine similarity formula to calculate the similarity η of the two semantic vectors AB , and the formula is η AB =(vector ZA vector ZB) / (||vector ZA||×||vector ZB||), and finally obtain a quantified semantic similarity value (such as 0.82).

[0030] This step breaks through the limitation of "superficial differences in field names" by mining the semantic association of field annotations. For example, it can identify the core meaning consistency between "material code" and "goods number", and avoid misjudging as unrelated fields by humans only because of different names. At the same time, it realizes automated calculation with the help of a semantic model, replacing the traditional mode of manual reading of annotations and subjective judgment, reducing human understanding bias, and providing an accurate semantic basis for subsequent foreign key determination.

[0031] S200, obtain the attribute data similarity λ between the attribute data list corresponding to TA and the attribute data list corresponding to TB. AB .

[0032] Furthermore, step S200 includes the following steps:

[0033] S210, respectively obtain the attribute data list D_A corresponding to TA in the first data table and the attribute data list D_B corresponding to TB in the second data table.

[0034] In this embodiment, the business scenarios of the first and second data tables are first clarified (e.g., the purchasing data table and sales data table in an ERP system, respectively), and the field names to be analyzed are determined as TA-TB (e.g., TA is the "material code" in the purchasing table, and TB is the "product number" in the sales table). Using a database query statement (e.g., SQL's SELECT material code FROM purchasing table;), all attribute data corresponding to TA are extracted from the first data table to form a complete attribute data list D_A (example: ["CG010001","CG010002","CG020001",...,"CG100000"]). Using the same method, all attribute data corresponding to TB are extracted from the second data table to form an attribute data list D_B (example: ["XH010001","CG010001","XH020001",...,"XH080000"]). During the extraction process, null values ​​and invalid data (such as codes with abnormal character lengths) need to be filtered to ensure the validity of the data in D_A and D_B.

[0035] This step provides a complete and clean data source for subsequent data similarity calculations, avoiding interference from null or invalid data in the similarity results. By accurately locating the attribute data corresponding to the field names, a direct relationship between "field name" and "data content" is established, providing an objective data basis for determining whether two fields represent the same entity, which is a prerequisite for quantifying attribute data similarity.

[0036] S220, if the number of data entries N_A of D_A or the number of data entries N_B of D_B is greater than the preset data volume threshold N_th, then random sampling is performed on D_A and D_B respectively to obtain the sampling attribute data list S_A corresponding to D_A and the sampling attribute data list S_B corresponding to D_B.

[0037] Based on the requirements of data processing efficiency and accuracy, a preset data volume threshold N_th is set (usually set to 1000 records, which can be adjusted according to system performance). At the same time, sampling parameters are set—sampling ratio k (range 5%-20%) and minimum sample size S_min (not less than 100 records). When sampling, the larger value of the two calculated results is taken as the target sample size (for example, when k=10%, if the data volume is 15000 records, the 10% sampling size is 1500 records, which is greater than S_min=100, so the target sample size is 1500; if the data volume is 800 records, the 10% sampling size is 80 records, which is less than S_min, so the target sample size is 100).

[0038] Sampling execution: Determine whether the number of data entries N_A in D_A and the number of data entries N_B in D_B exceed N_th. If N_A = 15000 > 1000, use a random number generation algorithm to sample D_A without replacement, extracting 1500 data entries to form a sampling list S_A; if N_B = 12000 > 1000, similarly extract 1200 data entries to form S_B. If the number of entries in a certain data list does not exceed the threshold (e.g., N_A = 800 < 1000), then directly use that list as the sampling list (S_A = D_A).

[0039] This step addresses the issues of time-consuming and resource-intensive computation of large-scale data through a sampling mechanism, significantly improving the efficiency of subsequent similarity calculations. Simultaneously, by setting a minimum sample size and a reasonable sampling ratio, the representativeness of the sampled data is ensured, avoiding similarity calculation biases caused by excessively small sample sizes, thus achieving a balance between efficiency and accuracy.

[0040] S230, if the attribute data types corresponding to TA and TB are numerical data, then calculate the statistical feature similarity between S_A and S_B; the statistical features include mean, variance, and quartiles.

[0041] By analyzing the metadata or data format of the data tables, it was determined that the attribute data types of TA and TB are numeric (e.g., TA is the "Purchase Unit Price" in the Purchase table, with data of [12.5, 28.0, 15.3, ...]; TB is the "Sales Unit Price" in the Sales table, with data of [18.8, 35.5, 20.1, ...]).

[0042] Calculate the core statistical characteristics of sampling lists S_A and S_B respectively: mean (reflecting the central tendency of the data), variance (reflecting the dispersion of the data), and quartiles (reflecting the distribution interval of the data). Calculate the similarity using the "feature difference normalized summation" method, with the formula: Statistical Feature Similarity = Σ(1-|f_A|S_B ... c -f_B c | / max(f_A c ,f_B c)) / d, where c ranges from 1 to d; (d is the number of statistical features, f_A c Let f_B be the c-th eigenvalue of S_A. c (where c is the c-th feature value of S_B). Substituting the example data, we get: (1-|16.2-22.8| / 22.8+1-|32.5-45.1| / 45.1+1-|20.1-27.5| / 27.5) / 3≈0.721, which is the statistical feature similarity between S_A and S_B.

[0043] This step, tailored to the characteristics of numerical data, selects statistical features that reflect the overall distribution of the data for similarity calculation, rather than simply comparing individual data points. This avoids the impact of outliers or price fluctuations on the results. Normalization eliminates differences in numerical magnitude, allowing statistical features from different dimensions to directly participate in the calculation, ensuring the objectivity and reasonableness of the similarity results and providing an accurate basis for determining the association of numerical fields.

[0044] S240, if the attribute data types corresponding to TA and TB are character data, then calculate the matching similarity ζAB between S_A and S_B = (TN1 + TN2) / max(TNA, TNB); TN1 is the number of identical data entries in S_A and S_B, TN2 is the number of data entries in S_A and S_B whose string cosine similarity is greater than or equal to the preset cosine similarity threshold; TNA is the number of data entries in S_A, and TNB is the number of data entries in S_B; max() is the preset maximum value function.

[0045] The attribute data of TA and TB are determined to be of character type (e.g., TA is the "Material Code" in the purchasing table, and S_A data is ["CG010001", "CG010002",...]; TB is the "Product Number" in the sales table, and S_B data is ["CG010001", "XH010001",...]). The cosine similarity threshold of the strings is preset to 0.8, and the n-gram segmentation method with n=2 is used (the string is split into two consecutive character combinations, such as "CG010001" split into "CG", "G0", "01", "10", "00", "00", "01").

[0046] Count the number of data entries that are completely identical in S_A and S_B, TN1 (Example: S_A and S_B both contain “CG010001”, “CG020001”, etc., a total of 320 entries); for data that are not completely identical, convert them into feature vectors through ngram segmentation and calculate the cosine similarity, and count the number of data entries with a similarity ≥ 0.8, TN2 (Example: “CG010001” and “CG010002” have a vector similarity of 0.82 after segmentation, and are included in TN2, a total of 180 entries).

[0047] Similarity calculation: Calculated using the formula ζAB=(TN1+TN2) / max(TNA,TNB). If TNA=1500 and TNB=1200, substituting them, we get ζAB=(320+180) / 1500≈0.333. This value is the matching similarity of character data.

[0048] This step employs a hierarchical matching logic to address format differences in character-based data (such as abbreviations, full names, and mixed Chinese and English). Complete matching ensures the relevance of core data, while cosine similarity matching identifies characters that are semantically related but slightly different in expression, overcoming the limitation of traditional character matching that only matches identical characters. The ngram segmentation method effectively captures local features of strings, improving the accuracy of similar string recognition and providing flexible and reliable support for determining the association of character-based fields.

[0049] S250, repeat steps S220-S240 several times, and take the average of the calculation results as the attribute data similarity λAB between TA and TB.

[0050] The preset number of repeated sampling calculations, t (usually 3-5 times, balancing accuracy and efficiency), is used. The sampling and similarity calculation are repeated t times according to steps S220-S240. Each sampling uses an independent random seed to ensure sample randomness. Example: t=3 calculations yield results of 0.333, 0.351, and 0.342. The sum of the three results is then taken as the average, i.e., λ. AB = (0.333 + 0.351 + 0.342) / 3 ≈ 0.342, which is the final attribute data similarity between TA and TB.

[0051] This step, through repeated sampling and averaging of the results, effectively reduces the random error of a single sampling and avoids similarity bias caused by the special distribution of samples in a particular sampling, thus ensuring the final λ... AB The values ​​are more stable and more closely reflect the true similarity of the data. The mechanism of repeated calculation enhances the reliability of attribute data similarity results, providing a more accurate quantitative basis for the subsequent selection of initial field name pairs.

[0052] S300, if η AB >η0 or λ AB If η > λ0, then TA and TB are determined as the initial field name pair, resulting in the initial field name pair list C; η0 is the preset semantic similarity threshold, and λ0 is the preset attribute data similarity threshold.

[0053] In this embodiment, the semantic similarity threshold η0=0.7 and the attribute data similarity threshold λ0=0.6 are preset based on the business scenario (the thresholds can be calibrated based on historical data).

[0054] Validate each TA-TB field name pair (such as "Material Code-Item Number", "Purchase Quantity-Sales Amount", "Supplier Code-Customer Number", etc.). If η AB >η0 or λ AB If the value is greater than λ0, then it is included in the initial field name pair list C.

[0055] The final list C contains field name pairs that meet the criteria, such as "material code - product number", "purchase price - sales price", and "material name - product name".

[0056] This step uses a dual filtering rule of "semantic or data" to quickly filter out obviously irrelevant field name pairs, significantly reducing the scope of subsequent calculations and avoiding unnecessary resource consumption. At the same time, the "OR" logic is designed to accommodate different scenarios—some field comments are concise, leading to η... AB The data may be low, but highly similar data can still be retained, which improves the comprehensiveness of the initial screening and lays the foundation for accurate judgment in the future.

[0057] S400, obtain the matching degree between the first data table and the second data table, and the expansion rate corresponding to each initial field name pair in C; the matching degree is determined based on the number of field names that exist simultaneously in the first data table and the second data table; the expansion rate is determined based on the number of attribute data corresponding to TA that exist in the second data table.

[0058] Furthermore, step S400 includes the following steps:

[0059] S410, obtain the number of field names that exist in both the first data table and the second data table, NUM1, and the total number of field names in the first data table, NUM.

[0060] In this embodiment, the criteria for determining "field names that coexist in both the first and second data tables" are first clarified—it is not merely that the field names are completely identical, but rather that the results of S100 (annotation semantic similarity) and S200 (attribute data similarity) are combined to determine field names that "represent the same entity" (e.g., "material code" in the first data table and "product number" in the second data table are considered to "coexist"). Taking an ERP purchasing data table as an example and an ERP sales data table as an example:

[0061] Calculate the total number of field names (NUM) in the first data table: Analyze all field names in the purchasing table, including material code, purchase quantity, purchase unit price, supplier code, purchase date, material name, warehouse, and purchase order number, totaling 8, i.e., NUM=8;

[0062] The number of field names that "exist simultaneously" is NUM1: This filters out field name pairs that represent the same entity in the purchasing and sales tables, such as "material code-product number", "purchase price-sales price", and "material name-product name". There are a total of 3 groups, so NUM1=3.

[0063] During the statistical process, non-core business fields such as empty fields and system-reserved fields should be excluded to ensure that the data focuses on the fields that are actually involved in business analysis.

[0064] This step clarifies the core basis for matching degree calculation, replacing simple textual matching of field names with the criterion of "representing the same entity." This aligns with the business nature of data table integration and avoids the omission of "coexisting" fields due to differences in field name descriptions. Simultaneously, excluding non-core fields ensures the validity of the basic data, providing an objective and accurate data source for subsequent matching degree calculations.

[0065] S420, based on NUM1 and NUM, determine the matching degree ρ=NUM1 / NUM between the first data table and the second data table.

[0066] Based on NUM1 and NUM obtained from S410, the matching degree between the two tables is calculated using the formula ρ=NUM1 / NUM. The core principle is to use the first data table as a benchmark and quantify the coverage of its fields in the second data table. Continuing the example above: the matching degree ρ=3 / 8=0.375. This value reflects that 37.5% of the core fields in the purchasing table can find corresponding related fields in the sales table, demonstrating the close business relationship between the two tables. If, in a real-world scenario, the total number of fields in both tables needs to be used as the benchmark, the formula can be adjusted to ρ=NUM1 / ((NUM+total number of fields in the second data table) / 2). A pre-defined calculation benchmark is required based on business needs, and this benchmark must remain consistent throughout the entire process.

[0067] This step quantifies the business relationship between two data tables from a "table-level macro perspective," overcoming the limitations of analyzing a single field and allowing subsequent relationship calculations to be made in conjunction with the overall business scenario. The quantified matching results can quickly distinguish between "highly correlated" (such as the procurement and sales table) and "weakly correlated" (such as the purchase table and the attendance table) data tables, avoiding excessive computational resources invested in weakly correlated data tables and improving the overall efficiency of foreign key determination.

[0068] S430, for any initial field name pair ZD=(A, B) in C, retrieve the deduplicated attribute data corresponding to A, resulting in the attribute data list SA=(SA1, SA2, ..., SA2) of A. i SA n ), i=1,2,…,n; SA iLet be the i-th deduplicated attribute data corresponding to A, and n be the number of deduplicated attribute data corresponding to A; A is the field name determined from the first data table, and B is the field name determined from the second data table.

[0069] For any field name pair ZD=(A,B) in the initial field name pair list C (e.g., A is the "Material Code" in the purchasing table, and B is the "Item Number" in the sales table):

[0070] Extract all original attribute data corresponding to field name A from the first data table (e.g., the original data of "Material Code" in the purchasing table contains duplicate values: ["CG010001","CG010001","CG010002",..., "CG100000"]).

[0071] The original data is deduplicated to remove duplicate attribute data, resulting in a unique set of values, i.e., the attribute data list SA corresponding to A. In the example, after deduplication, SA = ["CG010001", "CG010002", ..., "CG100000"], where n is the number of unique material codes after deduplication (e.g., 10000).

[0072] During the deduplication process, the original format of the data (such as character length and encoding rules) must be preserved to avoid data distortion caused by format conversion.

[0073] This step eliminates the interference of duplicate data in the inflation rate calculation by deduplicating data, focusing on the distribution characteristics of "unique attribute values" to ensure that the subsequent inflation rate can truly reflect the coverage of a single attribute value between the two tables, rather than the cumulative effect of duplicate data. At the same time, it preserves the requirements of the original data format, making the attribute data matching more in line with the actual business scenario and avoiding statistical bias caused by format issues.

[0074] S440, based on the quantity of each attribute data in SA in the second data table, determine the inflation rate corresponding to each attribute data in SA, and obtain the inflation rate list α = (α1, α2, ..., α...). i , …, α n ); α i for SA i The corresponding expansion rate; α i =NB i / NA i NA i for SA i The quantity in the first data table, NB i for SA i The quantity in the second data table.

[0075] For each attribute data in SA i(e.g., SA1=CG010001):

[0076] Statistics SA i The quantity NA in the first data table i This refers to the number of times the material code appears in the purchase order table (e.g., CG010001 corresponds to 50 purchase records, NA...). i =50); Statistical SA i The quantity NB in ​​the second data table i This refers to the number of times the material code appears in the sales table (e.g., CG010001 corresponds to 35 sales records, NB...). i =100); according to formula α i =NB i / NA i Calculate SA i The corresponding expansion rate (α1=100 / 50=2 in the example); if SA i No matching data found in the second data table (NB) i If =0), then α i =0, to ensure no data is missing.

[0077] This step calculates the inflation rate in a refined manner from the "single attribute value dimension," accurately capturing the distribution differences of different attribute values ​​between the two tables and avoiding the distortion of inflation rate caused by averaging the overall data. By quantifying the cross-table coverage of each attribute value, it can identify "core attribute values" (high inflation rate) and "marginal attribute values" (low inflation rate), making subsequent field-level inflation rate calculations more aligned with actual business needs and providing refined support for determining the association value of field pairs.

[0078] S450, according to α i Determine the expansion rate corresponding to A as μ = (1 / n) × ∑ n i=1 α i .

[0079] Based on the expansion rate list α obtained from S440, according to the formula μ=(1 / n)×∑ n i=1 α i Calculate the overall inflation rate corresponding to field name A, that is, for all SA. i The expansion rate is taken as the arithmetic mean.

[0080] This step aggregates the inflation rate of individual attribute values ​​into an overall inflation rate at the field level, realizing the transformation from "attribute value dimension" to "field dimension", allowing the inflation rate to directly participate in the subsequent calculation of the correlation between field pairs.

[0081] S500 retrieves the historical query percentage corresponding to each field name in each initial field name pair in C; the query percentage is determined based on the historical query statements.

[0082] Furthermore, step S500 includes the following steps:

[0083] S510, obtain C = (C1, C2, ..., C... j C m ), j=1,2,…,m; C j Let m be the j-th initial field name pair in C, and m be the number of initial field name pairs in C; j =(C j，A C j，B ); C j，A C is the initial field name determined from the first data table. j，B These are the initial field names determined from the second data table.

[0084] First, clarify the source of the initial field name pair list C—this list is the output of S300, containing all pairs that satisfy the "annotation semantic similarity η" condition. AB >η0 or attribute data similarity λ AB >λ0” field name pairs. Considering the ERP system's purchasing and sales data scenario (the first data table is the purchasing table, and the second table is the sales table), first break down the hierarchical structure of list C:

[0085] List C is an ordered set C = (C1, C2, C3), where m = 3 (representing 3 initial field name pairs);

[0086] Each field name corresponds to C j All are composed of "first table field name C" j,A "and "Second table field name C j,B "Composition, namely C" j =(C j,A C j,B A specific example is: C1 = (Purchase Table "Material Code", Sales Table "Item Number").

[0087] S520, obtain C j，A XC represents the historical query count for the query criteria. j，A and with C j，B XC represents the historical query count for the query criteria. j，B .

[0088] Using the structured field names to access C j Based on this, the query count of the target field name is extracted from the database query log. The core steps are:

[0089] Determine the scope of the query logs: Preset the time dimension (e.g., the last 6 months, covering a sufficient business cycle), and extract all SQL query statement logs for the first data table (purchase table) and the second data table (sales table) within this period (example: a total of 1200 logs).

[0090] Precisely filter target queries: for each C j Filter each containing C j,A (Field names in the first table) and C j,B The query statement for (second table field names) – for example, a purchase table query statement that filters for C1=("material code", "product number") and includes "WHERE material code=" (denoted as XC) 1,A ), and sales table query statements containing "WHERE product number=" (referred to as XC) 1,B );

[0091] Query count: This function deduplicatively counts the number of queries submitted by the same user after filtering (excluding duplicate submissions of the same query). The result is: XC 1,A =180 ("Material code" was queried 180 times), XC 1,B =150 ("Item Number" was queried 150 times); XC 2,A =120 (“Purchase Unit Price”), XC 2,B =100(“Selling Price”); XC 3,A =140 ("Material Name"), XC 3,B =110 ("Product Name").

[0092] This step, through the logic of "time range limitation + precise statement filtering + deduplication counting," ensures the authenticity and relevance of historical query counts. It focuses on the core fields in the initial field name pairs, eliminating interference from irrelevant field query logs, allowing the statistical results to directly reflect the frequency of target field usage in actual business operations. Simultaneously, it separately counts C... j,A and C j,B The number of queries provides independent data support for subsequent calculations of the relative importance of the two fields.

[0093] S530, according to XC j，A and XC j，B Determine C j，A The corresponding query percentage β j，A =XC j，A / ∑ m j=1 XC j，A And C j，B The corresponding query percentage β j，B =XC j，B / ∑ mj=1 XC j，B .

[0094] Based on the query count obtained from S520, the query percentage for each field name is calculated according to the principle of "normalization of fields within the same table". The core is to convert the absolute query count into a relative percentage by using the "sum of query counts for all target fields in the same table" in the denominator. This is specifically divided into two types of calculations:

[0095] The proportion of fields in the first data table (β) j,A Calculate: The denominator is all C j,A The total number of queries, with the numerator being a single C. j,A Query count XC j,A .

[0096] The proportion of fields in the second data table (β) j,B Calculate: The denominator is all C j,B The total number of queries, with the numerator being a single C. j,B Query count XC j,B .

[0097] This step calculates the query percentage through "table normalization," eliminating the impact of differences in the total query frequency between the two tables (e.g., the total number of queries in the procurement table may be much higher than that in the sales table), making the query popularity of fields in different data tables comparable. The query percentage quantifies the relative importance of a single field within the target field of the same table, preventing fields with "high absolute query counts but low percentages" from being overemphasized. This injects a "business usage popularity" dimension into subsequent relational calculations, making foreign key determination more aligned with actual business needs.

[0098] S600, determine the correlation of each initial field name pair based on the matching degree between the first data table and the second data table, the expansion rate corresponding to each initial field name pair in C, and the proportion of historical queries.

[0099] Furthermore, step S600 includes the following steps:

[0100] S610, based on ρ, C j，A The corresponding normalized value of the expansion rate μ j，A β j，A and β j，B Determine C j The corresponding correlation θ j =τ1×ρ+τ2×μ j，A +τ3×β j，A +τ4×β j，B τ1, τ2, τ3 and τ4 are the first preset weight, the second preset weight, the third preset weight and the fourth preset weight, respectively; τ1+τ2+τ3+τ4=1.

[0101] normalized expansion rate μj,A C based on S450 j,A Original expansion rate μ j Through min-max normalization (μ) j,A =(μ j -All C j,A (Minimum expansion rate) / (All C) j,A (Maximum expansion rate minus minimum expansion rate)

[0102] The weights τ1, τ2, τ3, and τ4 need to be set based on the importance of each indicator in the business scenario. The core principle is that "the more critical the indicator is for determining field association, the higher its weight," and τ1+τ2+τ3+τ4=1 must be satisfied. Taking the scenario of determining foreign keys in ERP procurement and sales data tables as an example (prioritizing data association, then business usage frequency, and finally overall table association):

[0103] First preset weight τ1 (matching degree ρ): 0.2 (the overall relationship between tables is the basic dimension, and the weight is moderate);

[0104] The second preset weight τ2 (normalized value of expansion rate μ) j,A ): 0.4 (Cross-table data coverage directly reflects the actual value of field associations and has the highest weight).

[0105] The third preset weight τ3 (C) j,A Query percentage β j,A ): 0.2 (Business usage frequency of the first data table field);

[0106] Fourth preset weight τ4 (C j,B Query percentage β j,B ): 0.2 (Business usage frequency of the second data table field);

[0107] Weights can be calibrated for accuracy using the Analytic Hierarchy Process (AHP) or historical foreign keys, adapting to the business needs of different industries (e.g., increasing the weight of queries in financial data scenarios).

[0108] This step calculates the correlation degree through a weighted fusion of multi-dimensional indicators, overcoming the limitations of single-indicator judgment. It ensures the overall business rationality of field associations through inter-table matching degree ρ, reflects the actual data-level correlation strength through expansion rate, and incorporates the business usage frequency dimension by combining query proportion, ensuring that the correlation degree results balance technical rationality and business practicality. The flexibility of weight setting can adapt to the foreign key determination needs of different industries and different types of data tables, while normalization eliminates the interference of differences in various indicator dimensions, ensuring the fairness and comparability of the calculation results. The final quantified correlation degree provides accurate and comprehensive quantitative basis for subsequent foreign key determination, replacing the traditional manual subjective judgment mode, significantly reducing the probability of foreign key misjudgment, and improving the accuracy and reliability of data table foreign key determination.

[0109] S700: Initial field name pairs with a correlation degree greater than a preset correlation degree threshold are identified as foreign keys in the first and second data tables.

[0110] This step, through final threshold filtering, outputs accurate foreign key results, replacing the traditional manual verification and judgment method, significantly improving the efficiency of determining foreign keys across multiple data tables. Simultaneously, the correlation calculation based on multi-dimensional indicators ensures the accuracy of foreign key determination, reducing data table merging errors caused by human misjudgment. This provides a reliable basis for the efficient integration of multiple business data tables such as purchasing and sales tables, helping to solve the data integration challenges of ERP systems.

[0111] In Example 2, in Example 1 above, the first data table and the second data table are actually related. However, the relationship between the two cannot be determined by a single field name, that is, the foreign key between the first data table and the second data table cannot be determined, leading to an error in the foreign key determination of the first data table and the second data table. To avoid this problem, after step S700, the method further includes the following steps:

[0112] S800, if the foreign key of the first data table and the second data table is not determined, then obtain the field names in C that belong to the first data table to obtain the first field name list YA, and obtain the field names in C that belong to the second data table to obtain the second field name list YB.

[0113] When single-field foreign key identification fails, this step quickly focuses on all candidate field names with basic semantic / data similarity and categorizes them according to their data table affiliation, thus defining a precise range for subsequent joint field analysis. This avoids introducing irrelevant fields that increase computational complexity while relying on existing preliminary screening results to ensure the validity of the analysis objects, thereby improving the efficiency of joint foreign key identification.

[0114] S810, Get the list of historical query statements SQ = (SQ1, SQ2, ..., SQ... x , ..., SQy ), x=1,2,…,y;SQ x Let y be the x-th historical query statement retrieved, and y be the number of historical query statements retrieved; SQ x The query criteria must include at least two field names.

[0115] Only historical query statements with "query conditions containing at least two field names" are extracted (excluding single-field queries and full table queries without filtering conditions) to ensure that the statements reflect the business logic of the relationships between fields. This step focuses on historical statements of "multi-field join queries," as these statements naturally reflect the actual understanding of field relationships by business personnel, providing a basis for determining join foreign keys that fits the business scenario. Filtering rules exclude valueless query statements to ensure that all data in the SQL statement reflects field join relationships, avoiding invalid information from interfering with subsequent analysis.

[0116] S820, iterate through SQ, if SQ x If every field name in the query condition is contained in YA or is contained in YB, then SQ will be... x The specified query statement is determined, and the list of specified query statements ZQ = (ZQ1, ZQ2, ..., ZQ...) is obtained. a , ..., ZQ b ), a=1,2,…,b;ZQ a The a-th specified query statement is determined, and b is the number of specified query statements determined.

[0117] Taking example SQ, the query condition fields of SQ1 span YA (material code, purchase date) and YB (product number, sales date), which does not conform to the rules and is excluded; the fields of SQ2 (material code, supplier code) all belong to YA, and the fields of SQ3 (product number, sales date) all belong to YB, both of which conform to the rules.

[0118] This step filters statements using the "field attribution full match" rule, focusing on multi-field join queries within the same data table. These statements directly reflect the business relationship logic of fields within the same table, providing crucial clues for deriving cross-table foreign keys. Excluding cross-table field queries makes the analysis more focused and improves the accuracy of foreign key determination.

[0119] S830, obtain each field name contained in the query conditions of each specified query statement in ZQ, and obtain the field name list set HQ = (HQ1, HQ2, ..., HQ) corresponding to ZQ. a HQ b HQ a For ZQ a The corresponding list of field names; HQ a = (HQ) a，1 HQ a，2HQ a，c HQ a，f（a） ), c=1, 2,…, f (a); HQ a，c For ZQ a The query condition includes the c-th field name, f(a) is ZQ a The number of field names included in the query conditions.

[0120] For each ZQ in ZQ a Extract all field names from the query conditions and arrange them in order into a field name list HQ. a This step transforms the text-based query conditions into a structured list of field names, clearly presenting the fields used in each business query and making the relationships between fields intuitively visible. This structured processing avoids the inefficient operation of repeatedly extracting fields from text, improving the efficiency of composite foreign key analysis.

[0121] S840, HQ a The field names in HQ are defined as a set of composite foreign keys, and HQ is used to define them. a Each field name in the initial field name pair corresponds to a field name belonging to the first or second data table, which is determined as a group of composite foreign keys, resulting in several groups of composite foreign keys.

[0122] Each HQ a The field name is defined as "single table join field group"; according to the correspondence between the initial field name and C, the corresponding field of the single table join field group in another data table is matched to form a cross-table join foreign key group.

[0123] Example: HQ1 = ("Material Code", "Supplier Code") (Purchase Table Field) → Matches the "Material Code" in C to the "Item Number" in the Sales Table. Since "Supplier Code" has no corresponding field, it forms a composite foreign key group 1: (Purchase Table: Material Code, Supplier Code; Sales Table: Item Number).

[0124] HQ2 = ("Product Number", "Sales Date") (Sales Table Fields) → Matches "Product Number" in C with "Material Code" in the Purchasing Table and "Sales Date" with "Purchase Date" in the Purchasing Table, forming a joint foreign key group 2: (Purchasing Table: Material Code, Purchase Date; Sales Table: Product Number, Sales Date).

[0125] This step addresses the core issue of "single-field inability to determine foreign keys" by uncovering hidden joint relationships between two tables through the combined use logic of fields as revealed in business queries. By relying on the initial field name pair correspondences, it ensures that the combined foreign key group is based on existing semantic / data similarity, avoiding unfounded field combinations and improving the rationality of the combined foreign key.

[0126] S850 determines the target joint foreign key based on the weight of each joint foreign key.

[0127] Furthermore, step S850 includes the following steps:

[0128] S851, for any set of joint foreign keys HG = (HG1, HG2, ..., HG... p HG q ), p=1,2,…,q;HG p Let q be the p-th field name in HG, and q be the number of field names in HG.

[0129] This step clarified the field composition and business implications of the composite foreign key through structured analysis. Focusing on the field groups of the first data table simplified the logic for subsequent attribute data retrieval and avoided retrieval biases caused by cross-table field confusion. The upfront business logic validation ensured that the HG possessed the fundamental characteristic of "uniquely identifying business records," providing an effective analytical object for subsequent weight calculations.

[0130] S852, if the field names in HG belong to the first data table and each set of attribute data corresponding to HG is different, then retrieve the w sets of attribute data corresponding to HG from the first data table, and obtain the list of attribute data groups corresponding to HG, HW = (HW1, HW2, ..., HW...). e HW w ), e=1,2,…,w;HW e This refers to the e-th group of attribute data corresponding to HG, obtained from the first data table.

[0131] First, confirm two core conditions: ① All field names in HG belong to the first data table (Purchase Table); ② Each set of attribute data (field combination value) corresponding to HG is unique (e.g., “Material Code=CG010001+Purchase Date=2024-01-01” and “CG010001+2024-01-02” are different combinations and have no repetition). If the conditions are met, execute data extraction.

[0132] Randomly select w sets (w can be preset to 5-10 sets to balance verification accuracy and efficiency) of attribute data combinations corresponding to HG from the first data table to form an ordered list HW.

[0133] Example: w=5, HW=(HW1: Material Code=CG010001, Purchase Date=2024-01-01, HW2: Material Code=CG010001, Purchase Date=2024-01-02, HW3: Material Code=CG010002, Purchase Date=2024-01-01, HW4: Material Code=CG010002, Purchase Date=2024-01-02, HW5: Material Code=CG010003, Purchase Date=2024-01-01); The format of the attribute data should be consistent (e.g., date format is "YYYY-MM-DD", code format is fixed length) to ensure consistency with the search criteria format of the second data table.

[0134] This step focuses on extracting "unique attribute data combinations" to avoid duplicate data interfering with the search results and ensure that each HW group... e Each method can independently verify the uniqueness of the composite foreign key. Randomly sampling multiple sets of data covers attribute combinations from different business scenarios, making subsequent weight calculations more representative. Format calibration eliminates missed detections caused by differences in data formats, ensuring the accuracy of search results.

[0135] S853, with HW e Using the search criteria, search the second data table and determine HW based on the search results. e The corresponding first sub-weight QC e If the number of data entries in the search results is 0, then QC... e =k1; If the number of data entries in the search result is 1, then QC e =k2; If the number of data entries in the search results is greater than 1, then QC e =0; k1 < k2.

[0136] Set sub-weight constants k1 and k2 (satisfying k1 < k2, example: k1=0, k2=1, k1 represents "no matching data", k2 represents "unique matching data"). QC is applied when the search result is greater than 1. e =0 means "the matched data is not unique and cannot be used as a foreign key");

[0137] Cross-table retrieval: by HW per group e To perform an exact search in the second data table (sales table) using the search criteria "HW",... e HG p The corresponding second data table field name = attribute value (e.g., the search conditions for HW1: sales table "product number" = CG010001, sales table "sales date" = 2024-01-01);

[0138] Search result determination and sub-weight assignment:

[0139] HW1 search results: Number of matching data entries in the sales table = 1 → QC e =k2=1;

[0140] HW2 search results: Number of matching data entries in the sales table = 0 → QC e =k1=0;

[0141] HW3 search results: Number of matching data entries in the sales table = 1 → QC e =1;

[0142] HW4 search results: Number of matching data entries in the sales table = 2 → QC e =0;

[0143] HW5 search results: Number of matching data entries in the sales table = 1 → QC e =1;

[0144] Results record: For each group of HW e Label the corresponding 'e' to form a sub-weight list.

[0145] This step quantifies the sub-weights by measuring the number of search results, precisely anchoring the core feature of the composite foreign key—uniqueness (the foreign key must satisfy a one-to-one association, avoiding one-to-many relationships). The differentiated settings of k1 and k2 distinguish between scenarios of "no match" and "unique match," directly assigning a value of 0 to invalid scenarios where "number of matches > 1," quickly filtering attribute combinations that do not meet the uniqueness requirements of the foreign key. This allows the sub-weights to directly reflect the effectiveness of the composite foreign key, providing a precise quantitative basis for subsequent overall weight calculations.

[0146] S854, based on the first sub-weight corresponding to each set of attribute data in HW, determine the first weight φ=∑ w e=1 QC e .

[0147] This step transforms the validation results of multiple sets of attribute data into a quantitative value of the overall validity of the composite foreign key by summing the aggregate subweights, thus avoiding the randomness of validation of a single set of data. The summation logic is simple and intuitive, and can quickly reflect the matching effect of the composite foreign key in multiple sets of business data, while preserving the difference between "valid match (k2)" and "invalid match (k1 / 0)", so that the φ value can accurately characterize the overall usability of the composite foreign key.

[0148] S855, if φ is greater than the preset weight threshold, then HG will be determined as the target joint foreign key.

[0149] The preset weight threshold is combined with the accuracy setting of historical joint foreign key determination. The preset weight threshold is set (example: threshold = 2, that is, at least 2 sets of attribute data achieve unique matching).

[0150] This step uses threshold filtering to ensure that the final target composite foreign key has the characteristic of "validity verified by multiple sets of data," avoiding misjudgments caused by accidental matching of a single set of data. The preset threshold can be adapted to different business scenarios (e.g., increasing the threshold for high-precision scenarios and decreasing the threshold for high-efficiency scenarios), offering high flexibility. The final output target composite foreign key not only meets the uniqueness requirement of a foreign key but has also been verified by multiple sets of data, effectively solving the problem of "a single field cannot determine a foreign key," and ensuring the accuracy of determining the relationship between the two data tables.

[0151] S851-S855, through a complete process of "structured analysis → unique attribute data extraction → sub-weight quantification → overall weight aggregation → threshold filtering," focuses on the core characteristic of "uniqueness" in composite foreign keys, solving the problem of misjudgment of association relationships caused by the failure of single-field foreign key determination. Each step revolves around "data matching validity," replacing subjective human judgment with quantified weights, ensuring both the technical rationality of composite foreign keys (satisfying the one-to-one foreign key association rule) and alignment with actual business scenarios (verification of multiple sets of attribute data). The threshold filtering mechanism further improves the reliability of the results, ensuring that the final determined target composite foreign key accurately reflects the true association relationship between the two data tables, avoiding integration deviations caused by incorrect foreign key determination during data fusion, and improving the accuracy and efficiency of multi-table fusion.

[0152] After step S851 and before step S852, the following steps are also included:

[0153] S856, if the field name in HG belongs to the second data table and each set of attribute data corresponding to HG is different, then retrieve the w sets of attribute data corresponding to HG from the second data table.

[0154] S857: Using any set of attribute data as search criteria, search the first data table and determine the second weight σ corresponding to HG based on the search results.

[0155] S858, if σ is greater than the preset weight threshold, then HG is determined as the target joint foreign key.

[0156] In this embodiment, the methods of S856-S858 are the same as those of steps S851-S855 described above, and will not be repeated here.

[0157] Example 3:

[0158] In the first embodiment above, if TA and TB are selected randomly, there is a high probability that field names with no relation to each other will be selected, resulting in wasted computing power and low computational efficiency. Based on this, the following method is provided:

[0159] Q100: Obtain the name of each field representing the same entity type in the data table to be merged.

[0160] In this embodiment, the set of data tables to be merged is first identified (such as the purchase data table, sales data table, and invoice data table in the ERP system), and the metadata information of each table (field name, field comment, data type) is sorted out.

[0161] A pre-defined dictionary of business entity types (such as “supplier information”, “material information”, “customer information”, “order amount”, etc.) is used to filter out field names that represent the same entity type by semantic matching of field annotations (such as annotations containing “supplier”, “vendor”, “supplier”).

[0162] Summarize all matching field names to form a list. Example: Select "Supplier Information" as the target entity type and extract the following fields: "Full Name of Supplier" and "Abbreviated Name of Supplier" from the Purchase table; "Supplier Name" and "supplier_name" from the Sales table; and "Supp_Brand Name" and "Name Corresponding to Supplier Code" from the Invoice table.

[0163] This step eliminates interference from irrelevant business fields (such as "inbound time" in the purchase table and "shipping address" in the sales table) by focusing on field names of the same entity type. This allows subsequent clustering operations to focus only on the core business entities, avoiding clustering bias caused by the mixing of different entity fields and defining a clear scope boundary for accurate clustering.

[0164] Q200: Cluster all the obtained field names to obtain the first cluster FA, the second cluster FB, and the third cluster FC; wherein, the character type of the field names in FA includes only the first character type, the character type of the field names in FB includes only the second character type, and the character type of each field name in FC includes both the first and second character types; the first character type and the second character type are different.

[0165] The system predefines a first character type (e.g., pure Chinese) and a second character type (e.g., pure English), with clear determination rules: characters containing only Chinese characters (including Chinese punctuation) are classified as the first character type, characters containing only English letters / numbers / English symbols (e.g., underscores) are classified as the second character type, and characters containing both types are classified as a mixed type.

[0166] Field-by-field clustering: Traverse the field names obtained from Q100 and categorize them by character type:

[0167] Pure Chinese field names are grouped into the first cluster FA (Example: FA=["Full Supplier Name","Abbreviated Supplier Name","Supplier Name"]).

[0168] Field names consisting entirely of English characters are grouped into the second cluster FB (example: FB=["supplier_name","vendor_code","supplier_id"]).

[0169] Mixed character field names are grouped into the third cluster FC (example: FC=["Supp_Brand Name","Customer Name_ABC","Supplier Code_EN"]).

[0170] Clustering validation: Verify the character type of the field names in each cluster to avoid misclassification (e.g., classify "supplier123" as pure Chinese and classify it as FA, and classify "supplier_123" as pure English and classify it as FB).

[0171] This step stratifies and clusters field names according to character type, eliminating the interference of character form differences on semantic recognition, avoiding semantic judgment errors caused by directly clustering field names with pure Chinese, pure English, and mixed characters, making subsequent semantic clustering within the same character type more focused and accurate, and also laying a stratified foundation for semantic association determination across character types.

[0172] Q300, based on the semantics of each field name in FA, FB and FC, clusters the field names in FA, FB and FC respectively to obtain several field name groups corresponding to FA, FB and FC.

[0173] Furthermore, step Q300 includes the following steps:

[0174] Q310, preprocess each field name in the target cluster; the preprocessing includes: special character removal, word segmentation, and word segmentation filtering; the target cluster is one of FA, FB, and FC.

[0175] The preprocessing is designed to differentiate character types for the target clusters (FA / FB / FC). The core of this design is based on FA (pure Chinese cluster, example: FA=["Supplier Full Name!","Supplier Abbreviation","

Supplier Name

[0176] 1. Special character removal:

[0177] For FA (pure Chinese): Remove punctuation marks (!, 【】), underscores, spaces, and other meaningless special characters, and the processed result is ["Supplier Full Name", "Supplier Abbreviation", "Supplier Name"];

[0178] For Facebook (pure English): Remove special symbols (#, -) and numbers (123) that have no business meaning, and the result is ["supplier_name","vendor_name","supplier_id"];

[0179] For FC (mixed characters): First, split the Chinese and English parts, remove the special characters from each part, and then merge them again. The result is ["Supp Brand Name","Customer Name ABC","Supplier Code EN"];

[0180] 2. Word segmentation processing:

[0181] FA uses jieba word segmentation to extract core words, such as "supplier full name" being segmented as ["supplier", "full name"], and "supplier abbreviation" being segmented as ["supplier", "abbreviation"].

[0182] Facebook splits words using snake / camel case naming conventions, such as splitting "supplier_name" into ["supplier","name"], and "vendor_name" into ["vendor","name"];

[0183] FC segments the Chinese part and splits and merges the English part. For example, "Supp brand name" is split into ["Supp", "brand name"], and "customer name ABC" is split into ["customer name", "ABC"];

[0184] Word segmentation filtering: Load a preset business dictionary (containing core entity words such as "supplier", "customer", "name", "vendor" etc.), filter meaningless stop words (such as "of", "part", "irrelevant numbers", etc.), after FA filtering, retain ["supplier", "full name"] "supplier", "abbreviation"] "supplier", "name"], FB retain ["supplier", "name"] "vendor", "name"] "supplier", "id"], FC retain ["Supp", "brand name"] "customer name", "ABC"] "supplier", "code", "EN"].

[0185] This step eliminates the interference of special characters and meaningless modifiers / numbers on semantic recognition through differentiated preprocessing operations. At the same time, it adapts the word segmentation logic to different character types, accurately extracts the core semantic words of field names, and avoids the deviation in semantic feature extraction caused by the complexity of the surface form of field names (such as differences in punctuation and naming format). This lays a clean and accurate text foundation for subsequent semantic vector conversion and similarity calculation.

[0186] Q320: Obtain the semantic feature vector of each field name in the target cluster.

[0187] A lightweight pre-trained semantic model (such as BERT-tiny) is selected. This model is suitable for the semantic representation of short texts (field names), and its computational efficiency can meet the needs of multi-field name analysis. The word segmentation results after Q310 filtering are concatenated into structured semantic description text (e.g., the input of "supplier full name" in FA is "supplier full name", the input of "supplier_name" in FB is "supplier name", and the input of "Supp brand name" in FC is "Supp brand name"). The semantic description text is input into the pre-trained model. The model learns the contextual association of words and outputs a fixed-dimensional semantic feature vector (e.g., 768-dimensional), with each field name corresponding to a unique vector representation.

[0188] Example results: In FA, the vector corresponding to "full supplier name" is V1=[0.12,0.35,...,0.21], and the vector corresponding to "abbreviated supplier name" is V2=[0.11,0.34,...,0.20]; in FB, the vector corresponding to "supplier_name" is V4=[0.45,0.67,...,0.89], and the vector corresponding to "vendor_name" is V5=[0.44,0.66,...,0.88].

[0189] This step transforms textual field names into quantifiable numerical vectors through a pre-trained semantic model, achieving a digital representation of semantics. This overcomes the limitations of subjective human judgment of the semantic association of field names, allowing field names with different expressions and character types to have a unified computational dimension, providing an objective and accurate foundation for subsequent similarity quantification analysis.

[0190] Q330: For the semantic feature vectors of each field name in the target cluster, calculate the cosine similarity pairwise to obtain the semantic similarity matrix.

[0191] The semantic similarity between pairs of vectors is calculated using the cosine similarity formula. Taking FA (containing 3 field names: full supplier name, abbreviation of supplier, and name of supplier) as an example, a 3×3 symmetric similarity matrix is ​​constructed. The diagonal lines represent the similarity between a field name and itself (the value is always 1), and the off-diagonal lines represent the similarity values ​​between pairs of field names. An example matrix is ​​shown in Table 1.

[0192] Table 1

[0193]

[0194] This step transforms the discrete semantic associations of field names into intuitive quantitative data by calculating cosine similarity pairwise and constructing a structured similarity matrix. It clearly presents the semantic similarity between all field names, avoids the fragmentation of similarity calculation, and provides a traceable and verifiable quantitative basis for subsequent semantically related field name determination and clustering operations.

[0195] Q340: Two field names whose cosine similarity is greater than or equal to the second preset similarity threshold are identified as semantically related field names.

[0196] Based on the business scenario, a second preset similarity threshold XS1 (e.g., 0.7) is set. This threshold serves as a unified criterion for determining "semantic relevance," balancing clustering accuracy and coverage. The off-diagonal elements of the similarity matrix are traversed, and field name pairs with values ​​≥ XS1 are selected. In the example, "Supplier Full Name - Supplier Abbreviation" (0.85 ≥ 0.7), "Supplier Full Name - Supplier Name" (0.82 ≥ 0.7), and "Supplier Abbreviation - Supplier Name" (0.78 ≥ 0.7) are all determined to be semantically relevant field names. If there is a "Supplier Code - Supplier Name" similarity of 0.65 < 0.7, it is determined to be semantically unrelated. All semantically relevant field name pairs are compiled into a structured association list, and the similarity value of each pair of field names is labeled.

[0197] This step quickly identifies semantically related field names through threshold filtering, effectively narrowing the analysis scope of subsequent clustering operations, eliminating interference from field names with significant semantic differences, and improving the efficiency of clustering operations. At the same time, the unified threshold standard ensures the objectivity of semantic relevance judgment, avoids the bias of subjective human judgment, and ensures the consistency of judgment results.

[0198] Q350 employs a density clustering algorithm, using semantically related field names as neighborhood criteria. It clusters field names within the target cluster that satisfy a mutual semantic similarity greater than or equal to a second preset similarity threshold into one class, resulting in several field name groups corresponding to FA, FB, and FC.

[0199] The DBSCAN density clustering algorithm is used, with "semantically related field names" as the neighborhood determination criterion. The core process is illustrated by FA as an example:

[0200] DBSCAN parameter adaptation: Map the algorithm's "neighborhood radius ε" to "cosine similarity ≥ XS1", and set "minimum number of points MinPts" to 2 (adapting to business scenarios: the same entity corresponds to at least 2 different named field names);

[0201] Core point determination: Traverse the field names. If the number of field names with similarity ≥ XS1 within the semantic neighborhood of a certain field name is ≥ MinPts, then it is determined to be a core point. In the example, the neighborhood of "full name of supplier" contains "abbreviated name of supplier" and "name of supplier" (quantity = 2 ≥ 2), so it is determined to be a core point.

[0202] Cluster expansion: Starting from the core point, all semantically related field names in its neighborhood are included in a temporary cluster, and then the neighborhood of the field names in the temporary cluster is traversed (using the transitivity of semantic relatedness) until no new semantically related field names can be added;

[0203] Output results: In the example, FA clustering yields one field name group ["Full Supplier Name", "Abbreviated Supplier Name", "Supplier Name"]; if the similarity between "supplier_id" and other field names in FB is less than 0.7, then it is clustered into a separate group, and finally FB yields two groups: ["supplier_name", "vendor_name"] and ["supplier_id"]; FC clusters according to the same logic to obtain the corresponding field name groups.

[0204] This step employs a density-based clustering algorithm, eliminating the need to pre-determine the number of clusters. It is well-suited for business scenarios where "the same entity may correspond to multiple different named field names," avoiding the mis-clustering and missed-clustering issues caused by pre-determined cluster numbers in algorithms like K-means. By using semantically related field names as neighborhood conditions, it ensures that all field names within a cluster are semantically similar, accurately grouping field names of the same entity. This completely resolves the misjudgment of field names caused by differences in naming conventions (such as abbreviations, full names, and Chinese / English expressions), providing a precise foundation for subsequent cross-cluster merging based on groups of semantically similar field names.

[0205] Q400, retrieve any field name group FA from FA. R and any field name group in FB R Similarity between XS AB .

[0206] Furthermore, the Q400 includes the following steps:

[0207] Q410, obtain FA R The most frequent field name FA Z and FB R The most frequent field name in the middle is FB Z .

[0208] In this embodiment, FA R It contains several field names, some of which are identical. We can first group these identical field names together, and then assign the field name FA to the group with the most identical field names. Z FB Z The determination method and FA Z The method for determining the number of field names is the same; if there is more than one group with the most field names in a group, the number can be determined randomly from multiple groups.

[0209] This step avoids semantic bias caused by selecting the most widely used core field names within the field name group as representatives, thus ensuring that cross-cluster similarity calculations can anchor the core semantics of the field name group.

[0210] Q420, will FA Z The corresponding semantic feature vector and FB Z The similarity between the corresponding semantic feature vectors is determined as XS. AB .

[0211] FA Z The semantic feature vector of "supplier name" follows the output of Q320—a fixed-dimensional vector (e.g., 768-dimensional) V_FA obtained by preprocessing, word segmentation and filtering, and transformation through a pre-trained semantic model (e.g., BERT); the semantic feature vector of FBZ ("supplier name") is similarly the vector V_FB output by Q320 for the FB cluster; the semantic correlation between the two core vectors is quantified using the cosine similarity formula;

[0212] Furthermore, before obtaining the semantic feature vector, FB can be... Z The second character type character is converted to the first character type character, and then the corresponding semantic feature vector is obtained; thereby improving the accuracy of the semantic feature vector.

[0213] This step focuses on calculating the semantic similarity of core field names within a group, replacing the complex logic of "calculating cross-cluster similarity for all field names within a group and then taking the average." This significantly simplifies the cross-cluster similarity calculation process, reduces computational resource consumption, and improves overall efficiency. Simultaneously, vector calculation based on core field names accurately reflects the core semantic relationship between two field name groups, avoiding interference from marginal field names within the group and ensuring XS... AB It can accurately and objectively reflect the semantic similarity of field name groups between FA and FB clusters, providing a precise quantitative basis for subsequent cross-cluster merging operations.

[0214] Q500, if XS AB If ≥XS0, then FB R Each field name in the FA is merged into FA. R In the middle, XS0 is the first preset similarity threshold.

[0215] This step uses threshold filtering to merge only field names of different character types that are semantically highly similar, ensuring that the merged field name groups still focus on the same entity and preventing irrelevant fields from being mixed in. It also enables the aggregation of the same entity's fields between pure Chinese and pure English clusters, breaking down the fundamental barriers of character types and allowing field names of the same entity to form a preliminary cross-character type set.

[0216] Q600, obtain FA R and any field name group in FC R Similarity between XS AC .

[0217] In this embodiment, the similarity calculation method in this step is the same as that in step Q400, and will not be described again here. This step calculates the similarity between the merged field name group and the field name group of the mixed character type cluster, covering the field name scenario of mixed character types. This ensures that the same entity field of all character types can be included in the association determination scope, avoids the omission of mixed character fields due to their special form, and further improves the association dimension of the same entity field.

[0218] Q700, if XS AC If ≥XS0, then FC R Each field name in the FA is merged into FA. R middle.

[0219] This step aggregates the mixed character type field name group with the existing merged group, realizing the complete collection of the same entity fields under the full character type. It allows all field names with different names and different character types representing the same entity to form a unified set, completely breaking down the dual barriers of character type and naming form, and providing a unique and complete field dimension basis for data fusion.

[0220] After obtaining the field name group, TA and TB are selected from the same field name group to provide "semantically cleaned input" for the foreign key determination scheme. The foreign key determination scheme provides "dual verification of technology and business" for the clustering results, ultimately forming a closed loop of "semantic unification → precise matching → foreign key locking → fusion implementation". This not only solves the fusion error caused by field name differences, but also ensures the technical rationality and business practicality of foreign key association.

[0221] Furthermore, although the steps of the method in this disclosure are described in a specific order in the accompanying drawings, this does not require or imply that the steps must be performed in that specific order, or that all the steps shown must be performed to achieve the desired result. Additional or alternative steps may be omitted, multiple steps may be combined into one step, and / or a step may be broken down into multiple steps.

[0222] Embodiments of the present invention also provide a non-transitory computer-readable storage medium that can be disposed in an electronic device to store at least one instruction or at least one program related to implementing a method in the method embodiments, wherein the at least one instruction or the at least one program is loaded and executed by the processor to implement the method provided in the above embodiments.

[0223] The program product may employ any combination of one or more readable media. A readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of readable storage media (a non-exhaustive list) include: an electrical connection having one or more wires, a portable disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0224] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, carrying readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A readable signal medium may also be any readable medium other than a readable storage medium, capable of sending, propagating, or transmitting programs for use by or in conjunction with an instruction execution system, apparatus, or device.

[0225] The program code contained on the readable medium may be transmitted using any suitable medium, including but not limited to wireless, wired, optical fiber, RF, etc., or any suitable combination thereof.

[0226] Program code for performing the operations of this application can be written in any combination of one or more programming languages, including object-oriented programming languages ​​such as Java and C++, and conventional procedural programming languages ​​such as C or similar languages. The program code can execute entirely on the user's computing device, partially on the user's device, as a standalone software package, partially on the user's computing device and partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0227] Embodiments of the present invention also provide an electronic device, including a processor and the aforementioned non-transitory computer-readable storage medium.

[0228] The electronic device is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments in this application.

[0229] Electronic devices are manifested in the form of general-purpose computing devices. Components of an electronic device may include, but are not limited to: at least one processor, at least one memory, and a bus connecting different system components (including memory and processor).

[0230] The memory stores program code that can be executed by the processor, causing the processor to perform the steps in the various embodiments described in this specification.

[0231] The memory may include readable media in the form of volatile memory, such as random access memory (RAM) and / or cache memory, and may further include read-only memory (ROM).

[0232] The memory may also include programs / utilities having a set (at least one) of program modules, including but not limited to: an operating system, one or more application programs, other program modules, and program data, each or some combination of these examples may include an implementation of a network environment.

[0233] A bus can represent one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, a graphics acceleration port, a processor, or a local bus that uses any of the various bus structures.

[0234] Electronic devices can also communicate with one or more external devices (e.g., keyboards, pointing devices, Bluetooth devices, etc.), one or more devices that enable user interaction with the electronic device, and / or any device that enables the electronic device to communicate with one or more other computing devices (e.g., routers, modems, etc.). This communication can be achieved through input / output (I / O) interfaces. Furthermore, electronic devices can communicate with one or more networks (e.g., local area networks (LANs), wide area networks (WANs), and / or public networks, such as the Internet) via network adapters. The network adapter communicates with other modules of the electronic device via a bus. It should be understood that other hardware and / or software modules can be used in conjunction with the electronic device, including but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0235] From the above description of the embodiments, those skilled in the art will readily understand that the exemplary embodiments described herein can be implemented by software or by combining software with necessary hardware. Therefore, the technical solutions according to the embodiments of this disclosure can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (such as a CD-ROM, USB flash drive, external hard drive, etc.) or on a network, including several instructions to cause a computing device (such as a personal computer, server, terminal device, or network device, etc.) to execute the methods according to the embodiments of this disclosure.

[0236] Embodiments of the present invention also provide a computer program product including program code, which, when the program product is run on an electronic device, causes the electronic device to perform the steps of the methods described above in various exemplary embodiments of the present invention.

[0237] While specific embodiments of the invention have been described in detail by way of examples, those skilled in the art should understand that the examples are for illustrative purposes only and are not intended to limit the scope of the invention. Those skilled in the art should also understand that various modifications can be made to the embodiments without departing from the scope and spirit of the invention.

Claims

1. A method for determining foreign keys in a data table, characterized in that, The method includes the following steps: S100, obtain the semantic similarity η between the annotation ZA corresponding to any field name TA in the first data table and the annotation ZB of any field name TB in the second data table. AB The first and second data tables each include several rows and several columns. Each row corresponds to one piece of material information, each column corresponds to a field name, and each field name corresponds to the attribute data of each material. S200, obtain the attribute data similarity λ between the attribute data list corresponding to TA and the attribute data list corresponding to TB. AB ; S300, if η AB >η0 or λ AB If η > λ0, then TA and TB are determined as the initial field name pair, resulting in the initial field name pair list C; η0 is the preset semantic similarity threshold, and λ0 is the preset attribute data similarity threshold. S400, obtain the matching degree between the first data table and the second data table, and the expansion rate corresponding to each initial field name pair in C; the matching degree is determined based on the number of field names that exist simultaneously in the first data table and the second data table; the expansion rate is determined based on the number of attribute data corresponding to TA that exist in the second data table; S500, retrieves the historical query percentage corresponding to each field name in each initial field name pair in C; the query percentage is determined based on the historical query statements. S600, based on the matching degree between the first data table and the second data table, the expansion rate corresponding to each initial field name pair in C, and the proportion of historical queries, determine the correlation degree of each initial field name pair. S700: Initial field name pairs with a correlation degree greater than a preset correlation degree threshold are identified as foreign keys in the first and second data tables.

2. The method for determining foreign keys in a data table according to claim 1, characterized in that, Step S400 includes the following steps: S410, obtain the number of field names that exist in both the first data table and the second data table, NUM1, and the total number of field names in the first data table, NUM; S420, Based on NUM1 and NUM, determine the matching degree ρ=NUM1 / NUM between the first data table and the second data table; S430, for any initial field name pair ZD=(A, B) in C, retrieve the deduplicated attribute data corresponding to A, resulting in the attribute data list SA=(SA1, SA2, ..., SA2) of A. i SA n ), i=1,2,…,n; SA i Let be the i-th deduplicated attribute data corresponding to A, and n be the number of deduplicated attribute data corresponding to A; A is the field name determined from the first data table, and B is the field name determined from the second data table; S440, based on the quantity of each attribute data in SA in the second data table, determine the inflation rate corresponding to each attribute data in SA, and obtain the inflation rate list α = (α1, α2, ..., α...). i , ..., α n ); α i for SA i The corresponding expansion rate; α i =NB i / NA i NA i for SA i The quantity in the first data table, NB i for SA i The quantity in the second data table; S450, according to α i Determine the expansion rate corresponding to A as μ = (1 / n) × ∑ n i=1 α i .

3. The method for determining foreign keys in a data table according to claim 2, characterized in that, Step S500 includes the following steps: S510, obtain C = (C1, C2, ..., C... j C m ), j=1,2,…,m; C j Let m be the j-th initial field name pair in C, and m be the number of initial field name pairs in C; j =(C j，A C j，B ); C j，A C is the initial field name determined from the first data table. j，B These are the initial field names determined from the second data table; S520, obtain C j，A XC represents the historical query count for the query criteria. j，A and with C j，B XC represents the historical query count for the query criteria. j，B ; S530, according to XC j，A and XC j，B Determine C j，A The corresponding query percentage β j，A =XC j，A / ∑ m j=1 XC j，A And C j，B The corresponding query percentage β j，B =XC j，B / ∑ m j=1 XC j，B .

4. The method for determining foreign keys in a data table according to claim 3, characterized in that, Step S600 includes the following steps: S610, based on ρ, C j，A The corresponding normalized value of the expansion rate μ j，A β j，A and β j，B Determine C j The corresponding correlation θ j =τ1×ρ+τ2×μ j，A +τ3×β j，A +τ4×β j，B τ1, τ2, τ3 and τ4 are the first preset weight, the second preset weight, the third preset weight and the fourth preset weight, respectively; τ1+τ2+τ3+τ4=1.

5. The method for determining foreign keys in a data table according to claim 1, characterized in that, Step S200 includes the following steps: S210, respectively obtain the attribute data list D_A corresponding to TA in the first data table and the attribute data list D_B corresponding to TB in the second data table; S220, if the number of data entries N_A of D_A or the number of data entries N_B of D_B is greater than the preset data volume threshold N_th, then random sampling is performed on D_A and D_B respectively to obtain the sampling attribute data list S_A corresponding to D_A and the sampling attribute data list S_B corresponding to D_B. S230, if the attribute data types corresponding to TA and TB are numerical data, then calculate the statistical feature similarity between S_A and S_B; the statistical features include mean, variance, and quartiles; S240, If the attribute data types corresponding to TA and TB are character data, then calculate the matching similarity ζ between S_A and S_B. AB =(TN1+TN2) / max(TN A TN B TN1 represents the number of identical data entries in S_A and S_B, and TN2 represents the number of data entries in S_A and S_B with a string cosine similarity greater than or equal to a preset cosine similarity threshold; TN A TN represents the number of data entries in S_A. B The number of data entries in S_B; max() is the preset function for finding the maximum value; S250, Repeat steps S220-S240 several times, and take the average of the calculation results as the similarity λ of the attribute data corresponding to TA and TB. AB .

6. The method for determining foreign keys in a data table according to claim 1, characterized in that, Following step S700, the method further includes the following steps: S800, if the foreign key of the first data table and the second data table is not determined, then obtain the field names in C that belong to the first data table to obtain the first field name list YA, and obtain the field names in C that belong to the second data table to obtain the second field name list YB. S810, Get the list of historical query statements SQ = (SQ1, SQ2, ..., SQ... x , ..., SQ y ), x=1,2,…,y;SQ x Let y be the x-th historical query statement retrieved, and y be the number of historical query statements retrieved; SQ x The query criteria must include at least two field names; S820, iterate through SQ, if SQ x If every field name in the query condition is contained in YA or is contained in YB, then SQ will be... x The specified query statement is determined, and the list of specified query statements ZQ = (ZQ1, ZQ2, ..., ZQ...) is obtained. a , ..., ZQ b ), a=1,2,…,b;ZQ a Let a be the a-th specified query statement, and b be the number of specified query statements determined. S830, obtain each field name contained in the query conditions of each specified query statement in ZQ, and obtain the field name list set HQ = (HQ1, HQ2, ..., HQ) corresponding to ZQ. a HQ b HQ a For ZQ a The corresponding list of field names; HQ a = (HQ) a，1 HQ a，2 HQ a，c HQ a，f（a） ), c=1, 2,…, f (a); HQ a，c For ZQ a The query condition includes the c-th field name, f(a) is ZQ a The number of field names included in the query conditions; S840, HQ a The field names in HQ are determined as a set of composite foreign keys, and HQ is used to define them. a Each field name in the initial field name pair corresponds to a field name belonging to the first or second data table, which is determined as a group of composite foreign keys, resulting in several groups of composite foreign keys; S850 determines the target joint foreign key based on the weight of each joint foreign key.

7. The method for determining foreign keys in a data table according to claim 6, characterized in that, Step S850 includes the following steps: S851, for any set of joint foreign keys HG = (HG1, HG2, ..., HG... p HG q ), p=1,2,…,q;HG p Let q be the name of the p-th field in HG, and q be the number of field names in HG; S852, if the field names in HG belong to the first data table and each set of attribute data corresponding to HG is different, then retrieve the w sets of attribute data corresponding to HG from the first data table, and obtain the list of attribute data groups corresponding to HG, HW = (HW1, HW2, ..., HW...). e HW w ), e=1,2,…,w;HW e This refers to the e-th group of attribute data corresponding to HG, obtained from the first data table; S853, with HW e Using the search criteria, search the second data table and determine HW based on the search results. e The corresponding first sub-weight QC e If the number of data entries in the search results is 0, then QC... e =k1; If the number of data entries in the search result is 1, then QC e =k2; If the number of data entries in the search results is greater than 1, then QC e =0; k1 < k2; S854, based on the first sub-weight corresponding to each set of attribute data in HW, determine the first weight φ=∑ w e= 1QC e ; S855, if φ is greater than the preset weight threshold, then HG will be determined as the target joint foreign key.

8. The method for determining foreign keys in a data table according to claim 7, characterized in that, After step S851 and before step S852, the following steps are also included: S856, If the field name in HG belongs to the second data table and each set of attribute data corresponding to HG is different, then retrieve the w sets of attribute data corresponding to HG from the second data table. S857, using any set of attribute data as search criteria, search the first data table, and determine the second weight σ corresponding to HG based on the search results; S858, if σ is greater than the preset weight threshold, then HG is determined as the target joint foreign key.

9. A non-transitory computer-readable storage medium, wherein the storage medium stores at least one instruction or at least one program segment, characterized in that, The at least one instruction or the at least one program segment is loaded and executed by the processor to implement the foreign key determination method for a data table as described in any one of claims 1-8.

10. An electronic device, characterized in that, Includes a processor and the non-transitory computer-readable storage medium as described in claim 9.

Images