Method, system and equipment for querying asset depreciation data and medium

Abstract

The invention provides a method, system and equipment for querying asset depreciation data and a medium, and the method comprises the steps: creating a depreciation data table corresponding to a year according to the year, and storing the depreciation data of assets over the years in the corresponding depreciation data table; compressing the depreciation data of the assets over the years; carrying out depreciation data partitioning on the database by taking the financial division as a partitioning key; establishing data query entries over the years, and querying the asset depreciation data in the database by calling the query entries of the corresponding years; according to the method, the problem that a user cannot obtain a response in a reasonable corresponding time when querying reports such as general ledger or unit depreciation details or performing counting and depreciation operation is solved, and the speed of querying and updating asset depreciation data is increased.

Description

Technical Field

[0001] The present invention relates to the field of information data processing and storage of software development, and in particular to a method, system, device and medium for querying asset depreciation data. Background Art

[0002] Asset depreciation / amortization refers to the gradual decrease in the value of assets as the service life of administrative institutions increases during the use of assets. Amortization is calculated for intangible assets, while depreciation is calculated for tangible assets.

[0003] In the budget management integrated administrative and public institution asset management system, the average life method is generally used to accrue depreciation / amortization of assets. Every month, the depreciation data such as the monthly depreciation number, the accumulated depreciation months, and the accumulated depreciation of the assets involved in the depreciation / amortization are generated and saved in the database. The software needs to query the depreciation details data and display it in the unit's depreciation details and asset general ledger.

[0004] The traditional provincial-level administrative and public institution asset management system generally adopts an independent deployment solution for each fiscal division, and the data is stored in each division’s own database. The administrative and public institution asset management system with integrated budget management adopts a centralized deployment approach, and the data for the entire province is uniformly stored in one database.

[0005] Since each administrative unit (software user, the same below) needs to make depreciation / amortization for assets once a month, the amount of monthly depreciation data is basically equal to the number of unit assets. The data growth rate is linear and may continue to increase with the continuous growth of unit assets.

[0006] As the number of depreciation months continues to increase, the amount of data in the database's depreciation details, supplementary depreciation details and other tables will continue to grow, and the pressure on database queries will become greater and greater. If there are more than 20,000 units in a province, and each unit has an average of 1,000 assets (conservative estimate) participating in depreciation, the amount of data in the depreciation details alone will reach 20,000*1000*12=240,000,000 per year.

[0007] When the amount of data in a single table reaches billions, it will cause great pressure on database query and read and write operations, resulting in users being unable to get a response within a reasonable time when querying reports such as the general ledger or unit depreciation details or performing depreciation accrual operations. Summary of the invention

[0008] The technical problem to be solved by the present invention is to provide a method, system, device and medium for querying asset depreciation data. After archiving the depreciation data of previous years, compression operations are performed, and then the depreciation data is partitioned, so as to solve the problem that users cannot get a response within a reasonable corresponding time when querying reports such as general ledgers or unit depreciation details or performing depreciation accrual operations, thereby improving the speed of querying and updating asset depreciation data.

[0009] In a first aspect, the present invention provides a method for querying asset depreciation data, comprising:

[0010] Step 1: Create a depreciation data table for the corresponding year according to the year, and store the asset depreciation data over the years in the corresponding depreciation data table;

[0011] Step 2, compressing the asset depreciation data over the years, the compression specifically includes: calculating the total depreciation of 12 months in a specified year to obtain the annual depreciation value of the specified year, adding the number of depreciated months at the beginning of the specified year to the number of depreciation times in the specified year, calculating the total number of depreciated months at the end of the year, adding the accumulated depreciation value at the beginning of the year to the total depreciation value of the specified year, and obtaining the accumulated depreciation value of the asset at the end of the year; storing the annual depreciation value, the total number of depreciated months and the accumulated depreciation value in the corresponding depreciation data table;

[0012] Step 3: partition the database into depreciation data partitions using the fiscal division as the partition key;

[0013] Step 4: Establish a query entry for historical data, and query the asset depreciation data in the database by calling the query entry for the corresponding year.

[0014] Furthermore, the depreciation data table in step 1 includes an asset depreciation / amortization table, a depreciation detail table, a supplementary depreciation table, and a supplementary depreciation detail table;

[0015] The asset depreciation / amortization table is a data table that stores basic information, including the accounting date and the current depreciation amount;

[0016] The depreciation details table is a data table storing detailed information, including service life, current depreciation amount, monthly depreciation amount, and current depreciation amount. The depreciation details table corresponds to the asset depreciation / amortization table;

[0017] The supplementary depreciation table is a data table for storing supplementary depreciation and reduction business document information, and the supplementary depreciation and reduction business document information includes the accounting date and the current depreciation amount;

[0018] The supplementary depreciation details table is a data table that stores the detailed information of supplementary depreciation and depreciation reduction assets. The detailed information of supplementary depreciation and depreciation reduction assets includes the service life, depreciation months, monthly depreciation amount, current depreciation amount, and accumulated depreciation data.

[0019] Furthermore, the database partition described in step 3 uses the List partition of the database.

[0020] In a second aspect, the present invention provides a system for querying asset depreciation data, comprising:

[0021] The module for archiving asset depreciation data over the years creates a depreciation data table for the corresponding year according to the year, and stores the asset depreciation data over the years in the corresponding depreciation data table;

[0022] The asset depreciation data compression module compresses the asset depreciation data over the years. The compression is specifically as follows: the total depreciation of the 12 months of the specified year is calculated to obtain the annual depreciation value of the specified year, the number of depreciated months at the beginning of the specified year is added to the number of depreciation times of the specified year, the total number of depreciated months at the end of the year is calculated, the accumulated depreciation value at the beginning of the year is added to the total depreciation value of the specified year, and the accumulated depreciation value at the end of the year is obtained; the annual depreciation value, the total number of depreciated months and the accumulated depreciation value are stored in the corresponding depreciation data table;

[0023] Depreciation data partitioning module, which partitions the database into depreciation data according to the fiscal division as the partition key;

[0024] The historical data query module establishes a historical data query entry, and queries the asset depreciation data in the database by calling the query entry of the corresponding year.

[0025] Furthermore, the depreciation data table of the module for archiving asset depreciation data over the years includes asset depreciation / amortization table, depreciation detail table, supplementary depreciation table, supplementary depreciation detail table;

[0026] The asset depreciation / amortization table is a data table that stores basic information, including the accounting date and the current depreciation amount;

[0027] The depreciation details table is a data table storing detailed information, including service life, current depreciation amount, monthly depreciation amount, and current depreciation amount. The depreciation details table corresponds to the asset depreciation / amortization table;

[0028] The supplementary depreciation table is a data table for storing supplementary depreciation and reduction business document information, and the supplementary depreciation and reduction business document information includes the accounting date and the current depreciation amount;

[0029] The supplementary depreciation details table is a data table that stores the detailed information of supplementary depreciation and depreciation reduction assets. The detailed information of supplementary depreciation and depreciation reduction assets includes the service life, depreciation months, monthly depreciation amount, current depreciation amount, and accumulated depreciation data.

[0030] Furthermore, the depreciation data partitioning module uses the List partitioning of the database.

[0031] In a third aspect, the present invention provides an electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method described in the first aspect when executing the program.

[0032] In a fourth aspect, the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described in the first aspect.

[0033] One or more technical solutions provided in the embodiments of the present invention have at least the following technical effects or advantages:

[0034] The embodiments of the present application provide a method, system, device and medium for querying asset depreciation data. After archiving the depreciation data of previous years, a compression operation is performed, and then the depreciation data is partitioned, so as to solve the problem that users cannot get a response within a reasonable corresponding time when querying reports such as general ledgers or unit depreciation details or performing depreciation accrual operations, thereby improving the speed of querying and updating asset depreciation data.

[0035] The key point of the present invention is that after archiving the depreciation data of previous years, the data needs to be compressed to ensure that there is a total of the depreciation data of previous years in the current year. In this way, the complete depreciation data can be queried when querying. The compression order should be from the back to the front, and the total of the accumulated depreciation should be generated year by year. The compression ratio of the first year is about 12:1, and the compression ratio of the second year is about 24:1.

[0036] If depreciation is calculated based on 5 years, the data volume of a single table should be 240,000,000 before optimization. After optimization, the depreciation data for each year is about 48,000,000, and the initial depreciation data for the sixth year is only 1 depreciation number for each asset, which is 2,400,000. The data of each partition is 240,000. When querying data from previous years, decide which archive table to query based on the budget year currently being queried.

