데이터베이스 질문과 답변
by 내일배움카드 [SQL Query] [2022.12.19 17:52:48]
SELECT PLANTID ,STD_TIME ,LINEID ,PRODNAME ,TANK_START ,EXPIRATIONDATE_START ,LOGDATETIME_START ,FILLINGOUTPUT_START ,FILLINGOUTPUT_END , CAST(SUM(CONVERT(DECIMAL, FILLINGOUTPUT_END) - CONVERT(DECIMAL, FILLINGOUTPUT_START)) OVER(PARTITION BY PRODNAME ORDER BY LOGDATETIME_START ASC) AS VARCHAR(100)) 'PRODNAME별 누적값' FROM HY_HACCP_PRD.dbo.EXT_HMI_FILLING_HISTORY x WHERE 1=1 AND PLANTID = 1000 AND LINEID = '충전07' AND STD_TIME = CONVERT(VARCHAR(100), DATEADD(DAY, 0, CONVERT(DATE, '2022-12-15'))) ORDER BY LOGDATETIME_START DESC
PLANTID|STD_TIME |LINEID|PRODNAME|TANK_START|EXPIRATIONDATE_START|LOGDATETIME_START |FILLINGOUTPUT_START|FILLINGOUTPUT_END|PRODNAME별 누적값|
-------+----------+------+--------+----------+--------------------+-----------------------+-------------------+-----------------+-------------+
1000 |2022-12-15|충전07 |애플망고 |D6 |2022-12-30 |2022-12-15 06:40:00.000|81955 |113618 |54566 |
1000 |2022-12-15|충전07 |애플망고 |E2 |2022-12-30 |2022-12-15 05:33:00.000|59052 |81955 |22903 |
1000 |2022-12-15|충전07 |거야 |D4 |2022-12-30 |2022-12-15 04:20:00.000|40838 |59051 |58627 |
1000 |2022-12-15|충전07 |거야 |D3 |2022-12-30 |2022-12-15 01:00:00.000|424 |40838 |40414 |
위 쿼리에 대한 결과값입니다.
그리고 원하는 값은 현재
[PRODNAME별 누적값] 컬럼이 END에서 START를 뺀 값을 제품별로 구분해서 누적값을 관리하고 있는데
START에 대한 데이터가 신빙성이 없어서
END를 LAG를 통해서 이전 값까지의 누적값을 뺀 값으로 하려 했고
, CASE WHEN CONVERT(DECIMAL, FILLINGOUTPUT_END) - CONVERT(DECIMAL, LAG(FILLINGOUTPUT_END) OVER (ORDER BY LOGDATETIME_START ASC)) IS NULL THEN CONVERT(DECIMAL, FILLINGOUTPUT_END) ELSE SUM(CONVERT(DECIMAL, FILLINGOUTPUT_END) - CONVERT(DECIMAL, LAG(FILLINGOUTPUT_END) OVER (ORDER BY LOGDATETIME_START ASC))) OVER (PARTITION BY PRODNAME ORDER BY LOGDATETIME_START ASC) END AS CALC
위와 같은 형식으로 쿼리 짯을 경우
Error occurred during SQL query execution
이유:
SQL Error [4109] [S0001]: 기간 이동 함수는 다른 기간 이동 함수 또는 집계의 컨텍스트에서 사용할 수 없습니다.
아래 에러메시지가 발생했습니다.. 해결 방법 있을까요
집계함수와 분석함수로 나누어 생각해보면
분석함수(집계함수) 형태의 중첩은 가능하지만
집계함수(분석함수) 형태의 중첩은 불가능합니다.
분석함수(분석함수) 형태의 중첩도 불가능합니다.
가능하게 하는 간단한 방법은 인라인뷰를 사용하는 것입니다.
그런데. 분석함수가 필요 없지 않나요?
LAG(FILLINGOUTPUT_END) 대신 FILLINGOUTPUT_START 사용하면 동일한 거 아니었나요?
뭔가 잘못 생각하신 듯 한데
원본 대비 결과표로 질문해 주시면 좋을 것 같습니다.
현재 FILLINGOUTPUT_END가 누적으로 쌓이고 있고
FILLINGOUTPUT_START 의 데이터가 신빙성이 없어서
이전 FILLINGOUTPUT_END를 비교해서 PRODNAME별로 누적값을 관리하려 합니다.
그래서 짠 구문이
SUM(FILLINGOUTPUT_END) OVER (PARTITION BY PRODNAME ORDER BY LOGDATETIME_START ASC)을 통해서
제품별 현 누적치에 대한 합계를 구하고
SUM(LAG(()를 통해 이 전값까지의 누적값을 빼서 합산값을 구하려 했ㅅ습니다.
인라인뷰 한번 찾아보겠습니다 감사합니다.
조회시 결과값 이랑 조금 정리해서 내용 수정하고 댓글 남기겠슴다
CASE 구문에 IS NULL은 제가 쿼리 테스트할 때 첫값은 LAG로 불러올 값이 없어서 NULL로 떠서 그 처음값은 그냥 END에 대한 값으로 대체한 것입니다.
분석함수를 중첩으로 사용하는 이유를 모르겠네요?
그리고 CASE 구문 IS NULL 을 사용하는 이유는 뭔가요?
1. "CONVERT(DECIMAL, " 은
- 꼭 해야 하는 건가요?
- 안해도 된다면 빼는게 가독성이 좋을 듯 하네요.
2. CASE IS NULL 처리는 불필요해 보입니다.
- LAG 에 인자값을 추가하면 널처리 가능합니다.
- LAG(값, 1, 0) 직전값이 없을 경우 0 으로 대체
3. 인라인뷰 이용
- 신빙성이 없는 값 대신 LAG 를 이용한 값을 가져온 뒤(인라인뷰 안에서)
- 인라인뷰 밖에서 이 값을 이용하면 됩니다.
4. std_time 조건은?
- 왜 복잡한거죠?
SELECT plantid
, std_time
, lineid
, prodname
, tank_start
, expirationdate_start
, logdatetime_start
, fillingoutput_start
, fillingoutput_end
, fillingoutput_sta
, SUM(CONVERT(DECIMAL, fillingoutput_end) - CONVERT(DECIMAL, fillingoutput_sta))
OVER(PARTITION BY prodname ORDER BY logdatetime_start) "PRODNAME별 누적값"
FROM (SELECT plantid
, std_time
, lineid
, prodname
, tank_start
, expirationdate_start
, logdatetime_start
, fillingoutput_start
, fillingoutput_end
, LAG(fillingoutput_end, 1, 0) OVER(ORDER BY logdatetime_start) fillingoutput_sta
FROM HY_HACCP_PRD.dbo.EXT_HMI_FILLING_HISTORY x
WHERE 1=1
AND plantid = 1000
AND lineid = '충전07'
AND std_time = '2022-12-15'
) a
ORDER BY logdatetime_start DESC
;
1. 현재 sql function 내부에서 사용되는 쿼리라 실제로 리턴받아서 사용할 때 모든 데이터를 string으로 받고 있어서 내부에서 select시 varchar로 컨버트 하고 있습니다.
2. 넵 인지하고 있슴다, 쿼리 테스트로 짜는 도중에 발생한 사항입니다
3. 인라인뷰 예시 감사합니다.
4. STD_TIME은 현재 테이블에는 varchar타입의 yyyy-mm-dd 만 있는데, 비교하기 위한 데이터는 스칼라변수로 받아오는데 yyyy-mm-dd hh:mm:ss 포맷의 datetime이라서 컨버트해서 비교하기 위해 복잡해졌습니다.
좋은 팁들과 다시 한번 생각해보게끔 질문해주셔서 감사합니다.
SELECT '2' - '1' -- 이게 될 줄 알았는데 오류나네요.
"CONVERT(DECIMAL, " 필요하겠네요.
오라클은 자동형변환 되어 처리됨
) 버튼을 클릭하여 작성 하시면 됩니다.