Pareto graph data analysis method and system based on structured query language

Abstract

The invention discloses a Pareto graph data analysis method and system based on a structured query language, and relates to the technical field of data analysis, the Pareto graph data analysis method comprises the following steps: generating a problem reason statistical table; generating a problem reason statistical table with quantity descending ranking fields; generating an accumulation result of the problem number; generating a question reason statistical table with quantity descending ranking and question quantity accumulation proportion fields; according to a Pareto rule, generating a key reason field by utilizing a logic judgment statement in a structured query language; generating a Pareto graph data statistical table of the in-progress project and the historical project; according to the Pareto data of the historical project, generating an attention field of a project problem reason in progress. According to the method and the device, the problem reason attention field is defined by comparing the key reason fields of the in-progress and the historical in-progress, so that the attention analysis on the problem reason of the in-progress project according to the historical data is realized.

Description

Technical Field

[0001] The present invention relates to the technical field of data analysis, and in particular to a Pareto chart data analysis method and system based on structured query language. Background Art

[0002] At present, all industries at home and abroad can use Pareto charts to analyze quality problems and guide managers to find the key reasons for the largest number of problems, and then solve the main quality problems. Figure 1 Generally, it consists of a bar chart showing the cause of the problem and a line chart showing the cumulative proportion of the number of problems. However, quality problem data generally consists only of the cause of the problem and the description of the problem, and often lacks the data required to draw the bar chart and line chart in the Pareto chart. The SQL window function OVER (PARTITION BY) in the higher versions of each database is usually used to analyze the quality problem data and obtain the Pareto chart data.

[0003] Window functions are also called OLAP functions. OLAP is the abbreviation of online analytical processing, which means real-time analysis and processing of database data. Window functions are SQL functions added to implement OLAP. For example, Oracle has provided analytical functions since 8.1.6. The difference between it and aggregate functions is that it returns multiple rows for each group, while aggregate functions only return one row for each group. For example, after group by grouping and summarizing, the number of rows in the table is changed. A row has only one category, and only the fields involved in the grouping and the results of the aggregate function are retained. Compared with group by, partition by is used to calculate a certain aggregate value based on the group. It can group and sort only certain fields on the basis of retaining all the data.

[0004] In the prior art, Pareto Figure 1 Generally, it consists of a bar chart of problem causes and a line chart of the cumulative proportion of the number of problems. However, quality problem data generally consists only of problem causes and problem descriptions, and often lacks the data required to draw the bar chart and line chart in the Pareto chart. However, in the lower versions of various databases, because the analysis function is not supported, the SQL window function OVER (PARTITION BY) in the higher version cannot be used to analyze the quality problem data, and the Pareto chart data cannot be obtained.

[0005] Currently, no effective solution has been proposed for the problems in the related technologies. Summary of the invention

[0006] In view of the shortcomings of the prior art, the present invention proposes a Pareto chart data analysis method and system based on structured query language, which solves the existing Pareto chart data analysis problems proposed in the above background technology. Figure 1Generally, it consists of a bar chart of problem causes and a line chart of the cumulative proportion of the number of problems. However, quality problem data generally consists only of problem causes and problem descriptions, and often lacks the data required to draw the bar chart and line chart in the Pareto chart. However, in the lower versions of various databases, because the analysis function is not supported, the SQL window function OVER (PARTITION BY) in the higher version cannot be used to analyze the quality problem data, and the problem of the Pareto chart data cannot be found.

[0007] To achieve the above objectives, the present invention is implemented through the following technical solutions:

[0008] According to one aspect of the present invention, a Pareto chart data analysis method based on structured query language is provided, and the Pareto chart data analysis method comprises the following steps:

[0009] S1. Using the data set partitioning statement in the structured query language and the counting function in the structured query language, count each problem cause and the number of problems caused by it, and generate a problem cause statistics table;

[0010] S2. Sort the generated problem cause statistics table using the sort clause in the structured query language, and perform quantity ranking in descending order, problem cause and problem quantity field query in combination with the preset variables and ranking formula, to generate a problem cause statistics table with a quantity ranking field in descending order;

[0011] S3, presetting the problem cause statistics table with the quantity descending ranking field as two sets of identical data, using the selection standard clause specified in the structured query language and the aggregation function in the structured query language, accumulating the number of problems one by one, and generating a cumulative result of the number of problems;

[0012] S4. Generate the total number of problems using the counting function in the structured query language, divide the cumulative result of the number of problems by the total number of problems generated, and perform a query on all fields and the cumulative proportion of the number of problems on one of the preset sets of data to generate a problem cause statistics table with a descending ranking of the number of problems and a cumulative proportion of the number of problems;

[0013] S5. According to the Pareto principle, the key reason fields are generated by using the logical judgment statements in the structured query language;

[0014] S6. Based on the project screening conditions, the problem data is screened into ongoing project data and historical project data, and steps S1 to S5 are executed on the ongoing project data and the historical project data to generate a Pareto chart data statistical table of the ongoing projects and the historical projects;

[0015] S7. Based on the keyword return matching statement in the structured query language, the statistical results of the Pareto chart data of the ongoing project and the historical project are associated according to the problem cause field, and combined with the logical judgment statement in the structured query language, the attention field of the cause of the problem of the ongoing project is generated according to the Pareto data of the historical project.

[0016] Further, using the data set partitioning statement in the structured query language and the counting function in the structured query language to count each problem cause and the number of problems caused by it, generating a problem cause statistics table includes the following steps:

[0017] S11. Preset a quality problem data table structure, wherein the quality problem data table structure includes a problem cause field and a problem description field;

[0018] S12. Use a data set partitioning statement in the structured query language for the problem cause field, and execute a counting function in the structured query language for the problem description field, set the execution result as the problem quantity field of the problem cause, and generate a problem cause statistics table for the quality problem data.

[0019] Further, the generated problem cause statistics table is sorted using the sort clause in the structured query language, and the preset variables and ranking formula are combined to perform the quantity descending ranking, problem cause and problem quantity field query, and the problem cause statistics table with the quantity descending ranking field is generated, including the following steps:

[0020] S21, using the sort clause in the structured query language to sort the generated problem cause statistics table according to the rule that the problem quantity field is in descending order and the problem cause field is in ascending order;

[0021] S22, setting the initial value of the preset variable, and using the keyword statement in the structured query language to set the number of questions in descending order as the variable formula;

[0022] S23. Based on the variable formula, perform a query on the fields of quantity ranking in descending order, problem cause and problem quantity, and generate a problem cause statistics table with a field of quantity ranking in descending order.

[0023] Furthermore, the variable formula is:

[0024] @id1:=@id1+1;

[0025] In the formula, @id1 represents a variable; := represents assignment; +1 represents the execution of an addition calculation.

[0026] Furthermore, the problem cause statistics table with the quantity descending ranking field is preset as two sets of identical data, and the number of problems is accumulated one by one by using the selection standard clause specified in the structured query language and the aggregation function in the structured query language. The generation of the cumulative result of the number of problems includes the following steps:

[0027] S31, dividing the problem cause statistics table with the number of descending ranking fields into two groups of identical data, a and b;

[0028] S32, using the selection standard clause specified in the structured query language to filter out the data of group a whose quantity descending ranking field of group a is less than or equal to the quantity descending ranking field of group b;

[0029] S33. Use the aggregation function in the structured query language for the problem number field of group a data to generate a cumulative result of the number of problems for each reason in descending order, and use the cumulative result as the numerator of the cumulative proportion of the number of problems.

[0030] Further, the total number of problems is generated by using the counting function in the structured query language, the cumulative result of the number of problems is divided by the total number of generated problems, and all fields and the cumulative proportion of the number of problems are queried on one of the preset sets of data, and a problem cause statistics table with a descending ranking of the number and a cumulative proportion of the number of problems is generated, including the following steps:

[0031] S41, using the count function in the structured query language for the problem description field of the problem cause statistics table of the generated quality problem data to generate the total number of problems, and using the total number of problems as the denominator of the cumulative proportion of the number of problems;

[0032] S42, dividing the cumulative result of the number of questions by the total number of questions generated, and defining a field for the cumulative percentage of the number of questions;

[0033] S43. Query all fields and the cumulative percentage of problem quantity on group b data to generate a problem cause statistics table with descending ranking of quantity and cumulative percentage of problem quantity.

[0034] Further, according to the Pareto principle, generating the key reason field by using the logical judgment statement in the structured query language includes the following steps:

[0035] S51. According to the Pareto principle, use the logical judgment statement in the structured query language to determine the key cause field when the cumulative proportion of the number of problems is less than or equal to the threshold;

[0036] S52. Generate a key cause statistical table that complies with the Pareto principle to screen out the key causes of near-threshold problems.

[0037] Further, according to the Pareto principle, using the logical judgment statement in the structured query language to judge the key reason field when the cumulative proportion of the number of problems is less than or equal to the threshold includes the following steps:

[0038] S511. Based on the Pareto principle, when the cumulative proportion of the number of problems in descending order is less than or equal to the threshold, the key reason field is assigned a value of yes; otherwise, the key reason field is assigned a value of no;

[0039] S512. Query the cause of the problem, the number of problems, the cumulative proportion of the number of problems, and the key cause field, and output the screening results.

[0040] Further, based on the project screening conditions, the problem data is screened into ongoing project data and historical project data, and steps S1 to S5 are performed on the ongoing project data and the historical project data, and generating a Pareto chart data statistical table of the ongoing projects and the historical projects includes the following steps:

[0041] S61. Use report designer software to analyze the Pareto chart data of the quality problem table in the relational database, and divide the problem data into ongoing project data and historical project data based on project screening conditions;

[0042] S62, executing steps S1 to S5 for the ongoing project data and the historical project data respectively, and finally generating a Pareto chart data statistical table for the ongoing projects and the historical projects.

[0043] Further, based on the keyword return matching statement in the structured query language, the statistical results of the Pareto chart data of the ongoing project and the historical project are associated according to the problem cause field, and combined with the logical judgment statement in the structured query language, the attention field of the problem cause of the ongoing project is generated according to the Pareto data of the historical project, including the following steps:

[0044] S71, using a keyword in a structured query language to return a matching statement to associate the problem cause field of the Pareto chart data statistics of the ongoing project and the historical project;

[0045] S72. Based on the logical judgment statements in the structured query language, according to the comparison of the key cause field values ​​of the problem causes in the ongoing project data and the historical project data, generate the ongoing project problem cause attention field based on the historical project Pareto chart data statistics.

[0046] Further, based on the logical judgment statement in the structured query language, according to the comparison of the key cause field values ​​of the problem causes in the ongoing project data and the historical project data, generating the ongoing project problem cause attention field based on the historical project Pareto chart data statistics includes the following steps:

[0047] S721. If the values ​​of the key cause fields of the ongoing project and the historical project are both yes, the problem cause attention field is assigned a value of key attention;

[0048] S722. If the value of the key reason field of the ongoing project is yes and the value of the key reason field of the historical project is no, the value of the problem cause attention field is set to strengthen attention;

[0049] S723. If the value of the key reason field of the ongoing project is No, and the value of the key reason field of the historical project is Yes, the value of the Problem Cause Attention Field is set to Keep Attention;

[0050] S724. If the key cause field value of the ongoing project and the key cause field value of the historical project are not as described in step S721, step S722 and step S723, the problem cause attention field is assigned a value of general attention, and the ongoing project problem cause attention field is generated based on the historical project Pareto chart data statistics.

[0051] According to another aspect of the present invention, a Pareto chart data analysis system based on structured query language is also provided, and the Pareto chart data analysis system comprises:

[0052] A data statistics module is used to use the data set partitioning statement in the structured query language and the counting function in the structured query language to count the cause of each problem and the number of problems it causes, and generate a problem cause statistics table;

[0053] The data sorting module is used to sort the generated problem cause statistics table using the sorting clause in the structured query language, and to perform the descending ranking of quantity, the query of the problem cause and the problem quantity field in combination with the preset variables and the ranking formula, so as to generate the problem cause statistics table with the descending ranking field of quantity;

[0054] The cumulative calculation module is used to preset the problem cause statistics table with the quantity descending ranking field into two sets of identical data, and use the selection standard clause specified in the structured query language and the aggregation function in the structured query language to accumulate the number of problems one by one to generate a cumulative result of the number of problems;

[0055] The cumulative proportion calculation module is used to generate the total number of problems using the counting function in the structured query language, divide the cumulative result of the number of problems by the total number of problems generated, and perform a query on all fields and the cumulative proportion of the number of problems on one of the preset sets of data to generate a problem cause statistics table with a descending ranking of the number and a cumulative proportion of the number of problems field;

