A Specific Target Selection Method and Its Application in the Screening of Potential Customer Groups

Abstract

The present invention belongs to the technical field of electronic data processing and information retrieval, and specifically relates to a method for selecting specific targets and its application in screening potential customer groups. The method for selecting and its application of the present invention utilize the RoaringBitmap algorithm to achieve the calculation of intersection, union, and difference through precise bit operations, efficiently realizing the operation between two containers, and showing excellent performance in both storage and computing. At the same time, the use of a self-developed visual editor greatly simplifies the process of user selection, reduces the burden of non-technical staff in data preprocessing, and enables their work to be more focused, such as focusing on the formulation of business logic and marketing strategies. Through this application, staff can configure on an intuitive page without having to deeply understand the underlying data structure and algorithm principle. The user selection function becomes simple and fast, and precise screening of the target user group can be achieved with just a simple click.

Description

Technical Field

[0001] The present invention belongs to the technical field of electronic data processing and information retrieval, and particularly relates to a method for selecting a specific target and its application in screening potential customer groups. Background Art

[0002] With the rapid development of information technology, enterprises increasingly rely on data analysis to make decisions in their daily operations and strategic planning. Data analysis can help enterprises identify trends, optimize operations, discover new business opportunities, and gain an advantage in the highly competitive market. Therefore, data has become an important asset of enterprises, and how to effectively analyze and utilize this data has become the focus of increasing attention of enterprise management.

[0003] However, in the traditional data processing process, non-technical business personnel often need to rely on the support of the IT department to obtain and analyze data. This dependence not only increases the workload of the IT department but also prolongs the decision-making cycle. Whenever business personnel need a specific data report or analysis result, they often need to submit a request to the IT department and then wait for IT personnel to extract data, write query scripts, and generate reports. This process is time-consuming and inefficient, especially in cases where data needs to be updated frequently or immediate decisions need to be made.

[0004] Due to the lack of appropriate tools and technical support, business personnel face a relatively high technical threshold in the process of participating in data analysis. Traditional data query and analysis tools usually require certain programming skills and in-depth understanding of database systems. For business personnel without these skills, it is difficult to independently complete data analysis tasks, which limits their role in the decision-making process. In addition, this dependence also makes it difficult for business personnel to respond promptly to market changes and quickly obtain the required information to make decisions. Summary of the Invention

[0005] In view of the deficiencies of the prior art, the present invention provides a method for selecting a specific target and its application in screening potential customer groups. The purpose of the present invention is to enable non-technical personnel to easily participate in data analysis and improve the overall decision-making efficiency without affecting the existing IT infrastructure, especially to help business personnel focus more on business logic and strategy formulation.

[0006] The first aspect of the present invention is to provide a method for selecting a specific target, including the following steps:

[0007] Step 101: Collect the behavioral data and business data of users occurring on the terminal platform;

[0008] Step 102: Classify and statistically analyze the data collected in Step 101, develop attributes for users, and form structured data of user attributes;

[0009] Step 103: Based on the user attributes developed in Step 102, configure the rules for user selection in the visual logic editor to generate corresponding JSON-format data at the front end;

[0010] Step 104: The back end obtains the JSON-format data generated at the front end in Step 103 for subsequent parsing;

[0011] Step 105: Based on the JSON-format data obtained in Step 104, perform parsing in the logical operation engine to obtain the filtered fields and data types;

[0012] Step 106: Based on the JSON-format data obtained in Step 104, perform parsing in the logical operation engine to obtain the filtering conditions, and obtain the corresponding functions according to the filtering conditions and data types for splicing the conditional part of the query statement;

[0013] Step 107: Based on the JSON-format data obtained in Step 104, perform parsing in the logical operation engine to obtain the conditional relationships within a single conditional group;

[0014] Step 108: Based on the JSON-format data obtained in Step 104, perform parsing in the logical operation engine to obtain the relationships between conditional groups;

[0015] Step 109: Generate a single query statement according to the filtered fields and data types obtained in Step 105 and the filtering conditions obtained in Step 106; the query statement uses a bitmap Bitmap as the output result for subsequent logical operations on the bitmap;

[0016] Step 110: Combine the single query statement generated in Step 109 with the conditional relationships within a single conditional group obtained in Step 107 to generate the query statement for the same conditional group; the query statement for the same conditional group uses a bitmap Bitmap as the output result;

[0017] Step 111: Generate the final query statement through the conditional group relationships obtained in Step 108 and the query statements for the same conditional group generated in Step 110; the final query statement uses a bitmap Bitmap as the output result;

[0018] Step 112: Execute the final query statement generated in Step 111 to perform the selection of specific target populations.

[0019] As a further optimization solution for the above specific target selection method, in step 103, there are several editable condition group units on the operation interface of the visual logic editor. The number of condition group units is increased or decreased through operation buttons, and the logical relationship between condition group units is set through operation buttons; through the setting of logical relationships, the condition group units are combined to form a condition combination; several conditions and their logical relationships can be edited within each condition group unit.

