mysql开窗函数笔记记录

最新推荐文章于 2025-05-03 23:21:20 发布

Julie ZhuZhuzhu

最新推荐文章于 2025-05-03 23:21:20 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

文章标签： sql

本文链接：https://blog.youkuaiyun.com/lu_xiaochun/article/details/122827447

本文通过一系列的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;