Calculate quantile distributions for PostgreSQL column

postgresql_quantiles.md

SELECT
ntile,
CAST(avg(length) AS INTEGER) AS avgAmount,
CAST(max(length) AS INTEGER)  AS maxAmount,
CAST(min(length) AS INTEGER)  AS minAmount 
FROM (SELECT length, ntile(6) OVER (ORDER BY length) AS ntile FROM countries) x
GROUP BY ntile
ORDER BY ntile;

 ntile | avgamount | maxamount | minamount 
-------+-----------+-----------+-----------
     1 |   2147395 |   3831430 |     76366
     2 |   5000860 |   6155808 |   3837343
     3 |   7570729 |   9008463 |   6157514
     4 |  10563760 |  12137060 |   9008463
     5 |  14479508 |  18657806 |  12153800
     6 |  26754083 |  84192008 |  18773988
(6 rows)

• Background on ntile PostgreSQL window functions
• Source for script

quant_to_carto.sh

#!/bin/bash

if [ -z "$1" ] || [ -z "$2" ]; then
  echo "usage: ./quant_to_carto.sh table_name column_name"
  exit
fi
psql='/usr/local/bin/psql -U postgres -d postgis'
TABLE_NAME="$1"
QUANT_COL="$2"


$psql -c "SELECT ntile, CAST(avg($QUANT_COL) AS INTEGER) AS avgAmount, CAST(max($QUANT_COL) AS INTEGER)  AS maxAmount, CAST(min($QUANT_COL) AS INTEGER)  AS minAmount FROM (SELECT $QUANT_COL, ntile(6) OVER (ORDER BY length) AS ntile FROM $TABLE_NAME) x GROUP BY ntile ORDER BY ntile;" > $TABLE_NAME-$QUANT_COL.txt
cat $TABLE_NAME-$QUANT_COL.txt | sed -nE "1d;s/^ +[^\| ]+ \| +([^ \|]+) +\| +([^ \|]+) +\| +([^ \|]+) *.*$/[$QUANT_COL >= \3][$QUANT_COL <= \2] { }/p" 
rm $TABLE_NAME-$QUANT_COL.txt

usage.md

$ quant_to_carto.sh countries length

[length >= 76366][length <= 3831430] { }
[length >= 3837343][length <= 6155808] { }
[length >= 6157514][length <= 9008463] { }
[length >= 9008463][length <= 12137060] { }
[length >= 12153800][length <= 18657806] { }
[length >= 18773988][length <= 84192008] { }