[0020] As a further optimization solution for the above specific target selection method, in step 105, define an empty list to store the filtering fields and their data types; traverse the 'conditionDatas' list under the 'data' field in json_data. This list contains one or more condition groups; for each condition group condition_group, traverse each condition condition within this condition group again; for each condition condition, obtain the field name and data type in this condition and add them to the list used to store the filtering fields and their data types.

[0021] As a further optimization solution for the above specific target selection method, in step 106, define an empty list to store the filtering conditions; traverse the condition groups in the JSON data, find the 'data' field in json_data, and continue to access the 'conditionDatas' list under 'data'. This list contains all the condition groups; for each condition group condition_group, traverse each condition condition within this condition group again; for each condition condition, obtain the field name, operator, and value in this condition and add them to the list used to store the filtering conditions.

[0022] As a further optimization solution for the above specific target selection method, in step 107, define an empty list to store the condition relationships within each condition group; traverse the condition groups in the JSON data, find the 'data' field in json_data; continue to access the 'conditionDatas' list under 'data'. This list contains all the condition groups; for each condition group condition_group, obtain the condition relationship in this condition group and add it to the list used to store the condition relationships within each condition group.

[0023] As a further optimization solution for the above specific target selection method, in step 108, define a variable for storing the relationships between condition groups; find the 'data' field in json_data, and then continue to access the 'groupRelation' field under 'data'. This field specifies the logical relationships between all condition groups, and assign it to the variable for storing the relationships between condition groups.

[0024] As a further optimization solution for the above specific target selection method, in step 109, define an empty list for storing the generated single query statement; traverse the tuples of the list returned in step 106; construct the basic SQL query statement structure SELECT rb_build (array_agg(id)::int[]) AS bt FROM table WHERE field_name operator value; then fill in the information required for the query statement and store it in the list for storing the generated single query statement.

[0025] As a further optimization solution for the above specific target selection method, in step 110, define an empty list for storing the generated condition group query statements; traverse the list returned in step 107, and combine the query statements generated in step 109 according to each value to generate the condition group-level query statements, and store them in the list for storing the generated condition group query statements.

[0026] As a further optimization solution for the above specific target selection method, in step 111, S11.1: According to the values of the list returned in step 106, use the corresponding SQL functions to combine the condition group-level query statements generated in step 110 to obtain the final SQL query statement.

[0027] The second aspect of the present invention provides the application of the above specific target selection method in the screening of potential customer groups, which facilitates non-technical personnel to efficiently and accurately screen potential customer groups.

[0028] Beneficial effects

[0029] The selection method provided by the present invention has advantages in simplifying the selection process, reducing the technical threshold, improving security, enhancing flexibility and scalability, etc.

[0030] The method of the present invention significantly improves the efficiency and security of user selection, while reducing the technical threshold, enabling business personnel without a technical background to easily participate in the data analysis and decision-making process. Through the self-developed visual editor, users can accurately screen the target user group with just a simple click, greatly simplifying the operation process and reducing the workload of data preprocessing. This intuitive page configuration method enables business personnel to quickly get started without in-depth understanding of the underlying data structure and algorithm principles, improving work efficiency.

[0031] The method of the present invention also performs excellently in terms of improving flexibility and scalability. The easy version control and reuse of JSON configuration files enable new query conditions and logics to be easily added to the JSON file, and then the conversion logic is updated to support new query requirements. This design not only improves the reusability of query logics, but also enables the system to more flexibly adapt to changing business needs. At the same time, through the process of converting JSON to SQL, verification and cleaning steps can be added during the conversion process to ensure that the generated SQL statements are safe and effective, reducing security risks such as SQL injection.

[0032] The method of the present invention improves the maintainability and upgradability of the system by decoupling the logical design and execution. The front end is responsible for collecting user requirements and expressing them in the form of JSON, while the back end is responsible for parsing the JSON and generating the corresponding SQL queries. This separation helps to maintain and upgrade different parts of the system without affecting other parts, enabling the system to more flexibly adapt to changing business needs. Description of the Drawings

[0033] Figure 1 It is a partial interface schematic diagram of the visual logic editor.

[0034] Figure 2 It is a corresponding relationship diagram of the query statement functions referred to by different data type screening conditions.

[0035] Figure 3 It is a corresponding relationship diagram of the key functions corresponding to the conditional relationships. Detailed Embodiments

[0036] The present invention will be further illustrated below through specific embodiments. These embodiments are exemplary, aiming to illustrate the problem and explain the present invention, rather than a limitation.

[0037] Step 101: Collect the behavior data of users occurring on the terminal platform and the relevant data of the business database.

[0038] Some business data of users occurring on the terminal platform is as follows:

[0039] Time, User Code, User Age, User Name, Order Number, Course Name;

[0040] 2023 / 1 / 3 11:11, 1, 20, Zhang San, 123, Mathematics;

[0041] 2023 / 1 / 4 09:15, 1, 20, Zhang San, 234, English;

[0042] 2023 / 1 / 5 11:11, 2, 21, Li Si, 235, Chinese;

[0043] 2023 / 1 / 6 11:11, 2, 21, Li Si, 236, Computer;

[0044] 2023 / 1 / 7 11:11, 3, 25, Wang Chao, 237, Computer.

[0045] Part of the behavioral data of the user on the terminal platform is as follows:

[0046] Time, User Code, Action, Course Name;

[0047] 2023 / 1 / 1 10:11, 1, Click, Mathematics;

[0048] 2023 / 1 / 2 10:11, 1, Browse, Mathematics;

[0049] 2023 / 1 / 3 10:11, 1, Click, Mathematics;

[0050] 2023 / 1 / 3 18:06, 1, Click, English;

[0051] 2023 / 1 / 4 19:01, 2, Click, Computer;

[0052] 2023 / 1 / 5 10:11, 2, Click, Chinese;

[0053] 2023 / 1 / 6 10:11, 3, Browse, Computer.

[0054] Step 102: According to the data collected in Step 101, classify and statistically analyze the behavioral data to develop refined attributes for the user.

[0055] For each user, classify and statistically analyze their behavioral data, and combine their business information to extract personalized attribute features to construct a detailed user feature portrait. The following is part of the data of the constructed user attributes:

[0056] User Code, Course List, User Name, User Age, Number of Clicks on Mathematics, …;

[0057] 1, [Mathematics, English], Zhang San, 20, 2, …;

[0058] 2, [Chinese, Computer Science], Li Si, 21, 0, …;

[0059] 3, [Computer Science], Wang Chao, 25, 0, …

[0060] Step 103: Based on the user attributes developed in Step 102, configure the rules for user selection in the visual logic editor to generate the JSON - formatted data required by the logical operation engine.

[0061] On the operator's terminal of the staff, there is a visual logic editor. As Figure 1 shown, there are several editable condition - group units on the operation interface. The number of condition - group units can be increased or decreased through operation buttons, and the logical relationship between condition - group units can be set through operation buttons. Through the setting of logical relationships, these condition - group units form a condition combination. Several conditions and the logical relationships between these internal conditions can be edited within each condition - group unit.

[0062] As Figure 1 shown, the interface shows that the edited condition combination includes:

[0063] Condition combination C

[0064] Condition - group unit C1

[0065] Condition C1.1: Gender is "male";

[0066] Condition C1.2: Registration date is greater than or equal to May 1, 2024;

[0067] Condition - group unit C2

[0068] Condition C2.1: Province is "Shanxi Province";

[0069] Condition C2.2: Target school is "Taiyuan University of Technology".

[0070] In this condition combination C, the union is taken between condition - group unit C1 and condition - group unit C2, the intersection is taken for the conditions within condition - group unit C1, and the intersection is taken for the conditions within condition - group unit C2.

[0071] The operator's terminal of the staff can be a web - interface terminal, a mobile - terminal APP interface terminal, etc. Taking the web - interface terminal as an example, when the user selects and configures query conditions through the visual logic editor, the front - end will listen for the user's interaction events (such as clicks, inputs, etc.) and convert these operations into JSON - formatted data.

[0072] The above process mainly includes the following steps:

[0073] User operation: The user selects and configures query conditions through the interface provided by the front end, such as selecting a certain field, entering a condition value, etc.

[0074] Event listening: The front-end JavaScript code listens for these user operation events and records the user's selections.

[0075] State management: According to the user's operations, the front-end framework (such as React, Vue, or Angular, etc.) updates the state of the application, and this state contains all the selection information of the user.

[0076] JSON generation: When the user completes the configuration and submits (for example, clicks the "Save" or "Generate Query" button), the front end generates a JSON object based on the state of the application.

[0077] Step 104: The back end obtains the JSON object generated by the front end in Step 103 for subsequent parsing.

[0078] Exemplarily, after the staff performs the operation in Step 103, the JSON object data obtained by the back end is as follows:

[0079] Explanation of json key attributes

[0080] groupRelation Relationship between condition groups

[0081] conditionRelation Relationship between multiple conditions within a condition group

[0082] params.type Data type

[0083] params.fieldName Query field

[0084] params.operator Query condition

[0085] params.value Value of the query condition

