쿼리 질문이 있습니다 (수량이 0이 될때마다 그룹코드 부여하기) 1 9 202

by query1353 [SQL Query] [2021.02.22 12:27:08]


쿼리 관련하여 수량이 0인 시점마다 그룹코드를 부여하고 싶습니다. 아래와 같이 잔고가 0이 되는 시점이 있는

테이블이 있습니다.

일자 물건 구매유형 수량 잔고
2021.1.1 커피 구매 10 10
2021.1.2 커피 판매 10 0
2021.1.3 커피 구매 10 10
2021.1.4 커피 판매 5 5
2021.1.5 커피 판매 5 0
2021.1.6 커피 구매 1 1
2021.1.7 커피 구매 3 4
2021.1.8 커피 판매 2 2
2021.1.9 커피 판매 2 0

SQL을 활용해 물건에 따라 잔고가 0이 되는 시점까지 동일한 코드를 부여하고 싶습니다.

코드를 부여하고 그룹화하고 이를 통해 최초 구매일을 찾고 싶습니다 ㅠㅠ

아직 하수인지라... 어떻게 해야 되는지 모르겠습니다 

물건 구매유형 수량 잔고 코드 최초 구매일
2021.1.1 커피 구매 10 10 1  
2021.1.2 커피 판매 10 0 1 2021.1.1
2021.1.3 커피 구매 10 10 2  
2021.1.4 커피 판매 5 5 2  
2021.1.5 커피 판매 5 0 2 2021.1.3
2021.1.6 커피 구매 1 1 3  
2021.1.7 커피 구매 3 4 3  
2021.1.8 커피 판매 2 2 3  
2021.1.9 커피 판매 2 0 3 2021.1.6

쿼리 고수님의 도움 요청 드립니다 ㅠㅠ 

by 마농 [2021.02.22 13:04:28]

잔고는 테이블의 실제 컬럼인가요? 아니면 계산된 값인가요?
계산된 퀄럼이라면? 사용된 쿼리를 보여주세요.
DBMS 종류 및 버전이 어떻게 되나요?


by query1353 [2021.02.22 13:07:41]

잔고는 테이블의 실제 컬럼 입니다! DBMS는 현재 hue 4.0 (hive query) 사용하고 있습니다


by 마농 [2021.02.22 13:23:44]
WITH t AS
(
SELECT '2021.01.01' dt, '커피' cd, '구매' gb, 10 cnt, 10 rem FROM dual
UNION ALL SELECT '2021.01.02', '커피', '판매', 10,  0 FROM dual
UNION ALL SELECT '2021.01.03', '커피', '구매', 10, 10 FROM dual
UNION ALL SELECT '2021.01.04', '커피', '판매',  5,  5 FROM dual
UNION ALL SELECT '2021.01.05', '커피', '판매',  5,  0 FROM dual
UNION ALL SELECT '2021.01.06', '커피', '구매',  1,  1 FROM dual
UNION ALL SELECT '2021.01.07', '커피', '구매',  3,  4 FROM dual
UNION ALL SELECT '2021.01.08', '커피', '판매',  2,  2 FROM dual
UNION ALL SELECT '2021.01.09', '커피', '판매',  2,  0 FROM dual
)
-- Oracle  --
SELECT dt, cd, gb, cnt, rem
     , grp
     , DECODE(rem, 0, MIN(dt) OVER(PARTITION BY cd, grp ORDER BY dt)) sdt
  FROM (SELECT dt, cd, gb, cnt, rem
             , COUNT(DECODE(rem, 0, 1)) OVER(PARTITION BY cd ORDER BY dt
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
               ) + 1 grp
          FROM t
        ) a
;

1. 분석함수 및 Window 절 지원여부
 - 분석함수를 지원하는지? Over
 - 또, 분석함수중 Window 부분을 지원하는지? Rows between
2. 일자별 중복 발생 가능성?
 - 일자별 구매및 판매가 여러번 발생 가능하여 일자가 중복된다면? 그 순서를 어떻게 정하는지?
 - 일시 컬럼으로 시분초까지 관리하여 중복되는 일이 없다?
 - 일자 컬럼 외에 순번 항목이 있다?


by query1353 [2021.02.22 16:52:59]

혹시 DECODE 를 CASE WHEN 으로 바꿔주실 수 있을까요? 

HIVE에서 해보는데 계속 오류가 납니다 ㅠ

1. 분석함수 지원 합니다~! WINDOW 부분 지원합니다.

2. 실제로 열어보니 중복되는 일이 없었습니다~! (일자별 1건씩만 나오고 있습니다)


by 마농 [2021.02.22 17:28:11]
SELECT dt, cd, gb, cnt, rem
     , grp
     , CASE rem WHEN 0
       THEN MIN(dt) OVER(PARTITION BY cd, grp ORDER BY dt)
        END sdt
  FROM (SELECT dt, cd, gb, cnt, rem
             , COUNT(CASE rem WHEN 0 THEN 1 END) OVER(PARTITION BY cd ORDER BY dt
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
               ) + 1 grp
          FROM t
        ) a
;

 


by 동동동 [2021.02.23 13:12:27]

마농님

, COUNT(DECODE(rem, 0, 1)) OVER(PARTITION BY cd ORDER BY dt 

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING  ...

 

해당 부분 설명 가능하실련지요?

저게 어떤 동작을 하는건지요?

 


by 동동동 [2021.02.23 16:01:13]

아...

http://www.gurubee.net/lecture/2671

여기에 설명이 있네요..^^


by 마농 [2021.02.23 16:02:31]

1. 우선 분석함수의 기본적인 사용법 및 사용 예를 학습하셔야 합니다.
- 분석함수 http://gurubee.net/lecture/2671
- 윈도우함수 http://gurubee.net/lecture/2674
2. 구문의 이해
- 일반적인 COUNT(*) OVER(ORDER BY 1) 은 누적 카운트를 의미합니다.(처음부터 현재행까지의 누적 건수)
- 여기서 단순 카운트가 아닌 조건을 만족하는 것만 카운트 하기 위해 case 를 사용했구요.
- 0 이 되는 시점이 아닌 0 이 된 다음부터 새로운 그룹이 되므로
- 카운트 범위를 1행 이전으로 조정한 것입니다.
3. 단계적 실습을 통한 이해
- case 문만 따로 실행해 보고
- count() OVER() 까지 실행해 보고
- count() OVER( rows between) 까지 실행해 보고
- 3가지 결과를 비교해 보면 구문 이해에 도움이 됩니다.


by 동동동 [2021.02.23 17:18:58]

마농님 설명 감사드립니다...^^

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