XV.애매한 그룹

상사가 각 제품별로 가장 최근에 보고된 버그를 조사해달라고 요청하여,
PRODUCT_ID가 같은 그룹에서 DATE_REPORTED 컬럼이 가장 큰 값을 계산하는 쿼리를 작성함


PRODUCT_NAME         LASTED                   BUG_ID
-------------------- -------------------- ----------
Open RoundFile       2010-06-01                 1234  
Visual TurboBuilder  2010-02-16                 3456
ReConsider           2010-01-01                 5678

. 하지만 이 결과는 가장 최근 버그로 나열된 행의 BUG_ID가 가장 최근 버그가 아님이 확인됨


PRODUCT_NAME         LASTED                   BUG_ID
-------------------- -------------------- ----------
Open RoundFile       2009-12-19                 1234 -- bug_id가 1234인 행의 
Open RoundFile       2010-06-01                 2248 -- date_reported는 2010-06-01 이 아님
Visual TurboBuilder  2010-02-16                 3456
Visual TurboBuilder  2010-02-10                 4077
Visual TurboBuilder  2010-02-16                 5150
ReConsider           2010-01-01                 5678
ReConsider           2009-11-09                 8063

이처럼 실제 의도했던 데이터와 다르게 나오는 경우 어떻게 해야 처리를 할 수 있나?

1. 목표 :-그룹당 최대값을 가진 행 얻기

. 샘플 데이터 생성(Oracle 기준)


-- 0. Oracle Version Check
SELECT * FROM V$VERSION;

BANNER                                                               
---------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit     
PL/SQL Release 10.2.0.3.0 - Production                               

-- 1. Create Table
DROP TABLE BUGS_PRODUCTS;
DROP TABLE BUGS;
DROP TABLE PRODUCTS;
DROP TABLE ACCOUNTS;

CREATE TABLE ACCOUNTS (ACCOUNT_ID        NUMBER,
                       ACCOUNT_NAME      VARCHAR(20),
                       FIRST_NAME        VARCHAR(20),
                       LAST_NAME         VARCHAR(20),
                       EMAIL             VARCHAR(100),
                       PASSWORD_HASH     CHAR(64),
                       PORTRAIT_IMAGE    BLOB,
                       HOURLY_RATE       NUMERIC(9,2),
                       PRIMARY KEY      (ACCOUNT_ID));

CREATE TABLE PRODUCTS(PRODUCT_ID   NUMBER,
                      PRODUCT_NAME VARCHAR2(1000),
                      PRIMARY KEY  (PRODUCT_ID));

CREATE TABLE BUGS(BUG_ID        NUMBER,
                  DATE_REPORTED DATE NOT NULL,
                  SUMMARY       VARCHAR(280),
                  DESCRIPTION   VARCHAR2(80),
                  RESOLUTION    VARCHAR2(80),
                  REPORTED_BY   NUMBER NOT NULL,
                  ASSIGNED_TO   NUMBER,
                  VERIFIED_BY   NUMBER,
                  STATUS        VARCHAR2(20) DEFAULT 'NEW' NOT NULL ,
                  PRIORITY      VARCHAR2(20),
                  HOURS         NUMERIC(9,2),
                  PRIMARY KEY   (BUG_ID),
                  FOREIGN KEY (REPORTED_BY) REFERENCES ACCOUNTS(ACCOUNT_ID));

CREATE TABLE BUGS_PRODUCTS(BUG_ID      NUMBER NOT NULL,
                           PRODUCT_ID  NUMBER NOT NULL,
                           PRIMARY KEY (BUG_ID, PRODUCT_ID),
                           FOREIGN KEY (BUG_ID) REFERENCES BUGS(BUG_ID),
                           FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID));                           

-- 2. Insert Data
INSERT INTO ACCOUNTS(ACCOUNT_ID) VALUES(9901);
INSERT INTO ACCOUNTS(ACCOUNT_ID) VALUES(9902);
INSERT INTO ACCOUNTS(ACCOUNT_ID) VALUES(9903);
INSERT INTO ACCOUNTS(ACCOUNT_ID) VALUES(9904);
INSERT INTO ACCOUNTS(ACCOUNT_ID) VALUES(9905);
INSERT INTO ACCOUNTS(ACCOUNT_ID) VALUES(9906);
INSERT INTO ACCOUNTS(ACCOUNT_ID) VALUES(9907);


INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_NAME) VALUES(1, 'Open RoundFile');
INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_NAME) VALUES(2, 'Visual TurboBuilder');
INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_NAME) VALUES(3, 'ReConsider');

INSERT INTO BUGS(BUG_ID, DATE_REPORTED, REPORTED_BY) VALUES(1234, TO_DATE('20091219', 'YYYYMMDD'), 9901);
INSERT INTO BUGS(BUG_ID, DATE_REPORTED, REPORTED_BY) VALUES(2248, TO_DATE('20100601', 'YYYYMMDD'), 9902);
INSERT INTO BUGS(BUG_ID, DATE_REPORTED, REPORTED_BY) VALUES(3456, TO_DATE('20100216', 'YYYYMMDD'), 9903);
INSERT INTO BUGS(BUG_ID, DATE_REPORTED, REPORTED_BY) VALUES(4077, TO_DATE('20100210', 'YYYYMMDD'), 9904);
INSERT INTO BUGS(BUG_ID, DATE_REPORTED, REPORTED_BY) VALUES(5150, TO_DATE('20100216', 'YYYYMMDD'), 9905);
INSERT INTO BUGS(BUG_ID, DATE_REPORTED, REPORTED_BY) VALUES(5678, TO_DATE('20100101', 'YYYYMMDD'), 9906);
INSERT INTO BUGS(BUG_ID, DATE_REPORTED, REPORTED_BY) VALUES(8063, TO_DATE('20091109', 'YYYYMMDD'), 9907);

INSERT INTO BUGS_PRODUCTS(BUG_ID, PRODUCT_ID) VALUES(1234, 1);
INSERT INTO BUGS_PRODUCTS(BUG_ID, PRODUCT_ID) VALUES(2248, 1);
INSERT INTO BUGS_PRODUCTS(BUG_ID, PRODUCT_ID) VALUES(3456, 2);
INSERT INTO BUGS_PRODUCTS(BUG_ID, PRODUCT_ID) VALUES(4077, 2);
INSERT INTO BUGS_PRODUCTS(BUG_ID, PRODUCT_ID) VALUES(5150, 2);
INSERT INTO BUGS_PRODUCTS(BUG_ID, PRODUCT_ID) VALUES(5678, 3);
INSERT INTO BUGS_PRODUCTS(BUG_ID, PRODUCT_ID) VALUES(8063, 3);

COMMIT;

각 제품별로 가장 최근에 보고된 버그를 얻는 쿼리


col PRODUCT_NAME format a20
col LASTED       format a20

SELECT A.PRODUCT_NAME,
       MAX(TO_CHAR(C.DATE_REPORTED, 'YYYY-MM-DD')) AS LASTED
FROM   PRODUCTS      A,
       BUGS_PRODUCTS B,
       BUGS          C
WHERE  A.PRODUCT_ID = B.PRODUCT_ID
AND    B.BUG_ID     = C.BUG_ID
GROUP BY A.PRODUCT_NAME
;

PRODUCT_NAME         LASTED              
-------------------- --------------------
Open RoundFile       2010-06-01          
ReConsider           2010-01-01          
Visual TurboBuilder  2010-02-16

보고일자가 가장 최근인 BUG_ID를 얻기 위해 확장한 쿼리


SELECT A.PRODUCT_NAME,
       MAX(TO_CHAR(C.DATE_REPORTED, 'YYYY-MM-DD')) AS LASTED,
       C.BUG_ID
FROM   PRODUCTS      A,
       BUGS_PRODUCTS B,
       BUGS          C