[0056] The key reason marking module is used to generate the key reason field by using the logical judgment statement in the structured query language according to the Pareto principle;

[0057] A Pareto chart data generation module is used to filter the problem data into ongoing project data and historical project data based on the project screening conditions, and perform a Pareto chart analysis process on the ongoing project data and the historical project data to generate a Pareto chart data statistical table of the ongoing project and the historical project;

[0058] The Pareto chart data analysis module is used to return matching statements based on keywords in the structured query language, associate the Pareto chart data statistics of ongoing projects and historical projects according to the problem cause field, and combine the logical judgment statements in the structured query language to generate the attention field of the cause of the problem of the ongoing project based on the Pareto data of the historical project.

[0059] The beneficial effects of the present invention are:

[0060] 1. The present invention generates problem cause statistics data required for drawing a Pareto chart, including the descending ranking of quantity, the cumulative proportion of the number of problems, and the key cause fields, by using SQL statements supported by all versions of various databases for the quality problem data composed of the problem cause and the problem description. At the same time, in the Pareto chart data of the ongoing project, the problem cause field is associated with the historical project Pareto chart data, and the problem cause attention field is defined by comparing the ongoing and historical key cause fields, so as to realize the attention analysis of the problem causes of the ongoing project based on the historical data.

[0061] 2. The present invention facilitates project management by directly drawing a Pareto chart consisting of a statistical bar chart of problem causes and a line chart of the cumulative proportion of the number of problems through the generated data results, and by comparing the key causes of ongoing and historical projects, it realizes the attention analysis of the cause of problems in ongoing projects, effectively focusing on the key causes that produce the largest number of problems, thereby solving the main problems. The method has good instruction versatility and version compatibility, and meets the high cohesion and low coupling requirements of the code. BRIEF DESCRIPTION OF THE DRAWINGS

[0062] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0063] Figure 1 is a flow chart of a Pareto chart data analysis method based on structured query language according to an embodiment of the present invention;

[0064] Figure 2 is a principle block diagram of a Pareto chart data analysis system based on structured query language according to an embodiment of the present invention;

[0065] Figure 3 is a screenshot of quality problem data according to an embodiment of the present invention;

[0066] Figure 4 is a screenshot of problem cause statistics according to an embodiment of the present invention;

[0067] Figure 5 This is a screenshot of the descending ranking of quantity according to an embodiment of the present invention;

[0068] Figure 6 is a screenshot showing the number of questions accumulated one by one in descending order according to an embodiment of the present invention;

[0069] Figure 7 is a screenshot of the total number of questions according to an embodiment of the present invention;

[0070] Figure 8 This is a screenshot of the cumulative proportion of the number of questions accumulated one by one in descending order according to an embodiment of the present invention;

[0071] Fig. 9 It is a screenshot of the key reasons according to an embodiment of the present invention;

[0072] Fig.10 is a statistical screenshot of the Pareto chart data of an ongoing project according to an embodiment of the present invention;

[0073] Fig.11 is a statistical screenshot of the Pareto chart data of historical projects according to an embodiment of the present invention;

[0074] Fig.12 It is a screenshot of the cause of problem attention of an ongoing project based on historical projects according to an embodiment of the present invention.

[0075] In the figure:

[0076] 1. Data statistics module; 2. Data sorting module; 3. Cumulative calculation module; 4. Cumulative proportion calculation module; 5. Key cause marking module; 6. Pareto chart data generation module; 7. Pareto chart data analysis module. DETAILED DESCRIPTION

[0077] The technical solutions in the embodiments of the present invention will be described clearly and completely below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments.

[0078] In the description of the present invention, unless otherwise specified, the meaning of "plurality" is two or more. In addition, the terms "first", "second", "third", etc. are only used for descriptive purposes and cannot be understood as indicating or implying relative importance.

[0079] According to an embodiment of the present invention, a Pareto chart data analysis method and system based on structured query language are provided.

[0080] The present invention is further described with reference to the accompanying drawings and specific embodiments. Figure 1 As shown, according to the Pareto chart data analysis method based on structured query language according to an embodiment of the present invention, the Pareto chart data analysis method includes the following steps:

[0081] S1. Using a data set partitioning statement in structured query language (i.e., SQL group by statement) and a counting function in structured query language (i.e., SQL count function), count each cause of the problem and the number of problems caused by it, and generate a problem cause statistics table;

[0082] It is necessary to explain that the Pareto chart data analysis of the quality problem table in the MY SQL database (i.e., relational database management system) is implemented using the report designer software. The quality problem data table is defined to consist of the problem cause and problem description fields. The SQL group by statement is used for the problem cause field of the data table, and the SQLcount function is executed on the problem description field, which is defined as the problem cause field. The problem cause statistics of the quality problem data are generated, that is, each problem cause and the number of problems it causes. Figure 3 As shown, the SQL statement executed is as follows:

[0083] SELECT proCATEGORY as problem reason, proDetails as problem description FROM `qa_problem` ORDER BY proCATEGORY desc

[0084] like Figure 4 As shown, the SQL statement executed is as follows:

[0085] SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a group by a.proCATEGORY

[0086] S2. Sort the generated problem cause statistics table using the sorting clause in the structured query language (i.e., SQL order by clause), and perform quantity descending ranking, problem cause, and problem quantity field query in combination with the preset variables and ranking formula to generate a problem cause statistics table with a quantity descending ranking field;

[0087] It is necessary to explain that the Pareto chart data analysis of the quality problem table in the MY SQL database is realized by using the report designer software. The SQL as statement is used to define the problem quantity descending ranking field as the variable formula @id1:=@id1+1, where the variable @id1:=0; the SQL order by clause is used to sort the problem cause statistics table in descending order of the problem quantity field and ascending order of the problem cause field, and to perform the query of the quantity descending ranking, problem cause, and problem quantity fields, and generate the problem classification statistics with the quantity descending ranking field, that is, to realize the problem cause bar chart data of the Pareto chart.

[0088] like Figure 5 As shown, the SQL statement executed is as follows:

[0089] select(@id1:=@id1+1)as quantity descending ranking, x.problem cause, x.number of problems

[0090] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a

[0091] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0092] order by x. Number of problems desc, x. Reason for the problem asc

