一、SQL中的array_agg函数介绍与示例代码

1.在MySQL数据库中,array_agg是一种非常有用的聚合函数,用于将一列数据聚合为一个数组。

2.array_agg函数的概述
array_agg函数将一列数据聚合为一个数组,并返回此数组。这一功能在某些情况下非常有用,例如当我们需要将某一列的数据合并为一个数组以便于处理时。在MySQL中,array_agg函数是通过使用GROUP BY语句结合GROUP_CONCAT函数来实现的。
同理在PostgreSQL(瀚高数据库)中可以使用array_agg函数来进行处理,而在人大金仓数据库中可以使用ARRAY_agg和group_concat可以达到同样的效果。

SELECT item_类别,ARRAY_agg(item_商品名称)  FROM TLK_物品名称管理 GROUP BY item_类别;

SELECT item_类别,group_concat(item_商品名称)  FROM TLK_物品名称管理 GROUP BY item_类别;

在这里插入图片描述
人大金仓数据库
在这里插入图片描述
PostgreSQL。

  1. array_agg函数的注意事项
    在使用array_agg函数时,需要注意以下几点:

array_agg函数只能在GROUP BY语句中使用,用于对数据进行分组聚合。
array_agg函数返回的数组中的元素顺序是不确定的。如果需要按照特定的顺序返回数组,可以使用ORDER BY子句对数据进行排序。
array_agg函数只能在MySQL 5.7及以上的版本中使用。如果你的MySQL版本较低,可以考虑升级到较新的版本。

二、常见的聚合函数

1.概念

聚合函数通常作用于一组数据,并对一组数据返回一个值。
2.常见的聚合函数的类型
AVG(),SUM(),MAX(),MIN(),COUNT()

  1. AVG(),SUM():数值

①查询员工表的平均工资以及员工表的总工资

select avg(salary) “avg”,sum(salary) “sum”
from employees
②对于字符串结果是0

  1. MAX(),MIN():数值,字符串,日期

①查询员工表的最高的工资和最低的工资

select max(salary) “max”, min(salary) “min”
from employees
②对于字符串来说,可以查找到首字母最大的名字,最小的名字

select max(last_name) “max”, min(last_name) “min”
from employees

  1. COUNT()

①作用:计算指定的字段的个数

②count(*),count(1),count(字段名)的区别?

count()和count(1)能查询表中有多少条记录。
count(字段名)查询该字段名下非空的记录。

SELECT count(
),count(1),count(commission_pct)
FROM employees

6.查询员工表的平均奖金率。

avg(commission_pct)=sum(commission_pct)/count(commission_pct) 只计算员工有奖金的,除以有奖金的总人数。所以avg(commission_pct)是错误的。应该除以总人数SUM(commission_pct)/count(*)正确

  1. count(*),count(1),count(非空字段名)哪个效率高。

①如果使用的MyISAM的存储引擎,三者效率相同都是O(1) 引擎内部有一计数器在维护着行数

②如果使用的InnoDB的存储引擎,三个效率count(*) = count(1) > count(非空字段名)

count(*),count(1)直接读行数,复杂度是O(n), innodb真的要去数一遍。但好于具体的count(列名)

三、类型转换函数

1、常见的类型转换函数有CONVERT ,cast(item_入库数量 as BIGINT),(item_入库数量::BIGINT).

四、其他函数

1、concat():字符串拼接
特别注意:NULL和任何的数据concat拼接,结果都是NULL;(经测试,瀚高数据库和人大金仓数据库,当拼接了null值时,会拼接到空值,其余有值的字段正常显示)。
字段拼接中间以’,‘间隔(经测试,瀚高数据库和人大金仓数据库中间没有’,'拼接时也可以正常执行)

select concat(dist_type,',',dist_jk_id,dist_name,dist_interdddress,dist_address) as list from distribute_logs
或者
select concat(dist_type,',',dist_jk_id,',',dist_name,',',dist_interdddress,',',dist_address) as list from distribute_logs

在这里插入图片描述

在这里插入图片描述

2.判断null值,并返回指定字符
瀚高数据库目前测试用case可以用。

select CASE when  dist_database is null then '0' else dist_database end from distribute_logs

联合使用:

select concat((***CASE when  dist_database is null then '' else dist_database end***),',',dist_name) as list from distribute_logs

ifnull(str1,str2):若str1为null,返回str2;否则,返回str1;—这个用法在MySQL中可以用。

#若commission_pct为null,返回0,否则返回commission_pct
SELECT IFNULL(commission_pct,0) as 奖金率;

根据上面两个函数,示例:拼接可能为null的数据字段值,应该使用ifnull()+concat()

#为了防止commission_pct为null,导致concat()拼接结果为null,所以使用ifnull()判断
SELECT CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0))as OUT_PUT
FROM employees;

4.特别提示:+号与concat()

#+号,在sql中,如果拼接字段是字符串数据,它会转换为数据类型,作算术运算;
#如果不能转换为数据,就为0;+号和concat()函数不同;+号虽然在java或python中可以拼接字符,
#但在sql中,是向算术倾向的;

select (dist_jk_id+log_id) as id from distribute_logs;

经测试,字符串类型的数字相加会报错;
在这里插入图片描述
5.is/is not null/=null
特别注意:=号不能判断一个值是null;需要使用:IS;但是注意,is、is not只能用来判断字段值null

select * from distribute_logs where dist_password=null

select * from distribute_logs where dist_password is null

select * from distribute_logs where dist_database is  not null

6.安全等于:<=>
特别注意:<=>这个符号可以判断任何字段值是否等于,包括上面的值为null的情况;

7.LENGTH(str):字符串长度;upper:转大写;lower:转小写;

8.substr(str,a,b):在str中从索引a开始,截取b个字符
特别注意:sql的索引从1开始

select LENGTH(dist_tabaddress), dist_tabaddress,SUBSTRING(UPPER(dist_tabaddress),2,24)  from distribute_logs where dist_database is  not null

9.instr函数,instr(str1,str2)
这是orcle的函数,在瀚高数据库中可以通过构建函数。(目前没有构建成功,等后续成功了在做补充)
参照此地址中的构建方法。

instr(str1,str2)
返回str2字符串在str1中的起始索引,如果没有就返回0

SELECT INSTR("杨不悔爱上了殷六侠","殷六侠") AS out_put;#返回:7

构建方法:

-- 实现1
  CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS
    $$ DECLARE pos integer;
  BEGIN
    pos := instr($1, $2, 1);
    RETURN pos;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;    
  
  -- 实现2                    
  CREATE FUNCTION instr(string           varchar,
                        string_to_search varchar,
                        beg_index        integer) RETURNS integer AS
    $$        DECLARE pos integer NOT NULL DEFAULT 0;
    temp_str  varchar;
    beg       integer;
    length    integer;
    ss_length integer;
  BEGIN
    IF beg_index > 0 THEN
      temp_str := substring(string FROM beg_index);
      pos      := position(string_to_search IN temp_str);
    
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN pos + beg_index - 1;
      END IF;
    ELSIF beg_index < 0 THEN
      ss_length := char_length(string_to_search);
      length    := char_length(string);
      beg       := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos      := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          RETURN beg;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    ELSE
      RETURN 0;
    END IF;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;
  
  -- 实现3
  CREATE FUNCTION instr(string           varchar,
                        string_to_search varchar,
                        beg_index        integer,
                        occur_index      integer) RETURNS integer AS
    $$           DECLARE pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str     varchar;
    beg          integer;
    i            integer;
    length       integer;
    ss_length    integer;
  BEGIN
    IF beg_index > 0 THEN
      beg      := beg_index;
      temp_str := substring(string FROM beg_index);
      FOR i IN 1 .. occur_index LOOP
        pos := position(string_to_search IN temp_str);
        IF i = 1 THEN
          beg := beg + pos - 1;
        ELSE
          beg := beg + pos;
        END IF;
        temp_str := substring(string FROM beg + 1);
      END LOOP;
    
      IF pos = 0 THEN
        RETURN 0;
      ELSE
        RETURN beg;
      END IF;
    ELSIF beg_index < 0 THEN
      ss_length := char_length(string_to_search);
      length    := char_length(string);
      beg       := length + beg_index - ss_length + 2;
      WHILE beg > 0 LOOP
        temp_str := substring(string FROM beg FOR ss_length);
        pos      := position(string_to_search IN temp_str);
        IF pos > 0 THEN
          occur_number := occur_number + 1;
          IF occur_number = occur_index THEN
            RETURN beg;
          END IF;
        END IF;
        beg := beg - 1;
      END LOOP;
      RETURN 0;
    ELSE
      RETURN 0;
    END IF;
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;

