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

데이터 최고점일때 카운트 할수 있나요?

by 엠컨 [SQL Query] [2024.05.02 15:39:30]

안녕하세요

SELECT A_DATE

,A_DATA

FROM TABLE

ORDER BY A_DATE

로우데이터가 있는데 날짜 컬럼과 데이터 컬럼이 있습니다.

데이터가 고점을 찍고 내려오면 1을 카운터 하게 하고 싶은데 오차가 조금 있어도

데이터가 아래와 같이 들어온다면

1 1 1 2 2 3 4 5 6 7 7 8 8 9 10 10 9 10 8 7 6 6 5 4 3 2 3 5 6 6 7 8 9 10 8 9 8 7 6 5 4 3 2 1

최고값인 10을 갔다가 2를 찍고 다시 10을 갔다가 1로 내려갔는데

이때 카운트 2 가 나올수 있게 쿼리를 작성할 수 있을까요? 함수나 이런게 없을까요?

by 마농 [2024.05.02 15:46:11]

원칙이 모호한데요?

by 엠컨 [2024.05.02 15:50:07]

음 그럼 8이상이 되면 카운트를 1 올리고 3 이하가 됐다가 다시 8이상이 되면 카운트를 1 올리는 방식으로도 어려울까요?

8이상에서 계속 8 9 10 이 유지되어도 카운트는 올라가지 않게요

원료 투입시 Batch 수를 계산 하기 위함입니다. 원료 중량이 늘어났다가 줄어들면 1개 카운트 하는것입니다.

by 대궁이 [2024.05.02 16:05:28]

10 10 9 10 8 7 6 6 5 4 3 => 여기서 카운트 1

10 8 9 8 7 6 5 4 3 => 카운트 2

최고점이 10이라는 가정하에 이렇게 해서 2개의 카운트를 세시려는 건가요?

본문에는 3이하라는 조건이 없어서 최저점이 1이라서 1을 기준으로 하면 카운트가 2개가 아니라서 이해가 안가긴했는데 3이하라면 제 댓글처럼 해석하면 되는건가요?

아 전 날짜없이 작성을 해봤습니다.

WITH TBL AS 
(
SELECT 1 AS SN FROM DUAL UNION ALL
SELECT 1 AS SN FROM DUAL UNION ALL
SELECT 1 AS SN FROM DUAL UNION ALL
SELECT 2 AS SN FROM DUAL UNION ALL
SELECT 2 AS SN FROM DUAL UNION ALL
SELECT 3 AS SN FROM DUAL UNION ALL
SELECT 4 AS SN FROM DUAL UNION ALL
SELECT 5 AS SN FROM DUAL UNION ALL
SELECT 6 AS SN FROM DUAL UNION ALL
SELECT 7 AS SN FROM DUAL UNION ALL
SELECT 7 AS SN FROM DUAL UNION ALL
SELECT 8 AS SN FROM DUAL UNION ALL
SELECT 8 AS SN FROM DUAL UNION ALL
SELECT 9 AS SN FROM DUAL UNION ALL
SELECT 10 AS SN FROM DUAL UNION ALL
SELECT 10 AS SN FROM DUAL UNION ALL
SELECT 9 AS SN FROM DUAL UNION ALL
SELECT 10 AS SN FROM DUAL UNION ALL
SELECT 8 AS SN FROM DUAL UNION ALL
SELECT 7 AS SN FROM DUAL UNION ALL
SELECT 6 AS SN FROM DUAL UNION ALL
SELECT 6 AS SN FROM DUAL UNION ALL
SELECT 5 AS SN FROM DUAL UNION ALL
SELECT 4 AS SN FROM DUAL UNION ALL
SELECT 3 AS SN FROM DUAL UNION ALL
SELECT 2 AS SN FROM DUAL UNION ALL
SELECT 3 AS SN FROM DUAL UNION ALL
SELECT 5 AS SN FROM DUAL UNION ALL
SELECT 6 AS SN FROM DUAL UNION ALL
SELECT 6 AS SN FROM DUAL UNION ALL
SELECT 7 AS SN FROM DUAL UNION ALL
SELECT 8 AS SN FROM DUAL UNION ALL
SELECT 9 AS SN FROM DUAL UNION ALL
SELECT 10 AS SN FROM DUAL UNION ALL
SELECT 8 AS SN FROM DUAL UNION ALL
SELECT 9 AS SN FROM DUAL UNION ALL
SELECT 8 AS SN FROM DUAL UNION ALL
SELECT 7 AS SN FROM DUAL UNION ALL
SELECT 6 AS SN FROM DUAL UNION ALL
SELECT 5 AS SN FROM DUAL UNION ALL
SELECT 4 AS SN FROM DUAL UNION ALL
SELECT 3 AS SN FROM DUAL UNION ALL
SELECT 2 AS SN FROM DUAL UNION ALL
SELECT 1 AS SN FROM DUAL UNION ALL
SELECT 10 AS SN FROM DUAL 
)
SELECT *
 FROM (
        SELECT X.* ,
               LAG(SN,1)OVER(ORDER BY CHK_ROW) AS PRE_ROW, 
               LEAD(SN,1)OVER(ORDER BY CHK_ROW) AS NEXT_ROW
         FROM  (
                SELECT CASE 
                         WHEN SN <=3 THEN 1
                         WHEN SN >=8 THEN 10
                         ELSE SN 
                        END AS SN ,
                       ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY 1) AS CHK_ROW
                  FROM TBL
               ) X,
               (
                SELECT MIN( CASE WHEN SN <=3 THEN 1 WHEN SN >=8 THEN 10 ELSE SN END ) AS MIN_SN,
                       MAX(SN) AS MAX_SN
                  FROM TBL
               ) Y
        WHERE SN = MIN_SN OR SN=MAX_SN 
    )
WHERE SN != NEXT_ROW  
  AND SN > NEXT_ROW
 ;

by 엠컨 [2024.05.02 16:09:58]

넵 맞습니다 3이하로 2개 카운트 되면 됩니다

최고점도 8이상이면 됩니다

by 마농 [2024.05.02 17:08:01]

WITH t AS
(
SELECT '20240101' dt, 1 v FROM dual
UNION ALL SELECT '20240102',  1 FROM dual
UNION ALL SELECT '20240103',  1 FROM dual
UNION ALL SELECT '20240104',  2 FROM dual
UNION ALL SELECT '20240105',  2 FROM dual
UNION ALL SELECT '20240106',  3 FROM dual
UNION ALL SELECT '20240107',  4 FROM dual
UNION ALL SELECT '20240108',  5 FROM dual
UNION ALL SELECT '20240109',  6 FROM dual
UNION ALL SELECT '20240110',  7 FROM dual
UNION ALL SELECT '20240111',  7 FROM dual
UNION ALL SELECT '20240112',  8 FROM dual
UNION ALL SELECT '20240113',  8 FROM dual
UNION ALL SELECT '20240114',  9 FROM dual
UNION ALL SELECT '20240115', 10 FROM dual
UNION ALL SELECT '20240116', 10 FROM dual
UNION ALL SELECT '20240117',  9 FROM dual
UNION ALL SELECT '20240118', 10 FROM dual
UNION ALL SELECT '20240119',  8 FROM dual
UNION ALL SELECT '20240120',  7 FROM dual
UNION ALL SELECT '20240121',  6 FROM dual
UNION ALL SELECT '20240122',  6 FROM dual
UNION ALL SELECT '20240123',  5 FROM dual
UNION ALL SELECT '20240124',  4 FROM dual
UNION ALL SELECT '20240125',  3 FROM dual
UNION ALL SELECT '20240126',  2 FROM dual
UNION ALL SELECT '20240127',  3 FROM dual
UNION ALL SELECT '20240128',  5 FROM dual
UNION ALL SELECT '20240129',  6 FROM dual
UNION ALL SELECT '20240130',  6 FROM dual
UNION ALL SELECT '20240131',  7 FROM dual
UNION ALL SELECT '20240201',  8 FROM dual
UNION ALL SELECT '20240202',  9 FROM dual
UNION ALL SELECT '20240203', 10 FROM dual
UNION ALL SELECT '20240204',  8 FROM dual
UNION ALL SELECT '20240205',  9 FROM dual
UNION ALL SELECT '20240206',  8 FROM dual
UNION ALL SELECT '20240207',  7 FROM dual
UNION ALL SELECT '20240208',  6 FROM dual
UNION ALL SELECT '20240209',  5 FROM dual
UNION ALL SELECT '20240210',  4 FROM dual
UNION ALL SELECT '20240211',  3 FROM dual
UNION ALL SELECT '20240212',  2 FROM dual
UNION ALL SELECT '20240213',  1 FROM dual
)
SELECT COUNT(*) cnt
  FROM (SELECT v
             , LAG(v) OVER(ORDER BY dt) x
          FROM t
         WHERE v <= 3    -- 저점
            OR v >= 8    -- 고점
        )
 WHERE x >= 8    -- 고점에서 시작하여
   AND v <= 3    -- 저점으로 끝난자료
;

by 엠컨 [2024.05.02 17:48:11]

와 이렇게 간단하게 된다니 ㅜㅜ

감사합니닷!!

댓글등록

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

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

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

데이터베이스 질문과 답변