A data statistics method, device and terminal equipment based on two-dimensional report conversion

By editing SQL statements and SUBTOTAL statements, statistical calculations are performed on one-dimensional reports and generating two-dimensional reports, solving the problem of low efficiency of two-dimensional table aggregation operations in the existing technology, and achieving efficient data statistics and analysis.

CN112115683BActive Publication Date: 2024-11-05深圳市汉云科技有限公司
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202011051902.X
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2020-09-29
Publication Date
2024-11-05
Estimated Expiration
2040-09-29

AI Technical Summary

Technical Problem

In the existing technology, when analyzing the test score data of students, one-dimensional tables need to be converted into two-dimensional tables, and then aggregation operations such as summing and averaging are performed, resulting in larger changes in the converted two-dimensional tables and many operation steps, which are more efficient. low.

Method used

By editing SQL statements and SUBTOTAL statements, statistical calculations are performed on fixed columns and columns that need to be converted in the initial one-dimensional report to generate a two-dimensional report, avoiding additional aggregation operations on the two-dimensional table, and simplifying operation steps.

Benefits of technology

It realizes the direct summing and amplification operations such as aggregation operations such as summing and averaging during the conversion of two-dimensional reports, avoiding changes to two-dimensional tables and improving the efficiency of data statistics and analysis.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN112115683B_ABST
    Figure CN112115683B_ABST
Patent Text Reader

Abstract

The present invention is applicable to the field of computer technology, and provides a data statistics method, device and terminal device based on two-dimensional report conversion, the method comprising: specifying fixed columns in an initial one-dimensional report by editing a first SQL statement, specifying columns to be converted in the initial one-dimensional report by editing a second SQL statement; performing statistical calculations on columns to be converted and fixed columns by editing a SUBTOTAL statement; executing the edited first SQL statement, second SQL statement and SUBTOTAL statement to convert the initial one-dimensional report into a two-dimensional report; performing group query based on the initial one-dimensional report to obtain a result set; traversing the result set according to the group result of the group query, and writing data in the two-dimensional report according to the table header, so as to re-count the initial performance information through the first statistical result, the second statistical result and the third statistical result. Through the present invention, the results of aggregation operations such as summation and average can be directly added to the converted two-dimensional report, so as to quickly count the initial performance information.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a data statistics method, device and terminal equipment based on two-dimensional report conversion. Background Art

[0002] In-depth analysis of student test scores is an effective basis for objectively characterizing and positioning student learning outcomes and teacher teaching effectiveness. The analysis method used by most universities is still based on data analysis of the score statistics table. In the process of making a score statistics table, first, a one-dimensional table is used to store the student's test data as the source data for data analysis, and the data processing is also convenient. Then, a two-dimensional table is used to display the student's test data in detail. In other words, in the process of making a score statistics table, a one-dimensional table is generally used to save the original test data as the source table, and then the one-dimensional table is converted into a two-dimensional table. Based on the two-dimensional table, the aggregation operation of multiple rows and columns of data in the source table is realized, and when it is necessary to analyze the student's test data from multiple different focuses, it will be generated according to different needs and focuses, thereby generating an on-demand two-dimensional report.

[0003] Currently, for databases, the following technologies are used to convert a one-dimensional table with independent attributes in each column into a two-dimensional table using SQL statements:

[0004] 1. Using SQL statements and Excel pivot tables

[0005] Use similar case when statements to group the rows that remain unchanged and the columns that need to perform aggregate operations such as sum and avg. In the case when conditions, simply splice the constraints on the columns into the conditions to obtain all the data of multiple conditions associated with each cell. Finally, paste the data into execl and process it using a pivot table.

[0006] 2. Only use SQL statements

[0007] The columns that need to be converted are converted by row-column conversion, and then grouped by the columns of the vertical axis, that is, the rows that remain unchanged, and then perform aggregation operations such as summation and average.

[0008] It can be seen that in the above-mentioned technology of converting a one-dimensional table into a two-dimensional table, it is necessary to perform aggregation operations such as summation and average after the conversion. The amount of student test score data is usually large, so when performing aggregation operations, it will cause major changes to the converted two-dimensional table and there will be more operation steps. Summary of the invention

[0009] The main purpose of the present invention is to propose a data statistics method, device and terminal equipment based on two-dimensional report conversion, so as to solve the problem in the prior art that when analyzing student test scores, it is necessary to convert a one-dimensional table into a two-dimensional table and then perform aggregation operations such as summation and averaging, which requires significant changes to the converted two-dimensional table and involves many operation steps.

