欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

如何运用SQL进行双向表头报表查询

程序员文章站 2022-03-26 10:56:58
接到银行业务需求,要做一个如图的个人年龄区间存款统计报表: 思路: 可以看到,表头是分纵向和横向的,因此首先想到的是需要进行两次group by,而查询时想要将存款区间列作...

接到银行业务需求,要做一个如图的个人年龄区间存款统计报表:

如何运用SQL进行双向表头报表查询

思路:

可以看到,表头是分纵向和横向的,因此首先想到的是需要进行两次group by,而查询时想要将存款区间列作为纵向的表头,年龄区间作为横向表头这种样式,就要把存款类别和存款区间当成列内的数据来看,并将这两列的字段放在查询的前两位(如SELECT TYPE,BAL_ORANGE....)。

由此可以先将原始数据按照存款的区间和类别进行一次分组:

SELECT TYPE, CASE WHEN BAL < 50000 THEN '5万元以下'
WHEN BAL BETWEEN 50001 AND 100000 THEN '5-10万' 
WHEN BAL BETWEEN 100001 AND 200000 THEN '10-20万' 
WHEN BAL BETWEEN 200001 AND 300000 THEN '20-30万' 
WHEN BAL BETWEEN 300001 AND 400000 THEN '30-40万' 
WHEN BAL BETWEEN 400001 AND 500000 THEN '40-50万' 
WHEN BAL > 500000 THEN '50万以上'
END AS BAL_RANGE,
BAL, AGE, COUNT(*) COUNT_B
	FROM C_CUST_AGE
  WHERE DEPT_ID = ?
  AND TYPE = ?
	GROUP BY  TYPE, AGE, BAL

(其中的DEPT_ID可以针对不同部门获得对应的分组记录,TYPE为存款类别,仅作为查询条件。)

查询结果如下:

如何运用SQL进行双向表头报表查询

此时要注意的是COUNT_B这个字段,因为CASE WHEN语句只对BAL(余额)字段进行了分类,并作为BAL_RANGE(余额区间)附加在了原来的表上,实际上只对AGE字段进行了分组,所以此时的count_b其实只是当前年龄当前余额的客户数量,此时我们已经有了TYPE(存款类型),BAL_RANGE(余额区间)两个纵向表头,可以加入横向的的年龄区间表头了:

select type,
bal_range,
CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c,
CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b,
CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b) ELSE 0 end age31_40c,
CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b*bal) ELSE 0 end age31_40b,
CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b) ELSE 0 end age41_50c,
CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b*bal) ELSE 0 end age41_50b,
CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b) ELSE 0 end age51_60c,
CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b*bal) ELSE 0 end age51_60b,
CASE WHEN AGE > 60 then sum(count_b) ELSE 0 end age60_c,
CASE WHEN AGE > 60 then sum(count_b*bal) ELSE 0 end age60_b,
count(count_b) sum_count,
sum(count_b*bal) sum_bal
 from
(SELECT TYPE, CASE WHEN BAL < 50000 THEN '5万元以下'
WHEN BAL BETWEEN 50001 AND 100000 THEN '5-10万' 
WHEN BAL BETWEEN 100001 AND 200000 THEN '10-20万' 
WHEN BAL BETWEEN 200001 AND 300000 THEN '20-30万' 
WHEN BAL BETWEEN 300001 AND 400000 THEN '30-40万' 
WHEN BAL BETWEEN 400001 AND 500000 THEN '40-50万' 
WHEN BAL > 500000 THEN '50万以上'
END AS BAL_RANGE,
BAL, AGE, COUNT(*) COUNT_B
	FROM C_CUST_AGE
  WHERE DEPT_ID = ?
  AND TYPE = ?
	GROUP BY  TYPE, AGE, BAL)
  GROUP BY TYPE,bal_range,AGE

查询结果如下:

如何运用SQL进行双向表头报表查询

观察最前面那张报表示例图可知,实际的字段数据可以分为30岁以下的户数、30岁以下的余额、30-40岁的户数、30-40岁的余额······

由于COUNT_B只是针对同一年龄的用户数,所以

CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c

表示年龄小于30岁的所有用户数,即age_30c,但如果AGE不在小于30这列,就用0来表示

CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b

同理表示年龄小于30岁的所有用户总余额

此时查询结果会有大量的0存在,是因为还没有按照BAL_RANGE字段进行分组合并,所以最后再进行一次分组即可,最终SQL代码如下:

SELECT type,
bal_range, SUM(age_30c) age_30c, sum(age_30b) age_30b, sum(age31_40c) age31_40c, sum(age31_40b) age31_40b, sum(age41_50c) age41_50c, sum(age41_50b) age41_50b,
sum(age51_60c) age51_60c, sum(age51_60b) age51_60b, sum(age60_c) age60_c, sum(age60_b) age60_b,
sum(sum_count) sum_count, sum(sum_bal) sum_bal
FROM(select type,
bal_range,
CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b) ELSE 0 end age_30c,
CASE WHEN AGE BETWEEN 0 AND 30 then sum(count_b*bal) ELSE 0 end age_30b,
CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b) ELSE 0 end age31_40c,
CASE WHEN AGE BETWEEN 31 AND 40 then sum(count_b*bal) ELSE 0 end age31_40b,
CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b) ELSE 0 end age41_50c,
CASE WHEN AGE BETWEEN 41 AND 50 then sum(count_b*bal) ELSE 0 end age41_50b,
CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b) ELSE 0 end age51_60c,
CASE WHEN AGE BETWEEN 51 AND 60 then sum(count_b*bal) ELSE 0 end age51_60b,
CASE WHEN AGE > 60 then sum(count_b) ELSE 0 end age60_c,
CASE WHEN AGE > 60 then sum(count_b*bal) ELSE 0 end age60_b,
count(count_b) sum_count,
sum(count_b*bal) sum_bal
 from
(SELECT TYPE, CASE WHEN BAL < 50000 THEN '5万元以下'
WHEN BAL BETWEEN 50001 AND 100000 THEN '5-10万' 
WHEN BAL BETWEEN 100001 AND 200000 THEN '10-20万' 
WHEN BAL BETWEEN 200001 AND 300000 THEN '20-30万' 
WHEN BAL BETWEEN 300001 AND 400000 THEN '30-40万' 
WHEN BAL BETWEEN 400001 AND 500000 THEN '40-50万' 
WHEN BAL > 500000 THEN '50万以上'
END AS BAL_RANGE,
BAL, AGE, COUNT(*) COUNT_B
	FROM C_CUST_AGE
  WHERE DEPT_ID = ?
  AND TYPE = ?
	GROUP BY  TYPE, AGE, BAL)
  GROUP BY TYPE,bal_range,AGE)
  group by type,bal_range
  

最终结果集:

如何运用SQL进行双向表头报表查询