NTILE(), percentile_disc(), percentile_cont()

README.md

To use this, place the docker-compose.yaml file in a new directory. Then run:

docker-compose up

Once up, you can access http://localhost:8080 to administrate the db. Login with: postgres/example

Or run this from another terminal for command line access:

docker-compose exec db bash

Then run this to connect using the command line:

psql -h localhost -U postgres 

postgres=# select percentile_cont(.20) within group (order by height) from height;
 percentile_cont 
-----------------
             136
(1 row)

postgres=# select percentile_disc(.20) within group (order by height) from height;
 percentile_disc 
-----------------
             120
(1 row)

postgres=# select height, NTILE(5) over (order by height) from height;
 height | ntile 
--------+-------
    120 |     1
    140 |     2
    160 |     3
    170 |     4
    172 |     5
(5 rows)

postgres=# select height, NTILE(2) over (order by height) from height;
 height | ntile 
--------+-------
    120 |     1
    140 |     1
    160 |     1
    170 |     2
    172 |     2
(5 rows)

data.txt

insert into height values ('aloha', 120);
insert into height values ('greg', 140);
insert into height values ('malfoy', 170);
insert into height values ('harry', 172);
insert into height values ('cedric', 160);

docker-compose.yaml

# Use postgres/example user/password credentials
version: '2'

services:

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080