WHERE  A.PRODUCT_ID = B.PRODUCT_ID
AND    B.BUG_ID     = C.BUG_ID
GROUP BY A.PRODUCT_NAME
;

       C.BUG_ID
       *
ERROR at line 3:
ORA-00979: not a GROUP BY expression

하지만 이 쿼리는 GROUP BY 컬럼에 BUG_ID가 없기 때문에 에러를 발생시키며, 결과가 나오더라도 그 결과를 신뢰할 수 없음.
목표는 그룹의 최대값(또는 최소값 또는 평균값)뿐 아니라 해당 값을 찾는 행의 다른 속성도 포함토록 해야 함.

2. 안티패턴 - 그룹되지 않은 컬럼 참조

1) 단일 값 규칙

쿼리에서 SELECT 목록에 있는 모든 컬럼은 그룹 당 하나의 값을 가져야 하며, 이를 단일 값 규칙이라 함.
GROUP BY 절 뒤에 쓴 컬럼들은 얼마나 많은 행이 그룹에 대응되는지에 상관없이 각 그룹당 정확히 하나의 값만 나옴


col PRODUCT_NAME format a20
col LASTED       format a20

SELECT A.PRODUCT_NAME,
       MAX(TO_CHAR(C.DATE_REPORTED, 'YYYY-MM-DD')) AS LASTED
FROM   PRODUCTS      A,
       BUGS_PRODUCTS B,
       BUGS          C
WHERE  A.PRODUCT_ID = B.PRODUCT_ID
AND    B.BUG_ID     = C.BUG_ID
GROUP BY A.PRODUCT_NAME
;

PRODUCT_NAME         LASTED              
-------------------- --------------------
Open RoundFile       2010-06-01          
ReConsider           2010-01-01          
Visual TurboBuilder  2010-02-16

그러나, SELECT 목록에 있는 다른 컬럼에 대해서는, 그룹 안에서 모든 행에 같은 값이 나오는지를 보장할 수 없다.
이로 인해 대부분의 DB 제품은 GROUP BY 절 뒤에 나오는 컬럼 또는 집계함수 인수로 사용되는 컬럼 이외에 다른 컬럼 사용할 경우 에러 발생시킴


SELECT A.PRODUCT_NAME,
       MAX(TO_CHAR(C.DATE_REPORTED, 'YYYY-MM-DD')) AS LASTED,
       C.BUG_ID
FROM   PRODUCTS      A,
       BUGS_PRODUCTS B,
       BUGS          C
WHERE  A.PRODUCT_ID = B.PRODUCT_ID
AND    B.BUG_ID     = C.BUG_ID
GROUP BY A.PRODUCT_NAME
;

       C.BUG_ID
       *
ERROR at line 3:
ORA-00979: not a GROUP BY expression

2) 내 뜻대로 동작하는 쿼리

프로그래머들은 흔히, 다른 컬럼에 사용된 MAX()를 통해, 어떤 BUG_ID를 넣어야 할지 SQL이 알아낼 수 있다고 잘 못 생각함.
즉, 쿼리가 최대값을 얻을 때, 자연히 다른 컬럼의 값도 그 최대값을 얻은 행에서 가져올 것이라 가정함.
그러나 아래와 같은 경우에는 이런 추론을 할 수 없음
- 두 버그의 DATE_REPORTED 값이 동일하고 이 값이 그룹 내 최대값이라면, 쿼리에서 어느 BUG_ID 값을 보여줘야 하나?
- 쿼리에서 두 가지 다른 집계 함수라면(MIN, MAX), 이는 그룹 안에서 두 개의 다른 행에 대응될텐데, 이 그룹에서는 어느 BUG_ID를 리턴하나?


SELECT A.PRODUCT_NAME,
       MAX(TO_CHAR(C.DATE_REPORTED, 'YYYY-MM-DD')) AS LASTED,
       MIN(TO_CHAR(C.DATE_REPORTED, 'YYYY-MM-DD')) AS EARLIEST
       C.BUG_ID
FROM   PRODUCTS      A,
       BUGS_PRODUCTS B,
       BUGS          C
