SQL Server에서 A, B, 그리고 각각의 A 값에 대한 카운트를 하나의 행에 열로 표시하려면, 피벗(PIVOT) 기능을 사용할 수 있습니다. 피벗을 사용하면 각 A 값이 열로 변환되며, 각 열의 값은 해당 A 값의 카운트가 됩니다.

가정: 테이블 구조

• TableName 테이블에 A, B 열이 있다고 가정합니다.
• A 열에는 여러 종류의 값이 있으며, 이 값들을 기준으로 카운트를 집계합니다.

예제: 피벗을 사용하여 A 값의 카운트를 열로 표시

SELECT 
    B,
    [A1],
    [A2],
    [A3],
    [A4],
    [A5]
FROM (
    SELECT 
        B,
        A,
        COUNT(1) AS CountA
    FROM 
        TableName
    GROUP BY 
        B, A
) AS SourceTable
PIVOT (
    SUM(CountA)
    FOR A IN ([A1], [A2], [A3], [A4], [A5])
) AS PivotTable
ORDER BY B;​

설명:

1. 서브쿼리: 먼저 B, A, 그리고 각 A 값의 카운트를 집계합니다.
   • GROUP BY B, A: B와 A로 그룹화하여 각 A 값에 대한 카운트를 계산합니다.
2. 피벗:
   • PIVOT (SUM(CountA) FOR A IN ([A1], [A2], [A3], [A4], [A5])): 각 A 값이 열로 변환되며, 각 열의 값은 해당 A의 카운트입니다.
3. 결과: 최종적으로 B와 각 A 값의 카운트를 열로 표시합니다.

주의사항:

• IN 절에 지정된 A1, A2, A3, A4, A5는 실제로 A 컬럼에 있는 값으로 대체해야 합니다. 예를 들어, A 값이 'Type1', 'Type2', 'Type3' 등일 경우, 해당 값을 정확하게 열 이름으로 지정해야 합니다.
• 만약 A 값의 종류가 동적으로 변화한다면, 동적 SQL을 사용하여 피벗 쿼리를 작성해야 할 수 있습니다.

동적 피벗 예시

동적 피벗을 사용하면 테이블의 A 값이 변해도 자동으로 피벗을 적용할 수 있습니다.

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- A 값 가져오기
SELECT @cols = STRING_AGG(QUOTENAME(A), ', ')
FROM (SELECT DISTINCT A FROM TableName) AS Temp;

-- 피벗 쿼리 생성
SET @query = 'SELECT B, ' + @cols + '
              FROM (
                  SELECT B, A, COUNT(1) AS CountA
                  FROM TableName
                  GROUP BY B, A
              ) AS SourceTable
              PIVOT (
                  SUM(CountA)
                  FOR A IN (' + @cols + ')
              ) AS PivotTable
              ORDER BY B;';

-- 실행
EXEC sp_executesql @query;​

이 동적 피벗 예시는 A 값의 종류에 따라 자동으로 피벗 쿼리를 생성하고 실행합니다.

[DataBase] Postgres를 검색엔진으로 활용하기  

https://anyblockers.com/posts/postgres-as-a-search-engine

• 
  Postgres 내에서 시맨틱, 전문, 퍼지 검색을 모두 갖춘 하이브리드 검색 엔진을 구축할 수 있음
• 검색은 많은 앱에서 중요한 부분이지만 제대로 구현하기 쉽지 않음. 특히 RAG 파이프라인에서는 검색 품질이 전체 프로세스의 성패를 좌우할 수 있음
• 의미론적(Semantic) 검색이 트렌디하지만, 전통적인 어휘 기반 검색은 여전히 검색의 중추임
• 의미론적 기술은 결과를 개선할 수 있지만, 견고한 텍스트 기반 검색의 기반 위에서 가장 잘 작동함

Postgres를 활용한 검색 엔진 구현하기

• 세 가지 기술을 결합:
  • tsvector를 사용한 전체 텍스트 검색
  • pgvector를 사용한 의미론적 검색
  • pg_trgm을 사용한 퍼지 매칭
• 이 접근 방식은 모든 상황에서 절대적으로 최고는 아닐 수 있지만, 별도의 검색 서비스를 구축하는 것에 대한 훌륭한 대안임
• 기존 Postgres 데이터베이스 내에서 구현하고 확장할 수 있는 견고한 출발점
• Postgres를 모든 것에 사용해야 하는 이유 : 그냥 Postgres를 모든 곳에 사용하세요, PostgreSQL로 충분하다, 그냥 Postgres 쓰세요

FTS와 의미론적 검색 구현

