CN106484894B - A kind of multilist associated data based on relevant database shows method and system - Google Patents
A kind of multilist associated data based on relevant database shows method and system Download PDFInfo
- Publication number
- CN106484894B CN106484894B CN201610915632.XA CN201610915632A CN106484894B CN 106484894 B CN106484894 B CN 106484894B CN 201610915632 A CN201610915632 A CN 201610915632A CN 106484894 B CN106484894 B CN 106484894B
- Authority
- CN
- China
- Prior art keywords
- module
- qlikview
- data
- col
- multilist
- 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
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/26—Visual data mining; Browsing structured data
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
A kind of multilist associated data based on relevant database shows method, the front end of multilist associated data using QlikView reports as relevant database shows means, to each detail list comprising external key of the same name without attended operation, but QlikView is loaded into using them as back-end data to be associated, finally carrying out multilist associated data by QlikView fields relevance shows.By each detail list under the premise of external key content to be associated is not changed, summarize in advance, be re-used as back-end data loading；At this point, QlikView will carry out content association to each table of same file-name field, it can obtain and correctly summarize data.
Description
Technical field
The present invention relates to business intelligence, the analysis of public opinion fields, and redundant recording is generated for relevant database multi-table join
Situation propose a kind of processing method.In particular to a kind of multilist associated data side of showing based on relevant database
Method and device.
Background technology
With the fast development of internet economy, more and more users tend to disappear by e-commerce platform
Take.Correspondingly, the data information generated based on electric business platform, business intelligence field starts to show up prominently.The key of business intelligence
To extract useful data and to be cleared up from different operation informations, to ensure data correctness, then by extracting, conversion and
Loading process obtains the global view of a business data, is analyzed on this basis using suitable query analysis tool
Processing, is presented to manager by knowledge, support is provided for decision process.Mainstream relational data used by business intelligence field
There are Oracle, DB2, Microsoft SQL Server, Microsoft Access, MySQL etc. in library, and used front end shows
Tool has QlikView, Tableau etc..
New station safety net is domestic B2C internets insurance industry pioneer, and it is unexpected that insurance products cover traffic/synthesis
Danger, domestic/danger of overseas travelling, health insurance, group danger, family property insurance, shipping danger, financing danger, vehicle insurance, life insurance, production duty danger etc.
10 insurance kinds, lower list platform include PC ends, touch screen end, cell phone application end, have formed stable user group and the ecosphere, deep tool analysis
Value.Thus the present invention relies on new station safety net demonstration implementation result.
In oracle database, if Table A external key col_fk and table B external keys col_fk includes repetition values, by Table A and B
During by external key col_fk connections, no matter using left outside connection, right outer connection or interior connection, redundant recording can be all generated.To existing
It is exemplified below with the presence of the problem of technology：
Table 1 sales promotion information table promotion_info, external key order_id include repetition values
| cal_number | activity_id | activity_name | order_id | order_plat |
| 20150914 | 2408 | It places an order and send integration | 150914310611 | Official website |
| 20150914 | 2571 | Integration deduction cash | 150914310611 | Official website |
| 20150914 | 2979 | It places an order to stand and subtracts 8% | 150914310611 | Official website |
Table 2 sequence information table policy_order_info, external key order_id include repetition values
Table 3 includes the query result (only listing part field) of redundant recording
Table 4 20150914 summarizes data (only listing part field), and several indexs are repeated accumulative
| cal_number | price | cut_amt | pts_amt | points |
| 20150914 | 306.00 | 24.48 | 7.80 | 258 |
Table 1 records advertising campaign effective date, movable ID, activity name, order ID and lower single platform.1 order in table 1
Take part in 3 kinds of advertising campaigns.
Table 2 records declaration form ID, product IDs, the declaration form amount of money, discount amount, the integration deduction amount of money, integration, insurance kind, order ID.
1 order includes 7 declaration forms in table 2, and every declaration form corresponds to 1 section of insurance products.
At this point, no matter using left join, right join, inner join, by table 1, table 2 passes through order_id words
Section connection, can return to 21 query results for including redundant recording, such as table 3, each policy_id has more 2 redundant recordings.
If summarizing 20150914 same day data based on table 3, the accumulative declaration form amount of money, discount amount, integration deduction can be repeated
The indexs such as the amount of money, integration, such as table 4.As back-end data, QlikView front ends can not just show correct sales promotion information.
The present invention prepares back-end data by oracle database, shows means using QlikView reports as front end, carries
Go out a kind of multilist associated data based on relevant database and show method and apparatus.
Invention content
The present invention seeks to overcome existing QlikView reports to have the inquiry of redundant recording when showing means as front end more
As a result；The present invention prepares back-end data by oracle database, and means are showed using QlikView reports as front end, proposes one
Multilist associated data of the kind based on relevant database shows method and apparatus.
Technical solution of the present invention：A kind of multilist associated data based on relevant database shows method and system, with
QlikView reports show means as the front end of the multilist associated data of relevant database, to (having comprising external key of the same name
Repetition values) each detail list without attended operation, but be loaded into QlikView using them as back-end data to be associated, most
Multilist associated data is carried out by QlikView fields relevance eventually to show.
It is worth noting that, in order to mitigate QlikView fields association burden, the present invention treats each detail list not changing
Under the premise of being associated with external key content, summarize in advance, be re-used as back-end data loading；At this point, QlikView will be to same file-name field
Each table carries out content association, can obtain and correctly summarizes data.
Step is as follows：
Step 1. integrates out the backstage detail list for including external key col_fk of the same name in oracle database；
Step 11. integrates out the detail list 1 comprising col_fk, remembers Table A, and wherein col_fk has repetition values；
Step 12. integrates out the detail list 2 comprising col_fk, remembers that table B, wherein col_fk have repetition values；
Step 2. summarizes detail list under the premise of col_fk contents are not changed；
Step 21. retains col_fk contents, and Table A is summarized, note table C；
Step 22. retains col_fk contents, and table B is summarized, note table D；
Table C, table D are loaded into QlikView front ends by step 3. as back-end data, ensure table C, table D external key col_ to be associated
Fk titles are consistent, and QlikView will carry out content association to each table comprising same file-name field；
Step 4. designs QlikView modules, and by table C, table D Data Integrations can summarize for one to the varigrained overall situation
View shows correct data.
Equally, multiple detail lists also retain external key content to be associated and converge each table in advance by the way that QlikView is interrelated
Always, it is re-used as back-end data and is loaded into QlikView, finally carrying out multilist associated data by QlikView fields relevance shows.
Beneficial outcomes of the present invention are as follows：Multilist external key (comprising the repetition values) related question showed for business intelligence data
There is provided a kind of simplicity reliable solution, can finally generate one can summarize to varigrained global view, show just
Exact figures evidence.The present invention prepares back-end data by oracle database, shows means using QlikView reports as front end,
QlikView will carry out content association to each table of same file-name field, can obtain and correctly summarizes data.
Description of the drawings
Fig. 1 shows method flow diagram for multilist associated data；
Fig. 2 is multilist associated data demonstration device structure diagram.
Specific embodiment
Below in conjunction with drawings and examples, it is specifically described embodiment of the present invention.Described embodiment is merely illustrative, base
In the substantive change made of the technology of the present invention or equivalent variations, the scope of the present invention is still fallen within.
As shown in fig.1, flow of the embodiment of the present invention is as follows：
Step 11. integrates promotion, sequence information, and sales promotion information table promotion_info is generated in oracle database
And declaration form sequence information table policy_order_info, screen the detailed data conduct of order_id=150914310611
Embodiment, i.e. table 1, table 2；Wherein, table 1, the order_id fields of table 2 have repetition values；
Step 12. summarizes detail list under the premise of order_id field contents are not changed.Table 1 need not summarize, table 2
Retain order_id information, using product IDs as dimension, each policy_id is summarized for declaration form number, and the corresponding amount of money that adds up, generation
Table 5；
Table 1, table 5 are loaded into QlikView front ends by step 13. as back-end data, ensure table 1, the order_id words of table 5
Name section is consistent；
Step 14. designs module, can summarize to varigrained global view by the generation of QlikView fields relevance,
Show correct data, be the data summarized according to different demands such as table 6, table 7, table 8 etc..
The data that table 5 is summarized by table 2
Summarize data (only listing part field) in 6 QlikView front ends 20150914 of table
| cal_number | price | cut_amt | pts_amt | points |
| 20150914 | 102.00 | 7.71 | 2.60 | 86 |
7 QlikView front ends of table, which place an order, send integration summary of activity data (only listing part field)
8 QlikView front ends home journey insurance of table summarizes data (only listing part field)
As shown in fig.2, apparatus structure of the embodiment of the present invention is as follows：
Module 1:Data source unit, this module stores new station safety net detail transaction record, including product information, order
Information, promotional offer information etc. provides data for subsequent module.
Module 2:Data pre-processing unit, this module integrate out each detail comprising external key of the same name in oracle database
Table, and summarize each detail list under the premise of foreign key information is retained.
Module 3:Oracle back-end datas are loaded into QlikView front ends by QlikView data loading units, this module, are carried
It is fashionable to ensure that each summary sheet external key title to be associated is consistent.
Module 4:QlikView data display units, this module can be summarized using the generation of QlikView fields relevance to not
The global view of one-size, each input selection module of arranging in pairs or groups, multi-angle show correct data；
Module 41:Date selection module, this module can select different time according to day, week, the moon, season, year any switching laws
Range；
Module 42:Activity selecting module, this module can select discount coupon, completely subtract, discount, integration deduction, more multiplicatrixs grade
The advertising campaign of type；
Module 43:Insurance kind selecting module, this module can select the insurance products of the insurance kinds such as family property insurance, financing danger, vehicle insurance；
Module 44:Lower list platform selecting module, this module can select single mode under PC ends, touch screen end, cell phone application end etc..
Those of ordinary skills in the art should understand that：The above is only a specific embodiment of the present invention, and
The limitation present invention is not used in, all within the spirits and principles of the present invention, any modification, equivalent substitution, improvement and etc. done,
It should be included within protection scope of the present invention.
Claims (3)
1. a kind of multilist associated data based on relevant database shows method, it is characterized in that, using QlikView reports as
The front end of the multilist associated data of relevant database shows means, and each detail list comprising external key of the same name is grasped without connection
Make, but QlikView is loaded into using them as back-end data to be associated, finally carried out by QlikView fields relevance
Multilist associated data shows；
By each detail list under the premise of external key content to be associated is not changed, summarize in advance, be re-used as back-end data loading；This
When, QlikView will carry out content association to each table of same file-name field, correctly be summarized data；
Step is as follows：
Step 1. integrates out the backstage detail list for including external key col_fk of the same name in oracle database；
Step 11. integrates out the detail list 1 comprising col_fk, remembers Table A, and wherein col_fk has repetition values；
Step 12. integrates out the detail list 2 comprising col_fk, remembers that table B, wherein col_fk have repetition values；
Step 2. summarizes detail list under the premise of col_fk contents are not changed；
Step 21. retains col_fk contents, and Table A is summarized, note table C；
Step 22. retains col_fk contents, and table B is summarized, note table D；
Table C, table D are loaded into QlikView front ends by step 3. as back-end data, ensure table C, external key col_fk to be associated of table D
Claim unanimously, QlikView will carry out content association to each table comprising same file-name field；
Step 4. designs QlikView modules, and by table C, table D Data Integrations can summarize for one to varigrained global view,
Show correct data.
2. multilist associated data according to claim 1 shows method, it is characterized in that, multiple detail lists pass through QlikView
It is interrelated also to retain external key content to be associated and summarize each table in advance, it is re-used as back-end data and is loaded into QlikView, finally borrow
QlikView fields relevance is helped to carry out multilist associated data to show.
What 3. the multilist associated data based on relevant database according to one of claim 1-2 showed method composition is
System, it is characterized in that including module 1-4, the structure and function of module 1-4 is as follows：
Module 1:Data source unit, this module storage safety net detail transaction record, including product information, sequence information, promotion
Favor information provides data for subsequent module；
Module 2:Data pre-processing unit, this module integrate out each detail list comprising external key of the same name in oracle database,
And each detail list is summarized under the premise of foreign key information is retained；
Module 3:Oracle back-end datas are loaded into QlikView front ends by QlikView data loading units, this module, during loading
Ensure that each summary sheet external key title to be associated is consistent；
Module 4:QlikView data display units, this module include submodule 41-44；It is given birth to using QlikView fields relevance
Into summarizing to varigrained global view, each input selection module of arranging in pairs or groups, multi-angle shows correct data；
Module 41:Date selection module, this module select different time range according to day, week, the moon, season, year any switching laws；
Module 42:Activity selecting module, this module select discount coupon, completely subtract, discount, integration deduction, more times of integral types rush
Pin activity；
Module 43:Insurance kind selecting module, this module can determine the insurance products of family property insurance, financing danger, vehicle insurance insurance kind；
Module 44:Lower list platform selecting module, this module can select single mode under PC ends, touch screen end, cell phone application end.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610915632.XA CN106484894B (en) | 2016-10-20 | 2016-10-20 | A kind of multilist associated data based on relevant database shows method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610915632.XA CN106484894B (en) | 2016-10-20 | 2016-10-20 | A kind of multilist associated data based on relevant database shows method and system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106484894A CN106484894A (en) | 2017-03-08 |
| CN106484894B true CN106484894B (en) | 2018-07-06 |
Family
ID=58270305
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610915632.XA Active CN106484894B (en) | 2016-10-20 | 2016-10-20 | A kind of multilist associated data based on relevant database shows method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106484894B (en) |
Family Cites Families (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7580922B2 (en) * | 2005-01-04 | 2009-08-25 | International Business Machines Corporation | Methods for relating data in healthcare databases |
| CN100589101C (en) * | 2007-08-02 | 2010-02-10 | 上海宝信软件股份有限公司 | Data access method based on the Oracle relational database of routine call interface |
| US9251179B2 (en) * | 2012-04-12 | 2016-02-02 | International Business Machines Corporation | Managing record location lookup caching in a relational database |
| US10324917B2 (en) * | 2012-10-15 | 2019-06-18 | Qliktech International Ab | Methods and systems for data management |
| EP2846269A1 (en) * | 2013-09-10 | 2015-03-11 | Value Lab S.r.l. | Connector for a BI (Business Intelligence) platform |
-
2016
- 2016-10-20 CN CN201610915632.XA patent/CN106484894B/en active Active
Also Published As
| Publication number | Publication date |
|---|---|
| CN106484894A (en) | 2017-03-08 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Bhatia | Data mining and data warehousing: principles and practical techniques | |
| US8849826B2 (en) | Sentiment analysis from social media content | |
| JP5810452B2 (en) | Data collection, tracking and analysis methods for multimedia including impact analysis and impact tracking | |
| US9858326B2 (en) | Distributed data warehouse | |
| Veronin et al. | A systematic approach to'cleaning'of drug name records data in the FAERS database: a case report | |
| US8972463B2 (en) | Method and apparatus for functional integration of metadata | |
| WO2022006135A1 (en) | Artificial intelligence for keyword recommendation | |
| RU2012144649A (en) | PRODUCT SYNTHESIS FROM MULTIPLE SOURCES | |
| Arthur | Studying the UK job market during the COVID-19 crisis with online job ads | |
| US20210125294A1 (en) | Systems and methods for identifying potential real estate buyers | |
| US10235449B1 (en) | Extracting product facets from unstructured data | |
| Li et al. | The association between exposure to point‐of‐sale anti‐smoking warnings and smokers' interest in quitting and quit attempts: Findings from the International Tobacco Control Four Country Survey | |
| Yaakub et al. | Integration of opinion into customer analysis model | |
| Blackman et al. | Retailers’ perceptions of FDA tobacco regulation authority | |
| Giabbanelli et al. | Feasibility and framing of interventions based on public support: leveraging text analytics for policymakers | |
| Kadre et al. | Practical Business Analytics Using SAS: A Hands-on Guide | |
| Durham et al. | A model architecture for Big Data applications using relational databases | |
| Firdaus et al. | Indonesian presidential election sentiment: Dataset of response public before 2024 | |
| US10140578B1 (en) | System and method for managing social-based questions and answers | |
| CN106484894B (en) | A kind of multilist associated data based on relevant database shows method and system | |
| Fan et al. | Conditional dependencies: A principled approach to improving data quality | |
| Tanimura | SQL for Data Analysis | |
| KR101215307B1 (en) | Providing method of search result using association quiz | |
| Ashkani et al. | Influence of consumer brand relationship on brand evangelism | |
| Cioffi et al. | Identifying and correcting invalid citations due to DOI errors in Crossref data |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |