oracle数据库sql功能记录

目录

前言

一.row_number() 分组重新编号

二.实体化视图

   ​编辑

三.connect by的替代优化

前言

oracle的一些知识点

---

一.row_number() 分组重新编号

第一种写法：row_number() over(partition by 一个或多个分组列 order by 一个或多个排序列 asc/desc) as 别名  //如果不写asc/desc的话，默认为asc 
第二种写法：row_number() over(distribute by 一个或多个分组列 sort by 一个或多个排序列 asc/desc) as 别名

作用：将数据根据指定列分组进行重新编号，比如一个数据中有两组A组有3行数据，B组有4行数据，A组编成1，2，3;B组编成1，2，3，4

二.实体化视图

   1.创建 在oracle12c安装自带的sql developer

    

  2.创建完成遇到了commit了的新的数据一直没有过来，原因：在创建时需要设置刷新的时间

   

三.connect by的替代优化

如上，需要将equipcode用，分割，另外起一行

SELECT
    REGEXP_SUBSTR( equipcode, '[^,]+', 1, LEVEL, 'c' ) AS STR 
FROM
    tablename CONNECT BY LEVEL <= ( regexp_count ( equipcode, ',' ) + 1 )

如上可以分割，但是如果你想处理分割后的数据（比如外层套一个差count(1)或者group by 或者distinct都会很吃力），且你的数据在百万级别，那你数据库的io就会很吃力，

代替的方法：首先在你的oracle数据库里创建一个类型，跟一个函数

CREATE OR REPLACE TYPE str_split AS TABLE OF VARCHAR2(4000)；
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
   RETURN str_split
   PIPELINED
AS
   v_length   NUMBER := LENGTH(p_string);
   v_start    NUMBER := 1;
   v_index    NUMBER;
BEGIN
   WHILE(v_start <= v_length)
   LOOP
       v_index := INSTR(p_string, p_delimiter, v_start);
       IF v_index = 0
       THEN
           PIPE ROW(SUBSTR(p_string, v_start));
           v_start := v_length + 1;
       ELSE
           PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
           v_start := v_index + 1;
       END IF;
   END LOOP;
   RETURN;
END splitstr;

 然后如下通过table调用splitstr行数

select * from tablename a, table(splitstr(equipcode,',')) t1 where creationtime>=to_date('2024-07-01 00:00:00','yyyy-MM-dd hh24:mi:ss') 
and creationtime<=to_date('2024-08-31 00:00:00','yyyy-MM-dd hh24:mi:ss') and equipcode like '%,%'

效果如下，速度提高巨大