[0010] To achieve the above-mentioned purpose, a first aspect of an embodiment of the present invention provides a data statistics method based on two-dimensional report conversion, comprising:

[0011] Get an initial one-dimensional report including initial performance information;

[0012] Specifying fixed columns in the initial one-dimensional report by editing a first SQL statement, and specifying columns to be converted in the initial one-dimensional report by editing a second SQL statement;

[0013] By editing the SUBTOTAL statement, statistical calculations are performed on the columns to be converted and the fixed columns;

[0014] Execute the edited first SQL statement, the second SQL statement and the SUBTOTAL statement to convert the initial one-dimensional report into a two-dimensional report;

[0015] In the process of converting the initial one-dimensional report into a two-dimensional report, according to the columns to be converted, N first-level headings and M second-level headings are obtained, wherein any first-level heading includes k second-level headings, M=N×k, N, M, k are positive integers, and a table header is made according to the first-level heading, the second-level heading and the fixed columns;

[0016] In the table header, the last column of the mth sub-heading represents the first statistical result of the first k-1 columns in the mth sub-heading, where k is the total number of columns of the mth sub-heading, and m is a positive integer less than or equal to M;

[0017] A data statistics column is included after the column where the N-th first-level title is located, and the data statistics column represents the second statistical result of the first k-1 columns of the second-level title belonging to the N-th first-level title;

[0018] The last row of the fixed column also includes a data statistics row, and the data statistics row represents the third statistical results of the columns where the M secondary headings are located;

[0019] Perform group query based on the initial one-dimensional report to obtain a result set;

[0020] The result set is traversed according to the grouping result of the grouping query, and data is written into the two-dimensional report according to the table header, so as to re-count the initial score information through the first statistical result, the second statistical result and the third statistical result.

[0021] In combination with the first aspect of the present invention, in a first implementation of the present invention, N first-level headings and M second-level headings are obtained according to the columns to be converted, including:

[0022] Use the select statement to obtain the source of the first-level title and the second-level title;

[0023] Using a field group to represent the column to be converted as a row with the first-level title and a row with a second-level title, wherein the second-level title in the field group is included under the first-level title;

[0024] The first field of the field group represents a first-level title, and the second field of the field group represents a second-level title.

[0025] In combination with the first aspect of the present invention, in a second implementation of the present invention, performing a group query based on the initial one-dimensional report to obtain a result set includes:

[0026] Perform group query based on the row names, column names and statistical functions in the initial one-dimensional report to obtain a result set.

[0027] In combination with the second implementation manner of the first aspect of the present invention, in a third implementation manner of the present invention, traversing the result set according to the grouping result of the grouping query and writing data according to the table header includes:

[0028] Traversing the result set according to the grouping result of the grouping query, when the i-th data is obtained, inserting the i-th data into the string pointer, the string pointer indicates the position correspondence between the i-th data and the secondary title;

[0029] When the j-th data is obtained, if the data in the string pointer completely represents the k second-level headings under the n-th first-level heading, then the i-th data to the j-th data are filled into the column represented by the k second-level headings under the n-th first-level heading, where i is less than j, n is a positive integer less than or equal to N, and i, j, k, and n are positive integers.

[0030] In combination with the first aspect of the present invention, in a fourth embodiment of the present invention, in the process of converting the initial one-dimensional report into a two-dimensional report, before obtaining N first-level headings and M second-level headings according to the columns to be converted, and making a table header according to the first-level headings, the second-level headings and the fixed columns, the method includes:

[0031] Detecting whether the edited first SQL statement, the second SQL statement, and the SUBTOTAL statement include extended header information;

[0032] If the extended title information is included, a new title is obtained according to the extended title information, and the new title and the fixed columns are used to create the new table header;

[0033] The new title includes Pa-level titles, Pa=1, 2, 3, .., A, wherein any Pa-1-level title includes k Pa-level titles, and A is a positive integer.

[0034] In combination with the first aspect of the present invention, in the fifth implementation of the present invention, when the secondary title is empty, in the table header, a data statistics column is included after the column where the Nth first-level title is located, and the data statistics column represents the first statistical result of the first N-1 columns in the first-level title.

[0035] A second aspect of the present invention provides a data statistics device based on two-dimensional report conversion, comprising:

[0036] An initial performance information acquisition module, used to acquire an initial one-dimensional report including initial performance information;

[0037] A designation module, used for specifying fixed columns in the initial one-dimensional report by editing a first SQL statement, and specifying columns to be converted in the initial one-dimensional report by editing a second SQL statement;

[0038] A statistical calculation module, used for performing statistical calculation on the columns to be converted and the fixed columns by editing a SUBTOTAL statement;

[0039] A two-dimensional report conversion module, used for executing the edited first SQL statement, the second SQL statement and the SUBTOTAL statement, to convert the initial one-dimensional report into a two-dimensional report;

[0040] In the process of converting the initial one-dimensional report into a two-dimensional report, according to the columns to be converted, N first-level headings and M second-level headings are obtained, wherein any first-level heading includes k second-level headings, M=N×k, N, M, k are positive integers, and a table header is made according to the first-level heading, the second-level heading and the fixed columns;

[0041] In the table header, the last column of the mth sub-heading represents the first statistical result of the first k-1 columns in the mth sub-heading, where k is the total number of columns of the mth sub-heading;

[0042] A data statistics column is included after the column where the N-th first-level title is located, and the data statistics column represents the second statistical result of the first k-1 columns of the second-level title belonging to the N-th first-level title;

[0043] The last row of the fixed column also includes a data statistics row, and the data statistics row represents the third statistical results of the columns where the M secondary headings are located;

[0044] A result set acquisition module, used to perform group query based on the initial one-dimensional report to obtain a result set;

[0045] The data writing module is used to traverse the result set according to the grouping result of the grouping query, and write data in the two-dimensional report according to the table header, so as to re-count the initial score information through the first statistical result, the second statistical result and the third statistical result.

[0046] The third aspect of an embodiment of the present invention provides a terminal device, including a memory, a processor, and a computer program stored in the above memory and executable on the above processor, and when the above processor executes the above computer program, the steps of the method provided in the first aspect are implemented.

[0047] A fourth aspect of an embodiment of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method provided in the first aspect are implemented.

[0048] An embodiment of the present invention proposes a data statistics method based on two-dimensional report conversion, which specifies fixed columns and columns to be converted in an initial one-dimensional report through a first SQL statement and a second SQL statement, and performs statistical calculations on the columns to be converted and the fixed columns by editing a SUBTOTAL statement, so as to re-count the initial performance information through the first statistical result, the second statistical result and the third statistical result, so that the converted two-dimensional report includes aggregation operation results such as summation and average, thereby avoiding the steps of performing aggregation operations such as summation and average after converting a one-dimensional table to a two-dimensional table, and no longer changing the converted two-dimensional table, and simplifying the operation steps, thereby improving the efficiency of data statistics and analysis. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] Figure 1 A schematic diagram of the implementation flow of a data statistics method based on two-dimensional report conversion provided by an embodiment of the present invention;

[0050] Figure 2 A schematic diagram of an initial one-dimensional report provided by an embodiment of the present invention;

[0051] Figure 3 For the embodiment of the present invention according to Figure 2 The provided diagram is a 2D report with only the header created;

[0052] Figure 4 For the embodiment of the present invention according to Figure 2 A schematic diagram of the result set provided;

[0053] Figure 5 For the embodiment of the present invention according to Figure 3 and Figure 4 Schematic diagram of the two-dimensional report provided;

[0054] Figure 6 For the embodiment of the present invention according to Figure 2 A schematic diagram of a 2D report with only the header provided when extended title information is provided;

[0055] Figure 7 For the embodiment of the present invention according to Figure 2 Schematic diagram of a 2D report with only a header when only a first-level title is provided

[0056] Figure 8 A schematic diagram of the composition structure of a data statistics device based on two-dimensional report conversion provided by an embodiment of the present invention.

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

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

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

[0060] In this document, suffixes such as "module", "component" or "unit" used to represent elements are only used to facilitate the description of the present invention, and have no specific meanings. Therefore, "module" and "component" can be used interchangeably.

[0061] like Figure 1As shown, an embodiment of the present invention provides a data statistics method based on two-dimensional report conversion, which is applied to a statistical analysis scenario based on student test scores. When an initial one-dimensional report including initial score information is converted into a two-dimensional report, an aggregation operation can be performed on the converted two-dimensional report, so that the converted two-dimensional report includes aggregation operation results such as sum and average.

[0062] It includes but is not limited to the following steps:

[0063] S101. Obtain an initial one-dimensional report including initial performance information.

[0064] In the above step S101, each column of the initial one-dimensional report is an independent attribute, such as name, test subject, score, etc., and columns cannot be classified into the same category. Therefore, in the embodiment of the present invention, it is used to enter the initial score information.

[0065] like Figure 2 As shown, the initial score information used in the embodiment of the present invention is the score of a high school student. Figure 2 In the table, the names of the columns are: id, student name, gender, school, grade, class, test subject, test score, test type, and test month. Each row of records represents the student's test subject, test type, test month, and test score.