[0093] S3, presetting the problem cause statistics table with the quantity descending ranking field as two sets of identical data, using the selection standard clause specified in the structured query language (i.e., the SQL where clause) and the aggregation function in the structured query language (i.e., the SQL sum function), accumulating the number of problems one by one to generate a cumulative result of the number of problems;

[0094] S4. Use the count function in the structured query language (i.e., the SQL count function) to generate the total number of problems, divide the cumulative result of the number of problems by the total number of problems generated, and perform a query on all fields and the cumulative proportion of the number of problems on one of the preset sets of data to generate a problem cause statistics table with a descending ranking of the number and a cumulative proportion of the number of problems;

[0095] S5. According to the Pareto principle, the key reason field is generated by using the logical judgment statement in the structured query language (i.e., SQL case statement);

[0096] S6. Based on the project screening conditions, the problem data is screened into ongoing project data and historical project data, and steps S1 to S5 are executed on the ongoing project data and the historical project data to generate a Pareto chart data statistical table of the ongoing projects and the historical projects;

[0097] S7. Based on the keyword return matching statement in the structured query language (i.e., SQL LEFT JOIN statement), the Pareto chart data statistical results of the ongoing project and the historical project are associated according to the problem cause field, and combined with the logical judgment statement in the structured query language (i.e., SQL case statement), the attention field of the problem cause of the ongoing project is generated according to the Pareto data of the historical project.

[0098] In this optional embodiment, using the data set partitioning statement in the structured query language and the counting function in the structured query language to count each problem cause and the number of problems caused by it, generating a problem cause statistics table includes the following steps:

[0099] S11. Preset a quality problem data table structure, wherein the quality problem data table structure includes a problem cause field and a problem description field;

[0100] S12. Use a data set partitioning statement in the structured query language for the problem cause field, and execute a counting function in the structured query language for the problem description field, set the execution result as the problem quantity field of the problem cause, and generate a problem cause statistics table for the quality problem data.

[0101] In this optional embodiment, the generated problem cause statistics table is sorted using the sort clause in the structured query language, and the preset variables and ranking formula are combined to perform the quantity descending ranking, the problem cause and the problem quantity field query, and the problem cause statistics table with the quantity descending ranking field is generated, including the following steps:

[0102] S21, using the sort clause in the structured query language to sort the generated problem cause statistics table according to the rule that the problem quantity field is in descending order and the problem cause field is in ascending order;

[0103] S22, setting the initial value of the preset variable, and using the keyword statement in the structured query language to set the number of questions in descending order as the variable formula;

[0104] S23. Based on the variable formula, perform a query on the fields of quantity ranking in descending order, problem cause and problem quantity, and generate a problem cause statistics table with a field of quantity ranking in descending order.

[0105] In this alternative embodiment, the variable formula is:

[0106] @id1:=@id1+1;

[0107] In the formula, @id1 represents a variable; := represents assignment; +1 represents the execution of an addition calculation.

[0108] In this optional embodiment, the problem cause statistics table with the quantity descending ranking field is preset as two sets of identical data, and the number of problems is accumulated one by one by using the selection standard clause specified in the structured query language and the aggregation function in the structured query language. The generation of the cumulative result of the number of problems includes the following steps:

[0109] S31, dividing the problem cause statistics table with the number of descending ranking fields into two groups of identical data, a and b;

[0110] S32, using the selection standard clause specified in the structured query language (i.e., the SQL where clause) to filter out the data of group a whose quantity descending ranking field of group a is less than or equal to the quantity descending ranking field of group b;

[0111] S33. Use the aggregation function in the structured query language for the problem number field of group a data to generate a cumulative result of the number of problems for each reason in descending order, and use the cumulative result as the numerator of the cumulative proportion of the number of problems.

[0112] It is necessary to explain that the Pareto chart data analysis of the quality problem table in the MY SQL database is implemented using the report designer software. The problem cause statistics table with the quantity descending ranking field is defined as two groups of the same data, a and b, using the SQL as statement. The SQL where clause is used to filter the data of group a where the quantity descending ranking field of group a is ≤ the quantity descending ranking field of group b, and the SQL sum function is used for the problem quantity field of group a to generate the cumulative result of the number of problems accumulated one by one in descending order for each reason, and the result is defined as the numerator of the cumulative proportion of the number of problems.

[0113] like Figure 6 As shown, the SQL statement executed is as follows:

[0114] select b.*,(select sum(a.Number of problems)from(select(@id2:=@id2+1)as number descending order,x.Cause of problem,x.Number of problems

[0115] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a

[0116] group by a.proCATEGORY)as x,(Select(@id2:=0))as z

[0117] order by x.number of problems desc, x.reason of the problem asc)as a where a.number of descending ranking <= b.number of descending ranking)as cumulative number of problems from

[0118] (select(@id1:=@id1+1)as quantity descending ranking, x.problem cause, x.number of problems

[0119] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a

[0120] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0121] order by x.number of problems desc, x.reason of the problem asc)as b

[0122] In this optional embodiment, the total number of questions is generated using the SQL count function, the cumulative result of the number of questions is divided by the total number of questions generated, and a query of all fields and the cumulative proportion of the number of questions is performed on one of the preset sets of data. The problem cause statistics table with the descending ranking of the number and the cumulative proportion of the number of questions is generated, including the following steps:

[0123] S41, using the count function in the structured query language for the problem description field of the problem cause statistics table of the generated quality problem data to generate the total number of problems, and using the total number of problems as the denominator of the cumulative proportion of the number of problems;

[0124] It should be explained that the Pareto chart data analysis of the quality problem table in the MY SQL database is implemented using the report designer software. The SQL count function is used on the problem description field of the quality problem data table defined in step S1 above to generate the total number of problems, which is defined as the denominator of the cumulative proportion of the number of problems.

[0125] like Figure 7 As shown, the SQL statement executed is as follows:

[0126] select count(proDetails) as total number of problems from `qa_problem`

[0127] S42, dividing the cumulative result of the number of questions by the total number of questions generated, and defining a field for the cumulative percentage of the number of questions;

[0128] S43. Query all fields and the cumulative percentage of problem quantity on group b data to generate a problem cause statistics table with descending ranking of quantity and cumulative percentage of problem quantity.

[0129] It should be explained that the Pareto chart data analysis of the quality problem table in the MY SQL database is realized using the report designer software. The SQL as statement is used to define the cumulative percentage of the number of problems as the SQL statement of the result of the above step S3 divided by the SQL statement of the result of step S4, and all fields and the cumulative percentage of the number of problems are queried for the b group data defined in step S3, and the problem cause statistics with the descending ranking of the number and the cumulative percentage of the number of problems are generated, that is, all the data of the problem cause statistics bar chart and the cumulative percentage of the number of problems of the Pareto chart are realized, including: the descending ranking of the number (generated in step S2), the cause of the problem (original data), the number of problems (generated in step S1), and the cumulative percentage of the number of problems (generated in step S4).

[0130] like Figure 8 As shown, the SQL statement executed is as follows:

[0131] select b.*,(select sum(a.Number of problems)from(select(@id2:=@id2+1)as number descending order,x.Cause of problem,x.Number of problems

[0132] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a

[0133] group by a.proCATEGORY)as x,(Select(@id2:=0))as z

[0134] order by x.problem number desc, x.problem reason asc)as a where a.number of descending ranking <= b.number of descending ranking) / (select count(proDetails)from`qa_problem`)as cumulative proportion of problem number from

[0135] (select(@id1:=@id1+1)as quantity descending ranking, x.problem cause, x.number of problems

[0136] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a

[0137] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0138] order by x.number of problems desc, x.reason of the problem asc)as b

[0139] In this optional embodiment, according to the Pareto principle, generating the key reason field by using the logical judgment statement in the structured query language includes the following steps:

[0140] S51. According to the Pareto principle, use the logical judgment statement in the structured query language to determine the key cause field when the cumulative proportion of the number of problems is less than or equal to the threshold;

[0141] S52. Generate a key cause statistical table that complies with the Pareto principle to screen out the key causes of near-threshold problems.

[0142] It should be explained that the report designer software is used to implement the Pareto chart data analysis of the quality problem table in the MY SQL database. The Pareto principle is also called the 80 / 20 principle, which means that 80% of the problems are caused by 20% of the reasons. The Pareto chart is mainly used in project management to find out the key causes of most problems and to solve most problems. According to the Pareto principle, when the cumulative proportion of the number of problems in descending order is ≤0.8, the corresponding cause of the problem has caused nearly 80% of the problems. Therefore, the key cause field is defined for the cumulative proportion field of the number of problems in the result of step S4 above using the SQL case statement. When it is judged that the cumulative proportion field of the number of problems is ≤0.8, the key cause field value is assigned to "yes", and the value of the field is assigned to "no" in other cases, so as to facilitate the direct screening of the key causes of nearly 80% of the problems.

[0143] like Fig. 9 As shown, the SQL statement executed is as follows:

[0144] select c.*,(case when c. cumulative problem number ratio>0.8then'no'else'yes'end)as key reason from

[0145] (select b.*,(select sum(a.Number of problems)from(select(@id2:=@id2+1)as rank in descending order of quantity,x.Cause of problem,x.Number of problems

[0146] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a

[0147] group by a.proCATEGORY)as x,(Select(@id2:=0))as z

[0148] order by x.problem number desc, x.problem reason asc)as a where a.number of descending ranking <= b.number of descending ranking) / (select count(proDetails)from`qa_problem`)as cumulative proportion of problem number from

[0149] (select(@id1:=@id1+1)as quantity descending ranking, x.problem cause, x.number of problems

[0150] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM `qa_problem` as a

[0151] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0152] order by x. Number of problems desc, x. Reason for the problem asc)as b)as c

[0153] In this optional embodiment, according to the Pareto principle, using a logical judgment statement in a structured query language to judge the key reason field when the cumulative proportion of the number of problems is less than or equal to a threshold includes the following steps:

[0154] S511. Based on the Pareto principle, when the cumulative proportion of the number of problems in descending order is less than or equal to the threshold, the key reason field is assigned a value of yes; otherwise, the key reason field is assigned a value of no;

[0155] S512. Query the cause of the problem, the number of problems, the cumulative proportion of the number of problems, and the key cause field, and output the screening results.

[0156] In this optional embodiment, based on the project screening conditions, the problem data is screened into ongoing project data and historical project data, and steps S1 to S5 are performed on the ongoing project data and the historical project data. Generating a Pareto chart data statistical table of the ongoing project and the historical project includes the following steps:

[0157] S61. Use report designer software to analyze the Pareto chart data of the quality problem table in the relational database, and divide the problem data into ongoing project data and historical project data based on project screening conditions;

[0158] S62, executing steps S1 to S5 for the ongoing project data and the historical project data respectively, and finally generating a Pareto chart data statistical table for the ongoing projects and the historical projects.

[0159] It should be explained that the Pareto chart data analysis of the quality problem table in the MY SQL database is implemented using the report designer software. The SQL where clause is used to filter the ongoing project and historical project data, and the above steps S1 to S7 are performed on the filtered data to generate Pareto chart data statistics for the ongoing project and historical project respectively.

[0160] like Fig.10 and Fig.11 As shown in the figure, when WHERE projectID = 136, it is the ongoing project data; when WHERE projectID! = 136, it is the historical project data. The SQL statements are as follows:

[0161] ① Pareto chart data statistics of ongoing projects

[0162] select c.*,(case when c. cumulative problem number ratio>0.8then'no'else'yes'end)as key reason from

[0163] (select b.*,(select sum(a.Number of problems)from(select(@id2:=@id2+1)as rank in descending order of quantity,x.Cause of problem,x.Number of problems

[0164] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID = 136) as a

[0165] group by a.proCATEGORY)as x,(Select(@id2:=0))as z

[0166] order by x.problem quantity desc, x.problem reason asc)as a where a.number of descending ranking <= b.number of descending ranking) / (select count(a.proDetails)from(SELECT*from`qa_problem`whereprojectID=136)as a)as cumulative proportion of problem quantity from

[0167] (select project ID, (@id1:=@id1+1) as quantity descending ranking, x. cause of problem, x. number of problems

[0168] from(SELECT a.projectID as project ID, a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID=136) as a

[0169] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0170] order by x. Number of problems desc, x. Reason for the problem asc)as b)as c

[0171] ② Historical project Pareto chart data statistics

[0172] select c.*,(case when c. cumulative problem number ratio>0.8then'no'else'yes'end)as key reason from

