sql中常用函数的用法

1、sql中sum和count的区别                        ----sql聚合函数

      （1）首先，sum是对一个字段求和，hive中字段的类型一般是string或者是int，如果是int当然没有问题，如果是string类型但是全部是数字也没有问题，但是如果string类型中包含一个字母，sum的结果将会是0

      例如如下的表：

       user_id string shop_id string

          1234              hello

          1234              1234

      那么sum(user_id)的结果就是2468，sum(shop_id)的结果就是0.

     如果没有符合的条件的记录，sum的返回值就是Null，如：

            sum(case when user_id<1000 then shop_id end) 返回值就是Null 

      但是有些时候我们希望sum的结果如果没有符合的记录就返回0，可以用coalesce(shop_id, 0)解决这个问题。

     （2）count是对数据记录的条数进行统计，有一条符合的记录就是1，没有就是0.

2、 case when then end用法示例：        -----sql流程控制函数

             case value 

                      when compare-value then result 

                       when compare-value then result

                      ……

                      else result 

                      end

         例如： select case when 1>0 then 'yes' else 'no' end;

+----------------------------------------+

| case when 1>0 then 'yes' else 'no' end |

+----------------------------------------+

| yes                                                             |

+----------------------------------------+

3、row_number( ) over用法：             -------sql分析函数

      row_number() OVER ( PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的). 

4、if（）函数                                        ----------sql流程控制函数

（1）    if(expr1, expr2, expr3)

        如果expr1返回真， 则该表达式返回expr2，否则返回expr3

       e.g.   mysql>  select if(strcmp('test','test'),'no','yes');

+--------------------------------------+

| if(strcmp('test','test1'),'no','yes')       |

+--------------------------------------+

| yes                                                      |

+--------------------------------------+

     说明：strcmp(expr1, expr2)这个函数在expr1小于expr2时，返回-1，相等时返回0，其余情况返回1.

               在上面的例子中，strcmp返回-1，所以上例输出返回yes 

（2）      ifnull(expr1, expr2)

           该函数在expr1为NULL时，返回expr2，否则，返回expr1. 

（3）     nullif(expr1, expr2)

            如果expr1=expr2，则返回NULL，否则，返回expr1

5、max（）函数 和min（）函数                             ------sql聚合函数

     

      语法：MIN(e1)、MAX(e1)

      参数：e1为一个字符型、日期型或数值类型的表达式。

                若e1为字符型，则根据ASCII码来判断最大值与最小值。

       返回：根据e1参数的类型，返回对应类型的数据。

       作用：MIN(e1)返回e1表达式指定的列中最小值；

                 MAX(e1)返回e1表达式指定的列中最大值；

       说明：max函数可以使用于numeric、char、varchar、money、smallmoney、或datetime列，但不能用于bit列。不允许使用聚合函数和子查询。

e.g.

              查询TEACHER表中教师的最大年龄。实例代码：

              SELECT MAX (AGE) AS MAXAGE

               FROM     TEACHER

       运行结果如下图所示：

                        

              图1TEACHER表中教师的最大年龄

       然而，在实际应用中得到这个结果并不是特别有用，因为经常想要获得的信息是具有最大年龄的教师的教工号、姓名、性别等信息。然而SQL不支持如下的SELECT语句: 

                SELECT TNAME, DNAME, TSEX, MAX (AGE)

                FROM     TEACHER

        因为聚合函数处理的是数据组，在本例中，MAX函数将整个TEACHER表看成一组，而TNAME、DNAME和TSEX的数据都没有进行任何分组，因此SELECT语句没有逻辑意义。同样的道理，下面的代码也是无效的：

                SELECT TNAME, DNAME, TSEX,SAL ,AGE

                FROM     TEACHER    WHERE   AGE＝MAX (AGE)

        解决这个问题的方法，就是在WHERE子句中使用子查询来返回最大值，然后再基于这个返回的最大值，查询相关信息。

        下例在WHERE子句中使用子查询返回最大值：

         查询TEACHER表中年纪最大的教师的教工号、姓名、性别等信息。

         实例代码：

                 SELECT TNAME, DNAME, TSEX, SAL, AGE

                 FROM     TEACHER

                 WHERE   AGE＝(SELECT MAX (AGE) FROM    TEACHER)

         运行结果如下图所示：