CN114549042B

CN114549042B - Cost sharing method and system

Info

Publication number: CN114549042B
Application number: CN202210021935.2A
Authority: CN
Inventors: 屈恩
Original assignee: Beijing Yuannian Technology Co ltd
Current assignee: Beijing Yuannian Technology Co ltd
Priority date: 2022-01-10
Filing date: 2022-01-10
Publication date: 2022-10-18
Anticipated expiration: 2042-01-10
Also published as: CN114549042A

Abstract

The invention provides a ClickHouse-based cost allocation method and a system, wherein the cost allocation method comprises the following steps: mapping the service data with multiple dimensions to a table of a ClickHouse database, and performing corresponding configuration to establish a multidimensional cost allocation model, wherein each table has a corresponding scope for storing data in the corresponding step of allocation calculation; inputting an apportionment data source based on a multi-dimensional cost apportionment model and carrying out gradual apportionment calculation according to the step configuration preset by each table; and carrying out aggregation operation on the calculation result and outputting a final apportionment result. The cost allocation method provided by the embodiment of the invention matches the design target and characteristics of the ClickHouse database by mapping the service data to the table of the ClickHouse database, thereby fully exerting the high-efficiency data processing performance of the ClickHouse. And each table has different scope, so that the table can be more conveniently positioned and understood when used, and repeated development can be reduced through standard data layering.

Description

Cost sharing method and system

Technical Field

The invention relates to the field of computer data processing, in particular to a cost allocation method and system.

Background

The expense allocation means that the common expenses among all departments and all products are allocated so as to calculate the cost more scientifically and accurately, and further help enterprises to quickly adjust the product layout and the operation strategy and obtain better operation performance.

The cost is divided into one of the commonly used functional modules of the financial software. According to the technical platform division of the underlying apportionment calculation engine, the existing solutions mainly have two types: the first type is a product taking a traditional relational database as an apportionment calculation engine, and when the product is calculated, the apportionment logic is converted into SQL according to the allocation of apportionment steps to be executed on the database and obtain apportionment calculation result data; the second category is products using multidimensional internal memory database as apportionment calculation engine, which uses proprietary batch processing mechanism and calculation rule of multidimensional database to carry out apportionment calculation, and is commercial closed-source product

The existing apportionment calculation engine realized based on the traditional relational database has the disadvantages that the apportionment calculation period is long due to poor calculation performance; although the apportionment calculation engine realized based on the multidimensional database uses the advanced memory multidimensional database, the platform is a closed-source product and has high requirements on memory resources of a running environment.

In view of the above, the present invention is particularly proposed.

Disclosure of Invention

The invention provides a cost allocation method and a cost allocation system, which aim to solve the technical problems that an existing allocation computing system is long in computing period or high in requirement on memory resources of an operating environment.

In a first aspect of embodiments of the present invention, there is provided a cost apportionment method, including: mapping the service data with multiple dimensions to a table of a ClickHouse database, and performing corresponding configuration to establish a multidimensional cost allocation model, wherein each table has a corresponding scope for storing data in the corresponding step of allocation calculation; inputting an apportionment data source based on the multidimensional cost apportionment model and performing step-by-step apportionment calculation according to step configuration preset by each table; and carrying out aggregation operation on the calculation result and outputting a final apportionment result.

Optionally, in this embodiment of the present invention, the service data includes: data to be apportioned, apportionment factor data, apportionment result detail data and apportionment result summarized data; the table of the service data corresponding mapping is as follows: a data source table to be apportioned, an apportionment factor data table, a step apportionment result detail table and a step apportionment result summary table; wherein, different tables configure corresponding table engines according to the characteristics of the service data.

Optionally, in the embodiment of the present invention, the step apportionment result summary table configures a summingmercetetree clickwouse table engine, and the to-be-apportioned data source table, the apportionment factor data table, and the step apportionment result detail table all configure a mercetetree clickwouse table engine.

In an optional scheme of the embodiment of the invention, the data source table to be apportioned comprises a data base table to be apportioned, a data table to be apportioned and a data table to be apportioned in the step; wherein, the calculation results of different stages are stored in different tables; the step-by-step apportionment calculation of the input apportionment data source and the configuration of the steps preset by each table comprises the following steps: performing filtering query and batch data insertion on the data base table to be apportioned to obtain the data to be apportioned in the step; and performing multiplication operation and batch data insertion on the data table to be apportioned in the step to obtain the apportioned data in the step.

In an optional scheme of the embodiment of the present invention, the apportionment factor data table includes an apportionment factor base table, a step apportionment factor table, and a step apportionment proportion table; wherein the calculation results of the different stages are stored in different tables. The step-by-step apportionment calculation of the input apportionment data source and the configuration of the steps preset by each table comprises the following steps: carrying out filtering query and batch data insertion on the apportionment factor base table to obtain the apportionment factor table in the step; and performing polymerization calculation, double-table joint check, division calculation and batch data insertion on the step allocation factor table to obtain the step allocation ratio table.

Optionally, in the embodiment of the present invention, the inputting the apportioned data source and performing the gradual apportionment calculation according to the step configuration preset in each table further includes: and performing double-table joint check, multiplication calculation and batch data insertion according to the step proportion allocation data table and the step allocation ratio table to obtain the step allocation result detail data table.

In an optional scheme of the embodiment of the present invention, the inputting an apportionment data source and performing gradual apportionment calculation according to the step configuration preset in each table further includes: and inserting actual allocation data, actual allocation data and data to be allocated into the allocation result summary table in the step.

In an optional scheme of the embodiment of the present invention, the inputting an apportionment data source and performing gradual apportionment calculation according to the step configuration preset in each table further includes: and (4) carrying out polymerization operation on the step allocation result summary table to obtain the final allocation result of the step.

In a second aspect of the embodiments of the present invention, there is also provided a clickwouse-based cost apportionment system, including: the modeling module is used for mapping the service data with multiple dimensions to a table of a ClickHouse database so as to establish a multidimensional cost allocation model, wherein each table has a corresponding scope and is used for storing data in the corresponding step of allocation calculation; the calculation module is used for inputting an apportionment data source and performing step-by-step apportionment calculation according to the preset step configuration of each table, wherein the apportionment result of each step is stored in the corresponding database table; and the output module is used for carrying out aggregation operation on the calculation result and outputting a final apportionment result.

Finally, the present application provides a computer-readable storage medium having stored thereon instructions for enabling a processor to perform the above-described cost apportionment method when executed by the processor.

In summary, the cost allocation method provided by the application matches the design target and characteristics of the ClickHouse database by mapping the service data to the table of the ClickHouse database, thereby fully exerting the high-efficiency data processing performance of the ClickHouse. And each table has different scope, so that the tables can be more conveniently positioned and understood when used, and repeated development is reduced by standardizing data layering.

Additional features and advantages of embodiments of the invention will be described in the detailed description which follows.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and other drawings can be obtained by those skilled in the art without creative efforts.

FIG. 1 is a general flow diagram of a general inventive concept of a ClickHouse-based cost allocation method as provided by an embodiment of the present invention;

FIG. 2 is a model of a multi-dimensional model for cost sharing based on ClickHouse according to an embodiment of the present invention;

FIG. 3 is a schematic diagram of the configuration of the apportioning step in the ClickHouse-based cost apportionment method according to an embodiment of the present invention;

fig. 4 is a flowchart illustrating a modeling process in step S101 of the clickwause-based cost allocation method according to the embodiment of the present invention;

FIG. 5 is an exemplary diagram corresponding to modeling of FIG. 4 in a ClickHouse-based cost sharing method according to an embodiment of the present invention;

FIG. 6 is a flowchart of step S102 of the ClickHouse-based cost sharing method according to an embodiment of the present invention;

fig. 7 is a flowchart of a clickwouse-based cost allocation method according to an embodiment of the present invention, which performs gradual calculation according to a step configuration preset in each table.

Detailed Description

In order to make the aforementioned and other features and advantages of the invention more apparent, the invention is further described below with reference to the accompanying drawings. It is understood that the specific embodiments described herein are for purposes of illustration only and are not intended to be limiting.

Furthermore, the terms "first", "second" and "first" are used for descriptive purposes only and are not to be construed as indicating or implying relative importance or to implicitly indicate the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one such feature. In the description of the present invention, "a plurality" means at least two, e.g., two, three, etc., unless specifically limited otherwise.

In the description herein, references to the description of the term "one embodiment," "some embodiments," "an example," "a specific example," or "some examples," etc., mean that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the invention. In this specification, the schematic representations of the terms used above are not necessarily intended to refer to the same embodiment or example. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples. Furthermore, various embodiments or examples and features of different embodiments or examples described in this specification can be combined and combined by one skilled in the art without contradiction.

[ SUMMARY OF THE INVENTION ]

Referring to fig. 1, fig. 1 is a general flowchart of a clickwouse-based cost sharing method according to an embodiment of the present invention. The cost allocation method in the embodiment of the invention comprises the following steps:

step S101, mapping service data with multiple dimensions to a table of a ClickHouse database, and performing corresponding configuration to establish a multidimensional cost allocation model, wherein each table has a corresponding scope and is used for storing data of a corresponding step in allocation calculation;

step S102, inputting an apportionment data source based on a multi-dimensional cost apportionment model, and performing gradual apportionment calculation according to the step configuration preset by each table;

and step S103, carrying out aggregation operation on the calculation results and outputting final apportionment results.

In an example provided by the embodiment of the present invention, the service data mentioned in step S101 may include: the data to be apportioned, the apportionment factor data, the detailed data of the step apportionment results and the summarized data of the step apportionment results.

In the embodiment of the invention, the scope is defined as the accessible range of each table for the service data.

Further, the step S102 refers to "performing step-by-step calculation according to a preset step configuration of each table", which is based on the step S101, and each table is configured with a corresponding apportionment step configuration, so that each table processes a single problem to obtain a corresponding calculation result.

The aggregation operation mentioned in step S103 is a final apportionment result obtained by loading, fusing and preparing the data set through an aggregation function (the type of aggregation function is not limited in the general inventive concept, and can be customized), and calculating the grouping statistics.

It will be appreciated from the above steps that the cost apportionment method provided by embodiments of the present invention provides a general inventive concept that includes building a multidimensional cost apportionment model that takes advantage of the clickwouse database by mapping business data onto tables of the clickwouse database.

In the modeling direction, by setting each table to have its scope, the tables can be more conveniently located and understood when used, and different tables use the appropriate table engine according to the characteristics of the data. And each table is preset with corresponding steps for gradual calculation, and the whole calculation process is from inputting the apportionment data source to the multidimensional cost apportionment model to outputting the final apportionment result. The complex task is decomposed into a plurality of steps to be completed, each table only processes a single problem, the calculation steps are simplified, and data maintenance and error isolation are facilitated.

[ modeling stage ]

Specifically, referring to fig. 2, fig. 2 is a diagram illustrating an exemplary model of a multidimensional apportionment model in a clickwause-based cost apportionment method according to an embodiment of the present invention.

Fig. 2.1 is a schematic diagram of an OLAP Cube model of data to be shared in the clickwouse-based cost sharing method according to the embodiment of the present invention. In this example, the data to be apportioned includes at least 11 dimensions, in the diagram from top to bottom in the order of year, duration, caliber, version, channel, risk class, risk category, subject, book, cost center, institution dimensions.

Fig. 2.2 is a schematic view of a business model for distributing factor data in the clickwouse-based cost allocation method according to the embodiment of the present invention. In this example, the apportionment factor data is composed of 12 dimensions, and from top to bottom, are year, period, apportionment factor, caliber, version, channel, risk class, risk category, subject, account set, cost center, organization. Compared with the model of data to be apportioned in fig. 2.1, the business model of the apportionment factor data has one more dimension of the apportionment factor.

It should be understood by those skilled in the art that the data to be apportioned and the dimensions provided by the apportionment factor data provided by the embodiments of the present invention are merely exemplary, and the addition/reduction of dimensions or the modification of the types of dimensions based on the above description are all within the scope of the present invention.

Further, fig. 3 is a schematic diagram of a configuration of the apportioning step in the clickwouse-based cost apportionment method according to the embodiment of the present invention. An example apportionment step configuration for describing the apportionment computing process of the present invention.

Fig. 3.1 is a schematic diagram of a sender configuration in an exemplary apportionment step in the cost apportionment method based on clickwouse according to the embodiment of the present invention, and fig. 3.2 is a schematic diagram of a receiver configuration in an exemplary apportionment step in the cost apportionment method based on clickwouse according to the embodiment of the present invention.

