Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
SQL Window Functions Example Code

SQL Window Functions Example Code

This gist will contain some example code using SQL window functions. The code is made for PostgreSQL 9.1.9 and may or may not be compatible to other databases. MySQL is certainly not supported as it currently (2014) lacks support for window functions alltogether. The code creates, modifies and deletes some tables (see 05_cleanup.sql), so make sure to run it in a fresh db if you are not sure this does not mess with your existing data.

# Ingore our output files:
*.out
# Rest of this file: Ignore some OS spam
*~
# KDE directory preferences
.directory
# Windows image file caches
Thumbs.db
ehthumbs.db
# Folder config file
Desktop.ini
# Recycle Bin used on file shares
$RECYCLE.BIN/
.DS_Store
.AppleDouble
.LSOverride
# Icon must end with two \r
Icon
# Thumbnails
._*
# Files that might appear on external disk
.Spotlight-V100
.Trashes
# Directories potentially created on remote AFP share
.AppleDB
.AppleDesktop
Network Trash Folder
Temporary Items
.apdisk
CREATE TABLE accounts
( id SERIAL primary key
, owner VARCHAR(8) not null
)
;
INSERT INTO accounts (owner)
VALUES ('bernhard')
, ('julio')
, ('elke')
;
CREATE TABLE transactions
( id SERIAL primary key
, account_id INT not null references accounts(id)
, value_date DATE not null
, amount MONEY
)
;
INSERT INTO transactions (account_id,value_date,amount)
VALUES (1, current_date-4, 100.00)
, (1, current_date-2, - 3.96)
, (1, current_date-2, - 18.65)
, (1, current_date-1, 10.00)
, (2, current_date-4, 20.00)
, (2, current_date-1, - 12.34)
, (3, current_date-2, 100.00)
, (3, current_date-3, -234.56)
;
\timing
SELECT t.account_id
, SUM(t.amount) balance
FROM transactions t
GROUP BY t.account_id
;
SELECT t.*
, SUM(t.amount) OVER
( PARTITION BY t.account_id
ORDER BY t.value_date
, t.id
RANGE BETWEEN unbounded preceding
AND current row
)
AS balance
FROM transactions t
ORDER BY t.account_id
, t.value_date
, t.id
;
SELECT t.*
, (SELECT SUM(u.amount)
FROM transactions u
WHERE u.account_id = t.account_id
AND (u.value_date, u.id)
<= (t.value_date, t.id)
) AS balance
FROM transactions t
ORDER BY t.account_id
, t.value_date
, t.id
;
-- Will throw an error onless this account is created:
-- (this avoids endless running times...
-- INSERT INTO accounts (owner)
-- VALUES ('rando')
-- ;
INSERT INTO transactions (account_id,value_date,amount)
( SELECT 4
, current_date - ROUND(RANDOM()*60) * '1 day'::interval
, (ROUND((RANDOM()-0.45)*10000)/100)::float8::numeric::money
FROM generate_series(1,500000)
)
;
CREATE TABLE amino_acids
( letter CHAR primary key
, name VARCHAR(40) not null
, hydrophobicity REAL
)
;
CREATE TABLE aqp4
( id SERIAL primary key
, nucleotide CHAR not null references amino_acids(letter)
)
;
INSERT INTO amino_acids (letter,name,hydrophobicity)
VALUES ('A', 'Alanin', 1.8)
, ('B', 'Asparaginsäure or Asparagin', null)
, ('C', 'Cystein', 2.5)
, ('D', 'Aspartat', -3.5)
, ('E', 'Glutamat', -3.5)
, ('F', 'Phenylalanin', 2.8)
, ('G', 'Glycin', -0.4)
, ('H', 'Histidin', -3.2)
, ('I', 'Isoleucin', 4.5)
, ('K', 'Lysin', -3.9)
, ('L', 'Leucin', 3.8)
, ('M', 'Methionin', 1.9)
, ('N', 'Asparagin', -3.5)
, ('P', 'Prolin', -1.6)
, ('Q', 'Glutamin', -3.5)
, ('R', 'Arginin', -4.5)
, ('S', 'Serin', -0.8)
, ('T', 'Threonin', -0.7)
, ('U', 'Selenocystein', null)
, ('V', 'Valin', 4.2)
, ('W', 'Tryptophan', -0.9)
, ('Y', 'Tyrosin', -1.3)
, ('Z', 'Glutamat oder Glutamin', null) ;
INSERT INTO aqp4 (nucleotide)
VALUES ('M'),('S'),('D'),('R'),('P'),('T'),('A'),('R'),('R'),('W'),('G'),('K'),('C'),('G'),('P'),('L'),('C'),('T'),('R'),('E'),('N'),('I'),('M'),('V'),('A'),('F'),('K'),('G'),('V'),('W'),('T'),('Q'),('A'),('F'),('W'),('K'),('A'),('V'),('T'),('A'),('E'),('F'),('L'),('A'),('M'),('L'),('I'),('F'),('V'),('L'),('L'),('S'),('L'),('G'),('S'),('T'),('I'),('N'),('W'),('G'),('G'),('T'),('E'),('K'),('P'),('L'),('P'),('V'),('D'),('M'),('V'),('L'),('I'),('S'),('L'),('C'),('F'),('G'),('L'),('S'),('I'),('A'),('T'),('M'),('V'),('Q'),('C'),('F'),('G'),('H'),('I'),('S'),('G'),('G'),('H'),('I'),('N'),('P'),('A'),('V'),('T'),('V'),('A'),('M'),('V'),('C'),('T'),('R'),('K'),('I'),('S'),('I'),('A'),('K'),('S'),('V'),('F'),('Y'),('I'),('A'),('A'),('Q'),('C'),('L'),('G'),('A'),('I'),('I'),('G'),('A'),('G'),('I'),('L'),('Y'),('L'),('V'),('T'),('P'),('P'),('S'),('V'),('V'),('G'),('G'),('L'),('G'),('V'),('T'),('M'),('V'),('H'),('G'),('N'),('L'),('T'),('A'),('G'),('H'),('G'),('L'),('L'),('V'),('E'),('L'),('I'),('I'),('T'),('F'),('Q'),('L'),('V'),('F'),('T'),('I'),('F'),('A'),('S'),('C'),('D'),('S'),('K'),('R'),('T'),('D'),('V'),('T'),('G'),('S'),('I'),('A'),('L'),('A'),('I'),('G'),('F'),('S'),('V'),('A'),('I'),('G'),('H'),('L'),('F'),('A'),('I'),('N'),('Y'),('T'),('G'),('A'),('S'),('M'),('N'),('P'),('A'),('R'),('S'),('F'),('G'),('P'),('A'),('V'),('I'),('M'),('G'),('N'),('W'),('E'),('N'),('H'),('W'),('I'),('Y'),('W'),('V'),('G'),('P'),('I'),('I'),('G'),('A'),('V'),('L'),('A'),('G'),('G'),('L'),('Y'),('E'),('Y'),('V'),('F'),('C'),('P'),('D'),('V'),('E'),('F'),('K'),('R'),('R'),('F'),('K'),('E'),('A'),('F'),('S'),('K'),('A'),('A'),('Q'),('Q'),('T'),('K'),('G'),('S'),('Y'),('M'),('E'),('V'),('E'),('D'),('N'),('R'),('S'),('Q'),('V'),('E'),('T'),('D'),('D'),('L'),('I'),('L'),('K'),('P'),('G'),('V'),('V'),('H'),('V'),('I'),('D'),('V'),('D'),('R'),('G'),('E'),('E'),('K'),('K'),('G'),('K'),('D'),('Q'),('S'),('G'),('E'),('V'),('L'),('S'),('S'),('V')
;
SELECT AVG(hydrophobicity)
FROM aqp4
LEFT JOIN amino_acids
ON amino_acids.letter = aqp4.nucleotide
;
SELECT amino_acids.*
, AVG(hydrophobicity)
OVER (ROWS BETWEEN 5 preceding AND 5 following)
FROM aqp4
LEFT JOIN amino_acids
ON amino_acids.letter = aqp4.nucleotide
;
DROP TABLE aqp4;
DROP TABLE amino_acids;
DROP TABLE transactions;
DROP TABLE accounts;
PSQL=psql
SOURCES=$(sort $(wildcard *.sql))
%.out: %.sql
$(PSQL) -af $< > $@
all: $(SOURCES:.sql=.out)
clean: 05_cleanup.out
rm -v *.out
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment