SQL語句 函數

標籤：group   子串   iter   ini   max   公司   div   加工   sqrt   

6.4 Functions 6.4.1 Numeric

• abs/mod 絕對值/求模
• sqrt/power/exp 求冪
• ceil/floor/round 取整
• trunc 截取小數點
• sin/cos/ln/log 數學函數

6.4.2 Charactor

• chr/ascii ascii碼
• length/lengthb 字元/位元組長度
• initcap/upper/lower 字母大小寫
• substr/translate/replace/instr 子串/替換
• ltrim/rtrim/lpad/rpad 修剪/填充

6.4.3 Datetime

select sysdate, systimestamp, current_date from dual;-- x 天之後的日期select sysdate + 365 from dual;-- x 個月後的日期select add_months(sysdate, -5) from dual;-- 相差幾個月select months_between(to_date(‘20180501‘), sysdate) from dual;-- 下周 x 的日期select next_day(sysdate, ‘星期一‘) from dual;select next_day(sysdate, 2) from dual; -- 1 代表星期天, 0 代表星期一, 類推-- 一個月的最後一天/第一天select last_day(sysdate) from dual;select trunc(sysdate,‘MONTH‘) from dual;select trunc(sysdate,‘YEAR‘) from dual;-- 擷取日期的單個欄位select extract(day from sysdate) from dual;-- 對日期進行截取select round(to_date(‘20170916‘),‘MONTH‘) from dual; -- 四捨五入select trunc(to_date(‘20170916‘),‘MONTH‘) from dual;

6.4.4 Nullable

• nvl/nvl2/nullif

6.4.5 Miscellaneous

decode/case when:

-- 產生資料create table sss (name varchar2(20), gender int);insert into sss values (‘櫻桃小丸子‘, 2);insert into sss values (‘路飛‘, 1);insert into sss values (‘大熊‘, 3);insert into sss values (‘光頭強‘, 1);commit;-- 查詢，將 gender 的數字轉化為男女-- 1. decode 函數的方式select name, decode(gender, 1, ‘雄性‘, 2, ‘雌性‘, ‘未知性別‘) "性別" from sss;-- 2. case when 的方式 select name, case gender when 1 then ‘雄性‘ when 2 then ‘雌性‘ else ‘未知性別‘ end "性別" from sss;-- 3. case when 的另一種方式 select name, case when gender <= 1 then ‘雄性‘ when gender >= 2 then ‘雌性‘ else ‘未知性別‘ end "性別" from sss;-- 給 emp 中的人加工資：---- 1000 元以下的，加 50%---- 2000 元以下的，加 30%---- 3000 元以下的，加 20%---- 其他人 5%create table emp3 as select * from emp;-- 如果要將所有的工資翻倍，這麼寫：update emp3 set sal = sal*2;-- 如果不同的人加不同的工資，需要用到 case when:update emp3 set sal = (case when sal <= 1000 then sal*1.5 when sal <= 2000 then sal*1.3 when sal <= 3000 then sal*1.2 else sal*1.05 end);-- 如果 1981/5/1 之前來公司的人按照上面調薪，其他人只調 1% 呢？-- [TODO]

6.4.6 Aggregate Function

彙總函式是用來統計每個分組的統計資訊，它們要跟 group by 一起使用，用來將每個分組所有資料 彙總 成一條統計資料。

包括 max/min/count/avg/sum 等。

-- 按照部門進行分組統計的語句select deptno,             -- 因為按照 deptno 分組，select 中只能有 deptno 欄位       count(*), -- 每個分組多少條資料 max(sal), -- 每個分組工資的最大值 min(sal), -- 每個分組工資的最小值 avg(sal), -- 每個分組工資的均值 sum(nvl(comm, 0)) -- 每個分組獎金總和，因為獎金可能為 null，所以需要使用 nvl 進行去空 from emp group by deptno; -- 分組依據-- 可以用 having 對結果進行過濾-- 整個 select 語句執行順序大致是: where -> group by -> having -> order byselect deptno, count(*), max(sal), min(sal), avg(sal), sum(comm) from emp group by deptno having avg(sal) > 2000;-- having 等價於嵌套的 where，即上面語句跟下面這條等效。select * from ( select deptno, count(*), max(sal), min(sal), avg(sal) asal, sum(comm) from emp group by deptno ) where asal > 2000;

6.4.7 Analytic Function

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174

彙總函式統計的是一個分組的資訊，彙總之後每個分組只能得到一條資訊，但並不能夠擷取分組內成員的具體資訊。

而分析函數可以詳細顯示分組內部的統計資訊，它顯示的資訊更全面，文法也更複雜。

大致來說，分析函數的使用文法是這樣的:

分析函數() over ([partition by xxx] order by yyy [rows/range ...])

解釋：

• 一個分析函數，需要跟著一個視窗函數。
• 以 over 開始的部分叫 視窗函數, 它描述了要對哪些資料、按照什麼方式進行分析的行為。
• 視窗函數內部分為三部分，其中 order by 是必須的，其它可選
• partition by 指定了資料按照什麼方式 分組/分區
• order by 指定了在分區之內，資料的排序方式
• rows/range 可以對分區的資料進行限定
• 分析函數有很多，比如 row_number/rank/dense_rank/max/count 等，row_number 等有如下區別

  ROW_NUMBER 返回連續的排位，不論值是否相等RANK 具有相等值的行排位相同，序數隨後跳躍DENSE_RANK 具有相等值的行排位相同，序號是連續的

• 需要查詢組本身的資訊，用彙總函式 group by; 需要查詢群組成員的統計資訊，需要用分析函數。

-- 最基本，視窗描述內只有 order byselect e.*, sum(sal) over (order by sal) from emp e;select e.*, row_number() over (order by sal) from emp e;-- 以組為單位，進行排序select e.*, sum(sal) over (partition by deptno order by sal) from emp e;select e.*, row_number() over (partition by deptno order by sal) from emp e;-- 比如，要查詢每個組的最高工資，可以用彙總函式select deptno, max(sal) from emp group by deptno;-- 但如果要查看工資最高的那個人，彙總函式無能為力，需要用到分析函數---- 1. 首先按組排序select e.*, row_number() over (partition by deptno order by sal desc) from emp e;---- 2. 過濾，只取排名第一的那個人，okselect * from (select e.*, row_number() over (partition by deptno order by sal desc) rn from emp e) where rn = 1;

SQL語句 函數