db2 如何在不终止系统的情况下创建工作透视表

假设您有一个Customer表，这是一个只有4列的简单customer表：

客户代码数字（7，0）
客户名称字符（50）
客户增值税号码char（11）
客户位置字符（35）

请记住，customers表包含300万行，因为它包含了过去40年的所有客户，但活动客户只有980000。
假设我们有一个名为Sales的表，其结构如下：

saleID整数
客户代码数字（7，0）
座席ID数字（6，0）
产品ID字符（2）
日期开始销售日期
销售结束日期

此表中大约有350万行（这里也有40年前的东西），但目前各种产品的供应量总计为100万。该公司只销售4种产品。每个客户可以购买多达4种产品，4个不同的合同，甚至从4个不同的代理商。最（90%）只买一只，剩下的从两只到4只（那些做出完整分类的只有4只猫）。
我被要求建立一个数据透视表，显示每个客户的名称和位置，他购买的所有产品，从哪个代理商。
此数据透视表的建议布局为：

客户代码
客户名称
客户位置
产品ID1
座席ID 1
销售ID1
开始销售日期1
销售结束日期1
产品ID2
座席ID2
销售ID2
开始销售日期2
销售结束日期2
产品ID3
座席ID3
销售ID3
开始销售日期3
销售结束日期3
产品ID4
座席ID4
销售ID4
开始销售日期4
终止销售日期4

我建的枢纽站有风景。
首先，我创建了4个视图，每个视图对应Sales表中的一个产品ID，这对于其他统计和报告目的也很有用
视1为

客户代码1
产品ID1
座席ID 1
销售ID1
开始销售日期1
销售结束日期1

视图2为

客户代码2
产品ID2
座席ID2
销售ID2
开始销售日期2
销售结束日期2

依此类推，直到View4
然后，我加入了4个视图与客户表，并创建了我需要的透视视图。
现在，从数据透视视图中选择 * 可以完美地工作。
同时从customerLocation =“约克市”的数据透视视图中选择 *。
任何其他请求，例如：我们选择并统计居住在洛杉矶的客户，这些客户从同一个代理商或不同的销售代理商购买了产品，从字面上看，这使得机器坐下来，我看到内存占用增加（可能是由于构造了一些临时表或视图），并且查询的执行经常崩溃。
但是，如果我在表而不是视图上创建相同的透视表，则各种选择的次数会崩溃，即使繁重（总是有大约一百万条记录需要扫描以验证各种条件的存在），它们也会变得可以接受。
我肯定是弄错了什么，或者肯定有更好的方法来达到这个结果：具有建立在在线数据上的枢纽而不是来自每晚提取的数据的枢纽。
我很乐意阅读您的意见和建议。

我不太清楚您的数据布局和您需要什么，但我要说的是，在Db2 for IBM i上透视数据的常见问题是没有内置的方法来动态透视数据。
鉴于你只有4个产品，上述限制并不真正适用.
您的问题似乎是，通过在同一个表上创建4个视图，您重复地处理记录。相反，尝试只接触一次数据。

create view PivotSales as
  select 
     customerCode, 
     -- product 1
     max(case productID when '01' then productID end) as productID1,
     max(case productID when '01' then agentID end) as agentID1,
     max(case productID when '01' then saleID end) as saleID1,         
     max(case productID when '01' then dateBeginSale end) as dateBeginSale1,
     max(case productID when '01' then dateEndSale end) as dateEndSale1,
     -- product 2
     max(case productID when '02' then productID end) as productID2,
     max(case productID when '02' then agentID end) as agentID2,
     max(case productID when '02' then saleID end) as saleID2,         
     max(case productID when '02' then dateBeginSale end) as dateBeginSale2,
     max(case productID when '02' then dateEndSale end) as dateEndSale2,
     -- repeat for product 3 and 4
  from Sales
  group by customerCode;

现在您可以拥有CustomerSales视图：

create view CustomerSales as
  select *
  from Customers join SalesPivot using (customerCode);

运行查询，使用Visual Explain查看系统建议需要哪些索引。至少应具有以下索引：

客户（客户代码）
客户（位置、客户代码）
销售（客户代码）

我猜想在Sales和Customer的不同列上使用一些编码向量索引（Encoded Vector Index，EVI）会很有帮助。特别是你提到了"计数"。EVI跟踪符号的计数。所以计数是"免费的"。举个例子：

create encoded vector index customerLocEvi
 on Customers (location);

-- this doesn't have to read any rows in customer
select count(*) 
from customer 
where location = 'LOS ANGELES';

我肯定是弄错了什么，或者肯定有更好的方法来达到这个结果：具有建立在在线数据上的枢纽而不是来自每晚提取的数据的枢纽。
不要太肯定。最好支持商业智能类型查询的DB结构通常与典型的事务数据结构不匹配。周期性的"提取、转换、加载（ETL）"是非常典型的。
对于您的特定用例，您可以将CustomerSales转换为一个Materalized Query Table（MQT），为它构建一些支持索引，然后直接在它上面运行查询。
或者，如果您也需要，因为Db2 for IBM i不支持SYSTEM MAINTAINED MQT，所以Sales上的触发器可以自动将数据传播到CustomerSales，而不是每晚重建数据。

db2 如何在不终止系统的情况下创建工作透视表

1条答案

相关问题

热门标签

最新问答