꿈꾸는 개발자, DBA 커뮤니티 구루비

아무리 테스트 해봐도 안되니 SQL 의견 좀 주세요

by 타잔 [SQL Query] 기간구분 금액 금액합계 가격변동 [2024.03.05 21:16:56]

안녕하세요.
아무리 테스트 해보아도 결과가 안와서 질문 드립니다.

아래 표처럼 왼쪽의 데이터 원본을 오른쪽 결과처럼 생성하고 싶은데 도무지 안되네요.

SALE_PRC 변화별 기간(시작~종료)과 금액을 추출하고 그 기간별 SALE_PRC 합계를 구하는 것입니다.
2023년 월별로 SQL결과가 나오도록 작성해야 하는데 난감합니다.
SALE_PRC 가 각각 다르면 GROUP BY로 가능한데 동일한 금액이 나오는 경우 처리가 힘드네요.

데이터 원본		SQL 결과
SALE_DY	SALE_PRC	FROM_DATE	TO_DATE	SALE_PRC	SUM_SALE_PRC
20231101	1000	20231101	20231105	1,000	4,000
20231102	1000	20231108	20231113	1,500	9,000
20231103	1000	20231115	20231117	1,000	3,000
20231105	1000	20231118	20231121	2,000	8,000
20231108	1500
20231109	1500
20231110	1500
20231111	1500
20231112	1500
20231113	1500
20231115	1000
20231116	1000
20231117	1000
20231118	2000
20231119	2000
20231120	2000
20231121	2000

WITH TEST AS (
SELECT '20231101' AS SALE_DY, 1000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231102' AS SALE_DY, 1000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231103' AS SALE_DY, 1000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231105' AS SALE_DY, 1000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231108' AS SALE_DY, 1500 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231109' AS SALE_DY, 1500 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231110' AS SALE_DY, 1500 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231111' AS SALE_DY, 1500 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231112' AS SALE_DY, 1500 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231113' AS SALE_DY, 1500 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231115' AS SALE_DY, 1000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231116' AS SALE_DY, 1000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231117' AS SALE_DY, 1000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231118' AS SALE_DY, 2000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231119' AS SALE_DY, 2000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231120' AS SALE_DY, 2000 AS SALE_PRC FROM DUAL UNION ALL
SELECT '20231121' AS SALE_DY, 2000 AS SALE_PRC FROM DUAL )
SELECT * FROM TEST;

by 마농 [2024.03.05 23:58:39]

WITH test AS
(
SELECT '20231101' sale_dy, 1000 sale_prc FROM dual
UNION ALL SELECT '20231102', 1000 FROM dual
UNION ALL SELECT '20231103', 1000 FROM dual
UNION ALL SELECT '20231105', 1000 FROM dual
UNION ALL SELECT '20231108', 1500 FROM dual
UNION ALL SELECT '20231109', 1500 FROM dual
UNION ALL SELECT '20231110', 1500 FROM dual
UNION ALL SELECT '20231111', 1500 FROM dual
UNION ALL SELECT '20231112', 1500 FROM dual
UNION ALL SELECT '20231113', 1500 FROM dual
UNION ALL SELECT '20231115', 1000 FROM dual
UNION ALL SELECT '20231116', 1000 FROM dual
UNION ALL SELECT '20231117', 1000 FROM dual
UNION ALL SELECT '20231118', 2000 FROM dual
UNION ALL SELECT '20231119', 2000 FROM dual
UNION ALL SELECT '20231120', 2000 FROM dual
UNION ALL SELECT '20231121', 2000 FROM dual
)
SELECT MIN(sale_dy) from_date
     , MAX(sale_dy) to_date
     , sale_prc
     , SUM(sale_prc) sum_sale_prc
  FROM (SELECT sale_dy, sale_prc
             , SUM(flag) OVER(ORDER BY sale_dy) grp
          FROM (SELECT sale_dy, sale_prc
                     , DECODE(LAG(sale_prc) OVER(ORDER BY sale_dy), sale_prc, 0, 1) flag
                  FROM test
                )
        )
 GROUP BY grp, sale_prc
 ORDER BY grp
;

by 타잔 [2024.03.06 01:18:29]

많은 도움 되었네요. 감사합니다.

by 마농 [2024.03.06 01:21:39]

WITH test AS
(
SELECT '20231101' sale_dy, 1000 sale_prc FROM dual
UNION ALL SELECT '20231102', 1000 FROM dual
UNION ALL SELECT '20231103', 1000 FROM dual
UNION ALL SELECT '20231105', 1000 FROM dual
UNION ALL SELECT '20231108', 1500 FROM dual
UNION ALL SELECT '20231109', 1500 FROM dual
UNION ALL SELECT '20231110', 1500 FROM dual
UNION ALL SELECT '20231111', 1500 FROM dual
UNION ALL SELECT '20231112', 1500 FROM dual
UNION ALL SELECT '20231113', 1500 FROM dual
UNION ALL SELECT '20231115', 1000 FROM dual
UNION ALL SELECT '20231116', 1000 FROM dual
UNION ALL SELECT '20231117', 1000 FROM dual
UNION ALL SELECT '20231118', 2000 FROM dual
UNION ALL SELECT '20231119', 2000 FROM dual
UNION ALL SELECT '20231120', 2000 FROM dual
UNION ALL SELECT '20231121', 2000 FROM dual
)
SELECT MIN(sale_dy) from_date
     , MAX(sale_dy) to_date
     , sale_prc
     , SUM(sale_prc) sum_sale_prc
  FROM (SELECT sale_dy, sale_prc
             , ROW_NUMBER() OVER(ORDER BY sale_dy) rn1
             , ROW_NUMBER() OVER(PARTITION BY sale_prc ORDER BY sale_dy) rn2
          FROM test
        )
 GROUP BY sale_prc, rn1 - rn2
 ORDER BY 1
