postgre sql 累计和 hive sql
sql功能
Hive
length ,split的用法
# 类似 hive split的函数,自己定义的
create or replace function ypl_str2Array(
in _originStr VARCHAR(800),
in _delimeter VARCHAR(10)
) RETURNS VARCHAR(800)[]
as $$
declare _strres varchar(800);
declare _cindex INTEGER;
declare _arrIndex INTEGER;
DECLARE _arr_str VARCHAR(800)[];
DECLARE _tmp_str varchar(800);
DECLARE _debugStr VARCHAR(400);
BEGIN
_arrIndex:=1;
_strres:='';
_cindex:=1;
if _delimeter is NULL or "character_length"(_debugStr)<1 THEN
return _arr_str;
end IF;
--_strres:=_strres||'原始字符串是:'||_originStr;
while _cindex<"length"(_originStr) loop
--_strres :=_strres||'【这个是什么?】'||split_part(_originStr, _delimeter, _arrIndex);
_tmp_str:=split_part(_originStr, _delimeter, _arrIndex);
if "character_length"(_tmp_str)<1 then
exit;
end if;
_arr_str:=_arr_str|| _tmp_str;
_arrIndex:=_arrIndex+1;
END loop;
return _arr_str;
end;
$$ LANGUAGE plpgsql volatile;
SELECT count(1)
FROM model_part.t_model_part_output where batch_no = '20200428'
and cardinality(ypl_str2Array(domain,'.')) >2 and limit 100;
SELECT count(1)
FROM model_part.t_model_part_output where batch_no = '20200428'
and cardinality(string_to_array(domain,'.')) >2 and limit 100;