数据库高级操作（一）

1.COALESCE()函数

主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下：
COALESCE ( expression,value1,value2……,valuen)
COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。
如果expression不为空值则返回expression；否则判断value1是否是空值，
如果value1不为空值则返回value1；否则判断value2是否是空值，
如果value2不为空值则返回value2；……以此类推，
如果所有的表达式都为空值，则返回NULL。

2.NULLIF函数来处理空值

这个NULLIF函数是PostgreSQL提供的最常见的条件表达式之一。

用法：NULLIF(argument_1,argument_2);

这个NULLIF如果函数返回空值参数_1等于参数_2，否则返回参数_1。

3.lpad || rpad 函数来处理空值

select lpad(‘538’, 6, ‘0’) //解释：538不满6位数时，左变补0，让其达到6位长度
select rpad(‘538’, 6, ‘0’)//解释：538不满6位数时，右边补0，让其达到6位长度

4.substring

substring函数用于从字符串中提取子字符串。

用法： SUBSTRING ( string, start_position, length )

5.case when

(
case
when substr(starttime::varchar,0,5)=‘2020’ then ‘2020年’
when substr(starttime::varchar,0,5)=‘2019’ then ‘2019年’
when substr(starttime::varchar,0,5)=‘2018’ then ‘2018年’
when substr(starttime::varchar,0,5)=‘2017’ then ‘2017年’
when substr(starttime::varchar,0,5)=‘2016’ then ‘2016年’
when substr(starttime::varchar,0,5)=‘2015’ then ‘2015年’
when substr(starttime::varchar,0,5)=‘2014’ then ‘2014年’
when substr(starttime::varchar,0,5)=‘2013’ then ‘2013年’
when substr(starttime::varchar,0,5)=‘2012’ then ‘2012年’
when substr(starttime::varchar,0,5)=‘2011’ then ‘2011年’
when substr(starttime::varchar,0,5)=‘2010’ then ‘2010年’
when starttime is null then ‘其他年份’ end
) AS year

6.行转列

SELECT unnest(string_to_array(‘4513,4564,555’,‘,’)) as a1 ;
6.1 STRING_TO_ARRAY 该函数用于分割字符串至数组元素，请看语法
string_to_array(string, delimiter [, null string])

		SELECT string_to_array('xx~^~yy~^~zz', '~^~');       -- {xx,yy,zz}
		SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy'); -- {xx,,zz}	

6.2 unnest函数返回表

SELECT t as name
FROM unnest(string_to_array('john,smith,jones', ',')) AS t;      

name
john
smith
jones

6.3 LIMIT
LIMIT 1 取第一个

LIMIT 1 OFFSET 0 从第零个取1个
7.split_part 切割函数

text=“name.cn” split_part(text,’.’,1) 结果： name
text=“name.cn” split_part(text,’.’,2) 结果： cn
text=“name.cn.com” split_part(text,’.’,3) 结果： com

8.strpos()
strpos() 函数返回字符串在另一个字符串中第一次出现的位置。
如果没有找到该字符串，则返回 false。

9 row_number() 序号函数
SELECT row_number() over(order by regexp_split_to_table(‘1420,168,173,174,177,181,182’, ‘,’ )) as row_number,
1.order by ：会将数据排序标号
2.不需要排序 row_number() over(） 即可
※SELECT row_number() over(order by regexp_split_to_table(‘1420,168,173,174,177,181,182’, ‘,’ )) as row_number,
这样得方法不可以写成
SELECT row_number() over(regexp_split_to_table(‘1420,168,173,174,177,181,182’, ‘,’ )) as row_number 会报 ERROR: syntax error at or near “(” 语法错误。
改写成
SELECT ROW_NUMBER
( ) OVER ( ) AS ROW_NUMBER,*
FROM
( SELECT regexp_split_to_table( ‘1420,168,173,174,177,181,182’, ‘,’ ) )AS t