It will be appreciated that the apportionment operation is divided into a sender and a receiver, i.e. an allocator and an importer that act as a cost apportionment.

With continued reference to fig. 3.1, the diagram shows the extent of data to be apportioned for the participating apportionment calculation's assignor sender. The "resource" column set in the embodiment of the present invention indicates the dimension participating in the apportionment calculation in the data to be apportioned in fig. 2.1, and the "member range" column defines the members actually participating in the apportionment calculation in each dimension of the data to be apportioned.

It can be understood as equivalent to performing a dicing operation on the members defined according to the member range column in the Cube (Cube: data Cube in multi-dimensional analysis) of the data to be apportioned, thereby performing data filtering on the members with the specified dimension participating in the apportionment calculation to serve as the data to be apportioned in the apportionment step.

Further, the manner in which embodiments of the present invention are arranged, if the member range is "(minimum descendant)" then this means that all members of the dimension have been selected. The upper scale represents the proportion of the apportionment step that only apportions the percentage of all the numbers to be apportioned after the dicing operation.

Continuing to refer to fig. 3.2, the range of the contributors or recipients participating in the contribution calculation and the setting of the contribution factor are shown. In the figure, the dynamic data aperture column defines the range of the apportionment factor data participating in the calculation of the apportionment step, and similarly, the members of the apportionment factor data actually participating in the apportionment calculation are defined, and the method is also equivalent to that the members defined by the dynamic data aperture column are cut into blocks in Cube of the apportionment factor data to be used as the basic data of the apportionment ratio calculation of the apportionment step.

In the mode set by the embodiment of the invention, 3 columns of 'object', 'member range' and 'translation resource range' determine the range of the apportionment calculation receiver; the dimension of the 'translation resource range' is selected to indicate that the dimension members of the sender are allocated to the dimension members of the receiver, so that the member range of the receiver does not need to be allocated; otherwise, if the mechanism dimension of the translation resource range is not selected, the member range needs to be specified, which means that the members which are not distinguished from the mechanism of the sender are distributed to all the final-stage members of the mechanism of the receiver; the 3 columns of the object, the member range and the translation resource range define the members of each dimension of the distributor actually participating in the distribution calculation, and the members are used as the distribution or receiving range of the distribution step. Wherein the setting of the share factor above the table in fig. 3.2 affects the calculation of the share ratio in the sharing process.

Further, fig. 4 is a flowchart illustrating a modeling process in step S101 of the clickwouse-based cost allocation method according to the embodiment of the present invention. The modeling process of the multi-dimensional apportionment model of the invention based on the business model of fig. 2 is described. Fig. 5 is an exemplary diagram corresponding to modeling in fig. 4 in the clickwouse-based cost apportionment method according to an embodiment of the present invention. Fig. 5.1 to 5.8 are exemplary diagrams of models built in various steps, respectively.

Specifically, step S101a represents modeling of the to-be-apportioned number. The modeling results include the tables in fig. 5.1, fig. 5.2, fig. 5.3.

Step 101b represents split factor modeling. The modeling results include the tables in fig. 5.4, fig. 5.5, fig. 5.6.

Step 101c represents step amortization detail result modeling. The modeling results include the table in fig. 5.7.

Step 101d represents the step apportionment summary result modeling. The modeling results include the table in fig. 5.8.

In the above four steps, the tables respectively corresponding to the mapping may be divided into: a data source table to be apportioned, an apportionment factor data table, a step apportionment result detail table and a step apportionment result summary table; namely, the model corresponding to the business data mapped to the table of the clickwouse database.

In particular, fig. 5.1, fig. 5.2, fig. 5.3 are tables of data sources to be apportioned. Fig. 5.1 is a data base table to be apportioned, which stores all numbers to be apportioned in the whole range and is the only source of the numbers to be apportioned in all the apportioning steps.

The fields of the data base table to be apportioned should correspond to the dimensions of the OLAP Cube model of the data to be apportioned in fig. 2.1 one by one, and a number field to be apportioned is added at last to store the data to be apportioned.

FIG. 5.2 is a step to be apportioned table, which has the same structure as the base table of the data to be apportioned in FIG. 5.1, and shows that an OLAP dicing operation is performed from the base table of the data to be apportioned; fig. 5.3 is a step-to-scale apportionment number table showing the apportionment numbers calculated from the apportionment ratios configured by the sender of the apportionment step of fig. 3.1, with one more apportionment coefficient field compared to the structure of the table of fig. 5.2.

Further, FIG. 5.4,5.5,5.6 is a table of apportionment factors. Fig. 5.4 is an apportionment factor base table, which stores all the apportionment factor numbers in the whole organization range, and is the only source of the apportionment factor numbers in all the apportionment steps, and the fields of the table correspond to the dimensions of the OLAP Cube model of the apportionment factor data in fig. 2.2 one by one, and finally, a field to be apportioned is added; FIG. 5.5 is a table of step partition factors, the structure of which is the same as that of the partition factor base table of FIG. 5.4, showing that an OLAP dicing operation is performed from the partition factor base table of FIG. 5.4; fig. 5.6 is a step share ratio table storing the share ratios calculated based on the data in fig. 5.5 and the configuration of the share factors of the sharing step in fig. 3, with one more share ratio field compared to the table in fig. 5.5.

FIG. 5.7 is a step apportionment result list storing the detail apportionment result data calculated according to the apportionment step configuration in FIG. 3. The table structure comprises a field of a sending party and a field of a receiving party, and the apportionment result field stores the calculated apportionment result number. Each row of data in the table represents an apportionment result, wherein a sender field represents an apportioned entity, a receiver field represents an apportioned entity, and an apportionment result field represents the actual number of apportionment results.

FIG. 5.8 is a summary of step apportionment results. The final number of the results of the apportionment obtained by summarizing the numbers in the table of the apportionment results of fig. 5.7 according to the apportionment coefficients, i.e., "should be apportioned", "actually apportioned", and "actually apportioned".

As can be seen from the above, the range of action of the different tables is different: the number base table to be apportioned and the apportionment factor base table are tables which are common to the whole apportionment module and are irrelevant to the specific apportionment steps; the other tables store the processing results of different processing stages in the specific apportionment step, respectively.

Further, the different tables use a suitable ClickHouse table engine depending on the nature of the data: the summary apportionment results table uses the SummingMergeTree clickwouse table engine to fully utilize its ability to aggregate the same primary key value records. Other tables use the MergeTree ClickHouse table engine for both data insertion and query scenarios.

[ calculation stage ]

Referring to fig. 6, fig. 6 is a flowchart of step S102 in the clickwouse-based cost allocation method according to the embodiment of the present invention.

Based on the established model, the data source table to be apportioned comprises a data base table to be apportioned, a data table to be apportioned and a data table to be apportioned in a step;

further, the calculation results of the different stages are stored in different tables, i.e. the processing result data of the different stages of the apportioning step are stored in different tables.

It can be understood that by storing the processing result data of different stages in different tables so as to maintain the accuracy of the data and isolate data errors, after a problem occurs in the data, all the data can be repaired without starting from the problem place.

In the embodiment of the invention, the step-by-step apportionment calculation according to the preset step configuration of each table by inputting the apportionment data source comprises the following steps:

s102a, performing filtering query and batch data insertion on a data base table to be apportioned to obtain a data table to be apportioned;

and S102b, performing multiplication operation and batch data insertion on the data table to be apportioned to obtain the apportioned data table in the step.

Further, the apportionment factor data table comprises an apportionment factor base table, a step apportionment factor table and a step apportionment proportion table; inputting an apportionment data source and carrying out gradual apportionment calculation according to the preset step configuration of each table comprises the following steps:

s102c, carrying out filtering query and batch data insertion on the spreading factor base table to obtain a spreading factor table in the step;

and step S102d, carrying out polymerization calculation, double-table joint check, division calculation and batch data insertion on the apportionment factor table to obtain the apportionment ratio table.

Inputting an apportionment data source and performing gradual apportionment calculation according to the preset step configuration of each table further comprises:

and S102e, performing double-table joint check, multiplication calculation and batch data insertion according to the step proportion allocation data table and the step allocation occupation ratio table to obtain a step allocation result list.

s102f, inserting actual distribution data and actual distribution data into the distribution result summary table;

s102g, inserting data to be distributed into the step distribution result summary table;

and step S102h, carrying out aggregation operation on detailed data in the step apportionment result summary table to obtain a final apportionment result.

