Data sorting method, database engine, and storage medium

Abstract

This application provides a data sorting method, a database engine, and a storage medium. In this application, for scenarios involving multi-column sorting, the data rows to be sorted are first grouped according to the clustering column. Then, the sorting is performed between and within each data group according to the sorting column, using the same sorting order. The sorting result obtained in this way retains the clustering characteristics of the clustered columns while essentially ensuring the global order of the data, which is beneficial for improving the computational performance related to the clustered columns. Especially when querying based on the sorting result, it ensures that the performance of queries by data range is largely unaffected, while significantly improving the performance of queries by data grouping.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a data sorting method, a database engine, and a storage medium. Background Technology

[0002] A database is a repository for organizing, storing, and managing data according to a data structure, typically consisting of multiple tables. In practical applications, it is often necessary to sort the data in these tables. When sorting involves multiple columns of data, existing techniques usually prioritize one column or sort the columns according to their priority, such as the Zorder sorting algorithm. While existing sorting methods can guarantee that the columns involved in the sorting are completely ordered, the clustering property of the data is disrupted when clustered columns exist, which reduces the computational performance related to those clustered columns. Summary of the Invention

[0003] This application provides a data sorting method, a database engine, and a storage medium to preserve the data aggregation characteristics of clustered columns and improve the related computational performance of clustered columns while ensuring the global order of data.

[0004] This application provides a data sorting method, comprising: in response to a sorting instruction, obtaining a dataset to be sorted, the dataset comprising N data rows and M data columns, wherein the M data columns include at least a clustering column and a sorting column, and N and M are natural numbers greater than or equal to 2; grouping the N data rows in the dataset according to the clustering column to obtain at least two data groups; and sorting the data according to the sorting column in the same sorting order between the at least two data groups and within each data group to obtain a sorting result.

[0005] This application embodiment also provides a database engine, including: a memory and a processor; the memory is used to store a computer program; the processor, coupled to the memory, is used to execute the computer program for: in response to a sorting instruction, obtaining a dataset to be sorted, the dataset including N rows and M columns, the M columns including at least a clustering column and a sorting column, where N and M are natural numbers greater than or equal to 2; grouping the N data rows in the dataset according to the clustering column to obtain at least two data groups; and sorting the data according to the sorting column in the same sorting order between the at least two data groups and within each data group to obtain a sorting result.

[0006] This application also provides a computer-readable storage medium storing a computer program, which, when executed by a processor, causes the processor to implement the steps in the data sorting method provided in this application.

[0007] This application also provides a computer program product, including a computer program / instructions, which, when executed by a processor, cause the processor to implement the steps in the data sorting method provided in this application.

[0008] In this embodiment, for scenarios involving multi-column sorting, the data rows to be sorted are first grouped according to the clustering column. Then, the sorting is performed between and within each data group according to the sorting column, using the same sorting order. This sorting method preserves the clustering characteristics of the clustered columns while maintaining the overall global order of the data, thus improving the computational performance of the clustered columns. Especially when querying based on the sorting results, it ensures that performance is largely unaffected when querying by data range, while significantly improving performance when querying by data group. Attached Figure Description

[0009] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments of this application and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0010] Figure 1a A flowchart illustrating a data sorting method provided for an exemplary embodiment of this application;

[0011] Figure 1b A schematic diagram illustrating the application status of the data sorting method provided in an exemplary embodiment of this application in different scenarios;

[0012] Figure 1c To adopt Figure 1a The diagram illustrates the process of sorting a dataset containing order numbers and shipping times.

[0013] Figure 1d This is a comparison diagram showing the sorting results of the method in the embodiments of this application and the sorting results of the single-column sorting method in terms of orderliness.

[0014] Figure 2 A schematic diagram of the structure of a data sorting device provided for an exemplary embodiment of this application;

[0015] Figure 3 This is a schematic diagram of the structure of a database engine provided for an exemplary embodiment of this application. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0017] In existing database sorting schemes, the clustering characteristics of data are disrupted when clustered columns exist, leading to reduced computational performance related to these columns. To address this issue, this application's embodiments, in scenarios involving multi-column sorting, first group the data rows to be sorted according to the clustered columns. Then, sort the data rows within each group and between them according to the sorting columns, maintaining the same sorting order. This sorting method preserves the clustering characteristics of the clustered columns while largely ensuring the global order of the data, thus improving the computational performance related to these columns. Especially when querying based on the sorted results, it ensures that performance is largely unaffected when querying by data range while significantly improving performance when querying by data group.

[0018] The technical solutions provided by the various embodiments of this application are described in detail below with reference to the accompanying drawings.

[0019] Figure 1a This is a flowchart illustrating a data sorting method provided for an exemplary embodiment of this application. Figure 1a As shown, the method includes:

[0020] 11. In response to a sorting instruction, obtain the dataset to be sorted, which includes N data rows and M data columns, where the M data columns include at least a clustered column and a sorted column, and N and M are natural numbers greater than or equal to 2;

[0021] 12. Group the N data rows in the above dataset according to the clustering column to obtain at least two data groups;

[0022] 13. Sort the data in the same order as the sorted data group, between at least two of the above data groups and within each data group, to obtain the sorted result.

[0023] The data sorting method provided in this embodiment is applicable to various database systems that support data tables, such as traditional databases, cloud databases, data warehouses, and data lakes. A cloud database refers to a database optimized or deployed in a virtual computing environment. A data warehouse is a subject-oriented, integrated, non-volatile, and time-variant collection of data. The data warehouse in this embodiment can be either a traditional data warehouse or a cloud-native data warehouse. A data lake is a system that provides users with serverless, interactive query and analysis services in the cloud.

