Archive for

간단한 조회쿼리를 작성하다가 기억하고싶은 부분이 있어서 기록해본다.

조건 및 쿼리 작성 의도 

• pk가 아닌 '검색어' 컬럼 SCT가 핵심
• 테이블에 검색어가 들어가는데, 이때 똑같은 검색어가 몇번이고 중복되어들어갈수있다. 단, 일련번호와 등록 시점이 다름.
• 이때 SCT를 중복을 제거한 상태로 목록에 불러오고 중복된 횟수를 나란히 출력한다. 

• 즉 출력 목록은 그룹화되고 중복이 제거된 검색어, 검색어별 중복검색횟수. 
• - 정렬기준은 중복 횟수의 내림차순

[미동작 쿼리]

• 중복제거된 검색어 기준으로 GROUP BY를 해야하고, 동시에 ORDER BY는 검색된 최신날짜를 기준으로 정렬하고싶었다. 
• 그런데 문제는 아래와 같이 작성하면 전체 검색어의 총개수와 그룹화되고 정렬된 검색어의 총개수가 안맞는다는 점이었다.
• 문제상황 :

중복제거된 검색어 수 (DISTINCT SCT) 
=/=
그룹화되고 정렬화된 검색어수 (GROUP BY,ORDER BY)

SELECT 
   DISTINCT CEIL(COUNT(DISTINCT SCT) OVER() / 1) AS TOT_CNT /* 중복제거된 검색어들의 총 개수 */
 , SCT  /* 검색어 */
 , COUNT(SCT) AS DUP_SCT  /* 동일한 검색어 검색 횟수 */
 , REG_DT    /* 등록일(검색일) */
FROM TN_SCT
WHERE 1=1
AND TO_CHAR(REG_DT, 'YYYYMMDD') >= '20240106'
AND TO_CHAR(REG_DT, 'YYYYMMDD') <= '20240108'
GROUP BY SCT, REG_DT
ORDER BY MAX(REG_DT) DESC
;

• 검색결과 GROUP BY, ORDER BY를 조합함으로서 생략되는 데이터, 혹은 중복되는 데이터가 발생하고
• 그결과 검색어수가 불일치한 원인이 되는 것 같았다. 
• 챗지피티에도 질문해보았다. 

쿼리의 결과가 예상과 다른 이유는 GROUP BY 구문과 COUNT(DISTINCT SCT) OVER()의 사용 방식에 있습니다.

COUNT(DISTINCT SCT) OVER()은 윈도우 함수로서, 전체 결과 집합에 대해 중복을 제거하고 그 결과의 총 개수를 계산합니다. 즉, COUNT(DISTINCT SCT) OVER()는 GROUP BY와는 관계없이 전체 데이터에 대한 중복 없는 SCT의 개수를 계산합니다.

반면에 GROUP BY SCT, REG_DT를 사용한 부분은 각각의 그룹에 대해 SCT와 REG_DT의 조합을 기준으로 그룹을 형성하고, 각 그룹에 대해 COUNT(SCT)를 계산합니다. 이는 SCT와 REG_DT의 조합에 따라 여러 그룹이 형성되기 때문에 COUNT(SCT)의 결과가 COUNT(DISTINCT SCT) OVER()와 다를 수 있습니다.

결론적으로, COUNT(DISTINCT SCT) OVER()는 전체 결과 집합에 대한 중복 없는 SCT의 개수를 계산하고, GROUP BY SCT, REG_DT는 SCT와 REG_DT의 조합에 따라 그룹을 형성하고 각 그룹의 SCT 개수를 계산합니다. 따라서 두 개의 결과가 서로 다를 수 있습니다.

• 하나씩 제거해보니 조건에서 등록날짜만 제거하면 검색어 결과수가 일치했다.
• 그래서 REG_DT를 ORDER BY에서 그룹기준 정렬하는게 아니라 SELECT 목록으로 빼고 ALIAS를 줘보았다.

[동작 쿼리]

SELECT 
   DISTINCT CEIL(COUNT(DISTINCT SCT) OVER() / 1) AS TOT_CNT /* 중복제거된 검색어들의 총 개수 */
 , SCT  /* 검색어 */
 , COUNT(SCT)  AS DUP_SCT  /* 동일한 검색어 검색 횟수 */
 , MAX(REG_DT) AS REG_DT    /* 등록일(검색일) */
FROM TN_SCT
WHERE 1=1
AND TO_CHAR(REG_DT, 'YYYYMMDD') >= '20240106'
AND TO_CHAR(REG_DT, 'YYYYMMDD') <= '20240108'
GROUP BY SCT
ORDER BY REG_DT DESC
;

* GROUP BY와 ORDER BY 사용시 유의사항

•  GROUP BY와 ORDER BY는 일반적으로 같은 열(컬럼)을 지칭하지 않아도 됩니다. 그러나 몇 가지 사항을 고려해야 합니다.
• 1. 집계 함수 사용 시: GROUP BY를 사용할 때 집계 함수(예: COUNT, SUM, AVG 등)를 사용하면 해당 집계 함수가 적용된 열을 기준으로 그룹화됩니다. 이때 ORDER BY에서도 동일한 집계 함수를 사용해야 합니다.

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

• 2. Alias 사용 시: GROUP BY 또는 ORDER BY에서 열에 별칭(Alias)을 사용하면, 이 별칭을 그대로 사용할 수 있습니다.
• 위 예제에서 employee_count는 GROUP BY와 ORDER BY에서 모두 사용됩니다.

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

• 3. 순서 지정: 결과를 정렬하는 경우 ORDER BY에 지정한 열의 순서는 GROUP BY와 일치하지 않아도 됩니다.
• 위 예제에서는 employee_count를 내림차순으로 정렬하고, 동일한 employee_count일 경우 department를 오름차순으로 정렬합니다.

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC, department ASC;

문제 

예전에는 운영체제에서 크로아티아 알파벳을 입력할 수가 없었다. 따라서, 다음과 같이 크로아티아 알파벳을 변경해서 입력했다.

예를 들어, ljes=njak은 크로아티아 알파벳 6개(lj, e, š, nj, a, k)로 이루어져 있다. 단어가 주어졌을 때, 몇 개의 크로아티아 알파벳으로 이루어져 있는지 출력한다.
dž는 무조건 하나의 알파벳으로 쓰이고, d와 ž가 분리된 것으로 보지 않는다. lj와 nj도 마찬가지이다. 위 목록에 없는 알파벳은 한 글자씩 센다.

입력

첫째 줄에 최대 100글자의 단어가 주어진다. 알파벳 소문자와 '-', '='로만 이루어져 있다.
단어는 크로아티아 알파벳으로 이루어져 있다. 문제 설명의 표에 나와있는 알파벳은 변경된 형태로 입력된다.

출력

입력으로 주어진 단어가 몇 개의 크로아티아 알파벳으로 이루어져 있는지 출력한다.

제출답안

import java.util.Scanner;

public class Main{

	public static void main(String[] args){

		Scanner sc = new Scanner(System.in);
		String words = sc.nextLine();
		String[] croatia = {"c=", "c-", "dz=", "d-", "lj", "nj", "s=", "z="};

		sc.close();

		for(int i=0; i<croatia.length; i++){
		// 단어 words 에 크로아티아 배열 인덱스의 단어가 있으면 한글자로 대체
			while(words.contains(croatia[i])){
				words = words.replaceAll(croatia[i], "A");
			}
		}

	System.out.println(words.length());

	}
}

Q.

SELECT 조회를 수행하면서 조회 결과로 출력되는 데이터집합의 총 ROW수를 어떻게 구해야할까? 

A.

• 다른 컬럼을 조회하면서 동시에 결과 집합의 총 행 수를 구하려면 COUNT() 함수와 윈도우 함수를 함께 사용할 수 있다. 

SELECT
    column1,
    column2,
    COUNT(*) OVER () AS total_rows
FROM
    your_table
WHERE
    your_condition;

• your_table은 대상 테이블이고, your_condition은 원하는 행을 선택하기 위한 조건이다. 
• 이 쿼리는 your_table에서 your_condition을 만족하는 각 행에 대해 column1과 column2를 조회하면서, 동시에 전체 행 수를 나타내는 total_rows를 반환한다. 

COUNT(*) OVER () 란? 

• COUNT(*) OVER ()는 SQL에서 사용되는 윈도우 함수(Window Function) 중 하나로, 전체 결과 집합의 총 행 수를 각 행에 포함된 결과로 반환하는데 사용된다. 
• 이 함수는 각 행에 동일한 값을 부여하며, 그 값은 전체 결과 집합의 행 수다.
• 즉, COUNT(*)는 각 행이 속한 전체 결과 집합의 행 수를, OVER ()는 윈도우 함수가 전체 결과 집합에 대해 적용되도록 지정한다.

그 외의 윈도우 함수(Window Function)

• SQL에서 사용되는 윈도우 함수는 다양하며, 데이터를 파티션별로 나누고 정렬된 순서대로 처리하는데 사용된다. 

ROW_NUMBER(): 결과 집합 내에서 각 행에 대한 순서 번호를 할당합니다.

SELECT
    column1,
    column2,
    ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
FROM
    your_table;

RANK(): 결과 집합 내에서 값이 동일한 행을 하나의 등급으로 묶어 순서 번호를 할당합니다. 같은 값이 여러 번 나타날 경우 같은 등급이 부여됩니다.

SELECT
    column1,
    column2,
    RANK() OVER (ORDER BY some_column) AS rank_num
FROM
    your_table;

DENSE_RANK(): RANK()와 비슷하지만, 같은 값이 여러 번 나타날 경우 중복된 등급이 부여되지 않습니다.

SELECT
    column1,
    column2,
    DENSE_RANK() OVER (ORDER BY some_column) AS dense_rank_num
FROM
    your_table;

SUM() OVER(): 결과 집합 내의 각 행에 대해 누적 합계를 계산합니다.

SELECT
    column1,
    column2,
    SUM(column2) OVER (ORDER BY some_column) AS cumulative_sum
FROM
    your_table;

AVG() OVER(): 결과 집합 내의 각 행에 대해 평균을 계산합니다.

SELECT
    column1,
    column2,
    AVG(column2) OVER (ORDER BY some_column) AS average
FROM
    your_table;

LEAD(): 현재 행 다음에 나오는 값을 가져옵니다.

SELECT
    column1,
    column2,
    LEAD(column2) OVER (ORDER BY some_column) AS next_value
FROM
    your_table;

LAG(): 현재 행 이전에 나오는 값을 가져옵니다.

SELECT
    column1,
    column2,
    LAG(column2) OVER (ORDER BY some_column) AS previous_value
FROM
    your_table;