SQLite7

• [Sqlite] 월을 이름으로 표현하기 1 -> Jan, 2-> Feb 일부 데이터베이스 프로그램에서 지원하는 함수 중에 to_char라는 함수가 있습니다. 이 함수를 사용하게 되면 숫자로 표시된 월을 이름으로 표현할 수 있습니다. 예를 들어 to_char('2019-01-01', 'Month')라고 표현하면 January라고 출력이 되는 것이죠. 아쉽게도 Sqlite는 to_char의 기능이 없습니다. 하지만 다음과 같이 동일한 효과를 구현할 수는 있습니다. substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3 * (strftime('%m', '2019-01-01') - 1 ), 3) substr은 문자열중에서 중간의 일부 문자열을 추출할 때 사용하는 함수입니다. 세 개의 인자를 전달받는데 차례대로 '대상문자열', '문자시작위치',.. 프로그래밍 2023. 1. 12.

• [Sqlite] case문을 이용해서 여러개의 열 만들기 CASE문은 한 번에 한 개의 열 밖에는 리턴할 수 없습니다. 가령 매달의 판매기록을 저장하고 있는 테이블이 있고 이를 각 연도별로 월마다의 매출 기록을 비교하고 싶은 상황을 가정해 봅시다. sales_month | sales 1992-01-01 | 790 1992-02-01 | 539 1992-03-01 | 535 ... 1993-01-01 | 998 1993-02-01 | 568 1993-03-01 | 602 ... 이 경우에 먼저 sales_month에서 달에 해당하는 정보를 추출하여 group by를 이용해 묶어줘야 합니다. 또한 case문을 통해서 sales_month에서의 연도 정보를 추출하고 년도값에 따라서 다른 열에 값을 분배하도록 해야 합니다. 다음과 같이 시도해 볼 수 있습니다. SEL.. 프로그래밍/Sqlite 2023. 1. 11.

• [Sqlite] YoY, MoM, DoD 분석하기 Sqlite를 이용해서 시계열 분석 중 YoY, MoM, DoD 등 구간비교를 하는 방법에 대해서 정리해 보도록 하겠습니다. 특정 시간 구간의 데이터를 이전 시간의 구간 데이터와 비교함으로써 많은 이점을 얻을 수 있는데 계절성등을 확인할 때 유용하게 사용할 수 있습니다. 실제로 많은 데이터들이 과거 같은시점, 전 달의 같은 날짜 등과 같이 1년, 1달, 혹은 1일 주기로 데이터를 비교하며 증감 추이를 신중하게 관찰하는 것을 알 수 있습니다. 각각을 YoY(Year over Year, 전년 대비 증감률), MoM(Month over Month, 전월 대비 증감률), DoD(Day over Day) 전일 대비 증감률이라는 용어로 구분해서 부르고 있죠. 계절성(seasonality)은 일정한 간격을 두고 규칙.. 프로그래밍/Sqlite 2023. 1. 10.

• [Sqlite] 날짜 차원(Date Dimension) 테이블 만들기 시계열 분석을 할 때 주의해야 할 것은 중간에 측 데이터가 생기는 상황에서의 대응입니다. 이를 처리하기 위해서 날짜 차원 테이블을 이용하는데 날짜 차원 테이블은 특정 구간 내 모든 날짜별로 값이 저장된 행이 있는 정적 테이블을 의미합니다. 대부분의 데이터베이스에서 기본적으로 내장되어 있는 테이블이지만 sqlite를 이용할 경우 날짜 차원 테이블을 이용하기 위해서는 직접 만들어주는 수 밖에 없습니다. 다음의 쿼리문을 실행시키면 date_dim이라는 이름의 날짜 차원 테이블을 생성할 수 있습니다. 1. 날짜 차원 테이블 생성 쿼리 -- Create a table to permanently store the output CREATE TABLE date_dim AS -- Initiate the recursive.. 프로그래밍/Sqlite 2023. 1. 6.

• [Sqlite] 이동평균(moving avg) 계산하기 데이터베이스를 시계열 분석을 하게 되면 이동평균을 계산하는 상황이 많이 발생합니다. 이동평균을 구하면 데이터의 노이즈를 줄여주어 경향성을 파악하는데 도움이 많이 되기 때문이죠. 다음은 Postgre에서 이동평균을 구하는 하나의 예제 sql문입니다. 이를 sqlite 문법을 이용해서 구현할 때 어떤 차이점이 있는지 확인해 보도록 하겠습니다. 1. Postgre SELECT a.sales_month ,a.sales ,avg(b.sales) as moving_avg ,count(b.sales) as records_count FROM sales a JOIN sales b on a.kind_of_business = b.kind_of_business and b.sales_month between a.sales_mo.. 프로그래밍/Sqlite 2023. 1. 6.

• [sqlite] date_part 기능 사용하기 Postgre와 같은 데이터베이스에서 날짜 및 시간 데이터 조작을 위한 명령어 중 date_part라는 명령어가 있습니다. 예를 들어 판매날짜가 저장되어 있는 sales_month라는 열의 데이터에서 연도 정보를 추출하기 위해서 다음과 같이 사용합니다. SELECT date_part('year', sales_month) as sales_year; 하지만 sqlite는 위와 같이 date_part와 같은 명령어가 없습니다. 하지만 위와 같은 결과를 출력하기 위해서 아래와 같이 작성해 줄 수 있습니다. SELECT strftime('%Y-01-01', sales_month) as sales_year; 이렇게 작성할 경우 sales_year에 연도를 제외한 나머지 월, 일 정보가 1월 1일로 변경된 날짜가 저.. 프로그래밍/Sqlite 2023. 1. 5.

• [sqlite] csv로부터 데이터베이스 만들기(windows) csv 파일을 sqlite 데이터베이스 파일로 변환하기 위한 방법은 다음과 같습니다. 먼저 실행창에 'PowerShell'을 검색하여 'Windows PowerShell'을 엽니다. csv 파일이 있는 폴더로 이동합니다. 예를 들어 sales.csv 라는 이름의 csv 파일을 mydatabase.db라는 데이터베이스를 만들고 그 안에 table1이라는 이름으로 저장한다고 하면 다음과 같이 명령어를 입력합니다. sqlite3 mydatabase.db ".mode csv" ".import sales.csv table1" ".exit" 명령어를 실행하면 cvs가 위치한 폴더에 mydatabase.db 라는 데이터베이스 파일이 생성되고 SQLite Expert Personal 프로그램을 통해서 데이터베이스를 열.. 프로그래밍/Sqlite 2023. 1. 5.