一.NULL值的處理——以FLUX字段為例
1.當遇到聚合時，可能為null值，可以用COALESCE(FLUX,0)將null值賦值為0
select URL,sum(COALESCE(FLUX,0)) as sf,sum(COALESCE(COUNT,0)) as sc from URLDAY
where DT>='20200510000000' and DT<='20200517000000'
group by URL
order by sf desc
limit 10
2.排序時悉患，如果有null值，倒序會排在數(shù)值前面泣港，此時可以將COALESCE(FLUX,0)放在order by 的之后，讓排序正常价匠。
select URL,sum(FLUX) as sf,sum(COUNT) as sc from URLDAY
where DT>='20200510000000' and DT<='20200517000000'
group by URL
order by COALESCE(sf,0) desc
limit 10
3.如果在where條件里過濾null值当纱，則需要FLUX is not null
select URL,sum(FLUX) as sf,sum(COUNT) as sc from URLDAY
where DT>='20200510000000' and DT<='20200517000000' and FLUX is not null
group by URL
order by sf desc
limit 10
二.查詢
1.打開sqlline
/usr/lib/phoenix-4.14.1/bin/sqlline.py 10.21.69.1,10.21.69.2,10.21.69.3:2181:/hbase98
sqlline
2.查看表信息
!describe CHANNEL5MINSPEED_2005
describe
3.退出sqlline
!exit
exit
三.重點函數(shù)
1.字符串相關(guān)
a.字符串截取函數(shù)
SUBSTR(字符串，起始位置踩窖，長度)
select SUBSTR(DT,1,10) from CHANNEL5MINSPEED_2005;
substr
b.去除空格
去除前后空格 TRIM(" a ")
去除最左空格 LTRIM(" b")
去除最右空格 RTRIM("b ")
c.填充字符
左邊填充：LPAD（字符串坡氯，字符數(shù)，填充字符）
不要舉一反三洋腮，沒有RPAD箫柳，hhhhh
select LPAD(DT,20,'0') from CHANNEL5MINSPEED_2005;
LPAD
d.字符長度
LENGTH（“aaaa”）
select LENGTH(DT) from CHANNEL5MINSPEED_2005;
LENGTH
e.正則匹配
正則截取
REGEXP_SUBSTR（字符串，正則表達式啥供，選取第幾個）悯恍。省略第三個參數(shù)時，默認取第一個
正則替換
REGEXP_REPLACE（字符串伙狐，正則表達式涮毫，替換字符或字符串）
正則分割
REGEXP_SPLIT(字符串, 分割符)
重點：當分割字符相連時，會出現(xiàn)null值贷屎。
select REGEXP_SPLIT(DT,'5') from CHANNEL5MINSPEED_2005;
select REGEXP_SPLIT(DT,'0') from CHANNEL5MINSPEED_2005;
REGEXP_SPLIT