[0086] {

[0087] "data": {

[0088] "groupRelation": "or",

[0089] "conditionDatas":

[0090] {

[0091] "conditionRelation": "and",

[0092] "params":

[0093] {

[0094] "id": "5d6b93bb-b34d-5a89-6f49-5ccedd4655e5",

[0095] "type": "string",

[0096] "fieldName": "gender",

[0097] "fieldNameShow": "Gender",

[0098] "operator": "equal",

[0099] "operatorShow": "Equal to",

[0100] "value":

[0101] "male"

[0102] ,

[0103] "valueShow": "male, "

[0104] },

[0105] {

[0106] "id": "4f5e4f52-9483-1444-3f40-2a637bcc99b0",

[0107] "type": "date",

[0108] "fieldName": "reg_date",

[0109] "fieldNameShow": "Registration Date",

[0110] "operator": "greater_equal",

[0111] "operatorShow": "Greater than or equal to",

[0112] "value": "2024-05-01",

[0113] "valueShow": "2024-05-01"

[0114] } ​

[0116] },

[0117] {

[0118] "conditionRelation": "and",

[0119] "params": [

[0120] {

[0121] "id": "d6b544a6-bb64-48cb-3ec3-7a6fd61e93cc",

[0122] "type": "string",

[0123] "fieldName": "province",

[0124] "fieldNameShow": "Province",

[0125] "operator": "equal",

[0126] "operatorShow": "equals",

[0127] "value": [

[0128] Shanxi Province

[0129] ],

[0130] "valueShow": "Shanxi Province,"

[0131] },

[0132] {

[0133] "id": "6ef1dafe-3819-3881-3c26-50d2964b0b0b",

[0134] "type": "string",

[0135] "fieldName": "target_school_name",

[0136] "fieldNameShow": "Target School",

[0137] "operator": "equal",

[0138] "operatorShow": "equals",

[0139] "value":

[0140] "Taiyuan University of Technology"

[0141] ,

[0142] "valueShow": "Taiyuan University of Technology,"

[0143] }

[0145] }

[0147] }

[0148] }

[0149] Step 105: Based on the JSON-formatted data obtained in Step 104, parse it in the logical operation engine to obtain the filtered fields and data types.

[0150] Specifically, parse the JSON data through the following steps to obtain the filtered fields and their data types:

[0151] S5.1: Define an empty list fields_with_types to store the filtered fields and their data types.

[0152] S5.2: Traverse the 'conditionDatas' list under the 'data' field in json_data. This list contains one or more condition groups.

[0153] S5.3: For each condition group condition_group, traverse each condition condition within the condition group again.

[0154] S5.4: For each condition condition: Obtain the field name field_name in the condition, i.e., condition['fieldName']; obtain the data type field_type in the condition, i.e., condition['type']; then add the pair (field_name, field_type) to the fields_with_types list.

[0155] S5.5: After all conditions have been traversed, return the fields_with_types list.

[0156] After the above steps, the returned result is as follows:

[0158] ​​​('gender','string'),

[0159] ('reg_date', 'date'),

[0160] ('province','string'),

[0161] ('target_school_name','string')

[0162] 。

[0163] Four fields and their corresponding data types are obtained, namely gender (string type), registration date (date type), province (string type), and target school name (string type).

[0164] Step 106: Based on the JSON-formatted data obtained in Step 104, parse it in the logical operation engine to obtain the filtering conditions, and obtain the corresponding functions according to the filtering conditions and data types for splicing the conditional part of the query statement.

[0165] Specifically, the JSON data is parsed through the following steps to obtain the filtering conditions and the corresponding functions:

[0166] S6.1: Define an empty list conditions to store the filtering conditions.

[0167] S6.2: Traverse the condition groups in the JSON data, find the 'data' field in json_data, and continue to access the 'conditionDatas' list under 'data', which contains all the condition groups.

[0168] S6.3: For each condition group condition_group, traverse each condition condition within the condition group again.

[0169] S6.4: For each condition condition, obtain the field name field_name in the condition, that is, condition['fieldName']; obtain the operator operator in the condition, that is, condition['operator']; obtain the value value in the condition, that is, condition['value'].

[0170] S6.5: Add the set of condition information (field_name, operator, value) to the conditions list.

[0171] S6.6: After all conditions have been traversed, return the conditions list.

[0172] After the above steps, the returned results are as follows:

[0174] ('gender', 'equal', ['male']),

[0175] ('reg_date', 'greater_equal', '2024-05-01'),

[0176] ('province', 'equal', ['Shanxi Province']),

[0177] ('target_school_name', 'equal', ['Taiyuan University of Technology'])

[0178] 。

[0179] Step 107: Based on the JSON-formatted data obtained in Step 104, parse it in the logical operation engine to obtain the condition relationships within a single condition group and the corresponding functions.

[0180] Specifically, parse the condition relationships and the corresponding functions within a single condition group through the following steps:

[0181] S7.1: Define an empty list relations to store the condition relationships within each condition group.

[0182] S7.2: Traverse the condition groups in the JSON data, find the 'data' field in json_data; then continue to access the 'conditionDatas' list under 'data', which contains all the condition groups.

[0183] S7.3: For each condition group condition_group, obtain the condition relationship relation within this condition group, that is, condition_group['conditionRelation'].

[0184] S7.4: Add relation to the relations list.

[0185] S7.5: After all condition groups have been traversed, return the relations list.

[0186] The returned results are as follows: ​​

[0188] 'and',

[0189] 'and'

[0190] 。

[0191] Note: There may be multiple conditions within a single condition group. Therefore, it is necessary to clarify the logical relationship (intersection, union, difference) between multiple conditions before generating a query statement for a condition group.

[0192] Step 108: Based on the JSON-formatted data obtained in Step 104, parse it in the logical operation engine to obtain the relationships between condition groups and the corresponding functions.

[0193] Specifically, the relationships between condition groups and the corresponding functions are parsed through the following steps:

[0194] S8.1: Define a variable group_relation to store the relationships between condition groups.

[0195] S8.2: Extract the relationships between condition groups from the JSON data: Locate the 'data' field in json_data and then continue to access the 'groupRelation' field under 'data'. This field specifies the logical relationships between all condition groups.

[0196] S8.3: Return the extracted relationships between condition groups, group_relation.

[0197] The returned result is as follows:

[0198] "group_relation": "or".

[0199] This result indicates that in the final query statement, the logical relationship between different condition groups is an "or" (OR) relationship.

[0200] Note: In the entire circled result, there may be multiple condition groups. Therefore, it is necessary to clarify the logical relationships between multiple condition groups.

[0201] Step 109: Generate a single query statement based on the filtered fields and data types obtained in Step 105 and the filtering conditions obtained in Step 106. The generated query statement uses a bitmap as the output result to facilitate subsequent efficient logical operations on the bitmap.

[0202] Specifically, a single query statement is generated through the following steps:

[0203] S9.1: Define an empty list queries to store the generated single query statements.

[0204] S9.2: Traverse the conditions list returned by step 106. For each (field_name, operator, value) tuple:

[0205] S9.3: Construct the basic SQL query statement structure SELECT rb_build (array_agg(id)::int[]) AS bt FROM table WHERE field_name operator value;.

[0206] S9.4: Process different data types and operators. If value is an array, use the IN operator; if it is a single value, directly use the equal sign = or other comparison operators.

[0207] S9.5: Ensure that the table name table is correctly replaced with the actual table name used, which is shown here as tags_dev.dws_user_nature_all_tag or tags_dev.dws_user_nature_all_tagWW.

[0208] S9.6: Add the constructed query statement to the queries list.

[0209] S9.7: When all tuples have been processed, return the queries list.

[0210] The single query statements generated according to the above steps include the following four:

[0211] SELECT rb_build (array_agg(id)::int[]) AS bt FROM

[0212] tags_dev.dws_user_nature_all_tagWW WHERE gender IN ('male')

[0213] SELECT rb_build (array_agg(id)::int[]) AS bt FROM

[0214] tags_dev.dws_user_nature_all_tag WHERE reg_date >= '2024-05-01'

[0215] SELECT rb_build (array_agg(id)::int[]) AS bt FROM

[0216] ags_dev.dws_user_nature_all_tag WHERE province IN ('Shanxi Province')

[0217] SELECT rb_build (array_agg(id)::int[]) AS bt FROM

[0218] tags_dev.dws_user_nature_all_tag WHERE target_school_name IN ('Taiyuan University of Technology'))。

[0219] Step 110: Combine the single query statement generated in Step 109 with the conditional relationships within the single conditional group obtained in Step 107 to generate a query statement for the same conditional group, and its result is also a bitmap.

[0220] Specifically, the query statement for the same conditional group is generated through the following steps:

[0221] S10.1: Define an empty list group_queries to store the generated conditional group query statements.

[0222] S10. 2: Traverse the relations list returned in Step 107. For each relation value:

[0223] S10.3: Merge the query statements generated in Step 109 according to the relation value (such as AND or OR) to generate a query statement at the conditional group level.

[0224] S10.4: Use the WITH clause to create a common table expression (CTE) and save the query result of each conditional group in a temporary table.

[0225] S10.5: For the AND relationship, use the rb_and_null2empty function to merge the results; for the OR relationship, use the rb_or_null2empty function.

[0226] S10.6: Add the constructed query statement at the conditional group level to the group_queries list.

[0227] S10.7: After all relations have been processed, return the group_queries list.

[0228] The query statements for the same condition group generated according to the above steps include the following two groups:

[0229] WITH

[0230] a_base1 AS ( SELECT rb_build (array_agg(id)::int[]) AS bt FROM tags_dev.dws_user_nature_all_tagWW WHERE gender IN ('male') ),

[0231] b_base1 AS ( SELECT rb_build (array_agg(id)::int[]) AS bt FROM tags_dev.dws_user_nature_all_tag WHERE reg_date>= '2024-05-01')

