Background technology
IPTV (Internet Protocol Television) is meant the television broadcasting service based on the IP agreement; Generally be referred to as Web TV; Be meant that mainly with " television set+STB " be main terminal equipment; Through broadband network, a kind of interactive television experience that provides for the broadband user and the broad band value-added service of multimedia service.Through using IPTV, the user can request program, choose at random the TV programme of oneself liking reviews.
Telecom operators are when mass development Application of I PTV is professional; Need carry out comprehensive statistical analysis to user's usage behavior, program issue and program request situation, system equipment performance etc.; For operation provides decision support, and the system that the IPTV statistical analysis system answers the requirement of operator to develop just.
The IPTV statistical analysis system mainly comprises source data extraction and preliminary treatment template, data loading and processing module, statistical forms customization functional module and statistics export function module etc., wherein:
1) source data is extracted and processing module: be used for obtaining source data from the IPTV database, it is the normal data file that source data is purified, makes up, goes weight, standardization and filing;
2) data loading and processing module: be used for the normal data file is imported database in batches, realize carrying out the reorganization and the statistical analysis of data through presetting task;
3) statistical forms customization module: be used to realize the customization of form material, produce form materials such as statistics, statistical chart according to customization, and realize of data organization and the conversion of statistics displaying data to report documents;
4) statistics derives module: be used for statistics is derived with the Excel mode with graphical, the batch of realizing the statistics of a plurality of forms is derived and the customization derivation of big data quantity statistics.
Therefore; Carry out the IPTV statistical analysis; The precondition of obtaining accurate analysis result is from the IPTV database, to import to obtain the accurate data file, yet in the prior art, when IPTV statistical analysis system database is arrived in the system business data importing of IPTV database; Do not consider the situation that traffic data field information is deleted in the IPTV database; The business datum that makes relevant information deleted imports the database of IPTV statistical analysis system, and the IPTV statistical analysis system is carried out statistical analysis based on this business datum, causes the inaccurate problem of statistic analysis result.
Summary of the invention
The purpose of technical scheme of the present invention provides a kind of data base processing method and database processing device of web television statistical analysis system; When upgrading the statistical data analysis storehouse of Web TV; Analyze the data information that is imported, simultaneously the deleted program recording of associated data field is imported the statistical data analysis storehouse, ensure the integrality of IPTV statistical analysis system database data; Avoid relevant information to be deleted, cause the inaccurate problem of statistic analysis result.
For realizing the foregoing invention purpose; One aspect of the present invention provides a kind of data base processing method of web television statistical analysis system; Through the statistical analysis system of Web TV, with the data information importing statistical data analysis storehouse of Service Database, said data base processing method comprises:
Said statistical analysis system is extracted data from said Service Database, obtains this and extracts data;
Said this extracted data to said statistical analysis system and the last fetched data compare; Search and be not present in said these extraction data; But be present in the deleted information record in the said last fetched data, the data that wherein said last fetched data are last time when said Service Database extracts data;
Said statistical analysis system is added the deleted marker field in the said deleted information record, said deleted information record is incorporated into said this extracts data, obtains the merging data data, and said merging data data is imported said statistical data analysis storehouse.
Preferably, above-mentioned described data base processing method, said statistical analysis system is being extracted said this in step that data and said last fetched data compare, and also comprises:
Search said this and extract the identical information record in data and the said last fetched data;
Said statistical analysis system is after the step that obtains said merging data data; Also comprise: delete the said identical information record in the said merging data data, the said merging data data that will delete said identical information record afterwards imports said statistical data analysis storehouse.
Preferably, above-mentioned described data base processing method also comprises: said statistical analysis system is extracted data in last time from said Service Database, when obtaining said last fetched data, preserves said last fetched data at first record form.
Preferably; Above-mentioned described data base processing method; Said statistical analysis system also comprises after the step that obtains said these extraction data: preserve said this at second record form and extract data, and simultaneously said these extraction data are copied to the 3rd record form.
Preferably; Above-mentioned described data base processing method; Said statistical analysis system is extracted said this in step that data and said last fetched data compare; Data in said first record form and said second record form are compared, afterwards said deleted information record is incorporated in said second record form, in said second record form, preserve said merging data data.
Preferably, above-mentioned described data base processing method after said merging data data is imported the step in said statistical data analysis storehouse, also comprises:
Empty said first record form,, empty said second record form and said the 3rd record form afterwards said first record form of the data importing in said the 3rd record form.
Preferably, above-mentioned described data base processing method also comprises:
Said statistical analysis system expands said statistical data analysis storehouse, increases said deleted marker field, and said delete flag field is used for writing down the deletion time of said statistical data analysis storehouse programme information record.
Preferably; Above-mentioned described data base processing method also comprises, said statistical system expands said statistical data analysis storehouse; Increase the creation-time attribute field, write down the creation-time of programme information record in the said statistical data analysis storehouse through said creation-time attribute field.
The present invention also provides a kind of database processing device of web television statistical analysis system on the other hand, is used for the data information of Service Database is imported the statistical data analysis storehouse, and said database processing device comprises:
The source data extraction unit is used for extracting data from said Service Database, obtains this and extracts data;
Data analysis unit; Be used for said these extraction data and last fetched data are compared; Search and be not present in said these extraction data; But be present in the deleted information record in the said last fetched data, the data that wherein said last fetched data are last time when said Service Database extracts data;
The data pretreatment unit; Be used for adding the deleted marker field of said deleted information record; Said deleted information record is incorporated into said this extracts data, obtain the merging data data, and said merging data data is imported said statistical data analysis storehouse.
Preferably, above-mentioned described database processing device, said data analysis unit also is used for: search said this and extract the identical information record of data and said last fetched data; Said data pretreatment unit also is used for: delete the said identical information record of said merging data data, the said merging data data that will delete said identical information record afterwards imports said statistical data analysis storehouse.
In the technique scheme of the present invention at least one has following beneficial effect:
The data base processing method of said web television statistical analysis system and database processing device; When the data information with the IPTV Service Database imports IPTV statistical data analysis storehouse; Through this extraction data information and last fetched data information are compared; Obtain the deleted information record in the business datum, the deletion time of mark deleted information record is not really deleted those records in the statistical data analysis storehouse; Come the recorded information in the regularly aging database through the deletion time mark; Thereby guarantee that the statistical data analysis storehouse imports the integrality and the accuracy of data, avoid in the prior art relative recording information to be deleted, cause the inaccurate problem of statistic analysis result;
In addition, utilize interim three record forms creating, when importing data in the statistical data analysis storehouse, the programme information record that programme information record that only increases newly synchronously and sign have the deletion time reaches the purpose that improves database storage efficient.
Embodiment
For making the object of the invention, technical scheme and advantage clearer, will combine accompanying drawing and specific embodiment to describe the present invention below.
The data base processing method of web television statistical analysis system according to the invention and device; When the data information with Web TV (IPTV) Service Database imports the statistical data analysis storehouse of Web TV; Through this was compared from the source data data that the IPTV Service Database extracts from source data data and last time that the IPTV Service Database extracts; Obtain this and extract the deleted information record of data, and upgrade the deleted marker field in this deleted information record, tag delete time with respect to the last fetched data; Afterwards this deleted information record is extracted data with this and import analytical database together; In analytical database, really do not delete those records, thereby guarantee that analytical database imports the integrality of data, and the correctness that guarantees IPTV statistical analysis system statistical report form.
Fig. 1 is the schematic flow sheet of the data base processing method of web television statistical analysis system according to the invention.Consult Fig. 1, said data base processing method comprises step:
S101, said statistical analysis system is extracted data from the IPTV Service Database, obtains this and extracts data;
S102; Said this extracted data to said statistical analysis system and the last fetched data compare; Search and be not present in said these extraction data; But be present in the deleted information record in the said last fetched data, the data that wherein said last fetched data are last time when said Service Database extracts data;
S103; Said statistical analysis system is added the deleted marker field in the said deleted information record; The record deletion time; Said deleted information record is incorporated into said this extracts data information, obtain the merging data data, and said merging data data is imported said statistical data analysis storehouse.
Data base processing method according to the invention; Through this being extracted the method that data and last fetched data compare; When the respective program recorded information is deleted in determining business datum, only the tag delete time in the analytical system database, really do not delete the field information that respective program writes down; Through the deletion time of writing down in the deleted marker field field information in the database that can regularly wear out; Thereby ensure the integrality and the accuracy of IPTV statistical analysis system database data, guarantee that the IPTV statistical analysis system counts the correctness of form, for telecom operators provide correct Operation Decision support.
Correspondingly; The IPTV analytical database of the said web television statistical analysis system of the specific embodiment of the invention, expansion increases the deleted marker field, as is " delete_date " field; Be used to write down the deletion time of respective program information record, conveniently to be used as the regularly aging of data.In addition; Said IPTV analytical database also further expands increases the creation-time attribute field; Like " Create_date " field; Be used to identify the creation-time of respective program information record, this creation-time imports when the IPTV Service Database extracts data, to make things convenient for the management of IPTV analytical database to programme information.Like table 1 is the data list structure sketch map of said IPTV analytical database.
Through in the IPTV database, increasing like " Delete_date " in the table 1 and " Create_date " record field; Deposit the deletion time and the creation-time of corresponding programme information record respectively, programme information is correctly analyzed and managed in order to make things convenient for the IPTV analytical database.
Table 1
Title |
Explanation |
Data type |
Length (byte) |
Whether essential |
ContentID |
Content |
string |
20 |
M |
Xx01 |
... |
... |
... |
... |
Xx02 |
... |
... |
... |
... |
Xx03 |
... |
... |
... |
... |
... |
... |
... |
... |
... |
Delete_date |
Be used to indicate the deletion date of this program |
number |
8 |
O |
Create_date |
Be used to indicate the date created of this program |
number |
8 |
O |
In addition; Best; Data base processing method according to the invention; When the data information with the IPTV Service Database imports IPTV statistical data analysis storehouse,, be the programme information record of non-NULL in programme information record that only synchronous importing increases newly and the deleted marker field with respect to importing data information last time.
Fig. 2 consults Fig. 2 for adopting the schematic flow sheet of data base processing method first embodiment according to the invention, and the step that the IPTV statistical analysis system is carried out database processing comprises:
S201, said statistical analysis system is extracted data from said Service Database, obtains this and extracts data;
S202, said this extracted data to said statistical analysis system and the last fetched data compare, and searches to be present in said last fetched data, do not extract the deleted information record in the data but be present in said this;
S203 compares said these extraction data and last fetched data, searches said this and extracts the identical information record in data and the said last fetched data;
S204 upgrades the deleted marker field in the said deleted information record, and the record deletion time is that the said deleted information record of non-NULL is incorporated into said this and extracts in data information with the deleted marker field, obtains the merging data data;
S205; With the said identical information record deletion in the said merging data data; Make in the said merging data data only to keep in the programme information record that increases newly and the deleted marker field to be the programme information record of non-NULL, at last with said merging data data importing IPTV statistical data analysis storehouse.
In data base processing method first embodiment according to the invention; For IPTV statistical data analysis storehouse; Be the programme information record of non-NULL in programme information record that only synchronous importing increases newly and the deleted marker field; Be not to import whole extraction data, owing to import the minimizing of data, the technique effect that the fulfillment database warehouse-in efficiency improves.
In addition; Data base processing method according to the invention; Can adopt the contents table structure after the expansion to create first record form, second record form and the 3rd record form respectively, be respectively applied for record last fetched data information, this merging data data and this that need import IPTV statistical data analysis storehouse extracts data information.
Like Fig. 3 is among data base processing method second embodiment according to the invention; Utilize interim first record form (T1 table), second record form (T2 table) and the 3rd record form of creating (T3 table), the data information of IPTV Service Database is imported the schematic flow sheet in IPTV statistical data analysis storehouse.Consult Fig. 3, second embodiment of data base processing method according to the invention comprises:
Step S301 before IPTV Service Database extraction source data information, empties the T2 table;
Step S302 empties the T3 table;
Step S303 from IPTV Service Database extraction source data information, is saved to the T2 table with this extraction source data information;
Step S304 shows the source data data backup of preserving in the T2 table to T3;
Step S305; With this extraction source data information of preserving in last fetched data of preserving in the T1 table and the T2 table relatively; According to " content address (Content ID) " field of each programme information record in the form, do not search and be present in the table 2, but be present in the deleted information record in the T1 table;
Step S306 upgrades the deleted marker field that those deleted informations write down, the record deletion time in the T1 table;
Step S307 with this extraction source data information of preserving in the last fetched data of preserving in the T1 table and the T2 table relatively, according to " content address (Content ID) " field of each programme information record in the form, searches the identical information record of T2 table in showing with T1;
Step S308, those identical information records in the deletion T2 table;
Step S309 is inserted into the deleted information record in the T1 table in the T2 table, and wherein the deleted marker field of those deleted information records is non-NULL;
Step S310, with the data importing in the T2 table to IPVT statistical data analysis storehouse;
Step S311 empties the T1 table;
Step S312 extracts data backup to the T1 table with this that preserve in the T3 table, is used for next time from IPTV Service Database extraction source data information the time, the usefulness of making comparisons.
Second embodiment through data base processing method according to the invention; When the data information of IPTV Service Database is imported the statistical data analysis storehouse of Web TV; Through this extraction data information and last fetched data information are compared, obtain the deleted information record in the business datum, the tag delete time in the statistical data analysis storehouse; Really do not delete those records, thereby guarantee that the statistical data analysis storehouse imports the integrality of data; In addition, utilize interim three record forms creating, when importing data in the statistical data analysis storehouse, the programme information record that programme information record that only increases newly synchronously and sign have the deletion time reaches the purpose that improves database storage efficient.
The present invention also provides a kind of database processing device of web television statistical analysis system on the other hand, is used for the data information of IPTV Service Database is imported IPTV statistical data analysis storehouse, and like Fig. 4, this database processing device 10 comprises:
Source data extraction unit 11 is used for extracting data from IPTV Service Database 20, obtains this and extracts data;
Data analysis unit 12; Be used for said these extraction data and last fetched data are compared; Search and be present in said last fetched data; Extract the deleted information record in data but be present in said this, the data that wherein said last fetched data are last time when IPTV Service Database 20 extracts data;
Data pretreatment unit 13; Said statistical analysis system is added the deleted marker field in the said deleted information record; Said deleted information record is incorporated into said this extracts data information; Obtain the merging data data, and said merging data data is imported IPTV statistical data analysis storehouse 30.
Wherein, this data analysis unit 12 also is used for searching the identical information record of said these extraction data and said last fetched data; Data processing unit 13 is then according to lookup result; Said identical information record in the deletion merging data data; Make in the said merging data data only to keep in the programme information record that increases newly and the deleted marker field to be the programme information record of non-NULL, at last with said merging data data importing IPTV statistical data analysis storehouse 30.
In addition; Said database processing device 10 also comprises memory cell 14; Be used to create three interim record forms; Wherein preserve said last fetched data at first record form, the said merging data data in second record form preservation needs importing IPTV statistical data analysis storehouse is preserved said this at the 3rd record form and is extracted data.
Said database processing device 10 utilizes this three record forms, and the method that the data information of IPTV Service Database 20 is imported IPTV statistical data analysis storehouse 30 is consulted shown in Figure 3, is not described in detail in this.
Said database processing device 10; When the data information with IPTV Service Database 20 imports IPTV statistical data analysis storehouse 30; Business datum is handled and compared, when some recorded information is deleted in determining business datum, only to those recorded information tag delete times of IPTV statistical analysis system database; Those information in the inreal delete database; Through the recorded information in the regularly aging database of deletion time tag, adopt incremental update (not deletion record information), regularly aging strategy to come the maintenance data base system, ensure the integrality and the accuracy of IPTV statistical analysis system database data.
In addition, when importing data in the statistical data analysis storehouse, only importing the programme information record that increases newly and importing the deleted marker field is the programme information record of non-NULL, reaches the purpose that improves database storage efficient.
The above only is a preferred implementation of the present invention; Should be pointed out that for those skilled in the art, under the prerequisite that does not break away from the principle of the invention; Can also make some improvement and retouching, these improvement and retouching also should be regarded as protection scope of the present invention.