SQL statement generation method, device, equipment and storage medium

Abstract

The present invention relates to the field of financial technology, and discloses a method, device, equipment and storage medium for generating SQL statements, wherein the method for generating SQL statements includes: obtaining each feature binning rule, and analyzing and processing each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule; obtaining the target grammar matching rule corresponding to each feature binning rule according to each feature type; and generating SQL statements corresponding to each feature binning rule based on each feature binning rule and the target grammar matching rule corresponding to each feature binning rule. The present invention realizes a system for automatically converting a feature binning model into an SQL statement, reduces the labor cost and time cost of outputting the model to run in a production environment, and improves the efficiency of generating SQL statements.

Description

Technical Field

[0001] The present invention relates to the field of financial technology, and in particular to a method, device, equipment and storage medium for generating an SQL statement. Background Art

[0002] With the development of computer technology, more and more technologies (big data, distributed, blockchain, artificial intelligence, etc.) are applied in the financial field. The traditional financial industry is gradually transforming to financial technology (Fintech). However, due to the security and real-time requirements of the financial industry, higher requirements are also placed on technology.

[0003] Feature binning is a feature processing method that divides interval feature values ​​into multiple discrete values ​​or groups discrete feature values, which can make feature values ​​more stable and improve the robustness of the model. Feature binning algorithms are widely used in modeling of various scenarios. After modelers establish binning models, they need to write SQL codes according to binning rules and hand them over to database administrators to deploy to the production environment for operation.

[0004] However, the current manual SQL code writing method is inefficient and has a high error rate. Especially when users need to frequently make interactive bin adjustments, the bin rules will be iteratively updated at a high frequency, resulting in excessive pressure on manual SQL. At the same time, feature binning generally involves a large number of feature processing, with each feature bin number ranging from dozens to hundreds, making the number of feature bin rules very large, resulting in excessive cost of manually writing SQL statements. Summary of the invention

[0005] The main purpose of the present invention is to provide a SQL statement generation method, device, equipment and storage medium, aiming to realize the automatic conversion of feature binning models into SQL statements, reduce the manpower cost and time cost of outputting models to run in a production environment, and improve the efficiency of SQL statement generation.

[0006] To achieve the above object, an embodiment of the present invention provides a method for generating a SQL statement, the method comprising:

[0007] Obtain each feature binning rule, and analyze and process each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule;

[0008] Obtaining target grammar matching rules corresponding to each feature binning rule according to each feature type;

[0009] Based on each of the feature binning rules and the target grammar matching rule corresponding to each of the feature binning rules, an SQL statement corresponding to each of the feature binning rules is generated.

[0010] Optionally, the analyzing and processing each feature binning rule according to a preset binning rule library to obtain a feature type of each feature binning rule includes:

[0011] Analyze and process each feature binning rule according to a preset binning rule library to obtain the feature attributes of each feature binning rule;

[0012] If the feature attribute is a limited partition attribute, the feature type of the feature binning rule is a column name type;

[0013] If the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is an interval type.

[0014] Optionally, after analyzing and processing each feature binning rule according to a preset binning rule library to obtain feature attributes of each feature binning rule, the method further includes:

[0015] If the feature attribute is a missing value attribute, the feature binning rule is discarded and not processed.

[0016] Optionally, the feature type is one of a column name type and an interval type, and the step of obtaining a target grammar matching rule corresponding to each feature binning rule according to each feature type includes:

[0017] If the feature type is column name type, the target syntax matching rule for obtaining the corresponding feature binning rule is the column name type syntax matching rule;

[0018] If the feature type is interval type, the target grammar matching rule for obtaining the corresponding feature binning rule is the interval type grammar matching rule.

[0019] Optionally, after generating the SQL statements corresponding to the feature binning rules based on the feature binning rules and the target grammar matching rules corresponding to the feature binning rules, the method further includes:

[0020] Get the generation order of each SQL statement;

[0021] All SQL statements are concatenated according to the generation order to generate a complete SQL statement.

[0022] Optionally, the feature type of each feature binning rule is obtained, including:

[0023] If the feature type of the target feature binning rule does not exist in the analysis and processing result, the actual business scenario of the target feature binning rule is obtained;

[0024] The feature type of the target feature binning rule is determined according to the actual business scenario, and the feature types of other feature binning rules except the target feature binning rule are extracted from the analysis result.

[0025] Optionally, the generating, based on each of the feature binning rules and the target grammar matching rule corresponding to each of the feature binning rules, a SQL statement corresponding to each of the feature binning rules includes:

[0026] Obtain statistical information and binning group numbers in each feature binning rule, wherein the statistical information includes non-event values ​​and event values;

[0027] The woe value of each feature binning rule is generated according to the non-event value and the event value respectively, and the SQL statement corresponding to each feature binning rule is generated according to the binning group number, the woe value and the corresponding target syntax matching rule of each feature binning rule.

[0028] The present invention also provides a SQL statement generating device, the SQL statement generating device comprising:

[0029] An analysis module is used to obtain each feature binning rule and analyze and process each feature binning rule according to a preset binning rule library to obtain a feature type of each feature binning rule;

[0030] An acquisition module is used to acquire target grammar matching rules corresponding to each feature binning rule according to each feature type;

[0031] A generation module is used to generate SQL statements corresponding to each feature binning rule based on each feature binning rule and a target grammar matching rule corresponding to each feature binning rule.

[0032] Optionally, the analysis module includes:

[0033] An analysis unit, used to analyze and process each feature binning rule according to a preset binning rule library to obtain feature attributes of each feature binning rule;

[0034] A first judgment unit, configured to determine that if the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is an interval type;

[0035] The second judgment unit is used to determine that if the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is an interval type.

[0036] Optionally, the analysis module further includes:

[0037] The missing value unit is used to discard the feature binning rule and not process it if the feature attribute is a missing value attribute.

[0038] Optionally, the feature type is one of a column name type and an interval type, and the acquisition module includes:

[0039] A first acquisition unit is used for acquiring a target syntax matching rule of a corresponding feature binning rule as a column name type syntax matching rule if the feature type is a column name type;

[0040] The second acquisition unit is used to acquire the target grammar matching rule corresponding to the feature binning rule as the interval-type grammar matching rule if the feature type is the interval-type.

[0041] Optionally, the SQL statement generating device further includes:

[0042] Sequential module, used to obtain the generation order of each SQL statement;

[0043] The splicing module is used to splice all SQL statements according to the generation order to generate a complete SQL statement.

[0044] Optionally, the analysis module includes:

[0045] A business unit, used for obtaining an actual business scenario of the target feature binning rule if the feature type of the target feature binning rule does not exist in the analysis and processing result;

[0046] A determination unit is used to determine the feature type of the target feature binning rule according to the actual business scenario, and to extract the feature types of other feature binning rules except the target feature binning rule from the analysis result.

[0047] Optionally, the generating module includes:

[0048] A statistical unit, used to obtain statistical information and binning group numbers in each feature binning rule, wherein the statistical information includes non-event values ​​and event values;

[0049] A generating unit is used to generate a woe value for each feature binning rule according to the non-event value and the event value, respectively, and to generate a SQL statement corresponding to each feature binning rule according to the binning group number, the woe value and the corresponding target syntax matching rule of each feature binning rule.

[0050] In addition, to achieve the above-mentioned purpose, the present invention further provides a device, comprising: a memory, a processor, and a SQL statement generation program stored in the memory and executable on the processor, wherein:

[0051] When the SQL statement generation program is executed by the processor, the steps of the SQL statement generation method described above are implemented.

[0052] In addition, to achieve the above-mentioned purpose, the present invention also provides a computer storage medium;

[0053] The computer storage medium stores a SQL statement generation program, and when the SQL statement generation program is executed by the processor, the steps of the above-mentioned SQL statement generation method are implemented.

[0054] The present invention obtains each feature binning rule, and analyzes and processes each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule; obtains the target syntax matching rule corresponding to each feature binning rule according to each feature type; and generates the SQL statement corresponding to each feature binning rule based on each feature binning rule and the target syntax matching rule corresponding to each feature binning rule. When processing the rule of each feature, the present invention first determines the feature type, whether it is an interval type or a column name type, and then generates the corresponding SQL statement according to the corresponding rule analysis and syntax matching, thereby realizing the automatic conversion of feature binning rules into SQL statements. When processing a large number of features and a large number of bins, and when the binning rules are frequently updated, the labor cost and time cost of outputting the model to the generation environment are reduced, and the automatic process of generating corresponding SQL statements from feature rules is realized, thereby improving the generation efficiency of SQL statements and greatly improving the modeling efficiency of modelers. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] Figure 1 A schematic diagram of the device structure of the hardware operating environment involved in the embodiment of the present invention;

[0056] Figure 2 This is a flow chart of an embodiment of a method for generating SQL statements according to the present invention;

[0057] Figure 3 A schematic diagram of feature binning rules for column name feature types in the SQL statement generation method of the present invention;

[0058] Figure 4 A schematic diagram of feature binning rules for interval-type features in the SQL statement generation method of the present invention;

[0059] Figure 5 A schematic diagram of the SQL statement generation method of the present invention performing splicing processing on SQL statements;

[0060] Figure 6 for Figure 3 The diagram of the SQL statement generated by the column name feature binning rule is shown;

[0061] Figure 7 for Figure 4 The diagram shows the SQL statement generated by the interval feature binning rule.

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

[0063] It should be understood that the specific embodiments described herein are only used to explain the present invention, and are not used to limit the present invention.

[0064] like Figure 1 As shown, Figure 1 It is a schematic diagram of the device structure of the hardware operating environment involved in the embodiment of the present invention.

[0065] The device in the embodiment of the present invention may be a PC or a server device.

[0066] like Figure 1 As shown, the device may include: a processor 1001, such as a CPU, a network interface 1004, a user interface 1003, a memory 1005, and a communication bus 1002. Among them, the communication bus 1002 is used to realize the connection and communication between these components. The user interface 1003 may include a display screen (Display), an input unit such as a keyboard (Keyboard), and the optional user interface 1003 may also include a standard wired interface and a wireless interface. The network interface 1004 may optionally include a standard wired interface and a wireless interface (such as a WI-FI interface). The memory 1005 may be a high-speed RAM memory, or a stable memory (non-volatile memory), such as a disk memory. The memory 1005 may also be a storage device independent of the aforementioned processor 1001.

[0067] Those skilled in the art will understand that Figure 1 The device structure shown in the figure does not constitute a limitation of the device, and may include more or less components than shown in the figure, or combine certain components, or arrange the components differently.

[0068] like Figure 1 As shown, the memory 1005 as a computer storage medium may include an operating system, a network communication module, a user interface module, and an SQL statement generation program.

[0069] exist Figure 1 In the device shown, the network interface 1004 is mainly used to connect to the background server and communicate data with the background server; the user interface 1003 is mainly used to connect to the client (user end) and communicate data with the client; and the processor 1001 can be used to call the SQL statement generation program stored in the memory 1005, and execute the operations in each embodiment of the following SQL statement generation method.

[0070] The main idea of ​​the embodiment of the present invention is: the present invention obtains each feature binning rule, and analyzes and processes each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule; obtains the target syntax matching rule corresponding to each feature binning rule according to each feature type; and generates the SQL statement corresponding to each feature binning rule based on each feature binning rule and the target syntax matching rule corresponding to each feature binning rule. When processing the rule of each feature, the present invention first determines the feature type, whether it is an interval type or a column name type, and then generates the corresponding SQL statement according to the corresponding rule analysis and syntax matching, thereby realizing the automatic conversion of feature binning rules into SQL statements. When processing a large number of features and a large number of bins, and when the binning rules are frequently updated, the labor cost and time cost of outputting the model to the generation environment are reduced, and the automatic process of generating corresponding SQL statements from feature rules is realized, thereby improving the generation efficiency of SQL statements and greatly improving the modeling efficiency of modelers.

[0071] The embodiments of the present invention take into account that in the prior art, the manual SQL code writing method is inefficient and has a high error rate. Especially when users need to frequently make interactive bin adjustments, the bin rules will be iteratively updated at a high frequency, resulting in excessive pressure on manual SQL. At the same time, feature binning generally involves a large number of feature processing, and the feature types are divided into interval-type and column-name-type features, resulting in different forms of bin rules. The number of feature bins for each feature ranges from dozens to hundreds, making the number of feature bin rules very large, resulting in excessive cost of manually writing SQL statements.

[0072] The present invention provides a solution. When processing the rule of each feature, the feature type is first determined, whether it is an interval type or a column name type. Then, the corresponding SQL statement is generated according to the corresponding rule analysis and syntax matching, thereby realizing automatic conversion of feature binning rules into SQL statements. When processing a large number of features and a large number of bins, and the binning rules are frequently updated, the manpower cost and time cost of outputting the model to the generation environment for operation are reduced, and an automated process of generating corresponding SQL statements from feature rules is realized, thereby improving the generation efficiency of SQL statements and greatly improving the modeling efficiency of modelers.

[0073] Based on the above hardware structure, an embodiment of the SQL statement generation method of the present invention is proposed.

[0074] The present invention belongs to the field of financial technology (Fintech), and provides a method for generating SQL statements.

[0075] In an embodiment of a method for generating a SQL statement, refer to Figure 2 , the SQL statement generation method includes:

[0076] Step S10, obtaining each feature binning rule, and analyzing and processing each feature binning rule according to a preset binning rule library to obtain a feature type of each feature binning rule;

[0077] Step S20, obtaining target grammar matching rules corresponding to each feature binning rule according to each feature type;

[0078] Step S30: Based on each of the feature binning rules and the target grammar matching rule corresponding to each of the feature binning rules, generate an SQL statement corresponding to each of the feature binning rules.

[0079] The SQL statement generation method can be applied to the device, and the specific contents are as follows:

[0080] Step S10, obtaining each feature binning rule, and analyzing and processing each feature binning rule according to a preset binning rule library to obtain a feature type of each feature binning rule;

[0081] Feature binning algorithms are widely used in modeling various scenarios, and modelers establish feature binning models. The feature binning model includes a large number of feature binning rules. The system device of the present invention obtains all feature binning rules from the feature binning model and determines the corresponding feature type according to the preset binning rule library. Since each feature binning rule will be converted into a corresponding SQL statement, and the SQL statement has a specific formatting limitation, it is necessary to classify the feature binning rules according to the generation rules of the SQL statement to determine the feature type, and realize the automatic recognition of the feature binning rules by the system device through the feature type, so as to automatically convert the statements in a specific format according to the feature type.

[0082] Specifically, the analysis and processing of each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule includes:

[0083] Step A1, analyzing and processing each feature binning rule according to a preset binning rule library to obtain feature attributes of each feature binning rule;

[0084] Step A2: if the feature attribute is a limited partition attribute, the feature type of the feature binning rule is a column name type;

[0085] Step A3: If the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is interval type.

[0086] In this embodiment, a binning rule base is preset, and the binning rule base is used to analyze and process the feature binning rules to obtain feature attributes. The feature attributes are attribute divisions of feature values. Figure 3 ,Depend on Figure 3It can be seen that the preset binning rule library extracts the feature binning rules, analyzes and determines the type of the feature value of the feature binning rules. The feature value is divided into two types, one is a numerical value, and the other is a value range. The process of the preset binning rule library analysis is: extracting the feature value and determining the type of the feature value. If the feature values ​​corresponding to the feature serial numbers in the feature binning rules are all numerical values, that is, a limited value is specified, it belongs to a limited partitioning attribute. In this embodiment, the limited partitioning attribute refers to the feature value having only one or a group of fixed values, which is a limited numerical attribute and is not extensible. Refer to Figure 4 ,Depend on Figure 4 It can be seen that if the feature values ​​corresponding to the feature numbers in the feature binning rules are all conditional value ranges, that is, the value range is specified, it belongs to the range partitioning attribute. In this embodiment, the range partitioning attribute means that the feature value can be any value within the value range, and belongs to any value within the limited range.

[0087] It can be seen that the difference between limited partition attributes and range partition attributes is that the feature value of the feature binning rule of limited partition attributes is a fixed value, which can be a numeric value or a fixed name, such as a numeric value (1, 2, 3, 4, 8, etc.), a country name (China, the United States, Japan, etc.), a gender (male, female), etc. The feature value of the feature binning rule of the range partition attribute is a floating value within the value range, and fixed values ​​and floating values ​​correspond to specific SQL statements. Correspondingly, if the feature attribute is a limited partition attribute, the feature type of its feature binning rule is a column name type, and if the feature attribute is a range partition attribute, the feature type of its feature binning rule is an interval type.

[0088] Reference Figure 3 and Figure 4 , the feature types of feature binning are divided into column name type and interval type. Column name type features are grouped by feature values, while interval type feature binning rules are divided by value range. The SQL statements generated by the two are different. The condition in the SQL statement generated by interval type features is the value range, while the condition in column name type features is whether the value is in the list.

[0089] Furthermore, after analyzing and processing each feature binning rule according to a preset binning rule library to obtain feature attributes of each feature binning rule, the method further includes:

[0090] Step A4: if the feature attribute is a missing value attribute, the feature binning rule is discarded and not processed.