WHERE  A.PRODUCT_ID = B.PRODUCT_ID
AND    B.BUG_ID     = C.BUG_ID
GROUP BY A.PRODUCT_NAME;

- 집계 함수가 리턴하는 값과 매치되는 행이 없는 경우 BUG_ID값을 어떻게 해야 하나?


SELECT A.PRODUCT_NAME,
       SUM(C.HOURS) AS TOTAL_PROJECT_ESTIMATE,
       C.BUG_ID
FROM   PRODUCTS      A,
       BUGS_PRODUCTS B,
       BUGS          C
WHERE  A.PRODUCT_ID = B.PRODUCT_ID
AND    B.BUG_ID     = C.BUG_ID
GROUP BY A.PRODUCT_NAME;

3. 안티패턴 사용이 합당한 경우

MySQL과 SQLite는 아래와 같이 단일값 규칙을 위반해도 에러가 나지 않는다.
그 이유는, BUGS.REPORTED_BY 컬럼이 ACCOUNTS 테이블에 대한 외리키이므로 한 레코드밖에 대응이 안되기 때문에 에러가 나지 않음


SELECT A.REPORTED_BY,
       B.ACCOUNT_NAME
FROM   BUGS          A,
       ACCOUNTS      B
WHERE  A.REPORTED_BY = B.ACCOUNT_ID
GROUP BY A.REPORTED_BY;

이런 종류의 명확한 관계를 함수 종속(functional dependency)이라 부름
그러나, 다른 DB 제품에서는 여전히 에러를 발생시킴

4. 해법 - 컬럼을 모호하게 사용하지 않기

1) 함수 종속인 컬럼만 쿼리하기


col PRODUCT_NAME format a20
col LASTED       format a20

SELECT A.PRODUCT_NAME,
       MAX(TO_CHAR(C.DATE_REPORTED, 'YYYY-MM-DD')) AS LASTED
FROM   PRODUCTS      A,
       BUGS_PRODUCTS B,
       BUGS          C
WHERE  A.PRODUCT_ID = B.PRODUCT_ID
AND    B.BUG_ID     = C.BUG_ID
GROUP BY A.PRODUCT_NAME
;

PRODUCT_NAME         LASTED              
-------------------- --------------------
Open RoundFile       2010-06-01          
ReConsider           2010-01-01          
Visual TurboBuilder  2010-02-16

이 쿼리는 각 제품에 대해 가장 최근에 보고된 버그의 날자만 보여주며, 가장 최근에 보고된 버그의 BUG_ID는 보여주지 않음.

2) 상호 연관된 서브쿼리 사용하기


col PRODUCT_NAME format a20
col LASTED       format a20

SELECT A_1.PRODUCT_NAME,
       TO_CHAR(C_1.DATE_REPORTED, 'YYYY-MM-DD') AS LASTED,
       C_1.BUG_ID
FROM   PRODUCTS      A_1,
       BUGS_PRODUCTS B_1,
       BUGS          C_1
WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
AND    B_1.BUG_ID     = C_1.BUG_ID
AND    NOT EXISTS (SELECT 1
                   FROM   BUGS_PRODUCTS B_2,
                          BUGS          C_2
                   WHERE  B_2.BUG_ID        = C_2.BUG_ID
                   AND    B_1.PRODUCT_ID    = B_2.PRODUCT_ID
                   AND    C_1.DATE_REPORTED < C_2.DATE_REPORTED)
;

PRODUCT_NAME         LASTED                   BUG_ID
-------------------- -------------------- ----------
Open RoundFile       2010-06-01                 2248
Visual TurboBuilder  2010-02-16                 3456
Visual TurboBuilder  2010-02-16                 5150
ReConsider           2010-01-01                 5678

상호 연관된 서브쿼리는 바깥쪽 쿼리에 대한 참조를 가지고 있어, 바깥쪽 쿼리의 각 행에 대해 다른 결과 생성 가능.
이를 이용해 서브쿼리가 그룹 내에서 날짜가 큰 버그를 찾게 해, 각 제품별로 가장 최근에 보고된 버그 조회 가능
하지만, 서브쿼리는 바깥쪽 쿼리의 각 행에 대해 한 번씩 실행되므로, 성능 상 최적의 방법은 아님.

