Skip to content

Instantly share code, notes, and snippets.

@tchen
Last active February 27, 2020 06:01
Show Gist options
  • Save tchen/593abcf9856d5c748144e93f70d0971b to your computer and use it in GitHub Desktop.
Save tchen/593abcf9856d5c748144e93f70d0971b to your computer and use it in GitHub Desktop.
NTILE(), percentile_disc(), percentile_cont()

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)
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);
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment