My input and output are as follows
input:
SELECT *,
grass_date,
grass_region AS region
FROM (SELECT DISTINCT userid AS user_id,
username,
email,
email_verified,
edm_flag, Datediff(CURRENT_DATE(), last_login) AS
days_since_last_login, Datediff(CURRENT_DATE(),
last_valid_checkout_time_sgt) AS
days_since_last_checkout, Datediff(CURRENT_DATE(), last_purchase_time) AS
days_since_last_purchase, Datediff(CURRENT_DATE(), registration_time) AS
days_since_registration, churn_score, is_life_cycle, grass_date,
grass_region
FROM shopee_reg_mkt_anlys.crm_buyer_lifecycle
WHERE grass_region = 'PH' AND is_life_cycle = 1 AND
total_valid_checkout = 0
AND status = 1 AND shopee_reg_mkt_anlys.crm_buyer_lifecycle.grass_date =
Date_sub(CURRENT_DATE(), 2)) TBLA
WHERE ( TBLA.days_since_registration >= 22
AND TBLA.days_since_registration <= 90 )
OR ( TBLA.days_since_registration > 90
AND TBLA.days_since_last_login < 15 )
output:
{
"code": 0,
"msg": "success",
"tid": "425a67a283d04705bbd69898d0015e65",
"result": {
"inPutTableAndFields": {
"shopee_reg_mkt_anlys.crm_buyer_lifecycle": [
"datediff(current_date(),last_login)",
"email_verified",
"datediff(current_date(),registration_time)",
"datediff(current_date(),last_valid_checkout_time_sgt)",
"edm_flag",
"userid",
"is_life_cycle",
"datediff(current_date(),last_purchase_time)",
"grass_date",
"email",
"churn_score",
"grass_region",
"username"
]
},
"outPutTable": "",
"outPutFields": [
"*",
"grass_date",
"grass_region"
]
}
}
Now, I want to remove the function part in inPutTableAndFields and only show the fields such as:
datediff(current_date(),registration_time) -> registration_time
Below is my code to get the dependent fields and tables in the parse tree:
public class GetInputTableAndFieldsLinstener extends SqlBaseBaseListener {
LinkedBlockingDeque<String> dependTableAndFieldsQueue;
public GetInputTableAndFieldsLinstener(LinkedBlockingDeque<String> dependTableAndFieldsQueue) {
this.dependTableAndFieldsQueue = dependTableAndFieldsQueue;
}
@Override
public void enterRelation(SqlBaseParser.RelationContext ctx) {
if (ctx.getChild(0).getText().contains("(")) { //判断是否为子查询
dependTableAndFieldsQueue.removeLast();
}
//log.info("队列中元素有" + dependTableAndFieldsQueue.toString());
}
//若重写enterExpression,队列需要add多次,达不到遇到自查询后及时清除队列中上一元素效果
@SneakyThrows
@Override
public void enterNamedExpressionSeq(SqlBaseParser.NamedExpressionSeqContext ctx) {
//遍历namedEXpressionSeq子树,剔除as别名、函数个别字符临时替换
String tmp = "";
String fields = "";
for (ParseTree tree : ctx.children) {
//判断是否为标准namedExression子树(孩子数目为1或者3)还是null(值为',')子树(孩子数目为0)或异常情况;
//判断孩子数目为0时需额外考虑select字段出现两次','符号仍提取成功的情况
if ((tree.getChildCount() != 0 && tree.getChild(0).getChild(0).getChildCount() == 1) || tree.getChildCount() == 3) {
//如果是非函数部分直接获取函数体,否则临时需要临时对`,`字符进行替换
if (!tree.getChild(0).getText().contains("(")) {
fields += tmp.concat(tree.getChild(0).getText());
} else {
fields += tree.getChild(0).getText().replace(",", "/");
}
} else if (tree.getChildCount() == 0) { //
fields += tmp.concat(","); //添加字段分隔符','
} else {
//sql语法错误,抛出异常,解析失败
log.error("血缘提取sql语法有误");
throw new RuntimeException();
}
}
dependTableAndFieldsQueue.add(fields);
}
@Override
public void enterTableName(SqlBaseParser.TableNameContext ctx) {
dependTableAndFieldsQueue.add("table." + ctx.multipartIdentifier().getText());
}
}