database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 29498 게시물 읽기
No. 29498
통계 내는 쿼리가 궁금합니다.
작성자
궁금이
작성일
2010-01-07 15:54
조회수
6,865

테이블에 그룹코드, 컴코드, 키코드 이렇게 있습니다.

그룹 4번인 통계를 잡아야 하는데 통계치를 컴코드별 혹은 키코드별로 요구하는게 아니고

컴코드는 01부터 07까지 있고요

키코드는 19부터 28까지 있습니다.

통계를 이렇게 구하고 싶습니다.

1.컴코드 01-03까지는 키코드 19부터 21번, 26번을 하나로 묶어서 갯수

2. 04에 해당하는 모든 키코드의 갯수 + 01-03 컴에의 키코드값 27번

3. 05에 해당하는 모든 키코드의 갯수 + 01-03 컴에의 키코드값 25번

4. 06에 해당하는 모든 키코드의 갯수

5. 07에 해당하는 모든 키코드의 갯수 + 01-03 컴에의 키코드값 28번

이렇게 해서

그룹핑, 갯수
=============================
그룹핑1(01-03), 4533

그룹핑2(04), 543

그룹핑3(05), 73

그룹핑4(06), 23
=============================

이것도 날짜별로 해야하는데 시간도 0-24시까지가 아니라 08시부터 다음날 08시 까지로 함수도 못쓰고 따로 통계를 내어야 할것 같습니다.

혹시 프로그램하고 짬뽕으로 구현해도 되니까 쉽고 빠른 쿼리나

구현방법 힌트좀 주시면 감사하겠습니다.

grpCode,comCode,keyIdx,CONCAT(di.creDate, ' ', di.creTime)
4,01,19,2010-01-03 12:42:48
4,01,19,2010-01-03 12:59:00
4,01,19,2010-01-03 13:02:49
4,01,19,2010-01-03 13:03:19
4,01,19,2010-01-03 13:14:15
4,01,19,2010-01-03 13:15:12
4,01,19,2010-01-03 13:15:21
4,01,19,2010-01-03 13:21:44
4,01,19,2010-01-03 13:22:19
4,01,19,2010-01-03 14:38:50
4,01,19,2010-01-03 15:41:04
4,01,19,2010-01-03 15:52:48
4,01,19,2010-01-03 17:22:19
4,01,19,2010-01-03 18:23:56
4,01,19,2010-01-03 19:01:59
4,01,20,2010-01-03 13:03:08
4,01,20,2010-01-03 13:22:28
4,01,20,2010-01-03 14:41:26
4,01,20,2010-01-03 14:58:50
4,01,20,2010-01-03 15:02:50
4,01,20,2010-01-03 15:06:32
4,01,20,2010-01-03 16:10:19
4,01,20,2010-01-03 16:12:03
4,01,20,2010-01-03 16:28:56
4,01,20,2010-01-03 16:31:26
4,01,20,2010-01-03 16:34:06
4,01,20,2010-01-03 17:15:24
4,01,20,2010-01-03 17:21:00
4,01,20,2010-01-03 18:05:15
4,01,20,2010-01-03 19:10:02
4,01,20,2010-01-03 19:29:16
4,01,20,2010-01-03 19:47:50
4,01,20,2010-01-03 20:11:16
4,01,20,2010-01-03 20:49:49
4,01,20,2010-01-03 21:16:42
4,01,20,2010-01-03 21:18:39
4,01,21,2010-01-03 14:28:01
4,01,21,2010-01-03 14:39:02
4,01,21,2010-01-03 14:39:12
4,01,21,2010-01-03 14:40:47
4,01,21,2010-01-03 14:45:11
4,01,21,2010-01-03 14:45:19
4,01,21,2010-01-03 14:49:09
4,01,21,2010-01-03 14:50:40
4,01,21,2010-01-03 14:51:08
4,01,21,2010-01-03 14:52:00
4,01,21,2010-01-03 14:54:12
4,01,21,2010-01-03 14:55:33
4,01,21,2010-01-03 14:55:54
4,01,21,2010-01-03 14:57:37
4,01,21,2010-01-03 14:58:17
4,01,21,2010-01-03 14:58:34
4,01,21,2010-01-03 14:59:14
4,01,21,2010-01-03 14:59:40
4,01,21,2010-01-03 15:00:15
4,01,21,2010-01-03 15:04:42
4,01,21,2010-01-03 15:07:59
4,01,21,2010-01-03 15:08:08
4,01,21,2010-01-03 15:10:45
4,01,21,2010-01-03 15:11:06
4,01,21,2010-01-03 15:11:22
4,01,21,2010-01-03 15:21:27
4,01,21,2010-01-03 15:22:24
4,01,21,2010-01-03 15:23:54
4,01,21,2010-01-03 15:26:34
4,01,21,2010-01-03 15:28:08
4,01,21,2010-01-03 15:29:53
4,01,21,2010-01-03 15:30:39
4,01,21,2010-01-03 15:32:31
4,01,21,2010-01-03 15:41:01
4,01,21,2010-01-03 15:52:01
4,01,21,2010-01-03 15:52:48
4,01,21,2010-01-03 16:08:01
4,01,21,2010-01-03 16:08:01
4,01,21,2010-01-03 16:08:57
4,01,21,2010-01-03 16:08:57
4,01,21,2010-01-03 16:30:17
4,01,21,2010-01-03 16:30:18
4,01,21,2010-01-03 17:02:06
4,01,21,2010-01-03 18:05:15
4,01,21,2010-01-03 19:01:57
4,01,21,2010-01-03 20:03:21
4,01,21,2010-01-03 20:06:21
4,01,21,2010-01-03 20:25:50
4,01,21,2010-01-03 21:18:38
4,01,21,2010-01-03 21:26:02
4,01,21,2010-01-03 22:58:54
4,01,21,2010-01-03 22:58:54
4,01,25,2010-01-03 12:36:35
4,01,25,2010-01-03 15:56:56
4,01,25,2010-01-03 16:14:48
4,01,25,2010-01-03 18:04:01
4,01,25,2010-01-03 18:18:20
4,01,25,2010-01-03 20:04:01
4,01,25,2010-01-03 20:41:10
4,01,25,2010-01-03 20:41:10
4,01,25,2010-01-03 20:50:28
4,01,25,2010-01-03 22:13:05
4,01,26,2010-01-03 12:43:55
4,01,26,2010-01-03 15:52:56
4,01,26,2010-01-03 15:52:56
4,01,26,2010-01-03 15:53:03
4,01,26,2010-01-03 15:59:44
4,01,26,2010-01-03 16:06:20
4,01,26,2010-01-03 16:34:05
4,01,27,2010-01-03 16:05:48
4,01,27,2010-01-03 16:39:03
4,01,27,2010-01-03 17:53:27
4,01,28,2010-01-03 08:34:04
4,01,28,2010-01-03 08:43:52
4,01,28,2010-01-03 10:01:32
4,01,28,2010-01-03 10:38:24
4,01,28,2010-01-03 10:41:21
4,01,28,2010-01-03 11:01:18
4,01,28,2010-01-03 11:14:20
4,01,28,2010-01-03 11:17:55
4,01,28,2010-01-03 11:18:17
4,01,28,2010-01-03 11:21:53
4,01,28,2010-01-03 12:04:39
4,01,28,2010-01-03 13:43:41
4,01,28,2010-01-03 14:37:13
4,01,28,2010-01-03 14:59:39
4,01,28,2010-01-03 18:50:04
4,01,28,2010-01-03 18:51:32
4,01,28,2010-01-03 21:32:25
4,01,28,2010-01-04 07:39:16
4,01,28,2010-01-04 07:44:23
4,02,19,2010-01-03 12:08:30
4,02,19,2010-01-03 15:25:32
4,02,19,2010-01-03 15:26:05
4,02,19,2010-01-03 15:28:19
4,02,19,2010-01-03 15:49:35
4,02,19,2010-01-03 15:51:02
4,02,19,2010-01-03 15:56:55
4,02,19,2010-01-03 16:02:27
4,02,19,2010-01-03 16:34:58
4,02,19,2010-01-03 16:36:51
4,02,19,2010-01-03 16:54:06
4,02,19,2010-01-03 17:22:23
4,02,19,2010-01-03 20:28:17
4,02,19,2010-01-03 20:50:22
4,02,20,2010-01-03 12:46:45
4,02,20,2010-01-03 12:58:54
4,02,20,2010-01-03 13:16:23
4,02,20,2010-01-03 13:20:14
4,02,20,2010-01-03 14:23:42
4,02,20,2010-01-03 14:26:25
4,02,20,2010-01-03 14:32:32
4,02,20,2010-01-03 14:38:35
4,02,20,2010-01-03 15:58:30
4,02,20,2010-01-03 16:50:03
4,02,20,2010-01-03 17:22:50
4,02,20,2010-01-03 17:25:53
4,02,20,2010-01-03 19:37:02
4,02,20,2010-01-03 19:49:28
4,02,20,2010-01-03 20:34:27
4,02,21,2010-01-03 12:46:09
4,02,21,2010-01-03 13:29:50
4,02,21,2010-01-03 13:36:08
4,02,21,2010-01-03 14:11:31
4,02,21,2010-01-03 14:38:00
4,02,21,2010-01-03 14:40:21
4,02,21,2010-01-03 14:40:47
4,02,21,2010-01-03 14:47:45
4,02,21,2010-01-03 14:47:55
4,02,21,2010-01-03 14:48:56
4,02,21,2010-01-03 14:49:06
4,02,21,2010-01-03 14:50:52
4,02,21,2010-01-03 14:52:20
4,02,21,2010-01-03 14:53:32
4,02,21,2010-01-03 14:54:43
4,02,21,2010-01-03 14:55:11
4,02,21,2010-01-03 14:55:43
4,02,21,2010-01-03 14:56:40
4,02,21,2010-01-03 14:57:10
4,02,21,2010-01-03 14:59:29
4,02,21,2010-01-03 15:00:14
4,02,21,2010-01-03 15:00:23
4,02,21,2010-01-03 15:01:10
4,02,21,2010-01-03 15:04:54
4,02,21,2010-01-03 15:05:09
4,02,21,2010-01-03 15:05:21
4,02,21,2010-01-03 15:05:30
4,02,21,2010-01-03 15:06:55
4,02,21,2010-01-03 15:07:43
4,02,21,2010-01-03 15:15:30
4,02,21,2010-01-03 15:18:04
4,02,21,2010-01-03 15:18:51
4,02,21,2010-01-03 15:19:09
4,02,21,2010-01-03 15:21:59
4,02,21,2010-01-03 15:23:32
4,02,21,2010-01-03 15:23:57
4,02,21,2010-01-03 15:25:28
4,02,21,2010-01-03 15:25:43
4,02,21,2010-01-03 15:26:05
4,02,21,2010-01-03 15:28:19
4,02,21,2010-01-03 15:28:36
4,02,21,2010-01-03 15:29:09
4,02,21,2010-01-03 15:34:04
4,02,21,2010-01-03 15:51:01
4,02,21,2010-01-03 15:54:25
4,02,21,2010-01-03 15:56:55
4,02,21,2010-01-03 15:58:30
4,02,21,2010-01-03 16:02:27
4,02,21,2010-01-03 16:11:13
4,02,21,2010-01-03 16:17:20
4,02,21,2010-01-03 16:50:02
4,02,21,2010-01-03 16:54:06
4,02,21,2010-01-03 17:18:50
4,02,21,2010-01-03 17:20:15
4,02,21,2010-01-03 19:43:45
4,02,21,2010-01-03 20:20:40
4,02,21,2010-01-03 20:28:15
4,02,21,2010-01-03 20:37:33
4,02,21,2010-01-03 20:45:04
4,02,21,2010-01-03 20:50:20
4,02,21,2010-01-03 20:59:53
4,02,25,2010-01-03 13:03:16
4,02,25,2010-01-03 14:03:17
4,02,25,2010-01-03 16:56:33
4,02,25,2010-01-03 18:03:00
4,02,25,2010-01-03 18:03:01
4,02,25,2010-01-03 18:04:30
4,02,25,2010-01-03 18:04:31
4,02,25,2010-01-03 18:19:10
4,02,25,2010-01-03 18:19:10
4,02,25,2010-01-03 18:40:55
4,02,25,2010-01-03 18:40:56
4,02,26,2010-01-03 13:37:06
4,02,26,2010-01-03 13:37:44
4,02,26,2010-01-03 13:39:10
4,02,26,2010-01-03 13:40:42
4,02,26,2010-01-03 13:52:25
4,02,26,2010-01-03 14:11:09
4,02,26,2010-01-03 14:16:14
4,02,26,2010-01-03 14:24:26
4,02,26,2010-01-03 14:32:25
4,02,26,2010-01-03 14:33:36
4,02,26,2010-01-03 14:43:10
4,02,26,2010-01-03 14:45:11
4,02,26,2010-01-03 17:01:31
4,02,26,2010-01-03 17:01:31
4,02,27,2010-01-03 12:27:13
4,02,27,2010-01-03 12:46:24
4,02,27,2010-01-03 12:48:34
4,02,27,2010-01-03 12:52:12
4,02,27,2010-01-03 12:58:29
4,02,27,2010-01-03 13:03:40
4,02,27,2010-01-03 13:07:14
4,02,27,2010-01-03 13:12:45
4,02,27,2010-01-03 14:37:43
4,02,27,2010-01-03 15:15:13
4,02,27,2010-01-03 16:17:09
4,02,27,2010-01-03 16:22:13
4,02,27,2010-01-03 16:30:54
4,02,27,2010-01-03 16:34:00
4,02,27,2010-01-03 16:35:19
4,02,27,2010-01-03 17:13:55
4,02,27,2010-01-03 17:26:02
4,02,27,2010-01-03 17:37:01
4,02,27,2010-01-03 17:55:56
4,02,27,2010-01-03 18:14:22
4,02,27,2010-01-03 18:20:27
4,02,28,2010-01-03 12:05:27
4,02,28,2010-01-03 12:23:39
4,02,28,2010-01-03 12:59:13
4,03,19,2010-01-03 10:24:18
4,03,19,2010-01-03 12:26:02
4,03,19,2010-01-03 12:32:14
4,03,19,2010-01-03 12:33:21
4,03,19,2010-01-03 12:50:11
4,03,19,2010-01-03 12:56:04
4,03,19,2010-01-03 13:02:05
4,03,19,2010-01-03 13:05:09
4,03,19,2010-01-03 13:05:22
4,03,19,2010-01-03 13:12:02
4,03,19,2010-01-03 13:50:42
4,03,19,2010-01-03 14:21:10
4,03,19,2010-01-03 14:32:53
4,03,19,2010-01-03 14:45:28
4,03,19,2010-01-03 15:03:19
4,03,19,2010-01-03 15:37:13
4,03,19,2010-01-03 15:54:39
4,03,19,2010-01-03 16:07:49
4,03,19,2010-01-03 16:09:44
4,03,19,2010-01-03 16:33:33
4,03,19,2010-01-03 19:37:06
4,03,19,2010-01-03 19:55:03
4,03,19,2010-01-03 20:44:24
4,03,19,2010-01-03 20:46:03
4,03,19,2010-01-03 20:47:13
4,03,19,2010-01-03 20:48:17
4,03,19,2010-01-03 20:56:30
4,03,19,2010-01-03 21:12:35
4,03,19,2010-01-04 00:26:04
4,03,20,2010-01-03 08:45:24
4,03,20,2010-01-03 09:38:11
4,03,20,2010-01-03 12:42:31
4,03,20,2010-01-03 12:52:23
4,03,20,2010-01-03 13:11:20
4,03,20,2010-01-03 13:18:19
4,03,20,2010-01-03 13:18:55
4,03,20,2010-01-03 13:22:00
4,03,20,2010-01-03 13:24:04
4,03,20,2010-01-03 13:39:29
4,03,20,2010-01-03 14:07:12
4,03,20,2010-01-03 14:27:19
4,03,20,2010-01-03 14:30:17
4,03,20,2010-01-03 14:45:07
4,03,20,2010-01-03 14:50:16
4,03,20,2010-01-03 14:51:12
4,03,20,2010-01-03 15:01:31
4,03,20,2010-01-03 15:06:48
4,03,20,2010-01-03 15:10:17
4,03,20,2010-01-03 15:17:47
4,03,20,2010-01-03 15:30:57
4,03,20,2010-01-03 16:46:56
4,03,20,2010-01-03 16:47:05
4,03,20,2010-01-03 16:50:59
4,03,20,2010-01-03 17:12:38
4,03,20,2010-01-03 17:24:06
4,03,20,2010-01-03 19:06:32
4,03,20,2010-01-03 19:35:30
4,03,20,2010-01-03 19:51:05
4,03,20,2010-01-03 19:56:05
4,03,20,2010-01-03 21:08:38
4,03,20,2010-01-03 21:36:46
4,03,20,2010-01-03 21:45:00
4,03,20,2010-01-03 22:10:07
4,03,20,2010-01-03 22:11:08
4,03,20,2010-01-03 22:16:54
4,03,20,2010-01-04 02:10:04
4,03,21,2010-01-03 12:58:22
4,03,21,2010-01-03 12:58:49
4,03,21,2010-01-03 13:50:42
4,03,21,2010-01-03 13:57:45
4,03,21,2010-01-03 14:11:06
4,03,21,2010-01-03 14:21:09
4,03,21,2010-01-03 14:34:55
4,03,21,2010-01-03 14:35:29
4,03,21,2010-01-03 14:38:57
4,03,21,2010-01-03 14:42:22
4,03,21,2010-01-03 14:42:40
4,03,21,2010-01-03 14:58:52
4,03,21,2010-01-03 14:59:03
4,03,21,2010-01-03 14:59:53
4,03,21,2010-01-03 15:00:33
4,03,21,2010-01-03 15:02:50
4,03,21,2010-01-03 15:05:45
4,03,21,2010-01-03 15:07:17
4,03,21,2010-01-03 15:18:49
4,03,21,2010-01-03 15:41:26
4,03,21,2010-01-03 15:42:23
4,03,21,2010-01-03 16:04:32
4,03,21,2010-01-03 16:07:48
4,03,21,2010-01-03 16:09:44
4,03,21,2010-01-03 16:33:32
4,03,21,2010-01-03 16:50:22
4,03,21,2010-01-03 16:56:24
4,03,21,2010-01-03 16:56:35
4,03,21,2010-01-03 19:15:01
4,03,21,2010-01-03 19:22:22
4,03,21,2010-01-03 19:27:11
4,03,21,2010-01-03 19:37:39
4,03,21,2010-01-03 19:39:47
4,03,21,2010-01-03 19:43:04
4,03,21,2010-01-03 19:49:44
4,03,21,2010-01-03 20:08:48
4,03,21,2010-01-03 20:09:56
4,03,21,2010-01-03 20:14:28
4,03,21,2010-01-03 20:22:08
4,03,21,2010-01-03 20:35:52
4,03,21,2010-01-03 20:36:23
4,03,21,2010-01-03 20:44:02
4,03,21,2010-01-03 20:44:24
4,03,21,2010-01-03 20:47:12
4,03,21,2010-01-03 20:47:57
4,03,21,2010-01-03 20:48:17
4,03,21,2010-01-03 20:50:11
4,03,21,2010-01-03 20:51:59
4,03,21,2010-01-03 20:56:17
4,03,21,2010-01-03 20:56:30
4,03,21,2010-01-03 21:06:18
4,03,21,2010-01-03 21:12:34
4,03,21,2010-01-03 21:17:11
4,03,21,2010-01-03 21:45:00
4,03,21,2010-01-03 21:48:29
4,03,21,2010-01-03 21:48:38
4,03,21,2010-01-03 21:54:31
4,03,21,2010-01-03 21:57:16
4,03,21,2010-01-03 22:00:05
4,03,21,2010-01-03 22:10:06
4,03,21,2010-01-03 22:11:07
4,03,21,2010-01-04 00:26:04
4,03,21,2010-01-04 02:10:02
4,03,25,2010-01-03 08:47:58
4,03,25,2010-01-03 08:51:35
4,03,25,2010-01-03 09:05:32
4,03,25,2010-01-03 09:11:31
4,03,25,2010-01-03 09:14:56
4,03,25,2010-01-03 09:40:24
4,03,25,2010-01-03 09:47:16
4,03,25,2010-01-03 10:03:46
4,03,25,2010-01-03 10:19:13
4,03,25,2010-01-03 10:57:37
4,03,25,2010-01-03 11:12:15
4,03,25,2010-01-03 11:12:26
4,03,25,2010-01-03 11:16:18
4,03,25,2010-01-03 11:22:20
4,03,25,2010-01-03 11:35:20
4,03,25,2010-01-03 11:37:51
4,03,25,2010-01-03 11:39:31
4,03,25,2010-01-03 11:44:09
4,03,25,2010-01-03 11:45:39
4,03,25,2010-01-03 11:49:41
4,03,25,2010-01-03 11:54:44
4,03,25,2010-01-03 11:58:49
4,03,25,2010-01-03 12:04:25
4,03,25,2010-01-03 12:07:00
4,03,25,2010-01-03 12:11:01
4,03,25,2010-01-03 12:28:38
4,03,25,2010-01-03 12:34:25
4,03,25,2010-01-03 12:38:08
4,03,25,2010-01-03 12:38:50
4,03,25,2010-01-03 12:54:18
4,03,25,2010-01-03 13:09:42
4,03,25,2010-01-03 13:14:54
4,03,25,2010-01-03 13:19:03
4,03,25,2010-01-03 13:28:13
4,03,25,2010-01-03 13:34:47
4,03,25,2010-01-03 13:37:25
4,03,25,2010-01-03 13:43:39
4,03,25,2010-01-03 13:53:07
4,03,25,2010-01-03 13:53:15
4,03,25,2010-01-03 14:06:06
4,03,25,2010-01-03 14:07:01
4,03,25,2010-01-03 14:08:40
4,03,25,2010-01-03 14:42:13
4,03,25,2010-01-03 14:47:14
4,03,25,2010-01-03 14:56:27
4,03,25,2010-01-03 14:58:26
4,03,25,2010-01-03 14:58:26
4,03,25,2010-01-03 15:06:15
4,03,25,2010-01-03 15:06:15
4,03,25,2010-01-03 15:17:25
4,03,25,2010-01-03 15:17:26
4,03,25,2010-01-03 15:17:32
4,03,25,2010-01-03 15:17:33
4,03,25,2010-01-03 15:20:36
4,03,25,2010-01-03 15:20:37
4,03,25,2010-01-03 15:25:47
4,03,25,2010-01-03 15:28:06
4,03,25,2010-01-03 15:39:45
4,03,25,2010-01-03 15:39:45
4,03,25,2010-01-03 15:47:10
4,03,25,2010-01-03 16:01:41
4,03,25,2010-01-03 16:08:16
4,03,25,2010-01-03 16:08:16
4,03,25,2010-01-03 16:15:35
4,03,25,2010-01-03 16:20:20
4,03,25,2010-01-03 16:26:01
4,03,25,2010-01-03 16:29:00
4,03,25,2010-01-03 16:48:27
4,03,25,2010-01-03 16:48:33
4,03,25,2010-01-03 16:49:54
4,03,25,2010-01-03 16:49:54
4,03,25,2010-01-03 16:51:49
4,03,25,2010-01-03 16:51:49
4,03,25,2010-01-03 16:52:53
4,03,25,2010-01-03 16:55:05
4,03,25,2010-01-03 16:57:20
4,03,25,2010-01-03 16:57:21
4,03,25,2010-01-03 17:09:29
4,03,25,2010-01-03 17:26:29
4,03,25,2010-01-03 17:26:29
4,03,25,2010-01-03 17:31:02
4,03,25,2010-01-03 17:31:50
4,03,25,2010-01-03 17:38:35
4,03,25,2010-01-03 17:51:38
4,03,25,2010-01-03 18:11:10
4,03,25,2010-01-03 18:27:47
4,03,25,2010-01-03 18:27:47
4,03,25,2010-01-03 18:41:34
4,03,25,2010-01-03 18:41:35
4,03,25,2010-01-03 18:48:59
4,03,25,2010-01-03 18:57:49
4,03,25,2010-01-03 18:59:17
4,03,25,2010-01-03 19:20:52
4,03,25,2010-01-03 19:21:29
4,03,25,2010-01-03 19:22:06
4,03,25,2010-01-03 19:29:14
4,03,25,2010-01-03 19:30:10
4,03,25,2010-01-03 19:30:11
4,03,25,2010-01-03 19:30:13
4,03,25,2010-01-03 19:30:37
4,03,25,2010-01-03 19:33:00
4,03,25,2010-01-03 19:51:21
4,03,25,2010-01-03 20:07:22
4,03,25,2010-01-03 20:16:57
4,03,25,2010-01-03 20:20:32
4,03,25,2010-01-03 20:21:47
4,03,25,2010-01-03 20:34:16
4,03,25,2010-01-03 20:49:03
4,03,25,2010-01-03 20:54:56
4,03,25,2010-01-03 20:57:22
4,03,25,2010-01-03 21:07:31
4,03,25,2010-01-03 21:15:22
4,03,25,2010-01-03 21:15:23
4,03,25,2010-01-03 21:19:02
4,03,25,2010-01-03 21:20:44
4,03,25,2010-01-03 21:20:44
4,03,25,2010-01-03 21:48:16
4,03,25,2010-01-03 21:51:24
4,03,25,2010-01-03 21:55:28
4,03,25,2010-01-03 22:04:12
4,03,25,2010-01-03 22:04:12
4,03,25,2010-01-03 22:04:13
4,03,25,2010-01-03 22:05:37
4,03,25,2010-01-03 22:05:56
4,03,25,2010-01-03 22:07:39
4,03,25,2010-01-03 22:08:15
4,03,25,2010-01-03 22:10:35
4,03,25,2010-01-03 22:41:40
4,03,25,2010-01-03 22:41:41
4,03,25,2010-01-03 22:52:26
4,03,25,2010-01-03 22:52:26
4,03,25,2010-01-03 22:52:27
4,03,25,2010-01-03 22:55:26
4,03,25,2010-01-03 22:57:27
4,03,25,2010-01-03 22:57:29
4,03,25,2010-01-03 22:57:29
4,03,25,2010-01-03 22:58:25
4,03,25,2010-01-03 22:58:26
4,03,25,2010-01-03 22:58:26
4,03,25,2010-01-03 23:20:28
4,03,25,2010-01-03 23:20:28
4,03,25,2010-01-04 01:06:01
4,03,25,2010-01-04 02:50:46
4,03,25,2010-01-04 06:47:35
4,03,25,2010-01-04 07:05:55
4,03,25,2010-01-04 07:08:02
4,03,25,2010-01-04 07:10:47
4,03,25,2010-01-04 07:19:12
4,03,25,2010-01-04 07:19:55
4,03,25,2010-01-04 07:39:09
4,03,25,2010-01-04 07:43:49
4,03,25,2010-01-04 07:45:02
4,03,25,2010-01-04 07:52:52
4,03,25,2010-01-04 07:57:16
4,03,25,2010-01-04 07:57:30
4,03,26,2010-01-03 15:05:55
4,03,26,2010-01-03 15:06:21
4,03,26,2010-01-03 15:06:36
4,03,26,2010-01-03 15:07:40
4,03,26,2010-01-03 15:07:41
4,03,26,2010-01-03 15:07:59
4,03,26,2010-01-03 15:08:01
4,03,26,2010-01-03 15:08:12
4,03,26,2010-01-03 15:09:21
4,03,26,2010-01-03 15:11:24
4,03,26,2010-01-03 15:13:02
4,03,26,2010-01-03 15:13:26
4,03,26,2010-01-03 15:13:41
4,03,26,2010-01-03 15:13:41
4,03,26,2010-01-03 15:13:46
4,03,26,2010-01-03 15:13:47
4,03,26,2010-01-03 15:13:50
4,03,26,2010-01-03 15:13:55
4,03,26,2010-01-03 15:14:09
4,03,26,2010-01-03 15:15:17
4,03,26,2010-01-03 15:15:24
4,03,26,2010-01-03 15:15:32
4,03,26,2010-01-03 15:15:33
4,03,26,2010-01-03 15:15:41
4,03,26,2010-01-03 15:17:18
4,03,26,2010-01-03 15:19:02
4,03,26,2010-01-03 15:22:03
4,03,26,2010-01-03 15:22:58
4,03,26,2010-01-03 15:22:59
4,03,26,2010-01-03 15:24:59
4,03,26,2010-01-03 15:25:58
4,03,26,2010-01-03 15:25:58
4,03,26,2010-01-03 15:29:37
4,03,26,2010-01-03 15:29:38
4,03,26,2010-01-03 15:32:20
4,03,26,2010-01-03 15:32:21
4,03,26,2010-01-03 15:32:43
4,03,26,2010-01-03 15:33:54
4,03,26,2010-01-03 15:34:07
4,03,26,2010-01-03 15:48:00
4,03,26,2010-01-03 15:52:05
4,03,26,2010-01-03 15:52:15
4,03,26,2010-01-03 15:52:16
4,03,26,2010-01-03 16:07:07
4,03,26,2010-01-03 16:07:08
4,03,26,2010-01-03 16:07:35
4,03,26,2010-01-03 16:07:35
4,03,26,2010-01-03 16:09:10
4,03,26,2010-01-03 16:15:28
4,03,26,2010-01-03 16:15:44
4,03,26,2010-01-03 16:35:03
4,03,26,2010-01-03 16:35:03
4,03,26,2010-01-03 16:45:42
4,03,26,2010-01-03 16:45:42
4,03,26,2010-01-03 17:30:04
4,03,26,2010-01-03 17:30:05
4,03,26,2010-01-03 17:42:25
4,03,26,2010-01-03 17:42:49
4,03,26,2010-01-03 17:42:50
4,03,26,2010-01-03 19:53:49
4,03,26,2010-01-03 20:16:16
4,03,26,2010-01-03 20:16:16
4,03,27,2010-01-03 12:11:48
4,03,27,2010-01-03 14:49:15
4,03,27,2010-01-03 14:49:30
4,03,27,2010-01-03 14:57:51
4,03,27,2010-01-03 15:02:34
4,03,27,2010-01-03 15:02:42
4,03,27,2010-01-03 15:03:01
4,03,27,2010-01-03 15:03:32
4,03,27,2010-01-03 15:04:48
4,03,27,2010-01-03 15:18:11
4,03,27,2010-01-03 15:18:35
4,03,27,2010-01-03 15:23:35
4,03,27,2010-01-03 15:25:40
4,03,27,2010-01-03 15:26:50
4,03,27,2010-01-03 15:28:12
4,03,27,2010-01-03 15:29:11
4,03,27,2010-01-03 15:34:35
4,03,27,2010-01-03 15:38:01
4,03,27,2010-01-03 16:02:37
4,03,27,2010-01-03 16:06:57
4,03,27,2010-01-03 16:08:01
4,03,27,2010-01-03 16:14:48
4,03,27,2010-01-03 16:17:59
4,03,27,2010-01-03 16:20:51
4,03,27,2010-01-03 16:27:08
4,03,27,2010-01-03 16:28:11
4,03,27,2010-01-03 16:29:51
4,03,27,2010-01-03 16:31:02
4,03,27,2010-01-03 16:35:12
4,03,27,2010-01-03 16:36:26
4,03,27,2010-01-03 16:36:59
4,03,27,2010-01-03 16:38:15
4,03,27,2010-01-03 16:39:11
4,03,27,2010-01-03 16:42:04
4,03,27,2010-01-03 16:43:19
4,03,27,2010-01-03 16:52:40
4,03,27,2010-01-03 16:57:07
4,03,27,2010-01-03 16:59:59
4,03,27,2010-01-03 17:03:42
4,03,27,2010-01-03 17:09:09
4,03,27,2010-01-03 17:15:27
4,03,27,2010-01-03 17:16:13
4,03,27,2010-01-03 17:35:44
4,03,27,2010-01-03 18:04:52
4,03,27,2010-01-03 18:48:09
4,03,27,2010-01-03 18:49:56
4,03,27,2010-01-03 18:52:35
4,03,27,2010-01-03 18:53:07
4,03,27,2010-01-03 19:02:04
4,03,27,2010-01-03 19:02:59
4,03,27,2010-01-03 20:50:51
4,03,27,2010-01-03 20:58:20
4,03,28,2010-01-03 08:50:44
4,03,28,2010-01-03 08:58:09
4,03,28,2010-01-03 09:31:21
4,03,28,2010-01-03 09:36:40
4,03,28,2010-01-03 09:40:16
4,03,28,2010-01-03 09:52:06
4,03,28,2010-01-03 10:04:57
4,03,28,2010-01-03 10:07:12
4,03,28,2010-01-03 10:13:42
4,03,28,2010-01-03 10:15:15
4,03,28,2010-01-03 10:18:29
4,03,28,2010-01-03 10:18:44
4,03,28,2010-01-03 10:23:38
4,03,28,2010-01-03 10:25:41
4,03,28,2010-01-03 10:29:59
4,03,28,2010-01-03 10:34:18
4,03,28,2010-01-03 10:41:39
4,03,28,2010-01-03 11:09:34
4,03,28,2010-01-03 11:12:45
4,03,28,2010-01-03 11:23:38
4,03,28,2010-01-03 11:24:52
4,03,28,2010-01-03 11:27:05
4,03,28,2010-01-03 11:52:23
4,03,28,2010-01-03 11:58:28
4,03,28,2010-01-03 12:01:55
4,03,28,2010-01-03 12:02:17
4,03,28,2010-01-03 12:06:00
4,03,28,2010-01-03 12:18:07
4,03,28,2010-01-03 12:27:54
4,03,28,2010-01-03 12:33:36
4,03,28,2010-01-03 12:33:44
4,03,28,2010-01-03 12:49:09
4,03,28,2010-01-03 12:56:25
4,03,28,2010-01-03 14:17:15
4,03,28,2010-01-03 15:05:34
4,03,28,2010-01-03 15:35:22
4,03,28,2010-01-03 17:16:22
4,03,28,2010-01-03 19:28:43
4,03,28,2010-01-03 20:20:55
4,03,28,2010-01-03 20:48:09
4,03,28,2010-01-03 22:35:55
4,03,28,2010-01-03 22:44:07
4,03,28,2010-01-03 23:48:27
4,03,28,2010-01-03 23:52:58
4,03,28,2010-01-04 00:05:02
4,03,28,2010-01-04 01:01:12
4,03,28,2010-01-04 01:11:17
4,03,28,2010-01-04 05:25:42
4,03,28,2010-01-04 07:18:20
4,03,28,2010-01-04 07:44:25
4,05,19,2010-01-03 18:40:31
4,05,20,2010-01-03 14:08:12
4,05,20,2010-01-03 18:45:01
4,05,21,2010-01-03 12:12:10
4,05,21,2010-01-03 15:29:08
4,05,21,2010-01-03 18:18:45
4,05,21,2010-01-03 18:35:45
4,05,21,2010-01-03 19:18:21
4,05,21,2010-01-03 19:54:42
4,05,22,2010-01-03 16:07:17
4,05,22,2010-01-03 19:40:13
4,06,19,2010-01-03 13:45:32
4,06,19,2010-01-03 14:41:20
4,06,19,2010-01-03 14:41:27
4,06,19,2010-01-03 17:34:16
4,06,20,2010-01-03 14:20:01
4,06,20,2010-01-03 16:09:24
4,06,20,2010-01-03 17:04:45
4,06,20,2010-01-03 17:05:30
4,06,20,2010-01-03 17:06:56
4,06,20,2010-01-03 17:15:58
4,06,20,2010-01-03 19:23:25
4,06,20,2010-01-03 19:52:06
4,06,21,2010-01-03 14:22:02
4,06,21,2010-01-03 14:59:32
4,06,21,2010-01-03 15:06:51
4,06,21,2010-01-03 15:35:30
4,06,21,2010-01-03 16:13:36
4,06,21,2010-01-03 16:17:37
4,06,21,2010-01-03 16:19:51
4,06,21,2010-01-03 17:32:56
4,06,21,2010-01-03 18:17:54
4,06,21,2010-01-03 19:16:47
4,06,21,2010-01-03 20:26:57
4,07,19,2010-01-03 17:58:58
4,07,20,2010-01-03 17:58:46
4,07,20,2010-01-03 17:58:50
4,07,20,2010-01-03 17:58:54
4,07,22,2010-01-03 20:16:35
4,07,22,2010-01-04 01:53:50
4,07,22,2010-01-04 06:29:03
4,07,22,2010-01-04 06:29:07
4,07,22,2010-01-04 06:29:11
4,07,28,2010-01-03 08:52:00
4,07,28,2010-01-03 09:27:12
4,07,28,2010-01-03 09:34:35
4,07,28,2010-01-03 09:40:43
4,07,28,2010-01-03 09:57:10
4,07,28,2010-01-03 10:02:02
4,07,28,2010-01-03 10:03:11
4,07,28,2010-01-03 10:04:50
4,07,28,2010-01-03 10:06:52
4,07,28,2010-01-03 10:10:35
4,07,28,2010-01-03 10:14:33
4,07,28,2010-01-03 10:18:28
4,07,28,2010-01-03 10:29:55
4,07,28,2010-01-03 10:35:19
4,07,28,2010-01-03 10:42:09
4,07,28,2010-01-03 10:44:26
4,07,28,2010-01-03 10:48:14
4,07,28,2010-01-03 10:52:41
4,07,28,2010-01-03 10:53:59
4,07,28,2010-01-03 10:54:19
4,07,28,2010-01-03 10:56:59
4,07,28,2010-01-03 11:01:41
4,07,28,2010-01-03 11:02:54
4,07,28,2010-01-03 11:03:25
4,07,28,2010-01-03 11:09:02
4,07,28,2010-01-03 11:14:23
4,07,28,2010-01-03 11:21:13
4,07,28,2010-01-03 11:22:57
4,07,28,2010-01-03 11:25:59
4,07,28,2010-01-03 11:26:49
4,07,28,2010-01-03 11:31:02
4,07,28,2010-01-03 11:32:08
4,07,28,2010-01-03 11:33:25
4,07,28,2010-01-03 11:34:07
4,07,28,2010-01-03 11:37:11
4,07,28,2010-01-03 11:45:32
4,07,28,2010-01-03 11:46:21
4,07,28,2010-01-03 11:50:26
4,07,28,2010-01-03 11:55:37
4,07,28,2010-01-03 12:08:45
4,07,28,2010-01-03 12:08:48
4,07,28,2010-01-03 12:12:45
4,07,28,2010-01-03 12:19:27
4,07,28,2010-01-03 12:31:46
4,07,28,2010-01-03 12:41:25
4,07,28,2010-01-03 12:42:31
4,07,28,2010-01-03 13:11:01
4,07,28,2010-01-03 14:31:59
4,07,28,2010-01-03 14:32:37
4,07,28,2010-01-03 14:34:54
4,07,28,2010-01-03 15:13:14
4,07,28,2010-01-03 16:09:06
4,07,28,2010-01-03 16:42:41
4,07,28,2010-01-03 17:51:28
4,07,28,2010-01-03 18:01:45
4,07,28,2010-01-03 18:02:12
4,07,28,2010-01-03 18:35:33
4,07,28,2010-01-03 18:46:15
4,07,28,2010-01-03 20:10:44
4,07,28,2010-01-03 20:40:37
4,07,28,2010-01-03 21:28:27
4,07,28,2010-01-04 07:44:15
4,07,28,2010-01-04 07:44:40
4,07,28,2010-01-04 07:57:05

이 글에 대한 댓글이 총 4건 있습니다.

날짜별 구분은 어렵지 않습니다. creDate + creTime 값을 UNIX TIMESTAMP로 변환한 후에, 3600 * 8 만큼 값을 빼서 DATE_FORMAT으로 Y-m-d 로 변환 후 그룹핑하시면 되겠습니다. 혹은 DATE_ADD, DATE_SUB등을 사용하시면 더 편리하게 구현하실 수 있습니다.

 

한 방쿼리로는 어렵고, 각각의 쿼리를 서브쿼리로 작성하신 후에, 다시 하나로 합치는 쿼리로 작성하셔야 할 것 같습니다.

박현우(lqez)님이 2010-01-07 18:01에 작성한 댓글입니다.

답변감사합니다.

데이타가 1주일치가 있다고 하고

검색일이 01-01일 부터 01-07일까지 사용자가 입력하면

실제 통계는

01일 08:00부터 02일 07:59:59까지 하루

02일 08:00부터 03일 07:59:59까지 하루

03일 08:00부터 04일 07:59:59까지 하루

04일 08:00부터 05일 07:59:59까지 하루

05일 08:00부터 06일 07:59:59까지 하루

06일 08:00부터 07일 07:59:59까지 하루

07일 08:00부터 08일 07:59:59까지 하루

