A method for processing data from multiple list collections using SQL statements.

Abstract

This invention provides a method for processing data from multiple list collections using SQL statements, comprising the following steps: S1: receiving and validating request parameters; S2: storing data from the list collections into a custom in-memory database; S3: validating the SQL statement; S4: parsing the SQL statement and obtaining SELECT information; S5: parsing the SELECT statement in the SELECT information to obtain SQL syntax information; S6: processing the data according to the SQL syntax information; S7: returning the final result data. This invention solves the problem of difficult data processing from multiple list collections. Based on the flexible data processing method of SQL language, it creates a custom in-memory database, eliminating the need to establish a connection with a database server, thus avoiding database security issues. It supports high concurrency, improves efficiency, enables complex data processing, and allows for timely modification and updating of SQL statements as needed, saving time and effort.

Description

Technical Field

[0001] This invention relates to the field of software development, and more particularly to a method for processing data from multiple list collections using SQL statements. Background Technology

[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 a List collection. The elements in the List collection are often of Map type, that is, a key-value data structure.

[0003] In existing technologies, data processing typically employs two methods: parsing the data to be processed using a judgment script and storing and retrieving data using a traditional database. These methods have the following drawbacks:

[0004] Using a simple conditional script to parse the data to be processed, this method is generally only suitable for processing a single list. When multiple lists are involved, they can only be merged. It does not support custom functions, cannot meet flexible and ever-changing needs, and has poor flexibility and extensibility.

[0005] Using traditional databases for data storage and retrieval requires developers to connect to the traditional database, create a separate data table for the value types of key-value pairs of each element in the List collection, insert each element of the list into the data table, write and execute the query statement to get the results, and finally delete the data table. This operation is very cumbersome, requires handling the table names and related table names in the query statement to prevent table name conflicts, and is inefficient because it relies on traditional databases. Summary of the Invention

[0006] The purpose of this invention is to address the shortcomings of existing technologies by proposing a method for processing data from multiple list collections using SQL statements.

[0007] To achieve the above objectives, the present invention adopts the following technical solution:

[0008] A method for processing data from multiple list collections using SQL statements includes the following steps:

[0009] S1: Receive and verify request parameters;

[0010] Includes the following sub-steps:

[0011] S11: Receive request parameters;

[0012] Receive request parameters, which include SQL statements and list sets;

[0013] The SQL statement is a SELECT statement or a With...CTE...SELECT statement;

[0014] The list collection is a Map of key-value pairs, where each key is the name of a data table; each value is a List, corresponding to data in the data table.

[0015] S12: Determine if the SQL statement is empty;

[0016] Determine if the SQL statement is empty. If it is empty, throw an exception indicating that the request is invalid or lacks necessary query information; otherwise, proceed to step S13.

[0017] S13: Determine if the list collection is empty;

[0018] Determine if the list collection 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.

[0019] S14: Determine if the value in the key-value pair is empty;

[0020] Iterate through each key-value pair in the list collection and check if the value in the key-value pair is empty. If it is empty, throw an exception, indicating that the data in the corresponding data table is missing or invalid; if it is not empty, proceed to step S2.

[0021] S2: Store the data in the list collection into a custom in-memory database;

[0022] Includes the following sub-steps:

[0023] S21: Create a custom in-memory database;

[0024] Create a custom in-memory database containing a collection of in-memory tables, and implement the SELECT syntax and the With...CTE...SELECT syntax;

[0025] S22: Iterate through each key-value pair in the list;

[0026] Iterate through each key-value pair in the list collection, using 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;

[0027] The collection of memory tables is a Map key-value pair data type, where the key is the name of the memory table and the value is a List.

[0028] S3: Validate the SQL statement;

[0029] A custom in-memory database receives and validates the SQL statements in the request parameters.

[0030] Specifically, a query statement validator 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 so, proceed to step S4.

[0031] S4: Parse the SQL statement and obtain the SELECT information;

[0032] Create a custom SQL parser to parse SQL statements and obtain SELECT information;

[0033] For CTE statements in SQL statements, 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 Map data type, where the key is an alias in the CTE statement and the value is the SELECT statement in the CTE statement;

[0034] For other ordinary SQL statements, the SELECT information is a key-value pair of type Map, with the key being "OUTPUT" and the value being the SELECT statement.

[0035] S5: Parse the SELECT statement in the SELECT information to obtain the SQL syntax information;

[0036] Iterate through each key-value pair in the SELECT information obtained in step S4, create an ANTLR custom SQL parser, and parse the values ​​in the key-value pairs, i.e. the SELECT statement, to obtain the corresponding SQL syntax information.

[0037] The SQL syntax information includes main table information, join information, condition information, grouping information, aggregate function information, sorting information, and output field information;

[0038] The main table information includes the table name and alias;

[0039] The join information includes the JOIN method, the table name of the join, the alias of the join, and the join conditions; the join information is a key-value pair of Map data type, where the key is the table name of the join, and the value is the JOIN method, the alias of the join, and the join conditions.

[0040] The 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;

[0041] The grouping information is a List, and the elements in the List are the grouping field names;

[0042] The aggregate function information is a key-value pair of Map data type, where the key is the name of the aggregate field and the value is the aggregate function and the aggregate alias.

[0043] The sorting information is a key-value pair of Map data type, where the key is the name of the sorting field and the value is the sorting method;

[0044] The output field information consists of key-value pairs of Map data type, where the key is the output field name and the value is the field alias and the output field function.

[0045] S6: Process data according to SQL syntax information;

[0046] Includes the following sub-steps:

[0047] S61: Get a List of the main table and the joined tables;

[0048] Based on the table names in the main table information and the linked table information, retrieve the corresponding main table list and the corresponding linked table list from the memory table collection;

[0049] S62: Generate intermediate connection result table;

[0050] Create a query iterator, traverse the obtained lists of the main table and the joined tables, and connect the main table and the joined tables according to the join conditions and JOIN method. Generate an intermediate result table based on the keys in the output field information, i.e., the field names.

[0051] The intermediate result table of the connection is a List, and the elements are of Map data type;

[0052] S63: Generate a conditional intermediate results table;

[0053] Create filter conditions based on the condition information, use the filter to filter the intermediate result table of the connection, filter out the data rows that meet the conditions, and generate the condition intermediate result table;

[0054] The intermediate result table for the conditions is a List, and the elements are of Map data type;

[0055] S64: Generate the intermediate results table for grouping;

[0056] Based on the grouping field names in the grouping information, the intermediate result table is grouped according to the grouping field names, and data rows with the same grouping field names are grouped together to generate a grouped intermediate result table.

[0057] The intermediate result table for grouping is a list of Map data types, where the key is the grouping field name, one key represents one group, and the value is a List. The elements of the List are key-value pairs of Map data types, where the key is the aggregation field name and the value is the aggregation field value.

[0058] S65: Generate an intermediate aggregation result table;

[0059] Based on the aggregation function information, the corresponding aggregation function is applied to each group in the intermediate grouping result table to calculate the aggregation result for each group. The name of the aggregation result is called the aggregation alias. The aggregation result is stored in a List to generate the intermediate aggregation result table.

[0060] The intermediate result table of the aggregation is a List, and the elements are key-value pairs of Map data type. 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.

[0061] S66: Generate the query results table;

[0062] Based on the sorting field name and sorting method in the sorting information, a sorter is created to sort the aggregated intermediate result table and generate the query result table.

[0063] The query result table is a List, and the elements are of Map data type;

[0064] S67: Using the key of the current element in the SELECT information as the table name and the query result table as the value, store it in the in-memory table collection Map in the custom in-memory database.

[0065] S7: Returns the final result data;

[0066] Query the value with key OUTPUT in the Map collection of memory tables in the custom in-memory database, and return that value, which is the processed final result data.

[0067] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0068] This invention solves the problem of processing multiple List collections of data. Based on the flexible data processing methods of SQL, it treats the List as a database table by creating a custom in-memory database. Executing SQL statements yields the results without establishing a connection to a database server as in traditional methods, thus eliminating database security issues. It supports high concurrency, pure in-memory data processing, and improves efficiency. Users can implement various complex data processing methods and modify and update SQL statements at any time as needed, saving time and effort. Attached Figure Description