[0024] Regardless of the type of database system, it provides users with data storage functions as well as various services such as querying, inserting, deleting, and updating. Users of a database system can be individuals, enterprises, services, application systems, etc., without limitation. In certain uses of a database system, such as data storage, data querying, or data migration, data sorting may be involved. When data sorting is required, a sorting command can be issued to the database system to instruct the database engine to perform the data sorting operation.

[0025] In its implementation, a sorting instruction can be one or more database statements, depending on the database language used by the database system, such as SQL, Sybase, or MySQL statements. A sorting instruction must include at least the following: information pointing to the dataset to be sorted. This information varies depending on the implementation of the dataset; for example, if the dataset is stored in one or more original tables, the information pointing to the dataset could be the name of the original table; or, if the dataset is stored in a cache, memory, file, or network, the information pointing to the dataset could be the access path or address of the dataset. Furthermore, the sorting instruction may also include other information related to the dataset, such as its size (e.g., N rows and M columns) and the data types of each column. Here, N and M are natural numbers greater than or equal to 2. After receiving the sorting instruction, the database engine can retrieve the dataset to be sorted according to the instruction and perform the sorting operation on the dataset.

[0026] Depending on the data sorting scenario, the entity issuing the sorting command may be either the database system user or the database system administrator; correspondingly, the method for obtaining the dataset to be sorted will also differ slightly. Examples are given below:

[0027] like Figure 1b As shown, in application scenario A1, the data source generates data during the actual production process. This data is uploaded to the database system in real time. The database engine stores the data in the original data table according to the data generation time. While the data is stored in the original data table in chronological order of generation, it is out of order in certain dimensions. The data source can be any software module that generates data and requires data storage, such as application systems, applications, mini-programs, or cloud services. It can also be various hardware modules such as CPUs, GPUs, terminal devices, server devices, and network devices.

[0028] Taking an order processing system as an example, when the system receives a user's order request, it generates an order number and associates this number with information such as the order time, user information, and order details. This forms a data entry and is recorded in the original data table. Further, when it receives a user's payment request, the system records the payment time, payment amount, and payment method corresponding to that order number, creating another data entry and recording it in the original data table. Finally, when the merchant ships the goods, the system also records the shipping time and shipping method, creating yet another data entry and recording it in the original data table. In the original data table, data generated for the same order at different times is stored in a scattered manner. If a user needs to query the complete transaction information for an order, they need to traverse the entire data table, resulting in relatively low query efficiency.

[0029] As illustrated in the example above, application scenario A1 faces the problem of low query efficiency. To improve query efficiency, it is necessary to sort the original data table to obtain the target data table that meets the query requirements. Therefore, the database system user can issue a sorting instruction, which includes the name of the original data table to be sorted, the specifications of the original data table, and the sorting requirements. These requirements specify which data columns should be sorted. Alternatively, the database system user can submit a sorting request to the database system administrator. This request includes the name of the original data table to be sorted, the specifications of the original data table, and the sorting requirements. The administrator generates a sorting instruction based on the request and provides it to the database engine, which then performs the sorting operation on the original data table according to the instruction. In application scenario A1, the original data table is the dataset to be sorted. After receiving the sorting instruction, the database engine can obtain the original data table to be sorted based on the name of the original data table contained in the instruction, and sort the data in the original data table according to the sorting requirements in the instruction to obtain the target data table.

[0030] like Figure 1b As shown in application scenario A2, the data source generates data during actual production. This data is temporarily stored or cached locally. When the amount of temporarily stored or cached data reaches a set data volume threshold, or when the set upload time or period is reached, the temporarily stored or cached data is transferred to the database system for storage. To facilitate subsequent data retrieval, during data storage, the database system user can issue an instruction to the database engine to store data in order. This means sorting the data during data storage and storing it according to the sorting result. This instruction includes the data access path or address and the sorting requirements. Alternatively, the database system user can also issue a request for sequential storage to the database system administrator, including the data access path or address and the sorting requirements. The administrator generates a sorting and storage instruction based on this request and provides it to the database engine, which then sorts and stores the data according to the instruction. In application scenario A2, the sorting and storage instruction can be considered a special type of sorting instruction, and the data to be stored is the dataset to be sorted. The database engine retrieves the data to be stored based on the data access path or address contained in the sorting and storage instruction, and then sorts the data to be stored according to the sorting requirements contained in the instruction before storing it in the target data table.

[0031] Furthermore, if some data already exists in the target data table, the database engine can merge the data to be stored with the existing data in the target data table to form a dataset to be sorted.

[0032] like Figure 1bAs shown in application scenario A3, the data source generates data during the actual production process, and this data is stored in tables within the database system. This data may be stored in real-time, in batches, or periodically; there is no limitation on this. When a user needs to query data, they can issue a query command to the database engine, which includes the data table to be queried and the query conditions. To improve query efficiency, the database engine can first sort the data table to be queried according to the query command to obtain the target data table, and then perform the query operation on the sorted target data table. In application scenario A3, the query command can be considered a special sorting command, and the data table to be queried is the dataset to be sorted. The database engine determines the data table to be sorted based on the data table name included in the query command, and sorts the data table to be queried according to the query conditions in the query command to obtain the target data table, and then performs the query on the target data table. The query conditions may be a range of data to be queried or a group of data to be queried; there is no limitation on this.

[0033] Regardless of the application scenario, after obtaining the dataset to be sorted, it can be determined that the dataset includes N data rows and M data columns, and that the M data columns include at least a clustered column and a sorted column. This embodiment focuses on describing the data sorting process involving both clustered columns and sorted columns. Clustered columns and sorted columns are both data columns in the dataset, but they are two types of data columns with different properties. A clustered column requires that the data in the column have clustering characteristics, meaning that identical data or data within the same range are stored adjacently. A sorted column requires that the data in the column have ordered characteristics, meaning that the data in the column are stored in ascending or descending order. How to simultaneously accommodate the clustering and sorting characteristics of the data when both sorted columns and clustered columns exist, in order to achieve better query performance, is the technical problem that this embodiment aims to solve.

