0001 WITH inv AS
0002 (SELECT
0003 w_warehouse_name,
0004 w_warehouse_sk,
0005 i_item_sk,
0006 d_moy,
0007 stdev,
0008 mean,
0009 CASE mean
0010 WHEN 0
0011 THEN NULL
0012 ELSE stdev / mean END cov
0013 FROM (SELECT
0014 w_warehouse_name,
0015 w_warehouse_sk,
0016 i_item_sk,
0017 d_moy,
0018 stddev_samp(inv_quantity_on_hand) stdev,
0019 avg(inv_quantity_on_hand) mean
0020 FROM inventory, item, warehouse, date_dim
0021 WHERE inv_item_sk = i_item_sk
0022 AND inv_warehouse_sk = w_warehouse_sk
0023 AND inv_date_sk = d_date_sk
0024 AND d_year = 2001
0025 GROUP BY w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy) foo
0026 WHERE CASE mean
0027 WHEN 0
0028 THEN 0
0029 ELSE stdev / mean END > 1)
0030 SELECT
0031 inv1.w_warehouse_sk,
0032 inv1.i_item_sk,
0033 inv1.d_moy,
0034 inv1.mean,
0035 inv1.cov,
0036 inv2.w_warehouse_sk,
0037 inv2.i_item_sk,
0038 inv2.d_moy,
0039 inv2.mean,
0040 inv2.cov
0041 FROM inv inv1, inv inv2
0042 WHERE inv1.i_item_sk = inv2.i_item_sk
0043 AND inv1.w_warehouse_sk = inv2.w_warehouse_sk
0044 AND inv1.d_moy = 1
0045 AND inv2.d_moy = 1 + 1
0046 AND inv1.cov > 1.5
0047 ORDER BY inv1.w_warehouse_sk, inv1.i_item_sk, inv1.d_moy, inv1.mean, inv1.cov
0048 , inv2.d_moy, inv2.mean, inv2.cov
