源代码：大批量SQL代码语法转换实战：PIVOT函数改写(案例2)
【摘要】 源代码：大批量SQL代码语法转换实战：PIVOT函数改写
### 背景：在不同数据库迁移的项目中，往往会遇到SQL语法不兼容的情况。比如有的数据库支持PIVOT函数，有的不支持。遇到这种情况，就必须对PIVOT函数进行改写。
### 问题：如果存在大量代码需要改写的情况，靠人工处理会很耗时，且容易出错。能不能通过工具实现代码语法的大批量自动转换？
### 方案：可以使用开源代码解析器 ZGLanguage 对SQL代码进行大批量自动转换
### 案例演示：
# 存在 SQL PIVOT函数 如下所示：
SELECT *
FROM table2222 PIVOT
(
SUM(sales) AS ss1,
SUM(cogs) AS sc
FOR (yr, qtr)
IN (
(2001, 'Q1'),
(2001, 'Q2'),
(2001, 'Q3'),
(2001, 'Q4')
)
) tmp
;
# 使用开源软件 ZGLanguage 转换规则，执行转换，可得到结果：
SELECT *
FROM
(
select ###,###,###
SUM(case when yr=2001 and qtr='Q1' then sales else null end ) AS "2001_Q1_ss1",
SUM(case when yr=2001 and qtr='Q2' then sales else null end ) AS "2001_Q2_ss1",
SUM(case when yr=2001 and qtr='Q3' then sales else null end ) AS "2001_Q3_ss1",
SUM(case when yr=2001 and qtr='Q4' then sales else null end ) AS "2001_Q4_ss1",
SUM(case when yr=2001 and qtr='Q1' then cogs else null end ) AS "2001_Q1_sc",
SUM(case when yr=2001 and qtr='Q2' then cogs else null end ) AS "2001_Q2_sc",
SUM(case when yr=2001 and qtr='Q3' then cogs else null end ) AS "2001_Q3_sc",
SUM(case when yr=2001 and qtr='Q4' then cogs else null end ) AS "2001_Q4_sc"
from table2222
where (yr, qtr) IN
(
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4')
)
group by ###,###,###
) tmp
;
# 转换规则如下所示 ：
__DEF_FUZZY__ Y
__DEF_DEBUG__ N
__DEF_CASE_SENSITIVE__ N
__DEF_LINE_COMMENT__ --
__DEF_LINES_COMMENT__ /* */
__DEF_STR__ __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_
__DEF_PATH__ __FROM_PIVOT_2_1__
1 : frm @ %__IF_KW__ | from
: tab @ | __TABLE_NAME__
: ssl @ + __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__ __//__ sum ....
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ %__IF_KW__ CAN_SKIP | as
: colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ %__IF_KW__ | for
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | )
: x2 @ | )
------------------------------------------------------------------
1 : frm @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ | as
: colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ | for
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __\b__
: y1 @ | __COLS_4_FOR__
: y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | )
1 : for2 @ | where
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | )
: x2 @ | )
__DEF_PATH__ __FROM_PIVOT_2_2__
1 : frm @ %__IF_KW__ | from
: tab @ | __TABLE_NAME__
: ssl @ + __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ %__IF_KW__ CAN_SKIP | as
: colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ %__IF_KW__ | for
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __COLS_VALUES__
e : dh7 @ | ,
1 : y6 @ | )
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x2 @ | )
------------------------------------------------------------------
1 : frm @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ | as
: colas @ | __NAME__
* : y3 @ | __COLS_VALUES__
e : y3 @ | ,
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x2 @ | )
__DEF_PATH__ __FROM_PIVOT_2_3__
1 : frm @ %__IF_KW__ | from
: tab @ | __TABLE_NAME__
: ssl @ + __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ %__IF_KW__ CAN_SKIP | as
: colas @ | __NAME__
: cw @ | __CASE_WHEN__
: as2 @ | as
: y2 @ | __VALUE_2_COL__
e : y3 @ | ,
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x2 @ | )
--------------------------------------------------------------
1 : frm @ | from
: x1 @ | (
: x1 @ STRING | select ###,###,###
N : fun @ | __NAME__
: fs @ | (
: cw @ | __CASE_WHEN__
: col1 @ | __NAME__
: col1 @ STRING | else null end
: fe @ | )
: as1 @ | as
: y2 @ | __VALUE_2_COL__
: colas @ \ __NAME__
: colas @ \ "
e : y3 @ | ,
1 : pvt @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x1 @ STRING | group by ###,###,###
: x2 @ | )
__DEF_SUB_PATH__ __VALUE_2_COL__
N : x1 @ | __INT__
+ : x2 @ | '
: x3 @ | __ANY__
: x4 @ | '
------------------------------------------------------------------
1 : x1 @ | "
: x3 @ | "
N : x1 @ \ __INT__
: x3 @ \ __ANY__
: x1 @ \ _
: x3 @ \ _
__DEF_SUB_PATH__ __CASE_WHEN__
N : x1 @ | __NAME__
: x2 @ | =
: x3 @ | __INT__
: x4 @ + __STRING__
e : x5 @ | and
------------------------------------------------------------------
1 : x1 @ STRING | case when
N : x1 @ | __NAME__
: x2 @ | =
: x3 @ | __INT__
: x4 @ | __STRING__
e : x5 @ | and
1 : x1 @ | then
__DEF_SUB_PATH__ __COLS_VALUES__
1 : x1 @ | (
N : x2 @ | __NAME__
e : x3 @ | ,
1 : x4 @ | )
: y1 @ | (
N : y2 @ | __INT__
: y3 @ + __STRING__
e : y4 @ | ,
1 : y5 @ | )
----------------------------------------------------------------------
N : x2 @ | __NAME__
: x2 @ / =
: y2 @ / __INT__
: y3 @ / __STRING__
e : x2 @ | and
1 : x2 @ | as
N : y2 @ | __INT__
: y3 @ | __STRING__
__DEF_SUB_PATH__ __COLS_4_FOR__
1 : x1 @ | (
N : x2 @ | __NAME__
e : x3 @ | ,
1 : x4 @ | )
__DEF_SUB_PATH__ __VALUE_4_IN__
1 : x1 @ | (
N : x2 @ | __INT__
: x3 @ + __STRING__
e : x4 @ | ,
1 : x5 @ | )
__DEF_SUB_PATH__ __TABLE_NAME__
1 : srctab @ | __NAME__
+ : schema @ | __NAME__
: pp @ | .
: srctab2 @ | __NAME__
__DEF_SUB_PATH__ __SUB_SELECT__
1 : x1 @ | __SUB__
__DEF_PATH__ __SUB__
1 : x1 @ | (
N : x2 @ | __ALL_STR__
: x3 @ + __SUB__
1 : x4 @ | )
__DEF_STR__ __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/
__DEF_STR__ __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert update delete truncate drop merge table select inner left join on from where group order partition by having union all with as set between and or like in is not null case when then pivot lateral view
__DEF_STR__ __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789
__DEF_STR__ __INT__
<1,100>
[1,100]0123456789
__DEF_SUB_PATH__ __STRING__
1 : x1 | '
: x2 | __ANY__
: x3 | '
### 转换规则详细说明：
以上PIVOT函数的转换规则比较复杂，不能一次性转换完毕，这里分成3次转换完成：
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r pivot_unpivot.code -o 1_mid_result.zgl ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 1_mid_result.zgl -o 2_mid_result.zgl ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 2_mid_result.zgl -o result.zgl
# 第1次转换规则 “__FROM_PIVOT_2_1__” 对源代码进行转换，
(A) 值“(yr, qtr)” 和 枚举值 “Q1,Q2,Q3,Q4” 的一一映射关系
(B) 新增：where结构（由 FOR 结构转换得到）
得到如下结果：
SELECT *
FROM table2222 PIVOT
(
SUM ( sales ) AS ss1 ,
SUM ( cogs ) AS sc
FOR (yr, qtr)
IN (
(yr, qtr) (2001, 'Q1') ,
(yr, qtr) (2001, 'Q2') ,
(yr, qtr) (2001, 'Q3') ,
(yr, qtr) (2001, 'Q4') )
where (yr, qtr)
IN (
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4') )
) tmp
;
# 第2次转换规则 “__FROM_PIVOT_2_2__” 对 “__FROM_PIVOT_2_1__” 的转换结果(以上)再次进行转换。
完成：
(A) 聚合函数“SUM字段” 和 “(yr, qtr)字段” 的笛卡尔积映射
(B) 提取枚举值准备生成新的字段别名
得到如下结果：
SELECT *
FROM table2222 PIVOT
(
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q1' as 2001 'Q1' ,
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q2' as 2001 'Q2' ,
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q3' as 2001 'Q3' ,
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q4' as 2001 'Q4' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q1' as 2001 'Q1' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q2' as 2001 'Q2' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q3' as 2001 'Q3' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q4' as 2001 'Q4'
where (yr, qtr)
IN (
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4') )
) tmp
;
# 第3次转换规则 “__FROM_PIVOT_2_3__” 对 “__FROM_PIVOT_2_2__” 的转换结果(以上)再次进行转换。
完成：
(A) 对SUM开头的字段内容进行新增、位移、合并等操作，形成语法正确的字段逻辑
(B) 剔除PIVOT关键字，移动表名到 where 语句上方
(C) 拼接新的字段名称
(D) 新增待人工补充部分： select ###,###,### group by ###,###,###
得到最终结果：
SELECT *
FROM
(
select ###,###,###
SUM(case when yr=2001 and qtr='Q1' then sales else null end) AS "2001_Q1_ss1",
SUM(case when yr=2001 and qtr='Q2' then sales else null end) AS "2001_Q2_ss1",
SUM(case when yr=2001 and qtr='Q3' then sales else null end) AS "2001_Q3_ss1",
SUM(case when yr=2001 and qtr='Q4' then sales else null end) AS "2001_Q4_ss1",
SUM(case when yr=2001 and qtr='Q1' then cogs else null end) AS "2001_Q1_sc",
SUM(case when yr=2001 and qtr='Q2' then cogs else null end) AS "2001_Q2_sc",
SUM(case when yr=2001 and qtr='Q3' then cogs else null end) AS "2001_Q3_sc",
SUM(case when yr=2001 and qtr='Q4' then cogs else null end) AS "2001_Q4_sc"
from table2222
where (yr, qtr)
IN (
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4') )
group by ###,###,###
) tmp
;
### 新增待补充部分 ###,###,### 说明：
1、通过简单的配置，不能直接转换成完全可用的SQL代码，有些代码部分依然需要人工补充
2、需要人工补充的部分，已经通过 ###,###,### 明显地标注出来
3、通过工具已经完成了大部分的转换工作，可以极大减轻人工参与的工作量，规避人工修改失误的风险
【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）