mysql开窗函数笔记记录

本文通过一系列的SQL查询示例,详细介绍了MySQL中的开窗函数应用,包括SUM、ROW_NUMBER、COUNT、PARTITION BY和ORDER BY等,展示了如何在不同场景下使用这些函数进行数据聚合和排序,以实现更复杂的数据分析和业务需求。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值