一、實驗概述
大資料計算服務（MaxCompute，原名 ODPS）是一種快速、完全托管的 GB/TB/PB 級資料倉庫解決方案。MaxCompute 向使用者提供了完善的資料導入方案以及多種經典的分布式計算模型，能夠更快速的解決使用者海量資料計算問題，有效降低企業成本，并保障資料安全。
本實驗結合實際資料和案例，深入淺出的示範了如何使用MaxCompute的内置函數。
二、實驗目标
本實驗通過用戶端方式進行實驗，掌握MaxCompute的内置函數的使用技巧，以及相關注意事項。
完成此實驗後，可以掌握的内置函數有：
1. 數值類函數；
2. 字元串類函數；
3. 日期類函數；
4. 視窗函數；
5. 聚合函數；
6. 其他函數； 三、學習建議
1. 掌握MaxCompute的内置函數的使用技巧以及相關指令的基礎操作；
2. 提前安裝 ODPS用戶端（下載下傳用戶端軟體）。 第 1 章：實驗簡介
1.1 實驗簡介
一、實驗概述
大資料計算服務（MaxCompute，原名 ODPS）是一種快速、完全托管的 GB/TB/PB 級資料倉庫解決方案。MaxCompute 向使用者提供了完善的資料導入方案以及多種經典的分布式計算模型，能夠更快速的解決使用者海量資料計算問題，有效降低企業成本，并保障資料安全。
本實驗結合實際資料和案例，深入淺出的示範了如何使用MaxCompute的内置函數。
二、實驗目标：
本實驗通過用戶端方式進行實驗，掌握MaxCompute的内置函數的使用技巧，以及相關注意事項。
完成此實驗後，可以掌握的内置函數有：
1. 數值類函數；
2. 字元串類函數；
3. 日期類函數；
4. 視窗函數；
5. 聚合函數；
6. 其他函數； 三、學習建議：
1. 掌握MaxCompute的内置函數的使用技巧以及相關指令的基礎操作；
2. 提前安裝 ODPS用戶端（下載下傳用戶端軟體）。 第 2 章：實驗準備
2.1 申請MaxCompute資源
在彈出的左側欄中，點選 建立資源 按鈕，開始建立實驗資源。
資源建立過程需要1-3分鐘。完成實驗資源的建立後，使用者可以通過 實驗資源 檢視實驗中所需的資源資訊，例如：阿裡雲賬号等。
2.2 進入實驗環境
1 、申請MaxCompute資源
登入雲中沙箱，在實驗目錄中查詢所需實驗，進入實驗，點選“實驗資源”，檢視所需具體資源
點選“建立資源”，即可進入實驗環境。（由于實驗環境一旦開始建立則進入計時階段，建議學員先基本了解實驗具體的步驟、目的，真正開始做實驗時再進行建立）
建立資源需要幾分鐘時間，請耐心等候……
企業别名：即主賬号ID；
子使用者名稱和子使用者密碼：登入實驗環境以及配置MaxCompute 資料源時需要；
AK ID和AK Secret：系統為本使用者配置設定的登入驗證密鑰資訊，在配置用戶端及資料源時應用；
控制台url：登入實驗環境的位址；
2 、進入實驗環境
步驟一：登入管理控制台
資源開通後，點選左側菜單欄中“控制台url”連結，進入使用者登入頁面。
輸入資源提供的“子使用者名稱”和“子使用者密碼”登入。
登陸成功後，進入管理控制台頁面：
步驟二：打開“DataWorks”
展開左側菜單，點選“産品與服務”，在“大資料（數加）”産品清單中，點選“DataWorks”。
步驟三：建立項目
建立項目需要管理者權限，沙箱實驗環境預設建立完項目：
步驟四：進入資料開發
選中項目，點選“進入資料開發”，則進入“資料開發”環境。
資料開發界面如下：
2.3 配置odpscmd用戶端
步驟1：用戶端媒體下載下傳 （本實驗在附件中提供）
步驟2：解壓odpscmd_public.zip 到本目錄，如：解壓至本地目錄 F:\ODPS_DEMO
步驟3：檢視本次實驗課用到的媒體，可以看到如下的檔案夾：
bin/ conf/ lib/ plugins/
步驟4：在conf檔案夾中有odps_config.ini檔案。編輯此檔案，填寫相關資訊：
project_name=<項目名稱>
access_id=< AK ID>
access_key=
end_point=http://service.odps.aliyun.com/api (預設)
tunnel_endpoint=http://dt.odps.aliyun.com (預設)
log_view_host=http://logview.odps.aliyun.com (預設)
https_check=true （預設）
步驟5：修改好配置檔案後運作bin目錄下的odpscmd(在Linux系統下是./bin/odpscmd，Windows下運作./bin/odpscmd.bat)，現在可以運作 MaxCompute 指令，如：
注意：項目可以随時根據情況切換，上圖表示環境設定成功.
2.4 測試表dual準備
1、展開左側菜單，點選“臨時查詢”，然後點選建立“ODPS SQL”。
2、在彈出對話框中，輸入“節點名稱”，選擇“目标檔案夾”，點選“送出”。
進入腳本編輯頁面，進行腳本開發，建立實驗測試表dual，點選運作
CREATE TABLE dual (id BIGINT) LIFECYCLE 10000;
測試表建立成功
往測試表裡插入一條資料，輸入sql, 選中後點選“運作”。insertinto table dual select count(1) fromdual;
檢視測試表資料内容，輸入sql, 選中後點選“運作”。
select * from dual limit 10;
2.5 測試表t_dml準備
進入腳本編輯頁面，進行腳本開發，建立實驗測試表t_dml，點選運作。
CREATE TABLE t_dml(detail_id BIGINT,sale_date datetime,province STRING,city STRING,product_id BIGINT,cnt BIGINT,amt DOUBL);
進入odpscmd互動界面
加載資料 t_dml.csv:tunnel upload f:\data\t_dml.csv t_dml;
檢視資料表資料select* from t_dml limit 10;
2.6 測試表t_product準備
進入腳本編輯頁面，進行腳本開發，建立實驗測試表t_product，點選運作。
CREATE TABLE t_product (product_id BIGINT,product_name STRING,category_id BIGINT,category_name STRING,price DOUBLE);
進入odpscmd互動界面
加載資料 t_product.csv:tunnel upload f:\data\t_product.csv t_product;
檢視資料表資料select* from t_product limit 10;
2.7 測試表t_sign準備
進入腳本編輯頁面，進行腳本開發，建立實驗測試表t_sign，點選運作
CREATE TABLE t_sign (id BIGINT,name STRING,height DOUBLE,is_female BOOLEAN,birth_day datetime);
進入odpscmd互動界面
加載資料 t_sign.csv:tunnel upload f:\data\t_sign.csv t_sign;
檢視資料表資料select* from t_sign limit 10;
第 2 章：實驗詳情
2.1 數值類函數
(1) 三角函數類
已知三角形兩邊長度為10,20，夾角為60度，求三角形面積
select 0.51020sin(60/1803.1415926) from dual;
輸入sql腳本，點選【運作】，檢視結果
(2) 數字整形類：
對數字進行加工處理，請分别顯示數字 3.1415926 的向上取整值、向下取整值、四舍五入保留3位小數的值、截掉小數位的值以及用二進制來表示該值。
Select ceil(3.1415926),
floor(3.1415926),
round(3.1415926,3),
trunc(3.1415926),
conv(‘3.1415926’,10,2)
from dual;
輸入腳本，點選【運作】，檢視結果
(3) 随機函數類：
select rand() from dual;
select rand(detail_id),rand() from t_dml limit 10;
輸入sql腳本，點選【運作】，檢視結果
(4) 綜合使用
使用蒙特卡洛法求π值的近似值:産生一系列的成對的随機數，根據每隊随機數到點(0.5,0.5)的距離可判斷該點是否在機關圓内，計算落在圓内的點占所有點的比例，即可得到π值的近似值：
// 産生約10萬對随機點進行近似值計算：
select (inCircle/totalCnt)/pow(0.5,2) as PI
from (select count(*) as totalCnt,
sum(case when sqrt(pow((x-0.5),2)+pow((y-0.5),2)) <0.5 then 1 else 0 end) inCircle
from (select /*+mapjoin(t2)*/ rand() as x,rand() as y
from (select * from t_dml limit 10000) t1
left outer join (select * from t_dml limit 10) t2
on t1.detail_id <> t2.detail_id) tt
) t;
// 産生約100萬對随機點進行近似值計算：
select (inCircle/totalCnt)/pow(0.5,2) as PI
from (select count(*) as totalCnt,
sum(case when sqrt(pow((x-0.5),2)+pow((y-0.5),2)) <0.5 then 1 else 0 end) inCircle
from (select /*+mapjoin(t2)*/ rand() as x,rand() as y
from (select * from t_dml limit 10000) t1
left outer join (select * from t_dml limit 100) t2
on t1.detail_id <> t2.detail_id) tt
) t;
輸入腳本，點選【運作】，檢視結果：
輸入腳本，點選【運作】，檢視結果：
2.2 字元串類函數
(1) 長度類：
輸入腳本，點選【運作】，檢視結果
select province,length(province),lengthb(province) from t_dml limit 10;
(2) 查找類：
目前銷售記錄中，哪些省、市名字比較接近？
select province, city, char_matchcount(province, city) as sim
from (select distinct province, city
from t_dml) t order by sim desc
limit 10;
輸入腳本，點選【運作】，檢視結果
目前銷售記錄中，省份的第一個字在城市名中是否出現？有沒有出現多次的？
select province, city,
instr(city,substr(province,1,3),1,1) as FirstPos,
case when instr(city,substr(province,1,3),1,2) = 0 then ‘No’
else ‘Yes’
end as SecondPos
from (select distinct province, city
from t_dml) t order by SecondPos desc, FirstPos desc
limit 10;
輸入腳本，點選【運作】，檢視結果
(3) 轉換類：
要把資料從一個編碼為 utf8 的庫導入到一個字元集為 gb2132 的庫中，其中有些繁體字，如“阿裏雲”等字樣，請問會出現亂碼的情況嗎?
select is_encoding(‘阿裏雲’, ‘utf-8’, ‘gb2312’) from dual;
輸入腳本，點選【運作】，檢視結果
(4) 整形類：
select concat(province, ‘|’,city) from t_dml limit 10;
select category_name, tolower(split_part(category_name,' ',2)) from t_product;
輸入腳本，點選【運作】，檢視結果
輸入腳本，點選【運作】，檢視結果
2.3 日期類函數
(1) 日期擷取：
//根據日期，截取部分資訊
select dt,
datepart(dt, ‘yyyy’) as year,
datepart(dt, 'mm') as month,
datepart(dt, 'dd') as day,
datepart(dt, 'hh') as hour,
datepart(dt, 'mi') as minute, datepart(dt, ‘ss’) as second
from (select getdate() dt from dual) t;
輸入腳本，點選【運作】，檢視結果
// 日期截取
select datetrunc(‘2015-01-31 02:30:45’, ‘dd’) from dual;
輸入腳本，點選【運作】，檢視結果
// 獲得具體日期
select getdate(),lastday(getdate()),weekday(getdate()),weekofyear(getdate())
from dual; 輸入腳本，點選【運作】，檢視結果
(2) 日期轉換：
//字元串轉成日期, 日期轉換成字元串
select to_date(‘20150131’,‘yyyymmdd’),
to_char(‘2015-01-31 00:00:00’, ‘日期：yyyymmdd’)
from dual;
輸入腳本，點選【運作】，檢視結果
// Unix時間和ODPS時間互轉
select from_unixtime(1), unix_timestamp(‘2015-10-01 00:00:00’) from dual;
輸入腳本，點選【運作】，檢視結果
// 判斷字元串是否滿足預定義的日期格式
select sale_date, isdate(sale_date, ‘yyyymmdd’) from t_dml limit 10;
輸入腳本，點選【運作】，檢視結果
(3) 日期運算：
統計5月1日從産品5第一次成交後一小時三十分鐘内（含），産品5銷量（含第一次成交）占同期總銷量的比例：
select /+mapjoin(t2)/
sum(case when product_id=5 then cnt else 0 end)/sum(cnt)
from t_dml t1
join (select min(sale_date) as begin_dt,
dateadd(dateadd(min(sale_date),1,'hh'),30, 'mi') as end_dt
from t_dml
where product_id=5
and datetrunc(sale_date,'dd')='2015-05-01 00:00:00')t2
on t1.sale_date >= t2.begin_dt
and t1.sale_date <= t2.end_dt; 輸入腳本，點選【運作】，檢視結果
日期相減：
select max(sale_date), min(sale_date),
datediff(max(sale_date),min(sale_date),‘dd’)
from t_dml;
輸入腳本，點選【運作】，檢視結果
第 3 章：實驗總結
3.1 實驗總結
MaxCompute的這幾類函數基本覆寫了我們日常工作的絕大多數資料處理需求，通過靈活熟練的使用這些函數，
可以提升開發效率，若仍有無法滿足的需求，還可以考慮自定義函數。
第 4 章：課後任務
4.1 課後任務
1、計算t_dml表中最大的日期距離2017-08-01的天數