[0091] It is understandable that feature attributes may have missing values. Figure 3 Feature number 10 and Figure 4In feature number 1, the feature value in the feature binning rule is a missing value, that is, the feature value is a null value, and its corresponding feature attribute contains a missing value attribute, which is neither a column name type nor an interval type, and cannot be classified into the above types. In order to avoid this situation causing errors when generating SQL statements, this embodiment can perform missing value processing on the feature binning rule when determining that the feature attribute is a missing value attribute. Preferably, the missing value processing of this embodiment is to discard the feature binning rule. In an optional embodiment, the feature value filling of the missing value attribute can be achieved by filling with preset values. It should be noted that the preset value filling scheme is suitable for situations where the data volume is large. In situations where the data volume is small, the filling of preset values ​​is likely to cause certain data interference to the data itself, thereby reducing data reliability.

[0092] For example, when the judgment condition is divided by missing values, OR IS NULL can be used to handle missing values:

[0093] CASE WEIGHT<=20OR WEIGHT IS NULL THEN

[0094] Case Weight<=20or weight is null then

[0095] Specifically, the feature types for obtaining each feature binning rule include:

[0096] Step A5: if the feature type of the target feature binning rule does not exist in the analysis and processing result, then obtain the actual business scenario of the target feature binning rule;

[0097] Step A6, determining the feature type of the target feature binning rule according to the actual business scenario, and extracting the feature types of other feature binning rules except the target feature binning rule from the analysis result.

[0098] In this embodiment, there is a situation that the feature type corresponding to some feature binning rules can be used as either an interval type or a column name type. For example, age can be used as a column name type or as an interval type feature, depending on the specific application scenario. In other words, the feature type of the target feature binning rule does not exist in the analysis and processing results. The target feature binning rule can be any feature type, and the feature type is related to the format of the generated SQL statement. At this time, the feature type needs to be determined by the actual business scenario. For example, in the target feature binning rule, the feature type of age is not determined, and the actual business scenario is for the loan business results of the 20-40 age range. Faced with massive data, the 20-40 age range is a huge database, so the corresponding age is further divided into the age range, rather than modeling the loan business results corresponding to each age value. Therefore, according to the actual business scenario, the feature type of the age is an interval type.

[0099] If the actual business scenario is the installment business results for students aged 15-18, since the students aged 15-18 control the location and age range, the installment business trends of specific objects in the area at different ages can be refined, and the data scale is controllable, so it corresponds to the division of the age range one by one. Therefore, according to the actual business scenario, the feature type of this age is a column type.

[0100] For the interval type, the binning rules are expressed by splitting points. For example, [50,70] can divide the weight into three intervals: 0-50, 50-70, and above 70.

[0101] For column-name months, the intervals are divided into different groups by using [[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]].

[0102] Step S20, obtaining target grammar matching rules corresponding to each feature binning rule according to each feature type;

[0103] After the feature type is obtained, the corresponding target syntax matching rule can be obtained according to the feature type. In this embodiment, the feature type represents the SQL statement rule of the feature binning rule. Different feature types have different rules, so the rules for generating SQL statements need to be pre-set. The target syntax matching rule generates matching rules according to the SQL statements corresponding to different feature types.

[0104] Specifically, the feature type is one of a column name type and an interval type, and the step of obtaining a target grammar matching rule corresponding to each feature binning rule according to each feature type includes:

[0105] Step B1, if the feature type is a column name type, the target syntax matching rule of the corresponding feature binning rule is obtained as a column name type syntax matching rule;

[0106] Step B2: If the feature type is interval type, the target grammar matching rule corresponding to the feature binning rule is obtained as the interval type grammar matching rule.

[0107] In this embodiment, there is only one feature type for any feature binning rule, which is one of column name type and interval type. For different feature types, that is, for feature binning rules of different feature types, syntax matching is specially established. SQL syntax matching will perform targeted syntax matching and parameter settings based on column name type feature binning rules and interval type feature binning rules to generate valid SQL statements. Therefore, when the feature type is column name type, the system device will obtain the column name type syntax matching rule; when the feature type is interval type, the system device will obtain the interval type syntax matching rule.

[0108] Step S30: Based on each of the feature binning rules and the target grammar matching rule corresponding to each of the feature binning rules, generate an SQL statement corresponding to each of the feature binning rules.

