Impala SQL 速查手册

-- 这一行是关于表的注释
create table ...;

/*
这是关于查询的多行注释
*/
select ...;

select * from t /* 这是关于查询的嵌入注释 */ where ...;

select * from t -- 这是多行命令中的尾随注释
where ...;

-- tinyint
select 10 as x;
-- smallint
select 4096 as x;
-- decimal(2,1)
select 1.5 as x;
-- decimal(4,3)
select 1.333 as x;

-- string
select "What\'s happening?" as single_within_double;
select 'I\'m not sure.' as single_within_single;
select "Homer wrote \"The Iliad\"." as double_within_double;
select 'Homer also wrote "The Odyssey".' as double_within_single;

-- Boolean
select true;
select * from t1 where assertion = false;
select case col when true then "yes" when false "no" else "null" end
from t1;

-- 时间
select CAST("2001-01-09 01:05:01" AS TIMESTAMP);
select CAST("2001-01-09T01:05:01" AS TIMESTAMP);
select CAST("1966‑07‑30" AS TIMESTAMP) + INTERVAL 5 YEARS;

-- 中文别名
SELECT 10 as `金额`

-- 转化字符串 '20210901' 为时间类型
select to_timestamp(dt, 'yyyyMMdd') AS dt

select cast('1966-07-30' as timestamp);
select cast('1985-09-25 17:45:30.005' as timestamp);
select cast('08:30:00' as timestamp);
-- 15，返回小时
select hour('1970-01-01 15:30:00');
-- NULL 因为需要秒字段
select hour('1970-01-01 15:30');
-- NULL 因为小时值超出范围
select hour('1970-01-01 27:30:00');
-- 1 代表星期天
select dayofweek('2004-06-13');
-- Sunday
select dayname('2004-06-13');
-- 2005-06-13 hh:mm:ss 字段为零
select date_add('2004-06-13', 365);
-- 13
select day('2004-06-13');
-- 这两个日期之间有多少天？
select datediff('1989-12-31','1984-09-01');
-- 返回本地时区中的当前日期和时间
select now();
-- 2022-01-01 将时间类型转为指定格式字符串
select from_timestamp(registe_time, 'yyyy-MM-dd')

-- 窗口去重计数，注：不支持 count(distinct col) over(partition by ..)
dense_rank() over(partition by [dt] order by [math])
 + dense_rank() over(partition by [dt] order by [math] desc)
 - 1