온도/습도 등의 기상 데이터를 LabVIEW를 통해 DB에 저장하고 싶다는 문의에 대한 답변 입니다. 

Q : 온도/습도 등의 몇개의 기상데이터가 10초마다 몇달을 txt파일로 저장하는데,
     DB를 쓰면 특정 구간의 평균/최대/최소 등의 통계 혹은 rawdata 값을 쉽게 뽑아 낼 수 있는지요?
A : 날짜 컬럼으로 Index(목ㅊ, 색인) 가 걸려 있다면 특정 날짜 구간의 데이터를 빠르게 추출 및 통계(MIN, AVG, MAX 등)도 빠르게 계산이 가능 합니다. 

 

어떤 DB를 사용해야 좋을 지 문의하신다면 결론부터 말해서 MSSQ(Express) 버전으로 시작하시길 권해드립니다.

Express 버전의 경우 개인사용자는 무료이고 MSSQL 자체가 GUI 환경이어 타 DB에 비해 설치 및 운용이 간편하여

접근성이 좋습니다. 물론 성능도 타 DB에 비해 절~대 뒤떨어 지지 않습니다.

MSSQL 단점 은

- Windos 계열의 OS 에서 (만?) 설치 및 운용이 가장 편리합니다. 

물론 리눅스에도 설치가 가능하다는데, 직접 설치해 본 적이 없어서, 설치 난이도가 어떤지 알지 못합니다.

https://docs.microsoft.com/ko-kr/sql/linux/sql-server-linux-setup?view=sql-server-ver15

 

SQL Server on Linux 설치 지침 - SQL Server

SQL Server on Linux를 설치, 업데이트 및 제거합니다. 이 문서에서는 온라인, 오프라인 및 무인 시나리오를 설명합니다.

docs.microsoft.com

 

------------------------------------------------------ LabVIEW ------------------------------------------------------

LabVIEW : https://www.ni.com/ko-kr.html

 

자동화 테스트 및 자동화 측정 시스템 - National Instruments

업계 최고의 측정 정확성을 제공하면서 비용, 설계 및 디바이스 관련 문제를 해결할 수 있도록 지원하는 NI 반도체 테스트 솔루션을 만나보십시오.

www.ni.com

LabVIEW 와 DB를 연동하려면 애드온을 구매해야 한다는군요.

LabVIEW DB연동 모듈
LabVIEW : ODBC 드라이버를 통한 DB 연동 가능

 

------------------ 아래 1번 부터 4번 까지 진행하시면 테스트 환경 구축은 가능하시리라 생각 됩니다.  ---------------

1. MSSQL Express 다운로드 및 설치방법 블로그 입니다. (퍼왔습니다.)

https://eine.tistory.com/entry/Microsoft-SQL-Server-2019-Express-%EC%84%A4%EC%B9%98

 

Microsoft SQL Server 2019 Express 설치 + SSMS

MS-SQL 설치 MS-SQL을 써야 할 일이 생겨서 한번 설치해보자. 테스트용으로만 필요하기 때문에 무료 테스트용 버전인 Express를 설치해보도록 하겠다. https://www.microsoft.com/ko-kr/sql-server/sql-server-dow..

eine.tistory.com

 

 

2. MSSQL DB 관리툴 입니다.

SSMS(SQL Server Management Studio)

https://docs.microsoft.com/ko-kr/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

 

SSMS(SQL Server Management Studio) 다운로드 - SQL Server Management Studio (SSMS)

최신 버전의 SSMS(SQL Server Management Studio)를 다운로드합니다.

docs.microsoft.com

 

 

3. 설치 완료 후 DB 생성 및 DB 사용 계정 생성 방법 입니다. (퍼왔습니다.)

https://server-talk.tistory.com/248

 

MSSQL 데이터베이스(DB) 생성과 새 로그인 사용자 추가

MSSQL 데이터베이스(DB) 생성과 새 로그인 사용자 추가 알아보기 이번에는 MSSQL에서 데이터베이스(DB) 생성과 계정생성을 알아보도록 하겠습니다 이전 글 : Windows SQL Server 2014 Express 설치 - http://serv..

server-talk.tistory.com

 

4. LabVIEW - MSSQL 연동 방법입니다. (퍼왔습니다.)

https://guslabview.tistory.com/66

 

[LabVIEW] MSSQL UDL 파일 생성 및 랩뷰와 연동 하기

[LabVIEW] MSSQL UDL 파일 생성 및 랩뷰와 연동 하기 이 글에서는 LabVIEW와 MSSQL을 연동하는 방식에 대해서 설명을 드리겠습니다. 랩뷰에서 손쉽게 DB에 접속하기 위해서는 UDL 파일이 필요합니다. UDL 파일은 U..