[0069] Figure 1 This is a flowchart illustrating the steps of a method for processing multiple list collections using SQL statements according to the present invention. Detailed Implementation

[0070] To provide a further understanding of the purpose, structure, features, and functions of the present invention, detailed descriptions are provided below with reference to specific embodiments.

[0071] like Figure 1 As shown, a method for processing data from multiple list collections using SQL statements includes the following steps:

[0072] S1: Receive and verify request parameters;

[0073] Includes the following sub-steps:

[0074] S11: Receive request parameters;

[0075] Receive request parameters, which include SQL statements and list sets;

[0076] The SQL statement is a SELECT statement or a With...CTE...SELECT statement;

[0077] The list collection is a Map of key-value pairs, where each key is the name of a data table; each value is a List, corresponding to data in the data table.

[0078] S12: Determine if the SQL statement is empty;

[0079] Determine if the SQL statement is empty. If it is empty, throw an exception indicating that the request is invalid or lacks necessary query information; otherwise, proceed to step S13.

[0080] S13: Determine if the list collection is empty;

[0081] Determine if the list collection 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.

[0082] S14: Determine if the value in the key-value pair is empty;

[0083] Iterate through each key-value pair in the list collection and check if the value in the key-value pair is empty. If it is empty, throw an exception, indicating that the data in the corresponding data table is missing or invalid; if it is not empty, proceed to step S2.

