Automated Table Joining via Granularity Transform Scripts
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Joining tables with different data granularities is a time-consuming and cumbersome task for users, as existing methods do not effectively address the mapping between columns with varying levels of granularity, such as finer or coarser granularities.
Innovation Solution
A computing device with a processor that selects pairs of columns with different granularities, modifies the data to match granularities, generates examples, and programmatically creates scripts to facilitate joins, using data granularity transform functions and programming-by-example algorithms to ensure consistent outputs across tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If user manually writes script to join tables with different granularities, then join operation can be performed, but user effort and time consumption increase significantly
Solution Approach 1:
The system automatically detects columns with different granularities, generates candidate mapping scripts, and performs join operations without requiring user intervention. The computer executes the join process autonomously by selecting columns, generating scripts, and completing the join operation, thereby eliminating manual scripting efforts and reducing time consumption.
2Reliability
If existing join methods are used on columns with different granularities, then join operation can be attempted, but mapping accuracy between columns deteriorates
Solution Approach 1:
The system transforms data by changing granularity parameters through generated scripts. It detects granularity differences between columns, generates candidate transformation scripts that adjust granularity levels (e.g., converting detailed timestamps to dates, or aggregating fine-grained data to coarse-grained data), and selects the best mapping based on match quality metrics, thereby achieving accurate joins despite initial granularity mismatches.
3Adaptability or versatility
If manual script writing is required for granularity matching, then custom join logic can be implemented, but device complexity and operational difficulty increase
Solution Approach 1:
The system performs preliminary actions by automatically detecting granularity differences between columns before the join operation, generating appropriate transformation scripts in advance, and preparing the data alignment. This preliminary automation of script generation and granularity matching eliminates the need for users to manually write complex custom join logic, thereby maintaining flexibility while reducing system and operational complexity.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A computing device is provided, comprising a processor configured to select at least one pair of columns. Each pair may include a source column of the first table and a target column of the second table. For each pair, the processor may detect that the columns contain data with different granularities. The processor may modify the data to have the same granularity, and may generate an example including an element from the source column and an element from the target column. For each example, the processor may programmatically generate a script that, when performed on the source column, produces a value consistent with the target column. For the script with output that meets a matching criterion, the processor may convey the output for display, and may, in response to a signal accepting the script, join the tables at least in part by performing the script on the source column.