MySQL8.0中的窗口函数的示例代码

目录

• 1.窗口函数与聚合函数
• 2.常见的窗口函数
• 3.over子句
• 4.代码示例
  • 4.1row_numberdense_rank rank
  • 4.2cume_distpercent_rank
  • 4.3first_valuelast_value th_value
  • 4.4ntile()
  • 4.5laglead
  • 4.6聚合函数
  • 4.7orderby子句
  • 4.8 window子句
  • 4.9 rows和range

frame_clause:

      frame_units frame_extent

frame_units:

        {ROWS | RANGE}

frame_extent:

     {frame_start | frame_between}

    frame_between:

              BETWEEN frame_start AND frame_end

    frame_start, frame_end:

         { CURRENT ROW

           | UNBOUNDED PRECEDING

           | UNBOUNDED FOLLOWING

           |expr PRECEDING

           | expr FOLLOWING

       }

select order_date,sum(quantity) as quantity,
rank()over(ORDER BY sum(quantity) desc) as rank_result,
dense_rank()over(ORDER BY sum(quantity) desc) as dense_result,
row_number()over(ORDER BY sum(quantity) desc) as row_result
from spm_order 
group by order_date
-- 限定一部分数据，没有实际意义，能展示出这三个函数的区别就可以了
having quantity>=98
order by quantity desc 

select order_date,num,
cume_dist()over(order by num asc) as cume_result,
percent_rank()over(order by num asc) as percent_result
from (select order_date,count(1) as num
from spm_order 
group by order_date
having num>=27)a
order by num asc

select sales_name,year_date,num,
first_value(num)over(PARTITION by sales_name order by year_date asc) as first_result,
last_value(num)over(PARTITION by sales_name order by year_date asc) as last_result,
nth_value(num,2)over(PARTITION by sales_name order by year_date asc) as nth_result
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order 
where sales_name in ("杨健","楚杰","洪光")
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a

select sales_name,year_date,num,
ntile(8)over(order by num asc) as n_bin
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order 
where sales_name in ("杨健","楚杰","洪光")
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a

select sales_name,year_date,num,
lag(num,2)over(PARTITION by sales_name order by year_date asc) as lag_result,
lead(num,2)over(PARTITION BY sales_name order by year_date asc) as lead_result
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order 
where sales_name in ("杨健","楚杰","洪光")
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a

select sales_name,year_date,num,
sum(num)over(PARTITION by sales_name) as sum_order,
avg(num)over(PARTITION by sales_name) as mean_order
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order 
where sales_name in ("杨健","楚杰","洪光")
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a

select sales_name,year_date,num,
sum(num)over(partition by sales_name) as count_1,
count(num)over(partition by sales_name order by num) as count_2
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order 
where sales_name in ("杨健","楚杰","洪光")
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a

select sales_name,year(order_date) as year_1,count(1) as num,
sum(count(1)) over w as sales_order,
sum(count(1)) over (w_1) as year_order,
rank()over(w order by count(1) desc) as rank_order
-- 三种写法都是符合语法规范的
from spm_order 
where sales_name in ("杨健","楚杰","洪光")
group by sales_name,year(order_date)
window w as (PARTITION by sales_name),
       w_1 as (PARTITION by year(order_date))
order by sales_order

select sales_name,month_1,rn_1,num,
sum(num)over(order by month_1 rows between 2 preceding and 1 preceding) as month_row,
sum(num)over(order by month_1 range between 2 preceding and 1 preceding) as month_range,
sum(num)over(order by rn_1 range between 2 preceding and 1 preceding) as rn_range
from (SELECT sales_name,month(order_date) as month_1,count(1) as num,
-- 由于rank()over()返回的是unsigned,当相减结果为负时(between子句会用到减法)会报错，所以这里转成signed类型
cast(rank()over(order by month(order_date)) as signed) as rn_1
from spm_order
where sales_name in ("洪光","范彩")
group by sales_name,month(order_date))a
order by month_1 asc