;

by 타잔 [2024.03.06 23:43:25]

오!!! 감사합니다.
동일한 일자에 가격이 다른 경우가 존재하여 어떻게 처리하나 고민하고 있었는데
위의 쿼리로 다 해결 되네요.

by 마농 [2024.03.07 10:18:39]

동일 날짜가 있다면?
동일날짜끼리의 추가 정렬기준이 필요합니다.
어떤 금액이 먼저 등록되었는지 구별이 가능해야 합니다.

by 타잔 [2024.03.07 12:36:58]

아 DENSE_RANK 로 해결했네요.
전체 데이터 다시 테스트해 보니 가격이 동일하다가 동일 일자에 하락한 경우 동일 일자에 상승한 경우 등등 있더라고요.
신경써 주셔서 감사합니다.

WITH TEST AS (
  SELECT '20231101' SALE_DY, 1000 SALE_PRC FROM DUAL
UNION ALL SELECT '20231102', 1000 FROM DUAL
UNION ALL SELECT '20231103', 1000 FROM DUAL
UNION ALL SELECT '20231104', 1000 FROM DUAL 
UNION ALL SELECT '20231104', 1500 FROM DUAL -- 별도의 한개 그룹 구성 
UNION ALL SELECT '20231105', 1000 FROM DUAL -- 위의 1000원 기간에 포함 시키려면 ? : 01일 ~ 06일  1000으로 그룹, 04일 1500 별도 그룹 
UNION ALL SELECT '20231106', 1000 FROM DUAL
UNION ALL SELECT '20231108', 1500 FROM DUAL
UNION ALL SELECT '20231109', 1500 FROM DUAL
UNION ALL SELECT '20231110', 1500 FROM DUAL
UNION ALL SELECT '20231111', 1500 FROM DUAL
UNION ALL SELECT '20231112', 1500 FROM DUAL
UNION ALL SELECT '20231113', 1000 FROM DUAL -- 아래 1000원 기간에 포함 시키려면 ? : 08일 ~ 13일 1500으로 그룹 
UNION ALL SELECT '20231113', 1500 FROM DUAL -- 위의 1500원 기간에 포함 시키려면 ? : 13일 ~ 17일 1000으로 그룹
UNION ALL SELECT '20231114', 1000 FROM DUAL
UNION ALL SELECT '20231115', 1000 FROM DUAL
UNION ALL SELECT '20231116', 1000 FROM DUAL
UNION ALL SELECT '20231117', 1000 FROM DUAL
UNION ALL SELECT '20231118', 2000 FROM DUAL
UNION ALL SELECT '20231119', 2000 FROM DUAL
UNION ALL SELECT '20231120', 2000 FROM DUAL
UNION ALL SELECT '20231121', 2000 FROM DUAL
)
SELECT MIN(SALE_DY) FROM_DATE
     , MAX(SALE_DY) TO_DATE
     , SALE_PRC
     , SUM(SALE_PRC) SUM_SALE_PRC
     , COUNT(*) PRC_CNT
  FROM (SELECT SALE_DY, SALE_PRC
             , DENSE_RANK() OVER(ORDER BY SALE_DY) RN1
             , DENSE_RANK() OVER(PARTITION BY SALE_PRC ORDER BY SALE_DY) RN2
          FROM TEST
        )
 GROUP BY SALE_PRC, RN1 - RN2
 ORDER BY 1;

20231101    20231106    1000    6000    6
20231104    20231104    1500    1500    1
20231108    20231113    1500    9000    6
20231113    20231117    1000    5000    5
20231118    20231121    2000    8000    4

by 마농 [2024.03.07 13:09:27]

dense_rank 로 해결할 문제가 아닙니다.
어느 값이 먼저인지를 알 수 있는 추가 정보가 반드시 있어야 합니다.

by 타잔 [2024.03.07 16:00:46]

동일 일자에 대해서 어느 금액이 먼저인지는 현재 알 수 없습니다.
전체 데이터 적용하니 예상 결과와 맞게 추출이 됩니다.

by 마농 [2024.03.07 16:32:11]

예시의 다음 4건에 대해
실질적인 순서를 알 수 없다면?
다음과 같이 2가지 순서가 모두 가능합니다.
-- CASE 1 --
'20231112', 1500
'20231113', 1000
'20231113', 1500
'20231114', 1000
-- CASE 2 --
'20231112', 1500
'20231113', 1500
'20231113', 1000
'20231114', 1000
이 경우
CASE1 은 결과가 4줄로 나올 것이고
CASE2 은 결과가 2줄로 나올 것입니다.
그런데 실제 순서를 알 수가 없다구요???

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변