SQL／HQL

1、mysql 实现row_number

select @rownum := @rownum + 1 as rownum,hui.user_name
from (select @rownum := 0) t1,hbzf_base.hfd_user_info hui
where hui.yn = 1

2、mysql 实现 rank() over(partition by order by)

select hui.position_id,hui.id,
  if(@positionid = hui.position_id,@rank := @rank + 1,@rank := 1) as rank,
  @positionid := hui.position_id
from hbzf_base.hfd_user_info hui
where hui.yn = 1
ORDER BY hui.position_id,hui.id ASC;

3、常用函数

时间函数：now(),current_date(),current_time(),date(now(),),year(),month(),

mysql:to_date(current_date()),

           date_format('2018-01-01','%Y-%M-%d %H-%m-%s %u'),

           str_to_date('2018-01-01','%Y-%m-%d')

hive :cast('2018-01-01' as date)

获取周几：mysql:dayofweek(current_date) ;hive

获取周数：mysql:week(current_date(),0-周日／1-周一)；hive：weekofyear(周一)

日期加减：mysql:date_sub(current_date(),INTERVAL 8 day);hive:date_sub(current_date(),8)   

unix时间戳：mysql:from_unixtime(unix_timestramp(),"%Y-%m-%d %H-%i-%S")

                                UNIX_TIMESTAMP('2017-08-23') 只能转化日期

                     hive:   from_unixtime(unix_timestramp(),"YYYY-MM-dd HH:mm:ss")

                                unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') 可以转化各种形式的日期

concat:字符串拼接

group_concat: group by XX with rollup

concat_ws:group by

right／left：截取字符串：right(current_date(),5) ->08-21

4、查询效率

1、查询顺序为：from－》where－》group by－》having－》order by

2、在表联合查询时先在on条件下生成临时表，然后在临时表中进行where查询，所以在left、right join下注意on条件（inner join时on和where等同）

3、关联的一张表数据量很小，防止某个reduce落的数据很大导致内存溢出情况

select /*+ mapjoin(A)*/ f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802) 

4、不等值的连接操作 在map阶段完成不等的操作，不必产生笛卡尔集

select /*+ MAPJOIN(a) */a.start_level, b.*

from dim_level a join (select * from test) b

where b.xx>=a.start_level and b.xx<end_level;

5、一般要使得数据库查询语句性能好点遵循一下原则:

• 在做表与表的连接查询时，大表在前，小表在后

• 不使用表别名，通过字段前缀区分不同表中的字段

• 查询条件中的限制条件要写在表连接条件前

• 尽量使用索引的字段做为查询条件，hive限制partition

5、map 、json解析

1、json解析：get_json_object("cloumn_name","$.key_name")  

2、str_to_map("cloumn_name","listLimiter","keyvalueLimiter")["key"] as "name"

6、UDF

输入：sys.stdin 读取数据list

输出：print \t 分割的list，每个list为输出所有字段

使用：添加py文件：add file /software/udf/udf1.py

          select transform(cloumns) using 'python udf1.py' as (cloumns)