CN116823381A - Data query method, system, equipment and medium of snowflake model - Google Patents
Data query method, system, equipment and medium of snowflake model Download PDFInfo
- Publication number
- CN116823381A CN116823381A CN202310513467.5A CN202310513467A CN116823381A CN 116823381 A CN116823381 A CN 116823381A CN 202310513467 A CN202310513467 A CN 202310513467A CN 116823381 A CN116823381 A CN 116823381A
- Authority
- CN
- China
- Prior art keywords
- data
- dimension table
- dimension
- level
- information
- 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.)
- Pending
Links
- 241000533950 Leucojum Species 0.000 title claims abstract description 53
- 238000000034 method Methods 0.000 title claims abstract description 31
- 238000004590 computer program Methods 0.000 claims description 10
- 238000012986 modification Methods 0.000 description 7
- 230000004048 modification Effects 0.000 description 7
- 238000010586 diagram Methods 0.000 description 5
- 238000012545 processing Methods 0.000 description 4
- 230000000694 effects Effects 0.000 description 2
- 238000012216 screening Methods 0.000 description 2
- 238000003491 array Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000012946 outsourcing Methods 0.000 description 1
- 238000005192 partition Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q30/00—Commerce
- G06Q30/06—Buying, selling or leasing transactions
- G06Q30/0601—Electronic shopping [e-shopping]
- G06Q30/0633—Lists, e.g. purchase orders, compilation or processing
- G06Q30/0635—Processing of requisition or of purchase orders
-
- 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/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Abstract
The invention discloses a data query method, a system, equipment and a medium of a snowflake model. The snowflake model comprises a fact table, a first-level dimension table and a second-level dimension table; the data query method comprises the following steps: acquiring data information to be queried, wherein the data information to be queried comprises first-level dimension information to be queried and second-level dimension information to be queried of storage data; determining a corresponding target first-level dimension table according to the first-level dimension information to be queried, and determining a target second-level dimension table corresponding to the target first-level dimension table according to the second-level dimension information to be queried; determining a target data identification number corresponding to the data information to be queried from a target second-level dimension table according to the second-level dimension information to be queried; and determining the fact data of the target data identification number according to the target data identification number and the fact table. By using the method, the fact data of the data identification numbers of the multiple dimensions can be quickly queried, and when the dimension data is modified, other data of the stored data does not need to be modified.
Description
Technical Field
The invention relates to the field of computers, in particular to a snowflake model data query method, a snowflake model data query system, snowflake model data query equipment and a snowflake model data query medium.
Background
In the prior art, data can only be queried based on a common star model, when data with more complex dimensions is queried, the supported dimensions are few, when next data with different dimensions is queried, the data in a dimension table needs to be modified, and the data query speed is low.
Disclosure of Invention
The invention aims to overcome the defects that in the prior art, the supported dimensionality is small, when next data of different dimensionalities is queried, data in a dimensionality table is required to be modified, and the data query speed is low, and provides a data query method, a system, equipment and a medium of a snowflake model.
The invention solves the technical problems by the following technical scheme:
a data query method of a snowflake model comprises a fact table, a first-stage dimension table and a second-stage dimension table corresponding to the first-stage dimension table;
the fact table comprises data identification numbers of stored data and fact data corresponding to the data identification numbers, each first-level dimension table comprises first-level dimension information of each stored data, and the first-level dimension information comprises second-level dimensions; each second-level dimension table comprises second-level dimension information corresponding to each data identification number in the second-level dimension;
the data query method comprises the following steps:
acquiring data information to be queried, wherein the data information to be queried comprises first-level dimension information to be queried and second-level dimension information to be queried of storage data;
determining a corresponding target first-stage dimension table according to the first-stage dimension information to be queried, and determining a target second-stage dimension table corresponding to the target first-stage dimension table according to the second-stage dimension information to be queried;
determining a target data identification number corresponding to the data information to be queried from the target second-level dimension table according to the second-level dimension information to be queried;
and determining the fact data of the target data identification number according to the target data identification number and the fact table.
Preferably, the first-level dimension table includes at least two of a time dimension table, a region dimension table, a product dimension table, a department dimension table, an age dimension table, and a gender dimension table.
Preferably, if the first-level dimension table includes a time dimension table, the second-level dimension table corresponding to the time dimension table includes at least one of a year dimension table, a quarter dimension table, a month dimension table and a day dimension table;
preferably, if the first-level dimension table includes a region dimension table, the second-level dimension table corresponding to the region dimension table includes at least one of a country dimension table, a province dimension table, a city dimension table and a county dimension table;
preferably, if the first-level dimension table includes a product dimension table, the second-level dimension table corresponding to the product dimension table includes at least one of a product name dimension table, a product selling price dimension table, a product description dimension table and a product quality dimension table;
preferably, if the first-level dimension table includes a department dimension table, the second-level dimension table corresponding to the department dimension table includes a head office dimension table, a branch office dimension table, a department dimension table, and an agent department dimension table.
Preferably, the fact data comprises a first type of fact data and/or a second type of fact data;
the step of determining the fact data of the target data identification number according to the target data identification number and the fact table specifically comprises the following steps:
and acquiring first-class fact data and/or second-class fact data of the target data identification number, and counting the first-class fact data and/or the second-class fact data according to second-class dimension information.
Preferably, the step of obtaining the data information to be queried specifically includes:
inputting the information to be queried into a data query model, and inputting the output result of the data query model into a database where a snowflake model is positioned;
the data query model is used for converting the input information to be queried into a query language conforming to the structural specification of the snowflake model.
As a second aspect of the invention, the invention provides a data query system of a snowflake model, wherein the snowflake model comprises a fact table, a first-level dimension table and a second-level dimension table corresponding to the first-level dimension table;
the fact table comprises data identification numbers of stored data and fact data corresponding to the data identification numbers, each first-level dimension table comprises first-level dimension information of each stored data, and the first-level dimension information comprises second-level dimensions; each second-level dimension table comprises second-level dimension information corresponding to each data identification number in the second-level dimension;
the data query system comprises: the system comprises a data information acquisition module to be queried, a data identification number query module and a fact data determination module;
the data information to be queried is used for acquiring data information to be queried, and the data information to be queried comprises first-level dimension information to be queried and second-level dimension information to be queried of storage data;
the data identification number inquiring module is used for determining a corresponding target first-stage dimension table according to the first-stage dimension information to be inquired, and determining a target second-stage dimension table corresponding to the target first-stage dimension table according to the second-stage dimension information to be inquired;
determining a target data identification number corresponding to the data information to be queried from the target second-level dimension table according to the second-level dimension information to be queried;
the fact data determining module is used for determining the fact data of the target data identification number according to the target data identification number and the fact table.
Preferably, the first-level dimension table includes at least two of a time dimension table, a region dimension table, a product dimension table, a department dimension table, an age dimension table, and a gender dimension table.
Preferably, if the first-level dimension table includes a time dimension table, the second-level dimension table corresponding to the time dimension table includes at least one of a year dimension table, a quarter dimension table, a month dimension table and a day dimension table;
preferably, if the first-level dimension table includes a region dimension table, the second-level dimension table corresponding to the region dimension table includes at least one of a country dimension table, a province dimension table, a city dimension table and a county dimension table;
preferably, if the first-level dimension table includes a product dimension table, the second-level dimension table corresponding to the product dimension table includes at least one of a product name dimension table, a product selling price dimension table, a product description dimension table and a product quality dimension table;
preferably, if the first-level dimension table includes a department dimension table, the second-level dimension table corresponding to the department dimension table includes a head office dimension table, a branch office dimension table, a department dimension table, and an agent department dimension table.
Preferably, the fact data comprises a first type of fact data and/or a second type of fact data;
the fact data determining module is specifically configured to: and acquiring first-class fact data and/or second-class fact data of the target data identification number, and counting the first-class fact data and/or the second-class fact data according to second-class dimension information.
Preferably, the fact data determining module is specifically configured to: inputting the information to be queried into a data query model, and inputting the output result of the data query model into a database where a snowflake model is positioned;
the data query model is used for converting the input information to be queried into a query language conforming to the structural specification of the snowflake model.
As a third aspect of the present invention, the present invention provides an electronic device comprising a memory, a processor and a computer program stored on the memory and adapted to run on the processor, the processor implementing the data query method of the snowflake model of the first aspect of the present invention when executing the computer program.
As a fourth aspect of the present invention, there is provided a computer-readable storage medium having stored thereon a computer program which, when executed by a processor, implements the data query method of the snowflake model in the first aspect of the present invention. On the basis of conforming to the common knowledge in the field, the above preferred conditions can be arbitrarily combined to obtain the preferred examples of the invention.
The invention has the positive progress effects that: determining a corresponding first-level dimension table and a corresponding target second-level dimension table according to the second-level dimension information to be queried, determining a target data identification number according to the second-level dimension information in the target second-level dimension table, and determining corresponding target fact information in the fact table. When inquiring the fact data of the next different dimension, the second-stage dimension table is not required to be processed, and only the corresponding second-stage dimension table is required to be selected, so that the fact data of any dimension combination can be quickly obtained, and the data inquiring speed is high. And when one of the second-level dimensions is modified, no modification is required to the other second-level dimension data of the stored data.
Drawings
FIG. 1 is a flow chart of a snowflake model data query method in the invention.
Fig. 2 is a schematic diagram of a first structure of a snowflake model according to the present invention.
Fig. 3 is a second structural schematic diagram of the snowflake model in the present invention.
Fig. 4 is a schematic view of a third structure of the snowflake model in the present invention.
Fig. 5 is a schematic structural diagram of a snowflake model data query system in the present invention.
Fig. 6 is a schematic structural diagram of an electronic device according to the present invention.
Detailed Description
The invention is further illustrated by means of the following examples, which are not intended to limit the scope of the invention.
Example 1
Referring to fig. 1, the present embodiment provides a data query method for a snowflake model.
The data query method is applied to a snowflake model, and the snowflake model comprises a fact table, a first-stage dimension table and a second-stage dimension table corresponding to the first-stage dimension table.
The fact table comprises data identification numbers of the data and the fact data corresponding to the data identification numbers, each first-level dimension table comprises first-level dimension information of each data, and the first-level dimension information comprises second-level dimensions; each second-level dimension table includes second-level dimension information corresponding to each data identification number in a second-level dimension.
The data query method comprises the following steps:
s1, acquiring data information to be queried.
The data information to be queried comprises first-level dimension information to be queried and second-level dimension information to be queried of the data.
S2, determining a corresponding target first-stage dimension table according to the first-stage dimension information to be queried, and determining a target second-stage dimension table corresponding to the target first-stage dimension table according to the second-stage dimension information to be queried.
S3, determining a target data identification number corresponding to the data information to be queried from the target second-level dimension table according to the second-level dimension information to be queried.
S4, determining the fact data of the target data identification number according to the target data identification number and the fact table.
In this embodiment, the fact data in the fact table is generally unchanged data such as the price of the data, the amount of sales of the order, and the like.
In an alternative embodiment, the first level dimension tables include at least two of a time dimension table, a region dimension table, a product dimension table, a department dimension table, an age dimension table, and a gender dimension table.
Referring to fig. 2, in this embodiment, for example, the stored data is order data, where the data identifier of the order may be the order number of the order, and the fact data includes sales amounts of the respective orders, where the information to be queried is the total sales amount of the order sold in the Shanghai on 3 months and 3 days. At this time, the first-level dimension information to be queried is a time dimension and a region dimension, and the second-level dimension information to be queried is a day dimension and a city dimension. The first-level dimension information of the information to be queried comprises a time dimension (at this time, a first-level dimension table of the time dimension is a time dimension table) and a region dimension (at this time, a first-level dimension table of the region dimension is a region dimension table); the time dimension table comprises a year key, a quarter key, a month key and a day key, and the second-level dimension table (a year dimension table, a Ji Weidu table, a month dimension table and a day dimension table) corresponding to the different keys can be accessed by clicking the different keys. Determining a corresponding target first-level dimension table as a time dimension table through the time dimension in the first-level dimension information to be queried, and determining a corresponding target second-level dimension table as a day dimension table according to the day dimension in the second-level dimension information to be queried; and determining the corresponding target first-level dimension table as a region dimension table through the region dimension in the first-level dimension information to be queried, and determining the corresponding target second-level dimension table as a city dimension table according to the city dimension in the dimension information to be queried. In the above embodiments, the first level dimension may be multiple, such as an architecture dimension, a product dimension, and the like. The department dimension comprises a department dimension, a head office dimension, a branch office dimension, an agent outsourcing dimension and the like; the product dimensions may include a second level dimension of product name, product description, product selling price, product quality, and the like. Each second-level dimension includes a data identification number and second-level dimension information corresponding to the data identification number (in order data, an order number may be present).
The above dimensions are merely descriptions of the dimensions, and the corresponding first-level dimension table and second-level dimension table may be specifically set according to specific situations.
In this embodiment, the fact table may further include different fact data types, and further, the different fact tables are used to store the different fact data types.
Referring to fig. 3, for example, the first fact table includes only the first type of fact data, and the second fact table includes only the second type of fact data. For example, the first fact table may store sales amount, the second fact table may store sales amount, when the information to be queried is sales amount of a certain day, the second fact table may be determined to be a target fact table, then the corresponding target data identification number is determined from the day dimension table, and finally the sales amount of the day is finally determined according to the target identification number in the second fact table.
In this embodiment, the target second-level dimension table and the target fact table may be determined first. And determining the final fact data according to the second-level dimension table corresponding to each target fact table.
Referring to fig. 4, in one embodiment, the stored data may be visitor data, marketing data, or sales data. The visitor data, marketing data, and sales data may be stored in a first fact table, a second fact table, and a third fact table, respectively. The visitor data comprise sales store visitors, channel visitors, vehicle type visitors, visitors received by each sales person id, reception times, visitor dates and visitor partitions; the marketing data includes marketing vehicle type, marketing channel, number of marketing clicks, marketing display duration, marketing cost, marketing date, marketing zone. The sales data includes sales order id, sales date, sales price, sales payment type, sales store city, sales frame number, sales vehicle model, sales person id.
The first dimension table and the second dimension table in fig. 4 each correspond to the first-stage dimension table in the above-described embodiment. Fig. 4 may perform left join (an association relationship) by dimension data of the vehicle model in the first fact table and the vehicle model in the second dimension table. And because the second dimension table is the first-level dimension table, dimension information of each vehicle type visitor in visitor data can be accessed through each key in the second dimension table, for example, in the vehicle type visitor, the visitor views which vehicle type (namely the vehicle type name), the suggested retail price interval of the vehicle type, the name of the manufacturer of the vehicle type, the power type of the vehicle type (such as an automobile or a trolley), the corresponding automobile level of the vehicle type, the release time of the vehicle type and the like.
In the embodiment, the data to be queried is the recommended retail price of 10 ten thousand to 15 ten thousand, the power type is the car type of the electric car, and the total number of visitors. It may be determined first that the fact data to be looked up is guest data and that the fact table is the first fact data table. Since the dimensions of the data to be queried are suggested retail price dimensions and dynamic types, the target first-level dimension table is determined to be a second dimension table (including vehicle model dimension data). And determining a dimension table of the suggested retail price dimension and a dimension table of the dynamic type dimension according to the specific suggested retail price dimension and the dynamic type. The corresponding first target data is found in the dimension table of the suggested retail price dimension, and the second target data with the power type being electric is found in the power type dimension table. And finally determining the recommended retail price of 10 ten thousand to 15 ten thousand according to the data identification number of the first target data and the data identification number of the second target data in the first event list, wherein the power type is the vehicle type of the electric vehicle, and the data identification number of each visitor and finally determining the total number of the visitors of the electric vehicle of 10 ten thousand to 15 ten thousand.
In an alternative embodiment, if the first-level dimension table includes a time dimension table, the second-level dimension table corresponding to the time dimension table includes at least one of a year dimension table, a quarter dimension table, a month dimension table, and a day dimension table.
In an alternative embodiment, if the first-level dimension table includes a region dimension table, the second-level dimension table corresponding to the region dimension table includes at least one of a country dimension table, a province dimension table, a city dimension table, and a county dimension table.
In an alternative embodiment, if the first-level dimension table includes a product dimension table, the second-level dimension table corresponding to the product dimension table includes at least one of a product name dimension table, a product selling price dimension table, a product description dimension table, and a product quality dimension table.
In an alternative embodiment, if the first level dimension table includes a department dimension table, the second level dimension table corresponding to the department dimension table includes a head office dimension table, a branch office dimension table, a department dimension table, and a proxy department dimension table.
In an alternative embodiment, the fact data comprises a first type of fact data and/or a second type of fact data.
The step of determining the fact data of the target data identification number according to the target data identification number and the fact table specifically comprises the following steps:
and acquiring the first-class fact data and/or the second-class fact data of the target data identification number, and counting the first-class fact data and/or the second-class fact data according to the second-class dimension information.
In this embodiment, two types of fact data (may be a first type of fact data and a second type of fact data) may be included in the fact table, for example, the fact data includes sales amount and sales number, and at least one type of fact data may be selected for determination when determining data of certain dimensions.
In an alternative embodiment, the step of obtaining the data information to be queried specifically includes:
and inputting the information to be queried into a data query model, and inputting the output result of the data query model into a database where the snowflake model is positioned.
The data query model is used for converting the input information to be queried into a query language conforming to the structural specification of the snowflake model.
In this embodiment, different types of data query models may be configured according to different types of databases, for example, a model satisfying a language of at least one of Hive (one database type), spark (one database type), prest (one database type), and click house (one database type) may be configured.
In this embodiment, since the data of the snowflake model can be queried by a user without language base when querying the data, a data query model is configured at this time, and the data query model can convert the input text into a query sentence conforming to the database, for example, as long as "time" is input: 3 months and 3 days; sales site: shanghai; querying items: sales amount "will be converted into a corresponding database query statement. Thus, the cost of learning by the user can be saved. In this embodiment, the text input by the user may also be input through operations such as dragging, so as to improve the efficiency of the user in querying the data.
In the method, the obtained data can be finally queried, the sentences in the database language can be translated into natural sentences (for example, the time is 3 months and 3 days, the sales place is Shanghai, the sales amount is xxxxx element) which can be understood by a user according to the data query model, and the data is returned to the interface input by the user, so that the use cost of the user can be greatly reduced.
The following specific examples are presented to describe the data query method of the snowflake model in this embodiment.
If the order information to be queried is the sales amount of the Shanghai 3 months and 3 days, the order information to be queried is queried through a star model, the time dimension table is processed from the time dimension table comprising the dimensions of the year dimension, the quarter dimension, the month dimension, the day dimension and the like, the day dimension information of the order number is screened out from the time dimension table, and the order number of 3 months and 3 days is screened out from the day dimension. And then processing the regional dimension table from the regional dimension table comprising the dimensions of the national dimension, the province dimension, the city dimension and the like, screening city dimension information of order numbers from the regional dimension table, screening order numbers of Shanghai from the city dimension, determining order numbers of Shanghai 3 on the basis of the order numbers of 3 months and 3 days and the order numbers of Shanghai, determining sales amount of Shanghai 3 months and 3 days in the fact table on the basis of the order numbers of Shanghai 3 months and 3 days. However, in the case of the above example, by using the data query method of the snowflake model in this embodiment, the corresponding second-level dimension (specifically, the day dimension table and the city dimension table) can be determined, so that the processing of the time dimension table can be omitted, and the order number of 3 months and 3 days can be directly screened out from the day dimension table; meanwhile, the processing of the regional dimension table is avoided, and the order numbers of Shanghai are directly screened out from the city dimension table. By using the order data query method of the snowflake model in the implementation, the needed information to be queried can be quickly queried.
And, when the second level dimension data needs to be changed, for example, order number: 001, time: 3 months and 3 days, place: shanghai, the time of the 001 order needs to be modified to: 3 months and 2 days. In the star model, the data of the order number of 001 needs to be rewritten in the time table, and at this time, the data amount of the whole table is large, so the time for modifying the data is long; however, in the snowflake model, the data only need to be rewritten in a daily dimension table, in the monthly dimension table, the month dimension information of the 001 order is still 3 months, when a single second-level dimension table is changed, the data volume is small, the modification time is short, and the order on the sea in 3 months is queried during the modification period, and the influence of data modification is avoided.
Example 2
Referring to fig. 5, the present embodiment provides a data query system for a snowflake model.
The snowflake model comprises a fact table, a first-level dimension table and a second-level dimension table corresponding to the first-level dimension table.
The fact table comprises data identification numbers of stored data and fact data corresponding to the data identification numbers, each first-level dimension table comprises first-level dimension information of each stored data, and the first-level dimension information comprises second-level dimensions; each second-level dimension table includes second-level dimension information corresponding to each data identification number in a second-level dimension.
The data query system comprises: the system comprises a data information acquisition module 201 to be queried, a data identification number query module 202 and a fact data determination module 203.
The data information to be queried obtaining module 201 is configured to obtain data information to be queried, where the data information to be queried includes first-level dimension information to be queried and second-level dimension information to be queried of storage data.
The data identifier query module 202 is configured to determine a corresponding target first-level dimension table according to the first-level dimension information to be queried, and determine a target second-level dimension table corresponding to the target first-level dimension table according to the second-level dimension information to be queried.
And determining a target data identification number corresponding to the data information to be queried from the target second-level dimension table according to the second-level dimension information to be queried.
The fact data determining module 203 is configured to determine the fact data of the target data identifier according to the target data identifier and the fact table.
In one embodiment, the first level dimension tables include at least two of a time dimension table, a region dimension table, a product dimension table, a department dimension table, an age dimension table, and a gender dimension table.
In one embodiment, if the first level dimension table includes a time dimension table, the second level dimension table corresponding to the time dimension table includes at least one of a year dimension table, a quarter dimension table, a month dimension table, and a day dimension table.
In one embodiment, if the first level dimension table includes a region dimension table, the second level dimension table corresponding to the region dimension table includes at least one of a country dimension table, a province dimension table, a city dimension table, and a county dimension table.
In one embodiment, if the first-level dimension table includes a product dimension table, the second-level dimension table corresponding to the product dimension table includes at least one of a product name dimension table, a product selling price dimension table, a product description dimension table, and a product quality dimension table.
In one embodiment, if the first level dimension table includes a department dimension table, the second level dimension table corresponding to the department dimension table includes a head office dimension table, a branch office dimension table, a department dimension table, and a proxy department dimension table.
In one embodiment, the fact data includes a first type of fact data and/or a second type of fact data.
The fact data determining module 203 is specifically configured to: and acquiring the first-class fact data and/or the second-class fact data of the target data identification number, and counting the first-class fact data and/or the second-class fact data according to the second-class dimension information.
In one embodiment, the fact data determining module 203 is specifically configured to: and inputting the information to be queried into a data query model, and inputting the output result of the data query model into a database where the snowflake model is positioned.
The data query model is used for converting the input information to be queried into a query language conforming to the structural specification of the snowflake model.
The data query system of the snowflake model provided in this embodiment 2 is used for executing the data query method of the snowflake model in embodiment 1, and the effect reached by the data query system in this embodiment is as follows: when inquiring the fact data of the next different dimension, only the corresponding second-level dimension table is needed to be selected, the fact data of any dimension combination can be quickly obtained, and the data inquiring speed is high. And when one of the second-level dimensions is modified, no modification is required to the other second-level dimension data of the stored data.
Example 3
Fig. 6 is a schematic structural diagram of an electronic device according to the present embodiment. The electronic device comprises a memory, a processor and a computer program stored on the memory and used for running on the processor, wherein the processor realizes the data query method of the snowflake model of the embodiment 1 when executing the program. The electronic device 30 shown in fig. 6 is merely an example, and should not be construed as limiting the functionality and scope of use of embodiments of the present invention.
As shown in fig. 6, the electronic device 30 may be embodied in the form of a general purpose computing device, which may be a server device, for example. Components of electronic device 30 may include, but are not limited to: the at least one processor 31, the at least one memory 32, a bus 33 connecting the different system components, including the memory 32 and the processor 31.
The bus 33 includes a data bus, an address bus, and a control bus.
Memory 32 may include volatile memory such as Random Access Memory (RAM) 321 and/or cache memory 322, and may further include Read Only Memory (ROM) 323.
Memory 32 may also include a program/utility 325 having a set (at least one) of program modules 324, such program modules 324 including, but not limited to: an operating system, one or more application programs, other program modules, and program data, each or some combination of which may include an implementation of a network environment.
The processor 31 executes various functional applications and data processing such as the data query method of the snowflake model of embodiment 1 of the present invention by running a computer program stored in the memory 32.
The electronic device 30 may also communicate with one or more external devices 34 (e.g., keyboard, pointing device, etc.). Such communication may be through an input/output (I/O) interface 35. Also, model-generating device 30 may also communicate with one or more networks, such as a Local Area Network (LAN), a Wide Area Network (WAN), and/or a public network, such as the internet, via network adapter 36. As shown, network adapter 36 communicates with the other modules of model-generating device 30 via bus 33. It should be appreciated that although not shown in the figures, other hardware and/or software modules may be used in connection with the model-generating device 30, including, but not limited to: microcode, device drivers, redundant processors, external disk drive arrays, RAID (disk array) systems, tape drives, data backup storage systems, and the like.
It should be noted that although several units/modules or sub-units/modules of an electronic device are mentioned in the above detailed description, such a division is merely exemplary and not mandatory. Indeed, the features and functionality of two or more units/modules described above may be embodied in one unit/module in accordance with embodiments of the present invention. Conversely, the features and functions of one unit/module described above may be further divided into ones that are embodied by a plurality of units/modules.
Example 4
The present embodiment provides a computer-readable storage medium having stored thereon a computer program which, when executed by a processor, implements the data query method of the snowflake model of embodiment 1.
More specifically, among others, readable storage media may be employed including, but not limited to: portable disk, hard disk, random access memory, read only memory, erasable programmable read only memory, optical storage device, magnetic storage device, or any suitable combination of the foregoing.
In a possible embodiment, the invention may also be implemented in the form of a program product comprising program code for causing a terminal device to carry out a data query method implementing the snowflake model of example 1, when the program product is run on the terminal device.
Wherein the program code for carrying out the invention may be written in any combination of one or more programming languages, the program code may execute entirely on the user device, partly on the user device, as a stand-alone software package, partly on the user device, partly on a remote device or entirely on the remote device. While specific embodiments of the invention have been described above, it will be appreciated by those skilled in the art that this is by way of example only, and the scope of the invention is defined by the appended claims. Various changes and modifications to these embodiments may be made by those skilled in the art without departing from the principles and spirit of the invention, but such changes and modifications fall within the scope of the invention.
Claims (10)
1. A data query method of a snowflake model is characterized in that the snowflake model comprises a fact table, a first-stage dimension table and a second-stage dimension table corresponding to the first-stage dimension table;
the fact table comprises data identification numbers of stored data and fact data corresponding to the data identification numbers, each first-level dimension table comprises first-level dimension information of each stored data, and the first-level dimension information comprises second-level dimensions; each second-level dimension table comprises second-level dimension information corresponding to each data identification number in the second-level dimension;
the data query method comprises the following steps:
acquiring data information to be queried, wherein the data information to be queried comprises first-level dimension information to be queried and second-level dimension information to be queried of storage data;
determining a corresponding target first-stage dimension table according to the first-stage dimension information to be queried, and determining a target second-stage dimension table corresponding to the target first-stage dimension table according to the second-stage dimension information to be queried;
determining a target data identification number corresponding to the data information to be queried from the target second-level dimension table according to the second-level dimension information to be queried;
and determining the fact data of the target data identification number according to the target data identification number and the fact table.
2. The data query method of claim 1, wherein if the first-level dimension table comprises a time dimension table, the second-level dimension table corresponding to the time dimension table comprises at least one of a year dimension table, a quarter dimension table, a month dimension table, and a day dimension table;
and/or, if the first-level dimension table comprises a region dimension table, the second-level dimension table corresponding to the region dimension table comprises at least one of a country dimension table, a province dimension table, a city dimension table and a county dimension table;
and/or, if the first-level dimension table comprises a product dimension table, the second-level dimension table corresponding to the product dimension table comprises at least one of a product name dimension table, a product selling price dimension table, a product description dimension table and a product quality dimension table;
and/or, if the first-level dimension table comprises a department dimension table, the second-level dimension table corresponding to the department dimension table comprises a main company dimension table, a branch company dimension table, a department dimension table and an agent department dimension table.
3. The data query method of claim 1, wherein the fact data comprises a first type of fact data and/or a second type of fact data;
the step of determining the fact data of the target data identification number according to the target data identification number and the fact table specifically comprises the following steps:
and acquiring first-class fact data and/or second-class fact data of the target data identification number, and counting the first-class fact data and/or the second-class fact data according to second-class dimension information.
4. The data query method as claimed in claim 1, wherein the step of obtaining the data information to be queried specifically includes:
inputting the information to be queried into a data query model, and inputting the output result of the data query model into a database where a snowflake model is positioned;
the data query model is used for converting the input information to be queried into a query language conforming to the structural specification of the snowflake model.
5. The data query system of the snowflake model is characterized in that the snowflake model comprises a fact table, a first-stage dimension table and a second-stage dimension table corresponding to the first-stage dimension table;
the fact table comprises data identification numbers of stored data and fact data corresponding to the data identification numbers, each first-level dimension table comprises first-level dimension information of each stored data, and the first-level dimension information comprises second-level dimensions; each second-level dimension table comprises second-level dimension information corresponding to each data identification number in the second-level dimension;
the data query system comprises: the system comprises a data information acquisition module to be queried, a data identification number query module and a fact data determination module;
the data information to be queried is used for acquiring data information to be queried, and the data information to be queried comprises first-level dimension information to be queried and second-level dimension information to be queried of storage data;
the data identification number inquiring module is used for determining a corresponding target first-stage dimension table according to the first-stage dimension information to be inquired, and determining a target second-stage dimension table corresponding to the target first-stage dimension table according to the second-stage dimension information to be inquired;
determining a target data identification number corresponding to the data information to be queried from the target second-level dimension table according to the second-level dimension information to be queried;
the fact data determining module is used for determining the fact data of the target data identification number according to the target data identification number and the fact table.
6. The data query system of claim 5, wherein if the first level dimension table comprises a time dimension table, the second level dimension table corresponding to the time dimension table comprises at least one of a year dimension table, a quarter dimension table, a month dimension table, and a day dimension table;
and/or, if the first-level dimension table comprises a region dimension table, the second-level dimension table corresponding to the region dimension table comprises at least one of a country dimension table, a province dimension table, a city dimension table and a county dimension table;
and/or, if the first-level dimension table comprises a product dimension table, the second-level dimension table corresponding to the product dimension table comprises at least one of a product name dimension table, a product selling price dimension table, a product description dimension table and a product quality dimension table;
and/or, if the first-level dimension table comprises a department dimension table, the second-level dimension table corresponding to the department dimension table comprises a main company dimension table, a branch company dimension table, a department dimension table and an agent department dimension table.
7. The data query system of claim 5, wherein the fact data comprises a first type of fact data and/or a second type of fact data;
the fact data determining module is specifically configured to:
and acquiring first-class fact data and/or second-class fact data of the target data identification number, and counting the first-class fact data and/or the second-class fact data according to second-class dimension information.
8. The data query system of claim 5, wherein the fact data determination module is specifically configured to:
inputting the information to be queried into a data query model, and inputting the output result of the data query model into a database where a snowflake model is positioned;
the data query model is used for converting the input information to be queried into a query language conforming to the structural specification of the snowflake model.
9. An electronic device comprising a memory, a processor and a computer program stored on the memory for execution on the processor, wherein the processor implements the method of querying data for a snowflake model of any one of claims 1 to 4 when executing the computer program.
10. A computer readable storage medium having stored thereon a computer program, wherein the computer program when executed by a processor implements the data query method of a snowflake model of any one of claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310513467.5A CN116823381A (en) | 2023-05-08 | 2023-05-08 | Data query method, system, equipment and medium of snowflake model |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310513467.5A CN116823381A (en) | 2023-05-08 | 2023-05-08 | Data query method, system, equipment and medium of snowflake model |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116823381A true CN116823381A (en) | 2023-09-29 |
Family
ID=88115723
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310513467.5A Pending CN116823381A (en) | 2023-05-08 | 2023-05-08 | Data query method, system, equipment and medium of snowflake model |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116823381A (en) |
-
2023
- 2023-05-08 CN CN202310513467.5A patent/CN116823381A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10715598B1 (en) | Implementation of a web-scale data fabric | |
| US11748371B2 (en) | Systems and methods for searching for and translating real estate descriptions from diverse sources utilizing an operator-based product definition | |
| US7711680B2 (en) | Common common object | |
| CN110795509A (en) | Method and device for constructing index blood relationship graph of data warehouse and electronic equipment | |
| US20120173355A1 (en) | System and method to price and exchange data between data producers and data consumers through formatting data objects with necessary and sufficient item definition information | |
| Coad et al. | Firm growth and productivity growth: evidence from a panel VAR | |
| US9020988B2 (en) | Database aggregation of purchase data | |
| US20170075896A1 (en) | System and method for analyzing popularity of one or more user defined topics among the big data | |
| CN110807016A (en) | Data warehouse construction method and device applied to financial business and electronic equipment | |
| Petermann et al. | FoodBroker-generating synthetic datasets for graph-based business analytics | |
| CN110543996A (en) | job salary assessment method, apparatus, server and storage medium | |
| US10719561B2 (en) | System and method for analyzing popularity of one or more user defined topics among the big data | |
| CN111444099A (en) | Data inconsistency analysis method, system, electronic device and storage medium | |
| CN117033431A (en) | Work order processing method, device, electronic equipment and medium | |
| Petrenko et al. | Development of BI-Platforms for cybersecurity predictive analytics | |
| CN116823381A (en) | Data query method, system, equipment and medium of snowflake model | |
| CN114817572A (en) | Knowledge classification method, system, device and medium based on knowledge graph | |
| CN114201157A (en) | Method and system for customizing target service module by low code | |
| Futia et al. | ContrattiPubblici. org, a semantic knowledge graph on public procurement information | |
| Sá et al. | Portuguese Local E-government: A Study on the Most Adopted Databases in 2019 | |
| US20150095079A1 (en) | Determining one or more skills required to address the problem symptoms of a problem report files | |
| CN111199287A (en) | Feature engineering real-time recommendation method and device and electronic equipment | |
| US20230185777A1 (en) | No-code rules engine to build near real-time data projections from disparate data sources | |
| CN112685495B (en) | Method, system, equipment and medium for displaying train ticket information | |
| CN113254622B (en) | Knowledge point query method, knowledge point query device and knowledge point query server |
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 |