[0173] (select b.*,(select sum(a.Number of problems)from(select(@id2:=@id2+1)as rank in descending order of quantity,x.Cause of problem,x.Number of problems

[0174] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID!=136) as a

[0175] group by a.proCATEGORY)as x,(Select(@id2:=0))as z

[0176] order by x.problem quantity desc, x.problem reason asc)as a where a.number of descending ranking <= b.number of descending ranking) / (select count(a.proDetails)from(SELECT*from`qa_problem`whereprojectID!=136)as a)as cumulative proportion of problem quantity from

[0177] (select(@id1:=@id1+1)as quantity descending ranking, x.problem cause, x.number of problems

[0178] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID!=136) as a

[0179] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0180] order by x. Number of problems desc, x. Reason for the problem asc)as b)as c

[0181] In this optional embodiment, based on the keyword return matching statement in the structured query language, the statistical results of the Pareto chart data of the ongoing project and the historical project are associated according to the problem cause field, and combined with the logical judgment statement in the structured query language, the attention degree field of the problem cause of the ongoing project is generated according to the Pareto data of the historical project, including the following steps:

[0182] S71, using a keyword in a structured query language to return a matching statement to associate the problem cause field of the Pareto chart data statistics of the ongoing project and the historical project;

[0183] S72. Based on the logical judgment statements in the structured query language, according to the comparison of the key cause field values ​​of the problem causes in the ongoing project data and the historical project data, generate the ongoing project problem cause attention field based on the historical project Pareto chart data statistics.

[0184] It should be explained that the Pareto chart data analysis of the quality problem table in the MY SQL database is implemented using the report designer software. Use the SQL LEFT JOIN statement to link the problem cause fields of the ongoing project and historical project Pareto chart data statistics in the result of step 8, and use the SQL case statement. According to the comparison of the key cause field values ​​of the problem causes in these two sets of data, when the key cause field values ​​of the ongoing project and the historical project are both "yes", the problem cause attention field is assigned the value of "focus on"; when the key cause field value of the ongoing project is "yes" and the key cause field value of the historical project is "no", the problem cause attention field is assigned the value of "strengthen attention"; when the key cause field value of the ongoing project is "no" and the key cause field value of the historical project is "yes", the problem cause attention field is assigned the value of "maintain attention"; in other cases, the field is assigned the value of "general attention", and the problem cause attention field of the ongoing project based on the Pareto chart data statistics of the historical project is generated.

[0185] like Fig.12 As shown, the SQL statement executed is as follows:

[0186] select a.*,b.key reason as historical key reason,(case when a.key reason = 'yes' and b.key reason = 'yes' then 'focus on' when a.key reason = 'yes' and b.key reason = 'no' then 'strengthen attention' when a.key reason = 'no' and b.key reason = 'yes' then 'maintain attention' else 'general attention' end)as problem cause attention from

[0187] (select c.*,(case when c. cumulative problem number ratio>0.8then'no'else'yes'end)as key reason from

[0188] (select b.*,(select sum(a.Number of problems)from(select(@id2:=@id2+1)as rank in descending order of quantity,x.Cause of problem,x.Number of problems

[0189] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID = 136) as a

[0190] group by a.proCATEGORY)as x,(Select(@id2:=0))as z

[0191] order by x.problem quantity desc, x.problem reason asc)as a where a.number of descending ranking <= b.number of descending ranking) / (select count(a.proDetails)from(SELECT*from`qa_problem`whereprojectID=136)as a)as cumulative proportion of problem quantity from

[0192] (select project ID, (@id1:=@id1+1) as quantity descending ranking, x. problem reason, x. number of problems

[0193] from(SELECT a.projectID as project ID, a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID=136) as a

[0194] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0195] order by x. Number of problems desc, x. Reason for the problem asc)as b)as c)a

[0196] left join

[0197] (select c.*,(case when c. cumulative proportion of problems>0.8then'no'else'yes'end)as key reason from

[0198] (select b.*,(select sum(a.Number of problems)from(select(@id2:=@id2+1)as rank in descending order of quantity,x.Cause of problem,x.Number of problems

[0199] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID!=136) as a

[0200] group by a.proCATEGORY)as x,(Select(@id2:=0))as z

[0201] order by x.problem quantity desc, x.problem reason asc)as a where a.number of descending ranking <= b.number of descending ranking) / (select count(a.proDetails)from(SELECT*from`qa_problem`whereprojectID!=136)as a)as cumulative proportion of problem quantity from

[0202] (select(@id1:=@id1+1)as quantity descending ranking, x.problem cause, x.number of problems

[0203] from(SELECT a.proCATEGORY as problem reason, count(a.proDetails) as problem number FROM(SELECT * from `qa_problem` where projectID!=136) as a

[0204] group by a.proCATEGORY)as x,(Select(@id1:=0))as z

[0205] order by x. Number of problems desc, x. Reasons for the problems asc)as b)as c)b

[0206] on a. Cause of the problem = b. Cause of the problem

[0207] In this optional embodiment, based on the logical judgment statement in the structured query language, according to the comparison of the key cause field values ​​of the problem causes in the ongoing project data and the historical project data, generating the ongoing project problem cause attention field based on the historical project Pareto chart data statistics includes the following steps:

[0208] S721. If the values ​​of the key cause fields of the ongoing project and the historical project are both yes, the problem cause attention field is assigned a value of key attention;

[0209] S722. If the value of the key reason field of the ongoing project is yes and the value of the key reason field of the historical project is no, the value of the problem cause attention field is set to strengthen attention;

[0210] S723. If the value of the key reason field of the ongoing project is No, and the value of the key reason field of the historical project is Yes, the value of the Problem Cause Attention Field is set to Keep Attention;

[0211] S724. If the key cause field value of the ongoing project and the key cause field value of the historical project are not as described in step S721, step S722 and step S723, the problem cause attention field is assigned a value of general attention, and the ongoing project problem cause attention field is generated based on the historical project Pareto chart data statistics.

[0212] According to another embodiment of the present invention, Figure 2 As shown, a Pareto chart data analysis system based on structured query language is also provided, and the Pareto chart data analysis system includes:

[0213] The data statistics module 1 is used to count each problem cause and the number of problems caused by it by using the data set partitioning statement in the structured query language and the counting function in the structured query language, and generate a problem cause statistics table;

[0214] Data sorting module 2, used to sort the generated problem cause statistics table using the sorting clause in the structured query language, and perform quantity descending ranking, problem cause and problem quantity field query in combination with preset variables and ranking formulas, to generate a problem cause statistics table with a quantity descending ranking field;

