긍정적 사고, 음식의 절제, 규칙적인 운동

[MSSQL] Sub Query 서브쿼리에서 정렬하기

SELECT 에서 중첩 서브쿼리 또는 스칼라 서브 쿼리에서는 order by 를 사용하지 못한다. 

하지만, TOP-N  ~ ORDER BY 는 사용가능하다. 

SELECT *
  FROM (SELECT TOP 10 
               USER_ID
             , USER_NAME
          FROM USERS
         ORDER BY UP_DATE DESC 
       ) A

그냥 order by 는 사용할 수 없다고 나오지만, TOP-N을 사용하면 order by  가 가능하다. 

그래서, 전체를 호출하고 싶다면 전체 개수 만큼 TOP-N 을 지정해주면 된다. 

SSMS(SQL Server Management Studio) 다운로드 : https://docs.microsoft.com/ko-kr/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16 

SSMS 다운로드

SSMS 19 미리 보기 2를 다운로드하려면 SSMS 19 다운로드를 방문하세요.

SSMS(SQL Server Management Studio ) 18.12.1 무료 다운로드

SSMS 18.12.1은 최신 GA(일반 공급) 버전입니다. 이전 GA 버전의 SSMS 18이 설치된 경우 SSMS 18.12.1을 설치하면 이 버전이 18.12.1로 업그레이드됩니다.

• 릴리스 번호: 18.12.1
• 빌드 번호: 15.0.18424.0
• 릴리스 날짜: 2022년 6월 21일

https://www.itworld.co.kr/news/242939?page=0,1 

"이러려고 데이터 과학자 됐나" 데이터 관리의 11가지 어두운 비밀

누군가는 데이터를 새로운 석유라 부르고, 누군가는 새로운 금이라고도 부른다. 철학자와 경제학자들은 비유의 적절성 대해 논쟁할 수 있겠지만, 데이터 기반 의사 결정을 도모하는 기업에 데이터 구성 및 분석이 필수적이라는 점은 의심의 여지가 없다.

일단은 견고한 데이터 관리 전략이 핵심이다. 데이터 거버넌스, 데이터 운영, 데이터 웨어하우징, 데이터 엔지니어링, 데이터 분석, 데이터 과학 등을 포괄하는 데이터 관리는 올바르게 수행될 경우 각종 비즈니스에서 경쟁 우위를 가져다줄 수 있다.

좋은 소식은 데이터 관리의 많은 측면이 잘 정립돼 있으며, 수십 년 동안 발전해 온 원칙이 존재한다는 점이다. 예를 들어, 적용하기 어렵거나 이해하기에 간단하지 않을 수 있지만, 많은 과학자와 수학자 덕분에 기업은 이제 데이터를 분석하고 결론을 내리기 위한 다양한 프레임워크를 갖게 됐다. 분석 한계를 나타내는 오차 막대를 그리는 통계 모델도 있다.

그러나 데이터 과학과 이를 뒷받침하는 다양한 학문에 대한 연구에서 얻은 모든 장점에도 불구하고 우리는 머리를 긁적거릴 때가 있다. 현장에서 벽에 부딪히는 경우가 많기 때문이다. 때로는 너무 많은 데이터를 수집하고 구성하는 역설적인 문제도 있다. 일부는 철학적이며 우리의 추상적 역량을 시험한다. 그리고 처음 데이터를 수집하는 데서는 개인 정보 보호 문제가 대두되고 있다.

다음은 수많은 기업에서 데이터 관리를 어려운 과제로 만드는 몇 가지 어두운 비밀들이다.

애물단지 비정형 데이터
기업 아카이브에 저장되어 있는 데이터의 대부분은 구조화되어 있지 않다. 은행의 콜센터 직원이 작성한 문자 메모를 검색하기 위한 인공 지능(AI) 사용을 원하는 경우가 있다. 이 문장에는 은행의 대출 및 서비스를 개선하는 데 도움이 될 수 있는 통찰이 담겨 있을 수 있다. 그러나 메모 데이터는 기록할 내용에 관해 서로 다른 생각을 가진 수백 명의 사람들이 작성한 것이다. 또한, 직원들은 서로 다른 작문 스타일과 능력을 가지고 있고, 일부는 전혀 쓰지 않았다. 또 어떤 사람들은 주어진 전화에 대해 너무 많은 정보를 기록한다. 수십 년 동안 수백 명의 직원이 작성한 텍스트 더미가 있다면, 구조화 수준이 훨씬 더 약해질 수 있다.

정형 데이터라도 비정형인 경우
좋은 데이터 과학자와 데이터베이스 관리자는 각 분야의 유형과 구조를 명료하게 지정해 데이터베이스를 마련한다. 때로는 필드의 값을 특정 범위의 정수 또는 미리 정의된 선택으로 제한한다. 하지만 사람들은 데이터베이스가 주름과 결함을 추가하는 방법을 생각해낸다.

필드가 비어 있는 경우도 있다. 질문이 적용되지 않는다고 생각할 때 ‘n.a.’를 넣기도 하지만, 그저 대시 기호를 넣는 이들도 있다. 사람들은 심지어 이름을 해마다, 날마다 다르게 표시하기도 한다. 

우수한 개발자는 유효성 검사를 통해 이런 문제 중 일부를 파악한다. 훌륭한 데이터 과학자는 정리를 통해 이런 불확실성을 어느 정도 줄일 수도 있다. 그러나 탁월하게 구조화된 표에도 의심스러운 항목이 있고, 이런 의심스러운 항목에 알 수 없는 항목과 분석 오류가 발생할 수 있다. 좌절감을 느끼게 하는 현실이다.

너무 엄격하거나 느슨한 데이터 스키마
데이터팀이 스키마 제약 조건을 아무리 자세히 설명하려 해도 다양한 데이터 필드의 값을 정의하기 위한 스키마는 완벽하기 어렵다. 너무 엄격하거나 너무 느슨하다. 데이터팀에서 엄격한 제약 조건을 추가하면 사용자는 허용 가능한 값의 좁은 목록에서 답을 찾을 수 없다고 불평한다. 스키마가 너무 수용적이면 사용자는 일관성이 거의 없이 이상한 값을 추가할 수 있다. 스키마를 올바르게 조정하는 것은 거의 불가능할 지경이다.

매우 엄격한 데이터 법률
개인정보 보호 및 데이터 보호에 관한 법률은 이미 강력하며, 점점 더 강력해지고 있다. GDPR, HIPPA 및 각종 규제 사이에서 데이터를 수집하는 것은 매우 어려울 수 있으며, 해커의 침입에 대응하기란 훨씬 더 위험할 수 있다. 많은 경우에 프로그래머나 데이터 과학자보다 변호사에게 비용을 지출하는 것이 더 쉬울 수 있다. 이런 골칫거리로 인해 일부 기업은 데이터를 제거할 수 있는 즉시 데이터를 폐기한다.

엄청난 데이터 정리 비용
많은 데이터 과학자가 자신의 작업 중 90%가 데이터를 수집하고, 일관된 형식으로 만들고, 끝없는 구멍이나 실수를 처리하는 것이라고 본다. 데이터를 가지고 있는 사람은 항상 “CSV에 모든 것이 있다”라고 말할 것이다. 그러나 그들은 빈 필드나 잘못된 특성을 언급하지 않는다. 실제로 통계 분석을 수행하기 위해 R 또는 파이썬에서 루틴을 시작하는 것보다 데이터 과학 프로젝트에서 사용할 데이터를 정리하는 데 10배나 더 많은 시간을 소비하기 쉽다.

사용자의 의심 증가
최종 사용자와 고객이 기업의 데이터 관리 관행에 대해 점점 더 의심을 품고 있다. 일부 AI 알고리즘에 대한 불안감과 두려움이 증폭되고 있다. 일거수일투족을 감시하는 듯한 반응을 보이기도 한다. 이런 두려움은 규제를 조장하고 종종 기업과 선의의 데이터 과학자를 괴롭힌다. 사용자가 의도적으로 가짜 값이나 오답으로 데이터 수집을 방해하는 경향을 보이는 것이다. 때로는 작업의 절반이 악의적인 파트너 및 고객을 상대하는 업무가 될 수 있다.

외부 데이터와의 통합 위험성
공격적인 기업은 자사의 정보를 서드파티 데이터 및 인터넷에 떠도는 방대한 개인화된 정보와 통합하는 방법을 모색하고 있다. 실제로 일부 도구는 모든 고객에 대한 데이터를 흡수해 각 구매에 대해 개인화된 정보를 작성하겠다고 공개적으로 공언한다. 그렇다, 이들은 사용자의 패스트푸드 구매와 신용 점수를 추적하는 데 첩보기관이 테러리스트를 추적하는 데 사용하는 기술을 사용한다. 사람들이 불안해하는 것이 사실 당연하다.

데이터 사용을 단속하는 규제 당국
데이터 분석이 언제 어떤 선을 넘을지 알기 어렵지만, 일단 선을 넘으면 규제 당국이 나타난다. 캐나다의 최근 사례에서, 정부는 일부 도넛 가게가 어떻게 경쟁업체에서 쇼핑하는 고객들을 추적하고 있는지 조사했다. 

정부는 보도 자료를 통해 “조사 결과, 팀 호튼스가 미국의 서드파티 위치 서비스 업체와 맺은 계약에 너무 모호하고 허용적인 표현이 포함되어 있었다. 이로 인해 회사가 ‘비식별화된’ 위치 데이터를 판매할 소지를 남겼을 것”이라고 밝혔다. 도대체 무엇을 위해? 더 많은 도넛을 팔기 위해서? 규제기관은 개인정보와 관련된 모든 것에 점점 더 주의를 기울이고 있다.

도출한 결과가 무가치함
우리는 뛰어난 알고리즘이 모든 것을 더 효율적이고 수익성 있게 만들 수 있다고 상상한다. 그리고 때로는 그런 알고리즘을 실제로 구현할 수 있다. 그러나 비용이 문제이다.

실제로 소비자는 물론 심지어 기업까지도 정교한 데이터 관리 체계에서 나오는 표적 마케팅의 가치에 점점 더 의문을 제기하고 있다. 이미 구매한 아이템에 대한 광고가 노출되는 것을 자주 경험했을 것이다. 엄격한 데이터 분석을 통해 실적이 낮은 공장을 식별했더니, 회사가 건물에 30년 임대 계약을 체결했을 수도 있다. 기업은 데이터 과학이 비현실적인 답을 산출할 가능성에 대비해야 한다.

결국 인간의 직관에 따라 결정
숫자는 높은 정확도를 제공하지만, 사람이 숫자를 어떻게 해석하는지가 더 중요한 경우도 많다. 모든 데이터 분석과 AI 마법이 끝난 후에 대부분 알고리즘은 일부 값이 임계 값을 초과하는지 또는 미만 인지에 대한 결정을 내려야 한다. 

가령 경찰은 제한 속도의 20%를 초과하는 차량에 속도위반 고지서를 주려고 한다. 이런 임계값은 종종 임의의 값이다. 데이터에 적용할 수 있는 모든 과학 및 수학의 경우, ‘데이터 기반’ 프로세스에는 생각보다 더 많은 회색 지대가 있다. 기업이 데이터 관리 프랙티스에 투입한 방대한 자원에도 불구하고 결국 의사 결정에 영향을 미치는 최대 요인은 인간의 직관인 경우가 많다.

폭발적인 데이터 스토리지 비용
테라바이트당 가격은 계속 떨어지고 있지만 프로그래머는 더 빠르게 데이터 비트를 모으고 있다. 사물 인터넷 디바이스는 계속해서 데이터를 업로드하고, 사용자는 이런 바이트의 풍부한 컬렉션을 영원히 탐색할 것으로 기대한다.  동시에 컴플라이언스 담당자와 규제기관은 감사를 위해 계속해서 더 많은 데이터를 요구한다. 실제로 다시 액세스되는 데이터의 비율은 계속 낮아지고 있다. 그저 데이터가 하염없이 쌓여만 가는 것이다. 

-- with(nolock)

MSSQL 은 기본적으로 SELECT 시 공유잠금(S Lock) 이 걸린다.
즉, DML(INSERT, UPDATE, DELETE) 작업중인 ROW 또는 TABLE 에 SELECT 할 경우,
해당 작업이 끝나야 SELECT 할 수 있다는 의미이다.  
이를 방지하기 위해 WITH(NOLOCK) 을 사용할 수 있다.
WITH(NOLOCK) 의 격리수준(Isolation Level)은 Read Uncommitted 와 같다고 보면 된다.
즉, SELECT 할 ROW 또는 TABLE 이 잠겨있어도(작업중이여도) 기다리지 않고 조회 (Dirty Read) 하겠다라는 의미이다.
이를 통해 조회 성능이 올라가고, 데드락(Deadlock) 을 방지할 수 있다.
 

MS-SQL에서 내부적으로 지원하는 문서화되지 않은 문자열 함수인 PWDENCRYPT와 PWDCOMPARE를 통해 암호화 기능을 구현할수 있다.

<사용예제>

' 테이블을 생성한다, 암호화할 컬럼은 VARBINARY 타입으로 한다. 

CREATE TABLE MEMBER
(
  UserId VARCHAR(25),
  UserPwd VARBINARY(100)
)

' 데이터를 입력한다. 암호화할 컬럼은 PWDENCRYPT메소드를 이용해서 넣는다.

INSERT INTO MEMBER (UserId, UserPwd)

       VALUES ('smith', PWDENCRYPT('1234')) 

' 해당아이디를 가진 회원의 암호화된 비밀번호 컬럼과 입력한 문자열 비밀번호를

' PWDCOMPARE(입력문자열, 컬럼명) 메소드를 이용해 비교한다.

' 둘이 같으면 1 (true), 틀리면 0 (false)를 리턴하게 된다.

' 그리고 원문이나 암호문 둘 중 하나가 NULL 이면 NULL을 리턴하게 된다.

SELECT PWDCOMPARE('1234', UserPwd)

       FROM MEMBER WHERE UserId='smith'  ' 결과 : 1

결과를 살펴보면 대소문자는 구분하지 않음을 알 수 있다. 암호문의 경우 위에서 살펴본대로 대소문자를 분명 다르게 비교하였으나, PWDCOMPARE 함수에서는 대소문자를 무시하였다.

또한, 실행 결과를 보면 형태는 원문의 길이에 상관없이 30~35자 사이의 거의 일정한 길이의 암호문을 출력하는 것을 볼 수 있다.  테스트 결과 원문의 길이가 varchar 기준으로 128자를 넘었을 경우 에러를 발생 하였다.

구문

JSON_MODIFY ( expression , path , newValue )  

기본 쿼리

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')

PRINT @info

-- Insert surname  

SET @info=JSON_MODIFY(@info,'$.surname','Smith')

PRINT @info

-- Set name NULL 

SET @info=JSON_MODIFY(@info,'strict $.name',NULL)

PRINT @info

-- Delete name  

SET @info=JSON_MODIFY(@info,'$.name',NULL)

PRINT @info

-- Add skill  

SET @info=JSON_MODIFY(@info,'append $.skills','Azure')

PRINT @info

결과

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-ver15 

PIVOT SUM FOR IN PIVOT_TABLE

PIVOT을 이용하여 세로를 가로로 변환 방법(행 열 변환) 

SELECT Student  
, isnull(CAST([Mathematics] AS varchar), '' ) [Mathematics]
, isnull(CAST([Science] AS varchar), '' ) [Science]
, isnull(CAST([Geography] AS varchar), '' ) [Geography]
-- , * 
FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM #TEMP_TABLE_Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Mathematics],
    [Science],
    [Geography]
  )
) AS PivotTable;

