sql常用函数

find：

查找文件：find ./actions/ -name Constract.php查找包含leyangjun字母的文件：find /etc -name '*leyangjun*'
查找srm开头的文件：find/etc -name 'srm*'我们要在/usr目录及子目录下查找文件名包含“leyangjun”关键字的文件：find /usr -print |grep leyangjun
grep：

在Constract.php文件中查找字符leyangjun的行：grep 'leyangjun' Constract.php不区分大小写地搜索。默认情况区分大小写：grep -i 'ModifySubacction' controllers/Constract.php
管道查询：
从根目录开始查找所有扩展名为.log的文本文件，并找出包含”leyangjun”的行：find / -type f -name "*.log" | xargs grep "leyangjun"
当前目录开始查找所有扩展名为.php的文本文件，并找出包含”leyangjun”的行：find ./ -name "*.php" | xargs grep "leyangjun"

脚本建表：
1.建表语句放入xxx.sql
2.impala-shell xxx.sql
3.hive -f xxx.sql

:_* scala中是将集合中的元素作为参数传递

LPAD(str,len,padstr)：字符串左填充函数
用字符串 padstr对 str进行左边填补直至它的长度达到 len个字符长度，然后返回 str。如果 str的长度长于 len'，那么它将被截除到 len个字符。

mysql> SELECT LPAD('hi',6,'abc'); -> 'abcahi'

mysql> SELECT LPAD('hi',8,'ab'); -> 'abababhi'

RPAD(str,len,padstr)：字符串右填充函数
用字符串 padstr对 str进行右边填补直至它的长度达到 len个字符长度，然后返回 str。如果 str的长度长于 len'，那么它将被截除到 len个字符。

hour(TIMESTAMP date)  minute(TIMESTAMP date)  SECOND(TIMESTAMP date) 返回对应的小时，分钟，秒
FLOOR(x) 函数返回小于 x 的最大整数值

