SQL generation method and device and computer readable storage medium

Abstract

The invention discloses an SQL (Structured Query Language) generation method and device and a computer readable storage medium, and relates to the technical field of data processing, the method comprises the following steps: loading an SQL template according to a starting point position generated by an SQL; the SQL template is analyzed, and placeholders in the SQL template are recorded; obtaining a replacement character string in a data exchange format, and searching a corresponding SQL fragment in the replacement character string according to the placeholder; the placeholder in the SQL template is replaced on the basis of the SQL fragment in the character string; and combining the replaced SQL fragments to determine a complete SQL statement, so that the technical defect of code redundancy during SQL generation is overcome, and the technical effect of reducing the code redundancy during SQL generation is realized.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a SQL generation method, device, and computer-readable storage medium. Background Art

[0002] The current SQL generation technology can automatically generate SQL statements, but the current SQL writing method does not establish an effective code reuse mechanism. When similar queries need to be executed, developers will write new SQL statements from scratch, which often leads to a large number of similar but not completely identical SQL codes being repeated, causing code redundancy.

[0003] The above contents are only used to assist in understanding the technical solution of the present application and do not constitute an admission that the above contents are prior art. Summary of the invention

[0004] The main purpose of this application is to provide a SQL generation method, device and computer-readable storage medium, aiming to solve the technical problem of how to reduce code redundancy in SQL generation.

[0005] To achieve the above purpose, the present application proposes a SQL generation method, which includes: Load the SQL template according to the starting point of SQL generation; Parsing the SQL template and recording the placeholders in the SQL template; Obtain a replacement string in a data exchange format, and search for a corresponding SQL fragment in the replacement string according to the placeholder; Replacing the placeholder in the SQL template based on the SQL fragment in the character string; The replaced SQL fragments are merged to determine a complete SQL statement.

[0006] In one embodiment, before the step of loading the SQL template according to the starting point position generated by the SQL, the following steps are included: A template string including the placeholder is defined as the SQL template.

[0007] In one embodiment, the step of parsing the SQL template and recording the placeholders in the SQL template includes: Traversing the SQL template character by character; According to the format of the placeholder, match the character string that matches the start delimiter and the end delimiter of the placeholder; Determine the location of said placeholder and record it In one embodiment, the step of obtaining a replacement string in a data exchange format and searching for a corresponding SQL fragment in the replacement string according to the placeholder comprises: Acquire the replacement string written by the user, wherein the replacement string includes a preset placeholder and a key-value pair of the corresponding SQL fragment; According to the recorded placeholder, the corresponding SQL fragment is searched in the replacement string.

[0008] In one embodiment, the step of replacing the placeholder in the SQL template based on the SQL fragment in the character string includes: Parsing the structure of the SQL fragment to determine the type of the SQL fragment; If the SQL fragment is a static parameter, the placeholder is directly replaced; If the SQL fragment is a call parameter, the corresponding function is called to obtain the value of the SQL fragment and replace the placeholder.

[0009] In one embodiment, if the SQL fragment is a call parameter, calling a corresponding function to obtain the value of the SQL fragment, and performing the step of replacing the placeholder includes: Calling the @foreach function including the SQL fragment template, the start value, the end value and the separator to process the repeated SQL fragment; The @foreach function loops through each value from the start value to the end value, and replaces the placeholder in the SQL fragment template with the value; Each of the replaced SQL fragment templates is connected using the separator to generate a complete SQL fragment.

[0010] In one embodiment, the step of merging the replaced SQL fragments to determine a complete SQL statement includes: Performing syntax verification on the replaced SQL fragment; The SQL fragments that have passed the verification are merged using a preset merging delimiter to determine a complete SQL statement.

[0011] In one embodiment, after the step of merging the replaced SQL fragments to determine a complete SQL statement, the following steps are included: The complete SQL statement is tested, and the SQL generation method is modified according to the test result to meet the requirements for running the SQL statement.

[0012] In addition, to achieve the above objectives, the present application also proposes a SQL generation device, which includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the SQL generation method described above.

[0013] In addition, to achieve the above objectives, the present application also proposes a storage medium, which is a computer-readable storage medium, and stores a computer program on the storage medium. When the computer program is executed by a processor, the steps of the SQL generation method described above are implemented.

[0014] The present application provides an SQL generation method. The present application firstly loads a predefined SQL template, without having to write the entire SQL statement from scratch, thereby avoiding the repetition of SQL code. By replacing the placeholders in the SQL template with actual SQL fragments, a complete SQL statement can be dynamically generated, and a SQL statement with a clear structure and easy maintenance can be generated, thereby improving the readability and maintainability of the code. By using the SQL template and the placeholder replacement mechanism, similar SQL queries can be uniformly managed and reused, thereby avoiding the redundancy of similar but not completely identical SQL codes.

[0015] In summary, the present application loads an SQL template according to the starting point position of SQL generation; parses the SQL template and records the placeholders in the SQL template; obtains a replacement string in a data exchange format, and searches for corresponding SQL fragments in the replacement string according to the placeholders; replaces the placeholders in the SQL template based on the SQL fragments in the string; merges the replaced SQL fragments to determine a complete SQL statement, thereby overcoming the technical defect of code redundancy during SQL generation and achieving the technical effect of reducing code redundancy in SQL generation. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0017] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0018] Figure 1 A flowchart of the first embodiment of the SQL generation method of this application is provided; Figure 2A flowchart of the second embodiment of the SQL generation method of this application is provided; Figure 3 A flowchart of the third embodiment of the SQL generation method of this application is provided; Figure 4 A flowchart of the fourth embodiment of the SQL generation method of this application is provided; Figure 5 This is a schematic diagram of the device structure of the hardware operating environment involved in the SQL generation method in the embodiment of the present application.

[0019] The purpose, features and advantages of this application will be further described in conjunction with the embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0020] It should be understood that the specific embodiments described herein are only used to explain the technical solutions of the present application and are not used to limit the present application.

[0021] In order to better understand the technical solution of the present application, a detailed description will be given below in conjunction with the accompanying drawings and specific implementation methods.

[0022] The main solutions of the embodiments of this application are: Currently, SQL generation technology can automatically generate SQL statements, but the current SQL writing method does not establish an effective code reuse mechanism. When similar queries need to be executed, developers will write new SQL statements from scratch, which often leads to a large number of similar but not completely identical SQL codes being repeated, causing code redundancy.

[0023] The present application overcomes the technical defect of code redundancy during SQL generation by loading an SQL template according to the starting point of SQL generation; parsing the SQL template and recording the placeholders in the SQL template; obtaining a replacement string in a data exchange format, and searching for a corresponding SQL fragment in the replacement string according to the placeholders; replacing the placeholders in the SQL template based on the SQL fragments in the string; merging the replaced SQL fragments to determine a complete SQL statement, thereby overcoming the technical defect of code redundancy during SQL generation and achieving the technical effect of reducing code redundancy in SQL generation.

[0024] It should be noted that the execution subject of this embodiment can be a SQL generation system, or a computing service device with data processing, network communication and program running functions, such as a tablet computer, a personal computer, a mobile phone, etc., or a control device of a SQL generation system capable of realizing the above functions, etc., and this embodiment does not specifically limit this. The following takes the SQL generation system as an example of the execution subject to illustrate this embodiment and the following embodiments.

[0025] Embodiment 1 Based on this, this application proposes a SQL generation method of the first embodiment, please refer to Figure 1 , the SQL generation method includes: Step S100, loading the SQL template according to the starting point position of SQL generation.

[0026] Through pre-defined SQL templates, you can dynamically fill in the placeholders or variables in the template according to different scenarios or data operation requirements to generate specific SQL statements.

[0027] In this embodiment, SQL (Structured Query Language) is a standard programming language for managing relational database systems, and is used to create, query, update, and manage data in a database. The starting point is the initial condition that triggers the loading of the SQL template, and is a specific user operation. The SQL template is a predefined set of SQL statements that contains preset placeholders.

[0028] As an optional implementation, the instruction position input by the user determines the starting position of SQL generation, and the corresponding SQL template is read from the database.

[0029] Step S200, parsing the SQL template and recording the placeholders in the SQL template.

[0030] Before sending the query to the database for execution, placeholders in the template are identified and processed, using placeholders to separate the static part of the query from the dynamically passed in parameters.

[0031] In this embodiment, the placeholder is a symbol mark in the SQL template used to indicate a position that will be replaced by an actual value in the future.

[0032] As an optional implementation, string segmentation and regular expressions are used to identify and record placeholders.

[0033] Optionally, step S200 includes: Step S210, traversing the SQL template character by character.

[0034] By traversing character by character, the placeholders in the SQL template are identified for subsequent replacement operations.

[0035] In this embodiment, traversal refers to the process of accessing and processing each character in the string, starting from the first character of the template and traversing to the last character.

[0036] Step S220 , matching a character string that matches the start delimiter and the end delimiter of the placeholder according to the format of the placeholder.

[0037] Match the start and end delimiters that match the placeholder format to accurately identify all placeholders in the template.

[0038] In this embodiment, the start delimiter is the start mark of the placeholder, used to indicate the start position of the placeholder, and includes "${", "@", "[", etc. The end delimiter is the end mark of the placeholder, used to indicate the end position of the placeholder, and corresponds to the start delimiter, and together defines the scope of the placeholder, includes "}", "]", space, etc.

[0039] Step S230, determining and recording the position of the placeholder.

[0040] As an optional implementation, according to the standard of the SQL template, the start delimiter and the end delimiter of the placeholder are clarified, the SQL template is traversed character by character, and the character matching the start delimiter of the placeholder is searched. When the matching start delimiter is found, the subsequent characters are checked until the character matching the end delimiter is found, and the start position and end position of the placeholder, as well as the content of the placeholder are recorded.

[0041] Exemplarily, each character in the template is checked. If the start tag "${" of the placeholder is encountered, the placeholder state is set to "start" and the starting position of the placeholder is recorded. If the end tag "}" of the placeholder is encountered in the placeholder state, the placeholder state is set to "end" and the end position of the placeholder and the content of the entire placeholder are recorded.

[0042] Step S300, obtaining a replacement string in a data exchange format, and searching for a corresponding SQL fragment in the replacement string according to the placeholder.

[0043] When building database queries, the data exchange format and SQL statements are dynamically combined. By replacing placeholders with specific SQL fragments, SQL statements can be flexibly built and executed according to different input data.

[0044] In this embodiment, the data exchange format is a standard format used when exchanging data between different systems or applications, including JSON (JavaScript Object Notation), XML (Extensible Markup Language), CSV (Comma-Separated Values), etc.

[0045] Optionally, step S300 includes: Step S310, obtaining the replacement string written by the user, wherein the replacement string includes a preset placeholder and a key-value pair of the corresponding SQL fragment.

[0046] Optionally, the format of the obtained replacement string is checked, the placeholders contained in the replacement string are verified, and the syntax of the SQL fragment is checked to see if it complies with the SQL language specification. Problems with the string that occur during the check are notified to the user through a pop-up window.

[0047] For example, check whether the key-value pair format is used correctly in the replacement string, whether there are correct separators between key values, verify whether the replacement string contains all expected placeholders, and whether there are undefined placeholders, and preliminarily check whether there are obvious syntax errors such as unclosed quotes, mismatched brackets, etc.

[0048] Step S320: searching the replacement string for the corresponding SQL fragment according to the recorded placeholder.

[0049] Exemplarily, an XML format replacement string containing placeholders is defined, and the corresponding SQL fragment in the XML format string is found according to the placeholders identified in the SQL template, and the XML document is parsed to extract <columns>, <condition>In the SQL template, find and replace all placeholders {columns}, {table}, and {condition}, and output or return the generated SQL statement.

[0050] As an exemplary implementation, the code is as follows: " <data> <columns> id, name, age< / columns> < / data> < / condition> and users <condition> age>18< / condition> ”.

[0051] The SQL template code is as follows: "SELECT {columns} FROM {table} WHERE {condition}".

[0052] The SQL statement generated after processing is as follows: "SELECT id, name, age FROM users WHERE age>18".

[0053] Step S400: Replace the placeholder in the SQL template based on the SQL fragment in the character string.

[0054] According to the SQL fragment provided by the user or predefined by the system, and the placeholder defined in the SQL template, the placeholder is replaced with the corresponding SQL fragment to generate a SQL query statement that can be directly executed.

[0055] In this embodiment, the character string is a data structure containing SQL fragments used to replace placeholders in the SQL template.

[0056] Step S500: merge the replaced SQL fragments to determine a complete SQL statement.

[0057] By merging SQL fragments, a SQL query that can be directly executed by the database is constructed. All placeholders defined in the SQL template are replaced by the corresponding SQL fragments. These fragments and the unchanged parts of the SQL template are merged into a continuous string, that is, a complete SQL statement.

[0058] In this embodiment, merging is to combine the replaced SQL fragments with the unchanged parts in the SQL template into a complete SQL statement. A complete SQL statement is an SQL query statement with complete structure and correct syntax and can be directly executed by the database.

[0059] Optionally, step S500 includes: Step S510: Verify the syntax of the replaced SQL fragment.

[0060] Step S520: merge the SQL fragments that have passed the verification using a preset merging delimiter to determine a complete SQL statement.

[0061] As an example of this embodiment, a JSON format string containing placeholders is defined, and the corresponding SQL fragment in the JSON format string is found according to the placeholders identified in the SQL template. A SQL query template containing placeholders is defined in sqlEntry, and there are multiple levels of placeholders, among which ${fields}, ${table}, ${ltvFields}, and ${conditions} are high-level placeholders, which are referenced in sqlEntry, and each can be further parsed into a specific value or expression. The value of ${ltvFields} contains a loop processing function @foreach, which generates a series of field aliases according to the given template, starting value, ending value, and separator. At runtime, the placeholders in the SQL template are replaced according to the provided values ​​or expressions, and after placeholder replacement and loop processing, the final SQL query statement is generated.

[0062] As an exemplary implementation, the code example is as follows: "{"sqlEntry": "SELECT ${fields} FROM ${table} WHERE ${conditions}","${fields}": "a, b, c, ${ltvFields}","${table}": "test_table tmp2","${ltvFields}": "@foreach('tmp2.sumpay_@id AS \"LTV@id\"', 1, 7, ',')","${conditions}": "a>1 and b>4"}” where sqlEntry is a string containing a SQL query template, where ${...} are placeholders to be replaced by corresponding values ​​at runtime. ${fields}, ${table}, ${ltvFields}, and ${conditions} are higher-level placeholders that are referenced in sqlEntry; the value of ${ltvFields} itself contains the loop processing function @foreach.

[0063] The SQL statement generated after processing is as follows: "PlaintextSELECT a, b, c, tmp2.sumpay_1 AS "LTV1",tmp2.sumpay_2 AS "LTV2",tmp2.sumpay_3,tmp2.sumpay_4 AS "LTV4",tmp2.sumpay_5 AS "LTV5",tmp2.sumpay_6 AS "LTV6",tmp2.sumpay_7 AS "LTV7" FROM test_table tmp2 WHERE a>1 and b>4”.

[0064] This embodiment provides an SQL generation method. This embodiment first improves the flexibility of SQL generation by dynamically loading SQL templates, parses the templates and records the placeholders therein to lay the foundation for subsequent replacement operations, obtains replacement strings from data exchange formats, searches for and replaces corresponding SQL fragments according to the placeholders, ensures the accuracy and consistency of replacement, and combines all replaced SQL fragments into a complete SQL statement to ensure the integrity of the statement, thereby improving development efficiency and code maintainability.

[0065] Based on Example 1, Example 2 of the present application proposes a SQL generation method, referring to Figure 2 , before step S100, including: Step S600: define a template character string including the placeholder as the SQL template.

[0066] Before loading the SQL template, define a template string containing placeholders. At runtime, replace the placeholders in the template according to specific conditions or inputs to generate different SQL statements.

[0067] As an optional implementation, the type, structure and possible changes of the SQL query to be constructed are clearly defined, placeholders are designed in the template, the placeholders include simple text strings and more complex nested placeholders or strings with specific formats, an SQL template string containing the placeholders is written, and the written SQL template string is stored in a database.

[0068] For example, there is a user information table users, which contains the fields id, name, and email. Now you need to query user information based on the user ID. Define the SQL template in the source code file or configuration file: "-- SQL template string SELECT id, name, email FROM users WHERE id = {user_id};", where {user_id} is a placeholder that will be replaced with a specific user ID later.

[0069] This embodiment provides an SQL generation method. This embodiment first defines a template string containing placeholders as an SQL template, thereby improving the flexibility of SQL statements, promoting code reuse, reducing the need to repeatedly write the same SQL statements, and improving development efficiency and maintainability.

[0070] Based on Example 1, Example 3 of the present application proposes a SQL generation method, referring to Figure 3 , step S400, comprising: Step S410: parsing the structure of the SQL fragment and determining the type of the SQL fragment.

[0071] Determine the type of SQL fragment, the database optimizer better chooses the execution plan, especially for complex queries, distinguishing between static parameters and functions can affect query caching, index usage, and execution order.

[0072] Exemplarily, a lexical analyzer is used to decompose the SQL fragment into a series of keywords, identifiers, operators, numbers, strings and other tokens. By analyzing these tokens, the composition of the SQL fragment is preliminarily determined. A syntax analyzer is used to build an abstract syntax tree representing the SQL query structure according to the syntax rules of SQL. The syntax tree is traversed to check the type and attributes of each node. Based on the node type and context information, it is determined whether it represents a static parameter or a function call. Based on the traversal result, the various parts in the SQL fragment are classified as static parameters or functions.

[0073] Step S420: If the SQL fragment is a static parameter, the placeholder is directly replaced.

[0074] Step S430: If the SQL fragment is a call parameter, call the corresponding function to obtain the value of the SQL fragment and replace the placeholder.

[0075] Exemplary code for determining the type of SQL fragment and replacing it: Create an empty string parsedSQL to store the SQL statement after parsing and replacing the placeholder; Set a cursor cursor with an initial value of 0 to track the current parsed position; Use a loop to traverse the SQL template string until the cursor reaches the end of the string, and find the start position of the next placeholder in the template. If the placeholder is not found, add the remaining part to parsedSQL and exit the loop; Extract the name of the placeholder from the start position of the found placeholder; According to the name of the placeholder, combine placeholdersMap and functionsMap to determine the type of the placeholder; If the placeholder type is static, get the corresponding value from placeholdersMap and replace it in parsedSQL; If the placeholder type is a function, call the corresponding function in functionsMap, get the function return value, and replace it in parsedSQL; Update the cursor to the end of the current placeholder so as to continue parsing the subsequent SQL template; After the loop ends, return parsedSQL, that is, the SQL statement after parsing and replacing the placeholder.

[0076] As an exemplary implementation, the code example is as follows: "function replacePlaceholdersInSQLTemplate(template, placeholdersMap,functionsMap): / / Parse the SQL template and replace the placeholders parsedSQL = "" cursor = 0 / / Current parsed position while cursor <length(template): / / Find the start position of the next placeholder startIndex = findNextPlaceholderStart(template, cursor) if startIndex == -1: / / No placeholder found, add the rest to the parsed SQL parsedSQL += substring(template, cursor, length(template)) break / / Extract the placeholder name placeholderName = extractPlaceholderName(template, startIndex) / / Determine the type of the placeholder placeholderType = determinePlaceholderType(placeholderName,placeholdersMap, functionsMap) / / Replace the placeholder if placeholderType == "static": / / Direct replacement of static parameters value = placeholdersMap[placeholderName] parsedSQL += substring(template, cursor, startIndex) + value elif placeholderType == "function": / / Call function to get value value = functionsMap[placeholderName]() / / Assume that the function can be called directly and return the result parsedSQL += substring(template, cursor, startIndex) + value / / Update the cursor position cursor = startIndex + length(placeholderName) +anySurroundingDelimiters return parsedSQL " Optionally, step S430 includes: Step S431, calling the @foreach function including the SQL fragment template, the start value, the end value and the separator to process the repeated SQL fragments.

[0077] Step S432: loop through each value from the start value to the end value through the @foreach function, and replace the placeholder in the SQL fragment template with the value.

[0078] Step S433: Use the separator to connect each of the replaced SQL fragment templates to generate a complete SQL fragment.

[0079] In this embodiment, @foreach is a loop processing function, and the @foreach function is defined as: "@foreach(sqlFragment,start,end,delimiter)", where sqlFragment is a SQL fragment in string form, containing @id as a placeholder; start is a numeric variable used to specify the loop start value; end is a numeric variable used to specify the loop end value; delimiter is a delimiter used to connect the SQL fragments generated by each iteration.

