一般常用的SQL函数(持续更新与完善…)

• 窗口函数，是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中，不会过滤元组。参考1 参考2

  • 一、特有窗口函数

    --rank() 有并列，446
    --dense_rank() 有并列，445
    --row_number() 没有并列 456
    
    select *,
       rank() over (parition by 班级 order by 成绩 desc) as ranking, 
       dense_rank() over (parition by 班级 order by 成绩 desc) as dese_rank,
       row_number() over (parition by 班级 order by 成绩 desc) as row_num 
    from 班级表
    
    --lag
    --leaf
    --Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立---的列。
    --这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率，其中over()表示当前查询的结果集对象，括---号里面的语句则表示对这个结果集进行处理。
    
    select userid,
           time stime,
           lead(time) over(partition by userid order by time) etime,
           UNIX_TIMESTAMP(lead(time) over(partition by userid order by time),'yyyy-MM-dd HH:mm:ss')- UNIX_TIMESTAMP(time,'yyyy-MM-dd HH:mm:ss') period,
           url 
      from test.user_log;
    

  • 二、聚合函数

    -- 对分区内排名在此元组之前（包括此元组）的聚合统计
    select *,
       sum(成绩) over (parition by 班级 order by 学号) as current_sum,
       avg(成绩) over (parition by 班级 order by 学号) as current_avg,
       count(成绩) over (parition by 班级 order by 学号) as current_count,
       max(成绩) over (parition by 班级 order by 学号) as current_max,
       min(成绩) over (parition by 班级 order by 学号) as current_min
    from 班级表
    -- 对分区内的聚合统计
    select *,
       sum(成绩) over (parition by 班级) as current_sum,
       avg(成绩) over (parition by 班级) as current_avg,
       count(成绩) over (parition by 班级) as current_count,
       max(成绩) over (parition by 班级) as current_max,
       min(成绩) over (parition by 班级) as current_min
    from 班级表
    

• ifnull

  select IFNULL(*expression*, *alt_value*)

• case when，可以理解为Java、C/C++里面的 if, else if, else

  --简单case函数，无法处理null
  case sex
    when '1' then '男'
    when '2' then '女’
    else '其他' end
  --case搜索函数 --
  case when sex = '1' then '男'
       when sex = '2' then '女'
       else '其他' end
  e.g.
  SQL> select
    2    sum(case u.sex when 1 then 1 else 0 end)男性,
    3    sum(case u.sex when 2 then 1 else 0 end)女性,
    4    sum(case when u.sex <>1 and u.sex<>2 then 1 else 0 end)性别为空
    5  from users u;
   
          男性         女性       性别为空
  ---------- ---------- ----------
           3          2          0
  
  --------------------------------------------------------------------------------
  SQL> select
    2    count(case when u.sex=1 then 1 end)男性,
    3    count(case when u.sex=2 then 1 end)女,
    4    count(case when u.sex <>1 and u.sex<>2 then 1 end)性别为空
    5  from users u;
   
          男性          女       性别为空
  ---------- ---------- ----------
           3          2          0
  

• with语句，能够使得SQL语句更加易于阅读

  with tmpTableName1 as ( subQuery1),
  tmpTableName2 as (subQuery2)
  Query
  e.g. 
  with tmp1 as (
      select *
      from SchoolA
  ), tmp2 as (
     select *
      from SchoolB 
  )
  select * 
  from tmp2