[0084] S2: Store the data in the list collection into a custom in-memory database;

[0085] Includes the following sub-steps:

[0086] S21: Create a custom in-memory database;

[0087] Create a custom in-memory database containing a collection of in-memory tables, and implement the SELECT syntax and the With...CTE...SELECT syntax;

[0088] S22: Iterate through each key-value pair in the list;

[0089] Iterate through each key-value pair in the list collection, using 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;

[0090] The collection of memory tables is a Map key-value pair data type, where the key is the name of the memory table and the value is a List.

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

[0092] S3: Validate the SQL statement;

[0093] A custom in-memory database receives and validates the SQL statements in the request parameters.

[0094] Specifically, a query statement validator 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 so, proceed to step S4.

[0095] S4: Parse the SQL statement and obtain the SELECT information;

[0096] Create a custom SQL parser to parse SQL statements and obtain SELECT information;

[0097] For CTE statements in SQL statements, 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 Map data type, where the key is an alias in the CTE statement and the value is the SELECT statement in the CTE statement;

[0098] For other ordinary SQL statements, the SELECT information is a key-value pair of type Map, with the key being "OUTPUT" and the value being the SELECT statement.

[0099] It supports dynamically inputting different data tables and data 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 dataset requirements, especially for handling multi-source heterogeneous data.

[0100] S5: Parse the SELECT statement in the SELECT information to obtain the SQL syntax information;

[0101] Iterate through each key-value pair in the SELECT information obtained in step S4, create an ANTLR custom SQL parser, and parse the values ​​in the key-value pairs, i.e. the SELECT statement, to obtain the corresponding SQL syntax information.

[0102] The SQL syntax information includes main table information, join information, condition information, grouping information, aggregate function information, sorting information, and output field information;

[0103] The main table information includes the table name and alias;

[0104] The join information includes the JOIN method, the table name of the join, the alias of the join, and the join conditions; the join information is a key-value pair of Map data type, where the key is the table name of the join, and the value is the JOIN method, the alias of the join, and the join conditions.

[0105] The 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;

[0106] The grouping information is a List, and the elements in the List are the grouping field names;

[0107] The aggregate function information is a key-value pair of Map data type, where the key is the name of the aggregate field and the value is the aggregate function and the aggregate alias.

[0108] The sorting information is a key-value pair of Map data type, where the key is the name of the sorting field and the value is the sorting method;

[0109] The output field information consists of key-value pairs of Map data type, where the key is the output field name and the value is the field alias and the output field function.

[0110] By using a custom SQL parser, the various components of an SQL statement (such as table information, join information, condition information, grouping information, etc.) can be parsed in a refined manner. This enables the processing of complex SQL statements, including multi-table joins and aggregate queries, and supports efficient query optimization, ensuring the accuracy and flexibility of queries.

[0111] S6: Process data according to SQL syntax information;

[0112] Includes the following sub-steps:

[0113] S61: Get a List of the main table and the joined tables;

[0114] Based on the table names in the main table information and the linked table information, retrieve the corresponding main table list and the corresponding linked table list from the memory table collection;

[0115] S62: Generate intermediate connection result table;

[0116] Create a query iterator, traverse the obtained lists of the main table and the joined tables, and connect the main table and the joined tables according to the join conditions and JOIN method. Generate an intermediate result table based on the keys in the output field information, i.e., the field names.

[0117] The intermediate result table of the connection is a List, and the elements are of Map data type;

[0118] S63: Generate a conditional intermediate results table;

[0119] Create filter conditions based on the condition information, use the filter to filter the intermediate result table of the connection, filter out the data rows that meet the conditions, and generate the condition intermediate result table;

[0120] The intermediate result table for the conditions is a List, and the elements are of Map data type;

[0121] S64: Generate the intermediate results table for grouping;

[0122] Based on the grouping field names in the grouping information, the intermediate result table is grouped according to the grouping field names, and data rows with the same grouping field names are grouped together to generate a grouped intermediate result table.

[0123] The intermediate result table for grouping is a list of Map data types, where the key is the grouping field name, one key represents one group, and the value is a List. The elements of the List are key-value pairs of Map data types, where the key is the aggregation field name and the value is the aggregation field value.

[0124] S65: Generate an intermediate aggregation result table;

[0125] Based on the aggregation function information, the corresponding aggregation function is applied to each group in the intermediate grouping result table to calculate the aggregation result for each group. The name of the aggregation result is called the aggregation alias. The aggregation result is stored in a List to generate the intermediate aggregation result table.

[0126] The intermediate result table of the aggregation is a List, and the elements are key-value pairs of Map data type. 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.

[0127] S66: Generate the query results table;

[0128] Based on the sorting field name and sorting method in the sorting information, a sorter is created to sort the aggregated intermediate result table and generate the query result table.

[0129] The query result table is a List, and the elements are of Map data type;

[0130] S67: Using the key of the current element in the SELECT information as the table name and the query result table as the value, store it in the in-memory table collection Map in the custom in-memory database.

[0131] The data query process is progressively refined by using multiple intermediate result tables (such as join intermediate result tables, conditional intermediate result tables, grouping intermediate result tables, and aggregation intermediate result tables). Each step can be processed independently, ultimately forming aggregated and sorted results. This ensures 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 manipulating large amounts of data.

[0132] S7: Returns the final result data;

[0133] Query the value with key OUTPUT in the Map collection of memory tables in the custom in-memory database, and return that value, which is the processed final result data.

[0134] The present invention has been described in the above-described embodiments; however, these embodiments are merely examples for implementing the present invention. It must be noted that the disclosed embodiments do not limit the scope of the present invention. Conversely, any modifications and refinements made without departing from the spirit and scope of the present invention are within the scope of patent protection of the present invention.

Claims

1. A method for processing data from multiple list collections using SQL statements, characterized in that: Includes the following steps: S1: Receive and verify request parameters; Includes the following sub-steps: S11: Receive request parameters; S12: Determine if the SQL statement is empty; S13: Determine if the list collection is empty; S14: Determine if the value in the key-value pair is empty; S2: Store the data in the list collection into a custom in-memory database; Includes the following sub-steps: S21: Create a custom in-memory database; S22: Iterate through each key-value pair in the list collection; S3: Validate the SQL statement; A custom in-memory database receives and validates the SQL statements in the request parameters. S4: Parse the SQL statement and obtain the SELECT information; Create a custom SQL parser to parse SQL statements and obtain SELECT information; S5: Parse the SELECT statement in the SELECT information to obtain the SQL syntax information; Iterate through each key-value pair in the SELECT information obtained in step S4, create an ANTLR custom SQL parser, parse the values ​​in the key-value pairs, i.e. the SELECT statement, and obtain the corresponding SQL syntax information; S6: Process data according to SQL syntax information; Includes the following sub-steps: S61: Get a List of the main table and the joined tables; S62: Generate intermediate connection result table; S63: Generate a conditional intermediate results table; S64: Generate the intermediate results table for grouping; S65: Generate an intermediate aggregation result table; S66: Generate the query results table; S67: Using the key of the current element in the SELECT information as the table name and the query result table as the value, store it in the memory table collection Map in the custom memory database; S7: Returns the final result data; Query the value with key OUTPUT in the Map collection of memory tables in the custom in-memory database, and return that value, which is the processed final result data.

