用sum case when做分类统计sql优化

原创 于 2017-11-23 15:46:03 发布 · 2.5k 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #sum #case #when

本文通过一个具体案例展示了如何通过优化SQL查询语句显著提高页面加载速度。通过对原始代码进行重构，利用CASE WHEN语句进行集中统计，减少了多次数据库访问，极大地提升了统计效率。

背景：本来一个页面涉及到多个类别的统计，最初的代码是每一个类别做一个sql来count出数目，可想而知，最后的加载速度和体验都是不好，能明显感觉至少5、6秒甚至更多的响应时间

之前的代码：

//136集团客户故障分析统计
	public List<AnalysisIsProvince> getGroupAnalysisCount(String nowmonth) throws WMSException {
		// 按136客户名称分组并计数
		
		String sql = "select sum (d.tcount) over(partition by d.team) as teamTotal,d.tcount,d.mouths,d.groupManager,d.trueName,d.provinceManager,d.team from (select a.* ,c.team from (select sum(v.tcount) tcount,v.mouths, v.groupManager,v.trueName,v.provinceManager from v_basic_fault v where v.mouths='"
		    		+ nowmonth
		    		+ "' group by  v.groupManager,v.trueName,v.provinceManager,v.mouths) a  left join t_customer c on a.trueName=c.name order by c.team) d";
		List<AnalysisIsProvince> list2 = new ArrayList<AnalysisIsProvince>();

		List<Map<String, Object>> list = jdbcDao.queryForList(sql);

		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = list.get(i);
			AnalysisIsProvince analysisIsProvince = new AnalysisIsProvince();
			analysisIsProvince.setCity((String) map.get("trueName"));//客户名称
			analysisIsProvince.setTotal(map.get("tcount")+"");//获取全省故障总数
			String team=(String)map.get("team");
			if(StringUtils.isEmpty(team)){
				team="其他";
			}
			analysisIsProvince.setTeam(team+"("+map.get("teamTotal")+")");
			
			/**
			 * 裸光纤 30B+D SDH FR ATM DDN MPLS VPN ADSL VPN VPDN IP专线 应用 普通电话
			 * 短号码/智能业务 2G/3G 其他增值 E税通 ICT/IDC SDH专线 MPLS VPN专线 ATM专线 FR专线 以太网专线
			 * LAN 裸光纤 短号码
			 * 
			 */
			String[] strings = { "SDH", "MPLS VPN", "ATM", "FR", "IP专线", "LAN", "裸光纤", "30B+D", "短号码/智能业务", "企业直线",
					"APN专线", "IDC", "其他" };
			for (int j = 0; j < strings.length; j++) {
				String sql2;
				if (strings[j].equals("MPLS VPN")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_three_level like'%MPLS%'";
				} else if (strings[j].equals("ADSL VPN")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_three_level like'%ADLS%'";
				} else if (strings[j].equals("IP专线")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_three_level like'%地市内以太网%'";
				} else if (strings[j].equals("30B+D")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_two_level like'%30%'";
				} else if (strings[j].equals("普通电话") || strings[j].equals("应用")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_three_level like'%" + strings[j]
							+ "%'";
				} else if (strings[j].equals("短号码/智能业务")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth
							+ "'and t.fault_type_three_level like' %短号码%' or t.fault_type_three_level like' %智能%' ";
				} else if (strings[j].equals("2G/3G")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_two_level like'%3G%'";
				} else if (strings[j].equals("ICT/IDC")) {
					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_two_level like'%IDC '";
				} else {

					sql2 = "select count(*)from t_basic_fault t where t.trueName='" + analysisIsProvince.getCity()
							+ "' and t.mouths='" + nowmonth + "'and t.fault_type_three_level like'%" + strings[j]
							+ "%'";
				}
				List<Map<String, Object>> list3;

				list3 = jdbcDao.queryForList(sql2);

				Map<String, Object> aMap = list3.get(0);
				List<String> aList = new ArrayList<String>();
				for (Map.Entry<String, Object> m : aMap.entrySet()) {
					aList.add(m.getValue().toString());
					switch (j) {
					case 0:
						analysisIsProvince.setData1(aList.get(0));
						break;
					case 1:
						analysisIsProvince.setData2(aList.get(0));
						break;
					case 2:
						analysisIsProvince.setData3(aList.get(0));
						break;
					case 3:
						analysisIsProvince.setData4(aList.get(0));
						break;
					case 4:
						analysisIsProvince.setData5(aList.get(0));
						break;
					case 5:
						analysisIsProvince.setData6(aList.get(0));
						break;
					case 6:
						analysisIsProvince.setData7(aList.get(0));
						break;
					case 7:
						analysisIsProvince.setData8(aList.get(0));
						break;
					case 8:
						analysisIsProvince.setData9(aList.get(0));
						break;
					case 9:
						analysisIsProvince.setData10(aList.get(0));
						break;
					case 10:
						analysisIsProvince.setData11(aList.get(0));
						break;
					case 11:
						analysisIsProvince.setData12(aList.get(0));
						break;

					}
				}

			}
			/*
			 * analysisIsProvince.setTotal(map.get("COUNT(*)"));
			 * analysisIsProvince.setCity(map.get("FAULT_CITY")); int total =
			 * Integer.parseInt(map.get("COUNT(*)"));
			 * 
			 */

			if (ObjectUtils.isNotEmpty(analysisIsProvince.getCity())) {
				List<ServiceRelation> liRelations = serviceRelationRepository
						.findByCustomerName(analysisIsProvince.getCity());
				if (liRelations.size() > 0) {

					analysisIsProvince.setData14(liRelations.get(0).getClientManager());
					analysisIsProvince.setData15(liRelations.get(0).getNetManager());
				}
				analysisIsProvince.setData13(String.valueOf(Integer.parseInt(analysisIsProvince.getTotal())
						- Integer.parseInt(analysisIsProvince.getData1())
						- Integer.parseInt(analysisIsProvince.getData2())
						- Integer.parseInt(analysisIsProvince.getData3())
						- Integer.parseInt(analysisIsProvince.getData4())
						- Integer.parseInt(analysisIsProvince.getData5())
						- Integer.parseInt(analysisIsProvince.getData6())
						- Integer.parseInt(analysisIsProvince.getData7())
						- Integer.parseInt(analysisIsProvince.getData8())
						- Integer.parseInt(analysisIsProvince.getData9())
						- Integer.parseInt(analysisIsProvince.getData10())
						- Integer.parseInt(analysisIsProvince.getData11())
						- Integer.parseInt(analysisIsProvince.getData12()))

				);

				list2.add(analysisIsProvince);
			}
		}
		return list2;
	}