guslabview.tistory.com

 

기타 문의 사항은 게시판에 남겨 주세요.

저작자표시 비영리 변경금지

'Database & Data' 카테고리의 다른 글

상황에 맞는 그래프 선  (0) 2023.01.02
JOIN 종류 도식화  (0) 2018.08.09
일련 번호 중 빠진 번호 조회  (0) 2017.09.18
세미나 동영상 [최적 데이터 구조 설계]-다운 가능  (0) 2015.09.20
ER Studio Relationship 관계선 종류  (0) 2015.08.14

Oracle의 NEXT_DAY 함수는 입력한 날짜 이후의 찾고자 하는 요일의 첫 번째 일자를 반한 합니다.

NEXT_DAY(날짜, 찾고자 하는 요일)

- 찾고자 하는 요일 : 1=일, 2=월, 3=화, 4=수, 5=목, 6=금, 7=토 

MSSQL 에는 대응되는 함수가 없기때문에 사용자 함수를 만들어 사용하셔야 합니다.

CREATE FUNCTION [dbo].[NEXT_DAY] (@p_date DATETIME, @p_WEEKNO INT) 
RETURNS DATETIME 
AS 
BEGIN 
DECLARE @next_day DATETIME; 

SELECT @next_day = DATEADD(DAY,n, @p_date) 
FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) AS t(n) 
WHERE DATEPART(WEEKDAY, DATEADD(DAY,n, @p_date)) = @p_WEEKNO 
OR CONVERT(varchar(10), DATEPART(WEEKDAY, DATEADD(DAY,n, @p_date))) = @p_WEEKNO 

RETURN @next_day 
END 

GO

Oracle 의 NEXT_DAY 와 비교한 결과는

지정일 검색
요일		 Oracle MSSQL
2020-02-16 23:56:17.077 1 2020-02-23 23:05:26 2020-02-23 23:56:17.077
2020-02-16 23:56:17.077 2 2020-02-17 23:05:26 2020-02-17 23:56:17.077
2020-02-16 23:56:17.077 3 2020-02-18 23:05:26 2020-02-18 23:56:17.077
2020-02-16 23:56:17.077 4 2020-02-19 23:05:26 2020-02-19 23:56:17.077
2020-02-16 23:56:17.077 5 2020-02-20 23:05:26 2020-02-20 23:56:17.077
2020-02-16 23:56:17.077 6 2020-02-21 23:05:26 2020-02-21 23:56:17.077
2020-02-16 23:56:17.077 7 2020-02-22 23:05:26 2020-02-22 23:56:17.077
2020-02-16 23:56:17.077 1 2020-03-01 23:05:26 2020-03-01 23:56:17.077
2020-02-16 23:56:17.077 2 2020-03-16 23:05:26 2020-03-16 23:56:17.077
2020-03-20 23:56:17.077 3 2020-03-24 23:05:26 2020-03-24 23:56:17.077
2020-03-31 23:56:17.077 4 2020-04-01 23:05:26 2020-04-01 23:56:17.077
2020-04-11 23:56:17.077 5 2020-04-16 23:05:26 2020-04-16 23:56:17.077
2020-04-22 23:56:17.077 6 2020-04-24 23:05:26 2020-04-24 23:56:17.077
2020-05-03 23:56:17.077 7 2020-05-09 23:05:26 2020-05-09 23:56:17.077

 

궁금증은 덧글로 편하게 남겨주세요.

저작자표시 비영리 변경금지

'Database & Data > MSSQL' 카테고리의 다른 글

[MSSQL] 트리구조 탐색 쿼리 - 모든 부모 node 탐색  (0) 2019.10.24
SP_LOCK 출력 결과의 정렬 순서 개선 (Mode DESC)  (0) 2019.01.01
MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05

MySQL 로 작성한 트리구조 탐색 쿼리를 MSSQL로 바꿔 보았습니다.

https://devse.tistory.com/28<< MySQL 쿼리

✔ 트리 구조

✔ 쿼리

1
2
3
4
5
6
7
8
9
10
11
12
13
declare @v_id varchar(45), @v_lev int
 
set @v_id = 'a20'; -- 시작 node
 
with tree as (
    select id, pid, 0 lev
    from t_test
    where id=@v_id
    union all
    select ta.id, ta.pid, (tb.lev + 1) lev
    from t_test ta inner join tree tb on ta.id = tb.pid
)
select id, pid, lev from tree order by lev desc

✔ 결과

✔ 테스트용 자료 입력

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
USE [TEST_DB]
GO
 
CREATE TABLE [dbo].[t_test](
    [id] [varchar](45) NOT NULL,
    [pid] [varchar](45) NOT NULL,
    [data] [varchar](100) NOT NULL,
 CONSTRAINT [PK_t_test] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a1', N'a0', N'그림1')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a10', N'a9', N'그림10')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a11', N'a7', N'그림11')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a12', N'a8', N'그림12')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a13', N'a10', N'그림13')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a14', N'a10', N'그림14')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a15', N'a10', N'그림15')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a16', N'a13', N'그림16')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a17', N'a13', N'그림17')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a18', N'a14', N'그림18')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a19', N'a15', N'그림19')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a2', N'a1', N'그림2')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a20', N'a17', N'그림20')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a21', N'a4', N'그림21')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a22', N'a21', N'그림22')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a23', N'a5', N'그림23')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a24', N'a11', N'그림24')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a25', N'a11', N'그림25')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a26', N'a12', N'그림26')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a27', N'a9', N'그림27')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a28', N'a25', N'그림28')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a29', N'a22', N'그림29')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a3', N'a1', N'그림3')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a30', N'a2', N'그림30')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a4', N'a2', N'그림4')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a5', N'a4', N'그림5')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a6', N'a4', N'그림6')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a7', N'a6', N'그림7')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a8', N'a3', N'그림8')
INSERT [dbo].[t_test] ([id], [pid], [data]) VALUES (N'a9', N'a3', N'그림9')
GO
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5text-decoration:none">Colored by Color Scripter

✔ 실행 계획

저작자표시 비영리 변경금지

'Database & Data > MSSQL' 카테고리의 다른 글

Oracle NEXT_DAY 함수를 MSSQL NEXT_DAY 로 만들기  (0) 2020.02.17
SP_LOCK 출력 결과의 정렬 순서 개선 (Mode DESC)  (0) 2019.01.01
MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05

MSSQL 에서 LOCK 을 모니터링 하기 위하여 SP_LOCK 을 사용하게 되는데,

기존 정렬이 SPID  ASC 여서 접속한 Process 가 많을 경우 스크롤을 해야하는 불편함이 존재 합니다. 


기본 SP_LOCK 의 쿼리에서 정렬만 추가하여 베타적 잠금이 위에 오도록 수정 하였습니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select     convert (smallint, req_spid) As spid,
    rsc_dbid As dbid,
    rsc_objid As ObjId,
    rsc_indid As IndId,
    substring (v.name, 1, 4) As Type,
    substring (rsc_text, 1, 32) as Resource,
    substring (u.name, 1, 8) As Mode,
    substring (x.name, 1, 5) As Status
    , xname = (case when u.name like '%x%' then 1 else 0 end)
from     master.dbo.syslockinfo,
    master.dbo.spt_values v,
    master.dbo.spt_values x,
    master.dbo.spt_values u
 
where   master.dbo.syslockinfo.rsc_type = v.number
        and v.type = 'LR'
        and master.dbo.syslockinfo.req_status = x.number
        and x.type = 'LS'
        and master.dbo.syslockinfo.req_mode + 1 = u.number
        and u.type = 'L'
        --and u.name like '%x%'
order by xname desc, spid asc
 
cs

[실행 결과] - 베타적 잠금이 조회 결과 상단에 오게 됩니다.

조회가 완료 되셨으면 dbcc inputbuffer(SPID) 쿼리를 실행하여 잠금이 발생한 쿼리를 확인 하시면 됩니다.



저작자표시 비영리 변경금지

'Database & Data > MSSQL' 카테고리의 다른 글

Oracle NEXT_DAY 함수를 MSSQL NEXT_DAY 로 만들기  (0) 2020.02.17
[MSSQL] 트리구조 탐색 쿼리 - 모든 부모 node 탐색  (0) 2019.10.24
MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05

MSSQL 복제 관련 조회 쿼리 입니다.



■ 서버가 배포자로 설정되어 있는지 확인

installed = 1 : 배포 서버로 설정 됨



■ 복제 대상인 테이블 목록 조회

1
2
3
4
5
select name, create_date, modify_date, is_published, is_replicated, type_desc
from sys.tables 
where (is_published = 1 or is_merge_published = 1 or is_schema_published = 1)
        -- and name = 'CF_RANK_USER_PRE_TD'
order by name asc
cs



저작자표시 비영리 변경금지

'Database & Data > MSSQL' 카테고리의 다른 글

[MSSQL] 트리구조 탐색 쿼리 - 모든 부모 node 탐색  (0) 2019.10.24
SP_LOCK 출력 결과의 정렬 순서 개선 (Mode DESC)  (0) 2019.01.01
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05

쿼리 유형별 트랜잭션 로그의 증가 수치를 설명하고, 

트랜잭션 로그 축소 쿼리를 설명 드리겠습니다.


▷TEST 장비 : i7 LG gram NoteBook, RAM: 24 G, SSD

▷TEST DB 정보 : 로그파일 10MB 단위로 증가


▷TEST TABLE 구조


▷ 작업 유형별 로그파일 증가량 : 각 테스트 전 로그파일을 기본 24MB로 축소

 건수

 작업 방식

 LOG 파일 증가량

 100만건 insert

 WHILE 을 이용하여 1건씩 100만번 INSERT

 24MB ⇒ 284MB

 집합 복제를 통하여 100만건을 한번에 INSERT

24MB ⇒ 524MB

 100만건 update

 WHILE을 이용하여 1000건씩 1000번 UPDATE

24MB ⇒ 94MB

 한번에 100만건 UPDATE

 24MB ⇒ 454MB

▷ 결론

1. 로그파일 증가를 최소화 하고 싶다면 INSERT, UPDATE, DELETE를 WHILE을 사용하여 나눠서 실시 → 단, 작업 시간은 증가


▷ 로그파일 축소 : 

    - DB가 사용중(update, select가 실행되고 있는 중) 에 로그파일 축소가 가능합니다.

      하지만 가급적 점검시 사용하는 것이 좋겠습니다.

1
2
3
4
5
6
7
8
9
10
11
12
ALTER DATABASE test_db  --test_db : DB명
SET RECOVERY SIMPLE;
GO
 
-- 로그파일을 10MB로 축소
-- DB 속성의 처음크기 이하로는 줄일 수 없음
/*
DB 속성의 처음크기를 너무 크게 잡은 경우, 해당 값을 500MB 정도로
줄인 후 로그 축소를 해야 함
*/
DBCC SHRINKFILE (TEST_DB_log, 10);  --TEST_DB_log : LOG 파일 명
GO
 
ALTER DATABASE test_db   --test_db : DB명
SET RECOVERY FULL;
GO

cs


'Database & Data > MSSQL' 카테고리의 다른 글

SP_LOCK 출력 결과의 정렬 순서 개선 (Mode DESC)  (0) 2019.01.01
MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24

JOIN 별 결과 집합을 도식화한 자료 입니다. 어디서 구했는지 생각나질 않아 링크는 못 걸어 드립니다.




'Database & Data' 카테고리의 다른 글

상황에 맞는 그래프 선  (0) 2023.01.02
LabVIEW 와 DB연동을 위한 전반적인 내용 입니다.  (0) 2020.04.20
일련 번호 중 빠진 번호 조회  (0) 2017.09.18
세미나 동영상 [최적 데이터 구조 설계]-다운 가능  (0) 2015.09.20
ER Studio Relationship 관계선 종류  (0) 2015.08.14

아래와 같이 Row 구분자 = "-" , Column 구분자 = "/" 인 문자열을

테이블 형식으로 변환해 보겠습니다.

EX) '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701'


>> Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DECLARE @V_STRING VARCHAR(3000);
SET @V_STRING = '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701';
 
WITH TA AS
(
    SELECT 1 LV
    UNION ALL
    SELECT LV + 1 LV FROM TA WHERE LV + 1 <= 4
) ,
TB AS
(
    SELECT 1 RNUM, dbo.regexp_substr(@V_STRING, '_', 1, 1) TXT
    UNION ALL
    SELECT RNUM + 1 RNUM, dbo.regexp_substr(@V_STRING , '_', 1, RNUM + 1) TXT
    FROM TB
    WHERE RNUM + 1 <= LEN(dbo.fn_StripCharacters(@V_STRING, '^_+'))+1
)
SELECT TC.RNUM
    , MAX(CASE WHEN TC.LV = 1 THEN TC.TXT ELSE NULL END) COL1
    , MAX(CASE WHEN TC.LV = 2 THEN TC.TXT ELSE NULL END) COL2
    , MAX(CASE WHEN TC.LV = 3 THEN TC.TXT ELSE NULL END) COL3
    , MAX(CASE WHEN TC.LV = 4 THEN TC.TXT ELSE NULL END) COL4
FROM (
    SELECT TB.RNUM, TA.LV, dbo.regexp_substr(TB.TXT, '/', 1, TA.LV) TXT
    FROM TB,TA
) TC
GROUP BY TC.RNUM
ORDER BY TC.RNUM ASC
cs


>> 결과 집합