• Supabase에 하이브리드 검색 구현에 대한 훌륭한 문서가 있으므로, 이를 시작점으로 삼을 것임
• 가이드에 따라 GIN 인덱스를 사용하여 FTS를 구현하고, pgvector(bi-encoder dense retrieval이라고도 함)를 사용하여 의미론적 검색을 구현함
• 개인적인 경험으로는 1536차원의 임베딩을 선택하는 것이 훨씬 더 나은 결과를 얻을 수 있음
• Supabase 함수를 CTE와 쿼리로 대체하고, 매개변수 앞에 $를 붙임.
• 여기서는 RRF(Reciprocal Ranked Fusion)를 사용하여 결과를 병합함
• 이 방법은 여러 목록에서 높은 순위를 차지하는 항목이 최종 목록에서 높은 순위를 부여받도록 보장함
• 또한 일부 목록에서는 높은 순위지만 다른 목록에서는 낮은 순위인 항목이 최종 목록에서 높은 순위를 부여받지 않도록 보장함
• 순위를 분모에 넣어 점수를 계산하면 순위가 낮은 레코드에 불이익을 줄 수 있음
• 주목할 만한 사항
  • $rrf_k: 첫 번째 순위 항목의 점수가 극단적으로 높아지는 것을 방지하기 위해(순위로 나누기 때문에), 분모에 k 상수를 추가하여 점수를 평활화하는 경우가 많음
  • $ _weight: 각 방법에 가중치를 할당할 수 있음. 이는 결과를 조정할 때 매우 유용함

퍼지 검색 구현하기

• 이전까지의 방법으로도 많은 부분을 해결할 수 있지만, 명명된 엔티티에서 오타가 있을 경우 즉각적인 이슈가 발생할 수 있음
• 의미론적 검색은 유사성을 포착하여 이러한 이슈 중 일부를 제거하지만, 이름, 약어 및 의미론적으로 유사하지 않은 기타 텍스트에 대해서는 어려움을 겪음
• 이를 완화하기 위해 pg_trgm 확장을 도입하여 퍼지 검색을 허용
  • 트라이그램으로 작동함. 트라이그램은 단어를 3자 시퀀스로 분해하기 때문에 퍼지 검색에 유용
  • 이를 통해 오타나 약간의 변형이 포함되어 있더라도 유사한 단어를 매칭할 수 있음
  • 예를 들어 "hello"와 "helo"는 많은 트라이그램을 공유하므로 퍼지 검색에서 더 쉽게 매칭될 수 있음
• 원하는 열에 대해 새 인덱스를 생성하고, 그 후 전체 검색 쿼리에 추가
• pg_trgm 확장은 % 연산자를 노출하여 유사도가 pg_trgm.similarity_threshold(기본값은 0.3)보다 큰 텍스트를 필터링함
• 유용한 다른 여러 연산자도 있음.

전문 검색 튜닝하기

• tsvector 가중치 조정하기 :실제 문서에는 제목뿐만 아니라 내용도 포함됨
• 열이 여러 개 있어도 임베딩 열은 하나만 유지함
• 개인적으로 여러 임베딩을 유지하는 것보다 title과 body를 같은 임베딩에 유지하는 것이 성능에 큰 차이가 없다는 것을 발견함
• 결국 title은 본문의 간단한 표현이어야 함. 필요에 따라 이를 실험해 보는 것이 좋음
• title은 짧고 키워드가 풍부할 것으로 예상되는 반면, body는 더 길고 더 많은 세부 정보를 포함할 것임
• 따라서 전체 텍스트 검색 열이 서로 어떻게 가중치를 부여하는지 조정해야 함
• 문서에서 단어가 있는 위치나 중요도에 따라 우선순위를 부여할 수 있음
  • A-weight: 가장 중요(예: 제목, 헤더). 기본값 1.0
  • B-weight: 중요(예: 문서 시작 부분, 요약). 기본값 0.4
  • C-weight: 표준 중요도(예: 본문 텍스트). 기본값 0.2
  • D-weight: 가장 덜 중요(예: 각주, 주석). 기본값 0.1
• 문서 구조와 애플리케이션 요구사항에 따라 가중치를 조정하여 관련성을 미세 조정함
• 제목에 더 많은 가중치를 부여하는 이유
  • 제목은 일반적으로 문서의 주요 주제를 간결하게 표현하기 때문
  • 사용자는 검색할 때 먼저 제목을 훑어보는 경향이 있으므로 제목의 키워드 일치는 일반적으로 본문 텍스트의 일치보다 사용자의 의도와 더 관련이 있음

길이에 따른 조정

• ts_rank_cd 문서를 읽어보면 정규화 매개변수가 있다는 것을 알 수 있음.
  • 두 랭킹 함수 모두 문서의 길이가 순위에 어떤 영향을 미쳐야 하는지 여부를 지정하는 정수 normalization 옵션을 사용함. 정수 옵션은 여러 동작을 제어하므로 비트 마스크임: |를 사용하여 하나 이상의 동작을 지정할 수 있음(예: 2|4).
• 이러한 다양한 옵션을 사용하여 다음을 수행 가능
  • 문서 길이 편향 조정
  • 다양한 문서 집합에서 관련성 균형 조정
  • 일관된 표현을 위해 랭킹 결과 조정
• 제목에는 0(정규화 없음), 본문에는 1(로그 문서 길이)을 설정하면 좋은 결과를 얻을 수 있음
• 다시 말하지만, 사용 사례에 가장 적합한 옵션을 찾기 위해 다양한 옵션을 실험해 보는 것이 좋음

크로스 인코더를 사용한 재랭킹

• 많은 검색 시스템은 두 단계로 구성됨
• 즉, 양방향 인코더를 사용하여 초기 N개의 결과를 검색한 다음, 크로스 인코더를 사용하여 이러한 결과를 검색 쿼리와 비교하여 순위를 매김
  • 양방향 인코더(bi-encoder) : 빠르기 때문에 많은 수의 문서를 검색하는 데 좋음
  • 크로스 인코더(cross-encoder)
    • 더 느리지만 성능이 더 좋아 검색된 결과의 순위를 다시 매기는 데 좋음
    • 쿼리와 문서를 함께 처리하여 둘 사이의 관계에 대한 더 미묘한 이해를 가능하게 함
    • 이는 계산 시간과 확장성을 희생하면서 더 나은 랭킹 정확도를 제공함
• 이를 수행하기 위한 다양한 도구들이 있음
• 가장 좋은 것 중 하나는 Cohere의 Rerank임
• 또 다른 방법은 OpenAI의 GPT를 사용하여 자체적으로 구축하는 것임
• 크로스 인코더는 쿼리와 문서 간의 관계를 더 잘 이해할 수 있도록 하여 검색 결과의 정확도를 높일 수 있음
• 그러나 계산 비용이 크기 때문에 확장성 면에서는 제한이 있음
• 따라서 초기 검색에는 양방향 인코더를 사용하고, 검색된 소수의 문서에 대해서만 크로스 인코더를 적용하는 두 단계 접근 방식이 효과적임

언제 대안 솔루션을 찾아야 할까

• PostgreSQL은 많은 검색 시나리오에 적합한 선택이지만 한계가 없는 것은 아님
• BM25와 같은 고급 알고리듬의 부재는 다양한 문서 길이를 처리할 때 느껴질 수 있음
• PostgreSQL의 전체 텍스트 검색은 TF-IDF에 의존하므로 매우 긴 문서와 대규모 컬렉션의 희귀 용어에 어려움을 겪을 수 있음
• 대안 솔루션을 찾기 전에 반드시 측정해 보아야 함. 그럴 가치가 없을 수도 있음

결론

• 이 글에서는 기본적인 전체 텍스트 검색부터 퍼지 매칭, 의미론적 검색, 결과 부스팅과 같은 고급 기술까지 많은 내용을 다루었음
• Postgres의 강력한 기능을 활용하여 특정 요구사항에 맞춘 강력하고 유연한 검색 엔진을 만들 수 있음
• Postgres는 검색을 위해 가장 먼저 떠오르는 도구는 아니지만 정말 멀리 갈 수 있게 해줌
• 훌륭한 검색 경험을 위한 핵심
  • 지속적인 반복과 미세 조정
  • 논의한 디버깅 기법을 사용하여 검색 성능을 이해하고, 사용자 피드백과 행동을 기반으로 가중치와 매개변수를 조정하는 것을 두려워하지 말아야 함
• PostgreSQL은 고급 검색 기능이 부족할 수 있지만, 대부분의 경우 충분히 강력한 검색 엔진을 구축할 수 있음
• 대안 솔루션을 찾기 전에 먼저 Postgres의 기능을 최대한 활용하고 성능을 측정해 보는 것이 좋고, 그래도 부족하다면 그때 다른 솔루션을 고려해 볼 수 있음

https://news.hada.io/topic?id=16468&utm_source=weekly&utm_medium=email&utm_campaign=202436

MSSQL에서 합산(SUM) 또는 평균(AVG) 결과를 5로 나누고 결과를 소수점 한 자리까지 표현하는 방법을 알아보겠습니다. 이를 위해 CAST 또는 ROUND 함수를 사용하여 결과를 적절히 포맷할 수 있습니다.