2. The method for processing data from multiple list collections using SQL statements as described in claim 1, characterized in that: The specific details of step S1 are as follows: S11: Receive request parameters; Receive request parameters, which include SQL statements and list sets; The SQL statement is a SELECT statement or a With...CTE...SELECT statement; The list collection is a Map of key-value pairs, where each key is the name of a data table; each value is a List, corresponding to data in the data table. S12: Determine if the SQL statement is empty; Check if 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, proceed to step S13; S13: Determine if the list collection is empty; Check if the list collection 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 if the value in the key-value pair is empty; Iterate through each key-value pair in the list collection, and check if 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, proceed to step S2.

3. The method for processing data from multiple list collections using SQL statements as described in claim 1, characterized in that: The specific details of step S2 are as follows: S21: Create a custom in-memory database; Create a custom in-memory database containing a collection of in-memory tables, and implement the SELECT syntax and the With...CTE...SELECT syntax; S22: Iterate through each key-value pair in the list collection; Iterate through each key-value pair in the list collection, using 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 collection of memory tables is a Map key-value pair data type, where the key is the name of the memory table and the value is a List.

4. The method for processing data from multiple list collections using SQL statements as described in claim 1, characterized in that: The specific details 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, throw an exception. If so, proceed to step S4.

5. The method for processing data from multiple list collections using SQL statements as described in claim 1, characterized in that: The specific details of step S4 are as follows: Create a custom SQL parser to parse SQL statements and obtain SELECT information; For CTE statements in SQL statements, 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 Map data type, where the key is an alias in the CTE statement and the value is the SELECT statement in the CTE statement; For other ordinary SQL statements, the SELECT information is a key-value pair of type Map, with the key being "OUTPUT" and the value being the SELECT statement.

6. The method for processing data from multiple list collections using SQL statements as described in claim 1, characterized in that: In step S5, the SQL syntax information includes main table information, join information, condition information, grouping information, aggregate function information, sorting information, and output field information; The main table information includes the table name and alias; The join information includes the JOIN method, the table name of the join, the alias of the join, and the join conditions; the join information is a key-value pair of Map data type, where the key is the table name of the join, and the value is the JOIN method, the alias of the join, and the join conditions. The 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, and the elements in the List are the grouping field names; The aggregate function information is a key-value pair of Map data type, where the key is the name of the aggregate field and the value is the aggregate function and the aggregate alias. The sorting information is a key-value pair of Map data type, where the key is the name of the sorting field and the value is the sorting method; The output field information consists of key-value pairs 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 from multiple list collections using SQL statements as described in claim 1, characterized in that: The specific details of step S6 are as follows: Includes the following sub-steps: S61: Get a List of the main table and the joined tables; Based on the table names in the main table information and the linked table information, retrieve the corresponding main table list and the corresponding linked table list from the memory table collection; S62: Generate intermediate connection result table; Create a query iterator, traverse the obtained lists of the main table and the joined tables, and connect the main table and the joined tables according to the join conditions and JOIN method. Generate an intermediate result table based on the keys in the output field information, i.e., the field names. The intermediate result table of the connection is a List, and the elements are of Map data type; S63: Generate a conditional intermediate results table; Create filter conditions based on the condition information, use the filter to filter the intermediate result table of the connection, filter out the data rows that meet the conditions, and generate the condition intermediate result table; The intermediate result table for the conditions is a List, and the elements are of Map data type; S64: Generate the intermediate results table for grouping; Based on the grouping field names in the grouping information, the intermediate result table is grouped according to the grouping field names, and data rows with the same grouping field names are grouped together to generate a grouped intermediate result table. The intermediate result table for grouping is a list of Map data types, where the key is the grouping field name, one key represents one group, and the value is a List. The elements of the List are key-value pairs of Map data types, where the key is the aggregation field name and the value is the aggregation field value. S65: Generate an intermediate aggregation result table; Based on the aggregation function information, the corresponding aggregation function is applied to each group in the intermediate grouping result table to calculate the aggregation result for each group. The name of the aggregation result is called the aggregation alias. The aggregation result is stored in a List to generate the intermediate aggregation result table. The intermediate result table of the aggregation is a List, and the elements are key-value pairs of Map data type. 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 the query results table; Based on the sorting field name and sorting method in the sorting information, a sorter is created to sort the aggregated intermediate result table and generate the query result table. The query result table is a List, and the elements are of Map data type; S67: Using the key of the current element in the SELECT information as the table name and the query result table as the value, store it in the in-memory table collection Map in the custom in-memory database.

Images