>> 필요 함수

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/*
    원본 문자열에서 특정 문자열을 제거
*/
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String                NVARCHAR(MAX), 
    @MatchExpression    VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'
 
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
 
    RETURN @String
 
END
GO
 
 
/*
    원본 문자열에서 특정 문자열 부분을 추출
*/
CREATE  FUNCTION [dbo].[REGEXP_SUBSTR] 
(
    @i_str varchar(1000), 
    @i_delimeter varchar(10), 
    @i_position int,
    @i_index int
) RETURNS varchar(2000)
AS BEGIN
 
    DECLARE @o_str varchar(2000); 
    SET @o_str = '';
 
    WITH t AS (
        SELECT 1 as lvl, CONVERT(VARCHAR(2000), @i_str) AS code
        UNION ALL
        SELECT lvl + 1 as lvl
            , CONVERT(VARCHAR(2000), SUBSTRING(code, CHARINDEX(@i_delimeter, code, 1) + 1, 99) ) AS code
        FROM t
        WHERE CHARINDEX(@i_delimeter, code, 1) > 0
    )
 
    SELECT @o_str = CONVERT(VARCHAR(2000), LEFT(code, CHARINDEX(@i_delimeter, code + @i_delimeter, 1) - 1))
    FROM t
    WHERE lvl = @i_index        
 
    RETURN @o_str
 
END
GO
 
cs


'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25

>> ORACLE 의 connect by level  구문을 MSSQL 로 변환

Oracle Query

 MSSQL Query  (MSSQL 2005 이상)

쿼리 결과
1
2
3
SELECT rownum RNUM
FROM DUAL
CONNECT BY LEVEL <= 5;
cs
1
2
3
4
5
6
7
WITH TA AS
(
    SELECT 1 LV
    UNION ALL
    SELECT LV + 1 LV FROM TA WHERE LV + 1 <= 5
)
SELECT * FROM TA
cs


>> 100 개 이상 출력시 오류 발생


>> option(maxrecursion 0) 구문을 추가하여 최대 재귀 횟수 제한 해제

1
2
3
4
5
6
7
8
WITH TA AS
(
    SELECT 1 lv
    UNION ALL
    SELECT lv + 1 FROM TA WHERE lv + 1 <= 200
)
SELECT * FROM TA option(maxrecursion 0)
;
cs

>> 100 개 이상 표시 출력 됨

 


'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24

LOGIN_LOG 테이블에서 연속 출석일수를 조회하는 쿼리 입니다. 

≫ 테이블 구조

  - 특정 기간 동안의 유저 로그인 기록 입니다. 일자별로 여러개 row가 있을 수 있는데, 

    우리가 원하는 것은 연속 출석일 수 를 구하는 것이기 때문에 유저별,일자별 그룹핑 하였습니다.

  - 결과 테이블 설명

1. 37번 유저 : 19일, 20일, 21일, 22일 연속해서 로그인 하였기 때문에 연속 출석일 수 = 4일

2. 38번 유저 : 6일, 7일 연속 출석일 수 = 2, 26일, 27일, 28일 연속 출석일 수 = 3일 

   로 계산 되었습니다.

 기반 테이블

 연속 출석일 수 계산 결과

 

 


≫ Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DECLARE @v_start_date datetime, @v_end_date datetime;
SET @v_start_date = '2018-02-01 00:00:00';
SET @v_end_date = '2018-02-28 23:59:59';
 
with tmp_user_login_list as (
    select ta.usn, ta.login_date
    from (
        select usn, convert(char(10),login_date,23) login_date
        from TBL_USER_CONNECTION_LOG where LOGIN_DATE between @v_start_date and @v_end_date
    ) ta
    group by ta.usn, ta.login_date
    --order by ta.usn, ta.login_date asc
),
tmp_user_min_login_date as (
    select usn, min(convert(char(10),login_date,23)) start_login_date
    from TBL_USER_CONNECTION_LOG where LOGIN_DATE between @v_start_date and @v_end_date
    group by usn
)
select usn, MIN(login_date) std_dt, MAX(login_date) end_dt , COUNT(*) cnt
from ( 
        select  ta.usn, login_date
        ,   ROW_NUMBER() over (order by  ta.usn, login_date) idx        -- 자료의 일자별 순서
        ,   DATEDIFF( dd, login_date, tb.start_login_date )  no            -- 유저의 해당 기간 최초 접속 일
        from  tmp_user_login_list ta
        inner join tmp_user_min_login_date tb on ta.USN = tb.USN
)  r
group by usn, idx + no
having count(*) > 1        -- 연속 로그인 일수가 1일 이상인 자료
order by usn, idx + no  desc
cs