[0034] After determining the clustering column and data columns in the dataset to be sorted, the N data rows in the dataset are first grouped according to the clustering column to obtain at least two data groups. Specifically, this grouping process involves dividing the N data rows into different data groups based on the data in the clustering column, with each data row as a unit. In one optional embodiment, data rows with identical data in the clustering column can be grouped into the same data group to obtain at least two data groups, thus reflecting the clustering characteristics of the data. Alternatively, in another optional embodiment, data rows with data within the same data range in the clustering column can also be grouped into the same data group to obtain at least two data groups, thus reflecting the clustering characteristics of the data. The granularity of the data clustering characteristics differs slightly in these two optional embodiments, but the principle is the same.

[0035] In this embodiment, the number of clustered columns is not limited; it can be one or more (here, "more" includes two or more). Regardless of the number of clustered columns, the implementation method for grouping N data rows in the dataset according to the clustered columns is the same or similar. When there are multiple clustered columns, grouping N data rows in the dataset according to the clustered columns includes the following methods:

[0036] Method 1: Group data rows with identical data in multiple clustered columns into the same data group to obtain at least two data groups, thus reflecting the clustering characteristics of the data. For example, assuming there are three clustered columns, R, T, and W, for any two data rows L1 and L2, if the values ​​in columns R, T, and W of data rows L1 and L2 are all the same, then data rows L1 and L2 are grouped into the same data group.

[0037] Method 2: Group rows where all data in multiple clustered columns fall within their corresponding data ranges into the same data group to obtain at least two data groups, thus reflecting the clustering characteristics of the data. The data ranges corresponding to different clustered columns can be the same or different. For example, assuming there are three clustered columns: R, T, and W, with column R corresponding to the data range (s1-s2), column T to the data range (s3-s4), and column W to the data range (s5-s6), then for any two data rows L1 and L2, if the values ​​in column R of both rows L1 and L2 are within the data range (s1-s2), the values ​​in column T are within the data range (s3-s4), and the values ​​in column W are within the data range (s5-s6), then rows L1 and L2 are grouped into the same data group.

[0038] Method 3: Group rows from multiple clustered columns where some clustered columns have identical data, while the data in the remaining clustered columns all fall within their corresponding data ranges, into the same data group to obtain at least two data groups, thus reflecting the clustering characteristics of the data. The data ranges corresponding to different clustered columns can be the same or different. For example, assuming there are three clustered columns, R, T, and W, where column R must have identical data, column T's data range is (s3-s4), and column W's data range is (s5-s6), then for any two data rows L1 and L2, if the values ​​in column R of data rows L1 and L2 are the same, while the values ​​in column T and column W are both within the data range (s3-s4) and (s5-s6), then data rows L1 and L2 are grouped into the same data group.

[0039] After obtaining at least two data groups, sorting can be performed in the same order as the sort sequence, both between the at least two data groups and within each data group, to obtain the final sorted result. Sorting between data groups, granularly according to the sort sequence, preserves the clustering characteristics of the data, facilitating queries by data group. Further sorting within each data group, according to the sort sequence, can, to some extent, present the globally ordered nature of the data, ensuring that performance is almost unaffected when querying by data range. It should be noted that the orderliness between data groups may exhibit slight fluctuations, i.e., data may show disordered characteristics within a small range. However, this range is relatively small and usually has little impact on the performance of queries by data range.

[0040] In the various embodiments of this application, the implementation of sorting is not limited to sorting between data groups or within each data group. In an optional embodiment B1, sorting can be performed first between data groups, and then sorting can be performed within each data group. In another optional embodiment B2, sorting can be performed first within each data group, and then sorting can be performed between data groups. The implementation process of the two sorting methods will be described in detail below.

[0041] Option B1: After obtaining at least two data groups, sort them in the specified sorting order according to the sorting sequence, first sorting between the at least two data groups; then, sorting within each data group to obtain the sorting result.

[0042] The specified sorting order can be determined through a sorting command. For example, a sorting command can include a parameter. If the parameter takes the first value, it indicates sorting in ascending order; if it takes the second value, it indicates sorting in descending order. The first value can be 0 and the second value can be 1; or the first value can be 1 and the second value can be 0; or the first value can be "↑" and the second value can be "↓", etc., without limitation. Alternatively, the sorting order can be pre-configured in the database engine, allowing the database engine to perform sorting operations between and within data groups using the pre-configured sorting order.

[0043] In optional embodiment B1, the data is first sorted between data groups, and then sorted within data groups. Both the sorting method between data groups and the sorting method within data groups can be implemented in various ways. One specific implementation of optional embodiment B1 includes:

[0044] First, based on the data in the sorted sequences contained in at least two data groups, determine the benchmark data corresponding to at least two data groups. This benchmark data is used for sorting among the at least two data groups, and it is determined based on the data in the sorted sequences contained in each data group. Optionally, for each data group, the minimum, maximum, median, or mean of the sorted sequences within that data group can be selected as the benchmark data for that data group. Here, the median refers to the data in the middle position of the sorted sequence after arranging the data in N data rows in a certain order. The mean refers to the result obtained by averaging the data in the sorted sequences of N data rows.

[0045] Next, according to the specified sorting order, the data groups are sorted based on the reference data corresponding to at least two data groups. If the specified sorting order is from smallest to largest, the data group with smaller reference data is stored earlier, and vice versa. If the specified sorting order is from largest to smallest, the data group with larger reference data is stored earlier, and vice versa. In practical implementation, the reference data corresponding to at least two data groups can be added to the target data table according to the specified sorting order to obtain the data partitions corresponding to at least two data groups in the target data table. The position of the reference data in the target data table can be regarded as the dividing line between two adjacent data groups in terms of storage location.