이렇게 통계를 내려면

==========================================

creDate + creTime 값을 UNIX TIMESTAMP로 변환한 후에, 3600 * 8 만큼 값을 빼서 DATE_FORMAT으로 Y-m-d 로 변환 후 그룹핑하시면 되겠습니다. 혹은 DATE_ADD, DATE_SUB등을 사용하시면 더 편리하게 구현하실 수 있습니다.

==========================================

위와 같이 하면 된다는 말씀이시죠?

언뜻 이해가 안가는데...자세하게 알려 주시면 안될까요?

지금 프로시져 만들어서 통계를 내려고 하는데

처음하는거라 진도가 잘 안나가네요.

1.컨셉은 그룹, 컴, 키코드별로 하루씩 통계내어서 새테이블에 집어 넣기

2. 여러날짜면 반복해서 인서트

3. 고객이 원하는 정보를 해당자료에서 빼서 원하는 데로 업데이트

이렇게 잡고 하는데

잘안되네요...ㅠ.ㅠ

 

궁금이님이 2010-01-09 14:15에 작성한 댓글입니다. Edit

미리 통계를 내어두고, 해당 통계만을 사용자에게 보여주는 것인지,

아니면 사용자의 다양한 요구에 따라 통계를 그때 그때 만들어야 하는 것인지에 따라 접근법이 많이 달라집니다.

 