[0066] S102: Specify fixed columns in the initial one-dimensional report by editing a first SQL statement, and specify columns that need to be converted in the initial one-dimensional report by editing a second SQL statement.

[0067] S103 . Perform statistical calculations on the columns to be converted and the fixed columns by editing a SUBTOTAL statement.

[0068] In the above steps S102 and S103, the SQL (Structured Query Language) statement is a database query and programming language, and the Subtotal statement is actually a function used to implement data statistics such as sum, average, count, maximum value, minimum value, etc.

[0069] In the embodiment of the present invention, data statistics is to perform statistics on data. Therefore, in combination with the above Figure 2In the one-dimensional report shown, in the above step S102, the fixed columns in the initial one-dimensional report specified by editing the first SQL statement are columns with unchanged attributes, such as names, and the columns to be converted in the initial one-dimensional report specified by editing the second SQL statement are columns with changed attributes, such as test types and subjects. Then in the above step S103, by editing the SUBTOTAL statement, statistical calculations are performed on the columns to be converted and the fixed columns, that is, statistical calculations are performed on the values, such as the average score and total score based on various test scores. Among them, various test scores include test scores based on all test subjects in any test type.

[0070] In actual applications, the first SQL statement, the second SQL statement and the SUBTOTAL statement edited above can be transform table rtc_2 row name column testtype, subject statistics avg(score)subtotal sum(name)subtotal avg(testtype)subtotal sum(subject) INTO transform_case2_11;

[0071] In the above statement, rtc_2 is the source table name, transform_case2_11 is the target table name, and combined with Figure 2 In the initial one-dimensional report shown in the embodiment of the present invention, the name is used as a fixed column, and the test type and test subject are used as columns that need to be converted. In the embodiment of the present invention, as follows Figure 2 For rtc_2, as follows Figure 3 and the following Figure 5 is transform_case2_11.

[0072] S104: Execute the edited first SQL statement, the second SQL statement, and the SUBTOTAL statement to convert the initial one-dimensional report into a two-dimensional report.

[0073] In the above step S104, the first SQL statement, the second SQL statement and the SUBTOTAL statement executed may be: SUBTOTAL avg(row.x)|sum(col.x);

[0074] Among them, row represents the fixed column specified by the edited first SQL statement, col represents the column to be converted specified by the edited second SQL statement, avg(row.x) and sum(col.x) represent the edited SUBTOTAL statements, which mean to calculate the average or sum of multiple rows and the average or sum of multiple cols at the same time.

[0075] In the process of converting the initial one-dimensional report into a two-dimensional report, according to the columns to be converted, N first-level headings and M second-level headings are obtained, wherein any first-level heading includes k second-level headings, M=N×k, N, M, k are positive integers, and a table header is made according to the first-level heading, the second-level heading and the fixed columns;

[0076] In the table header, the last column of the mth sub-heading represents the first statistical result of the first k-1 columns in the mth sub-heading, where k is the total number of columns of the mth sub-heading;

[0077] A data statistics column is included after the column where the N-th first-level title is located, and the data statistics column represents the second statistical result of the first k-1 columns of the second-level title belonging to the N-th first-level title;

[0078] The last row of the fixed column also includes a data statistics row, and the data statistics row represents the third statistical results of the columns where the M secondary headings are located;

[0079] In the above-mentioned process of obtaining N first-level headings and M second-level headings according to the columns to be converted, the detailed implementation steps may be:

[0080] Use the select statement to obtain the source of the first-level title and the second-level title;

[0081] Using a field group to represent the column to be converted as a row with the first-level title and a row with a second-level title, wherein the second-level title in the field group is included under the first-level title;

[0082] The first field of the field group represents a first-level title, and the second field of the field group represents a second-level title.

[0083] In actual applications, the select statement in the above steps can be:

[0084] select testtype,subject from rtc_2 group by testtype,subject order bytesttype,subject;

[0085] The above-mentioned first-level title comes from the exam type, and the above-mentioned second-level title comes from the exam subject.

[0086] It can be understood that, in the second-level headings belonging to a first-level heading, the last column of the second-level headings represents the first statistical result, and at the end of the first-level heading, there is also a data statistics column, representing the second statistical result. Therefore, in actual applications, the above-mentioned field groups can be: {first model, mathematics}, {first model, Chinese}, {first model, first statistical result}, {monthly exam, mathematics}, {monthly exam, liberal arts}, {monthly exam, science}, {monthly exam, English}, {monthly exam, Chinese}, {monthly exam, first statistical result}, {second statistical result, -}.

