源代码：大批量SQL代码语法转换实战：PIVOT函数改写(案例1)

### 背景：在不同数据库迁移的项目中，往往会遇到SQL语法不兼容的情况。比如有的数据库支持PIVOT函数，有的不支持。遇到这种情况，就必须对PIVOT函数进行改写。
### 问题：如果存在大量代码需要改写的情况，靠人工处理会很耗时，且容易出错。能否通过工具实现代码语法的大批量自动转换？
### 方案：可以使用开源代码 解析器 ZGLanguage 对SQL代码进行大批量自动转换
### 案例演示：
# 存在 SQL PIVOT函数 如下所示：

1. SELECT *
2. FROM (select country,state,yr,qtr,sales,cogs from table111)
3. PIVOT
4. (
5.     SUM(sales) AS ss1,
6.     SUM(cogs)  AS sc
7.     FOR qtr IN
8.     (
9.         'Q1' AS Quarter1,
10.         'Q2' AS Quarter2,
11.         'Q3' AS Quarter3,
12.         'Q4' AS Quarter4
13.     )
14. ) tmp
15. ;

复制代码

# 使用开源软件  ZGLanguage 执行转换，可得到结果：

1. SELECT *
2. FROM
3. (
4.   select ###,###,###
5.          SUM (case when qtr='Q1' then sales else null end) AS Quarter1_ss1,
6.          SUM (case when qtr='Q2' then sales else null end) AS Quarter2_ss1,
7.          SUM (case when qtr='Q3' then sales else null end) AS Quarter3_ss1,
8.          SUM (case when qtr='Q4' then sales else null end) AS Quarter4_ss1,
9.          SUM (case when qtr='Q1' then cogs else null end) AS Quarter1_sc,
10.          SUM (case when qtr='Q2' then cogs else null end) AS Quarter2_sc,
11.          SUM (case when qtr='Q3' then cogs else null end) AS Quarter3_sc,
12.          SUM (case when qtr='Q4' then cogs else null end) AS Quarter4_sc
13.   from (select country,state,yr,qtr,sales,cogs from table111)
14. where qtr IN('Q1','Q2','Q3','Q4')
15. group by ###,###,###
16. ) tmp
17. ;

复制代码

# 转换规则如下所示 ：

1. __DEF_FUZZY__             Y
2. __DEF_DEBUG__             N
3. __DEF_CASE_SENSITIVE__    N
5. __DEF_LINE_COMMENT__      --
6. __DEF_LINES_COMMENT__     /*     */
9. __DEF_STR__   __IF_KW__
10. <1,100>
11. [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
12. [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_
13. [NO] XXX
15. __DEF_PATH__    __FROM_PIVOT_1_1__
16. 1              : frm         @ %__IF_KW__             | from
17.                : tab         @                        | __TABLE_NAME__
18.                : ssl         @                        + __SUB_SELECT__
19.                : pvt         @                        | pivot
20.                : x1          @                        | (
21. N              : fun         @                        | __NAME__        __//__ sum ....
22.                : fs          @                        | (
23.                : col1        @                        | __NAME__
24.                : fe          @                        | )
25.                : as1         @ %__IF_KW__ CAN_SKIP    | as
26.                : colas       @                        | __NAME__
27. e              : dh1         @                        | ,
28. 1              : for         @ %__IF_KW__             | for
29.                : col2        @                        | __NAME__
30.                : in          @                        | in
31.                : x3          @                        | (
32. N              : val1        @                        | __INT__
33.                : val2        @                        + __STRING__
34.                : as2         @ CAN_SKIP               | as
35.                : coln        @                        | __NAME__
36. e              : dh          @                        | ,
37. 1              : x4          @                        | )
38.                : x2          @                        | )
39. -------------------------------------------------------------------------
40. 1              : frm         @                        | from
41.                : tab         @                        | __TABLE_NAME__
42.                : ssl         @                        | __SUB_SELECT__
43.                : pvt         @                        | pivot
44.                : x1          @                        | (
45. N              : fun         @                        | __NAME__
46.                : fs          @                        | (
47.                : col1        @                        | __NAME__
48.                : fe          @                        | )
49.                : as1         @                        | as
50.                : colas       @                        | __NAME__
51. e              : dh1         @                        | ,
52. 1              : for         @                        | for
53.                : col2        @                        | __NAME__
54.                : in          @                        | in
55.                : x3          @                        | (
56. N              : val1        @                        | __\b__
57.                : val2        @                        | __\b__
58.                : col2        @                        | __NAME__
59.                : col2        @                        | =
60.                : val1        @                        | __INT__
61.                : val2        @                        | __STRING__
62.                : as2         @                        | as
63.                : coln        @                        | __NAME__
64. e              : dh          @                        | ,
65. 1              : x4          @                        | )
66.                : x2          @                        | )
69. __DEF_PATH__    __FROM_PIVOT_1_2__
70. 1              : frm         @ %__IF_KW__             | from
71.                : tab         @                        | __TABLE_NAME__
72.                : ssl         @                        + __SUB_SELECT__
73.                : pvt         @                        | pivot
74.                : x1          @                        | (
75. N              : fun         @                        | __NAME__        __//__ sum ....
76.                : fs          @                        | (
77.                : col1        @                        | __NAME__
78.                : fe          @                        | )
79.                : as1         @ %__IF_KW__ CAN_SKIP    | as
80.                : colas       @                        | __NAME__
81. e              : dh1         @                        | ,
82. 1              : for         @ %__IF_KW__             | for
83.                : col2        @                        | __NAME__
84.                : in          @                        | in
85.                : x3          @                        | (
86. N              : col22       @                        | __NAME__
87.                : col23       @                        | =
88.                : val1        @                        | __INT__
89.                : val2        @                        + __STRING__
90.                : as2         @ CAN_SKIP               | as
91.                : coln        @                        | __NAME__
92. e              : dh          @                        | ,
93. 1              : x4          @                        | )
94.                : x2          @                        | )
95. --------------------------------------------------------------------
96. 1              : frm         @                        | from
97.                : tab         @                        | __TABLE_NAME__
98.                : ssl         @                        | __SUB_SELECT__
99.                : pvt         @                        | pivot
100.                : x1          @                        | (
101. N              : fun         @                        | __NAME__
102.                : fs          @                        | (
103.                : col1        @                        | __NAME__
104.                : fe          @                        | )
105.                : as1         @                        | as
106.                : colas       @                        | __NAME__
107. *              : col22       @                        | __NAME__
108.                : col23       @                        | =
109.                : val1        @                        | __INT__
110.                : val2        @                        | __STRING__
111.                : as2         @                        | as
112.                : coln        @                        | __NAME__
113. e              : coln        @                        | ,
114. 1              : for         @                        | where
115.                : col2        @                        | __NAME__
116.                : in          @                        | in
117.                : x3          @                        | (
118. N              : val1        @                        | __INT__
119.                : val2        @                        | __STRING__
120. e              : dh          @                        | ,
121. 1              : x4          @                        | )
122. 1              : x2          @                        | )
125. __DEF_PATH__    __FROM_PIVOT_1_3__
126. 1              : frm         @ %__IF_KW__             | from
127.                : tab         @                        | __TABLE_NAME__
128.                : ssl         @                        + __SUB_SELECT__
129.                : pvt         @                        | pivot
130.                : x1          @                        | (
131. N              : fun         @                        | __NAME__
132.                : fs          @                        | (
133.                : col1        @                        | __NAME__
134.                : fe          @                        | )
135.                : as1         @ %__IF_KW__ CAN_SKIP    | as
136.                : colas       @                        | __NAME__
137.                : col22       @                        | __NAME__
138.                : col23       @                        | =
139.                : val1        @                        | __INT__
140.                : val2        @                        + __STRING__
141.                : as2         @ %__IF_KW__ CAN_SKIP    | as
142.                : coln        @                        | __NAME__
143. e              : dh          @                        | ,
144. 1              : for         @                        | where
145.                : col2        @                        | __NAME__
146.                : in          @                        | in
147.                : x3          @                        | (
148. N              : val3        @                        | __INT__
149.                : val4        @                        + __STRING__
150. e              : dh1         @                        | ,
151. 1              : x4          @                        | )
152.                : x2          @                        | )
153. --------------------------------------------------------------------
154. 1              : frm         @ STRING                 | from
155.                : pvt         @ STRING                 | (select ###,###,###
156. N              : fun         @                        | __NAME__
157.                : fs          @                        / (
158.                : col22       @ STRING                 \ case when
159.                : col22       @                        / __NAME__
160.                : col23       @                        / =
161.                : val1        @                        / __INT__
162.                : val2        @                        / __STRING__
163.                : col1        @                        / then
164.                : col1        @                        / __NAME__
165.                : col1        @ STRING                 / else null end
166.                : fe          @                        \ )
167.                : as1         @                        | as
168.                : coln        @                        | __NAME__
169.                : coln        @                        \ _
170.                : colas       @                        \ __NAME__
171. e              : dh          @                        | ,
172. 1              : pvt         @                        | from
173.                : tab         @                        | __TABLE_NAME__
174.                : ssl         @                        | __SUB_SELECT__
175. 1              : for         @                        | where
176.                : col2        @                        / __NAME__
177.                : in          @                        / in
178.                : x3          @                        \ (
179. N              : val3        @                        \ __INT__
180.                : val4        @                        \ __STRING__
181. e              : dh1         @                        \ ,
182. 1              : x4          @                        \ )
183.                : x4          @ STRING                 | group by ###,###,###
184.                : x2          @                        | )
187. __DEF_SUB_PATH__       __TABLE_NAME__
188. 1        : srctab           @               | __NAME__
189. +        : schema           @               | __NAME__
190.          : pp               @               | .
191.          : srctab2          @               | __NAME__
194. __DEF_SUB_PATH__   __SUB_SELECT__
195. 1        : x1               @               | __SUB__
198. __DEF_PATH__   __SUB__
199. 1        : x1               @               | (
200. N        : x2               @               | __ALL_STR__
201.          : x3               @               + __SUB__
202. 1        : x4               @               | )
205. __DEF_STR__   __ALL_STR__
206. <1,20000>
207. [1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/
208. __DEF_STR__   __NAME__
209. <1,100>
210. [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
211. [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
212. [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
213. __DEF_STR__   __INT__
214. <1,100>
215. [1,100]0123456789
216. __DEF_SUB_PATH__   __STRING__
217. 1       : x1                  | '
218.         : x2                  | __ANY__
219.         : x3                  | '

复制代码

 
### 转换规则详细说明：
以上PIVOT函数的转换规则比较复杂，不能一次性转换完毕，这里分成3次转换完成：

1. ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r pivot_unpivot.code -o 1_mid_result.zgl
2. ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 1_mid_result.zgl -o 2_mid_result.zgl
3. ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 2_mid_result.zgl -o result.zgl

复制代码

# 第1次转换规则 “__FROM_PIVOT_1_1__” 对源代码进行转换，完成 值“qtr” 和 枚举值 “Q1,Q2,Q3,Q4” 的一一映射关系，得到如下结果：

1. SELECT *
2. FROM (select country,state,yr,qtr,sales,cogs from table111)
3. PIVOT
4. (
5.     SUM(sales ) AS ss1 ,
6.     SUM(cogs) AS sc
7.     FOR qtr IN
8.     (  
9.     qtr = 'Q1' AS Quarter1 ,  
10.     qtr = 'Q2' AS Quarter2 ,  
11.     qtr = 'Q3' AS Quarter3 ,  
12.     qtr = 'Q4' AS Quarter4
13.     )
14. ) tmp
15. ;

复制代码

# 第2次转换规则 “__FROM_PIVOT_1_2__” 对 “__FROM_PIVOT_1_1__” 的转换结果(以上)再次进行转换。 
  完成：
  (A) 聚合函数“SUM字段” 和 “qtr字段” 的笛卡尔积映射
  (B) FOR 结构转成 where 结构
  得到如下结果：

1. SELECT *
2. FROM (select country,state,yr,qtr,sales,cogs from table111) PIVOT
3. (
4.     SUM(sales) AS ss1   qtr = 'Q1' AS Quarter1 ,
5.     SUM(sales) AS ss1   qtr = 'Q2' AS Quarter2 ,
6.     SUM(sales) AS ss1   qtr = 'Q3' AS Quarter3 ,
7.     SUM(sales) AS ss1   qtr = 'Q4' AS Quarter4 ,
8.        
9.     SUM(cogs) AS sc     qtr = 'Q1' AS Quarter1 ,
10.     SUM(cogs) AS sc     qtr = 'Q2' AS Quarter2 ,
11.     SUM(cogs) AS sc     qtr = 'Q3' AS Quarter3 ,
12.     SUM(cogs) AS sc     qtr = 'Q4' AS Quarter4
13.        
14.         where qtr IN
15.     (
16.         'Q1' ,
17.         'Q2' ,
18.         'Q3' ,
19.         'Q4'
20.     )
21. ) tmp
22. ;

复制代码

# 第3次转换规则 “__FROM_PIVOT_1_3__” 对 “__FROM_PIVOT_1_2__” 的转换结果(以上)再次进行转换。 
  完成：
  (A) 对SUM开头的字段内容进行新增、位移、合并 等操作，形成语法正确的字段逻辑
  (B) 剔除PIVOT关键字，移动子查询到 where 语句上方
  (C) 新增待人工补充部分： select ###,###,###   group by ###,###,###
  得到最终结果：

1. SELECT *
2. FROM
3. (
4.   select ###,###,###
5.          SUM(case when qtr='Q1' then sales else null end) AS Quarter1_ss1,
6.          SUM(case when qtr='Q2' then sales else null end) AS Quarter2_ss1,
7.          SUM(case when qtr='Q3' then sales else null end) AS Quarter3_ss1,
8.          SUM(case when qtr='Q4' then sales else null end) AS Quarter4_ss1,
9.          SUM(case when qtr='Q1' then cogs else null end) AS Quarter1_sc,
10.          SUM(case when qtr='Q2' then cogs else null end) AS Quarter2_sc,
11.          SUM(case when qtr='Q3' then cogs else null end) AS Quarter3_sc,
12.          SUM(case when qtr='Q4' then cogs else null end) AS Quarter4_sc
13.   from (select country,state,yr,qtr,sales,cogs from table111)
14. where qtr IN('Q1','Q2','Q3','Q4')
15. group by ###,###,###
16. ) tmp
17. ;

复制代码

### 新增待补充部分 “###,###,###” 说明：
1、通过简单的配置，不能直接转换成完全可用的SQL代码，有些代码部分依然需要人工补充
2、需要人工补充的部分，已经通过 ###,###,### 明显地标注出来
3、通过工具已经完成了大部分的转换工作，极大的减轻了人工参与的工作量，规避人工修改失误的风险
源代码下载： https://gitee.com/zgl-20053779/zglanguage


 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！