Table data analysis method and device based on PostgreSQL database and medium
By creating tables in PostgreSQL database and using built-in function queues to process data, Excel is solved inefficient in processing large data volumes, and efficient data analysis is achieved.
Patent Information
- Application Number
- CN202510548356.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-04-28
- Publication Date
- 2025-08-08
AI Technical Summary
When Excel faces the task of processing large data volume data, it is difficult to quickly and effectively deal with the needs of large data volume processing by manually writing SQL statements.
Based on the PostgreSQL database, by creating multiple tables, inserting the pending data into the corresponding tables, and matching the built-in functions using the preset function knowledge graph, generating a built-in function queue, calling it in the order of business information and data analysis items, completing data processing, and storing the results in Excel.
It improves the accuracy and efficiency of function matching, reduces the time for users to manually write SQL statements, ensures that the data analysis process follows business logic, and significantly improves the efficiency of large data processing.
Smart Images
Figure CN120449845A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of data processing technology, and in particular to a table data analysis method, device, and medium based on a PostgreSQL database. Background Art
[0002] In today's data-driven era, data processing and analysis are becoming increasingly important. Excel, as a widely used data processing software, is favored by many office workers.
[0003] However, as businesses expand and data volumes grow, Excel's limitations become increasingly apparent when faced with large data processing tasks. When data volumes exceed a certain scale, Excel often encounters performance bottlenecks. When performing routine operations such as reading, calculating, sorting, and filtering data, data processing speeds become extremely slow, and even freezes frequently occur. This not only severely impacts work efficiency, but can also prevent some data processing tasks from being completed in a timely manner, impacting the timeliness and accuracy of business decisions.
[0004] Existing technologies typically require users to possess a certain level of database knowledge and manually configure database connection information to address Excel's challenges in processing large amounts of data. This process involves accurately filling in numerous parameters, such as the server address, port number, username, and password, and carries the risk of connection failure. Furthermore, writing SQL statements to query, process, and analyze data is particularly difficult and error-prone for users without a professional programming background. This creates significant challenges for users seeking solutions and makes it difficult to quickly and effectively address the demands of processing large amounts of data. Summary of the Invention
[0005] The embodiments of the present application provide a table data analysis method, device, and medium based on a PostgreSQL database, which are used to solve the following technical problem: when Excel faces large-scale data processing tasks, it is difficult to quickly and effectively respond to the needs of large-scale data processing by manually writing SQL statements to implement data processing.
[0006] The embodiments of this application adopt the following technical solutions:
[0007] The present invention provides a table data analysis method based on a PostgreSQL database. The method comprises the following steps: creating multiple tables based on a user-selected data area to be processed and data analysis items, inserting the data to be processed in the data area to be processed into the corresponding tables; matching corresponding built-in functions in PostgreSQL based on the data analysis items and the preset function knowledge graph corresponding to the tables; sorting the matching built-in functions in a call order based on the business information and data analysis items corresponding to the data to be processed, and generating a built-in function queue; processing the data to be processed using the built-in function queue, and storing the processed data in Excel to complete the table data analysis.
[0008] The embodiment of the present application matches the built-in functions in PostgreSQL through the preset function knowledge graph, which can accurately obtain the correlation relationship between functions and the semantic connection between functions and data analysis items, thereby improving the accuracy and efficiency of matching. Users do not need to manually write SQL statements, nor do they need to manually search and filter functions one by one, saving a lot of time and energy spent on function selection. Secondly, the built-in function queue generated by the embodiment of the present application is highly consistent with actual business needs, ensuring that the data analysis process follows business logic and can accurately reflect the business operation status. At the same time, through a reasonable function call sequence, it can reduce unnecessary calculations and data transmission, significantly improving the efficiency of data analysis. When faced with large-scale data processing tasks, data processing tasks can be performed accurately and efficiently.
[0009] In one implementation of the present application, based on the data area to be processed and the data analysis items to be processed selected by the user, multiple tables are created, and the data to be processed in the data area to be processed is inserted into the corresponding table, specifically including: labeling the data area to be processed based on the data type, data format and data structure corresponding to the data to be processed; dividing and mapping the data areas to be processed with the same label; dynamically generating a table structure based on the division result; and, based on the division result, determining the data insertion amount corresponding to each table structure, so as to insert the data to be processed into the corresponding table structure in batches based on the data insertion amount; performing association relationship detection on the data in the same table structure through the mapping relationship, and determining that the insertion of the data to be processed is completed when the detection result passes.
[0010] In one implementation of the present application, corresponding built-in functions are matched in PostgreSQL based on the data analysis items corresponding to the table and the preset function knowledge graph, specifically including: inputting the data information and data analysis items in the table into the preset processing flow prediction model to output multiple prediction processing steps through the preset processing flow prediction model; constructing a preset function knowledge graph based on multiple functions in the function library; wherein the nodes in the preset function knowledge graph are each function, and the edges in the preset function knowledge graph are the association relationships between each function; performing semantic analysis on multiple prediction processing steps, and converting the semantic analysis results into node representations, so as to perform node matching in the preset function knowledge graph based on the node representations; and determining the built-in functions corresponding to each table based on the matching results.
[0011] In one implementation of the present application, based on the business information and data analysis items corresponding to the data to be processed, the calling order of the matching built-in functions is sorted to generate a built-in function queue, which specifically includes: performing similarity processing on the business information and data analysis items with the historical database respectively, and determining the reference historical data based on the similarity; dividing the historical built-in function queue corresponding to the reference historical data based on the preset function knowledge graph, and dividing the historical built-in function queue into multiple sub-queues based on the execution logic order corresponding to the data analysis items; obtaining the data processing requirement information corresponding to the business information; re-sorting each function in the multiple sub-queues, comparing the execution performance information corresponding to the sorted function queue with the data processing requirement information, and determining the built-in function queue based on the comparison result.
[0012] In one implementation of the present application, the functions in multiple sub-queues are re-sorted, the execution performance information corresponding to the sorted function queues is compared with the data processing requirement information, and a built-in function queue is determined based on the comparison results, specifically including: obtaining data scale information corresponding to the data to be processed; wherein the data scale information includes at least one of the data dimension, the number of data, and the degree of discreteness of the distribution of data values; inputting the data scale information, system resource usage, and multiple sorted function queues into a preset decision tree prediction model to output execution performance information corresponding to different function execution orders through the preset decision tree prediction model; wherein the execution performance information includes at least one of the execution time and the remaining resources; comparing the execution performance information with the data processing requirement information to determine the reference function execution order corresponding to each sub-queue; splicing the sub-queues after the order adjustment to generate a built-in function queue.
[0013] In one implementation of the present application, the execution performance information is compared with the data processing requirement information to determine the reference function execution order corresponding to each sub-queue, specifically including:
[0014] According to the preset function:
[0015]
[0016] Determine the comparison function value corresponding to the subqueue; where q i is the function queue; T qi is the function execution time; T req The upper limit of the function execution time; R qi is the resource consumption value; R req is the upper limit of resource consumption; α is the execution time weight; β is the resource consumption weight; C(q i ) is q i The comparison function value corresponding to the queue; compare the comparison function values corresponding to each sub-queue to determine the reference function execution order.
[0017] In one implementation of the present application, the data to be processed is processed through a built-in function queue, and the processed data is stored in Excel, specifically including: generating a function call instruction queue based on the built-in function queue, so as to call the built-in functions in sequence through the function call instruction queue; processing the data to be processed in sequence through the called built-in functions; wherein the built-in functions include at least a group by clause, a cube clause, a rollup clause, and an over clause; dividing the processed data into multiple data blocks, and storing each data block in Excel in sequence.
[0018] In one implementation of the present application, the data to be processed is processed in sequence by calling a built-in function, specifically including: when the data analysis item includes a data filtering item, arranging the data in the table in chronological order and assigning a corresponding row number to each row; filtering the row number based on the window function to filter the data in the table by the filtered row number.
[0019] An embodiment of the present application provides a table data analysis device based on a PostgreSQL database, comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so as to enable the at least one processor to: create multiple tables based on a data area to be processed and a data analysis item selected by a user, and insert the data to be processed in the data area to be processed into corresponding tables; match corresponding built-in functions in PostgreSQL according to the data analysis items corresponding to the tables and a preset function knowledge graph; sort the matching built-in functions in a calling order based on business information and data analysis items corresponding to the data to be processed, and generate a built-in function queue; process the data to be processed through the built-in function queue, and store the processed data in Excel to complete the table data analysis.
[0020] An embodiment of the present application provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured to: create multiple tables based on a data area to be processed and a data analysis item selected by a user, and insert the data to be processed in the data area to be processed into corresponding tables; match corresponding built-in functions in PostgreSQL according to the data analysis items corresponding to the tables and a preset function knowledge graph; sort the matching built-in functions in a calling order based on business information and data analysis items corresponding to the data to be processed, and generate a built-in function queue; process the data to be processed through the built-in function queue, and store the processed data in Excel to complete table data analysis.
[0021] At least one of the above technical solutions adopted in the embodiment of the present application can achieve the following beneficial effects: the embodiment of the present application matches the built-in functions in PostgreSQL through the preset function knowledge graph, which can accurately obtain the correlation relationship between functions and the semantic connection between functions and data analysis items, thereby improving the accuracy and efficiency of matching. Users do not need to manually write SQL statements, nor do they need to manually search and filter functions one by one, saving a lot of time and energy spent on function selection. Secondly, the built-in function queue generated by the embodiment of the present application is highly consistent with actual business needs, ensuring that the data analysis process follows business logic and can accurately reflect the business operation status. At the same time, through a reasonable function call sequence, it can reduce unnecessary calculations and data transmission, significantly improving the efficiency of data analysis. When faced with large-scale data processing tasks, data processing tasks can be performed accurately and efficiently. BRIEF DESCRIPTION OF THE DRAWINGS
[0022] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments described in the present application. For those skilled in the art, other drawings can be obtained based on these drawings without inventive work. In the drawings:
[0023] Figure 1 A flowchart of a table data analysis method based on a PostgreSQL database provided in an embodiment of the present application;
[0024] Figure 2 A schematic diagram of a table data analysis process provided in an embodiment of the present application;
[0025] Figure 3 A schematic diagram of the structure of a table data analysis device based on a PostgreSQL database provided in an embodiment of the present application.
[0026] Reference numerals:
[0027] 200: Table data analysis device based on PostgreSQL database, 201: Processor, 202: Memory. DETAILED DESCRIPTION
[0028] The present invention provides a method, device, and medium for analyzing table data based on a PostgreSQL database.
[0029] In order to enable those skilled in the art to better understand the technical solutions in this application, the following will clearly and completely describe the technical solutions in the embodiments of this application in conjunction with the drawings in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments of this specification, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of this application.
[0030] The technical solutions proposed in the embodiments of the present invention are described in detail below with reference to the accompanying drawings.
[0031] Figure 1 A flow chart of a table data analysis method based on a PostgreSQL database is provided in an embodiment of the present application, as shown in FIG. Figure 1 As shown, the table data analysis method based on the PostgreSQL database includes the following steps:
[0032] S101 . Based on the data area to be processed and the data analysis items selected by the user, multiple tables are created, and the data to be processed in the data area to be processed is inserted into corresponding tables.
[0033] In one implementation of the present application, an embodiment of the present application provides an Excel plug-in for large-scale data analysis based on PostgreSQL. This plug-in uses a portable version of PostgreSQL that requires no installation and implements data communication between Excel and the PostgreSQL database through the Python psycopg2 library. Users do not need to worry about information such as the database port, username, password, installation location, etc., nor do they need to understand SQL statements or database configuration knowledge.
[0034] In one implementation of this application, Figure 2 A schematic diagram of a table data analysis process provided in an embodiment of the present application is shown in FIG. Figure 2As shown, step G001 is to open the Excel plug-in interface window, where you can select the data analysis item to be performed and select the corresponding data area. Step P001 is the actual logic processing in the background. The user is unaware of the existence of postgresql. By encapsulating the driver of psycopg, the user does not need to install other postgresql drivers, nor does he need to understand the implementation of complex sql statements. After selecting the corresponding data analysis item and the corresponding data area in step G001, the processing unit will create a table in postgresql. The table creation will be different according to the analysis function item selected by the user, and the data will be inserted into the table. The sql statement corresponding to the data analysis item is loaded to analyze, filter, and process the data. After completing the data processing, the data is saved to Excel through the encapsulated psycopg driver and python processing.
[0035] Furthermore, the plug-in uses PostgreSQL's built-in row_number function combined with window functions to handle paging statistics, data deduplication, data ranking, data grouping, data filtering, time series data analysis, and sequence value generation in Excel. The group by clause, grouping sets, corresponds to multi-dimensional analysis in Excel, simplifying query processing. The rollup clause handles multi-level aggregation and report generation. The cube clause handles all possible grouping combinations and cross-combination analysis of multidimensional data. The date_trunc clause handles data aggregation by date and time granularity in Excel. The over (partition by) clause handles analysis such as calculating averages, rankings within categories, and cumulative summaries within categories in Excel. This allows for rapid processing of large amounts of data and supports local computing, ensuring data security without connecting to the internet.
[0036] In one implementation of the present application, the data area to be processed is labeled based on the data type, data format, and data structure corresponding to the data to be processed. The data areas to be processed with the same label are divided and mapped. Based on the division results, a table structure is dynamically generated. Furthermore, based on the division results, the data insertion amount corresponding to each table structure is determined, and the data to be processed is inserted into the corresponding table structure in batches based on the data insertion amount. Through the mapping relationship, the data in the same table structure is tested for association relationships. If the test result passes, it is determined that the insertion of the data to be processed is complete.
[0037] Specifically, the data to be processed is analyzed, including its numeric, text, and date types. Data formats include specific encoding methods, numerical representations, and text format specifications. Data structures focus on whether the data is a simple flat structure or a complex structure with nested and hierarchical relationships. Based on the analysis results, annotations are added to each data area.
[0038] Furthermore, data regions with the same label are grouped together for division and mapping is performed, that is, data regions with the same label are mapped to each other.
[0039] Furthermore, based on the results of the partitioning and mapping, the table structure is dynamically generated. Specifically, based on the data's annotation information, the data type, length, constraints, and other attributes of the table fields are determined to generate the corresponding table structure. Based on the previous partitioning results, the amount of data to be inserted into each table structure is determined, and a batch insertion strategy is formulated based on factors such as system performance and memory limitations. The data to be processed is divided into several batches and inserted into the corresponding table structure. Batch insertion avoids problems such as system lag or memory overflow caused by processing large amounts of data at once, ensuring a stable and efficient data insertion process.
[0040] Furthermore, after data is inserted into the table structure, the established mapping relationship is used to perform association detection on the data in the same table structure. That is, it is determined whether the data in the same table have a mapping relationship with each other, thereby determining whether the data in the same table have the same labeled area.
[0041] S102. Match the corresponding built-in functions in PostgreSQL according to the data analysis items corresponding to the table and the preset function knowledge graph.
[0042] In one implementation of the present application, the data information and data analysis items in the table are input into a preset processing flow prediction model to output multiple predicted processing steps through the preset processing flow prediction model. Based on multiple functions in the function library, a preset function knowledge graph is constructed; wherein the nodes in the preset function knowledge graph are each function, and the edges in the preset function knowledge graph are the association relationships between each function. Semantic analysis is performed on the multiple predicted processing steps, and the semantic analysis results are converted into node representations, so as to perform node matching in the preset function knowledge graph based on the node representations. Based on the matching results, the built-in functions corresponding to each table are determined.
[0043] Specifically, data information from the table and user-defined data analysis items are collected. The table data includes various types of data, such as numbers, text, and dates, which form the basis for analysis. The data analysis items specify the information the user needs to obtain from this data, such as calculating averages, counting quantities, and classifying data. This data information and data analysis items are then input into a pre-configured process prediction model. This model, trained and optimized using extensive data, understands the characteristics of the data and the intent of the analysis items to output multiple prediction processing steps.
[0044] Based on the numerous functions in the function library, a preset function knowledge graph is constructed. The function library contains a wide variety of functions, each with its own specific functionality, such as mathematical calculation functions like addition and multiplication, and data processing functions like data cleansing and format conversion. In the preset function knowledge graph, each function is considered a node. The relationships between functions are represented by edges. These relationships can take many forms. For example, the output of one function may be the input of another function, and this data flow relationship constitutes an edge. Alternatively, two functions may share functional similarities and exist to solve similar data analysis problems, and an edge can be established between them. By constructing this knowledge graph, the relationships between functions become clear and explicit, providing a structured knowledge network for subsequent function matching.
[0045] Furthermore, semantic analysis is performed on the multiple predicted processing steps output by the previous model to understand the meaning expressed by each processing step. The results of the semantic analysis are converted into node representations, that is, the processing steps are converted into a form that can be matched in the preset function knowledge graph. The semantic information is mapped to the node space of the knowledge graph, so that each processing step corresponds to one or more node representations with similar semantics. Based on the converted node representations, node matching is performed in the preset function knowledge graph to search for nodes in the knowledge graph that are most similar in semantics to the node representation. When matching nodes are found, since these nodes represent functions in the function library, the function corresponding to each predicted processing step can be determined.
[0046] S103. Based on the business information and data analysis items corresponding to the data to be processed, the matching built-in functions are sorted in calling order to generate a built-in function queue.
[0047] In one implementation of the present application, business information and data analysis items are processed for similarity with the historical database, and reference historical data is determined based on the similarity. Based on the preset function knowledge graph, the historical built-in function queue corresponding to the reference historical data is divided, and the historical built-in function queue is divided into multiple sub-queues based on the execution logic order corresponding to the data analysis items. The data processing requirement information corresponding to the business information is obtained. The functions in the multiple sub-queues are re-sorted, and the execution performance information corresponding to the sorted function queues is compared with the data processing requirement information, and the built-in function queue is determined based on the comparison results.
[0048] Specifically, the business information and data analysis items for the current task are first obtained. Business information encompasses various aspects, such as business background, goals, and rules, reflecting the actual scenario and demand orientation of the data processing. Data analysis items clarify the specific analytical goals that users hope to achieve through data processing, such as calculating specific indicators and analyzing data trends. This business information and data analysis items are then compared with the historical database for similarity. Through similarity calculation and comparison, reference historical data is determined based on the similarity results. This reference historical data is selected from the historical database to identify the data and related processing records that are most similar to the current business and analysis needs.
[0049] Furthermore, based on the preset function knowledge graph that has been constructed, the historical built-in function queue corresponding to the reference historical data is processed. The preset function knowledge graph shows the association relationship between functions, including the functional dependency of functions, data flow relationship, etc. According to the execution logic order corresponding to the data analysis items, the historical built-in function queue is divided into multiple sub-queues. For example, in a sales data analysis task, if the execution logic of the data analysis item is to clean the data first, then summarize the sales amount by product category, and finally analyze the sales trend of each product category. Then the function responsible for data cleaning in the historical built-in function queue will be divided into a sub-queue, the function for summary calculation will be divided into another sub-queue, and the function for trend analysis will form another sub-queue.
[0050] Furthermore, the corresponding data processing requirements are extracted from the business information. This includes performance requirements for data processing, such as processing time limits, and resource utilization limits, such as memory and CPU usage limits, to prevent data processing tasks from occupying excessive resources and affecting the operation of other business systems.
[0051] Furthermore, for the multiple sub-queues obtained by division, the functions therein are re-sorted. After the sorting is completed, the execution performance information corresponding to the sorted function queue is obtained, including the expected execution time, possible memory occupied, CPU usage, etc. These execution performance information are then compared with the data processing requirement information obtained previously. If the expected execution time of a function queue is within the time range specified by the data processing requirements, and the resource consumption also meets the restriction requirements, while ensuring other requirements such as the accuracy and completeness of data processing, then this function queue is determined as the final built-in function queue. In this way, it is ensured that the determined built-in function queue can efficiently complete the data analysis task while meeting the business data processing requirements.
[0052] In one implementation of the present application, data scale information corresponding to the data to be processed is obtained; wherein the data scale information includes at least one of the data dimension, the number of data, and the degree of discreteness of the distribution of data values. The data scale information, system resource usage, and multiple sorted function queues are input into a preset decision tree prediction model to output execution performance information corresponding to different function execution orders through the preset decision tree prediction model; wherein the execution performance information includes at least one of the execution time and the remaining resources. The execution performance information is compared with the data processing requirement information to determine the reference function execution order corresponding to each sub-queue. The sub-queues after the order adjustment are spliced to generate a built-in function queue.
[0053] Specifically, the data scale information corresponding to the data to be processed is obtained, including: data dimension, data quantity, and the degree of discreteness of the distribution of data values. Secondly, the system resource usage is obtained, including the current system's CPU usage, memory remaining, disk space and other resource usage status. The data scale information, system resource usage and multiple sorted function queues are input into the preset decision tree prediction model. Among them, the preset decision tree prediction model in the embodiment of the present application is a model that makes decisions and predictions based on rules and data features. Based on the input information, it will analyze and predict the execution of each function queue under the current data scale and system resource conditions.
[0054] Furthermore, the pre-set decision tree prediction model processes and analyzes the input information and outputs execution performance information corresponding to different function execution orders. This performance information primarily includes execution time, which is the time required for each function queue to execute all functions, and remaining resources, which is the amount of CPU, memory, and other resources remaining in the system after the function queue has completed execution.
[0055] Furthermore, after obtaining the execution performance information, it will be compared with the previously determined data processing requirement information. If the execution time of a function queue meets the time limit and the remaining resources also meet the resource limit requirements, then the execution order of this function queue is more likely to be selected. Through this comparison, the reference function execution order corresponding to each sub-queue can be determined, that is, the function execution order that best meets the data processing requirements. After determining the reference function execution order corresponding to each sub-queue, the sub-queues after the order adjustment are spliced. According to the execution logic order corresponding to the data analysis items, the sub-queues are connected in sequence to form a complete built-in function queue.
[0056] In one implementation of the present application, according to the preset function:
[0057]
[0058] Determine the comparison function value corresponding to the subqueue; where q i is the function queue; T qi is the function execution time; T req The upper limit of the function execution time; R qi is the resource consumption value; R req is the upper limit of resource consumption; α is the execution time weight; β is the resource consumption weight; C(q i ) is q i Comparison function values corresponding to the queue. Compare the comparison function values corresponding to each sub-queue to determine the reference function execution order.
[0059] S104 , processing the data to be processed through the built-in function queue, and storing the processed data in Excel to complete the table data analysis.
[0060] In one implementation of the present application, a function call instruction queue is generated based on a built-in function queue, and the built-in functions are sequentially called through the function call instruction queue. The called built-in functions sequentially process the data to be processed; the built-in functions include at least a group by clause, a cube clause, a rollup clause, and an over clause. The processed data is divided into multiple data blocks, and each data block is sequentially stored in Excel.
[0061] Specifically, after the built-in function queue is determined, a function call instruction queue will be generated based on this queue, and the system will call the built-in functions in sequence according to this queue. These built-in functions are called in sequence to process the input data to be processed step by step. When processing data, each function will perform corresponding transformations and calculations on the data according to its own functions and parameter settings. When the data is processed using the built-in function, the processed data results will be obtained. In order to more conveniently store these data in Excel, the processed data is divided into multiple data blocks, and the data of each data block is written to the corresponding position of Excel in a certain order. In this way, the processed data can be fully presented in Excel, which is convenient for users to further view, analyze and process.
[0062] In one implementation of the present application, when the data analysis items include data filtering items, the data in the table is arranged in chronological order, and a corresponding row number is assigned to each row. The row numbers are filtered based on a window function, so that the data in the table is filtered based on the filtered row numbers.
[0063] Specifically, all data in the table is sorted chronologically. After the data is sorted, a row number is assigned to each row of data in the table. The row number is a number assigned to each row of data, starting with the first row and continuing with 1, 2, 3, and so on. This uniquely identifies each row of data. Window functions are then used to filter the previously assigned row numbers. For example, by setting certain conditions, only rows whose row numbers meet certain criteria can be selected. Window functions allow for flexible filtering of row numbers based on specific data analysis needs, resulting in a set of row numbers that meet the requirements. Because each row of data has a corresponding row number, the filtered row number can be used to find the corresponding row in the table. This allows rows of data that meet the requirements to be selected from the original table while excluding those that do not.
[0064] The PostgreSQL-based Excel large-scale data analysis plug-in in the embodiment of the present application is characterized by using a free-installation green version of PostgreSQL and implementing data communication between Excel and the PostgreSQL database through Python's psycopg2 library. Users do not need to worry about PostgreSQL's port, user name, password, installation location, and other information. Users also do not need to understand SQL statements and database configuration knowledge, and can complete the rapid processing of large amounts of data through PostgreSQL's built-in functions. The embodiment of the present application improves the efficiency and security of data processing by simplifying database configuration and operating procedures, solves the performance bottleneck problem of Excel when processing large amounts of data, and has broad application prospects.
[0065] Figure 3 The present invention provides a schematic diagram of a table data analysis device based on a PostgreSQL database. Figure 3 As shown, a table data analysis device 200 based on a PostgreSQL database includes: at least one processor 201; and a memory 202 communicatively connected to the at least one processor 201; wherein the memory 202 stores instructions that can be executed by the at least one processor 201, and the instructions are executed by the at least one processor 201 to enable the at least one processor 201 to: create multiple tables based on the data area to be processed and the data analysis items to be processed selected by the user, and insert the data to be processed in the data area to be processed into the corresponding table; match the corresponding built-in functions in PostgreSQL according to the data analysis items corresponding to the table and the preset function knowledge graph; sort the matching built-in functions in a calling order based on the business information and data analysis items corresponding to the data to be processed, and generate a built-in function queue; process the data to be processed through the built-in function queue, and store the processed data in Excel to complete the table data analysis.
[0066] An embodiment of the present application provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured to: create multiple tables based on a data area to be processed and a data analysis item selected by a user, and insert the data to be processed in the data area to be processed into corresponding tables; match corresponding built-in functions in PostgreSQL according to the data analysis items corresponding to the tables and a preset function knowledge graph; sort the matching built-in functions in a calling order based on business information and data analysis items corresponding to the data to be processed, and generate a built-in function queue; process the data to be processed through the built-in function queue, and store the processed data in Excel to complete table data analysis.
[0067] The various embodiments in this application are described in a progressive manner. Similar portions between the various embodiments can be referenced to each other, and each embodiment focuses on the differences from the other embodiments. In particular, the device, apparatus, and non-volatile computer storage medium embodiments are generally similar to the method embodiments, so their descriptions are relatively simple. For relevant portions, refer to the descriptions of the method embodiments.
[0068] The foregoing is merely an embodiment of the present application and is not intended to limit the present application. It will be apparent to those skilled in the art that various modifications and variations may be made to the embodiments of the present application. However, such modifications or substitutions do not deviate from the spirit and scope of the technical solutions of the various embodiments of the present application.
Claims
1. A table data analysis method based on a PostgreSQL database, characterized in that: The method comprises: Based on the data area to be processed and the data analysis items selected by the user, multiple tables are created, and the data to be processed in the data area to be processed is inserted into the corresponding tables; Match the corresponding built-in functions in PostgreSQL based on the data analysis items corresponding to the table and the preset function knowledge graph; Based on the business information corresponding to the data to be processed and the data analysis items, sorting the matching built-in functions in a calling order to generate a built-in function queue; The data to be processed is processed by the built-in function queue, and the processed data is stored in Excel to complete the table data analysis.
2. A table data analysis method based on a PostgreSQL database according to claim 1, characterized in that: The process of creating multiple tables based on the data area to be processed and the data analysis items selected by the user, and inserting the data to be processed in the data area to be processed into the corresponding tables, specifically includes: Marking the data area to be processed based on the data type, data format and data structure corresponding to the data to be processed; Dividing and mapping the data areas to be processed with the same label; Based on the partitioning results, the table structure is dynamically generated; and, based on the division result, determining the data insertion amount corresponding to each of the table structures, so as to insert the to-be-processed data into the corresponding table structure in batches based on the data insertion amount; Through the mapping relationship, the data in the same table structure are tested for association relationships. If the test result passes, it is determined that the insertion of the data to be processed is completed.
3. A table data analysis method based on a PostgreSQL database according to claim 1, characterized in that: The method of matching corresponding built-in functions in PostgreSQL based on the data analysis items corresponding to the table and the preset function knowledge graph specifically includes: Inputting the data information and data analysis items in the table into a preset processing flow prediction model to output a plurality of predicted processing steps through the preset processing flow prediction model; Based on multiple functions in the function library, the preset function knowledge graph is constructed; wherein the nodes in the preset function knowledge graph are the functions, and the edges in the preset function knowledge graph are the association relationships between the functions; Performing semantic analysis on the plurality of prediction processing steps, and converting the semantic analysis results into node representations, so as to perform node matching in the preset function knowledge graph based on the node representations; Based on the matching results, the built-in functions corresponding to the respective tables are determined.
4. A table data analysis method based on a PostgreSQL database according to claim 1, characterized in that: The step of sorting the matching built-in functions in a calling order based on the business information corresponding to the data to be processed and the data analysis item to generate a built-in function queue specifically includes: Performing similarity processing on the business information and the data analysis items with the historical database, and determining reference historical data based on the similarity; Based on the preset function knowledge graph, the historical built-in function queue corresponding to the reference historical data is divided, so as to divide the historical built-in function queue into a plurality of sub-queues based on the execution logic order corresponding to the data analysis items; Obtaining data processing requirement information corresponding to the business information; The functions in the plurality of sub-queues are re-sorted, the execution performance information corresponding to the sorted function queues is compared with the data processing requirement information, and the built-in function queue is determined based on the comparison result.
5. A table data analysis method based on a PostgreSQL database according to claim 4, characterized in that: The reordering of the functions in the plurality of subqueues, comparing the execution performance information corresponding to the ordered function queues with the data processing requirement information, and determining the built-in function queue based on the comparison results specifically includes: Obtaining data scale information corresponding to the data to be processed; wherein the data scale information includes at least one of the data dimension, the data quantity, and the degree of dispersion of the data value distribution; Inputting the data scale information, system resource usage, and the plurality of sorted function queues into a preset decision tree prediction model, so as to output execution performance information corresponding to different function execution orders through the preset decision tree prediction model; wherein the execution performance information includes at least one of execution time and remaining resources; Comparing the execution performance information with the data processing requirement information to determine a reference function execution order corresponding to each of the sub-queues; The sub-queues after the order adjustment are spliced together to generate the built-in function queue.
6. A table data analysis method based on a PostgreSQL database according to claim 5, characterized in that: Comparing the execution performance information with the data processing requirement information to determine the reference function execution order corresponding to each of the sub-queues specifically includes: According to the preset function: Determine the comparison function value corresponding to the subqueue; where q i is the function queue; T qi is the function execution time; T req The upper limit of the function execution time; R qi is the resource consumption value; R req is the upper limit of resource consumption; α is the execution time weight; β is the resource consumption weight; C(q i ) is q i The comparison function value corresponding to the queue; The comparison function values corresponding to the sub-queues are compared to determine the reference function execution order.
7. A table data analysis method based on a PostgreSQL database according to claim 1, characterized in that: The processing of the to-be-processed data by the built-in function queue and storing the processed data in Excel specifically includes: generating a function call instruction queue based on the built-in function queue, so as to call the built-in functions in sequence through the function call instruction queue; The data to be processed is processed in sequence by calling the built-in function; wherein the built-in function includes at least a group by clause, a cube clause, a rollup clause, and an over clause; The processed data is divided into multiple data blocks, and each of the data blocks is stored in Excel in sequence.
8. A table data analysis method based on a PostgreSQL database according to claim 7, characterized in that: The processing of the data to be processed in sequence by calling the built-in function specifically includes: In the case where the data analysis items include data screening items, the data in the table are arranged in chronological order and a corresponding row number is assigned to each row; The row numbers are filtered based on a window function, so as to filter the data in the table according to the filtered row numbers.
9. A table data analysis device based on a PostgreSQL database, characterized in that: The device comprises a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to execute the method according to any one of claims 1 to 8.
10. A non-volatile computer storage medium storing computer executable instructions, characterized in that: The computer executable instructions can execute the method according to any one of claims 1 to 8.