집계 함수는 계산 결과를 한 행으로 합치는 특성이 있는 반면에 윈도우 함수는 원래 행을 그대로 유지하는 특성이 있다
값과 순위를 매기는 함수가 대표적이다.
단계 2: (값) LAST VALUE(정해진 윈도우 프레임 내에서 마지막 행의 값을 반환)
SQL> SELECT Name, printf ( '%.f minutes', Milliseconds / 1000 / 60 ) AS Length, LAST_VALUE ( Name ) OVER ( ORDER BY Milliseconds RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS LongestTrack FROM tracks WHERE AlbumId = 4;
SQL> SELECT AlbumId, Name, printf ( '%.f minutes', Milliseconds / 1000 / 60 ) AS Length, LAST_VALUE ( Name ) OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS ShortestTrack FROM tracks;
단계 3: LEAD(직후에 물리적으로 떨어진 오프셋 값을 가져오기)
SQL> CREATE VIEW CustomerInvoices AS SELECT CustomerId, strftime('%Y',InvoiceDate) Year, SUM( total ) Total FROM invoices GROUP BY CustomerId, strftime('%Y',InvoiceDate);
SQL> SELECT * FROM CustomerInvoices ORDER BY CustomerId, Year, Total;
SQL> SELECT CustomerId, Year, Total, LEAD ( Total,1,0 ) OVER ( ORDER BY Year ) NextYearTotal FROM CustomerInvoices WHERE CustomerId = 1;
SQL> SELECT CustomerId, Year, Total, LEAD ( Total,1,0 ) OVER ( PARTITION BY CustomerId ORDER BY Year ) NextYearTotal FROM CustomerInvoices;
단계 4: (값) NTH_VALUE(정해진 윈도우 프레임 내에서 N번째 행의 값을 반환)
SQL> SELECT Name, Milliseconds Length, NTH_VALUE ( name,2 ) OVER ( ORDER BY Milliseconds DESC ) SecondLongestTrack FROM tracks;
SQL> SELECT AlbumId, Name, Milliseconds Length, NTH_VALUE ( Name,2 ) OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS SecondLongestTrack FROM tracks;
단계 5: (순위) NTILE(순서가 있는 결과 집합을 정해진 버킷에 나눠서 넣기)
SQL> SELECT Name, Milliseconds, NTILE ( 4 ) OVER ( ORDER BY Milliseconds ) LengthBucket FROM tracks WHERE AlbumId = 1;
SQL> SELECT AlbumId, Name, Milliseconds, NTILE ( 3 ) OVER ( PARTITION BY AlbumId ORDER BY Bytes ) SizeBucket FROM tracks;
단계 6: (순위) PERCENT_RANK(순위를 퍼센트로 보여줌)
SQL> SELECT Name, Milliseconds, PERCENT_RANK() OVER( ORDER BY Milliseconds ) LengthPercentRank FROM tracks WHERE AlbumId = 1;
SQL> SELECT Name, Milliseconds, printf('%.2f',PERCENT_RANK() OVER( ORDER BY Milliseconds )) LengthPercentRank FROM tracks WHERE AlbumId = 1;
SQL> SELECT AlbumId, Name, Bytes, printf('%.2f',PERCENT_RANK() OVER( PARTITION BY AlbumId ORDER BY Bytes )) SizePercentRank FROM tracks;
단계 7: (순위) RANK(순위를 보여줌, 중복 가능, 빈 순서 있음)
SQL> CREATE TABLE RankDemo ( Val TEXT );
SQL> INSERT INTO RankDemo(Val) VALUES('A'),('B'),('C'),('C'),('D'),('D'),('E');
SQL> SELECT * FROM RankDemo;
SQL> SELECT Val, RANK () OVER ( ORDER BY Val ) ValRank FROM RankDemo;
SQL> SELECT Name, Milliseconds, RANK () OVER ( ORDER BY Milliseconds DESC ) LengthRank FROM tracks;
SQL> SELECT Name, Milliseconds, AlbumId, RANK () OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC ) LengthRank FROM tracks;
SQL> SELECT * FROM ( SELECT Name, Milliseconds, AlbumId, RANK () OVER ( PARTITION BY AlbumId ORDER BY Milliseconds DESC ) LengthRank FROM tracks ) WHERE LengthRank = 2;
SQL>
단계 8: (순위) ROW_NUMBER(연속적인 정수를 부여, 중복 불가능)
SQL> SELECT ROW_NUMBER () OVER ( ORDER BY Country ) RowNum, FirstName, LastName, country FROM customers;
SQL> SELECT ROW_NUMBER () OVER ( PARTITION BY Country ORDER BY FirstName ) RowNum, FirstName, LastName, country FROM customers;
SQL> SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY FirstName ) RowNum, FirstName, LastName, Country FROM customers ) WHERE RowNum > 20 AND RowNum <= 30
SQL> CREATE VIEW Sales AS SELECT CustomerId, FirstName, LastName, Country, SUM( total ) Amount FROM invoices INNER JOIN customers USING (CustomerId) GROUP BY CustomerId;
SQL> SELECT Country, FirstName, LastName, Amount FROM ( SELECT Country, FirstName, LastName, Amount, ROW_NUMBER() OVER ( PARTITION BY country ORDER BY Amount DESC ) RowNum FROM Sales ) WHERE RowNum = 1;
CREATE TRIGGER [IF NOT EXISTS] schema_ame.trigger_name
INSTEAD OF [DELETE | INSERT | UPDATE OF column_name]
ON table_name
BEGIN
-- insert code here
END;
단계 2: INSTEAD OF 트리거 예제
SQL> CREATE VIEW AlbumArtists( AlbumTitle, ArtistName ) AS SELECT Title, Name FROM Albums INNER JOIN Artists USING (ArtistId);
SQL> SELECT * FROM AlbumArtists;
SQL> INSERT INTO AlbumArtists(AlbumTitle,ArtistName) VALUES('Who Do You Trust?','Papa Roach');
SQL> CREATE TRIGGER insert_artist_album_trg INSTEAD OF INSERT ON AlbumArtists BEGIN INSERT INTO Artists(Name) VALUES(NEW.ArtistName); INSERT INTO Albums(Title, ArtistId) VALUES(NEW.AlbumTitle, last_insert_rowid()); END;
SQL> INSERT INTO AlbumArtists(AlbumTitle,ArtistName) VALUES('Who Do You Trust?','Papa Roach');
SQL> SELECT * FROM Artists ORDER BY ArtistId DESC;
원본 학습자료는 SQLite Trigger를 참고하고, 방송에 사용한 실제 실습 자료는 다음을 참고한다:
단계 1: 트리거 기본 형식
CREATE TRIGGER [IF NOT EXISTS] trigger_name
[BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]
ON table_name
[WHEN condition]
BEGIN
statements;
END;
단계 2: BEFORE INSERT 트리거 예제
SQL> CREATE TABLE leads ( id integer PRIMARY KEY, first_name text NOT NULL, last_name text NOT NULL, phone text NOT NULL, email text NOT NULL, source text NOT NULL );
SQL> CREATE TRIGGER validate_email_before_insert_leads BEFORE INSERT ON leads BEGIN SELECT CASE WHEN NEW.email NOT LIKE '%_@__%.__%' THEN RAISE (ABORT,'Invalid email address') END; END;
SQL> INSERT INTO leads (first_name,last_name,email,phone) VALUES('John','Doe','jjj','4089009334');
CREATE [TEMP] VIEW [IF NOT EXISTS] view_name[(column-name-list)]
AS
select-statement;
단계 2: 복잡한 질의를 단순화하기 위한 뷰 생성
SQL> SELECT trackid, tracks.name, albums.Title AS album, media_types.Name AS media, genres.Name AS genres FROM tracks INNER JOIN albums ON Albums.AlbumId = tracks.AlbumId INNER JOIN media_types ON media_types.MediaTypeId = tracks.MediaTypeId INNER JOIN genres ON genres.GenreId = tracks.GenreId;
SQL> CREATE VIEW v_tracks AS SELECT trackid, tracks.name, albums.Title AS album, media_types.Name AS media, genres.Name AS genres FROM tracks INNER JOIN albums ON Albums.AlbumId = tracks.AlbumId INNER JOIN media_types ON media_types.MediaTypeId = tracks.MediaTypeId INNER JOIN genres ON genres.GenreId = tracks.GenreId;
SQL> SELECT * FROM v_tracks;
단계 3: 전용 컬럼 이름을 제공하기 위한 뷰 생성
SQL> CREATE VIEW v_albums ( AlbumTitle, Minutes ) AS SELECT albums.title, SUM(milliseconds) / 60000 FROM tracks INNER JOIN albums USING ( AlbumId ) GROUP BY albums.title;
CREATE TABLE table_name(
...,
column_name data_type CHECK(expression),
...
);
CREATE TABLE table_name(
...,
CHECK(expression)
);
BEGIN;
-- create a new table
CREATE TABLE new_table (
[...],
CHECK ([...])
);
-- copy data from old table to the new one
INSERT INTO new_table SELECT * FROM old_table;
-- drop the old table
DROP TABLE old_table;
-- rename new table to the old one
ALTER TABLE new_table RENAME TO old_table;
-- commit changes
COMMIT;
단계 2: 컬럼 CHECK 예제
SQL> CREATE TABLE contacts ( contact_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, phone TEXT OT NULL CHECK (length(phone) >= 10) );
SQL> INSERT INTO contacts(first_name, last_name, phone) VALUES('John','Doe','408123456');
SQL> INSERT INTO contacts(first_name, last_name, phone) VALUES('John','Doe','(408)-123-456');
단계 3: 테이블 CHECK 예제
SQL> CREATE TABLE products ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, list_price DECIMAL (10, 2) NOT NULL, discount DECIMAL (10, 2) NOT NULL DEFAULT 0, CHECK (list_price >= discount AND discount >= 0 AND list_price >= 0) );
SQL> INSERT INTO products(product_name, list_price, discount) VALUES('New Product #1',2000,1000);
SQL> INSERT INTO products(product_name, list_price, discount) VALUES('New Product #2',900,1000);
SQL> INSERT INTO products(product_name, list_price, discount) VALUES('New XFactor',1000,-10);
단계 4: 기존 테이블에 CHECK 제약 걸기
SQL> CREATE TABLE contacts ( contact_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, phone TEXT NOT NULL );
SQL> INSERT INTO contacts(first_name, last_name, phone) VALUES('John','Doe','(408)-123-456');
SQL> BEGIN;
SQL> CREATE TABLE new_contacts ( contact_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, phone TEXT NOT NULL CHECK (length(phone) >= 10) );
SQL> INSERT INTO new_contacts SELECT * FROM contacts;