[0232] SELECT rb_or_null2empty (rb_and_null2empty (COALESCE(a_base1.bt, rb_build ('{}')), COALESCE(b_base1.bt, rb_build ('{}'))) FROM a_base1, b_base1

[0233] WITH

[0234] a_base2 AS ( SELECT rb_build (array_agg(id)::int[]) AS bt FROM tags_dev.dws_user_nature_all_tag WHERE province IN ('Shanxi Province')),

[0235] b_base2 AS ( SELECT rb_build (array_agg(id)::int[]) AS bt FROM tags_dev.dws_user_nature_all_tag WHERE target_school_name IN ('Taiyuan University of Technology'))

[0236] SELECT rb_and_null2empty (COALESCE(a_base2.bt, rb_build ('{}')),COALESCE(b_base2.bt, rb_build ('{}')))) FROM a_base2, b_base2

[0237] Step 111: Generate the final bitmap query statement based on the condition group relationship obtained in Step 108 and the group query statement generated in Step 110.

[0238] Specifically, the final query statement is generated through the following steps:

[0239] S11.1: According to the group_relation value (such as OR) returned in Step 106, use the corresponding SQL function (such as rb_or_null2empty or rb_and_null2empty) to merge the query statements at the condition group level generated in Step 110.

[0240] S11.2: Return the final SQL query statement to the system for execution.

[0241] The above steps complete the entire process from user interface operation to the final SQL query statement. The generated final SQL query statement is as follows:

[0242] WITH a_base1 AS (

[0243] SELECT

[0244] rb_build (array_agg(id)::int[])AS bt

[0245] FROM

[0246] tags_dev.dws_user_nature_all_tagWW

[0247] WHERE

[0248] gender IN ('male')

[0249] ),

[0250] b_base1 AS (

[0251] SELECT

[0252] rb_build (array_agg(id)::int[])AS bt

[0253] FROM

[0254] tags_dev.dws_user_nature_all_tag

[0255] WHERE

[0256] reg_date>= '2024-05-01'

[0257] ),

[0258] a_base2 AS (

[0259] SELECT

[0260] rb_build (array_agg(id)::int[])AS bt

[0261] FROM

[0262] tags_dev.dws_user_nature_all_tag

[0263] WHERE

[0264] province IN ('Shanxi Province')

[0265] ),

[0266] b_base2 AS (

[0267] SELECT

[0268] rb_build (array_agg(id)::int[])AS bt

[0269] FROM

[0270] tags_dev.dws_user_nature_all_tag

[0271] WHERE

[0272] target_school_name IN ('Taiyuan University of Technology'))

[0273] SELECT

[0274] rb_or_null2empty (rb_and_null2empty (COALESCE(a_base1.bt, rb_build ('{}')), COALESCE(b_base1.bt, rb_build ('{}'))),rb_and_null2empty(COALESCE(a_base2.bt, rb_build ('{}')), COALESCE(b_base2.bt, rb_build ('{}'))))

[0275] FROM

[0276] a_base1,

[0277] b_base1,

[0278] a_base2,

[0279] b_base2

[0280] In the bitmap output generated by the above query statement, each subquery generates a bitmap, and then logical operations are performed on these bitmaps through bitmap operation functions (rb_and_null2empty and rb_or_null2empty) to finally generate a bitmap result. This kind of bitmap operation can reduce the time complexity of the processing process and contribute to the efficient calculation of intersections, unions, and differences of large amounts of data. The explanation of the above-generated bitmap query statement is as follows:

[0281] Generate basic bitmaps

[0282] a_base1 and b_base1 respectively generate bitmaps of user IDs that meet the conditions of gender IN ('male') and reg_date >= '2024-05-01'.

[0283] a_base2 and b_base2 respectively generate bitmaps of user IDs that meet the conditions of province IN ('Shanxi Province') and target_school_name IN ('Taiyuan University of Technology').

[0284] Merge bitmaps

[0285] rb_and_null2empty (COALESCE(a_base1.bt, rb_build ('{}')), COALESCE(b_base1.bt, rb_build ('{}'))) calculates the intersection of the a_base1 and b_base1 bitmaps.

[0286] rb_and_null2empty(COALESCE(a_base2.bt, rb_build('{}')), COALESCE(b_base2.bt, rb_build('{}'))) calculates the intersection of the bitmaps of a_base2 and b_base2.

[0287] Final merge

[0288] rb_or_null2empty calculates the union of the above two intersecting bitmaps to obtain the final bitmap result.

[0289] Step 112: Execute the final query statement generated in Step 111 to complete the population selection.

[0290] Note: Parse the JSON - formatted data in the logical operation engine to obtain the filtering conditions, and obtain the corresponding functions according to the filtering conditions and data types for splicing the condition part of the query statement. The query statement functions referenced by the filtering conditions of different data types are as Figure 2 shown.

