SQL 질문드립니다(MySQL5.6) 0 10 219

by 재팬종쿤 [SQL Query] SQL MySQL [2019.06.12 15:55:15]


안녕하세요.

쿼리 작성하는 데 있어서 의문점(?)이 생겨서 글을 올리게 되었습니다.

 

테이블 부터 설명드리면

1. user: 유저가 회원가입 하면 저장되는 곳 이라고 생각하시면 될 것 같습니다.

2. workflow: 유저가 워크플로우를 작성하면 저장되는 곳이라고 생각하시면 될 것 같습니다.

3. workflow_person: 유저가 워크플로우를 작성할 때 설정한 승인자와 신청자(자기 자신)이 저장되는 곳이라고 생각하시면 될 것 같습니다.

테이블간의 관계는

workflow : workflow_person = 1 : N

user : workflow_person :  = 1 : N

SQL Fiddle로 결과를 확인하실 수 있게 준비를 해봤습니다. 혹시 안보이실 경우는 말씀해주세요.

http://www.sqlfiddle.com/#!9/0b7f89/8

부가 설명을 드리면

SELECT 문에 서브쿼리 한 것과, WHERE 절에 서브쿼리 한 조건은 같은 데 next_approver 컬럼에 대해서 다른 결과가 나타나고 있습니다.

두개의 쿼리 중 "LEFT JOIN user AS e"로 시작하는 라인 빼고는 모두 동일합니다. 

해당 구문의 역할은 workflow.status = 1(워크플로우가 진행 중인 상태), workflow_person.type = 1(워크플로우를 신청한 유저)를 만족할 경우, 복수의 승인자 중에서 승인을 해야할 유저를 검색하고 있습니다(workflow_person.order로 우선 순위를 정하고 있습니다).

다른 결과가 나오는 이유나 의심스러운 부분이 보이시면, 도움을 주시면 감사하겠습니다!

 

###

죄송합니다. 역시 설명이 부족해서 다들 이해하시는 데 어려움을 겪고 계시군요...

우선 결과로는 /* Where statement subquery */ 로 작성한 구문이 정답입니다. 요점은 "next_approver" 컬럼입니다.

workflow.status: 워크플로우 자체의 상태를 의미 합니다(1: 신청 중, 2: 거절, 3: 승인 완료)

workflow.type: 워크플로우의 종류를 의미합니다(1: 계약 워크플로우, 2: 수선 워크플로우...등등)

workflow_person.type: 워크플로우의 신청자와 승인자가 저장됩니다(1: 신청자, 2: 승인을 해야하는 유저)

workflow_person.status: 승인자의 상태를 의미합니다(1: 기본 값, 2: 거절, 3: 승인) ... 신청자의 상태는 기본 값을 갖고 있지만 실제로는 사용되지 않습니다.

#워크로드

1. 승인자 중 한 명이라도 거절하는 경우, 워크플로우 상태가 거절로 바뀌게 됩니다.

2. 승인자 전부가 승인을 한 경우 워크플로우의 상태가 승인 완료로 바뀌게 됩니다.

3. 승인자가 A, B, C 세 명이 등록되어 있습니다. 승인을 할 수 있는 순번은 workflow_person.item_order로 조정하고 있습니다. A 승인자의 item_order = 1, B 승인자의 item_order = 3, C 승인자의 item_order = 2 인 경우는 A 승인자가 승인을 하지 않으면 item_order = 2 인 C 승인자는 액션 권한을 갖고 있지 않습니다(B 승인자도 물론 없습니다). 

4. 워크플로우의 상태가 신청 중인 경우에만 승인자가 승인 또는 거절을 할 수 있습니다.

5. next_approver을 보여줘야 하는 조건은, 자신이 워크플로우의 신청자인 경우에만 해당합니다.

6. 자신이 워크플로우의 신청자, 자신이 워크플로우의 승인자 이며 승인을 해야하는 순서, 워크플로우의 승인자 이며 승인을 한 워크플로우. 3가지의 경우를 만족하는 데이터만 결과 값으로써 보여줍니다.

by 르매 [2019.06.12 23:14:42]

작성하신 SELECT 문을 살펴보다가 그냥 "구문의 역할"이라고 설명한 부분을 만족하도록 새로 작성했습니다.

alias가 person_status와 person_type인 두 컬럼의 의미가 잘 파악이 되지 않는데, 아마 승인자에 대한 workflow_person.status / type 으로 생각되는군요. 다만, 이렇다고 가정하면 person_type은 항상 2이기 때문에 결과에서 제외했습니다.

SELECT STRAIGHT_JOIN u2.name AS next_approver
	, u1.name AS created_by
    , s.person_status
    , s.workflow_status
    , s.workflow_id
    , s.workflow_no 
FROM (
		SELECT STRAIGHT_JOIN wp1.workflow_id
			, wp1.user_id AS created_by_id
			, w.status AS workflow_status
			, w.no AS workflow_no
			, CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS next_approver_id
            , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.status ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS person_status
		FROM workflow_person wp1 FORCE INDEX FOR JOIN (`fk_workflow_person_user_id`)
			INNER JOIN workflow w FORCE INDEX FOR JOIN (PRIMARY) ON w.id = wp1.workflow_id
			LEFT OUTER JOIN workflow_person wp2 FORCE INDEX FOR JOIN (`fk_workflow_person_workflow_id`) ON wp2.workflow_id = w.id AND wp2.type = 2
		WHERE wp1.user_id = 6
			AND wp1.type = 1
			AND w.status = 1
		GROUP BY wp1.workflow_id, wp1.user_id, w.status, w.no
	) s
    INNER JOIN user u1 FORCE INDEX FOR JOIN (PRIMARY) ON u1.id = s.created_by_id
    LEFT OUTER JOIN user u2 FORCE INDEX FOR JOIN (PRIMARY) ON u2.id = s.next_approver_id;

p.s 작성하신 SELECT 문은 25건이 조회되는데.. user_id=6인 유저가 신청한 워크플로우 중 진행 상태인 워크플로우는 총 19개입니다. 따라서 우리가 만드려는 SELECT 문의 결과도 19건이 나와야 합니다.


by 재팬종쿤 [2019.06.13 10:01:22]

답변 감사합니다.

추가적으로 본문에 내용을 작성하였으니 확인 부탁드립니다.

25건 나오는 게 정답입니다. 

user_id=6인 유저가 신청한 워크플로우 뿐만 아니라, user_id=6이 승인자로써 지정받은 워크플로우도 보여주어야 합니다.

이 부분은 제 설명이 부족했네요.

"GROUP BY wp1.workflow_id, wp1.user_id, w.status, w.no"은 속도면에서 느리지 않을까 라는 생각이 듭니다만... SUBSTRING_INDEX을 이용하는 방법도 좋은 것 같네요!


by 르매 [2019.06.13 10:59:55]

1. GROUP BY wp1.workflow_id, wp1.user_id, w.status, w.no 성능 문제
느낌보다는 explain을 보시면 됩니다. 사실 MySQL에서는 뒤의 세 컬럼을 GROUP BY에서 빼고 실행해도 같은 결과를 보여줍니다. (다만, 성능도 같고 그런 작성법은 ANSI 규격 외라서 그렇게 적지는 않았어요)

그런데 애초에 저런 그룹핑이 사용되는 이유는 워크플로우 하나의 신청자가 2명 이상이 될 것처럼 생긴 모델 때문입니다.

정말로 워크 플로우 1개의 신청자가 2명 이상이 되는 경우가 있나요? 그렇지 않다면 신청자의 user_id는 workflow 테이블에 있어야 합니다. 그리고 workflow_person 테이블은 복수의 승인자만 들어가게 하면 되겠죠. 이것만으로도 지금 작성하는 SELECT 문이 많이 단순해 질 수 있을 겁니다.

2. 내가 현재 승인자로 할당되어 있고, 내가 승인할 순서인 워크 플로우는.. 별도의 SELECT 문으로 작성해서 UNION 해 주는 것도 고려해 보세요. (내가 신청하고 내가 승인할 차례인 경우는 양쪽 집합에서 나올테니 이런 중복을 제거하기위해서 부득이 UNION ALL이 아닌 UNION 사용)
 


by 재팬종쿤 [2019.06.13 11:30:34]

 

답변 감사합니다.

1번에 대해서 말씀 드리면, 워크플로우의 신청자는 1명 뿐입니다.

workflow_person에 신청자를 저장하고 있는 이유는 데이터를 보여줄 때 최근 순으로 등록 또는 승인자로써 지정 받은 워크플로우를 보여줘야 합니다. 그러므로 workflow와 workflow_person를 sorting 하는 것 보다 workflow_person만 sorting 하는 것이 빨랐기 때문에 이렇게 설계를 했습니다.

2번에 대해서 말씀드리면, 어플리케이션 쪽에서 반복문을 이용하여 워크플로우의 승인자인 경우 다음 승인자를 검색하는 SELECT를 하시라는 말씀이신거죠? UNION은 어떤 용도로 쓰이려는 지 잘 모르겠네요...

ANSI 규격에 대해서는 무지하기 때문에 공부 하도록 하겠습니다!

 


by 르매 [2019.06.13 12:52:09]

정렬 성능을 말씀하셨는데.. 신청자가 1명이라면 제가 권해드린 것처럼, 신청자의 user_id는 workflow 테이블에 있어야 합니다. 전체적으로는 득보다 실이 클 듯 하네요.

작성하신 쿼리의 결과에는 workflow.status가 1이 아닌 것도 포함되어 있는데 (2, 4, 6, 1340, 1341 번), 본문에서는 이런 워크플로우는 제외하고 싶다고 하셔서 필터링했습니다. -> 그래서 결과는 20개

어느쪽 성능이 좋은지는 테스트 해보시면 되겠지만, 그보다도 모델의 기능성 컬럼이 필요해 보이네요.
특히.. 다수의 승인자 중 현재 승인할 차례인지 아닌지에 대한 flag 컬럼은 꼭 있어야 할 듯 합니다.
지금처럼 SELECT 할 때마다 실시간으로 줄세우는 건 문제가 있어 보입니다.

아래 쿼리에서 UNION을 기준으로.. 윗쪽은 6번 유저가 신청한 워크플로우 / 아랫쪽은 6번 유저가 승인할 차례인 워크플로우입니다. 두 가지 집합은 결과를 얻기까지의 조회 경로가 다르기 때문에 억지로 합치기 보다는 각각을 UNION 하는게 나아 보입니다.

SET @user_id = 6;

SELECT STRAIGHT_JOIN u2.name AS next_approver
    , u1.name AS created_by
    , s.person_status
    , s.person_type
    , s.workflow_status
    , s.workflow_id
    , s.workflow_no 
FROM (
        SELECT STRAIGHT_JOIN wp1.workflow_id
            , wp1.user_id AS created_by_id
            , w.status AS workflow_status
            , w.no AS workflow_no
            , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS next_approver_id
            , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.status ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS person_status
            , 1 AS person_type
        FROM workflow_person wp1 FORCE INDEX FOR JOIN (`fk_workflow_person_user_id`)
            INNER JOIN workflow w FORCE INDEX FOR JOIN (PRIMARY) ON w.id = wp1.workflow_id
            INNER JOIN workflow_person wp2 FORCE INDEX FOR JOIN (`fk_workflow_person_workflow_id`) ON wp2.workflow_id = w.id AND wp2.type = 2 AND wp2.status = 1
        WHERE wp1.user_id = @user_id
            AND wp1.type = 1
            AND w.status = 1
        GROUP BY wp1.workflow_id
        UNION
        SELECT STRAIGHT_JOIN wp1.workflow_id
            , wp1.user_id AS created_by_id
            , w.status AS workflow_status
            , w.no AS workflow_no
            , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS next_approver_id
            , CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.status ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) AS person_status
            , 2 AS person_type
        FROM workflow_person wp1 FORCE INDEX FOR JOIN (`fk_workflow_person_user_id`)
            INNER JOIN workflow w FORCE INDEX FOR JOIN (PRIMARY) ON w.id = wp1.workflow_id
            INNER JOIN workflow_person wp2 FORCE INDEX FOR JOIN (`fk_workflow_person_workflow_id`) ON wp2.workflow_id = w.id AND wp2.type = 2 AND wp2.status = 1
        WHERE wp1.user_id = @user_id
            AND wp1.type = 2
            AND wp1.status = 1
            AND w.status = 1
        GROUP BY wp1.workflow_id
        HAVING CAST(SUBSTRING_INDEX(GROUP_CONCAT(wp2.user_id ORDER BY wp2.item_order), ',', 1) AS UNSIGNED) = @user_id
    ) s
    INNER JOIN user u1 FORCE INDEX FOR JOIN (PRIMARY) ON u1.id = s.created_by_id
    LEFT OUTER JOIN user u2 FORCE INDEX FOR JOIN (PRIMARY) ON u2.id = s.next_approver_id;

 


