Method for carrying out data processing on multiple list sets by using SQL (Structured Query Language) statements

Abstract

The invention provides a method for performing data processing on a plurality of list sets by using SQL statements. The method comprises the following steps of S1, receiving request parameters and verifying the request parameters; s2, storing the data in the list set into a user-defined memory database; s3, checking the SQL statement; s4, the SQL statement is analyzed, and SELECT information is obtained; s5, a SELECT statement in the SELECT information is analyzed, and SQL grammar information is obtained; s6, processing the data according to the SQL grammar information; s7, returning final result data; the problem that data processing of multiple List list sets is difficult is solved, connection with a database server is not needed based on a flexible data processing mode of the SQL language and a mode of creating the user-defined memory database, the database security problem does not exist, high concurrency is supported, efficiency is improved, complex data processing is achieved, and the method is suitable for large-scale popularization and application. SQL statements are modified and updated at any time according to requirements, and time and labor are

Description

Technical Field

[0001] The present invention relates to the field of software development, and in particular to a method for processing data on multiple list sets using SQL statements. Background Art

[0002] In the field of modern software development, especially in scenarios involving data processing, developers often encounter various data processing requirements. This data is usually stored in the form of List collections. The elements in the List collection are often of Map type, that is, a key-value data structure. In the existing technology, data processing is usually performed by using judgment scripts to parse the data to be processed and traditional databases for data storage and query. These methods have the following disadvantages: Using a simple judgment script to parse the data to be processed. This processing method is generally only suitable for processing a single list. When multiple lists are involved, the multiple lists can only be merged for processing. It does not support custom functions and cannot meet flexible and changing needs. It has poor flexibility and scalability. When using traditional databases for data storage and query, developers need to connect to the traditional database, create a separate data table for the value type of each key-value pair in the list collection, insert each element in the list into the data table, write a query statement and execute it to obtain the result, and finally delete the data table. This operation is very cumbersome and requires processing the table name of the data table and the related table names in the query statement to prevent table name conflicts. In addition, due to its reliance on traditional databases, it is inefficient. Summary of the Invention

[0003] The purpose of the present invention is to solve the shortcomings of the prior art and to propose a method for processing data on multiple list sets using SQL statements.

[0004] To achieve the above object, the present invention adopts the following technical solutions: A method for processing data on multiple list sets using SQL statements, comprising the following steps: S1: Receive request parameters and verify; The following sub-steps are included: S11: Receive request parameters; Receive request parameters, including an SQL statement and a list set; The SQL statement is a SELECT statement or a With...CTE...SELECT statement; The list set is a key-value pair of Map type, where each key is the name of a data table; each value is a List list corresponding to the data in the data table; S12: Determine whether the SQL statement is empty; Determine whether the SQL statement is empty. If it is empty, throw an exception, indicating that the request is invalid or lacks necessary query information; if it is not empty, go to step S13; S13: Determine whether the list set is empty; Determine whether the list set is empty. If it is empty, throw an exception, indicating that the request lacks data or the parameters are invalid; if it is not empty, proceed to step S14; S14: Determine whether the value in the key-value pair is empty; Traverse each key-value pair in the list set and determine whether the value in the key-value pair is empty. If it is empty, throw an exception, indicating that the data in the data table corresponding to the value is missing or invalid; if it is not empty, go to step S2.

[0005] S2: Store the data in the list collection into a custom memory database; The following sub-steps are included: S21: Create a custom in-memory database; Create a custom in-memory database that contains a set of in-memory tables and implements the SELECT syntax and the With...CTE...SELECT syntax. S22: traverse each key-value pair in the list; Traverse each key-value pair in the list collection, use the key as the table name of the memory table and the value as the value of the memory table, and store them in the memory table collection of the custom memory database; The memory table set is a Map key-value pair data type, the key is the memory table name, and the value is a List list.

[0006] S3: Verify SQL statements; The custom in-memory database receives and verifies the SQL statement in the request parameter; Specifically, a query statement verifier is created to verify whether the received SQL statement is a SELECT statement or a With...CTE...SELECT statement. If not, an exception is thrown; if yes, the process proceeds to step S4.

[0007] S4: Parse the SQL statement and obtain SELECT information; Create a custom SQL parser, parse the SQL statement through the custom SQL parser and obtain the SELECT information; For a CTE statement in an SQL statement, the CTE statement is a statement in the SQL statement used to define a temporary result set; the SELECT information is a key-value pair of a Map data type, where the key is the alias in the CTE statement and the value is the SELECT statement in the CTE statement; For other common SQL statements, the SELECT information is a key-value pair of Map type, where the key is "OUTPUT" and the value is the SELECT statement.

