在论坛看到一个帖子，需求是行转列。
但是表中的数据是千万级别的，运行很慢。
楼主的SQL
newkid大神优化的SQL
优化的思路就是
sum和表扫描省略不了，
但是可以通过两次分组，省下千万次的case when判断
参考:
http://www.itpub.net/thread-1781658-1-1.html
但是表中的数据是千万级别的，运行很慢。
楼主的SQL
-
set linesize 300;
-
set pagesize 300;
-
WITH t AS
-
(
-
SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 2 num,10.5 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 12 num,8 price FROM dual
-
UNION ALL
-
SELECT 'shop002' AS shopcode, 'store002' storecode,105 b_ID, 8 num,12.5 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 20 num,19 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 22 num,7 price FROM dual
-
UNION ALL
-
SELECT 'shop002' AS shopcode, 'store003' storecode,105 b_ID, 7 num,6 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 10 num,3 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store002' storecode,106 b_ID, 15 num,8 price FROM dual
-
)
-
select shopcode,storecode,
-
sum(case when b_id=101 then num end) a,
-
sum(case when b_id=105 then num end) b,
-
sum(case when b_id=106 then num end) c,
-
sum(case when b_id=101 then num*price end) ,
-
sum(case when b_id=105 then num*price end) d,
-
sum(case when b_id=106 then num*price end) e
-
from t
-
GROUP BY shopcode,storecode;
- exit;
newkid大神优化的SQL
-
set linesize 300;
-
set pagesize 300;
-
WITH t AS
-
(
-
SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 2 num,10.5 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 12 num,8 price FROM dual
-
UNION ALL
-
SELECT 'shop002' AS shopcode, 'store002' storecode,105 b_ID, 8 num,12.5 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 20 num,19 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store002' storecode,101 b_ID, 22 num,7 price FROM dual
-
UNION ALL
-
SELECT 'shop002' AS shopcode, 'store003' storecode,105 b_ID, 7 num,6 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store003' storecode,101 b_ID, 10 num,3 price FROM dual
-
UNION ALL
-
SELECT 'shop001' AS shopcode, 'store002' storecode,106 b_ID, 15 num,8 price FROM dual
-
)
-
select shopcode,storecode,
-
sum(case when b_id=101 then s1 end) a,
-
sum(case when b_id=105 then s1 end) b,
-
sum(case when b_id=106 then s1 end) c,
-
sum(case when b_id=101 then s2 end) ,
-
sum(case when b_id=105 then s2 end) d,
-
sum(case when b_id=106 then s2 end) e
-
from(
-
SELECT SHOPCODE, STORECODE, B_ID, SUM(num) S1, SUM(num * price) S2
-
FROM t
-
GROUP BY SHOPCODE, STORECODE, B_ID
-
)
-
GROUP BY SHOPCODE, STORECODE;
-
exit;
-
优化的思路就是
sum和表扫描省略不了，
但是可以通过两次分组，省下千万次的case when判断
参考:
http://www.itpub.net/thread-1781658-1-1.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-1160880/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
MySQL Join Buffer
下一篇：
JAVA切分大文件
请登录后发表评论
登录
全部评论