[0080] As an example of an embodiment of the present application, the payment behavior and advertising revenue of users in a specific time period are analyzed, the total value (LTV) of the user life cycle from LTV1 to LTV14 of each day is calculated, and the number of new users is displayed. The SQL code written by the conventional method is parsed. First, the pay_info subquery is used to focus on the payment event, and the number of days from the user's first start time to the payment event date is calculated, and the total payment amount is summarized. At the same time, the data is filtered according to the event name ('pay_ok'), country code ('kr', 'ph') and other conditions. Similarly, the ad_info subquery processes advertising revenue events, while the register_info subquery focuses on registration events, especially the number of new users per day. Next, the all_info subquery merges the results of ad_info and pay_info through a left join to further calculate the total payment amount. The tmp1 subquery focuses on calculating the total number of new users in the specified time period. The tmp2 subquery is grouped by days, calculates the total payment amount per day, and uses the CASE statement to exclude days after the current date to ensure the consistency of the time range of the data. The final query combines the results of tmp1 and tmp2, and displays the number of new users and the corresponding LTV (lifetime value) for each date through LEFT JOIN (using __fid and days as the connection conditions). Here, the COALESCE function is used to handle possible NULL values ​​(such as when there is no payment income) to ensure that the result is 0; the to_char and to_timestamp functions are used to convert timestamp and date formats; and the CASE statement is used to dynamically calculate or filter data based on conditions.

[0081] As an exemplary implementation, an example of SQL code written by a conventional method is as follows: "-- Calculation: Users whose region is kr, ph and who have successfully paid between 2024-07-06 and 2024-07-13with pay_info as (SELECTevent.__fid,(event.__s_event_date::date - event.__first_start_time::date) +1as days,sum(event.item_id__price) sum_pay,min(event.__first_start_time::date) __first_start_timeFROMevent_table eventWHEREevent.__event_name IN('pay_ok')AND EVENT.__pt_date::date>= '2024-07-06'and EVENT.__pt_date::date<= '2024-07-13'AND EVENT.__reg in ('kr','ph')group by 1,2order by1,2),-- Calculation: Region is kr, ph From 2024-07-06 to 2024-07-13, users viewed advertising revenue ad_info as(SELECTevent.__fid,(EVENT.__s_event_date::date - EVENT.__first_start_time::date) +1as days,coalesce(sum(event.revenue), 0) sum_ad,min(EVENT.__first_start_time::date) __first_start_timeFROMevent_table eventWHEREevent.__event_name IN('ad_revenue')AND EVENT.__pt_date::date>= '2024-07-06'and EVENT.__pt_date::date<= '2024-07-13'AND EVENT.__reg in ('kr','ph')GROUP BY 1,2),-- The area is kr,ph From 2024-07-06 to 2024-07-13, new users are added every day register_info as (SELECTEVENT.__first_start_time::date,count(distinct EVENT.__fid) cnt1FROMevent_table eventWHEREevent.__event_name IS NOT NULLAND EVENT.__pt_date::date>= '2024-07-06'and EVENT.__pt_date::date<= '2024-07-13'AND EVENT.__reg in ('kr','ph')GROUP BY 1),-- The area is kr,ph from 2024-07-06 to 2024-07-13 Total revenue (in-app purchase + advertising) all_info as (SELECT a.__fid,a.days,a.__first_start_time,a.sum_ad+a.sum_pay::decimal as sum_payFROM(SELECT ad_info.__fid,ad_info.days,ad_info.sum_ad,(casewhen pay_info.sum_pay is null then 0 else pay_info.sum_pay end) as sum_pay,ad_info.__first_start_timeFROM ad_info LEFT JOIN pay_info using(__fid,days))as a )--Region is kr, ph. Total new users from 2024-07-06 to 2024-07-13,tmp1 as (SELECT'2024-07-06'::Date||'~'||'2024-07-13'::Date as T,sum(register_info.cnt1)cnt1FROM register_infoGROUP BY 1) -- Count the total value of user life cycle LTV1 to LTV14 in this time period, and convert rows to columns, which requires secondary calculation,tmp2 as (SELECT '2024-07-06'::Date||'~'||'2024-07-13'::Date as T,sum(sumpay_1)sumpay_1,sum(sumpay_2)sumpay_2,sum(sumpay_3)sumpay_3,sum(sumpay_4)sumpay_4,sum(sumpay_5)sumpay_5,sum(sumpay_6)sumpay_6,sum(sumpay_7)sumpay_7,sum(connector_8)connector_8,sum(connector_9)connector_9,sum(connector_10)connector_10,sum(connector_11)connector_11,sum(connector_12)connector_12,sum(connector_13)connector_13,sum(connector_13)connector_14,sum(connector_14)LECT_14 __first_start_time,sumpay_1,(case when __first_start_time::date + 1>= CURRENT_DATE then 0 else sumpay_2end )sumpay_2,(case when __first_start_time::date + 2>= CURRENT_DATE then 0else sumpay end,(3) __first_start_time::date + 3>=CURRENT_DATE then 0 else sumpay_4 end )sumpay_4,(case when __first_start_time::date + 4>= CURRENT_DATE then 0 else sumpay_5 end )sumpay_5,(case first_start_time +__:== CURRENT_DATE then 0 else sumpay_6 end )sumpay_6,(case when __first_start_time::date + 6>= CURRENT_DATE then 0 else sumpay_7end )sumpay_7,(case when __first_start_time::date + 7_>= DATE end )connect_8,(case when __first_start_time::date + 8>=CURRENT_DATE then 0 else connect_9 end )connect_9,( case when __first_start_time :: date + 9 >= CURRENT_DATE then 0 else sum_10 end ) sum_10 , ( case when __first_start_ time :: date + 10 >= CURRENT_DATE then 0 else sum_11 end ) sum_11 , ( case when __first_start_time::date + 11>= CURRENT_DATE then 0 else sum_12 end )sum_12,(case when __first_start_time::date + 12>= CURRENT_DATE then 0 else sum_13 end )sum_13,(case when __first_start_time::date+ 13>= CURRENT_DATE then 0 else sumpay_14 end )sumpay_14FROM all_infogroup by1 ) as b)-- Close the default setting to SELECT tmp1.t as "date",tmp1.cnt1 as "New Language", tmp2.sum_1 as "LTV1",tmp2.sum_2 as "LTV2",tmp2.sum_3 as "LTV3",tmp2.sum_4 as "LTV4", tmp2.sum_5 as "LTV5", tmp2.sum_6 as "LTV6", tmp2.sum_7 as "LTV7",tmp2.sum_8 as "LTV8",tmp2.sum_9 as "LTV9",tmp2.sum_10 as "LTV10",tmp2.sum_11 as "LTV11",tmp2.sum_12 as "LTV12",tmp2.sum_13 as "LTV13",tmp2.sum_14 as "LTV14"FROM tmp1 LEFT JOINtmp2 using (t) LIMIT 10000”。,

[0082] The complex SQL statements written by the above conventional methods contain repeated SQL fragments. If a certain part needs to be modified, it must be modified multiple times. The code is cumbersome to write and has poor readability. Using the method of the present application, first use the JSON format to define a SQL query template, use the "${with_tmp_table}" variable to include the WITH part of the entire query, and define all required temporary tables, such as pay_info, ad_info, register_info, all_info, tmp1, and tmp2. These temporary tables focus on payment events, advertising revenue events, and registration events, respectively, and calculate the total payment amount and the number of new users. The @foreach loop structure is used to dynamically generate the sumpay_@id column in the tmp2 subquery, from sumpay_1 to sumpay_14, which reduces code redundancy and improves clarity. The final SQL query is dynamically constructed by replacing the variable ${with_tmp_table}, and a JSON format organization containing multiple nested SQL queries and variable replacements is used. The example of the method in this application only requires a few lines of code to automatically generate SQL statements, check the call relationship, and process repeated SQL fragments through the @foreach function to avoid code redundancy.

[0083] As an exemplary implementation, the structured JSON code example written by the present application method is as follows: "{"sqlEntry": " ${with_tmp_table} SELECT tmp1.t as \"Time\",tmp1.cnt1 as \"New User Count\", @foreach('tmp2.sumpay_@id as \"LTV@id\"', 1, 14,',') FROM tmp1 LEFT JOIN tmp2 using (t) LIMIT 10000","${with_tmp_table}": "with pay_info as (${pay_info_table}), ad_info as (${ad_info_table}),register_info as (${register_info_table}), all_info as (${all_info_table}),tmp1 as (${tmp1_table}), tmp2 as (${tmp2_table})","${pay_info_table}": "SELECT event.__fid,( event.__s_event_date :: date - event.__first_start_time ::date ) + 1 as days, sum(event.item_id__price) sum_pay, min(event.__first_start_time :: date) __first_start_time FROM event_table event WHERE event.__event_name IN('pay_ok') AND ${event_user_filter} group by 1, 2 order by 1,2","${ad_info_table}": "SELECT event.__fid,(EVENT.__s_event_date::date -EVENT.__first_start_time::date) +1 as days,coalesce(sum(event.revenue), 0)sum_ad,min(EVENT.__first_start_time::date) __first_start_time FROM event_table event WHERE event.__event_name IN('ad_revenue') AND ${event_user_filter} GROUP BY 1,2","${register_info_table}": "SELECT EVENT.__first_start_time::date,count(distinct EVENT.__fid) cnt1 FROM event_table event WHEREevent.__event_name IS NOT NULL AND ${event_user_filter} GROUP BY 1","${all_info_table}": "SELECT a.__fid,a.days,a.__first_start_time,a.sum_ad+a.sum_pay::decimal as sum_pay FROM ( SELECT ad_info.__fid,ad_info.days,ad_info.sum_ad,(case when pay_info.sum_pay is null then 0 else pay_info.sum_pay end) assum_pay,ad_info.__first_start_time FROM ad_info LEFT JOIN pay_info using(__fid,days) ) as a","${tmp1_table}": "SELECT '2024-07-06'::Date||'~'||'2024-07-13'::Date as T,sum(register_info.cnt1)cnt1 FROM register_info GROUP BY 1","${tmp2_table}": "SELECT '2024-07-06'::Date||'~'||'2024-07-13'::Date as T ,@foreach('sum(sumpay_@id)sumpay_@id',1,14,',') FROM ( SELECT __first_start_time ,sumpay_1 ,@foreach('(case when __first_start_time::date + @id>=CURRENT_DATE then 0 else sumpay_@id+@1 end )sumpay_@id+@1',1,13,',') FROMall_info group by 1 ) as b","${event_user_filter}": "EVENT.__pt_date::date>='2024-07-06' and EVENT.__pt_date::date<= '2024-07-13' AND EVENT.__reg in ('kr','ph')"}" Among them, sqlEntry is the starting point of SQL generation and the final content of SQL. Use ${with_tmp_table} to reference other SQL fragments; use @foreach to batch generate SQL fragments and simplify SQL fragments; ${with_tmp_table} is a collection of reference tables, and use ${pay_info_table}, ${ad_info_table}, ${register_info_table}, ${all_info_table}, ${tmp1_table}, and ${tmp2_table} to reference other SQL Fragment; ${pay_info_table} is used to query users who have successfully paid, and extract the common conditions of ${event_user_filter}; ${ad_info_table} is used to query users who watch ads, and extract the common conditions of ${event_user_filter}; ${register_info_table} is used to query daily new users, and extract the common conditions of ${event_user_filter}; ${all_info_table} is used to query the total income of users, and extract the common conditions of ${event_user_filter}; ${tmp1_table} is used to total new users; ${tmp2_table} is used to calculate the total value of user life cycle LTV1 to LTV14, and use @foreach to batch generate LTV1 to LTV14 sql fragments; ${event_user_filter} is used to extract the common conditions, which are used for other sql fragments to reduce redundant code. .

