Skip to content

Instantly share code, notes, and snippets.

@oscarfonts
Forked from hrwgc/postgresql_quantiles.md
Created May 31, 2018 13:36
Show Gist options
  • Save oscarfonts/447edc1e99d24eaa1ed26472b7c2cffa to your computer and use it in GitHub Desktop.
Save oscarfonts/447edc1e99d24eaa1ed26472b7c2cffa to your computer and use it in GitHub Desktop.
Calculate quantile distributions for PostgreSQL column
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)
#!/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
$ 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] { }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment