常用的sql命令
标题查看有哪些存储过程:
SHOW PLSQL FUNCTIONS;
我知道在hdfs上存放jar包的位置,如何查找这个jar对应的udf
select * from system.permanent_udfs_v where resource like '%xxxx%';
#查找某张表 匹配某张表
show tables like "f04*";
查找表的所在的数据库:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5iNwIjNzYjZzQWNxADZmRDZyYzX5AjNyQTM4EzLcdDMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
#查找某张表某字段空值占比
select
cust_id is null,
count(*)
from
b.a
group by
cust_id is null;
日期函数:
--根据日期增量判定,求取日期区间
select date_sub(to_date(sysdate),100) from system.dual;
select date_sub(to_date(sysdate),1) from system.dual;
--获取系统时间
select sysdate from system.dual;
--获取当月日期
select dayofmonth(sysdate) from system.dual;
--自动获取上个月最后一天
select last_day(add_months(sysdate,-1)) from system.dual;
--时间格式转换
select to_char('2019-11-01','yyyymm') from system.dual;
字符串操作
--截取前两个字符串
select substr(12141451,1,2) from system.dual;
select substring(12141451,1,2) from system.dual;
窗口函数
--窗口函数:
SELECT
empno,
WORKDEPT,
SALARY,
Row_Number() OVER (partition by workdept ORDER BY salary desc) rank
FROM
employee
--表示例
-- empno WORKDEPT SALARY
-- 000010 A00 152750 1
-- 000110 A00 66500 2
-- 000120 A00 49250 3
-- 200010 A00 46500 4
-- 200120 A00 39250 5
-- 000020 B01 94250 1
-- 000030 C01 98250 1
-- 000130 C01 73800 2
判断语句
--输出 2
select
if(true,2,3)
from
system.dual;
--输出 3
select
if(false,2,3)
from
system.dual;