1. SUM 후 5로 나누기
예를 들어, Sales 테이블에서 모든 제품의 Quantity 합을 구한 후 그 결과를 5로 나누고 소수점 한 자리까지 반올림하는 쿼리는 다음과 같습니다.

sql

SELECT 
    CAST(SUM(Quantity) / 5.0 AS DECIMAL(10, 1)) AS SumDividedBy5
FROM 
    Sales;
이 쿼리에서 SUM(Quantity) / 5.0는 Quantity의 합을 5로 나누고, CAST(... AS DECIMAL(10, 1))는 결과를 소수점 한 자리까지 포맷합니다. 5.0을 사용하는 것은 결과가 실수로 처리되도록 하기 위함입니다.

2. AVG 결과 소수점 한 자리까지
Sales 테이블에서 모든 제품의 Quantity 평균을 계산하고 소수점 한 자리까지 표시하는 쿼리는 다음과 같습니다.

sql

SELECT 
    CAST(AVG(Quantity) AS DECIMAL(10, 1)) AS AverageQuantity
FROM 
    Sales;
또는 ROUND 함수를 사용하여 같은 결과를 얻을 수 있습니다.

sql

SELECT 
    ROUND(AVG(Quantity), 1) AS AverageQuantity
FROM 
    Sales;
ROUND 함수는 첫 번째 인자로 받은 값을 두 번째 인자로 지정한 소수점 자리까지 반올림합니다.

위의 방법을 사용하여 SQL 쿼리 결과의 수치를 원하는 소수점 자리까지 조절할 수 있습니다. 이를 통해 보다 정확하고 의도한 형태의 데이터 분석 결과를 얻을 수 있습니다.

varchar와 nvarchar은 SQL Server에서 문자열 데이터를 저장하는 데 사용되는 두 가지 주요 데이터 형식입니다. 주된 차이점은 문자열의 저장 방식과 처리 방법에 있습니다.

1. 저장 방식:
   • varchar: ASCII 문자셋을 사용하여 문자열을 저장합니다. 한글이나 다국어 문자를 저장할 수 있지만, 해당 문자셋에 따라 저장 용량이 다를 수 있습니다.
   • nvarchar: 유니코드 문자셋(UTF-16)을 사용하여 문자열을 저장합니다. 모든 문자를 표현할 수 있으며, 다양한 언어 및 문자에 대한 지원이 내장되어 있습니다. 하지만 저장 용량이 더 크게 필요할 수 있습니다.
2. 저장 공간:
   • varchar: 저장 공간을 ASCII 문자셋에 맞게 할당합니다. 따라서 한글이나 다국어 문자의 경우에는 두 글자당 2바이트가 아닌 1바이트만 사용합니다.
   • nvarchar: 고정된 2바이트의 저장 공간을 사용합니다. 모든 문자가 2바이트로 저장되므로 저장 공간이 더 큽니다.
3. 검색 성능:
   • varchar: ASCII 문자셋을 사용하므로, 해당 문자셋으로 제한된 환경에서는 저장 공간 측면에서 더 효율적일 수 있습니다.
   • nvarchar: 유니코드 문자셋을 사용하므로 저장 공간이 더 크며, 이로 인해 저장 및 검색 성능이 더 느릴 수 있습니다.
4. 사용 사례:
   • ASCII 문자만 다루는 경우나 저장 공간이 중요한 경우에는 varchar를 고려할 수 있습니다.
   • 다국어 문자 또는 유니코드 문자를 다뤄야 하는 경우에는 nvarchar를 사용해야 합니다.

일반적으로, 다국어 문자를 다루거나 유니코드 문자셋이 필요한 경우에는 nvarchar를 사용하는 것이 권장됩니다. 하지만 저장 공간이나 특정한 문자셋의 경우에는 varchar를 고려할 수 있습니다.

[MSSQL] 테이블 정보 확인

테이블 컬럼 정보 확인

select * from information_schema.columns

where table_name = ''

테이블 제약조건 확인 

select

*

from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

where table_name = ''

테이블 인덱스 확인 확인 

SELECT * FROM sys.dm_db_index_physical_stats

[MSSQL] CHARINDEX 두개의 문자 에서 문자 찾기 

CHARINDEX(Transact-SQL)

이 함수는 두 번째 문자 식 내에서 하나의 문자 식을 찾고, 있는 경우 첫 번째 식의 시작 위치를 반환합니다.

DECLARE @document VARCHAR(64);  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bicycle', @document);  



-----------   
48