[0215] The cumulative calculation module 3 is used to preset the problem cause statistics table with the quantity descending ranking field as two sets of identical data, and use the selection standard clause specified in the structured query language and the aggregation function in the structured query language to accumulate the number of problems one by one to generate a cumulative result of the number of problems;

[0216] The cumulative proportion calculation module 4 is used to generate the total number of problems by using the counting function in the structured query language, divide the cumulative result of the number of problems by the total number of problems generated, and perform a query on all fields and the cumulative proportion field of the number of problems on one of the preset sets of data to generate a problem cause statistics table with a descending ranking of the number and a cumulative proportion field of the number of problems;

[0217] The key reason marking module 5 is used to generate the key reason field by using the logical judgment statement in the structured query language according to the Pareto principle;

[0218] The Pareto chart data generating module 6 is used to filter the problem data into ongoing project data and historical project data based on the project screening conditions, and perform a Pareto chart analysis process on the ongoing project data and the historical project data (i.e., perform steps S1 to S5), and generate a Pareto chart data statistical table of the ongoing project and the historical project;

[0219] The Pareto chart data analysis module 7 is used to return matching statements based on keywords in the structured query language, associate the Pareto chart data statistical results of ongoing projects and historical projects according to the problem cause field, and combine the logical judgment statements in the structured query language to generate the attention field of the cause of the problem of the ongoing project based on the Pareto data of the historical project.

[0220] In summary, with the help of the above technical solution of the present invention, the present invention generates the problem cause statistics data required for drawing the Pareto chart, including the descending ranking of quantity, the cumulative proportion of the number of problems, and the key cause fields, by using SQL statements supported by various versions of various databases for the quality problem data composed of the problem cause and the problem description. At the same time, in the Pareto chart data of the ongoing project, the problem cause field is associated with the Pareto chart data of the historical project, and the problem cause attention field is defined by comparing the ongoing and historical key cause fields, so as to realize the attention analysis of the problem causes of the ongoing project according to the historical data. The present invention is convenient in project management, and directly draws a Pareto chart composed of a problem cause statistical bar chart and a problem number cumulative proportion line chart through the generated data results, and realizes the attention analysis of the problem causes of the ongoing project by comparing the key causes of the ongoing and historical projects, and effectively pays attention to the key causes of the largest number of problems, thereby solving the main problem. The method has good instruction versatility and version compatibility, and meets the high cohesion and low coupling requirements of the code.

[0221] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A Pareto chart data analysis method based on structured query language, characterized in that: The Pareto chart data analysis method includes the following steps: S1. Using the data set partitioning statement in the structured query language and the counting function in the structured query language, count each problem cause and the number of problems caused by it, and generate a problem cause statistics table; S2. Sort the generated problem cause statistics table using the sort clause in the structured query language, and perform quantity ranking in descending order, problem cause and problem quantity field query in combination with the preset variables and ranking formula, to generate a problem cause statistics table with a quantity ranking field in descending order; S3, presetting the problem cause statistics table with the quantity descending ranking field as two sets of identical data, using the selection standard clause specified in the structured query language and the aggregation function in the structured query language, accumulating the number of problems one by one, and generating a cumulative result of the number of problems; S4. Generate the total number of problems using the counting function in the structured query language, divide the cumulative result of the number of problems by the total number of problems generated, and perform a query on all fields and the cumulative proportion of the number of problems on one of the preset sets of data to generate a problem cause statistics table with a descending ranking of the number of problems and a cumulative proportion of the number of problems; S5. According to the Pareto principle, the key reason fields are generated by using the logical judgment statements in the structured query language; S6. Based on the project screening conditions, the problem data is screened into ongoing project data and historical project data, and steps S1 to S5 are executed on the ongoing project data and the historical project data to generate a Pareto chart data statistical table of the ongoing projects and the historical projects; S7. Based on the keyword return matching statement in the structured query language, the statistical results of the Pareto chart data of the ongoing project and the historical project are associated according to the problem cause field, and combined with the logical judgment statement in the structured query language, the attention field of the cause of the problem of the ongoing project is generated according to the Pareto data of the historical project.

2. The Pareto chart data analysis method based on structured query language according to claim 1, characterized in that: The method of using the data set partitioning statement in the structured query language and the counting function in the structured query language to count each problem cause and the number of problems caused by it, and generating a problem cause statistics table comprises the following steps: S11. Preset a quality problem data table structure, wherein the quality problem data table structure includes a problem cause field and a problem description field; S12. Use a data set partitioning statement in the structured query language for the problem cause field, and execute a counting function in the structured query language for the problem description field, set the execution result as the problem quantity field of the problem cause, and generate a problem cause statistics table for the quality problem data.

3. The Pareto chart data analysis method based on structured query language according to claim 1, characterized in that: The method of using the sorting clause in the structured query language to sort the generated problem cause statistics table, combining the preset variables and the ranking formula, performing the quantity descending ranking, the problem cause and the problem quantity field query, and generating the problem cause statistics table with the quantity descending ranking field includes the following steps: S21, using the sort clause in the structured query language to sort the generated problem cause statistics table according to the rule that the problem quantity field is in descending order and the problem cause field is in ascending order; S22, setting the initial value of the preset variable, and using the keyword statement in the structured query language to set the number of questions in descending order as the variable formula; S23. Based on the variable formula, perform a query on the fields of quantity ranking in descending order, problem cause and problem quantity, and generate a problem cause statistics table with a field of quantity ranking in descending order.

4. The Pareto chart data analysis method based on structured query language according to claim 3, characterized in that: The variable formula is: @id1:=@id1+1; In the formula, @id1 represents a variable; := represents assignment; +1 represents the execution of an addition calculation.

5. The Pareto chart data analysis method based on structured query language according to claim 1, characterized in that: The problem cause statistics table with the number of descending ranking fields is preset as two sets of identical data, and the number of problems is accumulated one by one by using the selection standard clause specified in the structured query language and the aggregation function in the structured query language to generate the cumulative result of the number of problems, including the following steps: S31, dividing the problem cause statistics table with the number of descending ranking fields into two groups of identical data, a and b; S32, using the selection standard clause specified in the structured query language to filter out the data of group a whose quantity descending ranking field of group a is less than or equal to the quantity descending ranking field of group b; S33. Use the aggregation function in the structured query language for the problem number field of group a data to generate a cumulative result of the number of problems for each reason in descending order, and use the cumulative result as the numerator of the cumulative proportion of the number of problems.