[0087] It can be understood that the first column is the fixed column mentioned above, and the field group mentioned above is used to represent column data after the first column.

[0088] In a specific application, through the above steps S101 to S104, the obtained table header includes the first-level title, the second-level title and the fixed column, and also includes the last column of the mth second-level title, which represents the first statistical result of the first k-1 columns in the mth second-level title; and the data statistics column after the column where the Nth first-level title is located, which represents the second statistical result of the first k-1 columns in the second-level title belonging to the Nth first-level title; and the data statistics row after the last row of the fixed column, which represents the third statistical result of the column where the M second-level titles are located.

[0089] Combined with the above Figure 2 The initial one-dimensional report shown is as follows Figure 3 As shown, the table header in the embodiment of the present invention actually includes the test type, test subject and name, and also includes the last column of the mth test subject, which represents the first statistical result of the first k-1 test subjects. The first statistical result can be the total score or the average score. In the embodiment of the present invention, the total score is taken here; and the data statistics column located after the column where the Nth test type is located, which represents the second statistical result of the first k-1 test subjects among the test subjects belonging to the Nth test type. The second statistical result can be the total score or the average score. In the embodiment of the present invention, the average score is taken here; and the data statistics row located after the last row of the name represents the third statistical result of the column where the M test subjects are located, that is, the third statistical result based on each test subject of all people. The third statistical result can be the total score or the average score. In the embodiment of the present invention, the total score is taken here.

[0090] In the embodiment of the present invention, after executing the above steps S101 to S104, the obtained two-dimensional report only includes the header without data written. Therefore, the embodiment of the present invention also needs to combine the data in the initial one-dimensional report to complete the construction of the two-dimensional report.

[0091] S105: Perform group query based on the initial one-dimensional report to obtain a result set.

[0092] S106 , traversing the result set according to the grouping result of the grouping query, and writing data in the two-dimensional report according to the table header, so as to re-count the initial score information through the first statistical result, the second statistical result and the third statistical result.

[0093] In the embodiment of the present invention, combined with Figure 2 In the initial one-dimensional report shown, the data to be written in the above step S106 are the scores of each examination subject, that is, the examination scores.

[0094] Before writing the data, the embodiment of the present invention also processes the initial one-dimensional report through the above step S105 to facilitate the writing of the data in step S106.

[0095] In one embodiment, the above step S105 performs group query based on the initial one-dimensional report to obtain a result set, and the detailed implementation steps may be:

[0096] Perform group query based on the row names, column names and statistical functions in the initial one-dimensional report to obtain a result set.

[0097] In the embodiment of the present invention, combined with the above Figure 2 The initial one-dimensional report shown in the figure has a result set after group query. Figure 4 shown. Figure 4 In the table, the result set is still a one-dimensional table, but it is grouped according to the name. The second to fourth columns are the test type, test subject, and test score based on the name shown in the first column.

[0098] In actual applications, group query can also be executed using select statement, as shown in the following example:

[0099] select name,testtype,subject,avg(score)from rtc_2 group by name,testtype,subject order by name,testtype,subject;

[0100] Among them, the test scores of each test subject are expressed as average scores in the embodiment of the present invention. For example, student A's monthly test scores include the monthly test scores in October and the monthly test scores in December. Then, for the mathematics subject test scores of this type of monthly test, the average scores are expressed in the embodiment of the present invention.

[0101] Then the above step S106 of traversing the result set according to the grouping result of the grouping query and writing data according to the table header includes the following detailed implementation steps:

[0102] Traversing the result set according to the grouping result of the grouping query, when the i-th data is obtained, inserting the i-th data into the string pointer, the string pointer indicates the position correspondence between the i-th data and the secondary title;

[0103] When the jth data is obtained, if the data in the string pointer completely represents the k second-level headings under the nth first-level heading, then the ith data to the jth data are filled into the column represented by the k second-level headings under the nth first-level heading.

[0104] Among them, i is less than j, n is a positive integer less than or equal to N, and i, j, k, and n are positive integers.

[0105] In the embodiment of the present invention, each time a piece of data is read, the data is inserted into a string pointer until a row of data is completely read, and then filled into a two-dimensional table with only a header.

[0106] Combined with the above Figure 2 The initial one-dimensional report shown above Figure 3 The two-dimensional report with only the header is shown, and the above Figure 4 The data set shown, Figure 5 As shown, the embodiment of the present invention also shows a two-dimensional report form of re-statisticing the initial performance information through the first statistical result, the second statistical result and the third statistical result. Figure 5 In the example, when the first model data of a student named A, namely the test scores based on the two test subjects of mathematics and Chinese, and the first statistical results based on the above two test scores are inserted into the string pointer, the above three data are written into the columns (namely the test scores and the first statistical data) represented by the three second-level headings (namely the test subjects) under the first first-level heading (namely the test type).

[0107] and Figure 5The first statistical data is the total test score based on the first mock exam and the total test score based on the monthly exam. The second statistical result is the average test score based on the monthly exam. The third statistical result is the total mathematics test score of student A and student B, the total Chinese test score of student A and student B, and the total scores of the two subjects of student A and student B in the first mock exam, as well as the total mathematics test score of student A and student B, the total liberal arts test score of student A and student B, the total science test score of student A and student B, the total English test score of student A and student B, the total Chinese test score of student A and student B, and the total scores of the five subjects of student A and student B in the monthly exam, as well as the sum of the average scores of the five subjects of student A and student B in the monthly exam.

[0108] It can be seen that the embodiment of the present invention achieves the effect of re-statisticing the initial performance information using the first statistical result, the second statistical result and the third statistical result through the above-mentioned steps S101 to S106 through the SUBTOTAL statement, thereby avoiding the steps of converting the one-dimensional table into a two-dimensional table and then performing aggregation operations such as summation and averaging, and no longer changing the converted two-dimensional table, and simplifying the operation steps, thereby improving data statistical efficiency.

[0109] In a specific application, in the process of converting the initial one-dimensional report into a two-dimensional report, the initial one-dimensional report may include multiple types of information related to each other, such as Figure 2 In the report, the test type includes the test subjects, the test subjects include the test scores, and the test subjects are also counted according to the test time. Therefore, when generating a two-dimensional report, it may also include a third-level title, a fourth-level title, etc.

[0110] In the embodiment of the present invention, in the process of converting the initial one-dimensional report into a two-dimensional report, before obtaining N first-level titles and M second-level titles according to the columns to be converted, and making a table header according to the first-level titles, the second-level titles and the fixed columns, the method further includes:

[0111] Detecting whether the edited first SQL statement, the second SQL statement, and the SUBTOTAL statement include extended header information;

[0112] If the extended title information is included, a new title is obtained according to the extended title information, and the new title and the fixed columns are used to create the new table header;

[0113] The new title includes Pa-level titles, Pa=1, 2, 3, .., A, wherein any Pa-1-level title includes k Pa-level titles, and A is a positive integer.

[0114] In a specific application, the extended title information is the column to be converted specified when editing the first SQL statement, the second SQL statement and the SUBTOTAL statement. If the information in the column to be converted is greater than 2, the above extended title information will be generated.

[0115] Same as above Figure 2 Take the data in as an example, if the first SQL statement, the second SQL statement and the SUBTOTAL statement are edited as follows:

[0116] transform table rtc_2 row name column testtype,date,subjectstatistics avg(score)subtotal sum(name)subtotal avg(testtype)subtotal sum(subject)INTO transform_case2_11;

[0117] Among them, "column testtype, date, subject" indicates that the test date column needs to be converted at this time. The edited first SQL statement, the second SQL statement and the SUBTOTAL statement include extended title information. Compared with the original first-level title and second-level title, the test type is still the first-level title, the test date will be the new second-level title, and the test subject is the new third-level title. That is, in this embodiment, according to the extended title information, the first-level title and the second-level title, a new title is obtained. The new title includes the first-level title, the second-level title and the third-level title, which is test type-test date-test subject. The new table header is as follows Figure 6 shown.

[0118] Figure 6 In the new table header shown, since there are first-level, second-level, and third-level titles, Pa is 3. Figure 5 The header shown is, as you can imagine, Figure 6 The last column of the third-level title of the second-level title indicates that the first k 3 -1 column first statistical result, where k 3 The total number of columns of the third-level headings in this second-level heading. Figure 6 There are 3 sub-headings in total. In the first sub-heading, k 3 =3, in the second sub-heading, k 3 =5, in the third sub-heading, k 3 =5. Combine Figure 6According to the table content shown, the first statistical result can be the sum of the test subject scores of a student in October in the first mock exam type, the sum of the test subject scores of a student in October in the monthly exam type, and the sum of the test subject scores of a student in October.

[0119] In the embodiment of the present invention, statistics are also performed on the secondary titles, and the column where the secondary titles are located also includes an extended statistics column, which indicates the k 2 In the second-level heading, the second statistical result of the column where the third-level heading is located is as follows: Figure 6 There are 2 first-level headings in total. In the first first-level heading, k 2 = 1, and in this second-level title, the third-level title is in column 3. In the second first-level title, k 2 =2, and in these two second-level titles, the third-level titles are both in columns 5. Figure 6 The second statistical result can be the average of the sum of the October exam subject scores and the sum of the October exam subject scores for a student in the monthly exam type.

[0120] according to Figure 5 The header shown is, as you can imagine, Figure 6 In the example, the column where the last first-level title is located also includes a data statistics column, which indicates the third statistical result of the columns where all third-level titles are located. And the last row of the fixed column also includes a data statistics row, which indicates the fourth statistical result of the column where the third-level title is located. Figure 6 According to the table content shown, the third statistical result can be the average of the first and second statistical results of a student in the monthly exam type, and the fourth statistical result can be the sum of the scores of all students in each subject in each exam in both the first mock exam and the monthly exam, as well as the sum of the first, second and third statistical results in each column.

[0121] It is conceivable that the source of the new title can be obtained by using a select statement:

[0122] select testtype,subject from rtc_2 group by testtype,date,subjectorder by testtype,date,subject;

[0123] In specific applications, if the data to be counted does not need to be classified, the secondary title can also be empty. Therefore, the generated table header only includes the primary title. In this case, the column where the Nth primary title is located also includes a data statistics column, which indicates the first statistical result of the first N-1 columns in the primary title. Figure 7 As shown, the embodiment of the present invention is also combined with the above Figure 2 The initial one-dimensional report shown shows a two-dimensional report with only the header of the first-level title. Figure 7 Lieutenant General Figure 2 The second column in the table is used as a fixed column, and the seventh column is used as a column to be converted. The examination subjects in the seventh column are repeated, but according to the above step S106, the examination scores of each examination subject are expressed as average scores in the embodiment of the present invention. Therefore, it can be imagined that the repeated examination subjects are expressed with the same first-level title, and Figure 7 The test scores under each first-level heading are expressed as average scores.

[0124] like Figure 8 As shown, the embodiment of the present invention further provides a data statistics device 80 based on two-dimensional report conversion, comprising:

[0125] An initial performance information acquisition module 81 is used to acquire an initial one-dimensional report including initial performance information;

[0126] A designation module 82, used for designating fixed columns in the initial one-dimensional report by editing a first SQL statement, and designating columns to be converted in the initial one-dimensional report by editing a second SQL statement;

[0127] The statistical calculation module 83 is used to perform statistical calculations on the columns to be converted and the fixed columns by editing the SUBTOTAL statement;

[0128] A two-dimensional report conversion module 84, used to execute the edited first SQL statement, second SQL statement and SUBTOTAL statement to convert the initial one-dimensional report into a two-dimensional report;

[0129] In the process of converting the initial one-dimensional report into a two-dimensional report, N first-level titles and M second-level titles are obtained according to the columns to be converted, wherein any first-level title includes k second-level titles, M=N×k, N, M, k are positive integers, and a table header is made according to the first-level title, the second-level title and the fixed columns;

[0130] In the table header, the last column of the mth sub-heading indicates the first statistical result of the first k-1 columns in the mth sub-heading, where k is the total number of columns in the mth sub-heading;

[0131] The column where the Nth first-level title is located also includes a data statistics column, which indicates the second statistical result of the first k-1 columns in the second-level title belonging to the Nth first-level title;

[0132] The last row of the fixed column also includes a data statistics row, which indicates the third statistical result of the column where the M secondary headings are located;

[0133] A result set acquisition module 85 is used to perform group query based on the initial one-dimensional report to obtain a result set;

[0134] The data writing module 86 is used to traverse the result set according to the grouping result of the grouping query, and write data in the two-dimensional report according to the table header, so as to re-count the initial score information through the first statistical result, the second statistical result and the third statistical result.

[0135] An embodiment of the present invention also provides a terminal device including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, each step of the data statistics method based on two-dimensional report conversion in the above embodiment is implemented.

[0136] An embodiment of the present invention further provides a storage medium, which is a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, each step in the data statistics method based on two-dimensional report conversion in the above embodiment is implemented.

