行轉列的一種優化思路

壹頁書發表於2014-05-14
優化
在論壇看到一個帖子，需求是行轉列。
但是表中的資料是千萬級別的，執行很慢。

樓主的SQL
  1. set linesize 300;
  2. set pagesize 300;
  3. WITH t AS
  4. (
  5.  SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 2 num,10.5 price FROM dual
  6.  UNION ALL
  7.  SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 12 num,8 price FROM dual
  8.  UNION ALL
  9.  SELECT 'shop002' AS shopcode, 'store002' storecode,105 b_ID, 8 num,12.5 price FROM dual
  10.  UNION ALL
  11.  SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 20 num,19 price FROM dual
  12.  UNION ALL
  13.  SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 22 num,7 price FROM dual
  14.  UNION ALL
  15.  SELECT 'shop002' AS shopcode, 'store003' storecode,105 b_ID, 7 num,6 price FROM dual
  16.  UNION ALL
  17.  SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 10 num,3 price FROM dual
  18.  UNION ALL
  19.  SELECT 'shop001' AS shopcode, 'store002' storecode,106 b_ID, 15 num,8 price FROM dual
  20.  )
  21. select shopcode,storecode,
  22.        sum(case when b_id=101 then num end) a,
  23.        sum(case when b_id=105 then num end) b,
  24.        sum(case when b_id=106 then num end) c,
  25.        sum(case when b_id=101 then num*price end) ,
  26.        sum(case when b_id=105 then num*price end) d,
  27.        sum(case when b_id=106 then num*price end) e
  28.        from t
  29.        GROUP BY shopcode,storecode;
  30. exit;

newkid大神優化的SQL
  1. set linesize 300;
  2. set pagesize 300;
  3. WITH t AS
  4. (
  5.  SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 2 num,10.5 price FROM dual
  6.  UNION ALL
  7.  SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 12 num,8 price FROM dual
  8.  UNION ALL
  9.  SELECT 'shop002' AS shopcode, 'store002' storecode,105 b_ID, 8 num,12.5 price FROM dual
  10.  UNION ALL
  11.  SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 20 num,19 price FROM dual
  12.  UNION ALL
  13.  SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 22 num,7 price FROM dual
  14.  UNION ALL
  15.  SELECT 'shop002' AS shopcode, 'store003' storecode,105 b_ID, 7 num,6 price FROM dual
  16.  UNION ALL
  17.  SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 10 num,3 price FROM dual
  18.  UNION ALL
  19.  SELECT 'shop001' AS shopcode, 'store002' storecode,106 b_ID, 15 num,8 price FROM dual
  20.  )
  21. select shopcode,storecode,
  22.        sum(case when b_id=101 then s1 end) a,
  23.        sum(case when b_id=105 then s1 end) b,
  24.        sum(case when b_id=106 then s1 end) c,
  25.        sum(case when b_id=101 then s2 end) ,
  26.        sum(case when b_id=105 then s2 end) d,
  27.        sum(case when b_id=106 then s2 end) e
  28.        from(
  29.                        SELECT SHOPCODE, STORECODE, B_ID, SUM(num) S1, SUM(num * price) S2
  30.                        FROM t
  31.                        GROUP BY SHOPCODE, STORECODE, B_ID
  32.            )
  33.        GROUP BY SHOPCODE, STORECODE;
  34.        exit;


優化的思路就是
sum和表掃描省略不了，
但是可以通過兩次分組，省下千萬次的case when判斷

參考:
http://www.itpub.net/thread-1781658-1-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1160880/，如需轉載，請註明出處，否則將追究法律責任。

相關文章