해색주의 조용한 공간

 고객은 대출 상품 시작부터 대출 신청 종료까지 완료해야 합니다. 각 단계별 중단 고객들을 찾는 것이 이 분석의 목적입니다.

문제는 1 ~ 25단계까지 개별 단계 중 일부는 단계별로 나오지 않고 여러번 반복된다는 점을 찾아냈습니다.

1. 특정 단계의 경우, 공통 모듈이므로 여러번 나오게 됩니다.

2. 해당 상품 코드가 제대로 잡히지 않으므로 공통 모듈이 나오는 PAGE_ID가 해당 상품 단계에서 지나친 것인지 아니면, 다른 상품 단계에서 사용된 것인지 알 수가 없습니다. 예를 들어서 KYC/스크랩핑 모듈의 경우 여러 차례 나오게 되므로 어떤 고객은 1단계에서, 

문제는 고객이 1단계를 지나서 바로 14단계까지 나온다는 문제점이 있습니다. 개별 고객의 가장 큰 단계를 가져다 중단 고객의 중단 단계로 정할 경우, 위와 같은 문제점이 발생한다는 것을 발견했습니다.

3. 이번에 분석을 하면서, 중간 단계에서 데이터를 보정하는 방법을 분석할 때 사용했습니다.

4. 데이터 보정의 경우, 제대로 분석 이뤄지지 않는다는 제약이 있다는 생각을 했습니다.

 그래서 제 생각은 이렇게 분석을 하는 것보다는 실제 고객의 PAGE_ID 데이터를 필터링하는 것을 제외하고자 합니다. 클린징된 PAGE_ID 사용하는 것을 과감히 포기하고자 합니다. 이유는 우리가 맞다고 생각했던 PAGE_ID가 실제로 고객이 지나치는 것이 아니라는 것을 발견했기 때문입니다. 이런 이유로 퍼널 분석이 아닌, 단계별 PAGE_VIEW를 볼때에는 PAGE_ID를 사용하기 때문입니다. 우리가 이번에 적용할 방법은 아래와 같습니다.

1. 고객번호 - 단계명 가운데 최초의 것만 가져옵니다.

 이렇게 하면 되겠죠? 

 CREATE TABLE PHASE1 AS

  SELECT *

  FROM (SELECT CUST_NO, STEP, ROW_NUMBER() OVER (PARTITION BY CUST_NO, STEP ORDER BY CUST_NO, STEP, LOG_DTM) AS SEQ

 FROM DTLOG A) A1

WHERE SEQ = 1;

2. 고객별로 단계에 해당 하는 경우의 수를 다 계산해 봅니다. 중단 단계별 세부 STEP을 모두 확인이 가능합니다.

 CREATE TABLE PHASE2 AS

 SELECT CUST_NO, LIST_AGG(STEP, ', ') WITHIN GROUP (ORDER BY STEP) AS STEP_AGG, MAX(STEP) AS FNL_STEP

 FROM PHASE1

 GROUP BY CUST_NO;

 SELECT FNL_STEP, STEP_AGG, COUNT(*) AS CN

 FROM PHASE2

 GROUP BY FNL_STEP, STEP_AGG

 ORDER BY 1, 2;

 이렇게 해서 각 최종 단계별 실제 모습을 확인할 수 있습니다.

3. 위처럼 각 중단 단계별 실제 현황을 파악하고 나서, 최종 단계인 26단계까지 도달한 고객이 모든 단계를 빠지지 않고 간다면 사용 가능하다고 판단합니다. 1에서 26단계까지 중간에 중단한 고객의 경우에는, 이전 단계를 모두 거쳐야만 정상적인 절차로 판단합니다. 따라서 1, 2, 3, 4, 5, 12단계를 거치는 고객의 경우 5단계에서 중단한 것으로 간주하고, 12단계는 해당 상품신청 단계가 아닌 다른 상품신청 단계에서 방문한 페이지로 가주하고 제외합니다.(STEP_CHK = 0)

CREATE TABLE PHASE3 AS

SELECT A1.*

FROM (SELECT CUST_NO, STEP, CASE WHEN LEAD(STEP) OVER (PARTITION BY CUST_NO, STEP ORDER BY CUST_NO, STEP) = STEP THEN 1 ELSE 0 END AS STEP_CHK

 FROM PHASE1 ) A1

WHERE STEP_CHK = 1;

 말 그대로이다. 많이 배워야 하는 부분은 여전히 머신러닝, 딥러닝이기는 한데, 요즘 일하면서 오라클 코딩 튜닝을 어느 정도 해봐야 할 것 같아서 교보문고 갔다가 괜찮다고 생각해서 들고온 책인데 상당히 불친절하다. 아마 대부분의 글의 내용을 쿼리에 집중하고 싶었던 것 같고, 환경 구성이나 오라클 설치같은 부분을 넣다 보면 시간을 많이 잡아먹을 것 같아서 그런게 아닐까 싶다.

 오라클 익스프레스를 다운로드 받아고 sql developer까지 설치해서 연동하려고 하다가, Oracle LiveSQL로 해야겠다는 생각에 다 지우고 주말내내 LiveSQL로 실습을 했다. 아직까지는 쿼리에 대한 내용이 주이고, 힌트나 플랜과 같은 어려운 부분은 아직 나오지 않았다. LiveSQL에서 실행계획은 안나오잖아 생각했는데, 구글에 검색해 보니 아래처럼 실행계획도 지원된다는 재미있는 내용이 있다.

실행 계획도 지원된다. : 세부 내용은 요기를 참조

https://positivemh.tistory.com/375

 나름 이것저것 설치도 해봤다고 생각을 하기는 하지만, 가장 힘든게 환경구성과 설정 잡는 것들이다. 내가 이것때문에 R을 배우다가 여러번 화가 났던 적이 있었고, 중간에 파이썬으로 갈아탄 이유중에 하나이기도 했다. 요즘 세상에 이런거 하려고 오라클을 설치하는게 정말 구시대적인 발상이라고 생각이 들었다. 

 이전 회사에서 하이브와 임팔라를 사용하면서 공부하는데 많은 도움을 받고 쿼리를 작성했던 환경은 아래와 같다.

https://demo.gethue.com/

 위와 같은 HUE 환경을 썼던 나로서는 개인계정이 제공되고 재실행이 되고 쿼리 히스토리 저장도 되는 라이브sql은 정말 감사했던 사이트이다. 머신러닝, 딥러닝, 자연어 처리를 배우면서 초창기에 가장 힘들었던 부분이 환경 구성이었는데, 때마침 접했던 책에서 구글 코랩을 접하게 되었고 처음으로 의존성 문제를 고민하지 않고 순수하게 머신러닝 이론과 코딩에만 집중할 수 있었다. 이전에는 이런거 저런거 설치하고 에러나고 하면서 이론보다는 설치 부분에 대부분의 시간을 잡아 먹었다면 이후에는 그런 부분은 고민 안해도 되서 너무 좋았다.

 지금 내모습이 그렇다. 예전에는 저런 환경 구성을 위해서 좋은 컴퓨터를 사거나 AWS같은 환경 구성을 만들어야만 했는데, 이제는 그럴 필요가 없이 준비된 곳에서 코딩만 하면 된다. 

 회사에서 주로 사용하는 데이터 가운데 json으로 저장된 데이터를 분석하는 경우가 종종 존재한다.

이러한 분석을 할 때, 가장 힘들었던 부분은 자료가 인코딩되어 있던 것을 디코딩하는 것이었다. 처음에는 파이썬으로 분석을 처리하다가, 나중에는 오라클로 한번에 마무리하는 방식으로 변경하면서 처리했다. json이 저장된 컬럼은 clob 타입으로 되어 있어서, 오라클 VARCHAR 길이 제한 4,000을 넘어설 수는 없다. 그래서 아래와 같이 디코딩한 자료를 4000으로 잘라서 처리를 해야 에러가 나지 않는다.

 그리고 나서 추가된 부분을 수정하면서 느꼈던 부분은 배열 형태로 가져오는 것을 위해서 json_query()를 사용했다는 것이다.

SELECT JSON_VALUE(REPLACE(SYS.UTL_ENCODE.TEXT_DECODE(SUBSTR(json_encoding_column, 'UTF8', 1, 4000), '""', ''''), '$.tolCondNm') AS CONV_1, /*일반적인 json 형태의 자료를 가져올 때에는 json_value를 사용한다.*/

JSON_QUERY(REPLACE(SYS.UTL_ENCODE.TEXT_DECODE(SUBSTR(json_encoding_column, 'UTF8', 1, 4000), '""', ''''), '$.tgtId') AS CONV_2 /*배열 형태로 된 자료를 가져오는 경우, json_query를 사용한다. */

FROM JSON_TBL;

 오라클을 사용하면서 참 재있는 기능이 많은데, 그중에 가장 재미있는 기능중에 하나가 바로 이렇게 쿼리를 이용해서 다양한 보고서 기능을 사용할 수 있다는 것이다.

https://stackoverflow.com/questions/26977267/fill-null-values-with-last-non-null-amount-oracle-sql