큰값과 작은값 그룹핑 쿼리 질문입니다.! 0 15 4,499

by 히준3 [SQL Query] [2024.06.07 17:06:27]


DB는 오라클입니다.

WITH TABLE AS
(
    SELECT 'A' AS PRODUCT_ID, 69998 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'B' AS PRODUCT_ID, 69997 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'C' AS PRODUCT_ID, 69000 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'D' AS PRODUCT_ID, 66000 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'E' AS PRODUCT_ID, 65999 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'F' AS PRODUCT_ID, 3000 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'G' AS PRODUCT_ID, 2000 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'H' AS PRODUCT_ID, 1000 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'I' AS PRODUCT_ID, 7 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'J' AS PRODUCT_ID, 5 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
    UNION ALL
    SELECT 'K' AS PRODUCT_ID, 2 AS WEIGHT, 5 AS MAX_GRP_NO, 70000 AS GRP_MAX_WEIGHT FROM DUAL
)

원본

PRODUCT_ID WEIGHT MAX_GRP_NO GRP_MAX_WEIGHT
A 69998 5 70000
B 69997 5 70000
C 69000 5 70000
D 66000 5 70000
E 65999 5 70000
F 3000 5 70000
G 2000 5 70000
H 1000 5 70000
I 7 5 70000
J 5 5 70000
K 2 5 70000

그룹 조건

PRODUCT_ID: 제품, WEIGHT: 중량, MAX_GRP_NO: 최대그룹번호, GRP_MAX_WEIGHT: 그룹최대중량

1. 그룹이 없는 제품의 중량이 가장 작은 값이 그룹이 없는 제품의 중량이 가장 큰 값과 그룹이 됩야합니다.

2. 큰 중량과 작은 중량의 합이 그룹최대중량을 초과할 경우는 작은 중량은 큰 중량과 같은 그룹이 될수 없고 큰 중량의 제품만 혼자 그룹이 됩니다.

3. 만약 제품의 가장 작은 중량이 그룹최대중량 초과로 그룹이 안되었다면 다음 그룹이 없는 가장 큰 중량 제품과 그룹이 되어야합니다.

4. 그룹의 첫번째 중량이 큰순서로 그룹번호: 1, 그룹번호: 2, 그룹번호: 3이 되야합니다.

5. 그룹번호가 최대그룹번호를 넘을 수 없습니다.

6. 그룹이 없는 제품들은 다시 중량이 큰 것 중량이 작은것 순으로 그룹최대중량이 넘지 않는 가장 빠른 그룹번호로 같은 그룹이 되야합니다.

원하는 결과

PRODUCT_ID WEIGHT GRP_NO
A 69998 1
B 69997 2
C 69000 3
D 66000 4
E 65999 5
F 3000 4
G 2000 5
H 1000 5
I 7 4
J 5 3
K 2 1

조건 설명이 부족하다면 댓글 부탁드립니다..

by 우주민 [2024.06.10 09:38:08]

혹시 MAX_GRP_NO, GRP_MAX_WEIGHT 이 컬럼은 모두 동일한 값을 가지나요?


by 히준3 [2024.06.10 10:03:08]

네. 동일한게 맞습니다.

ROW별로 달린부분은 무시하셔도됩니다.!!


by 우주민 [2024.06.10 11:21:05]

와... 이거 생각보다 더 까다로운 문제군요. 재귀함수 써서 처리 가능할듯 싶었는데... 조금 더 확인 해봐야 겠네요.


by 우주민 [2024.06.10 11:33:26]

혹시 제가 생각한 방법이 위에 기술된 조건과 부합하는지 확인 부탁드립니다.

 

1. 중량이 큰 순서대로 1~5 그룹을 만든다. (기본 그룹)

2. 중량이 작은 순서대로 1 그룹부터 중량이 합산될때 최대 중량이 넘어가는지 확인 후 아래와 같은 액션을 한다.

  2-1. (최대중량이 넘을 경우) 다음그룹으로 넘어가 2 순서를 진행한다.

  2-2. (최대중량이 안넘을 경우) 해당 그룹값을 배정 후 그룹의 총 중량값에 중량값을 추가한다.

3. 기본 그룹에 추가되지 못할때 그룹값을 비워둔다.


by 히준3 [2024.06.10 11:47:51]

네 맞는것 같습니다..


by 우주민 [2024.06.10 13:12:19]

제가 문의한 로직대로 하면 아래와 같은 결과가 나오게 될텐데....

조금 더 자세한 로직 설명이 필요할듯 싶습니다.

 

PRODUCT_ID WEIGHT GRP_NO
A 69998 1
B 69997 2
C 69000 3
D 66000 4
E 65999 5
F 3000 5
G 2000 4
H 1000 4
I 7 3
J 5 3
K 2 1

by 마농 [2024.06.10 12:36:37]

결과가 좀 이상한 것 같은데요?
(F, G) 에 대한 그룹이 맞는지요?


by 히준3 [2024.06.10 12:47:16]

F는 그룹최대중량을 초과하지않는 4번 그룹이 됬습니다. (69000중량합)

G는 그룹최대중량을 초과하지않는 5번 그룹이 됬습니다. (68999중량합)


by 마농 [2024.06.10 12:57:43]

작은 순서대로 적용하여 거꾸로 되어야 하는 것 아닌지?
작은 순서대로 G4, F5


by 히준3 [2024.06.10 13:24:55]

그룹이 없는 G와 F중에 G가 큰중량이라 먼저 4그룹이 되게 하였는데.

거꾸로 되야하는 부분이 잘 이해가 가질 않습니다.


by 마농 [2024.06.10 13:31:50]

저는 적은 순서대로 반복하는 건줄 알았는데요.
첫번째 배정은 적은 순서이고, 그 이후에는 큰 순서인가보네요.
일관적인 기준이 있어야 할 거라 생각했나 봅니다.

일관되고 획일적이며 단순한 조건이어야 쿼리로 가능할 것 같은데.
쿼리보다는 PL/SQL 로 짜야 할 것 같습니다.


by 히준3 [2024.06.11 17:48:56]

감사합니다


by 히준3 [2024.06.11 17:58:10]

우주민님 위에 대댓글이 안달려서 댓글로 씁니다.

말씀하신대로 제 그룹조건이 잘못됬내요.

쉽게 그룹하나에 큰거하나 작은거하나 넣으려다가

중량초과내? 큰거만넣고

다음 그룹 큰거하나 작은거하나 이런식입니다..

 


by 우주민 [2024.06.12 10:24:37]

로직이 정해진게 아니고 주어진 데이터를 그룹에 배정하는 로직을 만드는 작업중에 계신 건가요?


by 히준3 [2024.06.12 16:03:26]

제가 본문에 써놓은 그룹기준과 비슷하게 그룹기준은 정해져있구요.

(실제로는 그룹기준이 더있으며 특정 한 케이스만 그룹짓는 부분을 예시로 만들었습니다.)

최근 댓글의 큰거하나 작은거하나는 뭔가 직관적(?) 으로 설명을 드리는게 나을것 같아서 말씀드렸습니다.

 

주어진 데이터에 그룹 배정을 한 쿼리로 풀어보려고 고민 하던 중 질문글을 작성하였구요.

실제 개발담당자는 따로 있고 PL/SQL로 거의다 완성한 상태입니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입