[0137] The embodiments described above are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the foregoing embodiments describe the present invention in detail, those skilled in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or replace some of the technical features therein by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A data statistics method based on two-dimensional report conversion, characterized in that: Applied to databases, methods include: Get an initial one-dimensional report including initial performance information; Specifying fixed columns in the initial one-dimensional report by editing a first SQL statement, and specifying columns to be converted in the initial one-dimensional report by editing a second SQL statement; By editing the SUBTOTAL statement, statistical calculations are performed on the columns to be converted and the fixed columns; Execute the edited first SQL statement, the second SQL statement and the SUBTOTAL statement to convert the initial one-dimensional report into a two-dimensional report; In the process of converting the initial one-dimensional report into a two-dimensional report, according to the columns to be converted, N first-level headings and M second-level headings are obtained, wherein any first-level heading includes k second-level headings, M=N×k, N, M, k are positive integers, and a table header is made according to the first-level heading, the second-level heading and the fixed columns; In the table header, the last column of the mth sub-heading represents the first statistical result of the first k-1 columns in the mth sub-heading, where k is the total number of columns of the mth sub-heading, and m is a positive integer less than or equal to M; A data statistics column is included after the column where the N-th first-level title is located, and the data statistics column represents the second statistical result of the first k-1 columns of the second-level title belonging to the N-th first-level title; The last row of the fixed column also includes a data statistics row, and the data statistics row represents the third statistical results of the columns where the M secondary headings are located; Perform group query based on the initial one-dimensional report to obtain a result set; The result set is traversed according to the grouping result, and data is written into the two-dimensional report according to the table header, so as to re-count the initial score information through the first statistical result, the second statistical result and the third statistical result.

2. The data statistics method based on two-dimensional report conversion according to claim 1, characterized in that: According to the columns to be converted, N first-level headings and M second-level headings are obtained, including: Use the select statement to obtain the source of the first-level title and the second-level title; Using a field group to represent the column to be converted as a row with the first-level title and a row with a second-level title, wherein the second-level title in the field group is included under the first-level title; The first field of the field group represents a first-level title, and the second field of the field group represents a second-level title.

3. The data statistics method based on two-dimensional report conversion according to claim 1 is characterized in that: Perform group query based on the initial one-dimensional report to obtain a result set, including: Perform group query based on the row names, column names and statistical functions in the initial one-dimensional report to obtain a result set.

4. The data statistics method based on two-dimensional report conversion as described in claim 3 is characterized in that: Traversing the result set according to the grouping result and writing data according to the header, including: Traversing the result set according to the grouping result, when the i-th data is obtained, inserting the i-th data into the string pointer, the string pointer indicates the position correspondence between the i-th data and the secondary title; When the j-th data is obtained, if the data in the string pointer completely represents the k second-level headings under the n-th first-level heading, then the i-th data to the j-th data are filled into the column represented by the k second-level headings under the n-th first-level heading, where i is less than j, n is a positive integer less than or equal to N, and i, j, k, and n are positive integers.

5. A data statistics device based on two-dimensional report conversion, characterized in that: Applied to database, the device includes: An initial performance information acquisition module, used to acquire an initial one-dimensional report including initial performance information; A designation module, used for specifying fixed columns in the initial one-dimensional report by editing a first SQL statement, and specifying columns to be converted in the initial one-dimensional report by editing a second SQL statement; A statistical calculation module, used for performing statistical calculation on the columns to be converted and the fixed columns by editing a SUBTOTAL statement; A two-dimensional report conversion module, used for executing the edited first SQL statement, the second SQL statement and the SUBTOTAL statement, to convert the initial one-dimensional report into a two-dimensional report; In the process of converting the initial one-dimensional report into a two-dimensional report, according to the columns to be converted, N first-level headings and M second-level headings are obtained, wherein any first-level heading includes k second-level headings, M=N×k, N, M, k are positive integers, and a table header is made according to the first-level heading, the second-level heading and the fixed columns; In the table header, the last column of the mth sub-heading represents the first statistical result of the first k-1 columns in the mth sub-heading, where k is the total number of columns of the mth sub-heading; A data statistics column is included after the column where the N-th first-level title is located, and the data statistics column represents the second statistical result of the first k-1 columns of the second-level title belonging to the N-th first-level title; The last row of the fixed column also includes a data statistics row, and the data statistics row represents the third statistical results of the columns where the M secondary headings are located; A result set acquisition module, used to perform group query based on the initial one-dimensional report to obtain a result set; The data writing module is used to traverse the result set according to the grouping result, and write data in the two-dimensional report according to the table header, so as to re-count the initial score information through the first statistical result, the second statistical result and the third statistical result.

6. A terminal device, characterized in that: The method comprises a memory, a processor and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, each step of the data statistics method based on two-dimensional report conversion as described in any one of claims 1 to 4 is implemented.

7. A storage medium, the storage medium being a computer-readable storage medium, on which a computer program is stored, characterized in that: When the computer program is executed by a processor, each step of the data statistics method based on two-dimensional report conversion as described in any one of claims 1 to 4 is implemented.