정렬 조건 없이 순번을 매겨보자

일반적으로 순번을 지정할 때 ROW_NUMBER(), RANK, DENSE_RANK 등을 이용한다.

[MSSQL] ROW_NUMBER, RANK, DENSE_RANK 순위함수

SELECT ROW_NUMBER() OVER(ORDER BY 컬럼명) FROM 테이블명
SELECT RANK() OVER(ORDER BY 컬럼명) FROM 테이블명
SELECT DENSE_RANK() OVER(ORDER BY 컬럼명) FROM 테이블명

이렇게 정렬할 기준 컬럼을 지정 후 순위를 매긴다.

하지만 SELECT 해서 나오는 결과 그대로 순위를 매기려고 한다.

SELECT ROW_NUMBER() OVER(ORDER BY 1)
FROM 테이블명

다음과 같이 ORDER BY 1로 하면 될 거 같은데...! 안된다..

그렇다면 어떻게 처리해야할까?

첫번째 방법. 의미없는 변수 사용

DECLARE @row INT = 1 -- 의미 없는 변수
 
SELECT ROW_NUMBER() OVER(ORDER BY @row)
FROM 테이블명

이렇게 의미없는 변수를 선언해주고 해당 변수를 ORDER BY 절에 넣어준다.

두번째 방법. SELECT 1 사용

SELECT ROW_NUMBER () OVER(ORDER BY (SELECT 1))
FROM 테이블명

NTILE(Transact-SQL)

정렬된 파티션의 행을 지정된 수의 그룹으로 분산시킵니다. 그룹에는 1부터 시작하는 번호가 매겨집니다. NTILE은 각 행에서 해당 행이 속한 그룹 번호를 반환합니다.

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/ntile-transact-sql?view=sql-server-ver16 

USE AdventureWorks2012;   
GO  
SELECT p.FirstName, p.LastName  
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile  
    ,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD  
    , a.PostalCode  
FROM Sales.SalesPerson AS s   
INNER JOIN Person.Person AS p   
    ON s.BusinessEntityID = p.BusinessEntityID  
INNER JOIN Person.Address AS a   
    ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0;  
GO

FirstName      LastName              Quartile  SalesYTD       PostalCode  
-------------  --------------------- --------- -------------- ----------  
Linda          Mitchell              1         4,251,368.55   98027  
Jae            Pak                   1         4,116,871.23   98055  
Michael        Blythe                1         3,763,178.18   98027  
Jillian        Carson                1         3,189,418.37   98027  
Ranjit         Varkey Chudukatil     2         3,121,616.32   98055  
José           Saraiva               2         2,604,540.72   98055  
Shu            Ito                   2         2,458,535.62   98055  
Tsvi           Reiter                2         2,315,185.61   98027  
Rachel         Valdez                3         1,827,066.71   98055  
Tete           Mensa-Annan           3         1,576,562.20   98055  
David          Campbell              3         1,573,012.94   98055  
Garrett        Vargas                4         1,453,719.47   98027  
Lynn           Tsoflias              4         1,421,810.92   98055  
Pamela         Ansman-Wolfe          4         1,352,577.13   98027  

(14 row(s) affected)

데이터 분석가 나도 시작할 수 있을까? (역량, 공부)

데이터 분석가가 하는 일은 정확히 무엇일까? 크게 3가지로 요약하면 다음과 같다.

1. 현황을 파악하는 일

2. 현황의 원인을 밝혀내는 일

3. 개선을 위한 시스템을 구축하는 일

예를 들어 이번에 고객 유입 개선을 위한 이벤트를 연다고 해보자. 이 고객 유입과 개선 정도를 파악하기 위해선 평가 지표를 기획하고 대시보드를 만들어야 한다. 이 과정에서 데이터 분석가는 참여 고객을 어떻게 정의할 것인지, 이벤트 화면을 최소 1번 본 사람? 게시물을 공유한 사람? 등을 결정한다.

데이터를 분석하여 논리적으로 설명 가능하도록 결론을 도출하는 것이다. 예를 들어 갑자기 CX문의가 늘었는데 이 원인이 무엇인지 파악하기 위해 다음과 같은 Action을 취한다고 한다.

한 달간 갑자기 CX 문의가 너무 늘어서, 콜센터가 마비가 될 지경입니다.

보험사기를 치는 것 같은 고객을 어떻게 자동으로 적발할 수 있을까?

인스타그램 데이터를 자연어 처리해서 최근 시장 동향을 자동으로 파악할 수는 없을까?

개선을 위한 시스템을 구축하는 일은 위와 같이 더 나아질 수 있는 점을 찾아 해결하는 것이다.

다 종합해보면!! 데이터 분석가는 구체적으로 이런 일을 한다.

• 문제 확인 & 가설 제작
• 쿼리 제작 (SQL)
• 모델링 (머신러닝, 통계 모델링, 비즈니스 모델링 등)
• 인사이트 정리/시각화 (ppt, notion colab 등)
• 협업/미팅