3) 유도 테이블 사용하기


SELECT T.PRODUCT_NAME,
       TO_CHAR(T.LASTED, 'YYYY-MM-DD') LASTED,
       C_2.BUG_ID
FROM   (SELECT A_1.PRODUCT_ID,
               A_1.PRODUCT_NAME,
               MAX(C_1.DATE_REPORTED) AS LASTED
        FROM   PRODUCTS      A_1,
               BUGS_PRODUCTS B_1,
               BUGS          C_1
        WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
        AND    B_1.BUG_ID     = C_1.BUG_ID
        GROUP BY A_1.PRODUCT_ID,
                 A_1.PRODUCT_NAME) T,
       BUGS_PRODUCTS B_2,
       BUGS          C_2
WHERE  B_2.BUG_ID        = C_2.BUG_ID
AND    B_2.PRODUCT_ID    = T.PRODUCT_ID
AND    C_2.DATE_REPORTED = T.LASTED  
;

PRODUCT_NAME         LASTED                   BUG_ID
-------------------- -------------------- ----------
Open RoundFile       2010-06-01                 2248
Visual TurboBuilder  2010-02-16                 3456
Visual TurboBuilder  2010-02-16                 5150
ReConsider           2010-01-01                 5678

서브쿼리를 유도 테이블로 사용해, 각 제품에 대한 PRODUCT_ID와 버그 보고일자의 최대값은 포함하는 임시 결과 생성.
이후 이 결과를 테이블과 조인해 쿼리 결과가 각 제품당 가장 최근의 버그만 포함하게 함.

만약, PRODUCT_ID당 하나의 행만 나오게 하고 싶으면, 바깥쪽 쿼리에 다른 그룹핑 함수 사용함.


SELECT T.PRODUCT_NAME,
       TO_CHAR(T.LASTED, 'YYYY-MM-DD') LASTED,
       MAX(C_2.BUG_ID) BUG_ID
FROM   (SELECT A_1.PRODUCT_ID,
               A_1.PRODUCT_NAME,
               MAX(C_1.DATE_REPORTED) AS LASTED
        FROM   PRODUCTS      A_1,
               BUGS_PRODUCTS B_1,
               BUGS          C_1
        WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
        AND    B_1.BUG_ID     = C_1.BUG_ID
        GROUP BY A_1.PRODUCT_ID,
                 A_1.PRODUCT_NAME) T,
       BUGS_PRODUCTS B_2,
       BUGS          C_2
WHERE  B_2.BUG_ID        = C_2.BUG_ID
AND    B_2.PRODUCT_ID    = T.PRODUCT_ID
AND    C_2.DATE_REPORTED = T.LASTED  
GROUP BY T.PRODUCT_NAME,
         TO_CHAR(T.LASTED, 'YYYY-MM-DD')
ORDER BY 1
;

PRODUCT_NAME         LASTED                   BUG_ID 
-------------------- -------------------- ---------- 
Open RoundFile       2010-06-01                 2248 
ReConsider           2010-01-01                 5678 
Visual TurboBuilder  2010-02-16                 5150

4) 조인 사용하기


-- 1. MySQL 버전
SELECT BP1.PRODUCT_ID,
       B1.DATE_REPORTED AS LATEST,
       B1.BUG_ID
FROM   BUGS B1
JOIN   BUGSPRODUCTS BP1 ON (B1.BUG_ID = BP1.BUG_ID)
LEFT   OUTER JOIN (BUGS AS B2 JOIN   BUGSPRODUCTS AS BP2 ON (B2.BUG_ID = BP2.BUG_ID)) ON 
(BP1.PRODUCT_ID = BP2.PRODUCT_ID AND (B1.DATE_REPORTED < B2.DATE_REPORTED OR B1.DATE_REPORTED = B2.DATE_REPORTED AND B1.BUG_ID < B2.BUG_ID))
WHERE  B2.BUG_ID IS NULL
;

