背景

在數(shù)據(jù)開發(fā)中，有些情況下，需要手動生成批量SQL，只需改變某個參數(shù)，比如日期，從某天到某天。

之前有一個實例，是用 stringr::str_replace_all() 去實現(xiàn)，這次就用 glue 來做示例，會更便捷。

glue

glue , 是 tidyverse 項目的一部分，擅長處理長字符串和文本段落，支持在字符串中使用變量和表達式，書寫較為自由、靈活。

glue主頁 https://glue.tidyverse.org/

目標

下面示例的表，是某WMS倉庫每日庫存快照。

在數(shù)據(jù)首次同步及初始化時，第一個日期分區(qū)包含了所有業(yè)務日期的數(shù)據(jù)。之后的每日分區(qū)中，只包含一天的業(yè)務數(shù)據(jù)。現(xiàn)需要將初始日期分區(qū)，改為獨立的日期分區(qū)，使得每個日期分區(qū)只包含一天的業(yè)務數(shù)據(jù)。

SQL腳本中，${bizdate}是一個參數(shù)，批量生成的目標是改變參數(shù)，從 20200826 到 20201125 。

備注：該問題可以通過動態(tài)分區(qū)一次性實現(xiàn)，這里用指定分區(qū)執(zhí)行多次的笨方法。

insert?overwrite?table?edw.dwd_wms_inv_snapshot_inventory_d?partition?(dt?=?'${bizdate}')
SELECT
id,
warehouse_code,
item_code,
bar_code,
lot_no,
quality,
company_code,
qty,
inventory_date,
created_dtm_loc,
updated_dtm_loc,
etl_insert_time
FROM?edw.dwd_wms_inv_snapshot_inventory_d
WHERE?dt?=?'20201125'?
and?inventory_date?=?to_date('${bizdate}',?'yyyymmdd')
;

R語言實現(xiàn)

#?載入所需的R語言包
library(magrittr)??#?使用?%>%?這個?pipeline?
library(lubridate)?#?日期處理
library(glue)??????#?字符段落處理
#?生成日期向量，格式改為?`yyyymmdd`?，這是?dt?日期分區(qū)的目標格式
ymd(20200826):ymd(20201125)?%>%
??as_date()?%>%
??as.character.Date(format?=?"%Y%m%d")?->
??bizdate
??

#?檢查是否符合預期
print(bizdate)
##??[1]?"20200826"?"20200827"?"20200828"?"20200829"?"20200830"?"20200831"
##??[7]?"20200901"?"20200902"?"20200903"?"20200904"?"20200905"?"20200906"
##?[13]?"20200907"?"20200908"?"20200909"?"20200910"?"20200911"?"20200912"
##?[19]?"20200913"?"20200914"?"20200915"?"20200916"?"20200917"?"20200918"
##?[25]?"20200919"?"20200920"?"20200921"?"20200922"?"20200923"?"20200924"
##?[31]?"20200925"?"20200926"?"20200927"?"20200928"?"20200929"?"20200930"
##?[37]?"20201001"?"20201002"?"20201003"?"20201004"?"20201005"?"20201006"
##?[43]?"20201007"?"20201008"?"20201009"?"20201010"?"20201011"?"20201012"
##?[49]?"20201013"?"20201014"?"20201015"?"20201016"?"20201017"?"20201018"
##?[55]?"20201019"?"20201020"?"20201021"?"20201022"?"20201023"?"20201024"
##?[61]?"20201025"?"20201026"?"20201027"?"20201028"?"20201029"?"20201030"
##?[67]?"20201031"?"20201101"?"20201102"?"20201103"?"20201104"?"20201105"
##?[73]?"20201106"?"20201107"?"20201108"?"20201109"?"20201110"?"20201111"
##?[79]?"20201112"?"20201113"?"20201114"?"20201115"?"20201116"?"20201117"
##?[85]?"20201118"?"20201119"?"20201120"?"20201121"?"20201122"?"20201123"
##?[91]?"20201124"?"20201125"

#?驗證有多少天(向量長度)：總共有 92 天
length(bizdate)
##?[1]?92
#?在?glue?字符串中，是可以直接使用變量的，用?{}?引用起來即可
#?如果原字符串中包含{}符號，可以修改?glue(.open?=?"{",??.close?=?"}",)?中的參數(shù)改為其他符號
glue("insert?overwrite?table?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df?partition?(dt?=?'{bizdate}')
SELECT
id,
warehouse_code,
item_code,
bar_code,
lot_no,
quality,
company_code,
qty,
inventory_date,
created_dtm_loc,
updated_dtm_loc,
etl_insert_time
FROM?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df
WHERE?dt?=?'20201125'?
and?inventory_date?=?to_date('{bizdate}',?'yyyymmdd')
;")?->
??sql
#?因為?bizdate?是字符向量，故而?sql?在經過?glue()?函數(shù)中使用了?bizdate?計算之后
#?sql?也是向量,?其長度保持與?bizdate?一致，也是?92，不需要再用?for?顯式循環(huán)
length(sql)
##?[1]?92
#?查看前兩個SQL語句
head(sql,?2)?%>%?
??cat(sep?=?"\n\n")
##?insert?overwrite?table?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df?partition?(dt?=?'20200826')
##?SELECT
##?id,
##?warehouse_code,
##?item_code,
##?bar_code,
##?lot_no,
##?quality,
##?company_code,
##?qty,
##?inventory_date,
##?created_dtm_loc,
##?updated_dtm_loc,
##?etl_insert_time
##?FROM?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df
##?WHERE?dt?=?'20201125'?
##?and?inventory_date?=?to_date('20200826',?'yyyymmdd')
##?;
##?
##?insert?overwrite?table?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df?partition?(dt?=?'20200827')
##?SELECT
##?id,
##?warehouse_code,
##?item_code,
##?bar_code,
##?lot_no,
##?quality,
##?company_code,
##?qty,
##?inventory_date,
##?created_dtm_loc,
##?updated_dtm_loc,
##?etl_insert_time
##?FROM?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df
##?WHERE?dt?=?'20201125'?
##?and?inventory_date?=?to_date('20200827',?'yyyymmdd')
##?;
#?查看后兩個SQL語句
tail(sql,?2)?%>%?
??cat(sep?=?"\n\n")
##?insert?overwrite?table?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df?partition?(dt?=?'20201124')
##?SELECT
##?id,
##?warehouse_code,
##?item_code,
##?bar_code,
##?lot_no,
##?quality,
##?company_code,
##?qty,
##?inventory_date,
##?created_dtm_loc,
##?updated_dtm_loc,
##?etl_insert_time
##?FROM?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df
##?WHERE?dt?=?'20201125'?
##?and?inventory_date?=?to_date('20201124',?'yyyymmdd')
##?;
##?
##?insert?overwrite?table?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df?partition?(dt?=?'20201125')
##?SELECT
##?id,
##?warehouse_code,
##?item_code,
##?bar_code,
##?lot_no,
##?quality,
##?company_code,
##?qty,
##?inventory_date,
##?created_dtm_loc,
##?updated_dtm_loc,
##?etl_insert_time
##?FROM?akdc.ods_akc_wms_cloud_inv_snapshot_inventory_df
##?WHERE?dt?=?'20201125'?
##?and?inventory_date?=?to_date('20201125',?'yyyymmdd')
##?;
#?將結果寫入到文件中(控制臺顯示不下)
sql?%>%
??write(file?=?"out_put.txt",?sep?=?"\n\n")

#?打開該目錄新生成的txt文件就可以看到全部，復制出來即可
#?可直接用RStudio文件模塊直接點擊打開文本窗口，也可用記事本之類的軟件打開

補充示例1

library(magrittr)?#?使用?%>%?這個?pipeline?
library(glue)?????#?字符段落處理


#?生成日期序列，并轉為字符型，格式為?yyyy-mm-dd
seq(from?=?as.Date("2018-06-11"),?
????to?=?as.Date("2018-06-13"),?
????by?=?1
????)?%>%
??as.character()?->
??seq_date

glue("insert?overwrite?table?rpt.rpt_collection_appoint_stronger_in_daily?partition?(dt?=?'{seq_date}')
select?appoint_stronger,?count(case_id)?as?case_num
from?edw.collection_case_strength_h
where?to_date(start_time)?<=?'{seq_date}'
and?to_date(end_time)?>=?'{seq_date}'
group?by?appoint_stronger
;")?%>%
??cat(sep?=?"\n\n")?#?輸出大屏幕，在結尾換行，之后再換行另起一行
##?insert?overwrite?table?rpt.rpt_collection_appoint_stronger_in_daily?partition?(dt?=?'2018-06-11')
##?select?appoint_stronger,?count(case_id)?as?case_num
##?from?edw.collection_case_strength_h
##?where?to_date(start_time)?<=?'2018-06-11'
##?and?to_date(end_time)?>=?'2018-06-11'
##?group?by?appoint_stronger
##?;
##?
##?insert?overwrite?table?rpt.rpt_collection_appoint_stronger_in_daily?partition?(dt?=?'2018-06-12')
##?select?appoint_stronger,?count(case_id)?as?case_num
##?from?edw.collection_case_strength_h
##?where?to_date(start_time)?<=?'2018-06-12'
##?and?to_date(end_time)?>=?'2018-06-12'
##?group?by?appoint_stronger
##?;
##?
##?insert?overwrite?table?rpt.rpt_collection_appoint_stronger_in_daily?partition?(dt?=?'2018-06-13')
##?select?appoint_stronger,?count(case_id)?as?case_num
##?from?edw.collection_case_strength_h
##?where?to_date(start_time)?<=?'2018-06-13'
##?and?to_date(end_time)?>=?'2018-06-13'
##?group?by?appoint_stronger
##?;

補充示例2

library(magrittr)?#?使用?%>%?這個?pipeline?
library(glue)?????#?字符段落處理

seq.Date(from?=?as.Date("2018-05-24"),?
?????????to?=?as.Date("2018-06-02"),?
?????????by?=?1
?????????)?%>%
??as.character()?->?
??seq_date

#?glue?支持在行尾使用\\雙反斜杠來斷行，而不會造成原字符串換行
#?在代碼書寫盡可能避免水平滾動條的出現(xiàn)是基本原則，該該功能非常實用
glue("
alter?table?\\
rpt.rpt_collection_ccms_oacase_list?\\
drop?if?exists?partition?\\
(dt?=?'{seq_date}');
")
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-24');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-25');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-26');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-27');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-28');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-29');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-30');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-05-31');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-06-01');
##?alter?table?rpt.rpt_collection_ccms_oacase_list?drop?if?exists?partition?(dt?=?'2018-06-02');

總結

以上是生活随笔為你收集整理的sql 关联使用id还是code_R语言实例：用glue批量生成SQL语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。