by 재팬종쿤 [2019.06.13 13:19:18]

감사합니다!

말씀해주신 내용을 토대로 재검토 해보도록 하겠습니다.


by 마농 [2019.06.13 08:33:18]

작성하신 구문은 표준에 어긋나는 구문이네요.
집계함수 MIN 도 독특하게 작성이 되었고, IF 문도 이해하기 어렵게 작성이 되었네요.
에러가 안나고 동작하기는 하지만 어떻게 동작할지 예측하기 어렵습니다.
르메님처럼 아예 새로 작성하시는게 좋을 듯 합니다.
type, status 의 의미 등을 상세히 알려주시고(테이블마다 해당 항목의 의미가 다른 듯 합니다?)
원본 대비 결과표로 질문해 주시면 좋습니다.


by 재팬종쿤 [2019.06.13 10:12:42]

답변 감사합니다.

표준에 어긋나는 부분이 구체적으로 어디를 말씀하시는 건가요?

조건문에 대해서 설명해드리면

WHERE절의 IF(a.type = 2, IF(a.item_order = 1, a.id = b.id, IF(a.type = 2 AND b.item_order = a.item_order-1 AND b.status = 3, 1, 0)), a.id = b.id) 은

(1) a.type = 2, 워크플로우의 승인자인 경우에 참입니다.

(2) a.item_order = 1, 순번이 1인 경우(승인을 해야하는 순서)에 참입니다.

(3) (2)를 만족할 경우 

      a.id = b.id, 즉 워크플로우의 승인자로 지정받았으며 자신이 승인을 해야하는 순번이므로 ID끼리 비교합니다.

(4) (2)를 만족하지 않을 경우 

      IF(a.type = 2 AND b.item_order = a.item_order-1 AND b.status = 3), 워크플로우의 승인자이고 순번이 1이 아닌 경우에는 조금 복잡합니다... 자신의 순번이 3인 경우, 순번이 2인 승인자의 상태를 보고 자신이 승인을 해야하는 순서인지 아닌지를 판단합니다. 순번이 2인 승인자가 승인을 했을 경우에만 승인을 할 수 있습니다.

(5) (1)을 만족하지 않는 경우, 자신이 신청자이므로 ID끼리 비교합니다.


by 마농 [2019.06.13 12:13:43]
SELECT e.name   AS next_approver
     , d.name   AS created_by
     , a.status AS person_status
     , a.type   AS person_type
     , c.status AS workflow_status
     , c.id     AS workflow_id
     , c.no     AS workflow_no 
  FROM workflow_person a                -- 본인
 INNER JOIN workflow c
    ON c.id = a.workflow_id
 INNER JOIN workflow_person b0          -- 작성자
    ON b0.workflow_id = a.workflow_id
   AND b0.type        = 1
  LEFT OUTER JOIN workflow_person b1    -- 이전승인자
    ON b1.workflow_id = a.workflow_id
   AND b1.type        = a.type
   AND b1.item_order  = a.item_order - 1
  LEFT OUTER JOIN workflow_person b2    -- 다음승인자
    ON b2.workflow_id = a.workflow_id
   AND b2.type        = CASE a.type WHEN 1 THEN 2 ELSE a.type END
   AND b2.item_order  = CASE a.type WHEN 1 THEN 1 ELSE a.item_order + 1 END
 INNER JOIN user d
    ON d.id = b0.user_id
  LEFT OUTER JOIN user e
    ON e.id = b2.user_id
 WHERE a.user_id = 6
   AND a.status != 3
   AND IFNULL(b1.status, 3) = 3         -- 이전승인완료
;

 


by 재팬종쿤 [2019.06.13 13:20:18]

감사합니다!

작성해주신 쿼리 살펴보겠습니다.

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