s=s.replace(/GLOBAL in/gi, "in"); // clickhouse s=s.replace(/limit\s+(\d*)\s*,\s*(\d*)\s*/gi, "offset $1 limit $2"); s=s.replace(/date_format\(/gi, "to_char(").replace(/STR_TO_DATE\(/gi, "to_timestamp("); s=s.replace(/%Y-%m-%d %H:%i:%s/g, "yyyy-mm-dd hh24:mi:ss").replace(/%Y-%m-%d/g, "yyyy-mm-dd").replace(/%H:%i:%s/g, "hh24:mi:ss"); s=s.replace(/formatDateTime\(/gi, "to_char(").replace(/toDateTime\(/gi, "to_timestamp("); // clickhouse s=s.replace(/%Y-%m-%d %H:%M:%S/g, "yyyy-mm-dd hh24:mi:ss").replace(/%Y-%m-%d/g, "yyyy-mm-dd").replace(/%H:%M:%S/g, "hh24:mi:ss"); // clickhouse s=s.replace(/IFNULL\(/gi, "coalesce("); s=s.replace(/IF\(\s*([^,]+)\s*,\s*([^(,]+)\s*\)/gi, "(case when $1 then $2 else null end)"); s=s.replace(/IF\(\s*([^,]+)\s*,\s*([^(,]+)\s*,\s*([^(,]+)\s*\)/gi, "(case when $1 then $2 else $3 end)"); s=s.replace(/sysdate\(\)/gi, "now()"); s=s.replace(/today()\(\)/gi, "curdate()"); // clickhouse s=s.replace(/group_concat\(\s*distinct\s+(\S+)\s*\)/gi, "array_to_string(array_agg(distinct $1), ',')"); s=s.replace(/group_concat\(\s*(\S+)\s*\)/gi, "array_to_string(array_agg($1), ',')"); s=s.replace(/groupArray\(/gi, "array_agg("); // clickhouse // s=s.replace(/arrayStringConcat\(\s*(\S+)\s*\)/gi, "array_to_string("); // clickhouse s=s.replace(/select\s+DISTINCT\s+(\S+)/gi, "select DISTINCT ON ($1) $1"); s=s.replace(/DATEDIFF\(\s*([^,]+)\s*,\s*([^(,]+)\s*\)/gi, "date_part('day', timestamp $1 - timestamp $2)"); s=s.replace(/TIMESTAMPDIFF\(\s*([^,]+)\s*,\s*([^,]+)\s*,\s*([^(,]+)\s*\)/gi, "date_part('$1', timestamp $3 - timestamp $2)"); s=s.replace(/date_add\(\s*([^,]+)\s*,\s*INTERVAL\s+(([^)]+)\s+(MICROSECOND|SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|QUARTER|YEAR))\s*\)/gi, "timestamp $1 + interval '$2'"); s=s.replace(/date_sub\(\s*([^,]+)\s*,\s*INTERVAL\s+(([^)]+)\s+(MICROSECOND|SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|QUARTER|YEAR))\s*\)/gi, "timestamp $1 - interval '$2'"); s=s.replace(/addDays\(\s*([^,]+)\s*,\s*([^(,]+)\s*\)/gi, "$1 + interval '$2 day'"); // clickhouse s=s.replace(/subDays\(\s*([^,]+)\s*,\s*([^(,]+)\s*\)/gi, "$1 - interval '$2 day'"); // clickhouse s=s.replace(/show tables/gi, "select tablename from pg_tables where schemaname = current_schema()"); s=s.replace(/show database/gi, "select current_database()"); s=s.replace(/show create table\s+`?([a-z0-9_]+)`?/gi, "SELECT HG_DUMP_SCRIPT($1)"); mysql / clickhouse -> holo (pgsql) 转换 结果: 新窗口打开