올려주신 데이터를 '날짜' 기준으로만 통계를 낸다면, 말씀하신 대로 미리 날짜별로 만들어 두고 조합하여 보여주는 것이 가능할 것 같습니다.

 

일단, 날짜 구분시간이 오전 8시이므로, 통계를 내실 때 위의 댓글에서 말씀드렸던 것과 같이, 쿼리문에 시간을 포함시켜 주시면 됩니다. (예1) 추가적으로 creDate와 creTime을 병합하여 creDateTime 컬럼으로 만든 후에 인덱스를 거시는 것이 전반적인 속도 향상에 도움이 될 것 같습니다. (예2)

(예1) SELECT ... FROM [DUAL] WHERE (creDate='2010-01-03' AND creTime >='08:00:00' ) OR ( creDate='2010-01-04' AND creTime < '08:00:00' )

(예2) SELECT ... FROM [DUAL] WHERE creDateTime >= '2010-01-03 08:00:00' AND creDateTime < '2010-01-04 08:00:00'

 

 

이와 같이 날짜별 통계를 낸 후에, 이를 별도 테이블에 보관하고 사용자 요청이 있을 시에 이 결과 테이블에서 조회하시면 되겠습니다.

 

박현우(lqez)님이 2010-01-11 10:38에 작성한 댓글입니다.

