CN109710616B - Data storage method - Google Patents
Data storage method Download PDFInfo
- Publication number
- CN109710616B CN109710616B CN201811637564.0A CN201811637564A CN109710616B CN 109710616 B CN109710616 B CN 109710616B CN 201811637564 A CN201811637564 A CN 201811637564A CN 109710616 B CN109710616 B CN 109710616B
- Authority
- CN
- China
- Prior art keywords
- data
- storage space
- storing
- column storage
- level
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a data storage method. The method comprises the following steps: defining metadata in terms of a statistical dimension n, includingn-level data TnN is 1,2, …, N is a positive integer; allocating a corresponding primary column storage space for the metadata of each dimension, wherein the primary data T1The first-level column storage space stores first data; secondary data T2The first-level column memory space stores second data, the second data is generated by dividing the first data into M1Unit of action divided into
Any position data of each group after the group; by analogy, m-level data TmFollowing storage
M-th data, M-th data being formed by dividing (M-1) -th data by Mm‑1Unit of action divided into
Any position data of each group after the group; n-level data TNThe first-level column storage space stores the Nth data, wherein the Nth data is the data at the appointed position in the (N-1) th data; and storing the data in the corresponding primary column storage space according to the statistical dimension. The invention can reduce the logic complexity and improve the query efficiency.
Description
Technical Field
The invention relates to the technical field of data storage, in particular to a data storage method.
Background
At present, accumulated incremental data of intelligent devices such as an electric meter, a water meter, a heat meter and a gas meter are generally stored by adopting a relational database (such as MySQL, Oracle and SQL Server). The relational database takes data storage similar to the EXCEL table, as shown in table 1: dividing the accumulated increment data of the intelligent equipment into three columns: smart device ID, data recording time, and smart device reading.
TABLE 1 common storage mode for intelligent device data
| ID | Time | Tabular value |
| 1 | 2018-01-07 01：00：00 | 1 |
| 2 | 2018-01-07 02：00：00 | 2 |
| 3 | 2018-01-07 03：00：00 | 3 |
| …… | …… | …… |
| 10000 | 2018-12-31 23：00：00 | 10000 |
Taking an electric meter as an example, according to the data storage mode shown in table 1, one data is stored for one hour, 24 data are stored for 1 day, 24 × 365 data are stored in a database for one year (calculated according to 365 days), and 100 ten thousand rows of data are required for storing 115 electric meters. However, when the relational databases such as MySQL, Oracle, SQL Server, etc. are more than 100 thousands of rows of data in a single table, the query performance will be reduced, and the query speed of the data is slow.
Therefore, a data storage method of a sub-library and a sub-table is proposed. The database and table division is to divide the original database and table according to a certain rule, store the data blocks originally stored in one database into a plurality of databases, and store the data blocks originally stored in one table into a plurality of tables. For example, the data is divided into tables according to the time dimension, and the monthly data is stored in one table. However, when data is queried, a single-table query is changed into a multi-table query, which causes logic complexity during query.
Chinese patent 201310305852.7 discloses a data storage and query method for smart meters and a system thereof, which divides a storage page into a plurality of storage pages, divides time into a plurality of time periods, calculates the storage page where the data to be queried is located according to the time of the data to be queried and the ratio relationship, thereby well achieving the balance between query speed and space utilization. However, if the query data is more, the proportional relation needs to be calculated every time, and the query efficiency is low.
Disclosure of Invention
In order to solve the problems in the prior art, the invention provides a data storage method, which reduces the logic complexity during data query and improves the query efficiency.
The invention provides a data storage method, which mainly comprises the following steps:
step 1, defining metadata according to a set statistical dimension n, wherein the metadata comprises n-level data TnN is 1,2, …, N is a positive integer;
step 2, distributing a corresponding primary column storage space for the metadata of each dimension, wherein:
the primary data T1Is used for storing
A piece of first data; the secondary data T2Is used for storing
A piece of second data formed by dividing the first data by M1Unit of action divided into
Any position data of each group after the group; by analogy, the m-level data TmIs used for storing
M-th data composed of (M-1) -th data expressed in Mm-1Unit of action divided into
Any position data of each group after the group; the N-level data TNIs used for storing MN-1The Nth data is the data of the appointed position in the (N-1) th data; wherein m is 3,4, …, N-1; m1、M2、…、MN-1Are all positive integers;
and 3, storing the data in the corresponding primary column storage space according to the statistical dimension.
Further, the step 2 further comprises:
dividing the front N-1 level data into M according to the statistical dimensionN-1Corresponding subdata;
respectively dividing the first-level column storage space of the first N-1-level data into MN-1A secondary column storage space corresponding to the subdata;
correspondingly, the secondary column storage space of each primary subdata is used for storing
First data of strip(ii) a The secondary column storage space of each secondary subdata is used for storing
A piece of second data; in this way, the secondary column storage space of each level l subdata is used for storing
The first data of each piece of data, wherein 1 piece of data is stored in the secondary column storage space of each N-1 level subdata, and l is 3,4, … … and N-2;
correspondingly, the step 3 is as follows: and correspondingly storing the data in the corresponding primary column storage space and the secondary column storage space.
Further, the statistical dimension unit of the first data adopts M1The statistical dimension unit of the second data adopts M2Carrying out a binary system and the like, wherein the statistical dimension unit of the mth data adopts MmAnd carrying out a carry system.
The invention has the beneficial effects that:
the invention provides a data storage method, firstly, a plurality of metadata are defined according to a set statistical dimension, and the metadata are correlated in the statistical dimension; and then distributing column storage space for each metadata, and storing the data of the equipment needing data statistics to the corresponding column storage space according to different statistical dimensions, thereby finally storing the accumulated incremental data of the equipment with different dimensions to a table in a database. Therefore, when the staff needs to inquire the data, the staff does not need to read a plurality of tables in different databases, and only needs to browse one table to obtain the relevant information, thereby reducing the logic complexity during data inquiry and improving the inquiry efficiency.
Drawings
Fig. 1 is a schematic flow chart of a data storage method according to an embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly described below with reference to the accompanying drawings in the embodiments of the present invention, and it is obvious that the described embodiments are some, but not all, embodiments of the present invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
The data of the invention is mainly accumulated incremental data accumulated along with set statistical dimensions.
Fig. 1 is a schematic flow chart of a data storage method according to an embodiment of the present invention. As shown in fig. 1, the method comprises the steps of:
s101, defining metadata according to set statistical dimension n, wherein the metadata comprises n-level data TnN is 1,2, …, N is a positive integer;
s102, distributing a corresponding primary column storage space for the metadata of each dimension, wherein:
the primary data T1Is used for storing
A piece of first data; the secondary data T2Is used for storing
A piece of second data formed by dividing the first data by M1Unit of action divided into
Any position data of each group after the group; by analogy, the m-level data TmIs used for storing
M-th data composed of (M-1) -th data expressed in Mm-1Unit of action divided into
Any position data of each group after the group; the N-level data TNIs used for storing MN-1The Nth data is the data of the appointed position in the (N-1) th data; wherein m is 3,4, …, N-1; m1、M2、…、MN-1Are all positive integers;
specifically, the statistical dimension unit of the first data is M1The statistical dimension unit of the second data adopts M2Carrying out a binary system and the like, wherein the statistical dimension unit of the mth data adopts MmAnd carrying out a carry system.
S103, storing the data in the corresponding primary column storage space according to the statistical dimension.
The invention provides a data storage method, firstly, a plurality of metadata are defined according to a set statistical dimension, and the metadata are correlated in the statistical dimension; and then distributing column storage space for each metadata, and storing the data of the equipment needing data statistics to the corresponding column storage space according to different statistical dimensions, thereby finally storing the accumulated incremental data of the equipment with different dimensions to a table in a database. Therefore, when the staff needs to inquire the data, the staff does not need to read a plurality of tables in different databases, and only needs to browse one table to obtain the relevant information, thereby reducing the logic complexity during data inquiry and improving the inquiry efficiency.
On the basis of the foregoing embodiment, as an implementable manner, the step S102 further includes: dividing the front N-1 level data into M according to the statistical dimensionN-1Corresponding subdata; respectively dividing the first-level column storage space of the first N-1-level data into MN-1A secondary column storage space corresponding to the subdata;
correspondingly, the secondary column storage space of each primary subdata is used for storing
A piece of first data; each secondary column storage space of the secondary subdata is used for storingStore up
A piece of second data; in this way, the secondary column storage space of each level l subdata is used for storing
The first data of each piece of data, wherein 1 piece of data is stored in the secondary column storage space of each N-1 level subdata, and l is 3,4, … … and N-2;
accordingly, the step S103 is: and correspondingly storing the data in the corresponding primary column storage space and the secondary column storage space. On the basis of the above embodiment, the present invention provides another embodiment, which comprises the following specific processes:
s201, defining metadata according to a time statistic dimension, wherein the metadata comprises primary data T1, secondary data T2, tertiary data T3 and quaternary data T4;
s202, distributing a corresponding primary column storage space for the metadata of each dimension;
s203, dividing the primary data T1, the secondary data T2 and the tertiary data T3 into P primary subdata, P secondary subdata and P tertiary subdata respectively according to time statistic dimensions;
s204, dividing primary column storage spaces of the primary data T1, the secondary data T2 and the tertiary data T3 into P secondary column storage spaces corresponding to the primary subdata, the secondary subdata and the tertiary subdata respectively;
specifically, the secondary column storage space of each primary subdata is used for storing M × N pieces of first data; the secondary column storage space of each secondary subdata is used for storing N pieces of second data; the secondary column storage space of each tertiary subdata is used for storing third data; the first-level column storage space of the four-level data T4 is used for storing fourth data.
The second data is composed of last first data of each group which is obtained by dividing the first data into N groups according to M line units; the third data consists of the last second data; the fourth data is the last third data.
S205, correspondingly storing the data in the corresponding primary column storage space and the secondary column storage space, and finally forming a table. As shown in table 2.
Table 2 data storage table
It should be noted that M, N and P in the present embodiment correspond to M in the above-described embodiment, respectively1、M2And M3。
Typical application examples of the data storage method provided by the invention include data storage for intelligent metering equipment or intelligent household equipment. The intelligent metering equipment comprises civil meters such as an electric meter, a water meter, a gas meter and a heat meter. Taking an intelligent electric meter as an example, according to the data storage method provided by the invention, the data storage method of the real-time data of the electric meter is as follows. First, an index table and a field table may be established for later query. The index table and field table are as in tables 3 and 4, respectively.
Table 3 index table
| Name (R) | Type (B) | Properties | Remarks for note |
| Main index | id | unique | Is free of |
| addr | equnum | Is free of | Is free of |
| year | year | Is free of | Is free of |
TABLE 4 field Table
And in combination with the table 2, defining metadata of a data storage table of the intelligent electric meter: primary data T1, secondary data T2, tertiary data T3, and quaternary data T4. Wherein, the primary data T1 comprises 12 primary sub-data (h1-h 12); the secondary data T2 includes 12 secondary data (d1-d 12); the tertiary data T3 includes 12 tertiary subdata (m1-m 12); the four-level data T4 is y 1. The initialization M is 24, N is 31, and P is 12.
The first data is real-time data of the smart metering device in units of hours, such as "1.1" described below; the second data is real-time data of the smart metering device in days as an updating unit, such as 26.4; the third data is real-time data of the smart metering device in a month updating unit, such as "818.4" described below; the fourth data is real-time data of the smart metering device in units of years, such as "89662.4" described below.
(1)12 hours column (h1-h 12): h1 records data for all hours of month 1, h2 records data for all hours of month 2, and so on, h12 records data for all hours of month 12.
For example, hour data of month 1 can be stored by the following procedure. Wherein "0" - "23" represents hours; "1.1" represents the meter reading.
(2)12 days column (d1-d 12): d1 records data for all days of month 1, d2 records data for all days of month 2, and so on, d12 records data for all days of month 12.
For example, 31-day data of 1 month can be stored by the following program. Wherein "01" - "31" represents a day; "26.4" represents the meter reading.
(3)12 months column (m1-m 12): m1 records data for all months of month 1, m2 records data for all days of month 2, and so on, and m12 records data for all days of month 12.
For example, month 1 month data: 818.4.
(4) column y1 for one year: the total data for one year is recorded.
For example, total data volume of 1 year: 89662.4.
therefore, when historical data such as electricity, water and the like are counted, data of 24 hours in a day, data of 31 days in a month and data of 12 months in a year need to be searched, and the data of one year can be conveniently and quickly searched in one piece of data. One piece of data occupies one row in the table, so that 100 ten thousand rows of data can store one year of data of 100 ten thousand devices, and the following advantages are achieved: the first time, the same data uses less storage space; secondly, the query performance is improved; thirdly, the logic is simple without making a database and a table; fourthly, reducing the query times of the database; fifthly, the inquired data can be directly displayed on the page without adding or subtracting the inquired data, so that the time is saved.
Finally, it should be noted that: the above examples are only intended to illustrate the technical solution of the present invention, but not to limit it; although the present invention has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical solutions described in the foregoing embodiments may still be modified, or some technical features may be equivalently replaced; and such modifications or substitutions do not depart from the spirit and scope of the corresponding technical solutions of the embodiments of the present invention.
Claims (2)
1. A method for storing data, wherein the data is cumulative incremental data accumulated over a statistical dimension of time, the method comprising:
step 1, defining metadata according to a time statistic dimension n, wherein the metadata comprises n levels of data TnN is 1,2, …, N is a positive integer;
step 2, in a table, allocating a corresponding primary column storage space for the metadata of each dimension, wherein:
the primary data T1Is used for storing
A piece of first data; the secondary data T2Is used for storing
A piece of second data formed by dividing the first data by M1Unit of action divided into
Any position data of each group after the group; by analogy, the m-level data TmIs used for storing
M-th data composed of (M-1) -th data expressed in Mm-1Unit of action divided into
Any position data of each group after the group; the N-level data TNIs used for storing MN-1The Nth data is the data of the appointed position in the (N-1) th data; wherein m is 3,4, …, N-1; m1、M2、…、MN-1Are all positive integers; the statistical dimension unit of the first data adopts M1The statistical dimension unit of the second data adopts M2Carrying out a binary system and the like, wherein the statistical dimension unit of the mth data adopts MmCarrying out a carrying process;
and 3, storing the data in the corresponding primary column storage space according to the time statistic dimension.
2. The method of claim 1, wherein step 2 further comprises:
dividing the front N-1 level data into M according to the statistical dimensionN-1Corresponding subdata;
respectively dividing the first-level column storage space of the first N-1-level data into MN-1A secondary column storage space corresponding to the subdata;
correspondingly, the secondary column storage space of each primary subdata is used for storing
A piece of first data; the secondary column storage space of each secondary subdata is used for storing
A piece of second data; in this way, the secondary column storage space of each level l subdata is used for storing
The first data of each piece of data, wherein 1 piece of data is stored in the secondary column storage space of each N-1 level subdata, and l is 3,4, … … and N-2;
correspondingly, the step 3 is as follows: and correspondingly storing the data in the corresponding primary column storage space and the secondary column storage space.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811637564.0A CN109710616B (en) | 2018-12-29 | 2018-12-29 | Data storage method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811637564.0A CN109710616B (en) | 2018-12-29 | 2018-12-29 | Data storage method |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109710616A CN109710616A (en) | 2019-05-03 |
| CN109710616B true CN109710616B (en) | 2021-10-22 |
Family
ID=66259762
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811637564.0A Active CN109710616B (en) | 2018-12-29 | 2018-12-29 | Data storage method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109710616B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103186566A (en) * | 2011-12-28 | 2013-07-03 | 中国移动通信集团河北有限公司 | Data classification storage method, device and system |
| CN103412889A (en) * | 2013-07-19 | 2013-11-27 | 深圳市金正方科技股份有限公司 | Data storage and query method of intelligent electric meter and system thereof |
| CN103678402A (en) * | 2012-09-21 | 2014-03-26 | 厦门雅迅网络股份有限公司 | Real-time data statistics method of mass data |
| CN104679889A (en) * | 2015-03-17 | 2015-06-03 | 浪潮集团有限公司 | Big data processing-oriented data storage method and device |
| CN104915456A (en) * | 2015-07-03 | 2015-09-16 | 宁夏隆基宁光仪表有限公司 | Mass power utilization data mining method on the basis of data analysis system |
Family Cites Families (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8311863B1 (en) * | 2009-02-24 | 2012-11-13 | Accenture Global Services Limited | Utility high performance capability assessment |
| MX339713B (en) * | 2009-05-07 | 2016-06-07 | Dominion Resources Inc | Voltage conservation using advanced metering infrastructure and substation centralized voltage control. |
| CN105096214A (en) * | 2015-08-15 | 2015-11-25 | 国家电网公司 | Power marketing data acquisition method |
| CN107203145A (en) * | 2016-03-17 | 2017-09-26 | 姜洪军 | A kind of intelligent network management platform that interconnects containing smart terminal product and automatic control system |
| CN106909495B (en) * | 2016-06-03 | 2020-07-03 | 阿里巴巴集团控股有限公司 | Data window statistical method, device and system |
| CN106294888B (en) * | 2016-10-24 | 2018-10-26 | 北京亚控科技发展有限公司 | A kind of method for subscribing of the object data based on space-time database |
| CN108897808B (en) * | 2018-06-16 | 2023-11-24 | 王梅 | Method and system for storing data in cloud storage system |
-
2018
- 2018-12-29 CN CN201811637564.0A patent/CN109710616B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103186566A (en) * | 2011-12-28 | 2013-07-03 | 中国移动通信集团河北有限公司 | Data classification storage method, device and system |
| CN103678402A (en) * | 2012-09-21 | 2014-03-26 | 厦门雅迅网络股份有限公司 | Real-time data statistics method of mass data |
| CN103412889A (en) * | 2013-07-19 | 2013-11-27 | 深圳市金正方科技股份有限公司 | Data storage and query method of intelligent electric meter and system thereof |
| CN104679889A (en) * | 2015-03-17 | 2015-06-03 | 浪潮集团有限公司 | Big data processing-oriented data storage method and device |
| CN104915456A (en) * | 2015-07-03 | 2015-09-16 | 宁夏隆基宁光仪表有限公司 | Mass power utilization data mining method on the basis of data analysis system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109710616A (en) | 2019-05-03 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN102646130B (en) | Method for storing and indexing mass historical data | |
| CN107103032B (en) | Mass data paging query method for avoiding global sequencing in distributed environment | |
| CN107273482A (en) | Alarm data storage method and device based on HBase | |
| CN103617232A (en) | Paging inquiring method for HBase table | |
| CN103577590A (en) | Data query method and system | |
| CN102890721B (en) | Based on database building method and the system of row memory technology | |
| US9600559B2 (en) | Data processing for database aggregation operation | |
| CN105426434A (en) | Multi-dimension-based population information statistical analysis system | |
| US10296656B2 (en) | Managing database | |
| CN111104457A (en) | Massive space-time data management method based on distributed database | |
| CN102902797A (en) | Method for storing and retrieving real-time monitored data of large-volume equipment | |
| CN109213761A (en) | Table method is divided in point library that a kind of spread exempts from migration | |
| CN105426421A (en) | Tense monitoring data quick visualization method and system | |
| CN102737123A (en) | Multidimensional data distribution method | |
| CN101692231B (en) | Remote sensing image block sorting and storing method suitable for spatial query | |
| CN108108358B (en) | Storage and retrieval method for power quality data | |
| CN109710616B (en) | Data storage method | |
| CN105426506A (en) | Massive dynamic data management method | |
| CN101149738B (en) | Method for utilizing fixed length field for optimizing iteration inquiry | |
| CN107038202B (en) | Data processing method, device and equipment and readable medium | |
| CN103778247A (en) | Data apportion method, device and equipment | |
| CN108614841B (en) | Time interval adjusting method and device | |
| CN101609331A (en) | A kind of data storage and read method that is applicable in the industrial process control | |
| CN113850427A (en) | Time series prediction method, electronic device, and computer storage medium | |
| CN110597830A (en) | Real-time index generation method and system, electronic device and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| CB02 | Change of applicant information |
Address after: 450000 Beidou Industrial Park C28-2, No. 50 Wutong Street, Zhengzhou High-tech Industrial Development Zone, Henan Province Applicant after: ZHENGZHOU CHUNQUAN ENERGY-SAVING Co.,Ltd. Address before: Bamboo high tech Development Zone in Henan province 450000 Zhengzhou Street No. 6 Building 1 unit 8 floor No. 13 East 1 Applicant before: ZHENGZHOU CHUNQUAN ENERGY-SAVING Co.,Ltd. |
|
| CB02 | Change of applicant information | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| PE01 | Entry into force of the registration of the contract for pledge of patent right |
Denomination of invention: A data storage method Effective date of registration: 20220609 Granted publication date: 20211022 Pledgee: Zhengzhou Bank Co.,Ltd. Changxi Lake New Area Branch Pledgor: ZHENGZHOU CHUNQUAN ENERGY-SAVING Co.,Ltd. Registration number: Y2022980007448 |
|
| PE01 | Entry into force of the registration of the contract for pledge of patent right |