3. 데이터 분석가에게 필요한 역량은?

1) 논리적 사고

논리적인 사고는 문제가 주어지면 목표를 설정하고 해결방안과 그 이유를 생각해낼 수 있으며, 그에 따른 결론 또는 성과를 설명할 수 있는 것이다. 논리적 사고가 중요한 이유는 항상 “왜”가 중요하기 때문이다. 데이터 분석가가 도출한 결론에는 꼭 근거가 있어야 한다.

면접을 볼 때도 논리적인 사고는 큰 도움이 된다.

ex) 왜 ~~ 이러한 모델/스코어/기법 등등을 썼나요?

ex) 왜 우리 회사에 지원했나요

2) 꼼꼼함

데이터 분석가에게는 쿼리를 더 효율적으로 짜는 꼼꼼함과 복잡한 현실세계의 데이터를 잘 정리하는 능력이 필요하다.

3) 액션 위주의 사고

액션 위주의 사고라는 게 처음엔 잘 이해가 안 됐는데 이해하고 나니까 내가 평소에 하던 것은 액션 위주의 사고가 아니라는 것을 알았다. 예를 들어 ‘이번 주 가동률은 70%다’라고 인식하는 게 아니라 ‘전해 동기 대비 이번 주 가동률은 3% 낮은 70%’라고 보는 것이 액션을 낼 수 있는 분석이라는 거다.

4) 모델링

해당 모델의 문제가 무엇인지 파악하고, 이를 어떻게 하면 개선할 수 있을지 리소스/상황에 따라 필요에 맞게 생각하는 능력 이번 선배팅에서 계속 강조하셨던 부분은 크게 2가지이다.

1. 논리적, 구조적 사고하기

2. 자신의 주력 도메인을 갈고닦기!

프로그래밍 언어도 여러 개를 하는 것보다 어느 하나를 깊게 파는 것이 좋다고 하는 것처럼 데이터 분석가가 되고 싶다면 자신이 자신 있는, 잘하는 분야를 더 파는 게 중요하다고 한다.

데이터 분석가에도 다양한 유형이 있기 때문에 위의 데이터 분석가의 역량 중 하나만이라도 자신의 강점인 부분을 잘 살려서 준비하면 된다.

https://modulabs.co.kr/blog/data-analyst/

[MSSQL] STRING_AGG(Transact-SQL) 문자열 식의 값을 연결하고 그 사이에 구분 기호 값을 추가합니다. 구분 기호는 문자열 끝에 추가되지 않습니다.

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16 

[MSSQL]  STRING_SPLIT(Transact-SQL)

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16 

지정된 구분 기호 문자에 따라 문자열을 부분 문자열의 행으로 분할하는 테이블 반환 함수입니다.

SELECT ProductId, Name, Tags  
FROM Product  
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

MSSQL 링크드서버,  linked server

MSSQL 은 연결된서버 기능을 제공하는데 이를 이용하면 다른 네트워크의 데이터베이스를 원격으로 접속하여

   사용할 수 있도록 해줍니다. 

-- MSSQL 연결된 서버 생성

EXEC sp_addlinkedserver
      @server = '[연결된 서버별칭]',
      @srvproduct = '',
      @provider = 'SQLOLEDB',
      @datasrc = '[서버 아이피]',
      @catalog = '[데이터 베이스명]'



-- MSSQL 연결계정 생성

EXEC sp_addlinkedsrvlogin
      @rmtsrvname= '[연결된 서버별칭]',
      @useself= 'false',
      @rmtuser = '[사용자 이름]',
      @rmtpassword = '[사용자 암호]'
      
      
-- MSSQL 연결된 서버 확인
   SELECT * FROM master.dbo.sysservers WHERE srvname = '[연결된 서버별칭]'
   

-- MSSQL 연결계정 확인
   SELECT * FROM master.sys.linked_logins WHERE remote_name = '[사용자 이름]'
   
   
-- MSSQL 연결된 서버 이용방법 
   /*연결된 서버를 등록한 후 사용하려면 [연결된 서버별칭].[데이터 베이스명].[데이터베이스 소유자명].[테이블명]
   형태로 호출하여 사용할 수 있습니다.
   SELECT 쿼리를 예로 들면 아래와 같습니다. */

 -- MSSQL 일반서버에 SELECT 쿼리시
   SELECT [컬럼명] FROM [테이블명] WHERE [조건절]

-- MSSQL 연결된 서버에 SELECT 쿼리시
   SELECT [컬럼명] FROM [연결된 서버별칭].[데이터 베이스명].[데이터베이스 소유자명].[테이블명] WHERE [조건절]

-- MSSQL 연결된 서버 삭제
  EXEC sp_dropserver
      @server = '[연결된 서버별칭]'

-- MSSQL 연결계정 삭제
   EXEC sp_droplinkedsrvlogin
      @rmtsrvname= '[연결된 서버별칭]',
      @locallogin = NULL

SQL Server 모든 테이블 크기를 조회하는 쿼리

테이블의 건수와, 테이블에 구성된 인덱스들의 합도 같이 확인할 수 있습니다.

SELECT
    OBJECT_SCHEMA_NAME(a2.object_id) AS SchemaName,
    a2.name AS TableName,
    a1.rows as [RowCount],
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
    CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB
FROM
    (SELECT 
        ps.object_id,
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY ReservedSize_MB DESC

[MSSQL] DB 복구모델 - 전체(Full) 로 변경

복구 모델을 보려면

1. 데이터베이스 엔진에 연결합니다.
2. 표준 도구 모음에서 새 쿼리를 선택합니다.
3. 다음 예제를 복사하여 쿼리 창에 붙여넣고 실행을 선택합니다. 이 예에서는 모델 데이터베이스의 복구 모델을 배우기 위해 sys.databases 카탈로그 뷰를 쿼리하는 방법을 보여줍니다.

복구 모델을 변경하려면

1. 데이터베이스 엔진에 연결합니다.
2. 표준 도구 모음에서 새 쿼리를 선택합니다.
3. 다음 예제를 복사하여 쿼리 창에 붙여넣고 실행을 선택합니다. 이 예에서는 model ALTER DATABASE FULL 문의 SET RECOVERY 옵션을 사용하여 데이터베이스의 복구 모델을 로 변경하는 방법을 보여 줍니다.

-- 복구 모델을 보려면
SELECT name, recovery_model_desc  
   FROM sys.databases  
      WHERE name = 'model' ;  
 


-- 복구 모델을 변경하려면
USE [master] ;  
ALTER DATABASE [model] SET RECOVERY FULL ;

SSMS에서 하기. 

DB 속성에서 옵션 들어가서 복구모델 선택하기. 

단순(Simple) 데이터베이스 복구 모델을 선택하는 몇 가지 이유는 다음과 같습니다.

• 개발 및 테스트 데이터베이스에 가장 적합합니다.
• 데이터 손실이 허용되는 단순한 보고 또는 애플리케이션 데이터베이스
• 장애 시점 복구는 전체 및 개별 백업 전용입니다.
• 관리 오버헤드 없음

다음을 지원합니다.

1. 전체 백업
2. 차등 백업
3. 복사 전용 백업
4. 파일 백업
5. 부분 백업

장점 : 고성능 대량 복사 작업이 가능하고 로그 공간을 확보하여 공간 요청을 작게 유지합니다.

단점 : 가장 최근의 데이터베이스 또는 불일치 백업을 다시 빌드해야 하므로 변경됩니다.

Full 

전체 복구 모델을 사용하면 SQL Server는 사용자가 백업할 때까지 트랜잭션 로그를 예약합니다. 이 복구 모델에서는 모든 거래(DDL(데이터 정의 언어) + DML(데이터 조작 언어))가 트랜잭션 로그 파일에 완전히 기록됩니다. 로그 순서는 손상되지 않고 데이터베이스가 작업을 복원할 수 있도록 보존됩니다. 단순 복구 모델과 달리 트랜잭션 로그 파일은 CHECKPOINT 작업 중에 자동으로 잘리지 않습니다.

데이터베이스 오류가 발생했을 때 전체 복구 모델을 사용하여 데이터베이스를 복원하는 가장 유연성을 얻을 수 있습니다. 지정 시간 복원, 페이지 복원 및 파일 복원을 포함한 모든 복원 작업이 지원됩니다.

전체 데이터베이스 복구 모델을 선택하는 이유:

• 미션 크리티컬 애플리케이션 지원
• 고가용성 키 설계
• 0 또는 명목 데이터 손실로 모든 데이터 복구를 용이하게 하기 위해
• 데이터베이스가 여러 파일 그룹을 갖도록 설계되었으며 읽기/쓰기 보조 파일 그룹 및 선택적으로 읽기 전용 파일 그룹의 부분적 복원을 수행하려는 경우
• 임의 시점 복원 허용
• 개별 시트 복원
• 높은 관리 오버헤드 유지

다음을 지원합니다.

1. 전체 백업
2. 차등 백업
3. 트랜잭션 로그 백업
4. 복사 전용 백업
5. 파일 및/또는 파일 그룹 백업
6. 부분 백업

장점 : 데이터 파일의 유실 또는 손상으로 인한 작업 오작동이 없습니다. 임의의 시점으로 회복될 수 있습니다.

단점 : 로그가 손상되면 가장 최근의 로그 백업 이후의 변경 사항을 다시 작성해야 합니다.

데이터베이스가 개발 또는 테스트 서버인 경우 단순 복구 모델이 대부분 적합해야 합니다. 그러나 데이터베이스가 프로덕션 데이터베이스인 경우 일반적으로 전체 복구 모델을 사용하는 것이 좋습니다. 전체 복구 모델은 대량 로그 복구 모델로 보완할 수 있습니다. 물론 데이터베이스가 작거나 데이터 웨어하우스의 일부이거나 데이터베이스가 읽기 전용인 경우에도 마찬가지입니다.