Referring to fig. 7, fig. 7 is a flowchart illustrating a step-by-step calculation according to a step configuration preset in each table in the clickwouse-based cost sharing method according to an embodiment of the present invention. The embodiment of the present invention is illustrated with the allocation step configuration shown in fig. 3 (a specific solution corresponding to the above steps S102a to S102 g), in which a rectangle in the flowchart in fig. 7 represents data and an ellipse represents a data conversion operation. The following steps are described in detail:

and 1021, performing a first block cutting operation on the bottom table of the data to be apportioned according to the allocation of the apportioning party of the apportioning step to obtain the number table to be apportioned of the step, which is used as the data to be apportioned of the apportioning step. This step is characterized by filtering the query and inserting the data in batches. Wherein the filtering conditions for the caliber and institution fields are from the member scope configuration of the sender of the apportionment step in fig. 3.

Step 1022 is to calculate a proportional product of the data to be apportioned in the apportioning step according to the proportion configured by the assignor in the apportioning step, and use the calculated proportional product as the data to be apportioned according to the proportion in the apportioning step. This step is characterized by multiplication operations, inserting data in batches. The apportionment coefficient field takes the value of "raw data", and the apportionment ratio value corresponds to the ratio configured by the sender in fig. 3.1. The computational feature of this step is batch insertion.

And 1023, according to the dynamic data caliber and the factor configuration of the distributor in the distributing step, performing block operation on the distribution factor table to obtain the distribution factor table in the step, and using the distribution factor table as the distribution factor data in the distributing step. This step is characterized by filtering the query and inserting the data in batches. The filtering condition for the split factor comes from the factor setting in fig. 3.2 (i.e. premium revenue-all); the filtering conditions for the account set, cost center, version and aperture fields come from the configuration of the dynamic data aperture of the apportionment step in fig. 3.2.

Step 1024, calculating the allocation ratio according to the configuration of the translation resource range column of the allocating party in the allocating step, and using the allocation ratio as the allocation ratio data in the allocating step. The step is characterized by polymerization calculation, double-table joint check, division calculation and batch data insertion. This step is divided into two sub-steps, a first sub-step and a second sub-step as an example. The first sub-step inserts the original data of the apportionment factors as the basic data of the second sub-step. The second substep, calculating the share ratio, requires two temporary tables: DETAIL table and AGGRE table, and taking quotient of apportionment ratio field value after double-table connection. The DETAIL table stores the base DETAIL data of the allocation factors; the AGGRE table stores aggregated data, with the grouping fields being all translation dimensions, the aggregation attribute being the "apportionment duty" value, and the non-translation dimension "mechanism" taking the "total" value. The condition for a two-table join is all other dimensions except the non-translation dimension.

And step 1025, calculating step allocation detail results based on the step proportional allocation number table and the step allocation ratio table according to the configuration of the dynamic data aperture column, the member range column and the translation resource range column in the allocation step. The step is characterized by double-table joint check, multiplication calculation and batch data insertion. And the double tables are connected to obtain the detailed result of the product calculation step, and the two connected tables are a number table to be apportioned according to the step and an apportionment ratio table in the step respectively. The connection condition is a field corresponding to a dimension with a null dynamic data aperture value in all translation dimensions. The field of the receiver only needs the field corresponding to the non-translation dimension, and because the value of the field corresponding to the translation dimension in each row of records in the table is consistent between the receiver and the sender, reducing the field value of the receiver of the records can reduce the disk and memory overhead and optimize the query performance. The calculation process has the effects that the number to be shared in the table EX _ DYN _ JOB _ DATA is shared on a plurality of final stage mechanisms according to the sharing ratio in the table EX _ DYN _ JOB _ DRIVER; that is, one line of DATA in the table EX _ DYN _ JOB _ DATA is shared among a plurality of lines in the table EX _ detailedjesults, indicating that a plurality of receiving agencies have received the shared number from the same transmitting agency; also, the rows of DATA in the table EX _ DYN _ JOB _ DATA may be apportioned to the same row in the table EX _ detailedjesults, indicating that one receiving entity received apportionment from multiple different sending entities.

