TA的每日心情 | 开心 2015-10-19 13:26 |
---|
签到天数: 2 天 连续签到: 2 天 [LV.1]测试小兵
|
我这次测试的项目是数据仓库的解决方案,验证的数据处理逻辑,基本上都是写在存储过程中的。
如何设计测试用例来验证其处理逻辑是否正确?
示例说明如下:求出过去3个月各有效商店销售的sku数目(先按商店求出每个商店销售的不同sku数目,再求和);求出过去3个月有销售sku的商店数目
源表:
init_store_scope: | 存储有效的商店信息,包括store_id(为总店,分店及一般商店),banner_id(banner_id不为空,则store_id是分店) | init_banner_outlet:
| 存储banner_id与store_id映射关系(init_store_scope中banner_id不为空时) | fdt_indirect_shipment: | 各store销售的商品信息,包括:store_id,product_id,shipment_su(+表示售出,-表示退货) | mdt_product: | 存储产品的层次信息,product_id上一层是product_l6_id(即sku) |
目标表:
hlfdt_p3m_ld:store_number(不同的商店数据),sku_number(先求出各商店不同的sku数目,再求和)
存储过程逻辑基本代码如下:
1.提取各商店过去3个月销售的所有sku
-------------------------------
INSERT INTO init_p3m_ld
(yearmonth
,country_id
,organization_id
,distributor_id
,store_id
,sku_id)
SELECT DISTINCT p_yearmonth AS yearmonth
,a.country_id
,a.organization_id
,a.distributor_id
,a.store_id
,b.product_l6_id AS sku_id
FROM fdt_indirect_shipment a
,mdt_product b
WHERE a.product_id=b.product_id(+)
AND a.shipment_su>0
AND(a.shipment_yearmonth=v_last1_yearmonth
OR a.shipment_yearmonth=v_last2_yearmonth
OR a.shipment_yearmonth=v_last3_yearmonth)
2.如果分店没有销售sku,则把总店销售的所有sku拷贝过来(只对country_id='156',其他国家不用)
-------------------------------------------------------------
MERGE INTO init_p3m_ld a
USING (SELECT DISTINCT a.yearmonth
,a.country_id
,a.organization_id
,a.distributor_id
,b.outlet_id store_id
,a.sku_id
FROM init_p3m_ld a
,init_banner_outlet b
WHERE a.store_id=b.banner_id
AND a.distributor_id=b.distributor_id
AND a.country_id='156'
) b
ON (a.store_id=b.store_id)
WHEN NOT MATCHED THEN
INSERT (a.yearmonth
,a.country_id
,a.organization_id
,a.distributor_id
,a.store_id
,a.sku_id)
VALUES (b.yearmonth
,b.country_id
,b.organization_id
,b.distributor_id
,b.store_id
,b.sku_id)
3.按商店分组,求每个商店不同的sku数目
--------------------------------------
INSERT INTO temp_p3m_ld
(yearmonth
,country_id
,distributor_id
,store_id
,sku_number)
SELECT a.yearmonth
,a.country_id
,a.distributor_id
,a.store_id
,COUNT(DISTINCT a.sku_id) AS sku_number
FROM init_p3m_ld a
GROUP BY a.yearmonth
,a.country_id
,a.distributor_id
,a.store_id
4.求SKU数目,求商店数目
------------------------------------
INSERT INTO hlfdt_p3m_ld
(yearmonth
,country_id
,distributor_id
,customer_id
,store_number
,sku_number)
SELECT a.yearmonth
,a.country_id
,a.distributor_id
,b.customer_id
,COUNT(DISTINCT a.store_id) AS store_number
,SUM(a.sku_number) AS sku_number
FROM temp_p3m_ld a
,init_store_scope b
WHERE a.distributor_id=b.distributor_id
AND a.store_id=b.store_id
GROUP BY a.yearmonth
,a.country_id
,a.distributor_id
,b.customer_id |
|