[0008] S5: Parse the SELECT statement in the SELECT information to obtain SQL syntax information; Traversing each key-value pair in the SELECT information obtained in step S4, creating an ANTLR custom SQL parser, and the ANTLR custom SQL parser parses the value in the key-value pair, that is, the SELECT statement, and obtains the corresponding SQL syntax information; The SQL syntax information includes main table information, joined table information, condition information, grouping information, aggregate function information, sorting information and output field information; The main table information includes table name and alias; The table join information includes the JOIN method, the table name of the joined table, the alias of the joined table, and the table join condition; the table join information is a key-value pair of the Map data type, the key is the table name of the joined table, and the value is the JOIN method, the alias of the joined table, and the table join condition; The table join condition is a key-value pair of Map data type, where the key is the condition name and the value is the condition symbol and condition value; The grouping information is a List list, and the elements in the List list are grouping field names; The aggregation function information is a key-value pair of Map data type, where the key is the aggregation field name and the value is the aggregation function and aggregation alias; The sorting information is a key-value pair of Map data type, where the key is the sorting field name and the value is the sorting method; The output field information is a key-value pair of Map data type, where the key is the output field name and the value is the field alias and the output field function; S6: Process data according to SQL syntax information; The following sub-steps are included: S61: Get the List of the main table and the linked table; According to the table names in the main table information and the linked table information, obtain the List list of the corresponding main table and the List list of the corresponding linked table from the memory table collection; S62: Generate a connection intermediate result table; Create a query iterator, traverse the obtained List of the main table and the joined table, connect the main table and the joined table according to the join condition and JOIN method, and generate the joined intermediate result table based on the key in the output field information, that is, the output field name; The connection intermediate result table is a List list, and the elements are of Map data type; S63: Generate a conditional intermediate result table; Create filter conditions based on the condition information, use the filter to filter the connection intermediate result table, filter out the data rows that meet the conditions, and generate the conditional intermediate result table; The conditional intermediate result table is a List list, and the elements are of Map data type; S64: Generate a grouping intermediate result table; According to the grouping field name in the grouping information, the conditional intermediate result table is grouped according to the grouping field name, and the data rows with the same grouping field name are grouped together to generate a grouped intermediate result table; The grouping intermediate result table is a list of Map data types, where the key is the grouping field name, one key is one group, and the value is a List list. The elements of the List list are key-value pairs of Map data types, where the key is the aggregate field name and the value is the aggregate field value. S65: Generate an aggregate intermediate result table; According to the aggregation function information, the corresponding aggregation function is used for each group in the grouped intermediate result table to calculate the aggregation result of each group. The name of the aggregation result is the aggregation alias, and the aggregation result is stored in the List list to generate the aggregation intermediate result table; The aggregation intermediate result table is a List list, the elements of which are key-value pairs of Map data type, where the key is the grouping field name and the aggregation alias, and the value is the value of the grouping field and the aggregation field; S66: Generate a query result table; Create a sorter based on the sort field name and sorting method in the sorting information, sort the aggregate intermediate result table, and generate a query result table; The query result table is a List list, and the elements are of Map data type; S67: Use the key of the current element in the SELECT information as the table name and the query result table as the value to store it in the memory table set Map in the custom memory database.

[0009] S7: Return the final result data; Query the value with the key OUTPUT in the memory table collection Map in the custom memory database and return the value, which is the processed final result data.

[0010] Compared with the prior art, the present invention has the following beneficial effects: This invention solves the problem of difficult data processing of multiple lists. Based on the flexible data processing methods of the SQL language, this invention creates a custom in-memory database, processes lists as database tables, and executes SQL statements to obtain results. This eliminates the need to establish a connection to a database server as in traditional methods, thus eliminating database security issues. It supports high concurrency and pure in-memory data processing, improving efficiency. Users can implement various complex data processing methods and can modify and update SQL statements at any time as needed, saving time and effort. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] Figure 1 The present invention is a flowchart of a method for processing data on multiple list sets using SQL statements. DETAILED DESCRIPTION

[0012] In order to provide a further understanding of the purpose, structure, features, and functions of the present invention, the present invention is described in detail below with reference to the embodiments.