And a step 1026 of inserting the actual data distributed by the receiver in the step distribution result list into the distribution result summary list. The step is characterized by batch insertion of data, namely, the apportionment data of a receiver is inserted into an apportionment result summary table. In the configuration of the apportionment step, the fields except the mechanism field are all translation fields, and the field values of the translation field sender and the corresponding receiver are the same, so that the sender field is used except the non-translation mechanism field in the step, the calculation and storage amount are reduced, and the performance is improved. A plurality of sending agencies can be shared by the same receiving agency, so that a plurality of pieces of actual distribution data with the same field value except for the distribution result field may exist in the table EX _ RESULTS, indicating that one receiving agency receives the distribution numbers from a plurality of different sending agencies.

It can be understood that when the data is calculated and allocated in detail, the service attribute of the translation dimension in allocation step configuration is fully utilized, and the translation field value of the receiver is stored in the corresponding field of the sender, so that the data redundancy is reduced, the occupation of disk and memory resources is reduced, and the calculation performance is improved.

Step 1027 inserts the actual data distributed by the sender in the detailed list of the result of the step into the summary list of the result of the distribution. This step is characterized by bulk insertion of data. And inserting the apportionment data of the sender into the apportionment result summary table. Since one line of DATA in the table EX _ DYN _ JOB _ DATA is shared among multiple lines in the table EX _ detailedjrests, there may be multiple pieces of actual shared DATA in the table EX _ RESULTS with the same field value except for the shared result field, indicating that one sender entity is shared among multiple different receiver entities.

Step 1028 inserts the data that should be distributed by the sender in the number-to-be-distributed table in step into the result-of-distribution summary table. The step is characterized in that data are inserted in batches, and original apportionment data of a sender are inserted into an apportionment result summary table as data to be apportioned.

Step 1029, performing aggregation calculation on the detailed data in the step allocation result summary table to generate step summary allocation result data. This step is characterized by triggering the aggregation computation of the SummingMergeTree table engine. The step triggers the aggregation calculation of the apportionment RESULTS of the table EX _ RESULTS SummingMergeTree table engine, aggregates the apportionment result values with the same field values except the apportionment result field, and generates the final summary apportionment result data of the apportionment step.

The embodiment of the application performs batch insertion operation in each step, does not modify data, group aggregation and result combination, and has simple calculation logic. These characteristics all match the design goals and characteristics of the clickwouse database, thereby fully exerting the high-efficiency data processing performance of the clickwouse.

The factor cost allocation method based on the ClickHouse has the performance superior to that of an allocation engine based on a memory multidimensional database through actual comparison tests.

The test items are the computational time and memory resource usage to perform the apportionment step in FIG. 3. The number of the test apportionment steps to be apportioned is 32 ten thousand, and the number of the apportionment factors in the apportionment steps is 440 ten thousand.

Test results show that the apportionment time performance of the ClickHouse is about 10 times of the apportionment engine performance based on the memory multidimensional database; in the aspect of memory resource occupation, the performance of the ClickHouse is 12 times smaller than that of a sharing engine based on a memory multi-dimensional database. In order to ensure the comparability of the test results, the same test environment is used in the test process.

Therefore, through comparison, under the condition of the same data and the same apportionment logic, the ClickHouse occupies less memory resources, and the embodiment of the invention establishes the multidimensional cost apportionment model by mapping the service data onto the table of the ClickHouse database, matches the design target and the characteristics of the ClickHouse database, and fully exerts the high-efficiency data processing performance of the ClickHouse. And each table has different scope, so that the tables can be more conveniently positioned and understood when used, and repeated development is reduced by standardizing data layering.

In a second aspect of the embodiments of the present invention, there is also provided a clickwouse-based cost apportionment system, including: the modeling module is used for mapping the service data with multiple dimensions to a table of a ClickHouse database and carrying out corresponding configuration to establish a multidimensional cost allocation model, wherein each table has a corresponding scope; and the calculation module is used for inputting an apportionment data source based on the multi-dimensional cost apportionment model and performing gradual apportionment calculation according to the step configuration preset by each table.

The system is used for inheriting the implementation and functional features of the method embodiments, and can be understood as encapsulating codes in different modules defined by functions to realize code modularization, and others are not described in too much here.

Although embodiments of the present invention have been shown and described above, it will be understood that the above embodiments are exemplary and not to be construed as limiting the present invention, and that changes, modifications, substitutions and alterations can be made to the above embodiments by those of ordinary skill in the art within the scope of the present invention.

As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may 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, and the like) having computer-usable program code embodied therein.

