【SQL】使用SQL查询制作分区间/分段统计的思路

SQL区间 区间相较数列具有不同的数据特征,不过在实际应用中,数列与区间的处理具有较多相通性。常见的区间操作有区间分割、区间交叉等SQL如何实现区间分割?SQL如何处理区间交叉?本节将介绍一些常见的区间场景,并抽象出通用的解决方案本文尝试独辟蹊径,强调通过灵活的、发散性的数据处理思维,就可以用最基础的语法,解决复杂的数据场景。文章涉及的SQL语句使用了Hive SQL的基础语法和部分高级语法特性传送门。 阅读详情
AI摘要

本文介绍如何使用SQL实现分段/分区间统计的方法,适用于报表制作、数据展示等场景。通过对具体案例的解析,展示了从数据准备到最终统计结果生成的全过程。

使用SQL查询制作分区间/分段统计的思路

一、场景简介

以下内容是对该问题和经验分享的场景进行简介,可以通过此部分查看此文章内容是否为你可以参考的内容。
使用SQL查询实现分区间/分段统计。该sql使用常存在于报表的制作,统计数据的计算,大屏数据展示等场景。通常我们会面对一类需求,将一组或一批或一张表的数据,根据某一字段按照分段、分区间(有时是自定义区间)的形式,进行汇总统计,计算相关数值、占比等。譬如以下场景:

  1. 统计某班级(或其他类似团体)某次测试分数分布
  2. 计算某站点访问量随时间变化的曲线
  3. 某地区/某行业(或其他维度)收入分布
  4. 记录某机器每天的某几个时段使用情况
  5. 为大屏展示提供某一组数据的自定义分段统计情况
  6. …
    分段统计样例

这些场景的特点是:不同于常规的分组统计,可以根据某一字段直接使用group by进行分组统计,而是一个区间范围,且区间范围可能大小不一。无法直接使用group by分组统计。
如果你有类似的需求,可以继续阅读此文章,或许可以为你提供一种思路上的帮助。
或者我们将需求抽象为:
将已有的一组可统计可计算的数据,依据某一维度(简单理解为某一个或几个字段联合)已知的一些区间(可能是规律区间,可能是自定义区间)划分进行汇总统计。
当我们有以上需求,且我们希望在sql层面直接解决数据的查询和统计,可以参考以下思路。

二、一些建议和前提

前提:首先我们手头应该有一张数据表,并包含我们需要的统计维度和分段需求。
建议:对于此类需求,我的建议是

  1. 分段/区间不要重叠,不要出现区间互相有覆盖的情况
  2. 维度尽量单一,也就是说最好以某单一字段(比如xx时间,xx分数)进行统计。
  3. 数据表关联尽量不要太多(出于效率考虑)

如果你的需求不满足以上内容,我建议是重新探讨需求是否足够合理,多维度的统计无论是表格还是图标都会产生较为混乱的情况,如果不是业务需求一定需要,还是尽量进行需求优化,一方面保证展示数据可读性,一方面保证sql代码可维护性。

三、先给个结果例子

  • 需求:已知一组公司(表company_info)、各公司的本年度收入(表company_income),给该组公司进行收入区间分组,区间为【10亿元以上】、【1亿元(含)-10亿元】、【5000万元(含)-1亿元】、【5000万元以下】。
  • 分析:属于典型的分段统计,并且每个段包含的范围大小各不相同
  • sql:
--thisYear 本年度收入 关联公司信息
with thisYear as
(
	select * from (
		select income.statis_rank,
			   income.main_income,
			   --给数据分配区间Id
			   (case
			    when income.main_income >= sections.section1 then 1
			    when income.main_income < sections.section1 and income.main_income >= sections.section2 then 2
			    when income.main_income < sections.section2 and income.main_income >= sections.section3 then 3
			    when income.main_income < sections.section3 then 4
			    else 0
			    end)
		  from company_info info
		  join company_income income 
		  	-- income 表限制条件
		    on income.company_id = info.id and income.isvalid = 1 and income.year = 2022 
		 	-- 主表限制条件
		 where info.isvalid = 1
		   and info.localion = '330000'
),
--构造分组Id名称映射表
sectionsTable as
(
	select 1 as groupId, '10亿元(含)及以上' as groupName
	 union
	select 2 as groupId, '1亿元(含)-10亿元' as groupName
	 union
	select 3 as groupId, '5000万元(含)-1亿元' as groupName
	 union
	select 4 as groupId, '5000万元以下' as groupName
	 union
	select 0 as groupId, '其他' as groupName
)
--开始统计
select 
	sectionTable.groupId,
	sectionTable.groupName,
	--统计 做非空处理
	coalesce(t4.num,0) as num,
	coalesce(t4.sectionIncome,'0.00')::varchar as sectionIncome,
	coalesce(t4.incomePercent,'0.00%') as incomePercent,
	--queryRowCount是业务框架需求 分页总数
	count(1) over() as queryRowCount
  from 
  	sectionTable
  -- t4 start最主要的统计步骤开始
  left join (
  	select t1.groupId,
  		   --统计总数
  		   count(1) as num,
  		   --统计区间总收入
  		   sum(t1.main_income) as sectionIncome,
  		   --统计区间收入占总收入比
  		   round(sum(t1.main_income)/t3.totalIncome * 100, 2) || '%' as incomePercent
  	  from thisYear t1
  	  left join (
  	  	--为满足计算区间收入占总收入比需求 将总收入作为一个附表进行关联
  	  	select sum(t2.main_income) as totalIncome from thisYear t2
  	  ) t3 on 1=1
  	 where 1=1
  	 --最重要的聚合操作 可以看到 因为已经为每个区间分配了groupId 此处聚合就变成了groupId的聚合
  	 group by t1.groupId, t3.totalIncome
  ) t4 on sectionTable.groupId = t4.groupId
  -- t4 end最主要的统计步骤结束 用聚合后的表的groupId与区间Id名称映射表的groupId进行关联
  order by groupId
  --分页参数
  limit {pageSize} offset {pageFirst}
  • 结果展示:
    分段统计结果展示

四、直接说思路

对于该类需求,我的经验思路如下(直接说写sql思路)。请对照上面示例,逐步理解该思路。

  1. 整理出原数据表中需要的相关内容,也就是select出你需要的字段,有需要关联其他表查询相关关联字段,也请在这一步完成。最终获得一个包含你需要的所有汇总计算字段和统计维度字段的新表(也可称为视图,暂称为DataTable)。
  2. 获得你的分段/分区间表。该表可用最简单的select语句完成,将每一个段的上下限都作为一个值select为一条数据(暂称为SectionTable)。
  3. 将你的数据表(DataTable)与区间表(SectionTable)进行关联(left join),关联条件为1=1即可。此时获得一个与DataTable条目数量相同的新表(WithSectionTable),包含数据和区间。
  4. 为你的每一条数据进行区间分配。使用(CASE WHEN THEN ELSE END)语句,将你的分区维度字段与每条数据关联的区间表的上下限进行比对,根据每条数据符合的条件,为其分配简单的sectionId(使用最简单的1,2,3…即可)。即在WithSectionTable的基础上多出一个sectionId字段。该字段就是我们用于聚合的字段。
  5. 重新select该WithSectionTable,使用groupId进行group by聚合,将需要计算的字段进行汇总计算。获得最终表(SummaryTable)
  6. (非必要)如果需要为每个区间命名,我的建议是再制作一个SectionNameValueTable。即用最简单的select,select两个字段,一个是sectionName,一个是sectionId,将sectionId和对应的name对应起来,有多少个区间就union多少个select语句,形成一个简单表。使用该表与SummaryTable进行关联(left join),以SectionNameValueTable为主表,groupId为关联字段,即可展示每个区间对应的区间名称。再解释一下为什么可能需要此步骤:我们汇总统计后的SummaryTable,如果某个段/区间没有统计到数据,则我们获得的表中就会少一个区间的汇总条目,而我们可能想保证每个区间都展示,即便其统计到的数据为0。也就是此时需要以区间表作为主表,从而我们创建该表,作为主表使用。

五、详细思路历程

对于较为复杂的sql需求,我们的核心思路有两点:

  1. 知道自己需要的结果集是长什么样子的(了解目标和结果)
  2. 将自己需要的数据表分别取完整后,进行关联和拼接。搞清楚各表之间的对应逻辑,
  3. 分段/分区间统计较为困难的地方在于,没有办法直接通过某个字段进行group by分组统计。这也就意味着我们需要一个字段,来标识每条数据属于哪个区间,从而通过该字段进行分组统计。
  4. 将数据分组转化为标识分组思路,将统计与分组分两步走。
  5. 建议构造对应的groupId与groupName映射表,便于观察和理解思路。
  6. sql细节多加注意,该使用索引处使用索引,该判断非空处判断非空
SQL关于 WHEN_THEN 区间划分的坑点 SQL关于 WHEN_THEN 区间划分的坑点构造测试数据case_when 划分区间方案一查询代码查询结果方案二查询代码查询结果方案三查询代码查询结果总结附录 构造测试数据 本文用子查询的形式构造测试数据,朋友们也可以通过 CREAT_ETABLE 进行构造测试数据。 -- 假设某店铺上个月卖出去了5款iPhone手机,以下是具体型号和销售数量 SELECT 'iPhone 1' AS type ,10 AS num UNION SELECT 'iPhone 2' AS type 阅读详情

相关推荐

sql连接2张表_SQL:多表查询

一,表的加法在原有school数据库里再创建一张跟course结构相同的表course1,可以【右击course】,【点复制表】-【选结构和顺序】,然后把course1里按照要求修改数据完成操作后发现course和course1 结构是一样的,列和列的数据类型是一致的,不同的是红色框的数据练习:将course和course1相加,用union将两张表的查询语句结合一起文氏图:加法(Union)图一...

weixin_42423817的博客 8246

sql 查询之分段统计

sql 分段统计 sql 分段统计数量,总数等 前言 使用sql 快速的进行分段统计 一、sql 分段统计长度 根据不同的管径统计总的长度 如: sql 执行语句: select case when PIPECALIBER between 0 and 300 then '0-300' when PIPECALIBER between 300 and 600 then '300-600' when PIPECALIBER between 600 and 900 then '6..

qq_41722795的博客 9332

SQL进阶技巧:如何分区间或分时段统计数据?| 等距分桶问题应用

0 需求 有一批交易数据,并希望每5秒中汇总一下这些数据。trx_log数据如下: trx_id trx_date trx_cnt 1 28-JUL-2005 19:03:07 44 2 28-JUL-2005 19:03:08 18 3 28-JUL-2005 19:03:09 23 4 28-JUL-2005 19:03:10 29 5 28-JUL-2005 19:03:11 27 6 28-JU

石榴姐yyds 3405

MySQL,使用SQL语句进行区间分组统计查询

区间分组统计查询需要使用到 elt,interval 一、根据学生的分数段统计各个分数段的学生总数 create table class ( id int(11) not null auto_increment, score int(11) default 0, primary key(id) )engine=innodb auto_increment=21 default charset=utf...

weixin_45310179的博客 1万+

ilqr算法原理推导及代码实践

ilqr算法原理详解

Forwarding3的博客 1741

Allegro17.2 出gerber文件详细流程 - 干货

Allegro17.2 出gerber文件详细流程 - 干货

u010614434的博客 1万+

三张表关联查询统计_SQL多表查询

真实的数据操作中,通常要获取的信息横跨多个表格,这个时候就要用到SQL多表查询,建立表格和表格的关联关系。一、表的加法即两张表加在一起。使用union 关键字,将两个表格查询的结果相加,在相加时会将重复的数据删除。如果想保留重复行,使用union all关键字,会将所有记录相加。如下图,清晰的显示两张课程表相加之后的结果。练习:合并两个表course和course1:SQL语句:运行结果:二、表的...

weixin_39631953的博客 1万+

KALI虚拟机挂代理教程

需要你本地挂了代理,然后通过虚拟机连接本机代理端口,不说废话了,直接说怎么做了。 1.查看选项设置中自己的小飞机的本地端口是多少:(默认是1080) 2.本机win+r打开cmd,输入ipconfig查看本地ip,我的本地ip是192.168.0.107: 2.打开kali虚拟机,编辑ProxyChains配置文件: vi /etc/proxychains.conf 3.在文件末尾的ProxyList中添加物理机本地ip和1080端口作为代理地址: 4.保存并退出即可: esc :wq 回车 5.

qq_34853514的博客 3万+

oracle 时间分段统计

oracle 时间分段统计先对时间进行分段嵌套进统计的目标其他时间段无值补零分割某段时间到某段时间 每三小时一份 先对时间进行分段 下面是每小时进行分段 select to_char(dt,'yyyy-mm-dd hh24') from ( select to_date(时间,'yyyy-mm-dd hh24') + (level-1)/24 dt from dual connet by level <= 24 ) 时间 : 可以是字符串 可以是查询出来的结果 最后 转成 to_date 后面

sinat_38981056的博客 2415

openEuler 22.03 安装及配置Gitlab容器

在 openEuler 22.03 上安装及配置Gitlab容器

轻步玉阶 3319

单细胞转录组数据分析中的基因ID注释转换问题:如何解决才能注释所有的UMIcount数据?

🏆本文收录于 《全栈 Bug 调优(实战版)》 专栏。专栏聚焦真实项目中的各类疑难 Bug,从成因剖析 → 排查路径 → 解决方案 → 预防优化全链路拆解,形成一套可复用、可沉淀的实战知识体系。无论你是初入职场的开发者,还是负责复杂项目的资深工程师,都可以在这里构建一套属于自己的「问题诊断与性能调优」方法论,助你稳步进阶、放大技术价值。

**My Coding Family** 1130

sql之分区域分段统计

sql之分区域分段统计 需求:在一个表中,有两列分别标记行政区划代码和家庭成员人数,需要得到不同乡镇的家庭成员人数在1-2人,3-4人,5-6人,6人以上的家庭数的表格 思路: 用case when对家庭成员数量做出判断,如果符合返回1,不符合返回0.再将这些1和0加起来,获得家庭数。 如果需要获得总人数可以将then 1 else 0 中的1改为CBFCYSL。 语句: ...

weixin_30840253的博客 479

2023年12月机器人技术等级考试试卷(三级理论) 试题解析

2023年12月机器人技术等级考试试卷(三级理论) 试题解析

http://www.cierobot.com 4098

sql之分段统计

sql之分段统计 需求:获取一个县所有家庭人数在1-2人,3-4人,5-6人,6人以上的家庭数的数组 思路:通过CASE WHEN 将CBFCYSL分组,然后统计数据条数。 语句: SELECT CASE WHEN CBFCYSL<=2 THEN '1-2' WHEN CBFCYSL<=4 AND CBFCYSL>2 T...

weixin_30865427的博客 698

RT-DETR改进策略【Neck】| GFPN 超越BiFPN 通过跳层连接和跨尺度连接改进RT-DETR颈部网络

本文记录的是利用颈部结构优化RT-DETR的目标检测网络模型。利用改进后的颈部网络,通过跳层连接,避免了在进行反向传播时的梯度消失问题,并且引入跨尺度连接,可以实现不同级别和层次的特征充分融合,获取足够的高层语义信息和低层空间信息交换,从而在大规模变化场景下提高检测性能。GIRAFFEDET: A HEAVY-NECK PARADIGM FOR OBJECT DETECTION-GFPN包含了上述的跳层连接(dense-link和log2nlog _{2} nlog2​n-link)以及跨尺度连接(Quee

Limiiiing的博客 2232

基于深度学习的商品标签识别系统:UI界面 + YOLOv8/v7/v6/v5代码 + 训练数据集

基于YOLO系列模型的商品标签识别系统能够有效解决商品信息提取的自动化问题。通过合理的数据集准备、模型训练和UI设计,我们可以实现一个功能强大的商品标签识别系统。希望本文提供的内容能够为大家提供有价值的帮助。

YOLO 476

sql 一段时间内相同的只统计一次_sql—多表查询

1、表的加法select`.... from 表1 union select ...... from 表2;union all 会保留两个表的重复行2、表的联结(1)交叉连接(cross join) (2)内联结(inner join): 保留两个表相同部分 (3)左联结(left join):保留左边表的全部和两个表相同的部分 (4)右联结(left join):保留右边表的全部和两个...

weixin_39796855的博客 302

迈瑞BS系列全自动生化分析仪LIS协议接口手册(内含HL7和ASTM)

迈瑞BS系列全自动生化分析仪LIS协议接口手册,内含HL7和ASTM)

sql 查询结果求和_SQL多表查询

多表查询指在多张表中获取数据,包括表的加法,表的联结,联结应用案例,case表达式四个部分。一、表的加法表course与course1的加法,unionunion all二、表的联结join表的联结共分为5种联结交叉联结、内联结、左联结、右联结、全联结交叉联结(cross join)可举例扑克牌,4*13+大小王共52张。内联结(inner join)左联结(left join)右联结(right...

weixin_39810196的博客 4315

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

IEM模型,地表粗糙度模拟

利用微波反演土壤水分时候,利用IEM模型可以实现对随机地表的模拟

join 子查询 效率_【SQL优化系列】2、SQL查询优化的思路与方法

一条查询SQL到执行的完整流程 整体流程如下: 用一条SQL举例如下:下面是一个简单的嵌套查询的SQL.第一步:会把这条 SQL 转换为一棵AST抽象语法树:第二步:会把这个AST抽象语法树转换为关系代数表达的逻辑计划:第三步:对逻辑计划进行优化: 上面是把StarsIn表与子表做了一个笛卡尔积,但是其实是可以做Join的,所以可以变换为如下查询计划(这里其实就是对逻辑计划进行优...

weixin_39630182的博客 358

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

sql 分组求和_从零学会SQL:多表查询

1、表的加法以【行】为单位将两张表“竖着”加在一起,没有的值为null【UNION】去除重复行,使用【UNION ALL】添加所有行,包括重复行。练习1、使用union添加数据练习2、使用union all添加数据2、表的联结以【列】为单位将两张表“横着”联结在一起,没有的值为null,表的联结分为:交叉联结、左联结、右联结、内联结、全联结。用的最多的是左联结、右联结和内联结,MySQL没有全联结...

weixin_39597868的博客 1078

按查询结果分组_SQL查询应用总结

一. 模糊查询知识点: like %表示任意字符串1. 查询姓’猴’的学生名单 猴%2.查询姓名中最后一个字是’猴’的学生名单 %猴3. 查询姓名中带’猴’的学生名单 %猴%4.查询姓“孟”老师的个数 count()二. 汇总分析分析方法:遇到问题---理解问题---逐步理清思路---写出SQL语句知识点:汇总函数,分组,对分组指定结果,排序的应用1. 查询课程编号是0002的总成绩2.查询选...

weixin_32216791的博客 555

sql 一段时间内相同的只统计一次_SQL——复习(常见面试题型)

用SQL解决业务问题的思路:step1:翻译成大白话step2:写出分析思路step1:写出对应的SQL语句题目的表来自创建的4个表:学生表、成绩表、课程表、教师表一、面试题型——简单查询基本语句格式:select 查询结果from 从哪张表中查找数据where查询条件;1.查询姓“猴”的学生名单2.查询姓名中最后一个字是“猴”的学生名单3.查询姓名中带“猴”的学生名单4.查询姓“孟”老师的个数二...

weixin_39654436的博客 197

sql中分别统计不同专业的人数_SQL——复习(常见面试题型)

用SQL解决业务问题的思路:step1:翻译成大白话step2:写出分析思路step1:写出对应的SQL语句题目的表来自创建的4个表:学生表、成绩表、课程表、教师表一、面试题型——简单查询基本语句格式:select 查询结果from 从哪张表中查找数据where查询条件;1.查询姓“猴”的学生名单2.查询姓名中最后一个字是“猴”的学生名单3.查询姓名中带“猴”的学生名单4.查询姓“孟”老师的个数二...

weixin_42498006的博客 5299

sql查询每科成绩的最高分_sql面试题小结

已经和小海豚熟悉起来了,通过navicat的介绍和接触。总结一下目前接触的经验,为了更高效、更快速的应对sql面试。1-3题使用表格:1.简单查询查询姓“猴”的学生名单2.查询姓名中最后一个字是猴的学生名单3.查询姓名中带“猴”的学生名单4.查询姓“孟”的老师个数2.汇总分析汇总题5.查询课程编号为“0002”的总成绩6. 查询选了课程的学生人数错误总结⚠️:此处不用按学号分组,只是查询有几个学号...

weixin_29611737的博客 3398

sql 一段时间内相同的只统计一次_SQL面试题

本章通过实战练习,演示如何进行SQL常见查询类型的应用。一、简单查询创建学校数据库的四张表:在navicat图形端创建四张表结构如下:导入数据之后结果如下:练习:1、查询姓“猴”的学生名单。2、查询姓名中最后一个字是“猴”的学生名单。3、查询姓名中带“猴”的学生名单。SQL语句:结果:4、查询姓“孟”老师的个数结果:二、汇总分析1、查询课程编号为‘0002’的总成绩2、查询选了课程的学生人数1、S...

weixin_39640762的博客 411

case when 子查询_SQL之多表查询

本章涉及以下知识点:表的加法表的联结联结应用案例case表达式本章注意事项1. 表的加法现有course和course1两张表coursecourse1如何能够将两张表以行(纵向)合并起来呢?1)union: 将两张表按行(纵向)合并起来,会将重复的数据删除2)union all: 将两张表按行(纵向)合并起来,不会将重复的数据删除注意:作为集合运算对象的表的前提是列数要一致。运算结果会导致行的增...

weixin_39734399的博客 1098
上一篇: JDK8的Stream流的Lambda表达式各类用法
下一篇: 业务场景下sql优化案例
Less_weight
博客等级 码龄9年 3粉丝 7原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值