hive 截取函数 hive取字段长度

获取字符串长度：length(字段)

字符串转数组：collect_list 列转行可得到数据，或split()某个字段得到数组

获取数组长度：size(数组字段)

参考sql： 

set edate=2019-07-21
SET company=***;
SET recomrecords=ods.ods_${hiveconf:company_RecomRecords;
SET employees=ods.ods_${hiveconf:company}_base_employees;
SET branchs=ods.ods_${hiveconf:company}_base_branchs;
SET regions=ods.ods_${hiveconf:company}_base_regions;

SELECT
    EmployeeNo,
    collect_list(FillinDate)                                   FillinDate,
    collect_list(FillinDate)[size(collect_list(FillinDate))-1] FillinDate,//取数组最后一个值
    size(collect_list(FillinDate))
FROM
    ${hiveconf:recomrecords}
WHERE
    EmployeeNo='401800106'
AND FillinDate>='2019-07-15'
AND FillinDate<='2019-07-21'
AND PhaseId=10
AND IsApprove=1
AND IsCancel IS NULL
GROUP BY
    EmployeeNo
ORDER BY
    fillindate DESC