-- 1_1. 위에 MySQL Ansi 버전은 이해가 되지 않음 -_-
--      그래서 아래 Oracle 버전에서 일부 조인 제거함

-- 2. Oracle 버전
col PRODUCT_NAME format a20
col LASTED       format a20

SELECT T_2.PRODUCT_NAME,
       MAX(TO_CHAR(T_2.LASTED, 'YYYY-MM-DD')) LASTED,
       MAX(T_2.BUG_ID) BUG_ID
FROM   (SELECT A_1.PRODUCT_ID,
               A_1.PRODUCT_NAME,
               C_1.DATE_REPORTED AS LASTED,
               C_1.BUG_ID
        FROM   PRODUCTS      A_1,
               BUGS_PRODUCTS B_1,
               BUGS          C_1
        WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
        AND    B_1.BUG_ID     = C_1.BUG_ID) T_1,
       (SELECT A_2.PRODUCT_ID,
               A_2.PRODUCT_NAME,
               C_2.DATE_REPORTED AS LASTED,
               C_2.BUG_ID
        FROM   PRODUCTS      A_2,
               BUGS_PRODUCTS B_2,
               BUGS          C_2
        WHERE  A_2.PRODUCT_ID = B_2.PRODUCT_ID
        AND    B_2.BUG_ID     = C_2.BUG_ID) T_2
WHERE  T_1.PRODUCT_ID = T_2.PRODUCT_ID
AND    T_1.LASTED     < T_2.LASTED
GROUP BY T_2.PRODUCT_NAME
ORDER BY 1
;

PRODUCT_NAME         LASTED                   BUG_ID
-------------------- -------------------- ----------
Open RoundFile       2010-06-01                 2248
ReConsider           2010-01-01                 5678
Visual TurboBuilder  2010-02-16                 5150

5) 다른 컬럼에 집계 함수 사용하기

BUG_ID 값은 항상 시간순으로 자동 증가한다고 가정하면, 아래처럼 SQL 작성 가능


SELECT A_1.PRODUCT_NAME,
       MAX(TO_CHAR(C_1.DATE_REPORTED, 'YYYY-MM-DD')) AS LASTED,
       MAX(C_1.BUG_ID) BUG_ID
FROM   PRODUCTS      A_1,
       BUGS_PRODUCTS B_1,
       BUGS          C_1
WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
AND    B_1.BUG_ID     = C_1.BUG_ID
GROUP BY A_1.PRODUCT_NAME
ORDER BY 1
;

PRODUCT_NAME         LASTED                   BUG_ID
-------------------- -------------------- ----------
Open RoundFile       2010-06-01                 2248
ReConsider           2010-01-01                 8063
Visual TurboBuilder  2010-02-16                 5150

6) 각 그룹에 대해 모든 값을 연결하기


-- 1. MySQL 버전
SELECT PRODUCT_ID,
       MAX(DATE_REPORTED) AS LATEST GROUP_CONCAT(BUG_ID) AS BUG_ID_LIST,
FROM   BUGS
JOIN   BUGSPRODUCTS
USING  (BUG_ID)
GROUP  BY PRODUCT_ID
;

-- 2. Oracle 버전
SELECT T.PRODUCT_NAME,
       T.LASTED,
       SUBSTR(SYS_CONNECT_BY_PATH(T.BUG_ID, ', '), 3) BUG_ID_LIST
FROM   (SELECT A_1.PRODUCT_NAME,
               TO_CHAR(C_1.DATE_REPORTED, 'YYYY-MM-DD') AS LASTED,
               C_1.BUG_ID BUG_ID,
               ROW_NUMBER() OVER(PARTITION BY A_1.PRODUCT_NAME ORDER BY C_1.DATE_REPORTED) CNT,
               COUNT(*) OVER(PARTITION BY A_1.PRODUCT_NAME) CNT_ALL
        FROM   PRODUCTS      A_1,
               BUGS_PRODUCTS B_1,
               BUGS          C_1
        WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
        AND    B_1.BUG_ID     = C_1.BUG_ID) T