The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may 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 the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

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 the flowchart flow or flows and/or block diagram block or blocks.

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

The memory may include forms of volatile memory in a computer readable medium, random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). The memory is an example of a computer-readable medium.

Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, 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 technology, compact disc read only memory (CD-ROM), digital Versatile Disks (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium, which can be used to store information that can be accessed by a computing device. As defined herein, a computer readable medium does not include a transitory computer readable medium such as a modulated data signal and a carrier wave.

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

The above are merely examples of the present application and are not intended to limit the present application. Various modifications and changes may occur to those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.

Claims

1. A ClickHouse-based cost apportionment method, the cost apportionment method comprising:

mapping the service data with multiple dimensions to a table of a ClickHouse database, and performing corresponding configuration to establish a multidimensional cost allocation model, wherein each table has a corresponding scope of action and is used for storing data in the corresponding step of allocation calculation;

inputting an apportionment data source based on the multi-dimensional cost apportionment model and carrying out gradual apportionment calculation according to the step configuration preset by each table;

performing aggregation operation on the calculation result and outputting a final apportionment result;

the service data comprises: data to be apportioned, apportionment factor data, apportionment result detail data and apportionment result summarized data; the table of the service data corresponding mapping is as follows: a data source table to be apportioned, an apportionment factor data table, a step apportionment result detail table and a step apportionment result summary table;

wherein, different tables configure corresponding table engines according to the characteristics of the service data.

2. The cost sharing method according to claim 1, wherein the step apportionment result summary table configures a summingmercetetree ClickHouse table engine, and the to-be-apportioned data source table, the apportionment factor data table, and the step apportionment result detail table configure a mercetetree ClickHouse table engine.

3. The cost sharing method according to claim 1, wherein the data source table to be shared comprises a data base table to be shared, a data table to be shared and a data table to be proportionally shared, and the data source to be shared comprises data of the number to be shared and the sharing factor; wherein, the calculation results of different stages are stored in different tables; the step-by-step apportionment calculation is performed according to the preset step configuration of each table by inputting the apportionment data source, and comprises the following steps:

performing filtering query and batch data insertion on the data base table to be apportioned to obtain the data to be apportioned in the step;

and performing multiplication operation and batch data insertion on the data table to be apportioned in the step to obtain the apportioned data.

4. The cost sharing method according to claim 1, wherein the sharing factor data table includes a sharing factor base table, a step sharing factor table, a step sharing proportion table; the step-by-step apportionment calculation is performed by inputting apportionment data sources and configuring according to steps preset by each table, and comprises the following steps:

carrying out filtering query and batch data insertion on the apportionment factor base table to obtain the apportionment factor table in the step;

and performing polymerization calculation, double-table joint check, division calculation and batch data insertion on the step allocation factor table to obtain the step allocation ratio table.

5. The cost sharing method according to claim 4, wherein the inputting of the sharing data source and the step-by-step sharing calculation according to the step configuration preset by each table comprises:

and performing double-table joint check, multiplication calculation and batch data insertion according to the step proportion allocation data table and the step allocation ratio table to obtain the step allocation result list.

6. The cost sharing method according to claim 5, wherein the inputting of the sharing data source and the step-by-step sharing calculation according to the step configuration preset by each table comprises:

and inserting actual allocation data, actual allocation data and data to be allocated into the allocation result summary table in the step.

7. The cost sharing method according to claim 5, wherein the inputting of the sharing data source and the step-by-step sharing calculation according to the step configuration preset by each table comprises: and carrying out aggregation operation on the step apportionment result summary table to obtain a final apportionment result of the step.

8. A ClickHouse-based cost sharing system, comprising:

the modeling module is used for mapping the service data with multiple dimensions to a table of a ClickHouse database so as to establish a multidimensional cost allocation model, wherein each table has a corresponding scope of action and is used for storing data in the corresponding steps of allocation calculation;

the calculation module is used for inputting an apportionment data source and carrying out step-by-step apportionment calculation according to the preset step configuration of each table, wherein the apportionment result of each step is stored in the corresponding database table;

the output module is used for carrying out aggregation operation on the calculation result and outputting a final apportionment result;

9. A computer readable storage medium having stored thereon instructions for enabling a processor to carry out the cost sharing method of any one of claims 1 to 7 when executed by the processor.