데이터베이스 질문과 답변
어제 드린 질문 조금 상세하게 다시 드립니다..
mssql 사용중이고 아래 sql function의 수정 하고 싶은 내용은
condition3 라는 항목에 대해 COUNT의 누적합산 컬럼을 추가하고 싶습니다.
function구조가 익숙치 않아서 몇몇 가이드를 봐도 잘 적용이 안되더라구요.. 고수님들 한번 확인 부탁드립니다.
[1.] function:
테이블조회값의 특정 순서& 특정 컬럼을 리턴받기 위해 PARAMETER(순서)와 COLUMNNAME(컬럼) 변수를 사용했고,
select문에서 조회하기 위해 unpivot을 돌린 구조입니다.
밑에 [2.]번은 function에서 조회하는 테이블의 조회문입니다.
1.
CREATE FUNCTION [dbo].[FN_GET_History]
(
@CONDITION1 VARCHAR(50)
,@CONDITION2 VARCHAR(50)
,@INSPECTIONDATE VARCHAR(50)
,@PARAMETER NVARCHAR(100)
,@COLUMNNAME NVARCHAR(100)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @RTN_VALUE VARCHAR(1000)
SET
@RTN_VALUE = (
SELECT [VALUE] FROM (
SELECT * FROM (
SELECT COLUMN, VALUE FROM (
SELECT
CAST(CONDITION1 AS VARCHAR(100)) CONDITION1,
CAST(CONDITION2 AS VARCHAR(100)) CONDITION2,
CAST(CONDITION3 AS VARCHAR(100)) CONDITION3,
CAST(((CONVERT(decimal, OUTPUT_END) - (CONVERT(decimal, OUTPUT_START)))) AS VARCHAR(100)) INDIVIDUAL_COUNT,
CAST(CONVERT(decimal(18, 0), OUTPUT_START) AS VARCHAR(100)) OUTPUT_START,
CAST(CONVERT(decimal(18, 0), OUTPUT_END) AS VARCHAR(100)) OUTPUT_END,
CONVERT(VARCHAR(100), LOGDATETIME_START, 120) LOGDATETIME_START
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY LOGDATETIME_START DESC) AS ROWNUM, *
FROM EXT_HISTORY
WHERE CONDITION1 = @CONDITION1
AND CONDITION2 = @CONDITION2
AND STD_TIME = CONVERT(VARCHAR(100), DATEADD(DAY, 0, CONVERT(DATE, @INSPECTIONDATE)))
) AS HIS_TEMP
WHERE ROWNUM = @PARAMETER) AS TEMP_RESULT
UNPIVOT (VALUE
FOR COLUMN
IN(
[CONDITION1],
[CONDITION2],
[CONDITION3],
[PRODUCT_COUNT],
[OUTPUT_START],
[OUTPUT_END],
[LOGDATETIME_START]
)) AS UNPVT ) AS RES_TABLE
WHERE COLUMN = @COLUMNNAME
) AS RES )
RETURN @RTN_VALUE
END
2.
SELECT ROW_NUMBER() OVER(ORDER BY LOGDATETIME_START DESC) AS ROWNUM,
CAST(CONDITION1 AS VARCHAR(100)),
CAST(CONDITION2 AS VARCHAR(100)),
CAST(CONDITION3 AS VARCHAR(100)),
CAST(((CONVERT(decimal, OUTPUT_END) - (CONVERT(decimal, OUTPUT_START)))) AS VARCHAR(100)) INDIVIDUAL_COUNT,
CAST(CONVERT(decimal(18, 0), OUTPUT_START) AS VARCHAR(100)) OUTPUT_START,
CAST(CONVERT(decimal(18, 0), OUTPUT_END) AS VARCHAR(100)) OUTPUT_END,
CONVERT(VARCHAR(100), LOGDATETIME_START, 120) LOGDATETIME_START
FROM EXT_HISTORY
WHERE CONDITION1 = 1000
AND CONDITION2 = '가나다라'
AND STD_TIME = CONVERT(VARCHAR(100), DATEADD(DAY, 0, CONVERT(DATE, '2022-12-06')))
위와 같이 조회문만 돌렸을 경우에 아래와 같은 결과가 나오고
추가로 [CONDITION3의 누적값 컬럼을 따로 표현하고 싶습니다. ]
| ROWNUM | CONDITION1 | CONDITION2 | CONDITION3 | INDIVIDUAL_COUNT | OUTPUT_START | OUTPUT_END | LOGDATETIME_START |
| 1 | 1000 | 가나다라 | 구분1 | 400 | 500 | 900 | 2022-12-06 10:11:19 |
| 2 | 1000 | 가나다라 | 구분2 | 300 | 200 | 500 | 2022-12-06 09:13:53 |
| 3 | 1000 | 가나다라 | 구분2 | 200 | 0 | 200 | 2022-12-06 07:49:30 |
원하는 결과는 아래와 같습니다.
| ROWNUM | CONDITION1 | CONDITION2 | CONDITION3 | CONDITION3의 누적값 | OUTPUT_START | OUTPUT_END | LOGDATETIME_START |
| 1 | 1000 | 가나다라 | 구분1 | 400 | 500 | 900 | 2022-12-06 10:11:19 |
| 2 | 1000 | 가나다라 | 구분2 | 500 | 200 | 500 | 2022-12-06 09:13:53 |
| 3 | 1000 | 가나다라 | 구분2 | 200 | 0 | 200 | 2022-12-06 07:49:30 |
누적합계는 분석함수 사용하시면 됩니다.
SUM(값) OVER(PARTITION BY condition3 ORDER BY logdatetime_start)
-- 기존 쿼리에 괄호가 너무 많아서 가독성이 떨어지네요 --
, CAST(((CONVERT(DECIMAL, output_end) - (CONVERT(DECIMAL, output_start)))) AS VARCHAR(100)) individual_count
-- 불필요한 괄호 줄이기 --
, CAST( CONVERT(DECIMAL, output_end) - CONVERT(DECIMAL, output_start) AS VARCHAR(100)) individual_count
-- 누적 합계 --
, CAST(SUM(CONVERT(DECIMAL, output_end) - CONVERT(DECIMAL, output_start))
OVER(PARTITION BY condition3 ORDER BY logdatetime_start)
AS VARCHAR(100)) individual_count
기존에 제가 SUM 사용 했을 때 group by 되어 있지 않아서 못했었는데, 제가 완전 잘 못 쓰고 있었네요.. 감사합니다.
-- 인라인뷰(서브쿼리)가 불필요하게 많이 사용되네요. 줄여 봤습니다.
SELECT VALUE
FROM (SELECT CAST(CONDITION1 AS VARCHAR(100)) CONDITION1
, CAST(CONDITION2 AS VARCHAR(100)) CONDITION2
, CAST(CONDITION3 AS VARCHAR(100)) CONDITION3
, CAST(PRODUCT_COUNT AS VARCHAR(100)) PRODUCT_COUNT
, CAST(OUTPUT_START AS VARCHAR(100)) OUTPUT_START
, CAST(OUTPUT_END AS VARCHAR(100)) OUTPUT_END
, CONVERT(VARCHAR(100), LOGDATETIME_START, 120) LOGDATETIME_START
FROM (SELECT ROW_NUMBER() OVER(ORDER BY LOGDATETIME_START DESC) ROWNUM
, CONDITION1
, CONDITION2
, CONDITION3
, SUM(CONVERT(DECIMAL, OUTPUT_END) - CONVERT(DECIMAL, OUTPUT_START))
OVER(PARTITION BY CONDITION3 ORDER BY LOGDATETIME_START) PRODUCT_COUNT
, CONVERT(DECIMAL(18, 0), OUTPUT_START) OUTPUT_START
, CONVERT(DECIMAL(18, 0), OUTPUT_END ) OUTPUT_END
, LOGDATETIME_START
FROM EXT_HISTORY
WHERE CONDITION1 = @CONDITION1
AND CONDITION2 = @CONDITION2
AND STD_TIME = CONVERT(VARCHAR(100), DATEADD(DAY, 0, CONVERT(DATE, @INSPECTIONDATE)))
) HIS_TEMP
WHERE ROWNUM = @PARAMETER
) TEMP_RESULT
UNPIVOT (VALUE FOR COLUMN IN ( [CONDITION1]
, [CONDITION2]
, [CONDITION3]
, [PRODUCT_COUNT]
, [OUTPUT_START]
, [OUTPUT_END]
, [LOGDATETIME_START]
) ) UNPVT
WHERE COLUMN = @COLUMNNAME
;
감사합니다..! 지금 계속 수정중이라 아직 정리를 못했었는데, 참고하도록 하겠습니다 감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.