Michael Keller mhkeller

Count elements

See this gist for a more human readable version https://gist.github.com/mhkeller/4a2ab0e6bf229a6f2bc2

Basic example

select count_els('{1,2,3,4,2,1,2,3,4,5,3,1,2,3,43,1,2,3}'::text[]);

I hereby claim:

To claim this, I am signing this object:

	# Adapted from https://unix.stackexchange.com/questions/60577/concatenate-multiple-files-with-same-header#170692
	# `head -1` gets first line of the file of the file and stashes them as the header row into `all.txt`
	# `tail -n +2 -q *.csv >> all.txt` grabs every csv file from the second row down and stashes them into `all.txt`
	# `all.txt` is a csv file but we use the txt extension to avoid it being captured in the `*.csv` glob.
	# You could also output to a csv file by having your input files share a naming convention such as `file-001.csv` and glob on `file*.csv`
	# But renaming `all.txt` to `all.csv` is sometimes easier than worrying about a naming convention and txt and csvs are the same thing

	head -1 my-csv-01.csv > all.txt; tail -n +2 -q *.csv >> all.txt

	CREATE OR REPLACE FUNCTION rando(anyarray) RETURNS any AS $BODY$
	SELECT unnest($1::anyarray ORDER BY RANDOM()) LIMIT 1
	$BODY$
	LANGUAGE SQL;

	SELECT
	ntile,
	avg(ct) AS avgAmount,
	max(ct) AS maxAmount,
	min(ct) AS minAmount,
	median(ct) as median
	FROM (SELECT ct, ntile(5) OVER (ORDER BY ct) AS ntile FROM (select count(col_name) as ct from table GROUP BY col_Name) t) x
	GROUP BY ntile
	ORDER BY ntile;

	/**
	* This function takes a canvas, context, width and height. It scales both the
	* canvas and the context in such a way that everything you draw will be as
	* sharp as possible for the device.
	*
	* It doesn't return anything, it just modifies whatever canvas and context you
	* pass in.
	*
	* Adapted from Paul Lewis's code here:
	* http://www.html5rocks.com/en/tutorials/canvas/hidpi/

	-- least onerous option from here https://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query#16552441
	set session my.vars.id = '%my_string%';

	SELECT
	*
	FROM
	my_table
	WHERE
	field LIKE current_setting('my.vars.id')::text
	OR other_field LIKE current_setting('my.vars.id')::text