SQL基础整理（七）关于关于CASE表达式和窗口函数

还是按照惯例，把这次示例需要用到的表贴出来 

表名product

一.关于CASE表达式

1. 了解其他语言的人，都使用过IF语句,在SQL语句中，CASE语句的作用有点类似于IF

2.

select product_name,
       case when product_type='衣服'
					    THEN 'A:' || product_type
            when product_type='办公用品'
					    THEN 'B:' || product_type
            when product_type='厨房用具'
					    THEN 'C:' || product_type
            ELSE NULL
       end as result
from product;

对上面的代码进行讲解，首先case语句必须对应一个end 标志表示case 语句的结束，然后 当when后面的语句成立时，会执行then 后面的代码，这里是对商品类型判断，然后与A B C进行拼接，结果如下，同时，为了程序的可读性，一般会在when的末尾增加一个else 语句，表示当所有when语句都不成立时执行的代码。

 

3.如果我们想统计每种商品的售价总和，我们通过group by 语句对表进行切分后可以很容易的得到

select product_type,sum(sale_price)
from product
GROUP BY product_type;

那么，如果我们想要把这个表横过来，我们应该怎么写呢，让我们通过case语句实现一下

select sum( case when product_type='衣服'
								then sale_price else 0 END) as clothes_price,
       sum( case when product_type='厨房用具'
								then sale_price else 0 END) as kitchen_price,
       sum( case when product_type='办公用品'
								then sale_price else 0 END) as office_price
from product;

 

 

 

在对select的结果进行编辑的时候，CASE语句能发挥比较大的作用

 

 

二.窗口函数

1. 建议使用Oracle数据库进行窗口函数的学习，因为Oracle数据库对于窗口函数的支持度是比较高的

2. 窗口函数可以通过进行排序，生成序列号等一般聚合函数无法实现的高级功能

3. 基本语法 ：  <窗口函数> OVER( partition by <字段名>

                                                                order by <字段名>)

4. 能够作为窗口函数的有（1）专门的窗口函数：RANK() , DENSE_RANK , ROW_NUMBER

                                     （2） 可以作为窗口函数的聚合函数：sum , avg , count , max , min

5.   基本示例 1 使用RANK函数

select product_name,product_type,sale_price,
       rank() over(partition by product_type
                      order by sale_price) as ranking
from product;

 

 

可以看到，在这个例子中，在相同的商品类型中，按照商品价格 生成了序列号  ，其中partiton by product_type可以理解成是group by语句，是的下面的order by语句是在相同的商品类型下进行的，那么如果不写partiton by结果是什么样的呢

select product_name,product_type,sale_price,
       rank() over(order by sale_price) as ranking
from product;

可以看到，这样是对整体进行排序

 

6. 基本示例2 DENSE_RANK(), ROW_NUMBER()函数

select product_name,product_type,sale_price,
       rank() over(order by sale_price) as ranking,
       dense_rank() over(order by sale_price) as dense_ranking,
       row_number() over(order by sale_price) as row_num
from product;

 

 

通过这个例子，我们可以看出来，对于RANK（），擦菜板变成了第四名，对于DENSE_RANK()擦菜板排在了第三名，而对于row_number更类似于是按照行号拍醒，通过上面的例子，可以很容易的理解出三个专用窗口函数的区别

 

7.  作为窗口函数使用的聚合函数

示例1： 将sum作为聚合函数

select product_name,product_type,sale_price,
      sum(sale_price) over( ORDER BY product_id) 
from product;

在这个示例中，最后一个字段对之前所有的sale_price进行累加计算

 

示例2：将avg()作为窗口函数

select product_name,product_type,sale_price,
      avg(sale_price) over( ORDER BY product_id ) 
from product;

 

 

 

 

8.  计算移动平均：将汇总范围确定在更具体的范围内

   可以看到，在我们将聚合函数作为窗口函数的时候，他们将之前行的所有数据都进行了聚合，但是我们可以进行更加精确范围的聚合，比如 我们只聚合在这一行之前的前一行或者是后一行

只聚合改行前面一行的数据 通过rows 1 preceding 实现

select product_name,product_type,sale_price,
      avg(sale_price) over( ORDER BY product_id rows 1 preceding ) 
from product;

 

对该行的前一行和后一行进行聚合

select product_name,product_type,sale_price,
      avg(sale_price) over( ORDER BY product_id rows BETWEEN 1 preceding and 1 following ) 
from product;