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)

         运行结果如下图所示:

                  



                       

展开阅读全文

没有更多推荐了,返回首页