greemplum的計算核心是postgresql，所有下面總結的幾點也適用於mysql遷移到postgresql

1、時間格式化

mysql: select DATE_FORMAT(now(),'%Y%m%d%H%i%s') =>20160720035809 獲取一個日期的年月日小時分鐘秒字串注：%H表示24小時制

GP： to_char(now(), 'YYYYMMDDHH24MISS') =>20160720155848獲取一個日期的年月日小時分鐘秒字串型別

to_date(now(), 'YYYYMMDD') =>20160720155848獲取一個日期的年月日小時分鐘秒日期型別

to_number(now(),'9999999999')=>20160702獲取一個日期的年月日數值型別

to_number(now(),'9999999999999999999')=>20160720155848獲取一個日期的年月日小時分鐘秒數值型別

注：HH24表示24小時制

數值型別轉換時間型別

mysql：select cast(20160501 as datetime)=>2016-05-01 00:00:00

select cast(20160501 as date)=>2016-05-01

GP： select to_timestamp('20160801','YYYYMMDDHH24MISS')=>2016-08-01 00:00:00+08

select 20160801::varchar::TIMESTAMP=>2016-08-01 00:00:00+08

select to_date('20160801','YYYYMMDDHH24MISS')=>2016-08-01

select 20160801::varchar::date=>2016-08-01

GP詳細時間函式可見http://blog.csdn.net/snn1410/article/details/7741283

獲取小時函式

mysql: select hour(now())=>16 獲取一個日期的小時

GP: select EXTRACT(HOUR from now()) =>16 獲取一個日期的小時

GP衍生：

select EXTRACT(day from now()) 日期

select EXTRACT(dow from now()) 每週的星期號

select EXTRACT(doy from now()) 一年的第幾天

select EXTRACT(month from now())月份

select EXTRACT(minute from now())分鐘

select EXTRACT(week from now())一年的第幾周

select EXTRACT(year from now())年份

時間計算

mysql :DATE_SUB(now(),INTERVAL 5 day) 5天前 DATE_SUB(now(),INTERVAL -5 day)5天后

GP：now()-INTERVAL '5 day' 5天前 now()+INTERVAL '5 day' 5天后

now()-INTERVAL '5 year' 5年前

now()-INTERVAL '5 month' 5個月前

now()-INTERVAL '5 min' 5分鐘前

注：上述的DATE_SUB函式中的5這個值可以是變數，比如case when a=1 then 5 else 0

但是如果GP要實現這樣的功能需要換一種寫法，比如select now()- (case when a=1 then '5' else '0' end || ' day')::INTERVAL實現當a=1的時候減5天，其他值則時間為當前時間

時間差

mysql：DATEDIFF(b.pay_time,c.value_date)

GP：（b.pay_time::date-c.value_date::date）

2、lfnull處理空字串

mysql: ifnull(null,0.00) =>0.00 欄位如果為空則為0.00

GP： COALESCE(null,0.00 )=>0.00 欄位如果為空則為0.00

注意：COALESCE中的兩個資料型別要一致COALESCE(1,'a')會報錯，mysql的ifnull則不會

3、字串拼接

mysql: SELECT concat('My','S','QL') =>MySQL

GP: SELECT 'My'||'S'||'QL' => MySQL

4、執行儲存過程
mysql: call proc_test();
GP: select proc_test();

5、substr和substring

substr(str from pos)和substring(str from pos)都可以使用

GP不能使用substr(str from pos) 只能substring(str from pos)

6、convert函式差異

mysql convert函式可用來強制轉換資料型別例：convert('20160801',date)=>2016-08-01

postgresql convert則是用來用轉換字符集型別 convert_to(string text, dest_encoding name)

7、GP不能將字元欄位改成數值型別

8、GP不能使用if函式

mysql:if(a=1,b=1,b=2)=>如果a=1則b=1否則b=2

GP: 只能用case when a=1 then b=1 else b=2 end實現，也可以自定義函式實現mysql的if函式功能

9、locate函式獲取某個字串在一個字串中的位置

mysql：locate（'a','bcad'）=>3

GP： position('a' in 'bcad' )=>3

10、create table

mysql:create table t1 select * from t2;

GP create table t1 as select * from t2; GP必須加上as

11、聯合更新

mysql:update CDM.cdm_product a INNER JOIN dim_product_tmp b on a.id = b.id

set a.enddate = b._date,a.is_current = 0

GP：update CDM.cdm_product a set enddate = b._date,is_current = 0 from dim_product_tmp b where a.id = b.id; GPupdate允許子查詢,注意enddate = b._date不能寫成a.enddate = b._date

12、主鍵自增

mysql:CREATE TABLE `cdm_product` ( `id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`))) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

GP: CREATE TABLE "cdm"."cdm_product" ( "id" SERIAL primary key)

13、

mysql：insert into value()

GP ：insert into values()

14、聚集函式

mysql:GROUP_CONCAT(date_day ORDER BY date_day asc separator ',')

GP :array_to_string(ARRAY(SELECT unnest(array_agg(date_day)) order by 1),',')

15、從表中獲取資料給變數賦值

mysql:SELECT @min_date:=min(DATE_FORMAT(u.create_date, '%Y%m%d')) FROM `ODS`.ods_sys_user u;

GP :SELECT min(to_number(u.create_date, '9999999999')) into min_date FROM ODS.ods_sys_user u;

16、建立臨時表

mysql:create TEMPORARY table product select

GP ：create TEMPORARY table product as select

17、join on 和 using

USING 是個一個連線條件的縮寫語法：它接收一個用逗號分隔的欄位名列表，這些欄位必須是連線表共有的並且其值必須相同。最後，JOIN USING 會將每一對相等的輸入欄位輸出為一個欄位，其後跟著所有其它欄位。因此，USING (a, b, c) 等效於 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不過是如果使用了 ON ，那麼在結果裡 a, b, c 欄位都會有兩個，而用 USING 的時候就只會有一個。

18、\除法運算

mysql：select 250/999 =>0.2503結果保留到4位小數，被除數如果是0則返回null

GP :select 250/999 =>0，select 2500/999 =>2結果只保留整數，被除數如果是0則報錯ERROR: division by zero

如果要保留小數需要int型別轉換成numeric型別：select round(250::numeric/999::numeric,4)=>0.2503

常見問題：[Err] ERROR: mismatched parentheses 不匹配的括號，檢查是否確實左右括號

GP不同資料型別不會做隱式轉換，一定要顯示轉換

GP列名不允許出現特殊字元

mysql遷移到greenplum sql改造總結

相關文章