hive/spark-sql使用自定义函数解决sql不能解决的情况

package com.mingzhi;

import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;

/**
 * 计算客户评价，回访，审核评价星级
 */
public class GetStar extends UDF {


    /**
     * values:AUDIT_ORDER:3|RETURN_VISIT:5|EVALUATE_ORDER:5
     * concat()函数只要一个参数为null，返回的值就是null
     * @param s AUDIT_ORDER->AUDIT_ORDER:3|RETURN_VISIT:5|EVALUATE_ORDER:5
     * @return
     */
    public String evaluate(final String s) {

        if (StringUtils.isEmpty(s)) {
            return "-1";
        }

        int audit_order_star = 0;
        int revisit_star = 0;
        int evaluate_order_star = 0;

        String[] kv = s.split("->");

        if (kv.length==1){
            return "-1";
        }

        if (StringUtils.isEmpty(kv[1])) {
            return "-1";
        }

        for (String s1 : kv[1].split("\\|")) {

            String[] typeStar = s1.split(":");

            if (s1.contains("AUDIT_ORDER")) {

                if (!StringUtils.isEmpty(typeStar[1])) {
                    audit_order_star = Integer.parseInt(typeStar[1]);
                }


            } else if (s1.contains("RETURN_VISIT")) {
                if (!StringUtils.isEmpty(typeStar[1])) {
                    revisit_star = Integer.parseInt(typeStar[1]);
                }
            } else if (s1.contains("EVALUATE_ORDER")) {
                if (!StringUtils.isEmpty(typeStar[1])) {
                    evaluate_order_star = Integer.parseInt(typeStar[1]);
                }
            }

        }


        switch (kv[0]) {
            case "AUDIT_ORDER":
                return audit_order_star + "";
            case "RETURN_VISIT":
                return revisit_star + "";

            case "EVALUATE_ORDER":
                return evaluate_order_star + "";

        }
        return "";

    }
}

使用

add jar /root/jars/udf-1.0-SNAPSHOT-jar-with-dependencies.jar

create temporary function get_star as "com.mingzhi.GetStar"

drop temporary function if exists get_star

select get_star('RETURN_VISIT->AUDIT_ORDER:3|RETURN_VISIT:5|EVALUATE_ORDER:5') from dwd_order_basic