[0037] After archiving, compressing, and partitioning the depreciation data in the above manner, the query time was optimized from an average of 65 seconds to an average of 2.5 seconds, and the speed of depreciation / amortization was optimized from 16 seconds to 1.8 seconds, which significantly improved the query and update speed. At the same time, the database overhead was also reduced.

[0038] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The present invention will be further described below in conjunction with embodiments with reference to the accompanying drawings.

[0040] Figure 1 is a flow chart of the method of the present invention;

[0041] Figure 2 It is a structural schematic diagram of the device of the present invention; DETAILED DESCRIPTION

[0042] The embodiments of the present application provide a method, system, device and medium for querying asset depreciation data to solve the problem that users cannot get a response within a reasonable corresponding time when querying reports such as general ledger or unit depreciation details or performing depreciation accrual operations.

[0043] Embodiment 1

[0044] First, the database tables involved in asset depreciation of administrative institutions are explained:

[0045] 1. Asset depreciation / amortization table (FA_DEPREC): This table stores the basic information of depreciation orders, which are stored by unit, mainly including the accounting date, the current depreciation amount, etc.

[0046] 2. Depreciation Details Table (FA_DEPREC_DETAIL): This table stores detailed information about depreciable assets, including useful life, depreciation already provided, monthly depreciation amount, current depreciation amount, etc., corresponding to the asset depreciation / amortization table.

[0047] 3. Additional Depreciation Table (FA_INC_CUT): This table stores the basic information of additional depreciation and reduction business documents, mainly including the accounting date, the current depreciation amount, etc. Through this business, you can make additional depreciation or reduce depreciation for assets with over- or under-depreciation.

[0048] 4. Additional Depreciation Detail Table (FA_INC_CUT_DETAIL): This table stores detailed information on additional depreciation and reduction of depreciable assets, including useful life, depreciation months, monthly depreciation amount, current depreciation amount, accumulated depreciation, etc., corresponding to the additional depreciation and reduction table of the asset information card.

[0049] This embodiment provides a method for querying asset depreciation data, such as Figure 1 As shown:

[0050] Step 1: Create a depreciation data table for the corresponding year according to the year, and store the asset depreciation data over the years in the corresponding depreciation data table;

[0051] Depreciation data is generally stored by month, but the budget management integrated administrative and public institution management system is managed by budget year, so consider backing up the asset depreciation data of previous years by year;

[0052] The depreciation data table specifically includes an asset depreciation / amortization table, a depreciation detail table, a supplementary depreciation table, and a supplementary depreciation detail table;

[0053] The asset depreciation / amortization table is a data table that stores basic information, including the accounting date and the current depreciation amount;

[0054] The depreciation details table is a data table storing detailed information, including service life, current depreciation amount, monthly depreciation amount, and current depreciation amount. The depreciation details table corresponds to the asset depreciation / amortization table;

[0055] The supplementary depreciation table is a data table for storing supplementary depreciation and reduction business document information, and the supplementary depreciation and reduction business document information includes the accounting date and the current depreciation amount;

[0056] The supplementary depreciation details table is a data table that stores the detailed information of supplementary depreciation and depreciation reduction assets. The detailed information of supplementary depreciation and depreciation reduction assets includes the service life, depreciation months, monthly depreciation amount, current depreciation amount, and accumulated depreciation data.

[0057] Step 2, compress the asset depreciation data over the years;

[0058] Under normal depreciation conditions, an asset will generate one depreciation record per month, and 12 records per year. For the asset's depreciation data from previous years, multiple records can be compressed into one record.

[0059] The compression is specifically as follows: calculating the total depreciation of 12 months in a specified year to obtain the annual depreciation value of the specified year, adding the number of depreciated months at the beginning of the specified year to the number of depreciation times in the specified year to calculate the total number of depreciated months at the end of the year, adding the accumulated depreciation value at the beginning of the year to the total depreciation value of the specified year to obtain the accumulated depreciation value of the asset at the end of the year; storing the annual depreciation value, the total number of depreciated months and the accumulated depreciation value in the corresponding depreciation data table;

[0060] Step 3, depreciation data partitioning, partitioning the database into depreciation data using the fiscal division as the partition key;