优化之后的代码： 

public List<AnalysisIsProvince> getGroupAnalysisCount(String nowmonth) throws WMSException {
		// 按136客户名称分组并计数
		String sql = "select sum (d.tcount) over(partition by d.team) as teamTotal,d.mouths,d.groupManager,d.provinceManager,d.team,d.tcount,s.* from (select a.* ,c.team from (select sum(v.tcount) tcount,v.mouths, v.groupManager,v.trueName,v.provinceManager from v_basic_fault v where v.mouths='"
				+ nowmonth + "' group by  v.groupManager,v.trueName,v.provinceManager,v.mouths) a "
				+ " left join t_customer c on a.trueName=c.name order by c.team) d" + " left join （ SELECT t.truename,"
				+ " SUM(CASE  WHEN  t.fault_type_three_level like'%SDH%' THEN 1 ELSE 0 END ) AS data1 ,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%MPLS%' THEN 1 ELSE 0 END ) as  data2,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%ADLS%' THEN 1 ELSE 0 END ) as  data16,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%ATM%' THEN 1 ELSE 0 END ) as  data3,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%FR%' THEN 1 ELSE 0 END ) as  data4,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%地市内以太网%' THEN 1 ELSE 0 END ) as  data5,"
				+ "SUM(CASE  WHEN  t.fault_type_three_level like'%LAN%' THEN 1 ELSE 0 END ) AS data6 ,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%裸光纤%' THEN 1 ELSE 0 END ) as  data7,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%30%' THEN 1 ELSE 0 END ) as  data8,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%普通电话%' THEN 1 ELSE 0 END ) as  data17,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like' %短号码%' or t.fault_type_three_level like' %智能%' THEN 1 ELSE 0 END ) as  data9,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%企业直线%' THEN 1 ELSE 0 END ) as  data10,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%APN专线%' THEN 1 ELSE 0 END ) as  data11,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like' %IDC%' THEN 1 ELSE 0 END ) as  data12,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like' %VPDN%' THEN 1 ELSE 0 END ) as  data18,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like' %3G%' THEN 1 ELSE 0 END ) as  data19,"
				+ "SUM(CASE  WHEN t.fault_type_three_level like'%其他%' THEN 1 ELSE 0 END ) as  data13 "
				+ "FROM  t_basic_fault t  where t.mouths='" + nowmonth
				+ "' group by t.truename）s on d.trueName=s.trueName";
		List<AnalysisIsProvince> list2 = new ArrayList<AnalysisIsProvince>();
		List<Map<String, Object>> list = jdbcDao.queryForList(sql);
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = list.get(i);
			AnalysisIsProvince analysisIsProvince = new AnalysisIsProvince();
			analysisIsProvince.setCity((String) map.get("trueName"));// 客户名称
			analysisIsProvince.setTotal(map.get("tcount") + "");// 获取全省故障总数
			analysisIsProvince.setData14((String) map.get("provinceManager"));// 客户经理
			analysisIsProvince.setData15((String) map.get("groupManager"));// 网络服务经理
			String team = (String) map.get("team");
			if (StringUtils.isEmpty(team)) {
				team = "其他";
			}
			analysisIsProvince.setTeam(team + "(" + map.get("teamTotal") + ")");
			analysisIsProvince.setData1(map.get("data1") + "");
			analysisIsProvince.setData2(map.get("data2") + "");
			analysisIsProvince.setData3(map.get("data3") + "");
			analysisIsProvince.setData4(map.get("data4") + "");
			analysisIsProvince.setData5(map.get("data5") + "");
			analysisIsProvince.setData6(map.get("data6") + "");
			analysisIsProvince.setData7(map.get("data7") + "");
			analysisIsProvince.setData8(map.get("data8") + "");
			analysisIsProvince.setData9(map.get("data9") + "");
			analysisIsProvince.setData10(map.get("data10") + "");
			analysisIsProvince.setData11(map.get("data11") + "");
			analysisIsProvince.setData12(map.get("data12") + "");
			analysisIsProvince.setData13(map.get("data13") + "");
			analysisIsProvince.setData16(map.get("data16") + "");
			analysisIsProvince.setData17(map.get("data17") + "");
			analysisIsProvince.setData18(map.get("data18") + "");
			analysisIsProvince.setData19(map.get("data19") + "");
			list2.add(analysisIsProvince);
		}
		return list2;
	}
优化之后：代码量明显减少，页面加载速度明显加快，我之前也是不知道这种语句，偶然在搜索的时候才发现，感觉这种做分类统计真的太好用了，一句sql即可



评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序媛七分

随多随少随你心意^-^

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值