[0291] Note: There may be multiple conditions within a single condition group. It is necessary to clarify the relationship between multiple conditions as intersection, union, or difference before generating the query statement for a condition group. The key functions corresponding to the condition relationships are as Figure 3 shown.

[0292] Effect verification

[0293] To evaluate the performance of the above - mentioned target selection method compared with the mature commercial solutions purchased in the market in the actual business scenario, the following comparative experiments are implemented. In the experiment, each solution is tested for one week, and about 1400 selection tasks are executed daily for a single solution during the test period. By recording the running time of each task and dividing it into five time intervals (1 - 5 seconds, 5 - 10 seconds, 10 - 30 seconds, 30 - 60 seconds, >60 seconds), the proportion of the number of tasks in each time interval is statistically analyzed, and the results are as follows:

[0294] The selection solution of the present invention:

[0295] The proportion of the number of tasks with a running time of 1 - 5s: 45.2%;

[0296] The proportion of the number of tasks with a running time of 5 - 10s: 30.5%;

[0297] The proportion of the number of tasks with a running time of 10 - 30s: 9.2%;

[0298] The proportion of the number of tasks with a running time of 30 - 60s: 10.1%;

[0299] Percentage of tasks with running time > 60s: 5.0%.

[0300] Commercial solution:

[0301] Percentage of tasks with running time from 1 to 5s: 0%;

[0302] Percentage of tasks with running time from 5 to 10s: 0%;

[0303] Percentage of tasks with running time from 10 to 30s: 10.3%;

[0304] Percentage of tasks with running time from 30 to 60s: 24.1%;

[0305] Percentage of tasks with running time > 60s: 65.6%.

[0306] The results show that the self-developed solution of the present invention is significantly superior to the commercial solution in terms of task execution speed in each time interval. The task proportions of the self-developed solution in the two intervals of 1 - 5 seconds and 5 - 10 seconds are 45.2% and 30.5% respectively, which are significantly higher than 0% and 0% of the commercial solution. In the longer running time intervals, the self-developed solution also performs more excellently, indicating its significant advantages in task execution speed, resource utilization rate and user experience.

[0307] As described above, the user selection method and application utilize the RoaringBitmap algorithm to achieve the calculations of intersection, union and difference through precise bit operations, and efficiently implement the operations between two containers, making it excellent in both storage and computing performance. At the same time, the use of the self-developed visual editor greatly simplifies the user selection process, reduces the burden of non-technical staff in data preprocessing, and enables their work to be more focused, such as focusing on the formulation of business logic and marketing strategies. Through this application, staff can configure on an intuitive page without having to deeply understand the underlying data structure and algorithm principle. The user selection function becomes simple and fast, and precise screening of the target user group can be achieved with just a simple click.

[0308] The solution parses the JSON formed at the front end through the self-developed logical operation engine at the back end, converts it into SQL and then conducts queries. Such operations have the following advantages in multiple aspects:

[0309] Unified interface and flexibility: By allowing staff to configure query conditions through a visual interface and save them in JSON format, a unified interface can be provided for non-technical personnel to use, while maintaining the flexibility of the back-end processing logic. Non-technical personnel, such as business personnel, do not need to directly write SQL code and only need to select and configure conditions through the graphical interface.

[0310] Abstract level: The JSON file describes the logic of the query but does not specifically specify how to execute this logic in the underlying database. By converting JSON to SQL through a logical operation engine, the high-level logical abstraction can be transformed into specific query instructions for a particular database system. This means that even if the backend database changes, as long as the JSON-to-SQL conversion logic is adjusted, the front-end configuration interface can remain unchanged.

[0311] Reusability and scalability: The JSON configuration file is easy to version control and reuse. For non-technical personnel, it is easy to save common query configurations for future reuse. In addition, as business requirements change, new query conditions and logic can be easily added to the JSON file, and then the conversion logic can be updated to support the new query requirements.

[0312] Security and effectiveness: Allowing staff to directly write SQL queries may pose security risks, such as SQL injection attacks. By converting JSON to SQL, it provides a convenient condition to add verification and cleaning steps during the conversion process to ensure that the generated SQL statements are secure and effective.

[0313] Cross-platform compatibility: JSON is a lightweight data exchange format that is easy for humans and machines to read and can be used across platforms. Converting JSON to SQL makes it possible to deploy and run the same query logic in different environments.

[0314] Separation of concerns: This method achieves the decoupling between logical design and execution. The front-end is responsible for collecting user requirements and expressing them in the form of JSON, while the back-end is responsible for parsing the JSON and generating the corresponding SQL queries. This separation helps to maintain and upgrade different parts of the system without affecting other parts.

[0315] Through these mechanisms above, the system can more flexibly adapt to the changing business requirements, and enable non-technical business personnel to also participate in the data analysis and decision-making process, thereby improving work efficiency.

