count(*) over(partition by…的用法记录参考

最新推荐文章于 2024-08-27 14:19:14 发布

Accelerating

最新推荐文章于 2024-08-27 14:19:14 发布

阅读量3.7k

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/Accelerating/article/details/103911354

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

count(*) over(partition by…的用法记录参考

select count(*) over(partition by col1) from tmp_test;

计算每一行中，根据col1分组的数量

with a as (
select tollgateid,plateno,platecolor,storageurlcloseshot,storageurlplate,storageurldistantshot,passtime,motorvehicleid,laneno,direction,
row_number() over(partition by plateno,platecolor order by passtime ) as row_num
from vehicle_records
)
select a.*,b.count_vehicle
from a
left join (select count(1) as count_vehicle from a where row_num=1) b
on 1=1  where a.row_num=1 order by a.passtime desc  limit 10 offset 0;

采用count(1) over 优化

with a as (
select tollgateid,plateno,platecolor,storageurlcloseshot,storageurlplate,storageurldistantshot,passtime,motorvehicleid,laneno,direction,
count(1) over() as count_vehicle
 from (select tollgateid,plateno,platecolor,storageurlcloseshot,storageurlplate,storageurldistantshot,passtime,motorvehicleid,
laneno,direction,row_number() over(partition by plateno,platecolor order by passtime ) as row_num from
vehicle_records) t
where row_num=1
)
select tollgateid,plateno,platecolor,storageurlcloseshot,storageurlplate,storageurldistantshot,passtime,motorvehicleid,
laneno,direction,count_vehicle from a
 order by passtime desc  limit 10 offset 0;