[0061] The partition strategy is mainly to use the List partition of the database. Since the table has been divided according to the year, considering that the integrated budget management is mainly managed according to the financial division, the financial division is used as the partition key, and all districts under the same city are divided into one partition. Calculated based on 10 cities in a province, the average depreciation of each partition is 240000000 / 10=24000000.

[0062] Based on the same inventive concept, the present application also provides a system corresponding to the method in Example 1, see Example 2 for details.

[0063] Embodiment 2

[0064] In this embodiment, a system for querying asset depreciation data is provided, such as Figure 2 As shown, including:

[0065] The module for archiving asset depreciation data over the years creates a depreciation data table for the corresponding year according to the year, and stores the asset depreciation data over the years in the corresponding depreciation data table;

[0066] The asset depreciation data compression module compresses the asset depreciation data over the years. The compression is specifically as follows: the total depreciation of the 12 months of the specified year is calculated to obtain the annual depreciation value of the specified year, the number of depreciated months at the beginning of the specified year is added to the number of depreciation times of the specified year, the total number of depreciated months at the end of the year is calculated, the accumulated depreciation value at the beginning of the year is added to the total depreciation value of the specified year, and the accumulated depreciation value at the end of the year is obtained; the annual depreciation value, the total number of depreciated months and the accumulated depreciation value are stored in the corresponding depreciation data table;

[0067] Depreciation data partitioning module, which partitions the database into depreciation data according to the fiscal division as the partition key;

[0068] The historical data query module establishes a historical data query entry, and queries the asset depreciation data in the database by calling the query entry of the corresponding year.

[0069] Since the system introduced in the second embodiment of the present invention is a system used to implement the method of the first embodiment of the present invention, those skilled in the art can understand the specific structure and deformation of the device based on the method introduced in the first embodiment of the present invention, so it is not described here in detail. All systems used in the method of the first embodiment of the present invention belong to the scope of protection of the present invention.

[0070] Embodiment 3

[0071] In this embodiment, a method for querying asset depreciation data is provided, as shown in the first embodiment; wherein the statement method for creating a partition table is:

[0072] Taking a provincial level as an example, when entering the new year's account period, a corresponding depreciation detail table needs to be created. The corresponding statement for creating a partition table is:

[0073]

[0074]

[0075]

[0076] MOF_DIV_CODE is the fiscal division code. In the example, the fiscal division of a province is divided into 10 divisions, namely, MOF_P0 of the province, MOF_P1 of city 1 of the province, MOF_P2 of city 2 of the province, MOF_P3 of city 3 of the province, MOF_P4 of city 4 of the province, MOF_P5 of city 5 of the province, MOF_P6 of city 6 of the province, MOF_P7 of city 7 of the province, MOF_P8 of city 8 of the province, and MOF_P9 of city 9 of the province. Each division contains the fiscal division data of the corresponding prefecture-level city.

[0077] Based on the same inventive concept, the present application provides an electronic device embodiment corresponding to the first embodiment, see the fourth embodiment for details.

[0078] Embodiment 4

[0079] This embodiment provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, any implementation method in the first embodiment can be implemented.

[0080] Since the electronic device introduced in this embodiment is a device used to implement the method in the first embodiment of the present application, based on the method introduced in the first embodiment of the present application, a person skilled in the art can understand the specific implementation of the electronic device of the present embodiment and its various variations, so how the electronic device implements the method in the embodiment of the present application is not described in detail here. As long as a person skilled in the art implements the device used by the method in the embodiment of the present application, it belongs to the scope of protection of the present application.

[0081] Based on the same inventive concept, the present application provides a storage medium corresponding to Example 1, see Example 5 for details.

[0082] Embodiment 5

[0083] This embodiment provides a computer-readable storage medium on which a computer program is stored. When the computer program is executed by a processor, any implementation method in the first embodiment can be implemented.

[0084] The technical solution provided in the embodiments of the present application has at least the following technical effects or advantages: the embodiments of the present application provide a method, system, device and medium for querying asset depreciation data, which archives the depreciation data of previous years, performs compression operations, and then partitions the depreciation data, thereby solving the problem that users cannot get a response within a reasonable corresponding time when querying reports such as general ledgers or unit depreciation details or performing depreciation accrual operations, and improving the speed of querying and updating asset depreciation data.

[0085] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented 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.

