SQL Window Functions Example Code

#SQL-Window-Functions-README.md

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.

.gitignore

# 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

00_create_table.sql

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)
          ;

01_running_balances.sql

\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
        ;

02_random_data.sql

-- 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)
          )
          ;

03_bioseeds.sql

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')
          ;

04_rollmeans.sql

   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
        ;

05_cleanup.sql

DROP TABLE aqp4;
DROP TABLE amino_acids;
DROP TABLE transactions;
DROP TABLE accounts;

Makefile

PSQL=psql
SOURCES=$(sort $(wildcard *.sql))

%.out: %.sql
	$(PSQL) -af $< > $@


all: $(SOURCES:.sql=.out)

clean: 05_cleanup.out
	rm -v *.out