[0046] Furthermore, after determining the data partition corresponding to each data group in the target data table, for each data group, data rows in the data group can be filled into the data partition where the data group is located according to the specified sorting order, in order to obtain the sorting result. Specifically, for any data row to be filled in the data group, the data of the data row to be filled on the sorting list is compared with the data of each existing data row in the data partition where the data group is located (including the data row where the reference data is located and other data rows that have already been filled) on the sorting list. According to the size relationship between the data, an existing data row adjacent to the data row to be filled is found according to the specified sorting order, and the data row to be filled is filled above or below the existing data row.

[0047] Furthermore, in the above embodiments, the reference data corresponding to each data group can be selected from data in the sorted sequence of the data group, such as the minimum data, maximum data, or median; alternatively, data not belonging to the sorted sequence of the data group can be selected, such as the average of the data in the sorted sequence of the data group. For each data group, if the reference data corresponding to the data group does not belong to the sorted sequence contained in the data group, after filling the data row of the data group into the data partition where the data group is located, the data row containing the reference data can also be deleted from the target data table to ensure that no additional data is added when the sorting is successful, and to keep the original data volume unchanged.

[0048] Option B2: After obtaining at least two data groups, sort them in the specified sorting order according to the sorting sequence, first sorting within each data group; then sorting between at least two data groups to obtain the sorting result.

[0049] In optional embodiment B2, the data is first sorted within a data group, and then sorted between data groups. Both the sorting method within and between data groups can be implemented in various ways. One specific implementation of optional embodiment B2 includes:

[0050] First, according to the sorting sequence, the data rows in each data group are stored in a temporary storage area in the specified sorting order, thus achieving sorting within the data group. The temporary storage area can be cache space, memory space, or a temporary file. Sorting within a data group refers to the process of sorting the data rows in each data group according to the sorting sequence. If the specified sorting order is ascending, for any data row in a data group, the smaller the data in the sorting sequence, the earlier the data row is stored; conversely, the larger the data in the sorting sequence, the later the data row is stored. If the specified sorting order is descending, for any data row in a data group, the larger the data in the sorting sequence, the earlier the data row is stored; conversely, the smaller the data in the sorting sequence, the later the data row is stored. It should be noted that this step does not restrict which data group is sorted within the group first, or the order in which the data groups are sorted within the group is not limited. In other words, within the temporary storage area, the data rows within each data group are arranged in an ordered manner according to the sorting sequence, but they may be out of order between data groups.

[0051] After sorting each data group within its own group, the next step is to sort between at least two data groups, a process known as between-group sorting. This between-group sorting process includes:

[0052] Based on the data in the sorted sequence contained in each of the at least two data groups, determine the reference data corresponding to the at least two data groups; the method for determining the reference data and its function can be found in the foregoing embodiments, and will not be repeated here.

[0053] Sort the reference data corresponding to at least two data groups according to the specified sorting order to obtain the sorting order among the at least two data groups. The sorting order among the reference data is the same as the sorting order among the at least two data groups.

[0054] Finally, according to the sorting order among at least two data groups, data rows from at least two data groups are sequentially read from the temporary storage area and written to the target data table to obtain the sorting result. In this embodiment, data is read from the temporary storage area sequentially, group by group, according to the sorting order among at least two data groups; that is, data is read directly from one data group at a time. Correspondingly, when writing data to the target data table, data is also written sequentially, group by group, according to the sorting order among at least two data groups; that is, data from one data group is written to the target data table at a time. Ultimately, in the target data table, the data between and within each data group is sorted in order.

[0055] The following is based on Figure 1c Taking the dataset to be sorted shown as an example, the sorting process of this application embodiment will be illustrated. Figure 1c In the dataset to be sorted, there are 10 rows and 2 columns. One column stores order numbers generated at different times, denoted as `orderkey`. The other column stores the shipping dates corresponding to different order numbers, denoted as `shipdate`. Figure 1c In the data column, the order number is a clustered column, and the shipping date is a sorted column.

[0056] like Figure 1c As shown, the sorting process includes:

[0057] First, the 10 data rows are divided into 4 groups based on the clustering column `orderkey`. Specifically, data rows with the same value in the `orderkey` column are grouped into the same data group. Figure 1cIn the data, rows with a clustered column orderkey value of 35 are grouped into the same data group, denoted as Group 1; rows with a clustered column orderkey value of 68 are grouped into the same data group, denoted as Group 2; rows with a clustered column orderkey value of 93 are grouped into the same data group, denoted as Group 3; and rows with a clustered column orderkey value of 117 are grouped into the same data group, denoted as Group 4.

[0058] Next, sort the data groups according to the sorting sequence `shipdate`. Specifically, sort the data groups according to the minimum value in the sorting sequence `shipdate`. The minimum value in `shipdate` for group 1 is 1992-01-22, for group 2 it is 1996-01-01, for group 3 it is 1992-01-08, and for group 4 it is 1996-02-01. Sort them in ascending order to obtain... Figure 1c The sorting results shown are, in order, group 3, group 1, group 2, and group 4; the data rows containing the four smallest data points are stored in the target data table according to the sorting results among the four groups. In this embodiment, the size of time data refers to the time of day; the earlier the time, the smaller the time data, and the later the time, the larger the time data.

[0059] Next, for each data group, according to the size relationship of the data in the sorted sequence `shipdate`, the remaining data rows in that data group are populated in the target data table. For example... Figure 1c As shown, the data is filled in ascending order according to the data in the sorting sequence `shipdate`. The resulting sorting results are as follows: the order between groups is Group 3, Group 1, Group 2, and Group 4. Within Group 3, the sorted data are 1992-01-08, 1992-02-17, and 1992-03-15; within Group 1, the sorted data are 1992-01-22, 1992-02-21, and 1992-03-19; within Group 2, the sorted data are 1996-01-01 and 1996-02-13; and within Group 4, the sorted data are 1996-02-01 and 1996-04-13. Figure 1c As can be seen, the data within each group is stored continuously. That is, within the same data group, the row containing the smallest data in the sorted sequence shipdate in each group forms the boundary between it and its previous neighbor.

[0060] It should be noted here that, Figure 1cIn the illustrated embodiments, the detailed implementation process of optional embodiment B1 has been described by way of example. The detailed implementation process of optional embodiment B2 is similar and will not be described by way of example again. In addition, in Figure 1c The illustrated embodiment uses a single sorting sequence as an example, but is not limited to this. The embodiments of this application do not limit the number of sorting sequences; it can be one or more (here, "more" refers to two or more). When there are multiple sorting sequences, the methods for sorting data groups according to the sorting sequence can be, but are not limited to, the following:

[0061] Method C1: Select a baseline sorting sequence from multiple sorting sequences, and sort the data groups according to the baseline sorting sequence in a specified sorting order. There is no limitation on the method of selecting the baseline sorting sequence. Optionally, a sorting sequence can be randomly selected as the baseline sorting sequence; alternatively, the priority of multiple sorting sequences can be defined in the sorting instruction, and the sorting sequence with the highest priority can be selected as the baseline sorting sequence; or, the sorting sequence with the most suitable data attributes for sorting can be selected as the baseline sorting sequence. After selecting the baseline sorting sequence, sorting the data groups according to the baseline sorting sequence includes: using the baseline sorting sequence as a reference, determining the reference data corresponding to each data group, and then sorting between at least two data groups based on the reference data. Detailed implementation processes can be found in the aforementioned embodiments and will not be repeated here.

[0062] Method C2: Based on the priority between sorting sequences, multiple sorting sequences are used sequentially in a specified sorting order to perform multiple rounds of sorting among the data groups. When sorting among the data groups using each sorting sequence in this round, the currently used sorting sequence is used as the basis to determine the reference data corresponding to each data group. Then, based on the reference data, this round of sorting is performed between at least two data groups. For detailed implementation process, please refer to the aforementioned embodiments, which will not be repeated here.

[0063] When there are multiple sorting sequences, sorting within each data group based on the sorting sequence can be done in, but is not limited to, the following ways:

[0064] Method D1: Select a base sorting sequence from multiple sorting sequences, and sort the data rows within the data group according to the base sorting sequence in the specified sorting order.

[0065] Method D2: Based on the priority between sorting sequences, multiple sorting sequences are used sequentially in a specified sorting order to perform multiple rounds of sorting among the data rows within the data group.

[0066] It should be noted that the data sorting method provided in this application embodiment can be used to sort datasets that simultaneously contain clustered columns and sorted columns. In the dataset, which one or more data columns are clustered columns and which one or more data columns are sorted columns can be pre-configured, or they can be left unconfigured. If no clustered or sorted columns are configured, after obtaining the dataset to be sorted, suitable clustered and sorted columns need to be configured for the dataset according to the sorting requirements. If clustered and sorted columns are pre-configured, these columns may or may not be suitable for the data sorting method provided in this application embodiment. If unsuitable, the clustered and sorted columns in the dataset can be readjusted or configured. Therefore, after obtaining the dataset to be sorted, the following operation is also included: configuring clustered and sorted columns suitable for data sorting using the data sorting method provided in this application embodiment, based on the data attributes and / or data distribution characteristics of each data column in the dataset. This configuration can be an initial configuration of clustered and sorted columns, or a process of reconfiguring or adjusting existing clustered and sorted columns. The data attributes of a data column reflect the attributes, types, or characteristics of the data it carries. Taking order information as an example, for a data column storing order numbers, its data attributes reflect the order number as a data object; for a data column storing behavioral data corresponding to an order, its data attributes reflect behavioral data related to the order, such as placing an order, making a payment, or shipping; and for a data column storing the time information that generated the behavioral data, its data attributes reflect the time information when the behavioral data was generated. The data distribution characteristics of a data column include the distribution of the data stored in that column. This distribution mainly refers to the global data fluctuation range, the local data fluctuation range under specific conditions, and the proportional relationship between the local fluctuation range and the global data fluctuation range.

[0067] In one optional embodiment, considering that data sorting is more common in database analytical scenarios, and that this data analysis mainly targets the object to be analyzed, such as an order, a student, or an application, the identification information of the object to be analyzed needs to have clustering properties. By storing related data of the same object to be analyzed contiguously, relevant data of the object to be analyzed can be easily and quickly retrieved during data analysis. Based on this, the data column used to store the identification information of the object to be analyzed can be identified according to the data attributes of the M data columns in the dataset to be sorted, and the data column used to store the identification information of the object to be analyzed can be configured as a clustered column.

[0068] Furthermore, the sorting method provided in this embodiment needs to simultaneously consider both clustering and sorting characteristics. To maintain the global sorting characteristics among the data as much as possible, the local fluctuation range of the data in the sorted sequence should be as small as possible relative to the global data fluctuation range. Here, the local data fluctuation range refers to the data fluctuation range of the same object to be analyzed in the sorted sequence. Taking orders as an example, the time range for generating various behavioral data for the same order is usually no more than one day, but the time range for generating various behavioral data for different orders can be very wide, possibly one month, three months, or one year. Assuming the global fluctuation range of time information is within one year, while the fluctuation range of time information for the same order is within one day, this local data fluctuation range is much smaller than the global data fluctuation range. Similarly, taking student grades as an example, the grades of the same student will not change much across different semesters, but the grades of all students in the entire school will fluctuate significantly across different semesters. Therefore, the fluctuation of the same student's grades is much smaller than the fluctuation of the overall student performance. In this embodiment, the ratio of the local data fluctuation range to the global data fluctuation range can be used to determine whether the local data fluctuation range is much smaller than the global data fluctuation range. Based on this, after determining the clustered columns, data columns whose local data fluctuation range relative to the global data fluctuation range of the same object meets the set conditions (e.g., less than a set ratio threshold) can be identified according to the data distribution characteristics of the other data columns in the dataset. These data columns are then set as sorted columns. The set conditions can be flexibly configured according to application requirements and are not limited thereto.

