|
안녕하세요. 아래와 같은 샘플 데이터를 결과와 같이 추출하려고 합니다.
이름과 날짜(년/월) 기준으로 가장 자주 나타나는 값을 찾는것입니다.
년/월별로 날짜는 그 달중 아무값이나 나와도 됩니다.
년/월 기준으로 값의 개수가 같다면 일자까지 고려해서 추출합니다.
<샘플 데이터>
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.
|