[0109] After obtaining the target grammar matching rules corresponding to all feature binning rules, process each feature binning rule and its corresponding target grammar matching rule to obtain the SQL statement corresponding to each feature binning rule. For example, if there are feature binning rules a, b, and c, which correspond to target grammar matching rules A, A, and B respectively, when processing a, call the corresponding A, apply A to a to obtain SQL statement 1; execute the next round of processing, when processing b, call the corresponding A, apply A to b to obtain SQL statement 2; execute the next round of processing, when processing c, call the corresponding B, apply B to c to obtain SQL statement 3; the processing ends.

[0110] In other words, different feature binning rules map to corresponding target grammar matching rules. For easier understanding, the following example explains:

[0111] 1. Reference Figure 6 , Figure 6 for Figure 3 The following is a schematic diagram of the SQL statement generated by the column name feature binning rule. For the column name type, "WHEN", variable name, "IN", and value generation rules are used. For example, the above month division can be translated into:

[0112] CASE WHEN MONTH IN(1,2,3,4)THEN

[0113] WHEN MONTH IN(5,6,7,8) THEN

[0114] ELSE

[0115] END

[0116] 2. Reference Figure 7 , Figure 7 for Figure 4 The schematic diagram of SQL statements generated by interval-type feature binning rules is shown in the figure. For interval-type, "WHEN", variable name, "<=", ">", and threshold are used to combine and generate rules. For example, if the weight passes the threshold [50,70], it can be converted into:

[0117] CASE WHEN WEIGHT<50THEN

[0118] WHEN WEIGHT>=50AND WEIGHT<70THEN

[0119] ELSE

[0120] END

[0121] Furthermore, the generating of SQL statements corresponding to each feature binning rule based on each feature binning rule and the target grammar matching rule corresponding to each feature binning rule includes:

[0122] Step C1, obtaining statistical information and binning group numbers in each feature binning rule, wherein the statistical information includes non-event values ​​and event values;

[0123] Step C2, generating the woe value of each feature binning rule according to the non-event value and the event value, and generating the SQL statement corresponding to each feature binning rule according to the binning group number, the woe value and the corresponding target syntax matching rule of each feature binning rule.

[0124] The feature binning rules store statistical information and bin group numbers, which represent the feature data and bin number of the current feature binning. Therefore, the statistical rules and bin group numbers can be used as identification data of the current feature binning. In this embodiment, the statistical information includes non-event values ​​and event values, and the woe value can be obtained by calculating the non-event value and the event value.

[0125] Specifically, refer to the following algorithm:

[0126] Features: X;

[0127] Number of bins: n, which indicates the number of segments into which feature X is divided after sorting;

[0128] Box: X i, 1<=i<=n, represents a segment of data of feature X after sorting;

[0129] Binning point: S, containing n-1 different values, S in turn i , 1<=i<=n-1, and S i i+1 ;

[0130] Number of events in each bin: T event , contains n values, in order

[0131] Number of non-events in each bin: T non-event , contains n values, in order

[0132] Total number of events: N event ,

[0133] Total number of non-events: N non-event

[0134] Woe value of each bin,

[0135] According to the bin group number and the woe value of the feature bin, as well as the corresponding target syntax matching rules, the corresponding SQL statement can be generated, for example:

[0136] '{"grp_WEIGHT": 1, "woe_WEIGHT": 0.0937}'

[0137] The present invention obtains each feature binning rule, and analyzes and processes each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule; obtains the target syntax matching rule corresponding to each feature binning rule according to each feature type; and generates the SQL statement corresponding to each feature binning rule based on each feature binning rule and the target syntax matching rule corresponding to each feature binning rule. When processing the rule of each feature, the present invention first determines the feature type, whether it is an interval type or a column name type, and then generates the corresponding SQL statement according to the corresponding rule analysis and syntax matching, thereby realizing the automatic conversion of feature binning rules into SQL statements. When processing a large number of features and a large number of bins, and when the binning rules are frequently updated, the labor cost and time cost of outputting the model to the generation environment are reduced, and the automatic process of generating corresponding SQL statements from feature rules is realized, thereby improving the generation efficiency of SQL statements and greatly improving the modeling efficiency of modelers.

[0138] ​Further, based on the first embodiment, a second embodiment of the method for generating SQL statements of the present invention is proposed. In this embodiment, after generating the SQL statements corresponding to the feature binning rules based on the feature binning rules and the target grammar matching rules corresponding to the feature binning rules, the method further includes:

[0139] Step a, obtaining the generation order of each SQL statement;

[0140] Step b: concatenate all SQL statements in the order in which they are generated to generate a complete SQL statement.

[0141] The system can splice each SQL statement after obtaining all SQL statements to obtain a complete SQL statement; or it can splice when obtaining some SQL statements until all SQL statements are spliced ​​to obtain a complete SQL statement. In this embodiment, the splicing order of SQL statements can be determined according to the generation order of SQL statements. That is, the traversal generation order of SQL statements can be used as the splicing order of the final complete SQL statement. Each generated SQL statement is spliced ​​in series to obtain the final complete SQL statement. Figure 5 As shown, the SQL statement above the diagram is concatenated with subsequent SQL statements to form a final complete SQL statement.

[0142] In addition, an embodiment of the present invention further provides a SQL statement generating device, wherein the SQL statement generating device comprises:

[0143] An analysis module is used to obtain each feature binning rule and analyze and process each feature binning rule according to a preset binning rule library to obtain a feature type of each feature binning rule;

[0144] An acquisition module is used to acquire target grammar matching rules corresponding to each feature binning rule according to each feature type;

[0145] A generation module is used to generate SQL statements corresponding to each feature binning rule based on each feature binning rule and a target grammar matching rule corresponding to each feature binning rule.

[0146] Optionally, the analysis module includes:

[0147] An analysis unit, used to analyze and process each feature binning rule according to a preset binning rule library to obtain feature attributes of each feature binning rule;

[0148] A first judgment unit, configured to determine that if the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is an interval type;

[0149] The second judgment unit is used to determine that if the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is an interval type.

[0150] Optionally, the analysis module further includes:

[0151] The missing value unit is used to discard the feature binning rule and not process it if the feature attribute is a missing value attribute.

[0152] Optionally, the feature type is one of a column name type and an interval type, and the acquisition module includes:

[0153] A first acquisition unit is used for acquiring a target syntax matching rule of a corresponding feature binning rule as a column name type syntax matching rule if the feature type is a column name type;

[0154] The second acquisition unit is used to acquire the target grammar matching rule corresponding to the feature binning rule as the interval-type grammar matching rule if the feature type is the interval-type.

[0155] Optionally, the SQL statement generating device further includes:

[0156] Sequential module, used to obtain the generation order of each SQL statement;

[0157] The splicing module is used to splice all SQL statements according to the generation order to generate a complete SQL statement.

[0158] Optionally, the analysis module includes:

[0159] A business unit, used for obtaining an actual business scenario of the target feature binning rule if the feature type of the target feature binning rule does not exist in the analysis and processing result;

[0160] A determination unit is used to determine the feature type of the target feature binning rule according to the actual business scenario, and to extract the feature types of other feature binning rules except the target feature binning rule from the analysis result.

[0161] Optionally, the generating module includes:

[0162] A statistical unit, used to obtain statistical information and binning group numbers in each feature binning rule, wherein the statistical information includes non-event values ​​and event values;

[0163] A generating unit is used to generate a woe value for each feature binning rule according to the non-event value and the event value, respectively, and to generate a SQL statement corresponding to each feature binning rule according to the binning group number, the woe value and the corresponding target syntax matching rule of each feature binning rule.

[0164] In addition, an embodiment of the present invention further proposes a device, which includes: a memory 109, a processor 110, and a SQL statement generation program stored in the memory 109 and executable on the processor 110. When the SQL statement generation program is executed by the processor 110, the steps of each embodiment of the above-mentioned SQL statement generation method are implemented.

[0165] In addition, the present invention also provides a computer storage medium, which stores one or more programs. The one or more programs can also be executed by one or more processors to implement the steps of each embodiment of the above-mentioned SQL statement generation method.

[0166] The extended contents of the specific implementation methods of the device and storage medium (ie, computer storage medium) of the present invention are basically the same as the above-mentioned embodiments of the SQL statement generation method, and are not described in detail here.

[0167] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.

[0168] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0169] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes a number of instructions for a device (which can be a mobile phone, a computer, a server, or a network device, etc.) to execute the methods described in each embodiment of the present invention.

[0170] The embodiments of the present invention are described above in conjunction with the accompanying drawings, but the present invention is not limited to the above-mentioned specific implementation methods. The above-mentioned specific implementation methods are merely illustrative and not restrictive. Under the enlightenment of the present invention, ordinary technicians in this field can also make many forms without departing from the scope of protection of the purpose of the present invention and the claims, which all fall within the protection of the present invention.