[0069] Furthermore, in some analytical scenarios, the data related to the object to be analyzed includes not only the object's identification information but also the behavioral data generated by the object and the time information of that behavioral data generation. Based on this, in data columns where the ratio of the data fluctuation range corresponding to the same object to be analyzed to the global data fluctuation range meets certain conditions, the data columns used to store the behavioral data generated by the object and / or the data columns used to store the time information of that behavioral data can be configured as sorting columns. Simply put, a prerequisite for a data column to serve as a sorting column is that the local data fluctuation range of the data in that column relative to the same object to be analyzed is much smaller than the global data fluctuation range, based on the premise of not affecting the global ranking of the data in that column.

[0070] Based on the clustered and sorted columns, the dataset to be sorted can be sorted using the method described in the above embodiments, thereby obtaining a sorting result that takes into account both data clustering characteristics and global sorting characteristics. The method provided in this application, while ensuring data clustering characteristics, combines the characteristic that the fluctuation range of data within the same group on the sorted column is extremely small compared to the global data range, and sorts between groups based on the benchmark data obtained from the data on the sorted columns within each group. This ensures that the data between groups generally presents a sorting trend from small to large or from large to small. Even if sorting jitter occurs, it will only occur within a very small range, and this jitter has almost no impact on the performance of queries based on data ranges, but can greatly improve the performance of grouped queries. Figure 1d As shown, using the same dataset, the straight line represents the data sorting result obtained by sorting the dataset using a sorting sequence, where the data follows a completely ascending order; the broken line represents the data sorting result obtained by sorting the dataset using the method provided in the embodiments of this application. The broken line represents the same trend as the straight line, except that there is sorting jitter within a small range. This sorting jitter has virtually no impact on queries based on data ranges; that is, when querying data within a certain range, this jitter will fall within the query range. For example, suppose a query request might require retrieving data within the range of 1-10, and the sorting jitter is typically between 0.5 and 1.

[0071] It should be noted that the execution subject of each step of the method provided in the above embodiments can be the same device, or the method can be executed by different devices. For example, the execution subject of steps 11 to 13 can be device A; or the execution subject of steps 11 and 12 can be device A, and the execution subject of step 13 can be device B; and so on.

[0072] Furthermore, in some of the processes described in the above embodiments and accompanying drawings, multiple operations appear in a specific order. However, it should be clearly understood that these operations may not be executed in the order they appear herein, or they may be executed in parallel. The operation numbers, such as 11, 12, etc., are merely used to distinguish different operations and do not represent any execution order. Additionally, these processes may include more or fewer operations, and these operations may be executed sequentially or in parallel. It should be noted that the descriptions such as "first" and "second" in this document are used to distinguish different messages, devices, modules, etc., and do not represent a sequential order, nor do they limit "first" and "second" to different types.

[0073] Figure 2 This is a schematic diagram of a data sorting apparatus provided for an exemplary embodiment of this application. Figure 2As shown, the device includes: an acquisition module 21, a grouping module 22, and a sorting module 23.

[0074] The dataset comprises several modules: Acquisition module 21, which, in response to a sorting instruction, acquires the dataset to be sorted. The dataset consists of N rows and M columns, where the M columns include at least a cluster column and a sorting column. N and M are natural numbers greater than or equal to 2. Grouping module 22, which groups the N rows of the dataset according to the cluster column to obtain at least two data groups. Sorting module 23, which sorts the data according to the sorting column in the same order between the at least two data groups and within each data group to obtain the sorted result.

[0075] In an optional embodiment, when the grouping module 22 groups the N data rows in the dataset according to the cluster column, it is specifically used to: divide the data rows with the same data or within the same data range in the cluster column into the same data group to obtain at least two data groups.

[0076] In an optional embodiment, when sorting data according to the sorting sequence between and within the at least two data groups, the sorting module 23 is specifically configured to:

[0077] According to the sorting sequence, the data is sorted in the specified sorting order between the at least two data groups, and then sorted within each data group to obtain the sorting result;

[0078] or

[0079] After sorting within each data group according to the specified sorting order, sorting is performed between the at least two data groups to obtain the sorting result.

[0080] Further optionally, after sorting among at least two data groups, the sorting module 23, when sorting within each data group, specifically performs the following: determining the base data corresponding to the at least two data groups based on the data in the sorting sequence contained in each of the at least two data groups; adding the base data corresponding to the at least two data groups to a target data table according to a specified sorting order to obtain the data partitions corresponding to the at least two data groups in the target data table; and for each data group, filling the data rows in the data group into the data partition where the data group is located according to the specified sorting order of the sorting row to obtain the sorting result.

[0081] Further optionally, when determining the reference data corresponding to the at least two data groups, the sorting module 23 is specifically used to: for each data group, take the minimum data, maximum data, median or average of the sorted sequence contained in the data group as the reference data corresponding to the data group.

[0082] Further optionally, if the reference data corresponding to the data group does not belong to the data in the sorting sequence contained in the data group, the sorting module 23 is further configured to: after filling the data rows in the data group into the data partition where the data group is located, delete the data row where the reference data is located from the target data table.

