一、最基礎(chǔ)（選數(shù)據(jù)）

1.怎么把數(shù)據(jù)從表里選出來？

-- 從table_1中選擇a這一列
select a from table_1

2.表連接

-- table_1中有id,age；table_2中有id,sex。想取出id,age,sex 三列信息
select table_1.id,age,sex
from table_1 
left join table_2
on table_1.id = table_2.id; 

在這里先介紹一下幾種join: （敲重點(diǎn)，很容易問的哦）

join :?join默認(rèn)是inner join，找出左右都可匹配的記錄

eft join:?左連接，以左表為準(zhǔn)，逐條去右表找可匹配字段，如果有多條會(huì)逐次列出，如果沒有找到則是NULL；

right join：右連接，以右表為準(zhǔn)，逐條去左表找可匹配字段，如果有多條會(huì)逐次列出，如果沒有找到則是NULL

full outer join:?全連接，包含兩個(gè)表的連接結(jié)果，如果左表缺失或者右表缺失的數(shù)據(jù)會(huì)填充NULL

每種join 都有on , on的是左表和右表中都有的字段。join 之前要確保關(guān)聯(lián)鍵是否去重，是不是刻意保留非去重結(jié)果。

3. 兩張表數(shù)據(jù)的字段一樣，想合并起來，怎么辦？

-- 不去重，合并兩張表的數(shù)據(jù)
select * from table_1 UNION ALL select * from table_2;

union和union all均基于列合并多張表的數(shù)據(jù)，所合并的列格式必須完全一致。union的過程中會(huì)去重并降低效率，union all直接追加數(shù)據(jù)。union前后是兩段select 語句而非結(jié)果集。

二.最常用

為方便大家理解每個(gè)函數(shù)的作用，先建一個(gè)表，后面以這個(gè)為示例。

1. 去重 distinct

-- 羅列不同的id
select distinct id from table_1

-- 統(tǒng)計(jì)不同的id的個(gè)數(shù)
select count(distinct id) from  table_1

-- 優(yōu)化版本的count distinct
select count(*) from(select distinct id from table_1) tb

distinct 會(huì)對(duì)結(jié)果集去重，對(duì)全部選擇字段進(jìn)行去重，并不能針對(duì)其中部分字段進(jìn)行去重。使用count distinct進(jìn)行去重統(tǒng)計(jì)會(huì)將reducer數(shù)量強(qiáng)制限定為1，而影響效率，因此適合改寫為子查詢。

2. 聚合函數(shù)和group by

-- 統(tǒng)計(jì)不同性別（F、M）中，不同的id個(gè)數(shù)
select count(distinct id) from table_1 group by sex

-- 其它的聚合函數(shù)例如：max/min/avg/sum
-- 統(tǒng)計(jì)最大/最小/平均年齡s
select  max(age), min(age),avg(age) from table_1 group by id

聚合函數(shù)幫助我們進(jìn)行基本的數(shù)據(jù)統(tǒng)計(jì)，例如計(jì)算最大值、最小值、平均值、總數(shù)、求和。

3. 篩選 where/having

-- 統(tǒng)計(jì)A公司的男女人數(shù)
select count(distinct id) from table_1 where company = 'A' group by sex;

-- 統(tǒng)計(jì)各公司的男性平均年齡，并且僅保留平均年齡30歲以上的公司
select company, avg(age) from table_1 where sex = 'M' group by company having avg(age)>30;

4. 排序 order by

-- 按年齡全局倒序排序取最年邁的10個(gè)人 
select id,age from table_1 order by age DESC limit?10

5. case when 條件函數(shù)

-- 收入?yún)^(qū)間分組 
select id,
(case when CAST(salary as float)<50000 Then '0-5萬'
when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10萬'
when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20萬'
when CAST(salary as float)>200000 then '20萬以上'
else NULL end from table_1;

case 函數(shù)的格式為（case when 條件1 then value1 else null end）,?其中else 可以省，但是end不可以省。

在這個(gè)例子里也穿插了一個(gè)CAST的用法，它常用于string/int/double型的轉(zhuǎn)換。

6. 字符串

1)concat( A, B...)返回將A和B按順序連接在一起的字符串，如：concat('foo', 'bar') 返回'foobar'。

select concat('www','.iteblog','.com');

2)split(str, regex)用于將string類型數(shù)據(jù)按regex提取，分隔后轉(zhuǎn)換為array。

-- 以","為分隔符分割字符串，并轉(zhuǎn)化為array
select split("1,2,3",",") as value_array from table_1;

-- 結(jié)合array index,將原始字符串分割為3列
select value_array[0],value_array[1],value_array[2] from (select  split("1,2,3",",")as value_array from table_1 ) t;

3)substr（str,0,len)?截取字符串從0位開始的長(zhǎng)度為len個(gè)字符。

select substr('abcde',3,2);

-- 得到cd

三.基礎(chǔ)進(jìn)階

1.row_number(）

-- 按照字段salary倒序編號(hào)
select *, row_number() over (order by salary desc) as row_num from table_1;

-- 按照字段deptid分組后再按照salary倒序編號(hào)
select *, row_number() over (partition by deptid order by salary desc) as rank from table_1;

按照depid分組，對(duì)salary進(jìn)行排序（倒序）

除了row_number函數(shù)之外，還有兩個(gè)分組排序函數(shù)，分別是rank() 和dense_rank()。

rank()排序相同時(shí)會(huì)重復(fù)，總數(shù)不會(huì)變 ，意思是會(huì)出現(xiàn)1、1、3這樣的排序結(jié)果；

dense_rank()?排序相同時(shí)會(huì)重復(fù)，總數(shù)會(huì)減少，意思是會(huì)出現(xiàn)1、1、2這樣的排序結(jié)果。

row_number() 則在排序相同時(shí)不重復(fù)，會(huì)根據(jù)順序排序。

2.percentile 百分位函數(shù)

-- 獲取income字段的top10%的閾值
select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;

-- 獲取income字段的10個(gè)百分位點(diǎn)
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles from table_1;

3.時(shí)間函數(shù)

-- 轉(zhuǎn)換為時(shí)間數(shù)據(jù)的格式
select to_date("1970-01-01 00:00:00") as start_time from table_1;

-- 計(jì)算數(shù)據(jù)到當(dāng)前時(shí)間的天數(shù)差 
select datediff('2016-12-30','2016-12-29');

-- 得到 "1"

to_date函數(shù)可以把時(shí)間的字符串形式轉(zhuǎn)化為時(shí)間類型，再進(jìn)行后續(xù)的計(jì)算。

常用的日期提取函數(shù)包括：

• year()/month()/day()/hour()/minute()/second()
• 日期運(yùn)算函數(shù)包括datediff(enddate,stratdate) 計(jì)算兩個(gè)時(shí)間的時(shí)間差（day)
• date_sub(stratdate,days) 返回開始日期startdate減少days天后的日期
• date_add(startdate,days) 返回開始日期startdate增加days天后的日期