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
운영게시판
최근게시물
Informix Q&A 3484 게시물 읽기
No. 3484
가장 자주 나타나는 일자 찾기
작성자
정상규(pajama)
작성일
2020-03-16 14:27ⓒ
2020-03-17 10:16ⓜ
조회수
462

 

안녕하세요. 아래와 같은 샘플 데이터를 결과와 같이 추출하려고 합니다.

 

이름과 날짜(년/월) 기준으로 가장 자주 나타나는 값을 찾는것입니다.

 

년/월별로 날짜는 그 달중 아무값이나 나와도 됩니다.

 

년/월 기준으로 값의 개수가 같다면 일자까지 고려해서 추출합니다.

 

<샘플 데이터>
NAME    DOB         AVG
JESSE   1992/10/27  10
JESSE   1992/10/27  20
JESSE   1992/11/06  30
JESSE   1992/11/11  40
JESSICA 1992/03/11  50
JESSICA 1992/11/03  60
JESSICA 1992/10/29  70
JESSICA 1992/11/10  80
JESSICA 1992/11/30  90
JESSICA 1992/11/12  10
JESSICA 1992/12/07  20
JESSICA 1992/12/09  30

 

<결과>

 

NAME    DOB
JESSE   1992/10/27 
JESSICA 1992/11/?? (11월중 아무날짜나 가능)

 


 

제가 작성한 쿼리와 결과는 아래와 같습니다..

 

혹시 잘못되었거나 비효율적인 부분이 있다면 알려주시면 감사하겠습니다.

 

[informix@db2 skjeong]$ cat mode.sql

 

WITH t1 AS

 

(

 

SELECT 'JESSE' Name, '1992/10/27' dob, 10 score

 

UNION ALL SELECT 'JESSE', '1992/10/27', 20

 

UNION ALL SELECT 'JESSE', '1992/11/06', 30

 

UNION ALL SELECT 'JESSE', '1992/11/11', 40

 

UNION ALL SELECT 'JESSICA', '1992/03/11', 50

 

UNION ALL SELECT 'JESSICA', '1992/11/03', 60

 

UNION ALL SELECT 'JESSICA', '1992/10/29', 70

 

UNION ALL SELECT 'JESSICA', '1992/11/10', 80

 

UNION ALL SELECT 'JESSICA', '1992/11/30', 90

 

UNION ALL SELECT 'JESSICA', '1992/11/12', 10

 

UNION ALL SELECT 'JESSICA', '1992/12/07', 20

 

UNION ALL SELECT 'JESSICA', '1992/12/09', 30

 

)

 

SELECT a.Name,

 

       a.dob,

 

       a.avg

 

  FROM (SELECT t1.Name,

 

               t1.dob,

 

               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rn,

 

               AVG(t1.score) OVER (PARTITION BY t1.Name) avg

 

          FROM t1,

 

               (SELECT t1.Name,

 

                       t1.dob[1,7],

 

                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk

 

                  FROM t1

 

                 GROUP BY t1.Name, t1.dob[1,7]

 

               ) t2

 

         WHERE t2.rk = 1

 

           AND t1.Name = t2.Name

 

           AND t1.dob like t2.dob||'%'

 

         GROUP BY t1.Name, t1.dob, t1.score

 

       ) a

 

 WHERE a.rn = 1

 

[informix@db2 skjeong]$ dbaccess testdb mode.sql

 


 

Database selected.

 


 


 


 

name    dob                     avg

 


 

JESSE   1992/10/27 25.0000000000000

 

JESSICA 1992/11/30 60.0000000000000

 


 

2 row(s) retrieved.

 

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

1. jesse 의 평균이 25 맞나요?
 - 10월평균이 나와야 하는것 아닌지?
 - 10,11월 합친 평균이네요?
2. 동일 조건일때 우선순위 명확하게.
 - 아무거나라는 표현보다는 명확하게 정해야 합니다.
 - 월카운트 > 일카운트 > 다음조건은?
 - 동일하지 않은 유일값 조건이 될때까지 다음조건을 정해줘야 합니다.
 - 예) 월평균이 높은거/낮은거, 일자가 빠른거/늦은거 등

마농(manon94)님이 2020-03-18 08:38에 작성한 댓글입니다.

