안녕하세요. 

제가 크롤링 후 올려드리는 MySQL 백업파일의 복원방법을 문의하시는 분들이 있어서 복원 방법 설명드립니다.

제가 올리는 백업파일의 경우 아래와 같이 Database를 생성하는 구문이 없습니다.

그렇기 때문에 먼저 복원할 Database를 생성해 주셔야 합니다.
제가 올리는 백업파일의 명칭이 [db_name]_[날짜] (db_stock_20190407_010101.bak) 이기 때문에 저와 동일하게 db_stock 로 DB를 생성해주셔도 되고, 본인이 미리 생성한 db가 있다면 그대로 사용해 주셔도 됩니다. 

db를 생성할 경우에는 cmd 창을 열어 MySQL에 접속하신 후 create database [db 명]을 실행해 주시면 됩니다.

show databases; 를 실행하여 생성된 db를 확인합니다.

압축파일이 위치한 폴더로 이동 후 mysql -uroot -p [db 명] < [백업파일명]  을 실행하시면 몇분 후 DB 복원이 완료됩니다.

복원 완료 후 MySQL Workbench 로 접속한 모습입니다.

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

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

MySQL / MariaDB Slow Query Log 활성화 및 로그를 테이블에 기록  (0) 2020.07.07
MySQL Index 정보 조회 (key column 을 한줄로 표시)  (0) 2020.07.07
조회 결과를 CSV 파일로 출력  (0) 2017.12.30
Raspberry 에서 사용하는 MariaDB 백업 Shell Script 입니다.  (0) 2017.11.03
[MySQL] 트리구조 탐색 쿼리 - 모든 부모 node 탐색  (3) 2016.05.09

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

다음에서 크롤링한 20년치 증시 데이터 입니다. 

MYSQL 백업파일이며, ERD 첨부 합니다. 

일자별 가격, 외국인 보유 비율 등 입니다.

 

DB 백업파일 다운로드 주소 : http://bitly.kr/R65ncg <<  2020-07-29 다운로드 가능합니다.

첨부된 백업파일의 복원은 아래 게시물을 참고해 주시길 바랍니다.

https://devse.tistory.com/72

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

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

대한민국 환율 데이터 (2006-01-02 ~ 2021-06-18)  (0) 2021.06.19
다음-코스닥 보유 현황 약10년치 (2007-02-23 ~ 2017-12-28) 데이터-CSV  (0) 2017.12.30
다음-코스닥 증시 약20년치 (1998-02-02 ~ 2017-12-28) 데이터-CSV  (6) 2017.12.29
다음-코스피 증시 약20년치 (1983-05-03 ~ 2017-11-07) 데이터-CSV추가  (7) 2017.11.09
10년치 코스피 기업별 외국인/기관 보유 현황 (2007-02-23 ~ 2017-11-08)  (4) 2017.11.08

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

R Studio 에서 ggplot 로 그릴수 있는 그래프 목록 입니다. 

기본 명령어로만 그릴 수 있는 그래프만 표시 하였습니다. 대략적으로 어떤 그래프들이 있는지 보시라는 의미로 게시 합니다.


● 기본 자료 구조

▷ 왼쪽과 같은 구조의 엑셀을 불러와 그래프를 그려주기 위해 오른쪽 구조로 변형하였습니다.

 



[ 질문은 가볍게, 답변은 느긋하게 기다려 주세요. ]


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

R-잔존유저 정보를 그래프로 표현  (0) 2018.08.15

잔존 유저수 정보를 DB에서 추출하게 되면 보통 아래와 같은 구주로 출력 합니다.

하지만 아래와 같은 정보를 R에서 그래프로 표시해 주려면 구조를 변경해야 합니다.

지금부터 그 방법을 설명 드립니다.

        


● R Studio 에서 엑셀파일 불러오기

≫ R Studio 에서는 엑셀을 바로 불러올 수 있습니다. Environment 탭 ⇒ Import Dataset ⇒ From Excel

        

≫ Import Excel Data 폼에서 Excel 파일을 Load 하면 화면에 표시되고, 특정 컬럼의 테이터 형을 변경할 수 있습니다.

        

≫ Code Preview 영역에서 Data Frame의 명칭을 좀더 간단한 명칭으로 변경합니다.

     

    위와 같이 명칭을 변경하는 이유는 구문을 작성할 때 Data Frame의 명칭을 계속 써주어야 하는데 간단하게 줄여 놓아야 쓰기 편하기 때문 입니다.

    아래는 불어오기가 완료된 Data Frame 입니다.

        

    Console 창에서는 자동완성 기능이 지원됩니다.

        

≫ 불러오기가 완료된 Data Frame 입니다.

        


● Data Frame 구조 변경

≫ 위 구조는 그래프로 그려주기 힘들기 때문에 구조를 변경해 주어야 합니다. 

≫ reshape2 라이브러리를 추가합니다.

1
2
3
> library("reshape2", lib.loc="~/R/win-library/3.5")
> 
> dt1 <- melt(Data_1, id=("LEV"))
cs

≫ melt 함수를 사용하여 구조를 아래와 같이 변경 합니다.

        

≫ 컬럼명을 변경 합니다.

1
2
3
> names(dt1)[names(dt1)=="variable"] <- c("KIND")
> 
> names(dt1)[names(dt1)=="value"] <- c("USER_CNT")
cs

        

≫ ggplot 함수를 사용하여 그래프를 그려줍니다.

1
2
3
> g <- ggplot(data = dt1, aes(x=LEV, y=USER_CNT))
> 
> g + geom_line(aes(colour = KIND))
cs


이상 R을 사용한 기본적은 그래프 그리기가 완료 되었습니다.






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

R/ R Studio 기본 그래프 그리기 (ggplot)  (0) 2018.08.16

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

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


▷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

오라클

아래와 같이 구분자로 구분된 여러 항목이 들어 있는 문자열을 파싱하여 표 형태로 출력해 주는 쿼리 입니다.

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'

Row 구분자 :  "_"

Column 구분자 : "/"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT TD.RNUM
  , MAX(CASE WHEN TD.LV = 1 THEN TD.TXT ELSE NULL END) COL1
  , MAX(CASE WHEN TD.LV = 2 THEN TD.TXT ELSE NULL END) COL2
  , MAX(CASE WHEN TD.LV = 3 THEN TD.TXT ELSE NULL END) COL3
  , MAX(CASE WHEN TD.LV = 4 THEN TD.TXT ELSE NULL END) COL4
  FROM (
  SELECT TB.RNUM, TC.LV ,regexp_substr(TB.TXT, '[^/]+', 1, TC.LV) TXT
  FROM (
  SELECT rownum RNUM,regexp_substr(TA.TXT, '[^_]+', 1, LEVEL) TXT
  FROM (SELECT '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701' TXT FROM dual) TA
  CONNECT BY LEVEL <= length(regexp_replace(TA.TXT, '[^_]+',''))+1
  ) TB, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4) TC ) TD
  GROUP BY TD.RNUM
  ORDER BY TD.RNUM ASC;
cs

▶ 출력 결과


기타 문의 사항은 덧글로 부탁 드립니다.


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

하나의 테이블에서 Merge Into 실행  (0) 2020.03.21
Oracle-xml 문자열을 테이블에 저장하기 입니다.  (2) 2016.09.25

안녕하세요. 다음 코스닥 증시 데이터 중 기업의 일자별 기관/외국인 보유 현황 데이터 입니다.


▶ 다음 주식 정보 : http://finance.daum.net/item/quote.daum?code=005930


▶ 업체 코드 정보 : http://devse.tistory.com/attachment/cfile8.uf@99715A3A5A4769592010C0.csv

▶ 보유 현황 데이터

- 컬럼 : 

stock_code : 기업 코드      basic_date : 기준일       foreigner_share_cnt : 외국인 보유 주식 수

foreigner_share_ratio : 외국인 지분율(%)      foreigner_buying_cnt : 외국인 순매수량       organization_buying_cnt : 기관 순매수량

yesterday_ratio : 전일비           updown_ratio : 등락률(%)


코스닥_보유현황.7z.001

코스닥_보유현황.7z.002

코스닥_보유현황.7z.003


궁금하신 사항은 댓글로 남겨 주세요.

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

대한민국 환율 데이터 (2006-01-02 ~ 2021-06-18)  (0) 2021.06.19
코스피/코스닥 증시 데이터#20년#다음(1983-05-03 ~ 2017-12-28)  (10) 2019.04.07
다음-코스닥 증시 약20년치 (1998-02-02 ~ 2017-12-28) 데이터-CSV  (6) 2017.12.29
다음-코스피 증시 약20년치 (1983-05-03 ~ 2017-11-07) 데이터-CSV추가  (7) 2017.11.09
10년치 코스피 기업별 외국인/기관 보유 현황 (2007-02-23 ~ 2017-11-08)  (4) 2017.11.08

mysql 의 조회 결과를 CSV 파일로 출력하는 쿼리 입니다.

조회된 결과에서 컬럼명을 추가해 주기 위하여 union을 사용하였습니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 'stock_code', 'basic_date', 'open_value', 'high_value', 'low_value'
    , 'close_value', 'yesterday_ratio', 'updown_ratio', 'volume_value'
UNION ALL
SELECT ta.stock_code, ta.basic_date, ta.open_value, ta.high_value, ta.low_value
        , ta.close_value, ta.yesterday_ratio, ta.updown_ratio, ta.volume_value
FROM tbl_stock_history_daum ta 
inner join (SELECT stock_code 
            FROM tbl_coporation_info 
            where stock_type='KOSDAQ' order by stock_code asc limit 0,50) tb
on ta.stock_code = tb.stock_code
INTO OUTFILE '/Volumes/USB128G/DataBase/tbl_stock_history_daum_KOSDAQ_000250_011040.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
cs


궁금하신 사항은 댓글로 남겨 주세요.

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

MySQL Index 정보 조회 (key column 을 한줄로 표시)  (0) 2020.07.07
MySQL 복원  (0) 2020.02.22
Raspberry 에서 사용하는 MariaDB 백업 Shell Script 입니다.  (0) 2017.11.03
[MySQL] 트리구조 탐색 쿼리 - 모든 부모 node 탐색  (3) 2016.05.09
[MySQL] mysqldump 스크립트 자동화시 계정에 관한 경고 처리  (0) 2016.03.31

안녕하세요. 코스피 에 이어 이번에는 코스닥 증시 데이터를 수집해 보았습니다.

원하는 분들이 계셔셔, 바쁜 시간 내서 작업해 보았습니다.

유용하게 사용해 주셨으면 좋겠습니다.


▶ 다음 주식 정보 : http://finance.daum.net/item/quote.daum?code=005930

▶ 업체 코드 정보 : http://bitly.kr/LbCuTB  -- 2019-07-07 링크 수정


▶ 7Zip 분할 압축 입니다.

    CSV 컬럼 설명 , A: 업체코드, B: 일자, C: 시가, D:고가, E:저가, F:종가, G:전일비, H:등락률, I:거래량

MySQL 백업 파일은 사용하시는 분들이 없는것 같아 제외 하였습니다. 필요하신 분은 말씀해 주세요.

코스닥.7z.001

코스닥.7z.002

코스닥.7z.003

코스닥.7z.004


▶ MySQL 백업파일 : 7zip 분할 압축

db_stock_20180617_010101.7z.001

db_stock_20180617_010101.7z.002

db_stock_20180617_010101.7z.003

db_stock_20180617_010101.7z.004

db_stock_20180617_010101.7z.005

db_stock_20180617_010101.7z.006

db_stock_20180617_010101.7z.007

db_stock_20180617_010101.7z.008

db_stock_20180617_010101.7z.009

db_stock_20180617_010101.7z.010

db_stock_20180617_010101.7z.011

db_stock_20180617_010101.7z.012

db_stock_20180617_010101.7z.013

db_stock_20180617_010101.7z.014

db_stock_20180617_010101.7z.015


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

코스피/코스닥 증시 데이터#20년#다음(1983-05-03 ~ 2017-12-28)  (10) 2019.04.07
다음-코스닥 보유 현황 약10년치 (2007-02-23 ~ 2017-12-28) 데이터-CSV  (0) 2017.12.30
다음-코스피 증시 약20년치 (1983-05-03 ~ 2017-11-07) 데이터-CSV추가  (7) 2017.11.09
10년치 코스피 기업별 외국인/기관 보유 현황 (2007-02-23 ~ 2017-11-08)  (4) 2017.11.08
WICS 코드표 Excel 파일 입니다.  (1) 2017.11.04

안녕하세요. 이번에는 다음 코스피 증시 데이터를 수집하였습니다.

일전에 야후의 증시 데이터를 수집 하였는데, 맞지 않는다는 의견이 있으셔서 

다음 자료를 다시 수집하게 되었습니다.


▶ 다음 주식 정보 : http://finance.daum.net/item/quote.daum?code=005930

▶ Table Schema 

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `tbl_stock_history_daum` (
  `stock_code` varchar(10) NOT NULL,
  `basic_date` date NOT NULL,
  `open_value` double DEFAULT NULL COMMENT '시가',
  `high_value` double DEFAULT NULL COMMENT '고가',
  `low_value` double DEFAULT NULL COMMENT '저가\n',
  `close_value` double DEFAULT NULL COMMENT '종가',
  `yesterday_ratio` double DEFAULT NULL COMMENT '전일비',
  `updown_ratio` double DEFAULT NULL COMMENT '등락률(단위:%)',
  `volume_value` double DEFAULT NULL COMMENT '거래량\n',
  PRIMARY KEY (`stock_code`,`basic_date`),
  UNIQUE KEY `idx_TBL_STOCK_HISTORY_DAUM_stock_code_basic_date` (`basic_date`,`stock_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='다음 증시 정보';
cs


▶ MySQL 백업파일 - 255 MB

 tbl_stock_history_daum.7z.001

 tbl_stock_history_daum.7z.002

 tbl_stock_history_daum.7z.003

 tbl_stock_history_daum.7z.004

 tbl_stock_history_daum.7z.005

 


재미있게 활용해 주시고요, 기타 의견은 댓글로 부탁 드립니다.


--------------------------------------------------------------- 2017-11-10 ------------------------------------------------------------------

R하시는 분들은 CSV파일로 드리는게 편하실 것 같아서. 전체 데이터를 100개씩 나눠서 CSV로 추출 하였습니다.

아래 내용 참고 하시고 사용해 주세요.


▶  1번 파일로 원하시는 stock_code  확인 하신 후 그에 맞는 7zip 파일을 아래에서 다운받으시면 됩니다.

1.   tbl_coporation_info.csv


2.  tbl_wics_code_info.csv

▶ 증시 데이터 파일 


☆ 번거롭긴 하지만 많은 분들이 편하게 사용 하시길 바라는 마음에서 CSV파일도 추가 하였습니다.


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

다음-코스닥 보유 현황 약10년치 (2007-02-23 ~ 2017-12-28) 데이터-CSV  (0) 2017.12.30
다음-코스닥 증시 약20년치 (1998-02-02 ~ 2017-12-28) 데이터-CSV  (6) 2017.12.29
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

안녕하세요. 오늘은 코스피 기업별 외국인 및 기관의 보유 현황을 수집해 보았습니다.

▨ 일주일에 1개를 목표로 100주 동안 100개의 데이터를 수집하여 달인이 되려는 목표가 있었으나, 일주일에 한개도 벅차네요. ㅠ.ㅠ


대상 : Daum 주식 사이트 http://finance.daum.net/item/foreign.daum?code=005930&nil_profile=stockprice&nil_menu=b013


▶ Table Schema 

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `TBL_STOCK_STAKE_RATIO_DAUM` (
  `stock_code` varchar(10) NOT NULL,
  `basic_date` date NOT NULL,
  `foreigner_share_cnt` double DEFAULT NULL COMMENT '외국인 보유 주식수',
  `foreigner_share_ratio` double DEFAULT NULL COMMENT '외국인 지분율(%)',
  `foreigner_buying_cnt` double DEFAULT NULL COMMENT '외국인 순매수량',
  `organization_buying_cnt` double DEFAULT NULL COMMENT '기관 순매수량',
  `yesterday_ratio` double DEFAULT NULL COMMENT '전일비',
  `updown_ratio` double DEFAULT NULL COMMENT '등락률(%)',
  PRIMARY KEY (`stock_code`,`basic_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='주식 보유 비율(다음)';
cs


▶ MySQL 백업파일 - 111MB

TBL_STOCK_STAKE_RATIO_DAUM.7z.001

TBL_STOCK_STAKE_RATIO_DAUM.7z.002

TBL_STOCK_STAKE_RATIO_DAUM.7z.003


▨ 기타 요청 사항이나. 이런 데이터 수집하면 좋겠다. 하시는 분은 댓글로 내용과 함께 사이트 주소를 남겨 주시면 감사하겠습니다.

▨ 추가로 야후 증시 데이터에 잘못 된 부분이 많다는 의견이 있어, 다음에 있는 정보를 수집 중 입니다. 수집이 완료 되면 공유 드리도록 하겠습니다.


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

다음-코스닥 증시 약20년치 (1998-02-02 ~ 2017-12-28) 데이터-CSV  (6) 2017.12.29
다음-코스피 증시 약20년치 (1983-05-03 ~ 2017-11-07) 데이터-CSV추가  (7) 2017.11.09
WICS 코드표 Excel 파일 입니다.  (1) 2017.11.04
코스피 주가 데이터 (17년치, 2000-01-04 부터)  (18) 2017.11.01
50년치 기상청 날씨 데이터-MySQL  (0) 2017.10.28

증시정보 에서 기업의 종류를 구분하는 WICS 코드 정보 엑셀 파일 입니다.

> 참고 사이트 : http://www.wiseindex.com/About/WICS


> 엑셀 파일


WICS_code.xlsx


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

다음-코스피 증시 약20년치 (1983-05-03 ~ 2017-11-07) 데이터-CSV추가  (7) 2017.11.09
10년치 코스피 기업별 외국인/기관 보유 현황 (2007-02-23 ~ 2017-11-08)  (4) 2017.11.08
코스피 주가 데이터 (17년치, 2000-01-04 부터)  (18) 2017.11.01
50년치 기상청 날씨 데이터-MySQL  (0) 2017.10.28
통화코드 MySQL 파일 입니다.  (0) 2017.10.16

라즈베리에 DB 와 Python 을 설치하여 크롤링 데몬을 돌리고 있습니다.

DB 백업은 정기적으로 하고 있었는데 , 백업 파일을 동일 머신에 놓았더니 이번에 장애로 디스크가 날라 갔네요.

그래서, DB 백업 -> 압축 -> 별도 서버로 전송(Raspberry_02) 로 전송하는 스크립트로 수정 하였습니다.


> 동작 

오늘 날짜 폴더 생성 -> DB Backup -> 오래된 백업 폴더 삭제 -> 당일자 백업폴더 압축 -> 별도 서버에 전달 -> 압축파일 삭제 -> 별도 서버에서 4일전 압축파일 삭제


저는 crontab 에서 하루에 한번씩 호출 하도록 설정해 놓았습니다.


>    본문

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
#!/bin/bash
 
 
#Mysql DB Backup Script
 
# 오늘
nowdate=$(date +"%Y%m%d")
echo $nowdate
# 현재 시간
nowtime=$(date +"%H%M%S")
 
# 4일전
beforeday=$(date +"%Y%m%d" -d '-4days')
echo $beforeday
 
#오늘날짜 폴더 생성
mkdir /home/pi/DB_Backup/$nowdate
 
#DB_LOTTO 백업
mysqldump -uroot -p[password] --routines DB_AUCTION > /home/pi/DB_Backup/$nowdate/DB_AUCTION_"$nowdate"_"$nowtime".bak
 
mysqldump -uroot -p[password] --routines DB_FINANCE > /home/pi/DB_Backup/$nowdate/DB_FINANCE_"$nowdate"_"$nowtime".bak
 
 
#4일전 날짜의 백업 폴더 삭제
rm -rf /home/pi/DB_Backup/$beforeday
 
 
#오늘 날짜 백업 폴더를 압축
tar cfvzP /home/pi/DB_Backup/"$nowdate"_DB_BackupFile.tgz /home/pi/DB_Backup/$nowdate/
 
#압축 파일을 라즈베리2에 전달
scp -P 922 /home/pi/DB_Backup/"$nowdate"_DB_BackupFile.tgz a209@192.168.0.10:/home/a209/DB_Backup
 
#압축 파일 삭제
rm -f /home/pi/DB_Backup/"$nowdate"_DB_BackupFile.tgz
 
 
#Raspberry-02 에서 4일전 압축 파일을 삭제할 것 
ssh -p922 a209@192.168.0.10 "rm -f /home/a209/DB_Backup/$beforeday*.tgz"
 
cs


> 스크립트 파일 :      script_db_backup 2.sh



> 질문이나 의견 있으시면 댓글로 부탁 드립니다.

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

MySQL 복원  (0) 2020.02.22
조회 결과를 CSV 파일로 출력  (0) 2017.12.30
[MySQL] 트리구조 탐색 쿼리 - 모든 부모 node 탐색  (3) 2016.05.09
[MySQL] mysqldump 스크립트 자동화시 계정에 관한 경고 처리  (0) 2016.03.31
[MySQL] binary log 파일 변환 및 오류 해결  (0) 2016.03.25

안녕하세요. 오늘은 국내 코스피 기업들의 주가 데이터를 공유 드립니다.( MySQL)

기간 : 2000-01-04 ~ 2017-10-25


수집 방법은 아래와 같습니다.


1. 먼저 아래 사이트에서 코스피에 등록된 업체 코드를 다운 받았습니다.

http://bigdata-trader.com/itemcodehelp.jsp


2. 야후에서 국내 코스피 주가 정보를 조회할 수 있습니다. (역시 미쿡 센스 입니다.)

https://finance.yahoo.com/lookup


3. 야후 사이트에서 CSV 파일을 다운받아 RDB 로 변환 하였습니다.

총 개수가 900개 정도에 달하니 다운받는 것도 DB에 넣는것도 예상보다 시간이 많이 걸리더라구요.


>  주가 정보 테이블

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `TBL_STOCK_HISTORY` (
  `stock_code` varchar(10) NOT NULL,
  `basic_date` date NOT NULL,
  `open_value` float DEFAULT NULL COMMENT '시가',
  `high_value` float DEFAULT NULL COMMENT '고가',
  `low_value` float DEFAULT NULL COMMENT '저가',
  `close_value` float DEFAULT NULL COMMENT '종가',
  `adj_close_value` float DEFAULT NULL COMMENT '수정 주가',
  `volume_value` float DEFAULT NULL COMMENT '거래량',
  PRIMARY KEY (`stock_code`,`basic_date`),
  UNIQUE KEY `idx_TBL_STOCK_HISTORY_stock_code_basic_date` (`basic_date`,`stock_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
cs

> 업체 코드 테이블
1
2
3
4
5
6
7
CREATE TABLE `TBL_STOCK_INFO` (
  `stock_code` varchar(10) NOT NULL COMMENT '종목 코드',
  `stock_name` varchar(200) DEFAULT NULL COMMENT '종목명',
  `stock_type` varchar(10) DEFAULT NULL COMMENT '종목 타입',
  `use_yn` int(11) DEFAULT NULL COMMENT '사용 여부',
  PRIMARY KEY (`stock_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
cs


> Backup File 용량이 177MB 라서 분할압축하여 올립니다. 

DB_STOCK_2017-11-01.7z.001DB_STOCK_2017-11-01.7z.002DB_STOCK_2017-11-01.7z.003DB_STOCK_2017-11-01.7z.004


> yahoo 에서 다운받은 CSV 파일 입니다. (원하시는 분이 계서서요.)

stock_CSV.7z.001stock_CSV.7z.002stock_CSV.7z.003stock_CSV.7z.004


----------------------------------------------------------------------------------------------------------------------------------------------------------

> 2017-11-04 추가

1. Daum 페이지의 정보를 토대로 기업별 WICS 정보를 추가하였습니다. (http://finance.daum.net/item/main.daum?code=005930)

>> DB DDL :   TBL_STOCK_INFO.bakTBL_WICS_CODE_INFO.bak

>> WICS 정보 :  2017/11/04 - [Database/Data] - WICS 코드표 Excel 파일 입니다.




이제 다음은 나스닥 정보를 수집해 볼까 합니다 만 이건 좀 오래 걸릴 것 같네요.

업체별 세부 정보도 추가해야 할것 같은데 , 조회 가능한 사이트 알고 계시면 추천 부착 드리고요, 개인적인 의견 있으시면 댓글로 달아 주세요.


* CSV 파일을 DB에 넣는 python , eclipse 프로젝트 소스 입니다. 

2017/11/02 - [Python] - Python 으로 CSV 파일을 DB에 저장



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

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

안녕하세요. 오늘은 기상청 날씨 정보를 업로드 합니다.

음력 날짜도 있으니 음력으로 비교해 봐도 좋을 것 같습니다.


기간 : 1960-01-01 ~ 2017-10-26

사이트 : http://www.kma.go.kr/weather/climate/past_cal.jsp


Table Schema :

-- 기상청 지역 코드 테이블

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `TBL_LOCAL_INFO` (
 
  `local_code` int(11) NOT NULL,
 
  `local_name` varchar(15) DEFAULT NULL,
 
  `status` int(11) NOT NULL DEFAULT '1',
 
  PRIMARY KEY (`local_code`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
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
CREATE TABLE `TBL_WEATHER` (
 
  `basic_date` varchar(10) CHARACTER SET latin1 NOT NULL COMMENT '양력',
 
  `local_code` int(11) NOT NULL,
 
  `lunar_date` date DEFAULT NULL COMMENT '음력',
 
  `precipitation` float NOT NULL DEFAULT '0' COMMENT '일 강수량',
 
  `avg_snow` float NOT NULL COMMENT '평균 운량',
 
  `min_temperature` float NOT NULL COMMENT '최저 기온',
 
  `max_temperature` float NOT NULL COMMENT '최고 기온',
 
  `avg_temperature` float NOT NULL COMMENT '평균 기온',
 
  `reg_date` datetime DEFAULT CURRENT_TIMESTAMP,
 
  PRIMARY KEY (`basic_date`,`local_code`),
 
  UNIQUE KEY `idx_TBL_WEATHER_basic_date_local_code_lunar_date` (`lunar_date`,`basic_date`,`local_code`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
cs

ScreenShot:




MySQL 백업파일  DB_WEATHER.7z



* 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
통화코드 MySQL 파일 입니다.  (0) 2017.10.16
환율 데이터 (2010-01-01 ~ 2017-10-16) - 7년10개월 치 환율 데이터  (0) 2017.10.16

통화코드 자료가 필요하여 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

문자열로 아이템 목록을 전달하여 테이블에 저정해야 하는 상황이 종종 있습니다.

,(콤마) 나 |(파이프문자)로 구분된 문자열을 던져  While 안에서 instr + left 의 조합으로 파싱하여 

테이블에 insert 하는 방법이 있지만 구닥다리 에 성능도 좋지 않다.


mssql 이나 oracle 에서는 xml 문자열을 파싱할 수 있는 함수가 있는데 이를 이용하면 손쉽게 테이블에 insert 할 수 있다.

* xml을 사용하면 태그 문자가 붙어 문자열이 길어져 성능상 안좋다고 하는 사람들이 있을 수 있는데. 그런 사람들은 꼭~ 피해 다니길 당부 드린다.


먼저 아이템 목록이 담긴 xml 문자열 입니다.

"<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>"


Oracle SP 입니다.

  PROCEDURE SP_XML_SAVE

  (

    i_xml VARCHAR2 

  )

  IS

  BEGIN

         

    INSERT INTO T_ITEMS(ID,PRICE)

    SELECT 

        EXTRACTVALUE ( column_value, '/item/id' ) id                                                       --item 하위 id 속성의 값을 가져옴 

        ,to_number(EXTRACTVALUE ( column_value, '/item/price' )) price                         --값 추출 후 숫자형으로 변환

    FROM TABLE ( XMLSEQUENCE ( EXTRACT ( xmltype ( i_xml ), '/items/item' ) ) ) p;  --items 하위 item 목록 집합을 추출  

    

  END SP_XML_SAVE; 


SP 테스트 입니다.

DECLARE

  I_XML VARCHAR2(2000);

BEGIN

  I_XML := '<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> </items>';


  PKG_SUB.SP_XML_SAVE(I_XML => I_XML);

END;


결과:



xml 문자열로 입력받은 아이템 목록이 테이블에 잘 저장되어 있습니다.

위 내용은 ORACLE SQL Developer 에서 작성 및 테스트 되었습니다.


기타 궁금한 사항, 논의해 보고 싶으신 사항은 댓글 남겨 주세요. 언제나 환영 합니다.


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

하나의 테이블에서 Merge Into 실행  (0) 2020.03.21
Oracle 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.07.13

게임 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

+ Recent posts

티스토리툴바