mysql to greenplum_mysql查询在greenplum上的实现

FIND_IN_SET mysql的最终结果是逻辑判断，还有注意字段是否有大小写

m: FIND_IN_SET(b.my_building_project_id in a.my_building_project_phase_id)

g: position(UPPER(b.my_building_project_id) in UPPER(a.my_building_project_phase_id))>0

行列转换

string_agg(t2,',') tt1

SELECT t1,string_agg(t2,',') tt1 from test.test1 GROUP BY t1

a b,c

b a,g

regexp_split_to_table(t3.tt1,',')

SELECT t3.t1,regexp_split_to_table(t3.tt1,',') from

(

SELECT t1,string_agg(t2,',') tt1 from test.test1 GROUP BY t1 )t3 GROUP BY t3.t1,t3.tt1

b a

b g

a b

a c

SELECT regexp_split_to_table(t3.tt1,',') from

(

SELECT string_agg(t2,',') tt1 from test.test1 GROUP BY t1 )t3

比较字段有大小写

UPPER(a.my_building_project_id)=UPPER(b.building_father_id)

数值类型的判断

m:a.order_status !=-1

g: a.order_status <>-1

时间比较

m:TIMESTAMPDIFF(DAY,date(max(a.create_time)),ifnull(b.sign_time,now())) days_diff

m:TIMESTAMPDIFF(HOUR,a.create_time,b.create_time)<=168

注意顺序

g: date_part('epoch', b.create_time::TIMESTAMP-a.create_time::TIMESTAMP)>=604800

求出秒

SELECT date_part('epoch', '2019-05-06 12:00:00'::TIMESTAMP -'2019-05-03 11:30:01'::TIMESTAMP)::NUMERIC;

分组计算

SELECT t1,t2,t3,rank() over(PARTITION BY t1 ORDER BY t3) as t4 from test.test1

b g 3 1

b a 7 2

a b 8 1

a c 9 2

时间2

-- DATE(enter_time) =DATE_ADD(CURRENT_DATE,INTERVAL -1 day)

gp select date(CURRENT_DATE+interval '-1 day');

m SELECT DATE_ADD(CURRENT_DATE,INTERVAL -1 day)

group_concat

gp：string_agg(字段,'分隔符')

多字段合并

GP:concat_ws('分隔符'，字段，字段)

空值设置为0

g SELECT COALESCE(null,0)

m SELECT ifnull(null,0)

除法

m：SUM(a.stop_seconds)/36000 默认是带小数

g：SUM(a.stop_seconds)/36000 默认是取整数

decode(string text, format text) bytea 把用string表示的文本里面的二进制数据解码。 format选项和encode相同。 decode('MTIzAAE=', 'base64') \x3132330001

g：SUM(a.stop_seconds)/36000::numeric

二进制转换

m:SELECT from_base64('6IyD5q2j5YW0')

g:SELECT convert_from(decode('6IyD5q2j5YW0', 'base64'),'utf8')

convert_from(string bytea, src_encoding name) text 把原来编码为src_encoding的字符串转换为数据库编码格式。 这种编码格式中，string必须是有效的。 convert_from('text_in_utf8', 'UTF8') text_in_utf8 用当前数据库编码表示

decode(string text, format text) bytea 把用string表示的文本里面的二进制数据解码。 format选项和encode相同。 decode('MTIzAAE=', 'base64') \x3132330001

http://postgres.cn/docs/9.4/functions-string.html

SUBSTRING_INDEX(from_id,'@',-1)

CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)

RETURNS varchar AS $$

DECLARE

tokens varchar[];

length integer ;

indexnum integer;

BEGIN

tokens := pg_catalog.string_to_array($1, $2);

length := pg_catalog.array_upper(tokens, 1);

indexnum := length - ($3 * -1) + 1;

IF $3 >= 0 THEN

RETURN pg_catalog.array_to_string(tokens[1:$3], $2);

ELSE

RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);

END IF;

END;

$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;

FILTER的使用

gp:

SELECT c.openid,

MIN(createtime) FILTER (where c.wxconfigid='xxxxx') as n

from wxmgr_user c

group by c.openid

mysql

SELECT c.openid,

MIN(case when c.wxconfigid='xxxxx' then c.createtime else '2099-01-01 00:00:00' end) as n

from wxmgr_user c

group by c.openid

时间段

mysql：上周从周一开始

(a.create_time >= concat(ADDDATE(DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY),-6),' ','00:00:00') AND

a.create_time < concat(DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY),' ','00:00:00'))

gp:

(a.create_time >= CURRENT_DATE - (extract(DOW from CURRENT_DATE)+6 || ' day')::interval AND

a.create_time < CURRENT_DATE - (extract(DOW from CURRENT_DATE)-1 || ' day')::interval)

昨天

mysql：ADDDATE(CURRENT_DATE,INTERVAL -1 DAY)

gp：CURRENT_DATE-integer '1'

显示年月

mysql：DATE_FORMAT(a.day,'%Y-%m')

gp: to_char(a.day,'YYYY-MM')

SELECT to_char(now(),'YYYY-MM-DD HH24:MI:SS') now;