[0086] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0087] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0088] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0089] Although the specific implementation modes of the present invention are described above, those skilled in the art should understand that the specific implementation modes described are only illustrative and are not intended to limit the scope of the present invention. Equivalent modifications and changes made by those skilled in the art in accordance with the spirit of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A method for querying asset depreciation data, characterized in that: include: Step 1: Create a depreciation data table for the corresponding year according to the year, and store the asset depreciation data over the years in the corresponding depreciation data table; Step 2, compressing the asset depreciation data over the years, the compression specifically includes: calculating the total depreciation of 12 months in a specified year to obtain the annual depreciation value of the specified year, adding the number of depreciated months at the beginning of the specified year to the number of depreciation times in the specified year, calculating the total number of depreciated months at the end of the year, adding the accumulated depreciation value at the beginning of the year to the total depreciation value of the specified year, and obtaining the accumulated depreciation value of the asset at the end of the year; Store the annual depreciation value, the total number of months depreciated, and the accumulated depreciation value in the corresponding depreciation data table; Step 3: partition the database into depreciation data partitions using the fiscal division as the partition key; Step 4: Establish a query entry for historical data, and query the asset depreciation data in the database by calling the query entry for the corresponding year.

2. A method for querying asset depreciation data according to claim 1, characterized in that: The depreciation data table described in step 1 includes asset depreciation / amortization table, depreciation details table, supplementary depreciation table, and supplementary depreciation details table; The asset depreciation / amortization table is a data table that stores basic information, including the accounting date and the current depreciation amount; The depreciation details table is a data table storing detailed information, including service life, current depreciation amount, monthly depreciation amount, and current depreciation amount. The depreciation details table corresponds to the asset depreciation / amortization table; The supplementary depreciation table is a data table for storing supplementary depreciation and reduction business document information, and the supplementary depreciation and reduction business document information includes the accounting date and the current depreciation amount; The supplementary depreciation details table is a data table that stores the detailed information of supplementary depreciation and depreciation reduction assets. The detailed information of supplementary depreciation and depreciation reduction assets includes the service life, depreciation months, monthly depreciation amount, current depreciation amount, and accumulated depreciation data.

3. A method for querying asset depreciation data according to claim 1, characterized in that: The database partition described in step 3 uses the List partition of the database.

4. A system for querying asset depreciation data, characterized in that: include: The module for archiving asset depreciation data over the years creates a depreciation data table for the corresponding year according to the year, and stores the asset depreciation data over the years in the corresponding depreciation data table; The asset depreciation data compression module compresses the asset depreciation data over the years. The compression is specifically as follows: the total depreciation of the 12 months of a specified year is calculated to obtain the annual depreciation value of the specified year, the number of depreciated months at the beginning of the specified year is added to the number of depreciation times of the specified year, the total number of depreciated months at the end of the year is calculated, and the accumulated depreciation value at the beginning of the year is added to the total depreciation value of the specified year to obtain the accumulated depreciation value of the asset at the end of the year; Store the annual depreciation value, the total number of months depreciated, and the accumulated depreciation value in the corresponding depreciation data table; Depreciation data partitioning module, which partitions the database into depreciation data according to the fiscal division as the partition key; The historical data query module establishes a historical data query entry, and queries the asset depreciation data in the database by calling the query entry of the corresponding year.

5. A system for querying asset depreciation data according to claim 4, characterized in that: The depreciation data tables in the module for archiving asset depreciation data over the years include asset depreciation / amortization table, depreciation detail table, supplementary depreciation table, supplementary depreciation detail table; The asset depreciation / amortization table is a data table that stores basic information, including the accounting date and the current depreciation amount; The depreciation details table is a data table storing detailed information, including service life, current depreciation amount, monthly depreciation amount, and current depreciation amount. The depreciation details table corresponds to the asset depreciation / amortization table; The supplementary depreciation table is a data table for storing supplementary depreciation and reduction business document information, and the supplementary depreciation and reduction business document information includes the accounting date and the current depreciation amount; The supplementary depreciation details table is a data table that stores the detailed information of supplementary depreciation and depreciation reduction assets. The detailed information of supplementary depreciation and depreciation reduction assets includes the service life, depreciation months, monthly depreciation amount, current depreciation amount, and accumulated depreciation data.

6. A system for querying asset depreciation data according to claim 4, characterized in that: The depreciation data partition module uses the List partition of the database.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the method according to any one of claims 1 to 3 is implemented.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 3 is implemented.

Images