工作中用到的SQL--持续更新

如无特殊说明，均是 oracleDB

建表

建表时加主键及分区格式

CREATE TABLE schemaname.tablename
(
  字段名 数据类型,
  "INTERFACE_TIME" DATE DEFAULT SYSDATE,
 CONSTRAINT 主键名 PRIMARY KEY ("字段名","字段名 ")
ENABLE VALIDATE)
PARTITION BY RANGE (字段名) (
PARTITION PART_202403 VALUES LESS THAN (202404010000000000) TABLESPACE xxx,
PARTITION PART_202404 VALUES LESS THAN (202405010000000000) TABLESPACE xxx)

提取指定位置字符串

可以用substr()、instr()结合使用来提取，应该也可以用正则表达式来提取，但还不会
eg:分隔符为-,提取第3、4个“-”中间的字符串
可以这么写：
${text}参数示例：1-r-0po-one-456-wq-!

select  substr( '${text}' ,
instr( '${text}' , '-' ,1,3)+1,
instr( '${text}' , '-' ,instr( '${text}' , '-',1,3 )+1)-instr( '${text}' , '-',1,3 )-1)  AS test
from  dual

或者

select  substr( '${text}' ,
instr( '${text}' , '-' ,1,3)+1,
instr( '${text}' , '-' ,instr( '${text}' , '-',1,4 ))-instr( '${text}' , '-',1,3 )-1)  AS test
from  dual

时间函数相关

时间格式

查特定时间点的时候，如果该字段是date型的，若我们直接写where date型的列 = '2023-09-19 13:19:19'，显然会报错ORA-01861: 文字与格式字符串不匹配，需要把时间这一字符串利用 to_date() 函数转换格式。
如'yyyy-mm-dd hh24:mi:ss'，oracle不区分大小写，分钟是mi，误写成mm会报错ORA-01810: 格式代码出现两次。

计算两Date类型时间差（到秒）

两时间字段形如下图，求OUT到IN之间的时间间隔，具体到秒
两字段直接相减即可

SELECT 
(a.OUT_EVENT_TIME - a.IN_EVENT_TIME)*24*3600 AS TIME_INTERVAL
FROM TABLE a

字符varchar表示的时间比较

利用to_char()函数及trunc()函数：

>= TO_CHAR(TRUNC(SYSDATE,'MM')-INTERVAL'1'MONTH,'YYYYMMDD') 

trunc函数

trunc函数语法如下：TRUNC（date,[fmt]）,date 为必要参数，是输入的一个日期值，[fmt]参数可忽略，默认按天截断。

[fmt]参数	含义
day	返回当前星期的第一天
dd	返回当天零时
mm或month	当月第一天

例如：注意截断的时间是按月还是按天，这将影响后续减前一个月的时候，时间是从上月1号开始还是上月的当天号数开始。

发现oracle里星期的第一天是按周天开始算的嘿

从当前时间追溯前一段时间

追溯前一百天：
使用减时间间隔为100的方式报错SQL 错误 [1873] [22008]: ORA-01873: 间隔的前导精度太小

SELECT trunc(trunc(sysdate,'dd')- INTERVAL '100' day,'yyyymmdd') FROM dual

直接当前时间减100没报错，暂时不知道为什么。。。

SELECT SYSDATE,SYSDATE-100 FROM dual

从日期中提取第几月/日

extract函数

select extract(year from sysdate) YEAR	,extract(month from sysdate) MONTH	,extract(day from sysdate) day from dual;

select to_char(to_date(‘2018-05-11’,‘yyyy-mm-dd’),‘DAY’) from dual;

通配符

postgres里_、%，必要时配合转义字符\ 使用

查询每天特定时间段内的数据

postgres SQL 里可以这么写：
通过to_char()截取时、分信息，再写范围，注意时间格式HH大写，分钟MI

select * from ${table_name} where to_char(historytime,'HH24:MI') > '09:55' and to_char(historytime,'HH24:MI') < '10:05'

一些SQL优化原则

最常见的需要优化的SQL特征：

• 在字段开头使用模糊查询，导致索引失效，如 username LIKE '%陈%'。
• 使用 in 和 not in 操作符，导致全表扫描，如 id IN (2,3)。
• 使用 or 操作符，导致索引失效，如 id = 1 OR id = 3。
• 进行 null 值的判断，导致索引失效，如 score IS NULL。
• 在 where 条件中使用表达式或函数，导致索引失效，如 score/10 = 9。
• 使用 <> 或 != 操作符，导致索引失效，如 score <> 100。
• 在复合索引中没有使用前置列，导致索引失效，如 dept_code = '001'。
• 使用 select *，返回不必要的字段，增加网络IO，如 SELECT * FROM t。
• 使用 where 1 = 1，导致全表扫描，如 SELECT username, age, sex FROM t WHERE 1=1。
• 使用 order by 和 group by，导致排序和分组操作，增加CPU开销，如 SELECT * FROM t ORDER BY score。
• 尽量避免使用 select *，返回不必要的字段，增加网络IO¹²。应该只返回需要的字段，或者使用视图来封装查询语句。
• 尽量避免使用 where 1=1，导致全表扫描。应该用代码拼装SQL时进行判断，没有where条件就去掉where，有where条件就加and。
• 尽量避免使用 order by 和 group by，导致排序和分组操作，增加CPU开销。应该尽量在索引列上进行排序和分组，或者使用文件排序。
• 尽量避免使用 union，会导致数据去重，增加CPU开销。应该尽量使用 union all，不进行数据去重，或者使用 exists 代替 in。
• 尽量避免使用子查询，会导致多次查询，增加数据库负担。应该尽量使用关联查询，或者将子查询结果存入临时表。
• 尽量避免使用 having，会导致对分组后的数据进行筛选，增加数据库负担。应该尽量将筛选条件放在 where 子句中，或者使用 exists 代替 in。
• 尽量避免使用 limit，会导致对数据进行截取，增加数据库负担。应该尽量在 where 子句中使用条件限制数据量，或者使用分页查询。
• 尽量避免使用 distinct，会导致对数据进行去重，增加数据库负担。应该尽量在数据源端保证数据的唯一性，或者使用 group by 代替 distinct。

数据插入

从现有数据表中插入

INSERT INTO ${Destination_Table} SELECT * FROM ${Source_Table} 
WHERE condtions

插入数据的来源

手动删除了的数据

立刻马上利用快照查当时的数据，可以选择导出成csv、txt等格式文件，或者建一张临时存储之前的数据
快照

select * from ${table_name} as of timestamp (systimestamp - interval '10' minute)

亲测24小时以上的话数据库快照没了，可能跟数据库的数据量、备份策略有关系吧

1. 导出文件：根据DB工具的提示执行导出操作
2. 建一张临时表：

数据删除

DELETE FROM ${Table_Name} WHERE conditions