[0013] like Figure 1 As shown, a method for processing data on multiple list sets using SQL statements includes the following steps: S1: Receive request parameters and verify; The following sub-steps are included: S11: Receive request parameters; Receive request parameters, including an SQL statement and a list set; The SQL statement is a SELECT statement or a With...CTE...SELECT statement; The list set is a key-value pair of Map type, where each key is the name of a data table; each value is a List list corresponding to the data in the data table; S12: Determine whether the SQL statement is empty; Determine whether the SQL statement is empty. If it is empty, throw an exception, indicating that the request is invalid or lacks necessary query information; if it is not empty, go to step S13; S13: Determine whether the list set is empty; Determine whether the list set is empty. If it is empty, throw an exception, indicating that the request lacks data or the parameters are invalid; if it is not empty, proceed to step S14; S14: Determine whether the value in the key-value pair is empty; Traverse each key-value pair in the list set and determine whether the value in the key-value pair is empty. If it is empty, throw an exception, indicating that the data in the data table corresponding to the value is missing or invalid; if it is not empty, go to step S2.

[0014] S2: Store the data in the list collection into a custom memory database; The following sub-steps are included: S21: Create a custom in-memory database; Create a custom in-memory database that contains a set of in-memory tables and implements the SELECT syntax and the With...CTE...SELECT syntax. S22: traverse each key-value pair in the list; Traverse each key-value pair in the list collection, use the key as the table name of the memory table and the value as the value of the memory table, and store them in the memory table collection of the custom memory database; The memory table set is a Map key-value pair data type, the key is the memory table name, and the value is a List list.

[0015] Use a custom in-memory database for data storage and operations. By storing data tables in memory, frequent disk I / O operations are avoided, improving query and data processing efficiency.

[0016] S3: Verify SQL statements; The custom in-memory database receives and verifies the SQL statement in the request parameter; Specifically, a query statement verifier is created to verify whether the received SQL statement is a SELECT statement or a With...CTE...SELECT statement. If not, an exception is thrown; if yes, the process proceeds to step S4.

[0017] S4: Parse the SQL statement and obtain SELECT information; Create a custom SQL parser, parse the SQL statement through the custom SQL parser and obtain the SELECT information; For a CTE statement in an SQL statement, the CTE statement is a statement in the SQL statement used to define a temporary result set; the SELECT information is a key-value pair of a Map data type, where the key is the alias in the CTE statement and the value is the SELECT statement in the CTE statement; For other common SQL statements, the SELECT information is a key-value pair of Map type, where the key is "OUTPUT" and the value is the SELECT statement.

[0018] Supports dynamic input of different data tables and lists. Users can provide data in various formats, which can be processed through dynamically generated queries. It provides flexible data source access methods to adapt to different data set requirements, especially for processing multi-source heterogeneous data.

[0019] S5: Parse the SELECT statement in the SELECT information to obtain SQL syntax information; Traversing each key-value pair in the SELECT information obtained in step S4, creating an ANTLR custom SQL parser, and the ANTLR custom SQL parser parses the value in the key-value pair, that is, the SELECT statement, and obtains the corresponding SQL syntax information; The SQL syntax information includes main table information, joined table information, condition information, grouping information, aggregate function information, sorting information and output field information; The main table information includes table name and alias; The table join information includes the JOIN method, the table name of the joined table, the alias of the joined table, and the table join condition; the table join information is a key-value pair of the Map data type, the key is the table name of the joined table, and the value is the JOIN method, the alias of the joined table, and the table join condition; The table join condition is a key-value pair of Map data type, where the key is the condition name and the value is the condition symbol and condition value; The grouping information is a List list, and the elements in the List list are grouping field names; The aggregation function information is a key-value pair of Map data type, where the key is the aggregation field name and the value is the aggregation function and aggregation alias; The sorting information is a key-value pair of Map data type, where the key is the sorting field name and the value is the sorting method; The output field information is a key-value pair of Map data type, where the key is the output field name and the value is the field alias and the output field function; Through the custom SQL parser, each component of the SQL statement (such as table information, connection information, condition information, grouping information, etc.) is analyzed in detail. It can process complex SQL statements, including multi-table connection, aggregate query, etc., support efficient query optimization, and ensure the accuracy and flexibility of the query.