[0084] This embodiment provides an SQL generation method. This embodiment first parses the structure of the SQL fragment and determines its type, so that the composition and construction method of the SQL statement can be more clearly understood, which is convenient for maintaining the SQL statement. By distinguishing between static parameters and call parameters, static parameters are directly replaced, while call parameters dynamically obtain values ​​according to runtime conditions, so that the SQL statement can adapt to different query scenarios and data requirements, improve the readability and maintainability of the code, promote code reuse, and reduce the need to repeatedly write the same SQL fragment.

[0085] Based on Example 1, Example 4 of the present application proposes a SQL generation method, referring to Figure 4 , after step S500, including: Step S700, testing the complete SQL statement, and modifying the SQL generation method according to the test result to meet the requirements for running the SQL statement.

[0086] Test the complete SQL statement to ensure that the SQL statement can correctly return the expected results when it is actually executed and will not adversely affect the database performance.

[0087] Exemplarily, a test database isolated from a production environment is used for testing, and a complete SQL statement is executed in the test environment. The execution results are observed to check whether the results returned by the SQL statement meet expectations, check the type of data, quantity, order, and content returned, etc., evaluate the execution performance of the SQL statement, such as execution time and resource consumption, record errors and / or exceptions that occur during the execution of the SQL statement, analyze the cause of the error, modify the template or fragment used to generate the SQL statement, and optimize the logical processing part in the SQL generation method, re-run the SQL statement, and check whether the correctness and performance are improved. After multiple iterative tests, the final SQL statement and the template or fragment used to generate the SQL statement are obtained.

[0088] This embodiment provides an SQL generation method. This embodiment first tests the SQL statement and modifies the SQL generation method according to the test result, thereby improving data accuracy and system performance, enhancing system stability, and improving development efficiency.

[0089] It should be noted that the above examples are only used to understand the present application and do not constitute a limitation on the SQL generation method of the present application. More simple transformations based on this technical concept are all within the protection scope of the present application.

[0090] The present application provides an SQL generation device, which includes: at least one processor; and a memory that is communicatively connected to the at least one processor; wherein the memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the SQL generation method in the above-mentioned embodiment 1.

[0091] Reference below Figure 5 , which shows a schematic diagram of the structure of an SQL generation device suitable for implementing the embodiment of the present application. The SQL generation device in the embodiment of the present application may include but is not limited to mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Descriptions), PMPs (Portable Media Players), vehicle-mounted terminals (such as vehicle-mounted navigation terminals), etc., and fixed terminals such as digital TVs, desktop computers, etc. Figure 5 The SQL generation device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0092] like Figure 5 As shown, the SQL generating device may include a processing device 1001 (e.g., a central processing unit, a graphics processor, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM: Read Only Memory) 1002 or a program loaded from a storage device 1003 to a random access memory (RAM: Random Access Memory) 1004. In RAM1004, various programs and data required for the operation of the SQL generating device are also stored. The processing device 1001, ROM1002, and RAM1004 are connected to each other through a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Generally, the following systems can be connected to the I / O interface 1006: an input device 1007 including, for example, a touch screen, a touch pad, a keyboard, a mouse, an image sensor, a microphone, an accelerometer, a gyroscope, etc.; an output device 1008 including, for example, a liquid crystal display (LCD: Liquid Crystal Display), a speaker, a vibrator, etc.; a storage device 1003 including, for example, a magnetic tape, a hard disk, etc.; and a communication device 1009. The communication device 1009 can allow the SQL generation device to communicate with other devices wirelessly or by wire to exchange data. Although the figure shows the SQL generation device with various systems, it should be understood that it is not required to implement or have all the systems shown. More or fewer systems can be implemented or have alternatively.

[0093] In particular, according to the embodiments disclosed in the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present application include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication device, or installed from a storage device 1003, or installed from a ROM 1002. When the computer program is executed by the processing device 1001, the above-mentioned functions defined in the method of the embodiment disclosed in the present application are executed.

[0094] The SQL generation device provided by the present application adopts the SQL generation method in the above embodiment, which can solve the technical problem of how to reduce the code redundancy of SQL generation. Compared with the prior art, the beneficial effects of the SQL generation device provided by the present application are the same as the beneficial effects of the SQL generation method provided by the above embodiment, and other technical features in the SQL generation device are the same as the features disclosed in the method of the previous embodiment, which will not be repeated here.

