HQL语句规范

 

错误的写法：

from
(
from
(
select userkey, concat_ws('-', split(dt, '-')[0], split(dt, '-')[1]) as mth, dt from client_uv_daily where dt between '2014-04-01' and '2015-03-31' and instr(time, concat_ws('-', split(dt, '-')[0], split(dt, '-')[1])) = 0 and instr(dt, concat_ws('-', split(dt, '-')[0], split(dt, '-')[1])) <> 0) a
select userkey, mth, count(distinct dt) as days group by mth, userkey) b
select mth, days, count(distinct userkey) group by mth, days

这个写法中where子句的条件用到了select的投影字段（经过处理的中间字段）执行语句报错；

 

修改后的写法：

from
(
from
(
from
(
select userkey, concat_ws('-', split(dt, '-')[0], split(dt, '-')[1]) as mth, time, dt from client_uv_daily where dt between '2014-04-01' and '2015-03-31') a
select userkey, mth, dt where instr(time, mth) = 0 and instr(dt, mth) <> 0) b
select userkey, mth, count(distinct dt) as days group by mth, userkey) c
select mth, days, count(distinct userkey) group by mth, days

在select子句中处理的字段不能再where子句中直接使用，这个是HQL中条件子句的使用和SQL不同的地方。

 

连接和外连接：

select a.userkey, a.time, b.type from
(select userkey, time from client_uv_daily where dt = '2015-04-20' and time <> '#' and isopa = 1) a left join (select trim(userkey) as userkey, type from client_user_type where dt = '2015-04-20') b on a.userkey = b.userkey where a.userkey is not null;

报错：FAILED: ParseException line 2:100 cannot recognize input near 'left' 'join' '(' in join type specifier

改为：

select a.userkey, a.time, b.type from
(select userkey, time from client_uv_daily where dt = '2015-04-20' and time <> '#' and isopa = 1) a left outer join (select trim(userkey) as userkey, type from client_user_type where dt = '2015-04-20') b on a.userkey = b.userkey where a.userkey is not null;

执行成功。