6. The Pareto chart data analysis method based on structured query language according to claim 1, characterized in that: The method of using the counting function in the structured query language to generate the total number of problems, dividing the cumulative result of the number of problems by the total number of problems generated, and performing a query on all fields and the cumulative proportion of the number of problems on one of the preset sets of data to generate a problem cause statistics table with a descending ranking of the number and the cumulative proportion of the number of problems includes the following steps: S41, using the count function in the structured query language for the problem description field of the problem cause statistics table of the generated quality problem data to generate the total number of problems, and using the total number of problems as the denominator of the cumulative proportion of the number of problems; S42, dividing the cumulative result of the number of questions by the total number of questions generated, and defining a field for the cumulative percentage of the number of questions; S43. Query all fields and the cumulative percentage of problem quantity on group b data to generate a problem cause statistics table with descending ranking of quantity and cumulative percentage of problem quantity.

7. The Pareto chart data analysis method based on structured query language according to claim 1, characterized in that: According to the Pareto principle, generating the key reason field by using the logical judgment statement in the structured query language includes the following steps: S51. According to the Pareto principle, use the logical judgment statement in the structured query language to determine the key cause field when the cumulative proportion of the number of problems is less than or equal to the threshold; S52. Generate a key cause statistical table that complies with the Pareto principle to screen out the key causes of near-threshold problems.

8. The Pareto chart data analysis method based on structured query language according to claim 7, characterized in that: According to the Pareto principle, using the logical judgment statement in the structured query language to judge the key reason field when the cumulative proportion of the number of problems is less than or equal to the threshold includes the following steps: S511. Based on the Pareto principle, when the cumulative proportion of the number of problems in descending order is less than or equal to the threshold, the key cause field is assigned a value of yes; otherwise, the key cause field is assigned a value of no; S512. Query the cause of the problem, the number of problems, the cumulative proportion of the number of problems, and the key cause field, and output the screening results.

9. The Pareto chart data analysis method based on structured query language according to claim 1, characterized in that: Based on the project screening conditions, the problem data is screened into ongoing project data and historical project data, and steps S1 to S5 are performed on the ongoing project data and the historical project data to generate a Pareto chart data statistical table of the ongoing projects and the historical projects, including the following steps: S61. Use report designer software to analyze the Pareto chart data of the quality problem table in the relational database, and divide the problem data into ongoing project data and historical project data based on project screening conditions; S62, executing steps S1 to S5 for the ongoing project data and the historical project data respectively, and finally generating a Pareto chart data statistical table of the ongoing projects and the historical projects.

10. The Pareto chart data analysis method based on structured query language according to claim 1, characterized in that: The method of returning matching statements based on keywords in the structured query language, associating the statistical results of the Pareto chart data of the ongoing project and the historical project according to the problem cause field, and combining the logical judgment statements in the structured query language to generate the attention degree field of the problem cause of the ongoing project according to the Pareto data of the historical project includes the following steps: S71, using a keyword in a structured query language to return a matching statement to associate the problem cause field of the Pareto chart data statistics of the ongoing project and the historical project; S72. Based on the logical judgment statements in the structured query language, according to the comparison of the key cause field values ​​of the problem causes in the ongoing project data and the historical project data, generate the ongoing project problem cause attention field based on the historical project Pareto chart data statistics.

11. The Pareto chart data analysis method based on structured query language according to claim 10, characterized in that: The method of generating the ongoing project problem cause attention field based on the historical project Pareto chart data statistics based on the logical judgment statement in the structured query language and the comparison of the key cause field values ​​of the problem causes in the ongoing project data and the historical project data comprises the following steps: S721. If the values ​​of the key cause fields of the ongoing project and the historical project are both yes, the problem cause attention field is assigned a value of key attention; S722. If the value of the key reason field of the ongoing project is yes and the value of the key reason field of the historical project is no, the value of the problem cause attention field is set to strengthen attention; S723. If the value of the key reason field of the ongoing project is No, and the value of the key reason field of the historical project is Yes, the value of the Problem Cause Attention Field is set to Keep Attention; S724. If the key cause field value of the ongoing project and the key cause field value of the historical project are not as described in step S721, step S722 and step S723, the problem cause attention field is assigned a value of general attention, and the ongoing project problem cause attention field is generated based on the historical project Pareto chart data statistics.

12. A Pareto chart data analysis system based on structured query language, used to implement the Pareto chart data analysis method based on structured query language according to any one of claims 1 to 11, characterized in that: The Pareto chart data analysis system includes: A data statistics module is used to use the data set partitioning statement in the structured query language and the counting function in the structured query language to count the cause of each problem and the number of problems it causes, and generate a problem cause statistics table; The data sorting module is used to sort the generated problem cause statistics table using the sorting clause in the structured query language, and to perform the descending ranking of quantity, the query of the problem cause and the problem quantity field in combination with the preset variables and the ranking formula, so as to generate the problem cause statistics table with the descending ranking field of quantity; The cumulative calculation module is used to preset the problem cause statistics table with the quantity descending ranking field into two sets of identical data, and use the selection standard clause specified in the structured query language and the aggregation function in the structured query language to accumulate the number of problems one by one to generate a cumulative result of the number of problems; The cumulative proportion calculation module is used to generate the total number of problems using the counting function in the structured query language, divide the cumulative result of the number of problems by the total number of problems generated, and perform a query on all fields and the cumulative proportion of the number of problems on one of the preset sets of data to generate a problem cause statistics table with a descending ranking of the number and a cumulative proportion of the number of problems field; The key reason marking module is used to generate the key reason field by using the logical judgment statement in the structured query language according to the Pareto principle; A Pareto chart data generation module is used to filter the problem data into ongoing project data and historical project data based on the project screening conditions, and perform a Pareto chart analysis process on the ongoing project data and the historical project data to generate a Pareto chart data statistical table of the ongoing project and the historical project; The Pareto chart data analysis module is used to return matching statements based on keywords in the structured query language, associate the Pareto chart data statistics of ongoing projects and historical projects according to the problem cause field, and combine the logical judgment statements in the structured query language to generate the attention field of the cause of the problem of the ongoing project based on the Pareto data of the historical project.

Images