#开窗函数练习
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;