[0020] S6: Process data according to SQL syntax information; The following sub-steps are included: S61: Get the List of the main table and the linked table; According to the table names in the main table information and the linked table information, obtain the List list of the corresponding main table and the List list of the corresponding linked table from the memory table collection; S62: Generate a connection intermediate result table; Create a query iterator, traverse the obtained List of the main table and the joined table, connect the main table and the joined table according to the join condition and JOIN method, and generate the joined intermediate result table based on the key in the output field information, that is, the output field name; The connection intermediate result table is a List list, and the elements are of Map data type; S63: Generate a conditional intermediate result table; Create filter conditions based on the condition information, use the filter to filter the connection intermediate result table, filter out the data rows that meet the conditions, and generate the conditional intermediate result table; The conditional intermediate result table is a List list, and the elements are of Map data type; S64: Generate a grouping intermediate result table; According to the grouping field name in the grouping information, the conditional intermediate result table is grouped according to the grouping field name, and the data rows with the same grouping field name are grouped together to generate a grouped intermediate result table; The grouping intermediate result table is a list of Map data types, where the key is the grouping field name, one key is one group, and the value is a List list. The elements of the List list are key-value pairs of Map data types, where the key is the aggregate field name and the value is the aggregate field value. S65: Generate an aggregate intermediate result table; According to the aggregation function information, the corresponding aggregation function is used for each group in the grouped intermediate result table to calculate the aggregation result of each group. The name of the aggregation result is the aggregation alias, and the aggregation result is stored in the List list to generate the aggregation intermediate result table; The aggregation intermediate result table is a List list, the elements of which are key-value pairs of Map data type, where the key is the grouping field name and the aggregation alias, and the value is the value of the grouping field and the aggregation field; S66: Generate a query result table; Create a sorter based on the sort field name and sorting method in the sorting information, sort the aggregate intermediate result table, and generate a query result table; The query result table is a List list, and the elements are of Map data type; S67: Use the key of the current element in the SELECT information as the table name and the query result table as the value to store it in the memory table set Map in the custom memory database.

[0021] The data query process is gradually refined through multiple intermediate result tables (such as connection intermediate result tables, conditional intermediate result tables, grouped intermediate result tables, and aggregated intermediate result tables). Each step can be processed independently and ultimately form aggregated and sorted results; ensuring that data can be accurately filtered, grouped, and summarized in multi-level processing, supporting complex data analysis needs and avoiding performance bottlenecks caused by directly operating on large amounts of data.

[0022] S7: Return the final result data; Query the value with the key OUTPUT in the memory table collection Map in the custom memory database and return the value, which is the processed final result data.

[0023] The present invention has been described with reference to the above embodiments. However, the above embodiments are merely exemplary embodiments of the present invention. It should be noted that the disclosed embodiments do not limit the scope of the present invention. On the contrary, modifications and improvements that do not depart from the spirit and scope of the present invention are intended to be protected by the present invention.

Claims

1. A method for processing data on multiple list sets using SQL statements, characterized by: The following steps are involved: S1: Receive request parameters and verify; The following sub-steps are included: S11: Receive request parameters; S12: Determine whether the SQL statement is empty; S13: Determine whether the list set is empty; S14: Determine whether the value in the key-value pair is empty; S2: Store the data in the list collection into a custom memory database; The following sub-steps are included: S21: Create a custom in-memory database; S22: traverse each key-value pair in the list set; S3: Verify SQL statements; The custom in-memory database receives and verifies the SQL statement in the request parameter; S4: Parse the SQL statement and obtain SELECT information; Create a custom SQL parser, parse the SQL statement through the custom SQL parser and obtain the SELECT information; S5: Parse the SELECT statement in the SELECT information to obtain SQL syntax information; Traverse each key-value pair in the SELECT information obtained in step S4, create an ANTLR custom SQL parser, parse the value in the key-value pair, that is, the SELECT statement, and obtain the corresponding SQL syntax information; S6: Process data according to SQL syntax information; The following sub-steps are included: S61: Get the List of the main table and the linked table; S62: Generate a connection intermediate result table; S63: Generate a conditional intermediate result table; S64: Generate a grouping intermediate result table; S65: Generate an aggregate intermediate result table; S66: Generate a query result table; S67: Use the key of the current element in the SELECT information as the table name and the query result table as the value to store it in the memory table set Map in the custom memory database; S7: Return the final result data; Query the value with the key OUTPUT in the memory table collection Map in the custom memory database and return the value, which is the processed final result data.

2. The method for processing data on multiple list sets using SQL statements according to claim 1, wherein: The specific contents of step S1 are as follows: S11: Receive request parameters; Receive request parameters, including an SQL statement and a list set; The SQL statement is a SELECT statement or a With...CTE...SELECT statement; The list set is a key-value pair of Map type, where each key is the name of a data table; each value is a List list corresponding to the data in the data table; S12: Determine whether the SQL statement is empty; Determine whether the SQL statement is empty. If it is empty, an exception is thrown, indicating that the request is invalid or lacks necessary query information; If it is not empty, go to step S13; S13: Determine whether the list set is empty; Determine whether the list collection is empty. If it is empty, an exception is thrown, indicating that there is a lack of data in the request or the parameters are invalid; If it is not empty, go to step S14; S14: Determine whether the value in the key-value pair is empty; Traverse each key-value pair in the list collection and determine whether the value in the key-value pair is empty. If it is empty, an exception is thrown, indicating that the data in the data table corresponding to the value is missing or invalid; If it is not empty, go to step S2.

