mysql开窗函数笔记记录

#开窗函数练习
SUM(expr) over()

SELECT 
ROW_NUMBER() OVER (ORDER BY milk_tea.sale_price DESC) AS [RANK],*
FROM milk_tea;

select prod_name,net_w,count(*)over(partition by net_w) from milk_tea ;

select prod_name,net_w,count(*)over(partition by net_w) as t from milk_tea ;

select prod_name,net_w,count(*)over(partition by net_w) as t ,count(*) over(partition by prod_name)as h from milk_tea ;

select supplier_id,prod_name,brand,class from prod_info;

select supplier_id,prod_name,brand,class ,count(*)over(partition by class),count(*)over(partition by supplier_id)
from prod_info;

select supplier_id,prod_name,brand,class,cost,sale_price,sum(prod_id) over(order by prod_id rows between unbounded preceding and current row)as t
from prod_info;

select supplier_id,prod_name,brand,class,cost,sale_price,sum(sale_price) over(order by sale_price rows between unbounded preceding and current row)as t
from prod_info;

select supplier_id,prod_name,brand,class,cost,sale_price,sum(cost) over(order by cost )as t
from prod_info;

select supplier_id,prod_name,brand,class,cost,sale_price,sum(cost) over(order by cost rows between unbounded preceding and current row)as t
from prod_info;

SELECT supplier_id,prod_name,brand,class,cost,sale_price,
MAX(cost) OVER(ORDER BY class) 此行之前最大值
FROM prod_info

#开窗函数练习2
#over(partition by XX  order by XX)  partition by和order by 结合

SELECT supplier_id,prod_name,brand,class,cost,sale_price,
ROW_NUMBER()OVER(ORDER BY class) 
FROM prod_info

SELECT supplier_id,prod_name,brand,class,cost,sale_price,
ROW_NUMBER()OVER(partition by class) 
FROM prod_info

#不一样
SELECT supplier_id,prod_name,brand,class,cost,sale_price
FROM prod_info group by class;

SELECT supplier_id,class,prod_name,brand,cost,sale_price,
count(prod_name)OVER(partition by class order by prod_name ) 
FROM prod_info 

select *
row_number() over(partition by 列名 order by列名)
from prod_info
;

select * ,
row_number() over(partition by prod_name order by  sale_price) as r
from prod_info
;

select prod_name,sale_price ,cost,
row_number() over(partition by prod_name order by  cost DESC)r
from prod_info
where r = 2;

select ref_host,pv_ref_host_hour,concat(month,day,hour),
row_number() over (partition by concat(month,day,hour) order by pv_ref_host_hour desc) as od
from pvs_ref_host_hour 
where od <= 3;