sql - 每天注册/验证的员工人数

问题描述

我正在开发基于 Spring MVC 的 Web 应用程序，我实现了与我的客户端相关的数据的图形表示。非常简单，我需要说明每天注册和验证的注册员工和已验证员工的数量。

我写了一个下面的查询，

 SELECT monthyear 
                        ,SUM(CASE WHEN type='1' THEN total ELSE 0 END )as tot_ver 
                        ,SUM(CASE WHEN type='2' THEN total ELSE 0 END )as tot_reg   
                    FROM ( 
                          SELECT  
                            TO_CHAR(verify_date,'YYYY-mm-dd') as monthyear 
                            ,SUM(CASE WHEN verify_status=1  THEN 1 ELSE 0 END )as total 
                            ,'1' as type  
                            FROM EMPLOYEE 
                            GROUP BY TO_CHAR(verify_date,'YYYY-mm-dd')  
                        UNION ALL 
                         SELECT TO_CHAR(add_date,'YYYY-mm') as monthyear  
                         ,SUM(CASE WHEN emp_id!=0  THEN 1 ELSE 0 END )as total 
                         ,'2' as type 
                         FROM EMPLOYEE 
                        GROUP BY TO_CHAR(add_date,'YYYY-mm-dd') 
                       )  
                    where monthyear IS NOT NULL    
                    GROUP BY monthyear 
                 ORDER BY monthyear

和 JAVA 代码

 public TwoValueBean totRegisterUser() {
        String query = " SELECT monthyear"
                + "         ,SUM(CASE WHEN type='1' THEN total ELSE 0 END )as tot_ver"
                + "         ,SUM(CASE WHEN type='2' THEN total ELSE 0 END )as tot_reg  "
                + "     FROM ("
                + "           SELECT "
                + "             TO_CHAR(verify_date,'YYYY-mm') as monthyear"
                + "             ,SUM(CASE WHEN verify_status=1  THEN 1 ELSE 0 END )as total"
                + "             ,'1' as type "
                + "             FROM EMPLOYEE"
                + "             GROUP BY TO_CHAR(verify_date,'YYYY-mm') "
                + "         UNION ALL"
                + "          SELECT TO_CHAR(add_date,'YYYY-mm') as monthyear "
                + "          ,SUM(CASE WHEN emp_id!=0  THEN 1 ELSE 0 END )as total"
                + "          ,'2' as type"
                + "          FROM EMPLOYEE"
                + "         GROUP BY TO_CHAR(add_date,'YYYY-mm')"
                + "        ) "
                + "     where monthyear IS NOT NULL   "
                + "     GROUP BY monthyear"
                + "  ORDER BY monthyear";
        MapSqlParameterSource param = new MapSqlParameterSource();
        TwoValueBean bean = new TwoValueBean();
        try {
            List<TotalRegisterGraphData> list = getNamedParameterJdbcTemplate().query(query, param, new RowMapper<TotalRegisterGraphData>() {

                @Override
                public TotalRegisterGraphData mapRow(ResultSet rs, int rowNo) throws SQLException {
                    TotalRegisterGraphData regData = new TotalRegisterGraphData();
                    regData.setRegMonth(rs.getString("monthyear"));
                    regData.setTotRegister(rs.getLong("tot_reg"));
                    regData.setTotVerify(rs.getLong("tot_ver"));
                    return regData;
                }
            });
            if (list != null && list.size() > 0) {
                String reqDate = "";
                List<String> val1 = new ArrayList<String>();
                List<String> val2 = new ArrayList<String>();
                List<String> val3 = new ArrayList<String>();
                int currentVer = 0;
                int currentReg = 0;
                for (TotalRegisterGraphData data : list) {
                    currentReg += data.getTotRegister();
                    currentVer += data.getTotVerify();
                    val1.add("'" +data.getRegMonth()+ "'");
                    val2.add(String.valueOf(currentReg));
                    val3.add(String.valueOf(currentVer));
                }
                bean.setVal1(StringProcessorUtil.arrayToString(val1.toArray(new String[]{}), null));
                bean.setVal2(StringProcessorUtil.arrayToString(val2.toArray(new String[]{}), null));
                bean.setVal3(StringProcessorUtil.arrayToString(val3.toArray(new String[]{}), null));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bean;
    }
}

如您所见，查询代表注册日期获取数据，但也需要注意，它会将计数加起来，一旦用户注册它就会显示特定日期的最大计数。

我想要什么，我需要特定日期的确切注册员工人数。

例如：如果 50 名员工将在 24-11-2018 注册，则计数应显示为 50 ，而不是 50 + 所有以前的注册。

已验证员工也会出现类似情况。

注册是第一阶段（在查询中tot_reg，，保存计数）验证是最后阶段（在查询中tot_ver，保存计数）

注册可以通过add_date ，验证可以通过verify_date。

为了更清楚，我还附上了图表图像 n，其中蓝线显示已注册的员工，黑线显示已验证的员工人数，但您可以观察到，它显示了特定日期的最大人数。例如。如果有 1 人注册，则在 01-01-2018 显示 1 如果接下来两天还有 1 人注册，则显示 2 如果 2 人在 05-01-2018 注册，则显示 4

我不希望这种情况发生，我希望每天的用户注册数。

如果 10 人在 08-01-2018 注册，则应该显示 10 如果另外 20 人在 09-01-2018 注册，那么对于经过验证的员工，它同样显示 20

标签： sqlspring-mvc

您的 SQL 查询返回您请求的数据，即它返回日期（您混淆地称为月年）和当天注册/验证的员工人数

累积计数的是您的 Java 代码

更改这些：

currentReg += data.getTotRegister();
currentVer += data.getTotVerify();

对此：

currentReg = data.getTotRegister();
currentVer = data.getTotVerify();

请注意，查询中似乎存在错误：

SELECT TO_CHAR(add_date,'YYYY-mm') as monthyear --month precision, will give repeated rows
...
GROUP BY TO_CHAR(add_date,'YYYY-mm-dd') --day precision

并且您粘贴在顶部框中的查询与 java 代码中的查询不匹配

sql - 每天注册/验证的员工人数

问题描述

解决方案

推荐阅读