데이터베이스 질문과 답변
by 팩토리 [SQL Query] MS SQL GROUP BY [2019.07.03 11:57:47]
DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName sysname
SET @TableName = '<TABLE_NAME>'
SET @SQL = ''
SELECT @SQL =
@SQL + 'SELECT ' + QUOTENAME(sc.name, '''') + ' AS ColumnName, ' + QUOTENAME(t.name, '''') + ' AS DataType, ' +
QUOTENAME(sc.max_length, '''') + ' AS SetLength, MAX(DATALENGTH(' + QUOTENAME(sc.name) + ')) AS MaxLength
FROM '+@TableName+ char(10) +' UNION '
FROM sys.columns sc
join sys.types t on t.system_type_id = sc.system_type_id and t.name != 'sysname'
WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)
SET @SQL = LEFT(@SQL, LEN(@SQL)-6)
PRINT @SQL
EXEC(@SQL)
해당 쿼리는 테이블의 모든 컬럼들의 데이터 타입과 사용하고 있는 최대 BYTE를 조사하는 쿼리입니다.
궁금한 것이 있어서 올렸습니다.
1) MAX라는 집계함수를 사용하는데 GROUP BY가 없어도 구문 오류가 없이 잘 실행됩니다.
2) 위 쿼리에서 출력되는 컬럼을 증가시키면 아래와 같은 오류가 발생하고, 노란색 CHAR(10)의 숫자를 좀 변경하면 마찬가지로 출력됩니다.
위 쿼리에 대하여 잘 아는 분들의 조언이 필요합니다.
SELECT 'DOC_ID' AS ColumnName, 'nvarchar' AS DataType, '64' AS SetLength, MAX(DATALENGTH([DOC_ID])) AS MaxLength, (count([DOC_ID]) - count(distinct([DOC_ID])))
FROM TB_MIG_ATTACHP UNION SELECT 'ATTACH_TYPE' AS ColumnName, 'nvarchar' AS DataType, '64' AS SetLength, MAX(DATALENGTH([ATTACH_TYPE])) AS MaxLength, (count([ATTACH_TYPE]) - count(distinct([ATTACH_TYPE])))
FROM TB_MIG_ATTACHP UNION SELECT 'ATTACH_ID' AS ColumnName, 'nvarchar' AS DataType, '2000' AS SetLength, MAX(DATALENGTH([ATTACH_ID])) AS MaxLength, (count([ATTACH_ID]) - count(distinct([ATTACH_ID])))
FROM TB_MIG_ATTACHP UNION SELECT 'ATTACH_PATH' AS ColumnName, 'nvarchar' AS DataType, '2000' AS SetLength, MAX(DATALENGTH([ATTACH_PATH])) AS MaxLength, (count([ATTACH_PATH]) - count(distinct([ATTACH_PATH])))
FROM TB_MIG_ATTACHP
집계함수 사용시 그룹바이 필수는 아닙니다.
그룹바이가 있다면 그룹별 집계, 없으면 전체 집계가 됩니다.
DECLARE @SQL VARCHAR(MAX)
SET @TableName = '<TABLE_NAME>'
SELECT QUOTENAME(sc.name, '''') AS ColumnName, QUOTENAME(t.name, '''') AS DataType, QUOTENAME(sc.max_length, '''') AS SetLength, MAX(DATALENGTH(QUOTENAME(sc.name))) AS MaxLength
join sys.types t on t.system_type_id = sc.system_type_id and t.name != 'sysname'
WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)
EXEC(@SQL)
저 문장을 하나로 바꿔주면 GROUP BY가 지정되지 않았다는 구문 오류가 발생합니다.
sql 구문을 + 기호로 문자열 연결해 가면서 동적쿼리 만들고 있는 건데요.
그걸 정적 쿼리로 바꾸는 과정이 틀린거네요.
그룹바이 없이도 집계함수 사용이 가능하긴하지만
지금 하고자 하는 건 전체 집계가 아닌 그룹바이가 필요한 그룹별 집계네요.
집계함수 사용에 있어 여러 제약사항을 지켜 줘야만 쿼리가 동작하죠.
QUOTENAME 은 아마 따옴표 붙여줄라고 사용한 듯 하네요. 불필요해 보이고.
SELECT sc.name AS ColumnName
, t.name AS DataType
, sc.max_length AS SetLength
, MAX(DATALENGTH(sc.name)) AS MaxLength
FROM sys.columns sc
INNER JOIN sys.types t
ON t.system_type_id = sc.system_type_id
AND t.name != 'sysname'
WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)
GROUP BY sc.name, t.name, sc.max_length
;
다시 생각해 보니 정적 쿼리로 안되는 거네요.
동적쿼리를 써야 할 것 같은데요?
마농님 답변 감사합니다.
이게 동적쿼리 효과 였군요.
역시 공부 안한 부분은 구멍이 나네요. 열심히 연습해 놔야겠어요.
즐거운 하루 되세요.
) 버튼을 클릭하여 작성 하시면 됩니다.