网站建设 书,代理网页 国外,wordpress 文件调用规则,做网站游戏推广赚钱吗文章目录 定义案例#xff1a;零售销售数据仓库实践创建维度表创建事实表插入维度表数据插入事实表数据增改查 定义
维度建模是一种用于数据仓库设计的技术#xff0c;它的目标是使数据库结构更加直观#xff0c;易于理解和使用#xff0c;特别是对于那些进行数据查询和报… 文章目录 定义案例零售销售数据仓库实践创建维度表创建事实表插入维度表数据插入事实表数据增改查 定义
维度建模是一种用于数据仓库设计的技术它的目标是使数据库结构更加直观易于理解和使用特别是对于那些进行数据查询和报告的非技术用户。它主要包括两个核心概念事实表和维度表。
事实表这是数据仓库的核心用于存储度量或业务过程的量化数据。例如一个零售业务的事实表可能包含每笔销售的总额、数量和时间等信息。维度表这些表包含描述性信息用于“维度化”事实表中的量化数据。它们帮助用户理解事实表中的数据。继续上述例子维度表可能包括客户信息、产品信息、商店信息等每个维度表都与事实表通过外键关联。
案例零售销售数据仓库
假设我们要为一家零售商建立数据仓库。在这个例子中我们可能会有
事实表销售事实
销售ID产品ID (外键连接到产品维度表)顾客ID (外键连接到顾客维度表)商店ID (外键连接到商店维度表)销售日期 (外键连接到时间维度表)销售金额销售数量
维度表产品维度
产品ID产品名称产品类别产品价格
维度表顾客维度
顾客ID顾客姓名顾客地址顾客类别
维度表商店维度
商店ID商店名称商店位置商店类型
维度表时间维度
日期周月季度年
在这个例子中事实表提供了可度量的业务过程例如每笔销售的金额和数量而维度表则提供了理解这些度量所需的上下文例如销售发生在哪个商店由哪个顾客进行涉及哪个产品。通过这种方式维度建模帮助用户以直观的方式理解和分析复杂的业务数据。
实践
创建维度表
产品维度表
CREATE TABLE test.DimProduct (ProductID INT PRIMARY KEY,ProductName VARCHAR(255),Category VARCHAR(255),Price DECIMAL(10, 2)
);顾客维度表
CREATE TABLE test.DimCustomer (CustomerID INT PRIMARY KEY,CustomerName VARCHAR(255),Address VARCHAR(255),CustomerType VARCHAR(255)
);
商店维度表
CREATE TABLE test.DimStore (StoreID INT PRIMARY KEY,StoreName VARCHAR(255),Location VARCHAR(255),StoreType VARCHAR(255)
);时间维度表
CREATE TABLE test.DimTime (DateKey DATE PRIMARY KEY,Day INT,Month INT,Quarter INT,Year INT
);创建事实表
销售事实表
CREATE TABLE test.FactSales (SaleID INT PRIMARY KEY,ProductID INT,CustomerID INT,StoreID INT,DateKey DATE,Amount DECIMAL(10, 2),Quantity INT,FOREIGN KEY (ProductID) REFERENCES DimProduct(ProductID),FOREIGN KEY (CustomerID) REFERENCES DimCustomer(CustomerID),FOREIGN KEY (StoreID) REFERENCES DimStore(StoreID),FOREIGN KEY (DateKey) REFERENCES DimTime(DateKey)
);
插入维度表数据
向产品维度表插入数据
INSERT INTO test.DimProduct (ProductID, ProductName, Category, Price) VALUES
(1, 苹果手机, 电子产品, 5000.00),
(2, 三星电视, 电子产品, 3000.00),
(3, 联想笔记本, 电子产品, 4500.00);select * from test.DimProduct;如下 向顾客维度表插入数据
INSERT INTO test.DimCustomer (CustomerID, CustomerName, Address, CustomerType) VALUES
(1, 张三, 北京市, 个人),
(2, 李四, 上海市, 企业),
(3, 王五, 广州市, 个人);select * from test.DimCustomer;如下 向商店维度表插入数据
INSERT INTO test.DimStore (StoreID, StoreName, Location, StoreType) VALUES
(1, 京东商城, 在线, 电商),
(2, 苏宁易购, 在线, 电商),
(3, 国美电器, 线下, 实体店);select * from test.DimStore;如下 向时间维度表插入数据
INSERT INTO test.DimTime (DateKey, Day, Month, Quarter, Year) VALUES
(2023-01-01, 1, 1, 1, 2023),
(2023-02-01, 1, 2, 1, 2023),
(2023-03-01, 1, 3, 1, 2023);select * from test.DimTime;如下
插入事实表数据
向销售事实表插入数据
INSERT INTO test.FactSales (SaleID, ProductID, CustomerID, StoreID, DateKey, Amount, Quantity) VALUES
(1, 1, 1, 1, 2023-01-01, 5000.00, 1),
(2, 2, 2, 2, 2023-02-01, 6000.00, 2),
(3, 3, 3, 3, 2023-03-01, 4500.00, 1);select * from test.FactSales ;如下
增改查
向产品维度表添加新产品
INSERT INTO test.DimProduct (ProductID, ProductName, Category, Price) VALUES
(4, 惠普打印机, 电子产品, 800.00);从顾客维度表删除一位顾客 假设顾客ID为3的顾客不再是我们的客户我们可以从顾客维度表中删除这条记录。
更新商店维度表中的商店信息 如果商店ID为3的商店更改了其名称我们可以更新这条信息。
UPDATE test.DimStore SET StoreName 新国美电器 WHERE StoreID 3;查询2023年第一季度的总销售额 这个查询将涉及联接事实表和时间维度表以计算特定时间段内的总销售额。
SELECTSUM(test.FactSales.Amount) AS TotalSales
FROMtest.FactSales
JOINtest.DimTime ON test.FactSales.DateKey test.DimTime.DateKey
WHEREtest.DimTime.Year 2023 AND test.DimTime.Quarter 1;
如下
FROM FactSales: 这是查询的主表我们从这个事实表开始。JOIN DimTime ON FactSales.DateKey DimTime.DateKey: 这里我们通过JOIN操作将FactSales表和DimTime表联接起来。联接的条件是FactSales.DateKeyDimTime.DateKey意味着我们只关心那些在两个表中都有匹配日期的行。WHERE DimTime.Year 2023 AND DimTime.Quarter 1: 这个条件进一步过滤结果只包括2023年第一季度的数据。SELECT SUM(FactSales.Amount) AS TotalSales: 最后我们对联接后的数据进行汇总计算总销售额。
查询特定顾客的购买历史 这个查询显示了顾客ID为1的顾客在不同时间购买的产品。
SELECTDimCustomer.CustomerName,DimProduct.ProductName,FactSales.DateKey,FactSales.Amount
FROMtest.FactSales
JOINtest.DimCustomer ON FactSales.CustomerID DimCustomer.CustomerID
JOINtest.DimProduct ON FactSales.ProductID DimProduct.ProductID
WHEREDimCustomer.CustomerID 1;
如下 在这个查询中我们首先联接FactSales和DimCustomer然后再将结果与DimProduct联接。最终的结果集包含了顾客姓名、产品名称、购买日期和购买金额这些信息来自三个不同的表。
