如何拆分商品价格表并构建日期层级实现SSRS按多时间维度统计

我现有一张商品价格数据表，包含以下字段：

Country_id 
Country_name 
Locality_id 
Locality_Name 
Market_id 
Market_name 
Commodity_id 
Commodity_name 
Currency_id
Currency_name 
Market_type_id 
Market_type   
Month  
Year 
Price 

我希望将原表中的Month和Year字段移除，拆分出两张表，将单独的日期表作为关联参考表：

拆分后表结构要求 #

Table_1（商品价格主表） #

Country_id 
Country_name 
Locality_id 
Locality_Name 
Market_id 
Market_name 
Commodity_id 
Commodity_name 
Currency_id
Currency_name 
Market_type_id 
Market_type    
Price 

Table_2（日期参考维度表） #

Month 
Year 

同时我需要在日期参考表中构建Month -> Quarter -> Year的层级关系，最终可以在SSRS报表中实现按月份、季度、年份筛选统计价格数据。
原表示意图：

列名（逗号分隔）：Country_id ,Country_name, Locality_id, Locality_Name, Market_id,Market_name, Commodity_id, Commodity_name, Currency_id, Currency_name, Market_type_id, Market_type, Month, Year, Price
样例数据（逗号分隔）：

1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,1,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,2,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,3,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,4,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,5,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,6,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,7,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,8,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,9,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,10,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,11,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,12,2014,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,1,2015,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,2,2015,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,3,2015,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,6,2015,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,7,2015,50
1,Afghanistan,272,Badakhshan,266,Fayzabad,55,Bread _ Retail,0,AFN,15,Retail,8,2015,50

步骤1：表结构拆分与数据迁移 #

以SQL Server为例，可通过以下SQL完成操作：

• 首先创建日期维度表，新增主键和季度字段，满足层级关联需求：

-- 创建日期维度表Table_2
CREATE TABLE Table_2 (
    DateID INT IDENTITY(1,1) PRIMARY KEY, -- 新增主键，用于和主表关联
    [Year] INT,
    [Month] INT,
    Quarter INT -- 新增季度字段，用于构建层级
)

-- 从原表导入去重的年月数据，自动计算季度
INSERT INTO Table_2 ([Year], [Month], Quarter)
SELECT DISTINCT [Year], [Month], CEILING([Month]/3.0) AS Quarter
FROM 原商品价格表

• 然后创建商品价格主表，导入数据并关联日期维度表：

-- 创建商品价格主表Table_1
CREATE TABLE Table_1 (
    Country_id INT,
    Country_name NVARCHAR(100),
    Locality_id INT,
    Locality_Name NVARCHAR(100),
    Market_id INT,
    Market_name NVARCHAR(100),
    Commodity_id INT,
    Commodity_name NVARCHAR(100),
    Currency_id INT,
    Currency_name NVARCHAR(10),
    Market_type_id INT,
    Market_type NVARCHAR(50),
    Price DECIMAL(18,2),
    DateID INT FOREIGN KEY REFERENCES Table_2(DateID) -- 关联日期维度表
)

-- 导入主表数据，匹配对应DateID
INSERT INTO Table_1 (Country_id, Country_name, Locality_id, Locality_Name, Market_id, Market_name, Commodity_id, Commodity_name, Currency_id, Currency_name, Market_type_id, Market_type, Price, DateID)
SELECT 
    t.Country_id, t.Country_name, t.Locality_id, t.Locality_Name, t.Market_id, t.Market_name, t.Commodity_id, t.Commodity_name, t.Currency_id, t.Currency_name, t.Market_type_id, t.Market_type, t.Price, d.DateID
FROM 原商品价格表 t
INNER JOIN Table_2 d ON t.[Year] = d.[Year] AND t.[Month] = d.[Month]

步骤2：SSRS报表配置 #

• 数据源配置：在SSRS项目中添加Table_1和Table_2两张表，配置关联关系为Table_1.DateID = Table_2.DateID
• 级联参数配置：依次添加年份、季度、月份三个参数，参数值从Table_2对应字段去重获取，级联规则如下：
  • 选择年份后，季度参数仅展示该年份下的所有季度
  • 选择季度后，月份参数仅展示该季度下的所有月份
• 数据集筛选配置：报表查询语句添加对应参数筛选：

SELECT t1.*, t2.Year, t2.Quarter, t2.Month 
FROM Table_1 t1
INNER JOIN Table_2 t2 ON t1.DateID = t2.DateID
WHERE t2.Year = @Year
  AND t2.Quarter = @Quarter
  AND t2.Month = @Month

• 分层统计配置：如果需要按年/季/月做聚合统计，在SSRS表格控件中按Year、Quarter、Month依次添加行组，即可实现不同层级的价格汇总展示。

内容的提问来源于stack exchange，提问作者sunny nuri