3. The method for processing data on multiple list sets using SQL statements according to claim 1, wherein: The specific contents of step S2 are as follows: S21: Create a custom in-memory database; Create a custom in-memory database that contains a set of in-memory tables and implements the SELECT syntax and the With...CTE...SELECT syntax. S22: traverse each key-value pair in the list set; Traverse each key-value pair in the list collection, use the key as the table name of the memory table and the value as the value of the memory table, and store them in the memory table collection of the custom memory database; The memory table set is a Map key-value pair data type, the key is the memory table name, and the value is a List list.

4. The method for processing data on multiple list sets using SQL statements according to claim 1, wherein: The specific contents of step S3 are as follows: Create a query statement validator to verify whether the received SQL statement is a SELECT statement or a With...CTE...SELECT statement. If not, an exception is thrown. If yes, go to step S4.

5. The method for processing data on multiple list sets using SQL statements according to claim 1, wherein: The specific contents of step S4 are as follows: Create a custom SQL parser, parse the SQL statement through the custom SQL parser and obtain the SELECT information; For a CTE statement in an SQL statement, the CTE statement is a statement in the SQL statement used to define a temporary result set; the SELECT information is a key-value pair of a Map data type, where the key is the alias in the CTE statement and the value is the SELECT statement in the CTE statement; For other common SQL statements, the SELECT information is a key-value pair of Map type, where the key is "OUTPUT" and the value is the SELECT statement.

6. The method for processing data on multiple list sets using SQL statements according to claim 1, wherein: In step S5, the SQL syntax information includes main table information, joined table information, condition information, grouping information, aggregate function information, sorting information, and output field information; The main table information includes table name and alias; The table join information includes the JOIN method, the table name of the joined table, the alias of the joined table, and the table join condition; the table join information is a key-value pair of the Map data type, the key is the table name of the joined table, and the value is the JOIN method, the alias of the joined table, and the table join condition; The table join condition is a key-value pair of Map data type, where the key is the condition name and the value is the condition symbol and condition value; The grouping information is a List list, and the elements in the List list are grouping field names; The aggregation function information is a key-value pair of Map data type, where the key is the aggregation field name and the value is the aggregation function and aggregation alias; The sorting information is a key-value pair of Map data type, where the key is the sorting field name and the value is the sorting method; The output field information is a key-value pair of Map data type, where the key is the output field name and the value is the field alias and the output field function.

7. The method for processing data on multiple list sets using SQL statements according to claim 1, wherein: The specific contents of step S6 are as follows: The following sub-steps are included: S61: Get the List of the main table and the linked table; According to the table names in the main table information and the linked table information, obtain the List list of the corresponding main table and the List list of the corresponding linked table from the memory table collection; S62: Generate a connection intermediate result table; Create a query iterator, traverse the obtained List of the main table and the joined table, connect the main table and the joined table according to the join condition and JOIN method, and generate the joined intermediate result table based on the key in the output field information, that is, the output field name; The connection intermediate result table is a List list, and the elements are of Map data type; S63: Generate a conditional intermediate result table; Create filter conditions based on the condition information, use the filter to filter the connection intermediate result table, filter out the data rows that meet the conditions, and generate the conditional intermediate result table; The conditional intermediate result table is a List list, and the elements are of Map data type; S64: Generate a grouping intermediate result table; According to the grouping field name in the grouping information, the conditional intermediate result table is grouped according to the grouping field name, and the data rows with the same grouping field name are grouped together to generate a grouped intermediate result table; The grouping intermediate result table is a list of Map data types, where the key is the grouping field name, one key is one group, and the value is a List list. The elements of the List list are key-value pairs of Map data types, where the key is the aggregate field name and the value is the aggregate field value. S65: Generate an aggregate intermediate result table; According to the aggregation function information, the corresponding aggregation function is used for each group in the grouped intermediate result table to calculate the aggregation result of each group. The name of the aggregation result is the aggregation alias, and the aggregation result is stored in the List list to generate the aggregation intermediate result table; The aggregation intermediate result table is a List list, the elements of which are key-value pairs of Map data type, where the key is the grouping field name and the aggregation alias, and the value is the value of the grouping field and the aggregation field; S66: Generate a query result table; Create a sorter based on the sort field name and sorting method in the sorting information, sort the aggregate intermediate result table, and generate a query result table; The query result table is a List list, and the elements are of Map data type; S67: Use the key of the current element in the SELECT information as the table name and the query result table as the value to store it in the memory table set Map in the custom memory database.

Images