WHERE  T.CNT = T.CNT_ALL
CONNECT BY PRIOR T.CNT          = T.CNT - 1
AND        PRIOR T.PRODUCT_NAME = T.PRODUCT_NAME
START WITH CNT = 1
;

PRODUCT_NAME         LASTED               BUG_ID_LIST       
-------------------- -------------------- ------------------
Open RoundFile       2010-06-01           1234, 2248        
ReConsider           2010-01-01           8063, 5678        
Visual TurboBuilder  2010-02-16           4077, 3456, 5150

7) 필자 버전(Oracle 10g)


-- 1. 1차 버전
col PRODUCT_NAME format a20
col LASTED       format a20

SELECT A_1.PRODUCT_NAME,
       TO_CHAR(C_1.DATE_REPORTED, 'YYYY-MM-DD') AS LASTED,
       C_1.BUG_ID,
       ROW_NUMBER() OVER(PARTITION BY A_1.PRODUCT_NAME ORDER BY C_1.DATE_REPORTED DESC, C_1.BUG_ID DESC) CNT
FROM   PRODUCTS      A_1,
       BUGS_PRODUCTS B_1,
       BUGS          C_1
WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
AND    B_1.BUG_ID     = C_1.BUG_ID
;

PRODUCT_NAME         LASTED                   BUG_ID        CNT
-------------------- -------------------- ---------- ----------
Open RoundFile       2010-06-01                 2248          1
Open RoundFile       2009-12-19                 1234          2
ReConsider           2010-01-01                 5678          1
ReConsider           2009-11-09                 8063          2
Visual TurboBuilder  2010-02-16                 5150          1
Visual TurboBuilder  2010-02-16                 3456          2
Visual TurboBuilder  2010-02-10                 4077          3

-- 2. 2차 버전
SELECT *
FROM   (SELECT A_1.PRODUCT_NAME,
               TO_CHAR(C_1.DATE_REPORTED, 'YYYY-MM-DD') AS LASTED,
               C_1.BUG_ID,
               ROW_NUMBER() OVER(PARTITION BY A_1.PRODUCT_NAME ORDER BY C_1.DATE_REPORTED DESC, C_1.BUG_ID DESC) CNT
        FROM   PRODUCTS      A_1,
               BUGS_PRODUCTS B_1,
               BUGS          C_1
        WHERE  A_1.PRODUCT_ID = B_1.PRODUCT_ID
        AND    B_1.BUG_ID     = C_1.BUG_ID)
WHERE  CNT = 1
;

PRODUCT_NAME         LASTED                   BUG_ID        CNT
-------------------- -------------------- ---------- ----------
Open RoundFile       2010-06-01                 2248          1
ReConsider           2010-01-01                 5678          1
Visual TurboBuilder  2010-02-16                 5150          1

모호한 쿼리 결과를 피하기 위해 단일 값 규칙을 따라라.

문서에 대하여

최초작성자 : ~xsoft
최초작성일 : 2011년 12월 17일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.*

15장. 애매한 그룹 (by xsoft) [2011.12.13]

목차

XV.애매한 그룹

1. 목표 :-그룹당 최대값을 가진 행 얻기

2. 안티패턴 - 그룹되지 않은 컬럼 참조

1) 단일 값 규칙

2) 내 뜻대로 동작하는 쿼리

3. 안티패턴 사용이 합당한 경우

4. 해법 - 컬럼을 모호하게 사용하지 않기

1) 함수 종속인 컬럼만 쿼리하기

2) 상호 연관된 서브쿼리 사용하기

3) 유도 테이블 사용하기

4) 조인 사용하기

5) 다른 컬럼에 집계 함수 사용하기

6) 각 그룹에 대해 모든 값을 연결하기

7) 필자 버전(Oracle 10g)

문서에 대하여