'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24
MSSQL dm_exec_query_stats DMV 테스트  (0) 2016.07.05

통화코드 자료가 필요하여 DB에 입력 후 자료 공유 합니다.


사이트 : https://ko.wikipedia.org/wiki/ISO_4217

Table Schema : 

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `TBL_CURRENCY_CODE` (
 
  `code` varchar(10) NOT NULL COMMENT '통화코드',
 
  `no` int(11) NOT NULL COMMENT '통화 번호\n',
 
  `name` varchar(100) NOT NULL COMMENT '통화명',
 
  `nation_code` varchar(10) DEFAULT NULL COMMENT '국가코드',
 
  PRIMARY KEY (`code`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
cs

ScreenShot : 


 첨부 파일 : MySQL 테이블 백업파일

TBL_CURRENCY_CODE.bak


* 좋은 정보가 있는 사이트를 추천해 주시면 DB에 저장 후 공유 하겠습니다.



'Database & Data > Data' 카테고리의 다른 글

10년치 코스피 기업별 외국인/기관 보유 현황 (2007-02-23 ~ 2017-11-08)  (4) 2017.11.08
WICS 코드표 Excel 파일 입니다.  (1) 2017.11.04
코스피 주가 데이터 (17년치, 2000-01-04 부터)  (18) 2017.11.01
50년치 기상청 날씨 데이터-MySQL  (0) 2017.10.28
환율 데이터 (2010-01-01 ~ 2017-10-16) - 7년10개월 치 환율 데이터  (0) 2017.10.16

2010-01-01 ~ 2017-10-16 동안의 환율 데이터를 MySQL 에 저장 후 테이블 백업 파일 공유 드립니다.


환율 정보 사이트 : https://www.kebhana.com/cont/mall/mall15/mall1501/index.jsp

테이블 구조 :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE `TBL_EXCHANGE_RATE` (
 
  `basic_date` date NOT NULL COMMENT '기준일',
 
  `currency_code` varchar(5) NOT NULL COMMENT '통화코드',
 
  `cash_buy_exchange_rate` float NOT NULL COMMENT '현찰 살때 환율',
 
  `cash_buy_spread` float NOT NULL COMMENT '현찰 살때 spread',
 
  `cash_sell_exchange_rate` float NOT NULL COMMENT '현찰 팔때 환율',
 
  `cash_sell_spread` float NOT NULL COMMENT '현찰 팔때 spread',
 
  `transfer_send` float NOT NULL COMMENT '송금 보낼 때',
 
  `transfer_receive` float NOT NULL COMMENT '송금 받을 때',
 
  `tc_buy` float NOT NULL COMMENT 'T/C 사실때 ',
 
  `buy_basic_rate` float NOT NULL COMMENT '매매기준율',
 
  `transfer_commission` float NOT NULL COMMENT '환가료율',
 
  `usd_change_rate` float NOT NULL COMMENT '미화 환산율',
 
  `reg_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 
  PRIMARY KEY (`basic_date`,`currency_code`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
cs


첨부 파일 : MySQL 테이블 백업 파일

TBL_EXCHANGE_RATE.bak



* 환율 정보 사이트와 같이 유용한 정보가 있는 사이트를 공유해 주시면 DB형태로 변환하여 공유 드리겠습니다.



'Database & Data > Data' 카테고리의 다른 글

10년치 코스피 기업별 외국인/기관 보유 현황 (2007-02-23 ~ 2017-11-08)  (4) 2017.11.08
WICS 코드표 Excel 파일 입니다.  (1) 2017.11.04
코스피 주가 데이터 (17년치, 2000-01-04 부터)  (18) 2017.11.01
50년치 기상청 날씨 데이터-MySQL  (0) 2017.10.28
통화코드 MySQL 파일 입니다.  (0) 2017.10.16

일련 번호의 빠진 번호 중 가장 작은 값을 조회하는 쿼리 입니다.

MSSQL 쿼리이며 WITH(NOLOCK) 부분만 제가하면 Oracle 에서도 사용 가능 합니다.


1
2
3
4
5
6
-- {0} = 지정된 번호 이후의 비어있는 일련 번호 조회
 
SELECT min(IDX + 1)
FROM (SELECT {0} IDX UNION ALL SELECT IDX FROM TBL_TEST WITH(NOLOCK) WHERE IDX > {0}) TA
WHERE IDX+1 NOT IN (SELECT IDX FROM TBL_TEST WITH(NOLOCK))
 
cs



'Database & Data' 카테고리의 다른 글

상황에 맞는 그래프 선  (0) 2023.01.02
LabVIEW 와 DB연동을 위한 전반적인 내용 입니다.  (0) 2020.04.20
JOIN 종류 도식화  (0) 2018.08.09
세미나 동영상 [최적 데이터 구조 설계]-다운 가능  (0) 2015.09.20
ER Studio Relationship 관계선 종류  (0) 2015.08.14

MSSQL 2005 이상에서 사용 가능한  xml 문자열을 파싱하여 DB에 저장하는 방법 입니다.


* 입력받을 xml 형식의 아이템 문자열 입니다.


XML Type 1

<items>  

<item><id>M4A1</id><price>500</price></item>

<item><id>AK47</id><price>600</price></item>

<item><id>9A-91</id><price>700</price></item>

<item><id>M4</id><price>700</price></item>  

</items> 

XML Type 2

<items>

<item id="M4A1" price=500></item>

<item id="AK47" price=600></item>

<item id="9A-91" price=700></item>

<item id="M4" price=800></item>

</items>


* SP 입니다.

CREATE proc [dbo].[sp_xml_insert] (

@i_xml Varchar(3000)

)

as

BEGIN

    DECLARE @docHandle int;                                   --xml 문서핸들

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @i_xml;

    INSERT INTO tbl_items (item, price)

    SELECT *

    FROM OPENXML(@docHandle,'/items/item',3)   --flags 3으로 입력시 위 2가지 xml 형식에 대하여 모두 정상 변환 합니다.

    WITH ( id VARCHAR(30)                                      --3이 아닐 경우 항목이 null 이 표시 됩니다.

                ,price BIGINT

                );

    EXEC sp_xml_removedocument @docHandle;

END


** OPENXML 함수 설명

https://msdn.microsoft.com/ko-kr/library/ms186918.aspx

flag: XML 데이터와 관계형 행 집합 사이에 사용해야 하는 매핑과 남는 열을 채우는 방법을 나타냅니다. flags는 선택적 입력 매개 변수이며 다음 값 중 하나일 수 있습니다.

바이트 값설명
0기본적으로 특성 중심 매핑을 사용합니다.
1특성 중심 매핑을 사용합니다. XML_ELEMENTS와 결합할 수 있습니다. 이 경우 특성 중심 매핑이 먼저 적용된 다음 아직 처리되지 않은 모든 열에 대해 요소 중심 매핑이 적용됩니다.
2요소 중심 매핑을 사용합니다. XML_ATTRIBUTES와 결합할 수 있습니다. 이 경우 특성 중심 매핑이 먼저 적용된 다음 아직 처리되지 않은 모든 열에 대해 요소 중심 매핑이 적용됩니다.
8

XML_ATTRIBUTES 또는 XML_ELEMENTS와 결합(논리적 OR 연산을 수행)할 수 있습니다. 검색 상황에서 이 플래그는 소비된 데이터를 오버플로 속성인 @mp:xmltext로 복사할 수 없음을 나타냅니다.



-



'Database & Data > MSSQL' 카테고리의 다른 글

Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24
MSSQL dm_exec_query_stats DMV 테스트  (0) 2016.07.05
MSSQL mdf 파일 만으로 DB 복원중 발생하는 오류 해결법  (2) 2016.06.22

게임 DBA 를 하다보면 이벤트 관련한 쿼리 제작을 많이 하게 됩니다. 

보통의  [이벤트 대상자 추출] -> [대상자 들에게 상품 지급]  구조로 쿼리를 작성 하시게 되는데요 

이벤트 대상자를 산정하는 로직이 복잡 하거나 상품 지급 로직이 복잡 해지게 되면 쿼리가 길어지게 되고 그로인해 버그가 발생할 확률이 올라 갑니다.

그걸 방지하기 위해서 쿼리도 모듈화를 많이 하게 됩니다.

위 경우는 크게 [이벤트 대상자 추출] , [아이템 지급] 두개의 sp로 나눌 수 있습니다. 그때 sp간 자료 공유를 위해  쿼리에서 select into로 물리 테이블을 만들어 사용하시는데 그럴 필요 없이 지역 임시 테이블을 사용 해도 동일하게 구성할 수 있습니다.


대상자 추출 sp 입니다.


  CREATE proc [dbo].[sp_temptable_send] (  

@command Varchar(5)

  , @o_ret int out

  )

  as

set @o_ret = -1;

--다른 sp와 공유할 임시 테이블 생성 

create table #tmp_user( name varchar(30));

insert into #tmp_user(name) values (CONVERT(varchar(30),GETDATE(),21));

exec dbo.sp_temptable_receive 'test', @o_ret output; --호출 당한 sp에서 임시테이블의 정보를 select 

--지역 임시 테이블은 Procedure가 종료되면 자동으로 삭제됨 

--drop table #tmp_user;

return; 


상품 지급 sp


  CREATE proc [dbo].[sp_temptable_receive] (

  @command Varchar(5)

  , @o_ret int out

  )

  as

  set @o_ret = -1;

  --호출측 과 약속한 임시테이블에서 데이터를 꺼냄

  insert into [dbo].[tbl_test](cname) select name from #tmp_user;

  set @o_ret = @@ROWCOUNT;

  return;

 


대상자 추출 sp에서 대상자와 상품을 매칭 시켜 임시테이블에 담아 놓으면

상품 지급 sp에서는 해당 명칭의 임시테이블에서 정보를 가져와 실제로 상품을 지급하는 동작을 수행 합니다.

상품지급 sp를 생성할 때 임시테이블의 유무는 검사하지 않기 때문에 

상품지급 sp를 미리 만들어 놓고 약속된 임시테이블을 대상자 추출 sp에서 생성한 후 상품 지급 sp를 호출하여

정상적으로 상품을 지급할 수 있습니다.


위와 같이 하면 변하지 않는 상품 지급 sp는 한번만 만들어 놓고 검증까지 완료해 놓으면 매번 이벤트 때마다 상품 지급이 정상적으로 되었는지 

검증할 필요가 없어 집니다.

읽어 주셔서 감사합니다.



위 주제 관련 혹은 DB에 관련하여 토의하고 싶으신 내용이 있으시면 댓글 남겨 주세요.^^

'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL dm_exec_query_stats DMV 테스트  (0) 2016.07.05
MSSQL mdf 파일 만으로 DB 복원중 발생하는 오류 해결법  (2) 2016.06.22
MS SQL Express 2014 다운로드 경로  (0) 2016.04.05

DMV(동적관리뷰) 중 dm_exec_query_stats 에 대해서 알아 보겠습니다.


MSDN의 설명을 빌리자면   https://msdn.microsoft.com/ko-kr/library/ms189741.aspx


SQL Server에서 캐시된 쿼리 계획에 대한 집계 성능 통계를 반환합니다. 이 뷰에는 캐시된 계획 내의 쿼리 문당 하나의 행이 포함되어 있습니다. 


라고 설명 되어 있습니다. 


이 중 '캐시된 계획 내의 쿼리 문당 하나의 행' 에 대하여 테스트를 진행하였습니다.



테스트에 쓰인 SP 입니다. 

입력 변수의 값에 따라 총 3가지의 실행 계획이 생성 됩니다.

1번 IF 문만 실행,  2번 IF 문만 실행,   3번 IF 문만 실행



실행 계획이 3개가 생기니

뷰의 ROW로 실행에 맞춰 3개의 ROW가 생성 되었습니다.




자세한 내용은 아래 동영상을 참고해 주시길 바랍니다. 




'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24
MSSQL mdf 파일 만으로 DB 복원중 발생하는 오류 해결법  (2) 2016.06.22
MS SQL Express 2014 다운로드 경로  (0) 2016.04.05

MSSQL에서 mdf 파일만 가지고 DB을 생성할 수 있습니다.


하지만 이상하게 운영체제 오류, 오류:5120 오류가 발생 하는데, 설명은 부실하고, 몇번을 해봐도 똑같습니다.


해결 방법은 아주 간단 합니다. Ssms.exe Management Studio 응용프로그램을 관리자 권한으로 실행 시켜 주고


연결을 수행하면, 아~무~런 오류 없이 DB가 연결 됩니다.




'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24
MSSQL dm_exec_query_stats DMV 테스트  (0) 2016.07.05
MS SQL Express 2014 다운로드 경로  (0) 2016.04.05

https://www.microsoft.com/ko-kr/download/details.aspx?id=42299  << 로그인 없이 다운로드 가능

구글이나 네이버에서 검색하면 대부분 로그인을 해야하는 곳으로 안내한다. 



SQLEXPRADV 라고 되어있는 것이 Management Studio  가 함께 설치되는 파일이다. 

https://www.microsoft.com/ko-kr/download/details.aspx?id=42299  다운로그 경로 다시한번.


삭제시 에는


Microsoft SQL Server 2014(64비트) 를 클릭하여 삭제를 진행 합니다.




'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24
MSSQL dm_exec_query_stats DMV 테스트  (0) 2016.07.05
MSSQL mdf 파일 만으로 DB 복원중 발생하는 오류 해결법  (2) 2016.06.22

+ Recent posts

티스토리툴바