首页 > 数据库 >【数据库】对大数据量数据集,PostgreSQL分组统计数量,限定每组最多数量

【数据库】对大数据量数据集,PostgreSQL分组统计数量,限定每组最多数量

时间:2024-02-11 09:00:33浏览次数:29  
标签:city PostgreSQL number 对大 num 分组 数据量 row population

一、背景介绍

在处理大数据量数据集时,我们经常需要进行分组统计。例如,我们需要统计每个城市的人口数量、每个年龄段的人数等。在 PostgreSQL 中,我们可以使用 row_number() 函数结合 over (partition by) 子句来实现这个功能。同时,为了限定每组最多数量,我们可以使用 row_num <= 100 条件进行筛选。

二、实现方法

1. 使用 row_number() 函数

row_number() 函数是一个窗口函数,它可以为每一行分配一个唯一的序号。在 over (partition by) 子句中,我们可以指定按照哪个字段进行分组。例如,如果我们要按照城市进行分组,可以这样写:

SELECT city, age, population, row_number() over (partition by city order by population desc) as row_num
FROM population_data;

这里,我们首先按照城市进行分组,然后按照人口数量降序排列。接着,我们使用 row_number() 函数为每一行分配一个序号。最后,我们将结果命名为 row_num

2. 使用 row_num <= 100 条件进行筛选

为了限定每组最多数量,我们可以使用 row_num <= 100 条件进行筛选。例如,如果我们只想显示每个城市前100名的人口数量,可以这样写:

SELECT city, age, population, row_num
FROM (
    SELECT city, age, population, row_number() over (partition by city order by population desc) as row_num
    FROM population_data
) as subquery
WHERE row_num <= 100;

这里,我们首先将原始查询语句放入一个子查询中,然后在外部查询中添加 WHERE row_num <= 100 条件进行筛选。这样,我们就可以得到每个城市前100名的人口数量了。

三、示例数据

为了演示如何使用 row_number() 函数和 row_num <= 100 条件进行分组统计,我们创建了一个名为 population_data 的表,包含以下字段:

  • city:城市名称(字符串类型)
  • age:年龄(整数类型)
  • population:人口数量(整数类型)

以下是一些示例数据:
cityagepopulationBeijing3521542000Shanghai4224237800Guangzhou3913081000Shenzhen3612528300Hangzhou349816000Chengdu3716330000Wuhan3811292000Chongqing4130485500Tianjin4313562100Suzhou447725599Nanjing458288345Xi’an468375289Qingdao477779652Dongguan488349652Zhengzhou4913534752Ningbo507896521Harbin5110789652Changsha527896521Xiamen537896521Shenyang547896521Nanning557896521Wuxi567896521Jinan577896521Taiyuan587896521Luoyang597896521Lanzhou607896521Kunming617896521

标签:city,PostgreSQL,number,对大,num,分组,数据量,row,population
From: https://www.cnblogs.com/bigleft/p/18013157

相关文章

  • mongodb大数据量分页查询优化
    业务背景mongodb大数据量分页查询主要耗时是查询总条数,所以有两种优化方式1.不查询总条数,查询最近N页数据[改动略多,执行耗时很短]2.增加页面时间范围必填条件[改动很小,执行耗时略长,与数据量有关][比如默认查询创建时间最近一个月的数据根据数据量做调整,创建时间有组合索引]这两种......
  • Ubuntu18.04定时备份MySQL、PostgreSQL数据库
    1、备份MySQL数据库脚本LOG_DATE=`date+'%Y-%m-%d%H:%M:%S'`LOG_PATH=/home/dbserver/script_data/backup.logecho"$LOG_DATE=======>开始备份以下mysql数据库:">>${LOG_PATH}DUMP=/usr/bin/mysqldumpOUT_DIR=/home/dbserver/backup/mysql_backupLINU......
  • SpringBoot集成Flink-CDC 采集PostgreSQL变更数据发布到Kafka
    (之前写了一个flink-cdc同步数据的博客,发布在某N,最近代码开源了,直接复制过来了,懒得重新写了,将就着看下吧)最近做的一个项目,使用的是pg数据库,公司没有成熟的DCD组件,为了实现数据变更消息发布的功能,我使用SpringBoot集成Flink-CDC采集PostgreSQL变更数据发布到Kafka。 一、业务......
  • ubuntu中Postgresql备份恢复及高版本恢复数据到低版本库中
    一、备份基本使用可以使用pg_dump和pg_dumpall命令来进行备份操作。pg_dump:备份指定数据库pg_dumpall:备份所有数据库我这里只用到了pg_dump命令,pg_dumpall也是大同小异,大家自己摸索一下我们直接看一下备份的命令吧,pg_dump-hlocalhost-Upostgres-d数据库名称......
  • 数据量大导致接口请求超时
    问题场景某个接口因数据量过大导致请求超时,报错NetworkError。解决办法webpack默认2分钟后请求超时,更改vue.config.js配置如下://修改webpack默认2分钟的超时proxyTimeout:10*60*1000,onProxyReq:(proxyReq,req,res)=>req.setTime......
  • postgresql-时间函数
    PostgreSQL-version9.4.241.系统当前时间selectnow();--天、时分秒2022-09-2915:50:13.273269+08selectcurrent_date;--天2022-09-29selectcurrent_time;--时分秒15:50:13.507701+08selectcurrent_timestamp;--天、时分秒2022-09-2915:50:......
  • 【数据库】PostgreSQL中的DISTINCT ON和DISTINCT的区别
    深入理解PostgreSQL中的DISTINCTON和DISTINCT在数据库查询中,我们经常会遇到需要去除重复数据的情况。在PostgreSQL中,我们可以使用DISTINCT和DISTINCTON来实现这个目标。那么,它们之间有什么区别呢?本文将详细介绍这两种方法的用法、区别以及适用场景。DISTINCT的基本用法DISTIN......
  • 【PostgreSQL架构】PostgreSQL的最佳群集高可用性方案
    转载:【PostgreSQL架构】PostgreSQL的最佳群集高可用性方案-腾讯云开发者社区-腾讯云(tencent.com)如果您的系统依赖PostgreSQL数据库并且您正在寻找HA的集群解决方案,我们希望提前告知您这是一项复杂的任务,但并非不可能实现。我们将讨论一些解决方案,您可以从中选择对您的容错要......
  • PostgreSQL
    PostgreSQLsql语言范畴DDL:DROP、CREATE、ALTER等语句。DML:INSERT、UPDATE、DELETE语句。DQL:SELECT语句oDCL:GRANT、REVOKE、COMMIT、ROLLBACK等语句。DDL数据库定义语言定义数据库对象定义:数据库表,数据库类型,字段类型删除数据库表,删除字段类型等DML数据库......
  • 【数据库】为什么PostgreSQL第一次查询结果很慢,第二次就变快了
    引言在PostgreSQL数据库中,我们经常会遇到这样的情况:第一次执行某个查询语句时,结果返回的速度非常慢,而当我们再次执行相同的查询语句时,速度却变得非常快。这种现象可能会让我们感到困惑,为什么同样的查询语句,第一次执行会这么慢,而第二次执行却这么快呢?PostgreSQL的查询缓存机制Po......