안녕하세요. 마농님. 댓글과 여러가지 지적 감사드립니다.

 

1. jesse 의 평균이 25 맞나요?
네 10월 평균이 나오는게 맞습니다. 잘못되었네요..


2. 동일 조건일때 우선순위 명확하게.

그 달중에 어떤값이 나와도 관계없다고 해서 아무값이나 괜찮다는 표현을 쓰게 되었습니다. 

월카운트 > 일카운트 > 일자가 빠른것 순으로 하면 될듯합니다.


수정한 쿼리는 이렇습니다.


 WITH t1 AS

(
SELECT 'JESSE' Name , '1992/08/06' dob, 30 score
UNION ALL SELECT 'JESSE', '1992/08/06', 40 
UNION ALL SELECT 'JESSE', '1992/07/27', 10
UNION ALL SELECT 'JESSE', '1992/07/27', 20 
UNION ALL SELECT 'JESSE', '1992/11/06', 30 
UNION ALL SELECT 'JESSE', '1992/11/11', 40 
UNION ALL SELECT 'JESSICA', '1992/03/11', 50 
UNION ALL SELECT 'JESSICA', '1992/11/03', 60 
UNION ALL SELECT 'JESSICA', '1992/10/29', 70 
UNION ALL SELECT 'JESSICA', '1992/11/10', 80 
UNION ALL SELECT 'JESSICA', '1992/11/30', 90 
UNION ALL SELECT 'JESSICA', '1992/11/12', 10 
UNION ALL SELECT 'JESSICA', '1992/12/07', 20  
UNION ALL SELECT 'JESSICA', '1992/12/09', 30 
)
SELECT a.Name,
       a.dob,
       a.avg   
  FROM (SELECT t1.Name, 
               t1.dob, 
               ROW_NUMBER() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC, t1.dob ASC) rn, 
               AVG(t1.score) OVER (PARTITION BY t1.Name, t1.dob[1,7] ) avg
          FROM t1,
               (SELECT t1.Name, 
                       t1.dob[1,7], 
                       RANK() OVER (PARTITION BY t1.Name ORDER BY COUNT(*) DESC) rk
                  FROM t1
                 GROUP BY t1.Name, t1.dob[1,7]
               ) t2
         WHERE t2.rk = 1
           AND t1.Name = t2.Name
           AND t1.dob like t2.dob||'%'
         GROUP BY t1.Name, t1.dob, t1.score
       ) a
 WHERE rn =1
정상규(pajama)님이 2020-03-18 12:06에 작성한 댓글입니다.
이 댓글은 2020-03-18 12:22에 마지막으로 수정되었습니다.

SELECT *
  FROM (SELECT name
             , dob
             , avg_m
             , ROW_NUMBER() OVER(
               PARTITION BY name ORDER BY cnt_m DESC, cnt_d DESC, dob) rn
          FROM (SELECT name
                     , dob
                     , COUNT(*)   OVER(PARTITION BY name, dob[1,7]) cnt_m
                     , COUNT(*)   OVER(PARTITION BY name, dob     ) cnt_d
                     , AVG(score) OVER(PARTITION BY name, dob[1,7]) avg_m
                  FROM t1
                ) a
        ) a
 WHERE rn = 1
;

마농(manon94)님이 2020-03-19 09:10에 작성한 댓글입니다.

이렇게 풀수도 있네요... 가르쳐주셔서 감사합니다 ^^

정상규(pajama)님이 2020-03-19 09:23에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
3487환경변수 등록 [1]
익이
2020-05-22
217
3486informix 구동 후 [5]
익이
2020-05-21
260
3485informix 설치 및 실행(sqlhosts 파일 x) [3]
익이
2020-05-21
268
3484가장 자주 나타나는 일자 찾기 [4]
정상규
2020-03-16
462
3483informix 대문자 사용 방법 질문입니다. [1]
llms
2019-07-15
987
3482informix 실행계획 질문입니다. [1]
goblin
2018-06-12
1584
3480remote database에서 결과값 조회시 틀림 [6]
Moon
2017-11-08
2124
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2019 DSN, All rights reserved.
작업시간: 0.262초, 이곳 서비스는
	PostgreSQL v11.5로 자료를 관리합니다