to_timestamp(floor(trace_time/1000)
to_timestamp([STRING val, STRING format]|[BIGINT val])
Converts a bigint (delta from the Unix epoch) or a string with the specified format to a timestamp. Example: to_timestamp('1970-01-01 00:00:00', 'yyyy-MM-dd HH:mm:ss')

unix_timestamp转换成时间戳

from_unixtime(BIGINT unixtime [, STRING format])
Converts the number of seconds from the Unix epoch to the specified time into a string in the local time zone.

from_timestamp(TIMESTAMP val, STRING format)
Converts a specified timestamp to a string with the given format. Example: from_timestamp(cast('1999-01-01 10:10:10' as timestamp), 'yyyy-MM-dd')" results in "1999-01-01"

to_date()返回日期字符串

1.translate
  语法：TRANSLATE(char, from, to)
  
  用法：返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。
            若from比to字符串长，那么在from中比to中多出的字符将会被删除。
            三个参数中有一个是空，返回值也将是空值。
  举例：SQL> select translate('abcdefga','abc','wo') 返回值 from dual;
            返回值
            -------
            wodefgw
  分析：该语句要将'abcdefga'中的'abc'转换为'wo',
            由于'abc'中'a'对应'wo'中的'w',
            故将'abcdefga'中的'a'全部转换成'w';
            而'abc'中'b'对应'wo'中的'o',
            故将'abcdefga'中的'b'全部转换成'o';
            'abc'中的'c'在'wo'中没有与之对应的字符,
            故将'abcdefga'中的'c'全部删除;
            简单说来，就是将from中的字符转换为to中与之位置对应的字符，
            若to中找不到与之对应的字符，返回值中的该字符将会被删除。
            在实际的业务中，可以用来删除一些异常数据，
            比如表a中的一个字段t_no表示电话号码，
            而电话号码本身应该是一个由数字组成的字符串，
            为了删除那些含有非数字的异常数据，
            就用到了translate函数：
            SQL> delete from a，
                      where length(translate(trim(a.t_no), 
                                            '0123456789' || a.t_no,
                                            '0123456789')) <> length(trim(a.t_no));
 
2.replace 
   
  语法：REPLACE(char, search_string,replacement_string)
  用法：将char中的字符串search_string全部转换为字符串replacement_string。
        
  举例：SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') 返回值 from dual;
            返回值
            ---------
            fgsgswsgs
 
            SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 返回值 from dual;
            返回值
            -----------------------
            fgeeerrrtttsweeerrrttts
  分析：第一个例子中由于'fgsgswsgs'中没有与'fk'匹配的字符串，
            故返回值仍然是'fgsgswsgs'；
            第二个例子中将'fgsgswsgs'中的字符串'sg'全部转换为'eeerrrttt'。
 
总结：综上所述，replace与translate都是替代函数，
         
只不过replace针对的是字符串，而translate针对的是单个字符。

row_number的用途的非常广泛，排序最好用他，一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。row_number用法实例:

rank函数用于返回结果集的分区内每行的排名， 行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。可能我描述的比较苍白，理解起来也比较吃力，我们直接上代码，rank函数的使用方法与row_number函数完全相同。

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。将上面的Sql语句改由dense_rank函数来实现。

NTILE
　　ntile函数可以对序号进行分组处理，将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 
对于每一个行，ntile 将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。
ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。
也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。下面的SQL语句使用ntile函数对Order表进行了装桶处理：

FIRST_VALUE()和LAST_VALUE()字面意思已经很直观了，取首尾记录值。

例:查询部门最早发生销售记录日期和最近发生的销售记录日期
SQL> select *from criss_sales order by dept_id,sale_date;
 
DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT
------- ----------- ---------- -----------
D01     2014/3/4    G00                700
D01     2014/4/8    G01                200
D01     2014/4/30   G03                800
D01     2014/5/4    G02                 80
D01     2014/6/12   G01        
D02     2014/3/6    G00                500
D02     2014/4/8    G02                100
D02     2014/4/27   G01                300
D02     2014/5/2    G03                900

SQL> select
  2     dept_id
  3    ,sale_date
  4    ,goods_type
  5    ,sale_cnt
  6    ,first_value(sale_date) over (partition by dept_id order by sale_date) first_value
  7    ,last_value(sale_date) over (partition by dept_id order by sale_date desc) last_value
  8  from criss_sales;
 
DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT FIRST_VALUE LAST_VALUE
------- ----------- ---------- ----------- ----------- -----------
D01     2014/3/4    G00                700 2014/3/4    2014/3/4
D01     2014/4/8    G01                200 2014/3/4    2014/4/8
D01     2014/4/30   G03                800 2014/3/4    2014/4/30
D01     2014/5/4    G02                 80 2014/3/4    2014/5/4
D01     2014/6/12   G01                    2014/3/4    2014/6/12
D02     2014/3/6    G00                500 2014/3/6    2014/3/6
D02     2014/4/8    G02                100 2014/3/6    2014/4/8
D02     2014/4/27   G01                300 2014/3/6    2014/4/27
D02     2014/5/2    G03                900 2014/3/6    2014/5/2

看结果first_value()很直观，不用多解释
但是，last_value()值，部门D01不是应该为2014/6/12，部门D02不是应该为2014/5/2吗?为什么会每条记录都不一样?

可以这样去理解:last_value()默认统计范围是 rows between unbounded preceding and current row

验证一下:
SQL> select
  2     dept_id
  3    ,sale_date
  4    ,goods_type
  5    ,sale_cnt
  6    ,first_value(sale_date) over (partition by dept_id order by sale_date) first_value
  7    ,last_value(sale_date) over (partition by dept_id order by sale_date desc) last_value
  8    ,last_value(sale_date) over (partition by dept_id order by sale_date rows between unbounded preceding and unbounded following) last_value_all
  9  from criss_sales;
 
DEPT_ID SALE_DATE   GOODS_TYPE    SALE_CNT FIRST_VALUE LAST_VALUE  LAST_VALUE_ALL
------- ----------- ---------- ----------- ----------- ----------- --------------
D01     2014/3/4    G00                700 2014/3/4    2014/3/4    2014/6/12
D01     2014/4/8    G01                200 2014/3/4    2014/4/8    2014/6/12
D01     2014/4/30   G03                800 2014/3/4    2014/4/30   2014/6/12
D01     2014/5/4    G02                 80 2014/3/4    2014/5/4    2014/6/12
D01     2014/6/12   G01                    2014/3/4    2014/6/12   2014/6/12
D02     2014/3/6    G00                500 2014/3/6    2014/3/6    2014/5/2
D02     2014/4/8    G02                100 2014/3/6    2014/4/8    2014/5/2
D02     2014/4/27   G01                300 2014/3/6    2014/4/27   2014/5/2
D02     2014/5/2    G03                900 2014/3/6    2014/5/2    2014/5/2
 
全统计的情况下得到的last_value()值，部门D01为2014/6/12，部门D02为2014/5/2

union和union all的区别是，union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。