[0083] In an optional embodiment, after sorting within each data group, the sorting module 23, when sorting between the at least two data groups, specifically performs the following: storing data rows in each data group into a temporary storage area according to the sorting sequence and a specified sorting order; determining the base data corresponding to the at least two data groups based on the data in the sorting sequence contained in each of the at least two data groups; sorting the base data corresponding to the at least two data groups according to the specified sorting order to obtain the sorting order between the at least two data groups; and sequentially reading data rows from the at least two data groups from the temporary storage area and writing them into a target data table according to the sorting order between the at least two data groups to obtain the sorting result.

[0084] In an alternative embodiment, such as Figure 2 As shown, the device further includes a configuration module 24. The configuration module 24 is configured to, based on the data attributes of the M data columns in the dataset, configure the data columns used to store the identification information of the object to be analyzed as clustered columns; and based on the data distribution characteristics of the remaining data columns in the dataset, configure the data columns whose ratio of the local data fluctuation range to the global data fluctuation range corresponding to the same object to be analyzed meets a set condition as sorted columns.

[0085] Optionally, when configuring the sorting sequence, the configuration module 24 is specifically configured to: in the data columns in the dataset that store the behavioral data generated by the object to be analyzed and / or the data columns that store the time information of the behavioral data generated by the object to be analyzed, in the data columns that meet the set conditions for the proportional relationship between the data fluctuation range of the same object to be analyzed and the global data fluctuation range, configure the data columns in the dataset that store the behavioral data generated by the object to be analyzed as sorting sequences.

[0086] The above describes the internal functions and structure of the data sorting device. In practice, this data sorting device can be implemented as a database engine, which can be applied to various database systems, such as traditional databases, cloud databases, data warehouses, and data lakes. Figure 3 As shown, the database engine includes: memory 31 and processor 32.

[0087] Memory 31 is used to store computer programs and can be configured to store various other data to support operations on the database engine. Examples of this data include instructions, data, various database statements, etc., for any application or method used to operate on the database engine.

[0088] The memory 31 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk or optical disk.

[0089] Processor 32, coupled to memory 31, is configured to execute a computer program in memory 31 for: in response to a sorting instruction, acquiring a dataset to be sorted, the dataset comprising N data rows and M data columns, the M data columns including at least a clustering column and a sorting column, where N and M are natural numbers greater than or equal to 2; grouping the N data rows in the dataset according to the clustering column to obtain at least two data groups; and sorting the data groups in the same sorting order as the sorting column between the at least two data groups and within each data group to obtain a sorting result.

[0090] In an optional embodiment, when the processor 32 groups N data rows in the dataset according to the cluster column, it is specifically used to: divide data rows with the same data or within the same data range in the cluster column into the same data group to obtain at least two data groups.

[0091] In an alternative embodiment, when the processor 32 sorts the data in the same sorting order as the sort sequence, both between and within each of the at least two data groups, it specifically performs the following:

[0092] According to the sorting sequence, the data is sorted in the specified sorting order between the at least two data groups, and then sorted within each data group to obtain the sorting result;

[0093] or

[0094] After sorting within each data group according to the specified sorting order, sorting is performed between the at least two data groups to obtain the sorting result.

[0095] Further optionally, after sorting among at least two data groups, when sorting within each data group, the processor 32 specifically performs the following steps: determining the base data corresponding to the at least two data groups based on the data in the sorting sequence contained in each of the at least two data groups; adding the base data corresponding to the at least two data groups to a target data table according to a specified sorting order to obtain the data partitions corresponding to the at least two data groups in the target data table; and for each data group, filling the data rows in the data group into the data partition where the data group is located according to the specified sorting order based on the sorting row to obtain the sorting result.

[0096] Further optionally, when determining the reference data corresponding to the at least two data groups, the processor 32 is specifically configured to: for each data group, take the minimum data, maximum data, median or average of the sorted sequence contained in the data group as the reference data corresponding to the data group.

[0097] Further optionally, if the reference data corresponding to the data group does not belong to the data in the sorted sequence contained in the data group, the processor 32 is further configured to: after filling the data row in the data group into the data partition where the data group is located, delete the data row where the reference data is located from the target data table.

[0098] In an optional embodiment, after sorting within each data group, the processor 32, when sorting between the at least two data groups, specifically performs the following: storing data rows in each data group into a temporary storage area according to the sorting sequence and a specified sorting order; determining the base data corresponding to the at least two data groups based on the data in the sorting sequence contained in each of the at least two data groups; sorting the base data corresponding to the at least two data groups according to the specified sorting order to obtain the sorting order between the at least two data groups; and sequentially reading data rows from the at least two data groups from the temporary storage area and writing them into a target data table according to the sorting order between the at least two data groups to obtain the sorting result.

[0099] In an optional embodiment, the processor 32 is further configured to configure the data column used to store the identification information of the object to be analyzed as a clustered column according to the data attributes of the M data columns in the dataset; and to configure the data column corresponding to the same object to be analyzed whose ratio of local data fluctuation range to global data fluctuation range meets the set conditions as a sorted column according to the data distribution characteristics of the remaining data columns in the dataset.

[0100] Further optionally, when configuring the sorting sequence, the processor 32 is specifically configured to: in data columns whose ratio of the data fluctuation range corresponding to the same object to be analyzed to the global data fluctuation range meets the set conditions, configure the data columns in the dataset used to store the behavioral data generated by the object to be analyzed and / or the data columns used to store the time information of the behavioral data generated by the object to be analyzed as sorting sequences.

[0101] Furthermore, such as Figure 3 As shown, the database engine also includes other components such as a communication component 33 and a power supply component 34. The power supply component 34 is responsible for providing power to the various components of the database engine. The power supply component 34 may include a power management system, one or more power supplies, and other components associated with generating, managing, and distributing power to the device where the power supply component is located. Figure 3 The diagram only shows a portion of the components and does not imply that the database engine only includes... Figure 3 The components shown.

