按天、周、月、自定义时间段统计

最新推荐文章于 2022-06-23 15:21:22 发布

最新推荐文章于 2022-06-23 15:21:22 发布 · 289 阅读

本文介绍了如何根据不同的时间类型（天、周、月、自定义时间范围）使用SQL进行任务名称、日期、成功次数、失败次数的聚合查询。

部署运行你感兴趣的模型镜像

if("day".equals(tongjiType)){ sql = "select task.task_name,to_char(sysdate, 'yyyy-mm-dd'),\n" + " count(log.timertaskid),\n" + " sum(log.successcount),\n" + " sum(log.failcount)\n" + " from table_exchange_task task\n" + " join table_exchange_timer_task timer\n" + " join table_exchange_timer_task_log log\n" + " on (timer.id = log.timertaskid) ON(task.id = timer.task_id)\n" + " where to_char(sysdate, 'yyyy-mm-dd') =\n" + " to_char(log.starttime, 'yyyy-mm-dd')\n" + " group by task.task_name"; } else if("week".equals(tongjiType)) { sql = "select task.task_name,1,\n" + " count(log.timertaskid),\n" + " sum(log.successcount),\n" + " sum(log.failcount)\n" + " from table_exchange_task task\n" + " join table_exchange_timer_task timer\n" + " join table_exchange_timer_task_log log\n" + " on (timer.id = log.timertaskid) ON(task.id = timer.task_id)\n" + " where to_char(log.starttime, 'ww') = (select to_char(sysdate, 'ww') from dual)\n" + " group by task.task_name"; } else if("month".equals(tongjiType)) { sql = "select task.task_name,\n" + " to_char(sysdate, 'yyyy-mm'),\n" + " count(log.timertaskid),\n" + " sum(log.successcount),\n" + " sum(log.failcount)\n" + " from table_exchange_task task\n" + " join table_exchange_timer_task timer\n" + " join table_exchange_timer_task_log log\n" + " on (timer.id = log.timertaskid) ON(task.id = timer.task_id)\n" + "where to_char(sysdate, 'yyyy-mm') =\n" + " to_char(log.starttime, 'yyyy-mm')\n" + "group by task.task_name"; } else if ("diy".equals(tongjiType)) { String startTime = taskLog.getStartTime().toString() ; String endTime = taskLog.getEndTime().toString() ; sql = "select task.task_name,111,\n" + " count(log.timertaskid),\n" + " sum(log.successcount),\n" + " sum(log.failcount)\n" + " from table_exchange_task task\n" + " join table_exchange_timer_task timer\n" + " join table_exchange_timer_task_log log\n" + " on (timer.id = log.timertaskid) ON(task.id = timer.task_id)\n" + "where log.starttime>to_date('" + startTime.substring(0, startTime.length()-2) + "','yyyy-mm-dd hh24:mi:ss')\n" + " and log.endtime<to_date('" + endTime.substring(0, endTime.length()-2) + "','yyyy-mm-dd hh24:mi:ss')\n" + "group by task.task_name"; }

您可能感兴趣的与本文相关的镜像