IF OBJECT_ID('tempdb..#TEMP_TABLE_Grades') --임시 테이블 남아있을시 삭제 
  IS NOT NULL DROP TABLE #TEMP_TABLE_Grades;
 

CREATE TABLE #TEMP_TABLE_Grades(
  [Student] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Marks]   INT
);
 
INSERT INTO #TEMP_TABLE_Grades VALUES 
('Jacob','Mathematics',100),
('Jacob','Science',95),
('Jacob','Geography',90),
('Jacob2','Mathematics',''),
('Jacob2','Science',''),
('Amilee','Mathematics',90),
('Amilee','Science',90),
('Amilee','Geography',100);
 
SELECT * FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM #TEMP_TABLE_Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Mathematics],
    [Science],
    [Geography]
  )
) AS PivotTable

데이터 리터러시( Data literacy )는 데이터 를 정보 로 읽고, 이해하고, 생성하고, 전달할 수 있는 능력 입니다. 일반 개념으로서의 문해력 과 마찬가지로 데이터 문해력은 데이터 작업과 관련된 역량 에 중점을 둡니다. 그러나 데이터를 읽고 이해하는 것과 관련된 특정 기술이 필요하기 때문에 텍스트를 읽는 능력과 유사하지 않습니다. 

리터러시(literacy)는 글을 읽고 해독하는 능력을 의미한다. 데이터 리터러시는 데이터를 목적에 맞게 활용하는 데이터 해석 능력을 말한다. 여기서의 데이터는 고도의 처리 기술이 필요한 빅데이터에서 단순 수치 등도 포함한다. 수많은 데이터가 쏟아지는 상황에서 데이터에 담겨있는 의미를 파악하여 의미를 파악해 내는 능력은 데이터 활용 과정 전반에 필요한 역량이다. 빅데이터 시대에 데이터 리터러시는 개인에게도 필요한 능력이며, 데이터 수집 역량, 관리 역량, 가공 및 분석 역량, 시각화 역량, 기획 역량 등이 데이터를 활용하는 능력이다.

데이터 수집 및 데이터 공유 가 일상화되고 데이터 분석 및 빅 데이터 가 뉴스, 비즈니스, [2] 정부 [3] 및 사회 에서 일반적인 아이디어가 됨에 따라 [4] 학생, 시민 및 독자에게 점점 더 중요해지고 있습니다. 데이터 활용 능력이 있습니다. 이 개념은 일반적으로 자동화된 수단을 통한 데이터 분석과 결과의 해석 및 적용과 관련된 데이터 과학과 관련이 있습니다. [5]

데이터 리터러시(Data literacy)는 그래프와 차트를 읽고 데이터에서 결론을 도출하는 능력을 포함하여 데이터의 의미를 이해하는 것을 포함하기 때문에 통계 리터러시 와 구별됩니다 . [6] 한편, 통계적 소양은 그래프, 표, 진술, 설문조사, 연구와 같은 "일상적인 매체에서 요약 통계를 읽고 해석하는 능력"을 의미한다. [6]

정보를 찾고 사용하기 위한 가이드로서 사서 는 학생과 연구원을 위한 데이터 활용 능력에 대한 워크샵을 이끌고 자신의 데이터 활용 능력을 개발하기 위해 노력합니다. [7]

기관 및 학문 분야에 걸쳐 도서관 교육 프로그램에서 적용 가능한 공통 참조 프레임워크로 사용할 수 있는 일련의 핵심 역량 및 내용이 제안되었습니다. [8]

사서가 만든 리소스에는 MIT 의 데이터 관리 및 출판 자습서, EDINA 연구 데이터 관리 교육(MANTRA), 에딘버러 대학의 데이터 라이브러리 및 미네소타 대학 도서관의 구조 엔지니어를 위한 데이터 관리 과정이 있습니다.