How to accurately extract dependent fields in select type sql

Viewed 51

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());
    }

}
0 Answers
Related