【数据库】常用SQL语句

常用SQL语句汇总

一.时间相关函数

1、 '20220401' 转换成 '2022-04-01'

date(to_date('20220401','yyyymmdd'))

2、 两个timestamp 时间相减

datediff(next_start_time,start_time,'ss')

3、 dateadd 函数

+2 两天后；-2减两天

date(dateadd(to_date('${bizdate}','yyyymmdd'),2,'day'))

4、上月末最后一天

dateadd(datetrunc(date(to_date('${bizdate}','yyyymmdd')),'mm'),-1,'dd')

5、本月第一天

datetrunc(date(to_date('${bizdate}','yyyymmdd')),'mm')

6、本月最后一天

last_day(to_date('${bizdate}','yyyymmdd'))

7、昨天

date(dateadd(current_date(),-1,'day'))

二.字符串相关函数

1、11位以上连续数据换成*

regexp_replace(bc_content ,'\\d{11,}' ,'*')

2、ds分区昨日

replace(date(dateadd(current_date(),-1,'day')),'-','')

3、合并字段

concat('001','002')

4、uid首位以d\e\f开头

substr(b.uid,1,1) in ("d","e","f")

5、以指定符号分隔字符串

select split_part('a,b,c,d', ',', 1)

----->返回a

6、查看字符串长度

length(t1.cus_no)=11

三.数值处理

1、四舍五入

round(12.12345,4)

2、数据相除（排除分母为0）

select ISNULL((a / NULLIF(b,0)) ,0)

四.其他常用函数

1、 空值转换函数

如果a为Null，则返回default_value；如果a不为Null，则返回a

nvl (a,default_value)

2、 LEAD/LAG OVER(PARTITION BY ORDER BY)

可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。

SELECT A.phone_circuit_prefix,      
 A.call_time,       
lead(A.call_time,1) over (PARTITION by A.phone_circuit_prefix order by A.call_time) test
from A

示例结果：

3、 sum() over ( partiton by ... order by...)

4、 COALESCE

返回<expr1>, <expr2>, ...中第一个非NULL的值

coalesce(<expr1>, <expr2>, ...)

coalesce(call_time,created_at)

5、 nullif

如果两个参数相等，则返回空值；

如果两个参数都不相等，则返回第一个参数的值。

五.工作Q汇总

1、比率计算结果0时，可以通过转换数据类型为double得到结果

cast(sum(temp.jtcs) as double) / cast(sum(temp.bdcs) as double)

2、行列转换

行转列：

SELECT  id, 
        name, 
        SUM(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度', 
        SUM(CASE WHEN quarter=2 THEN number ELSE 0 END) '二季度', 
        SUM(CASE WHEN quarter=3 THEN number ELSE 0 END) '三季度',
        SUM(CASE WHEN quarter=4 THEN number ELSE 0 END) '四季度'
FROM testGROUP BY id,name

如果是不需要进行聚合的数据，可以用：

max(CASE WHEN quarter=1 THEN number ELSE 0 END) '一季度'

TRANS_ARRAY:

将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。

3、查询表内有相同记录的数据

select * from HR.Employees where title in 
    (select title from HR.Employees
    group by title
    having count(1)>1
    )

4、不支持COUNT(DISTINCT ) OVER (PARTITION BY )时

1） count(distinct ) over(partition by order by) 替换成 size(collect_set() over(partition by order by)) 来实现, 含义为求分组后的去重个数

注：可能效率需要优化

2） 可使用笛卡尔积

5、By月每一天累计值

select fnl.bizdate
       ,fnl.queue_name
       ,fnl.month_acc_total_pho_cnt
       ,fnl.month_acc_get_pho_cnt
from
    (select temp.bizdate,
             temp.queue_name,
             sum(temp.call_id_new) over (partition by temp.queue_name,substr(temp.bizdate,1,7) order by temp.bizdate) as     month_acc_total_pho_cnt,
            sum(temp.talk_duration ) over (partition by temp.queue_name,substr(temp.bizdate,1,7) order by temp.bizdate) as month_acc_get_pho_cnt
     from temp
     )fnl

6、TRUNCATE / DELETE/DROP区别

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行；如果要删除表定义及其数据，请使用 DROP TABLE 语句。

TRUNCATE TABLE [Table Name] ：

速度快,而且效率高；删除表中的所有行，但表结构及其列、约束、索引等保持不变；

由 FOREIGN KEY 约束引用的表\索引视图的表不可用

DELETE FROM [Table Name] WHERE 条件：

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项；如果想保留标识计数值，用DELETE语句。

7、连续两次通时小于10秒

select  temp3.uid
        ,IF(sum(temp3.talk_temp) > 0 ,'Y' ,'N')
from  ( select  temp2.uid            
        ,IF((temp2.talk_length1 < 10 and temp2.talk_length2 < 10),1,0) talk_temp
        from         
            (select  temp.uid
                    ,temp.talk_length as talk_length1
                    ,lead(temp.talk_length ,1 ,null) over ( partition by temp.uid order by  temp.created_at) as talk_length2
            from temp)
        )temp2
)temp3
         group by  temp3.uid

8、去除重复数据

select * from 
    (select A.*, 
            row_number() over( partition by name) 工资排名
     from A
    ) t 
where 工资排名=1;