常用的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;