如何运用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为存款类别，仅作为查询条件。)

查询结果如下：

此时要注意的是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

查询结果如下：

观察最前面那张报表示例图可知，实际的字段数据可以分为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进行双向表头报表查询

相关推荐