A data preprocessing method for large-scale computation of international trade statistics

CN118484451BActive Publication Date: 2024-11-12大连瀚闻资讯有限公司
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410946602.X
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-07-16
Publication Date
2024-11-12
Estimated Expiration
2044-07-16

Smart Images

  • Figure CN118484451B_ABST
    Figure CN118484451B_ABST
Patent Text Reader

Abstract

The present invention discloses a data preprocessing method for large-scale calculation of international trade statistics data, S1, obtaining international trade statistics data of various countries; S2, standardizing the international trade statistics data of various countries, and storing the standardized data in annual basic database tables and monthly basic database tables according to the publication methods of various countries; S3, mirroring data completion operation is performed on the data in the annual basic database tables and the monthly basic database tables; S4, merging and extracting the annual basic database tables and the monthly basic database tables according to different data statistical levels, so as to generate preprocessing tables. The present invention standardizes the collected international trade statistics data and stores them in annual and monthly database tables, then completes the missing data through mirroring technology, and then generates several highly preprocessed small-scale database tables through multi-dimensional statistics, which effectively speeds up the calculation speed of large-scale data analysis.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of big data processing, and in particular to a data preprocessing method for large-scale calculation of international trade statistical data. Background Art

[0002] In the field of international trade, processing and analyzing large-scale statistical data is crucial for governments, businesses and research institutions. These data contain information such as trade flows, commodity classifications, trading partners, etc. between countries, providing an important basis for policy making, assessing trade conditions and conducting market research. The characteristics of international trade statistics include:

[0003] International trade statistics usually come from different countries and organizations. Each country may adopt different data collection and coding standards. Therefore, there is a lack of standardization and unified format, which leads to deviations in the quality and consistency of the data, thereby increasing the difficulty of data comparison and analysis.

[0004] International trade statistics are huge in scale, often containing billions of data points and multi-dimensional information. Therefore, when processing large-scale data, we face the problems of high computational complexity, large storage requirements and low computational efficiency.

[0005] There are complex relationships in international trade statistics, such as dependencies between commodities, connections between trading partners, etc. In order to effectively discover and analyze these relationships and understand and analyze trade patterns and market trends, it is necessary to dig deep into trade statistics.

[0006] The update time of international trade statistics is delayed and uncertain. The update time of trade data in different countries will vary according to the statistical agencies and data release policies of different countries and regions. Therefore, it is inevitable to frequently collect and update data from various data sources.

[0007] In view of the characteristics of international trade data, the commonly used statistical methods have the following shortcomings:

[0008] 1. Slow data query speed: Due to the huge scale of international trade statistics, traditional data storage methods often aim to save space and avoid storing redundant data as much as possible. For example, data from countries around the world are collected and cleaned in a unified format into a database table or multiple tables with the same table structure to facilitate unified query and analysis. Although this method is logically simple and clear, as the scale of data increases, the speed becomes slow when querying and analyzing hundreds of millions of data, which poses a challenge to real-time analysis and query of trade data.

[0009] 2. Inefficient data processing and analysis: There are complex correlations and statistical requirements in international trade data, such as important trading partners of a country or economy, products with significant comparative advantages in an economy or region, and changes in the distribution of importers and exporters of products. The traditional single data structure is difficult to meet the needs of efficient correlation analysis and statistical calculations, resulting in inefficient data processing and analysis, and the inability to conduct effective multi-dimensional analysis in a short period of time. Summary of the invention

[0010] In view of the shortcomings of the prior art, the present invention provides a data preprocessing method for large-scale calculation of international trade statistics. The present invention standardizes the collected international trade statistics and stores them in annual and monthly database tables, then fills in the missing data through mirroring technology, and then generates several highly preprocessed smaller-scale database tables through multi-dimensional statistics, effectively speeding up the calculation speed of large-scale data analysis.

[0011] The technical means adopted by the present invention are as follows:

[0012] A data preprocessing method for large-scale calculation of international trade statistics data comprises the following steps:

[0013] S1. Obtain international trade statistics of various countries;

[0014] S2. Standardize the international trade statistics of various countries, and store the standardized data in the annual basic database table and the monthly basic database table according to the publication method of each country, and the publication method includes annual publication and monthly publication;

[0015] S3. Performing a mirror data completion operation on the data in the annual basic database table and the monthly basic database table, wherein the mirror data completion operation is used to deduce and obtain unpublished international trade data based on the published international trade data in the annual basic database table and the monthly basic database table;

[0016] S4. The annual basic database table and the monthly basic database table are merged and extracted according to different data statistical levels to generate pre-processing tables. The data statistical levels include the data source country level, product level, data source country and product level, data source country and trading partner country level, and data source country, product and trading partner country level.

[0017] Furthermore, we can obtain international trade statistics of various countries, including:

[0018] Obtain all published national trade statistics. The data items of the national trade statistics mainly include data source country, trading partner country, import and export information, time, commodity HS code, trade value, trade volume, quantity unit and transportation mode.

[0019] Furthermore, the international trade statistics of various countries are standardized and stored in the annual basic database table and the monthly basic database table according to the publication method of each country, including:

[0020] Obtain monthly data of each country based on international trade statistics of each country and store them in a monthly basic database table in the database. The fields of the monthly basic database table include data source country, trading partner country, import and export information, year, month, commodity HS code, trade value, trade volume and quantity unit;

[0021] The annual data of each country is obtained based on the international trade statistics of each country and stored in the annual basic database table in the database. The fields of the annual basic database table include data source country, trading partner country, import and export information, year, month, commodity HS code, trade amount, trade volume and quantity unit.

[0022] Furthermore, in the monthly basic database table and the annual basic database table, the data source country, trading partner country, import and export information and quantity units are coded, and the trade volume is uniformly converted.

[0023] Furthermore, the mirror data completion operation is performed on the data in the annual basic database table and the monthly basic database table, including:

[0024] Obtain candidate international trade data, which is the trade data between the first country and the second country. The first country has published international trade data, while the second country has not published international trade data. The international trade data of the second country is reversely calculated based on the international trade data published by the first country, and primary mirror completion is performed. The trade data of the first country and the second country are integrated, and all the primary mirror completion results are integrated to achieve secondary mirror completion of the trade data of the second country. For example, there are three countries ABC, among which the trade of country C is not published. Here, the trade data between A and C is first obtained, and then the trade data between B and C is obtained. Then the trade data of countries AB and C are integrated to achieve the reverse deduction of the trade data of country C.

[0025] Furthermore, the annual basic database table and the monthly basic database table are highly and diversely merged and counted to generate pre-processing tables, including:

[0026] At the data source country level, annual and monthly statistics are compiled on the total import and export volume of countries, regions and preset country groups around the world, and their share in the global total import and export volume is calculated;

[0027] At the product level, annual and monthly statistics are collected for products coded in 22 customs categories, HS2, HS4, HS6 and SITC, and their global import and export values ​​and their share in the global import and export values ​​are calculated;

[0028] At the data source country and product level, annual and monthly statistics are collected on the import and export trade volume of countries, regions and country groups under different product codes. Net imports, net exports and corresponding proportions are calculated, including the proportion of imports and exports to the total imports and exports of the data source country and the proportion of imports and exports to the global imports and exports of the product. Product codes include 22 customs categories, HS2, HS4, HS6 and SITC codes.

[0029] At the data source country and trading partner country level, annual and monthly statistics are collected on the import and export volume, net export volume, and the proportion of trade volume to the total import and export volume of the data source country between global countries, regions, and preset country groups;

[0030] At the level of data source countries, products and trading partner countries, annual and monthly statistics are collected on the import and export volume and proportion of 22 major product categories at different customs levels among countries, regions and preset country groups around the world. The proportion includes the proportion of product trade volume to the total trade volume between two countries, and the proportion of the trade volume of the import and export products of the data source country to the trade volume of all import and export products of the data source country.

[0031] Compared with the prior art, the present invention has the following advantages:

[0032] The present invention proposes a data preprocessing method for large-scale calculation of international trade statistics. The core idea of ​​the method is to standardize the collected international trade statistics and store them in annual and monthly database tables (referred to as basic tables), and then use the "mirror" technology to fill in the missing data, and then use multi-dimensional statistics to generate several highly preprocessed smaller-scale database tables (referred to as preprocessing tables). These preprocessing tables are the results of highly and diversely merging and statistics of the data in the basic tables. Through the "mirror" technology, the trade data of some countries in certain years and months that have not been published will be filled in, making the data coverage of the database more complete. Through the preprocessing of the basic table data, the complex relationships and statistical information in the basic table are calculated and stored in the preprocessing table in advance, thereby realizing the optimization of space for time, effectively improving the improvement of large-scale computing speed, the optimization of data processing efficiency, and the simplification of data association and statistics. The preprocessing method has important application prospects in the processing and analysis of large-scale international trade statistics. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0034] Figure 1 The present invention is a flow chart of a data preprocessing method for large-scale calculation of international trade statistics data. DETAILED DESCRIPTION

[0035] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0036] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0037] like Figure 1 As shown, the present invention provides a data preprocessing method for large-scale calculation of international trade statistics data, comprising the following steps:

[0038] S1. Obtain international trade statistics of various countries.

[0039] Specifically, all published national trade statistics are collected, and the data items mainly include the country of origin, trading partner country, import and export information, time, commodity HS code, trade value, trade volume, quantity unit and mode of transportation, etc. Some countries publish trade statistics annually, while others publish them monthly.

[0040] S2. Standardize the international trade statistics of various countries, and store the standardized data in annual basic database tables and monthly basic database tables according to the publication methods of various countries. The publication methods include annual publication and monthly publication.

[0041] Specifically, the collected trade statistics are further processed, and the monthly data of all countries are stored in the monthly basic table in the database. The fields in the table include key information such as the country of origin of the data, the trading partner country, import and export information, year, month, commodity HS code, trade value, trade volume, quantity unit, etc. At the same time, the collected national annual data are stored in the annual basic table in the database. The table fields are similar to the monthly basic table, and the time is only the year. In this step, the country, quantity unit and import and export information will be coded so that all the data stored in the database table are numbers, and the trade volume of each country will be converted into a unified currency unit, such as the US dollar and the RMB. At this point, the standardization of the statistical methods of trade statistics of various countries has been completed.

[0042] S3. Perform a mirror data completion operation on the data in the annual basic database table and the monthly basic database table, wherein the mirror data completion operation is used to deduce and obtain unpublished international trade data based on the published international trade data in the annual basic database table and the monthly basic database table.

[0043] After the first two steps, the original trade statistics of most countries have been collected. However, there are more than 200 countries and regions in the world, and not all countries or regions have complete international trade statistics for nearly a decade. Therefore, in order to more effectively mine and analyze the big data of global trade statistics, in step S3, the "mirror" technology is used to supplement and improve the international trade statistics. Simply put, the "mirror" technology generates unpublished trade data of countries or regions by deducing the original database. For example, the trade data of country A is not published, while the trade data of country B has been published, and there is a record of country B exporting a certain product to country A in the trade data of country B, then the data of country A importing the product from country B can be reversely deduced. Similarly, by "mirroring" the original trade data of multiple countries, most of the trade data of country A can be obtained. These supplementary data are generated based on real trade statistics, so they are authentic.

[0044] Preferably, the "mirror" completion process includes the following steps:

[0045] The first step is to mirror the monthly basic table. For each month, for those countries that have data as trading partners but no data as data source countries, reverse calculation and completion operations are performed, that is, the data source country of the original data becomes the trading partner country of the mirror data, and the trading partner country of the original data becomes the data source country of the mirror data, and the import and export of the original data are reversed. The final data source countries are limited to 205 countries and regions, and the mirror data are marked. In this way, after mirroring and completion, we get the monthly trade statistics of 205 countries and regions.

[0046] The second step is to find the corresponding countries and years and the missing months in the latest year in the statistical annual basic table, and accumulate them into annual data to supplement the missing data. For example, in 2024, the annual statistical data of some countries are only published until January, but the February data is already available through mirroring, so the February data in the monthly table is added to the 2024 data of this country in the annual table. In this process, the supplementary data may contain mirror data, which will also be marked in the annual basic table. Through this step, more complete annual trade statistics for 205 countries and regions can be obtained.

[0047] S4. The annual basic database table and the monthly basic database table are merged and extracted according to different data statistical levels to generate pre-processing tables. The data statistical levels include the data source country level, product level, data source country and product level, data source country and trading partner country level, and data source country, product and trading partner country level.

[0048] Specifically, this step uses the above-processed annual basic table and monthly basic table to generate multiple highly combined statistical pre-processed tables in the database, so that big data mining and analysis can be carried out more quickly. Specifically including:

[0049] At the data source country level, annual and monthly statistics are collected on the total import and export volume of countries, regions and some country groups around the world, and their share in the global total import and export volume is calculated.

[0050] At the product level, annual and monthly statistics are collected for the 22 customs categories, HS2, HS4, HS6 and SITC coded products, and their global import and export totals and their share in the global import and export totals are calculated.

[0051] At the data source country and product level, annual and monthly statistics are collected on the import and export trade volume of countries, regions and country groups under different product codes. At the same time, net imports, net exports and some percentage data are also calculated, such as the proportion of imports and exports to the total imports and exports of the data source country, and the proportion of imports and exports to the global imports and exports of the product. Product codes include 22 customs categories, HS2, HS4, HS6 and SITC codes.

[0052] At the data source country and trading partner country level, annual and monthly statistics are collected on the import and export volume, net export volume, and the proportion of trade volume to the total import and export volume of the data source country between countries, regions, and country groups around the world.

[0053] At the data source country, product and trading partner country level, annual and monthly statistics are collected on the import and export volume and proportion of 22 major categories of products between different customs of countries, regions and country groups around the world. The proportion includes the proportion of product trade volume to the total trade volume between two countries, the proportion of the import and export volume of the data source country's import and export products to the import and export volume of all products of the data source country, etc.

[0054] The database tables preprocessed at multiple levels described above will provide a faster data basis for large-scale computation and analysis of trade data. In this embodiment, in the application scenarios of large-scale computation of trade statistics data of different countries, the speed of SQL statements querying the preprocessing tables and the speed of querying the basic tables are compared as shown in Table 1. In order to make the experimental results more accurate, each group will perform three queries and take the average of the query speed as the final query speed.

[0055] Table 1 Query speed comparison statistics

[0056]

[0057] Panel A: Annual trade balances in Asia from 2010 to 2024. Trade balance = exports - imports.

[0058] Group B: Global import and export value of products with HS code 01 from 2010 to 2024.

[0059] Panel C: Changes in China’s trade volume of each imported product (HS2 code) and its share of the global import market from 2010 to 2024.

[0060] Panel D: Trade between Asia and Africa, 2010-2024.

[0061] It can be seen from Table 1 that the preprocessing method of the present invention can simplify query statements and speed up large-scale calculations of international trade statistics, thereby improving data analysis efficiency.

[0062] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A data preprocessing method for large-scale calculation of international trade statistics, characterized in that: The following steps are involved: S1. Obtain international trade statistics of various countries, including: obtaining all published national trade statistics, the data items of which include data source country, trading partner country, import and export information, time, commodity HS code, trade value, trade volume, quantity unit and mode of transportation; S2. Standardize the international trade statistics of various countries, and store the standardized data in the annual basic database table and the monthly basic database table according to the publication method of each country, and the publication method includes annual publication and monthly publication; S3. Performing a mirror data completion operation on the data in the annual basic database table and the monthly basic database table, wherein the mirror data completion operation is used to deduce and obtain unpublished international trade data based on the published international trade data in the annual basic database table and the monthly basic database table; the specific steps of the mirror data completion operation include: First, the monthly basic table is mirrored, including changing the source country of the original data to the trading partner country of the mirror data, the trading partner country of the original data to the source country of the mirror data, and the import and export of the original data are reversed. Secondly, count the missing countries and years in the annual basic table, and the missing months in the latest year, find the corresponding countries and years and months in the monthly basic table, and accumulate and summarize them into annual data to supplement the missing data; S4. The annual basic database table and the monthly basic database table are merged and extracted according to different data statistical levels to generate pre-processing tables. The data statistical levels include the data source country level, product level, data source country and product level, data source country and trading partner country level, and data source country, product and trading partner country level.

2. A data preprocessing method for large-scale calculation of international trade statistics according to claim 1, characterized in that: Standardize the international trade statistics of various countries and store the standardized data in the annual basic database table and the monthly basic database table according to the publication method of each country, including: Obtain monthly data of each country based on international trade statistics of each country and store them in a monthly basic database table in the database. The fields of the monthly basic database table include data source country, trading partner country, import and export information, year, month, commodity HS code, trade value, trade volume and quantity unit; The annual data of each country is obtained based on the international trade statistics of each country and stored in the annual basic database table in the database. The fields of the annual basic database table include data source country, trading partner country, import and export information, year, month, commodity HS code, trade amount, trade volume and quantity unit.

3. A data preprocessing method for large-scale calculation of international trade statistics according to claim 2, characterized in that: In the monthly basic database table and the annual basic database table, the data source country, trading partner country, import and export information and quantity units are coded, and the trade volume is uniformly converted.

4. A data preprocessing method for large-scale calculation of international trade statistics according to claim 3, characterized in that: Perform mirror data completion operations on the data in the annual basic database table and the monthly basic database table, including: Obtain candidate international trade data, where the candidate international trade data is trade data between a first country and a second country, the first country has published international trade data, and the second country has not published international trade data, reversely infer the international trade data of the second country based on the international trade data published by the first country, perform primary mirror completion, integrate the trade data of the first country and the second country, integrate all primary mirror completion results, and achieve secondary mirror completion of the trade data of the second country.

5. A data preprocessing method for large-scale calculation of international trade statistics according to claim 1, characterized in that: Perform high-level and diverse merging and statistics on the annual basic database tables and monthly basic database tables to generate pre-processing tables, including: At the data source country level, annual and monthly statistics are compiled on the total import and export volume of countries, regions and preset country groups around the world, and their share in the global total import and export volume is calculated; At the product level, annual and monthly statistics are collected for products coded in 22 customs categories, HS2, HS4, HS6 and SITC, and their global import and export values ​​and their share in the global import and export values ​​are calculated; At the data source country and product level, annual and monthly statistics are collected on the import and export trade volume of countries, regions and country groups under different product codes. Net imports, net exports and corresponding proportions are calculated, including the proportion of imports and exports to the total imports and exports of the data source country and the proportion of imports and exports to the global imports and exports of the product. Product codes include 22 customs categories, HS2, HS4, HS6 and SITC codes. At the data source country and trading partner country level, annual and monthly statistics are collected on the import and export volume, net export volume, and the proportion of trade volume to the total import and export volume of the data source country between global countries, regions, and preset country groups; At the level of data source countries, products and trading partner countries, annual and monthly statistics are collected on the import and export volume and proportion of 22 major product categories at different customs levels among countries, regions and preset country groups around the world. The proportion includes the proportion of product trade volume to the total trade volume between two countries, and the proportion of the trade volume of the import and export products of the data source country to the trade volume of all import and export products of the data source country.