Claims

1. A method for generating a SQL statement, characterized in that: The SQL statement generation method is applied to the field of financial technology, and the method includes: Obtain each feature binning rule, and analyze and process each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule; The analysis and processing of each feature binning rule according to a preset binning rule library to obtain the feature type of each feature binning rule includes: Analyze and process each feature binning rule according to a preset binning rule library to obtain the feature attributes of each feature binning rule; If the feature attribute is a limited partition attribute, the feature type of the feature binning rule is a column name type; If the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is interval type; Acquire a target syntax matching rule corresponding to each feature binning rule according to each feature type, wherein the feature type is an SQL statement rule of the feature binning rule; Based on each of the feature binning rules and the target grammar matching rule corresponding to each of the feature binning rules, generate an SQL statement corresponding to each of the feature binning rules; Wherein, generating SQL statements corresponding to each feature binning rule based on each feature binning rule and the target syntax matching rule corresponding to each feature binning rule includes: Obtain statistical information and binning group numbers in each feature binning rule, wherein the statistical information includes non-event values ​​and event values; The woe value of each feature binning rule is generated according to the non-event value and the event value respectively, and the SQL statement corresponding to each feature binning rule is generated according to the binning group number, the woe value and the corresponding target syntax matching rule of each feature binning rule.

2. The SQL statement generation method according to claim 1, characterized in that: After analyzing and processing each feature binning rule according to the preset binning rule library to obtain the feature attributes of each feature binning rule, the method further includes: If the feature attribute is a missing value attribute, the feature binning rule is discarded and not processed.

3. The SQL statement generation method according to claim 1, characterized in that: The feature type is one of a column name type and an interval type, and the step of obtaining a target grammar matching rule corresponding to each feature binning rule according to each feature type includes: If the feature type is column name type, the target syntax matching rule for obtaining the corresponding feature binning rule is the column name type syntax matching rule; If the feature type is interval type, the target grammar matching rule for obtaining the corresponding feature binning rule is the interval type grammar matching rule.

4. The SQL statement generation method according to claim 1, characterized in that: After generating the SQL statements corresponding to the feature binning rules based on the feature binning rules and the target grammar matching rules corresponding to the feature binning rules, the method further includes: Get the generation order of each SQL statement; All SQL statements are concatenated according to the generation order to generate a complete SQL statement.

5. The SQL statement generation method according to claim 1, characterized in that: The feature types for obtaining each feature binning rule include: If the feature type of the target feature binning rule does not exist in the analysis and processing result, the actual business scenario of the target feature binning rule is obtained; The feature type of the target feature binning rule is determined according to the actual business scenario, and the feature types of other feature binning rules except the target feature binning rule are extracted from the analysis and processing results.

6. A SQL statement generating device, characterized in that: The SQL statement generation device is applied to the field of financial technology, and the device comprises: An analysis module is used to obtain each feature binning rule, and analyze and process each feature binning rule according to a preset binning rule library to obtain a feature type of each feature binning rule. The analysis module is specifically used to analyze and process each feature binning rule according to a preset binning rule library to obtain a feature attribute of each feature binning rule; if the feature attribute is a limited partitioning attribute, the feature type of the feature binning rule is a column name type; if the feature attribute is a range partitioning attribute, the feature type of the feature binning rule is an interval type; An acquisition module, used for acquiring a target syntax matching rule corresponding to each feature binning rule according to each feature type, wherein the feature type is an SQL statement rule of the feature binning rule; A generation module is used to obtain statistical information and binning group numbers in each feature binning rule, wherein the statistical information includes non-event values ​​and event values; generate woe values ​​for each feature binning rule according to the non-event values ​​and the event values, respectively; and generate SQL statements corresponding to each feature binning rule according to the binning group number, the woe value and the corresponding target syntax matching rule of each feature binning rule.

7. A device, characterized in that: The device comprises: a memory, a processor, and a SQL statement generation program stored in the memory and executable on the processor. When the SQL statement generation program is executed by the processor, the steps of the SQL statement generation method according to any one of claims 1 to 5 are implemented.

8. A storage medium, characterized in that: The storage medium stores a SQL statement generation program, and when the SQL statement generation program is executed by the processor, the steps of the SQL statement generation method according to any one of claims 1 to 5 are implemented.

Images