[0095] It should be understood that the various parts disclosed in this application can be implemented by hardware, software, firmware or a combination thereof. In the description of the above embodiments, specific features, structures, materials or characteristics can be combined in any one or more embodiments or examples in a suitable manner.

[0096] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

[0097] The present application provides a computer-readable storage medium having computer-readable program instructions (ie, computer programs) stored thereon, and the computer-readable program instructions are used to execute the SQL generation method in the above embodiment.

[0098] The computer-readable storage medium provided in the present application may be, for example, a USB flash drive, but is not limited to electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, systems or devices, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM: Random Access Memory), a read-only memory (ROM: Read Only Memory), an erasable programmable read-only memory (EPROM: Erasable Programmable Read Only Memory or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM: CD-Read Only Memory), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, system or device. The program code contained on the computer-readable storage medium may be transmitted using any appropriate medium, including but not limited to: wires, optical cables, RF (Radio Frequency: Radio Frequency), etc., or any suitable combination of the above.

[0099] The computer-readable storage medium may be included in the SQL generating device; or may exist independently without being assembled into the SQL generating device.

[0100] The computer-readable storage medium carries one or more programs. When the one or more programs are executed by the SQL generating device, the SQL generating device can write computer program codes for performing the operations of the present application in one or more programming languages ​​or a combination thereof. The programming languages ​​include object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" language or similar programming languages. The program code can be executed completely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or completely on a remote computer or server. In the case of a remote computer, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computer (for example, through the Internet using an Internet service provider).

[0101] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present application. In this regard, each square box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each square box in the block diagram and / or flow chart, and the combination of the square boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0102] The modules involved in the embodiments described in this application may be implemented by software or hardware, wherein the name of the module does not constitute a limitation on the unit itself in some cases.

[0103] The readable storage medium provided in the present application is a computer-readable storage medium, which stores computer-readable program instructions (i.e., computer programs) for executing the above-mentioned SQL generation method, and can solve the technical problem of how to reduce the code redundancy of SQL generation. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in the present application are the same as the beneficial effects of the SQL generation method provided in the above-mentioned embodiment, and will not be repeated here.

[0104] The above descriptions are only some embodiments of the present application, and are not intended to limit the patent scope of the present application. All equivalent structural changes made using the contents of the present application specification and drawings under the technical concept of the present application, or direct / indirect applications in other related technical fields are included in the patent protection scope of the present application.< / columns>

Claims

1. A SQL generation method, characterized in that: The method includes: Load the SQL template according to the starting point of SQL generation; Parsing the SQL template and recording the placeholders in the SQL template; Obtain a replacement string in a data exchange format, and search for a corresponding SQL fragment in the replacement string according to the placeholder; Replacing the placeholder in the SQL template based on the SQL fragment in the character string; The replaced SQL fragments are merged to determine a complete SQL statement.

2. The method according to claim 1, characterized in that Before the step of loading the SQL template according to the starting point position generated by SQL, the method includes: A template string including the placeholder is defined as the SQL template.

3. The method according to claim 1, characterized in that The step of parsing the SQL template and recording the placeholders in the SQL template comprises: Traversing the SQL template character by character; According to the format of the placeholder, match the character string that matches the start delimiter and the end delimiter of the placeholder; The location of the placeholder is determined and recorded.

4. The method according to claim 1, characterized in that The step of obtaining a replacement string in a data exchange format and searching for a corresponding SQL fragment in the replacement string according to the placeholder comprises: Acquire the replacement string written by the user, wherein the replacement string includes a preset placeholder and a key-value pair of the corresponding SQL fragment; According to the recorded placeholder, the corresponding SQL fragment is searched in the replacement string.

5. The method according to claim 1, characterized in that The step of replacing the placeholder in the SQL template based on the SQL fragment in the character string comprises: Parsing the structure of the SQL fragment to determine the type of the SQL fragment; If the SQL fragment is a static parameter, the placeholder is directly replaced; If the SQL fragment is a call parameter, the corresponding function is called to obtain the value of the SQL fragment and replace the placeholder.

6. The method according to claim 5, characterized in that If the SQL fragment is a call parameter, calling a corresponding function to obtain the value of the SQL fragment and replacing the placeholder comprises: Calling the @foreach function including the SQL fragment template, the start value, the end value and the separator to process the repeated SQL fragment; The @foreach function loops through each value from the start value to the end value, and replaces the placeholder in the SQL fragment template with the value; Each of the replaced SQL fragment templates is connected using the separator to generate a complete SQL fragment.

7. The method according to claim 1, characterized in that The step of merging the replaced SQL fragments to determine a complete SQL statement comprises: Performing syntax verification on the replaced SQL fragment; The SQL fragments that have passed the verification are merged using a preset merging delimiter to determine a complete SQL statement.

8. The method according to claim 1, characterized in that After the step of merging the replaced SQL fragments to determine a complete SQL statement, the method further includes: The complete SQL statement is tested, and the SQL generation method is modified according to the test result to meet the requirements for running the SQL statement.

9. A SQL generation device, characterized in that: The device comprises: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the SQL generation method according to any one of claims 1 to 8.

10. A storage medium, characterized in that: The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, the steps of the SQL generation method according to any one of claims 1 to 8 are implemented.

Images