[0102] Accordingly, embodiments of this application also provide a computer-readable storage medium storing a computer program, which, when executed by a processor, enables the processor to implement the steps in the above-described data sorting method embodiments.

[0103] Accordingly, this application also provides a computer program product, including a computer program / instructions, which, when executed by a processor, enable the processor to implement the steps in the above-described data sorting method embodiments.

[0104] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0105] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in one or more flowchart illustrations and / or one or more block diagrams.

[0106] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means that implement the functions specified in one or more flowcharts and / or one or more block diagrams.

[0107] These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process, such that the instructions, which execute on the computer or other programmable apparatus, provide steps for implementing the functions specified in one or more flowcharts and / or one or more block diagrams.

[0108] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0109] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0110] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0111] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0112] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A method of sorting data, characterized by, The method comprises: in response to a sorting instruction, obtaining a data set to be sorted, the data set comprising N data rows and M data columns, at least a cluster column and a sorting column being included in the M data columns, N and M being natural numbers greater than or equal to 2; the sorting instruction comprising a sorting requirement; configuring, according to data attributes of the M data columns in the data set, a data column in the data set for storing identification information of an object to be analyzed as the cluster column; configuring, according to data distribution characteristics of the remaining data columns in the data set, a data column whose proportional relationship between a local data fluctuation range and a global data fluctuation range corresponding to a same object to be analyzed meets a set condition as the sorting column; if the configured cluster column and sorting column do not meet the sorting requirement, readjusting or configuring the cluster column and the sorting column; grouping, according to the cluster column, the N data rows in the data set to obtain at least two data groups; sorting, according to the sorting column and in a same sorting order, between the at least two data groups and within each data group to obtain a sorting result.

2. The method of claim 1, wherein, The method of grouping, according to the cluster column, the N data rows in the data set to obtain at least two data groups comprises: dividing, into a same data group, data rows with same data or within a same data range in the cluster column to obtain at least two data groups.

3. The method of claim 2, wherein, The method of sorting, according to the sorting column and in a same sorting order, between the at least two data groups and within each data group to obtain a sorting result comprises: sorting, according to the sorting column and in a specified sorting order, between the at least two data groups and then within each data group to obtain a sorting result; or sorting, according to the sorting column and in a specified sorting order, within each data group and then between the at least two data groups to obtain a sorting result. The method of sorting, according to the sorting column and in a specified sorting order, between the at least two data groups and then within each data group to obtain a sorting result comprises:

4. The method of claim 3, wherein, determining, according to data in the sorting column included in each of the at least two data groups, reference data corresponding to the at least two data groups; adding, according to the specified sorting order, the reference data corresponding to the at least two data groups to a target data table to obtain data partitions corresponding to the at least two data groups in the target data table; for each data group, filling, according to the sorting column and in a specified sorting order, data rows in the data group into a data partition where the data group is located to obtain a sorting result. The method of determining, according to data in the sorting column included in each of the at least two data groups, reference data corresponding to the at least two data groups comprises:

5. The method of claim 4, wherein, for each data group, taking minimum data, maximum data, median or average of the sorting column included in the data group as the reference data corresponding to the data group. If the reference data corresponding to the data group does not belong to data in the sorting column included in the data group, the method further comprises:

6. The method of claim 5, wherein, ​ After filling the data rows in the data packet into the data partition where the data packet is located, the data row where the reference data is located is deleted from the target data table.

7. The method of claim 3, wherein, After sorting each data packet according to the sorting column in a specified sorting order, sorting between the at least two data packets to obtain a sorting result, including: Storing the data rows in each data packet into a temporary storage area according to the sorting column in a specified sorting order; Determining the reference data corresponding to the at least two data packets according to the data in the sorting column contained in each of the at least two data packets; Sorting the reference data corresponding to the at least two data packets according to a specified sorting order to obtain the sorting order between the at least two data packets; According to the sorting order between the at least two data packets, the data rows in the at least two data packets are read from the temporary storage area in sequence and written into the target data table to obtain a sorting result.

8. The method of claim 1, wherein, According to the data distribution characteristics of the remaining data columns in the data set, the data column corresponding to the same to-be-analyzed object and meeting the set condition of the proportional relationship between the local data fluctuation range and the global data fluctuation range is configured as a sorting column, including: Among the data columns corresponding to the same to-be-analyzed object and meeting the set condition of the proportional relationship between the local data fluctuation range and the global data fluctuation range, the data column used to store the behavior data generated by the to-be-analyzed object and / or the data column used to store the time information of the behavior data generated by the to-be-analyzed object in the data set is configured as a sorting column.

9. A database engine, characterized by, Including: a memory and a processor; The memory is used to store a computer program; the processor, coupled with the memory, is used to execute the computer program, so as to: In response to a sorting instruction, a data set to be sorted is obtained, the data set includes N rows and M columns, and the M columns include at least a clustering column and a sorting column, N and M are natural numbers greater than or equal to 2; the sorting instruction includes a sorting requirement; According to the data distribution characteristics of the remaining data columns in the data set, the data column corresponding to the same to-be-analyzed object and meeting the set condition of the proportional relationship between the local data fluctuation range and the global data fluctuation range is configured as a sorting column; if the configured clustering column and sorting column do not meet the sorting requirement, the clustering column and the sorting column are re-adjusted or configured; According to the clustering column, the N data rows in the data set are grouped to obtain at least two data packets; According to the sorting column in a specified sorting order, sorting between the at least two data packets and within each data packet to obtain a sorting result.

10. A computer readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, the processor is caused to implement the steps in the method of any one of claims 1-8.

11. A computer program product comprising computer programs / instructions, characterized in that, When the computer program / instruction is executed by the processor, the processor is caused to implement the steps in the method of any one of claims 1-8.

Images