【SQL】使用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细节多加注意，该使用索引处使用索引，该判断非空处判断非空