[0316] The above embodiments are exemplary, aiming to illustrate the technical concept and features of the present invention so that those skilled in this field can understand the content of the present invention and implement it accordingly, and should not be used to limit the protection scope of the present invention. Any equivalent changes or modifications made according to the spirit and essence of the present invention should be covered within the protection scope of the present invention.

Claims

1. A specific target selection method, characterized in that: It includes the following steps: Step 101: Collect the behavioral data and business data of the user on the terminal platform; Step 102: Classify and statistically analyze the data collected in Step 101, develop the attributes for the user, and form the structured data of the user attributes; Step 103: According to the user attributes developed in Step 102, configure the rules for user selection in the visual logic editor, and generate the corresponding JSON format data at the front end; Step 104: The back end obtains the JSON format data generated at the front end in Step 103 for subsequent parsing; Step 105: Based on the JSON format data obtained in Step 104, perform parsing in the logic operation engine to obtain the filtered fields and data types; Step 106: Based on the JSON format data obtained in Step 104, perform parsing in the logic operation engine to obtain the filtering conditions, and obtain the corresponding functions according to the filtering conditions and data types for splicing the condition part of the query statement; Step 107: Based on the JSON format data obtained in Step 104, perform parsing in the logic operation engine to obtain the condition relationship within a single condition group; Step 108: Based on the JSON format data obtained in Step 104, perform parsing in the logic operation engine to obtain the relationship between condition groups; Step 109: Generate a single query statement according to the filtered fields and data types obtained in Step 105 and the filtering conditions obtained in Step 106; the query statement uses a bitmap Bitmap as the output result for subsequent logical operations on the bitmap; Step 110: Combine the single query statement generated in Step 109 with the condition relationship within a single condition group obtained in Step 107 to generate the query statement for the same condition group; the query statement for the same condition group uses a bitmap Bitmap as the output result; Step 111: Generate the final query statement through the condition group relationship obtained in Step 108 and the query statement for the same condition group generated in Step 110; the final query statement uses a bitmap Bitmap as the output result; Step 112: Execute the final query statement generated in Step 111 to select a specific target population.

2. The specific target selection method according to claim 1, characterized in that: In Step 103, there are several editable condition group units on the operation interface of the visual logic editor. The number of condition group units is increased or decreased through operation buttons, and the logical relationship between condition group units is set through operation buttons; through the setting of the logical relationship, the condition group units are combined to form a condition combination; several conditions and their logical relationships can be edited within each condition group unit.

3. The specific target selection method according to claim 1, wherein: In step 105, define an empty list to store the filtered fields and their data types; traverse the 'conditionDatas' list under the 'data' field in json_data, and this list contains one or more condition groups; for each condition group condition_group, traverse each condition condition within this condition group again; for each condition condition, obtain the field name and data type in this condition and add them to the list used to store the filtered fields and their data types.

4. The specific target selection method according to claim 3, characterized in that: In step 106, define an empty list to store the filtering conditions; traverse the condition groups in the JSON data, find the 'data' field in json_data, and continue to access the 'conditionDatas' list under 'data', which contains all the condition groups; for each condition group condition_group, traverse each condition condition within this condition group again; for each condition condition, obtain the field name, operator, and value in this condition and add them to the list used to store the filtering conditions.

5. The specific target selection method according to claim 4, wherein: In step 107, define an empty list to store the condition relationships within each condition group; traverse the condition groups in the JSON data, find the 'data' field in json_data; continue to access the 'conditionDatas' list under 'data', which contains all the condition groups; for each condition group condition_group, obtain the condition relationship in this condition group and add it to the list used to store the condition relationships within each condition group.

6. The specific target selection method according to claim 5, characterized in that: In step 108, define a variable to store the relationship between condition groups; find the 'data' field in json_data and continue to access the 'groupRelation' field under 'data', which specifies the logical relationship between all condition groups, and assign it to the variable used to store the relationship between condition groups.

7. The specific target selection method according to claim 6, characterized in that: In step 109, define an empty list to store the generated single query statement; traverse the tuples of the list returned in step 106; construct the basic SQL query statement structure SELECT rb_build (array_agg(id)::int[]) AS bt FROM table WHERE field_name operator value; then fill in the information required for the query statement and store it in the list used to store the generated single query statement.

8. The specific target selection method according to claim 7, wherein: In step 110, define an empty list to store the generated condition group query statements; traverse the list returned in step 107, and merge the query statements generated in step 109 according to each value to generate the query statements at the condition group level and store them in the list used to store the generated condition group query statements.

9. The specific target selection method according to claim 8, characterized in that: In step 111, S11.1: According to the values returned by the list in step 106, use the corresponding SQL functions to merge the query statements at the conditional group level generated in step 110 to obtain the final SQL query statement.

10. Application of a specific target selection method in screening potential customer groups, characterized in that: The specific target selection method is the method according to any one of claims 1 to 9.

Images