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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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 taskn" +
"
join table_exchange_timer_task timern" +
"
join table_exchange_timer_task_log logn" +
"
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 taskn" +
"
join table_exchange_timer_task timern" +
"
join table_exchange_timer_task_log logn" +
"
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 taskn" +
" join table_exchange_timer_task timern" +
" join table_exchange_timer_task_log logn" +
"
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 taskn" +
" join table_exchange_timer_task timern" +
" join table_exchange_timer_task_log logn" +
"
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";
}