12.trim:去除前后空格或其它指定字符

select trim(dist_tabaddress) from distribute_logs

13.replace函数:
REPLACE(source, old_text, new_text );

REPLACE() 函数接受三个参数:

source 是一个你想要替换的字符串。

old_text 是您要搜索和替换的文本。 如果 old_text 在字符串中多次出现,它的所有出现都将被替换。

new_text 是将替换旧文本 (old_text) 的新文本。

select REPLACE(dist_tabaddress,'9999','44')  from distribute_logs;

14.lpad()函数

PostgreSQL中的lpad()函数有两个功能:
1,如果长度不够指定的长度,就在左边填充字符串,
2,如果长度超出了指定的长度,就把右边截掉。
语法格式
lpad(string,length[,fill_text])
示例:

select lpad(dist_tabaddress,45,'44')  from distribute_logs;

在这里插入图片描述

select lpad(dist_tabaddress,12)  from distribute_logs;

在这里插入图片描述
now()、curdate()、str_to_date()、date_format()、datediff
#日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期,不带时间
SELECT CURDATE();
#curtime 返回当前系统时间,不带日期
SELECT curtime();
#可以指定显示:年、月、日、小时、分钟、秒(year、month、day、hour、minute、second)
SELECT YEAR(NOW()) 年;
SELECT SECOND(NOW()) 秒;
#str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE(‘9-9-2020’,‘%d-%m-%Y’) as 年月日;
#date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),‘%Y年%m月%d日’) as 年月日;
#datediff:返回两个日期相差的天数
select DATEDIFF(‘2020-01-02’,‘2020-01-01’);#返回:1

17、流程控制函数:if、case
#if(表达式1,表达式2,表达式3):1成立返回2,否则返回3
SELECT if(10<5,“对”,“错”);
SELECT last_name,commission_pct,IF(commission_pct IS NULL,“没奖金,哈哈”,“有奖金,吃肉”)FROM employees;
#case
SELECT salary 合同工资, department_id,
CASE department_id
WHEN 30 THEN salary1.1
WHEN 40 THEN salary
1.2
WHEN 50 THEN salary*1.3
ELSE 0
END as 新工资
FROM employees;

18:聚合函数:sum、、max 、min 、count
特别注意:聚合函数都忽略null值,即:遇到null直接跳过;

#sum:求和、avg 平均值、max 最大值、min 最小值、count 计算个数
SELECT SUM(salary) from employees;
SELECT SUM(salary) 求和,avg(salary) 平均,MIN(salary) 最小,MAX(salary) 最大,COUNT(salary) 个数 FROM employees;
#注意:
#1.聚合函数中sum、avg一般用于处理数值型;max、min、count可以处理任何类型;
#2.NULL类型不参与聚合函数运算,即:在执行这几种方法时,null被忽略跳过;
#3.可以和关键字distinct搭配,实现去重的聚合
SELECT sum(DISTINCT salary),sum(salary) FROM employees; #可以看出加了distinct的要小很多
#count()函数最常用:下面两种均是统计表中所有行数:或常量
SELECT COUNT(
) FROM employees;
SELECT COUNT(1) from employees;#在count()参数添加常量

Logo

有“AI”的1024 = 2048,欢迎大家加入2048 AI社区

更多推荐