프로시져로 해결했습니다.

한 5일 걸렸네요...ㅋㅋ

프로그램하고 SQL하고 썩어서 해결했습니다.

방법은 위에서 제가 말씀드린데로 했고요.

인덱스는 복합인덱스를 걸어서 그룹별로 조회

그값을 새로운 테이블에 인서트 하기전에 클라이언트가 원하는 값이 나올 수 있도록

수정해서 새로운 테이블에 집어 넣었습니다.

 

속도문제나 기타 다른 문제가 있는지 살펴 보고 있습니다.

이제 겨우 10만개정도 쌓였는데 어떨지는 좀더 두고 봐야 할거 같습니다.

 

감사하빈다.

궁금이님이 2010-01-12 14:23에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
29501like문에서 특수문자 escape하는 법... [1]
like문
2010-01-10
9162
29500Data 디렉토리 Tar로 넘겨도 되나요? [2]
한영호
2010-01-08
6143
29499MySQL 프로세스 개수
DM
2010-01-08
6393
29498통계 내는 쿼리가 궁금합니다. [4]
궁금이
2010-01-07
6865
29497한글 사용하려면 소스로 설치해야 하나요? [3]
네오
2010-01-06
7047
29496그룹으로 묶는 쿼리문. [1]
강민정
2010-01-06
6523
29495MySQL 느려지는 현상 [1]
H맨
2010-01-06
8277
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.023초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다