Sql Archives | Data makes sense.

Write less SQL

Let me start by saying I like SQL, and I believe it is fantastic in its ease of use. It’s meant for rearranging and aggregating data, making it easy to create reports from a dimensional model. I do however believe we should not misuse it, and simply write less SQL.

Now, there are two types of SQL code in BI: Simple SQL and Complex SQL.

Simple SQL

Simple SQL is the type of SQL that makes reports from a dimensional model – it seldom has subqueries, and all it does is some joining/grouping/aggregating, as all the numbers are available in fact tables in ready-to-use form. It’s easy enough that even some business users can easily understand it and possibly maintain it.

Simple SQL is fantastic because it allows easy operations on prepared data sets. It is easy to read and to maintain.

Complex SQL

Complex SQL is the SQL that makes fact tables, or the SQL of data products(data transformations for data applications). This SQL often has many subqueries, multiple steps of transformation, sometimes data cleaning and enriching.

Complex SQL is hard to maintain, and even harder to extend or modify. It often ends up worse and worse after each iteration. It’s next to impossible to refactor and you usually end up having to rewrite it after a while.

Complex SQL is cause for high rate of bugs, high maintenance and low bus factors. Since SQL is not easy to test (vs tests in a programming language), it is unlikely that it will ever have good test coverage, making it dangerous to work with. Subtle logic bugs will be well hidden, and due to the monolithic nature of the applications written in SQL it is very hard to maintain and debug.

I suggest to avoid Complex SQL entirely. The key concept for this argument is the ETL vs the ELT. Transform your data before it hits the database, and do it with easily testable and dry code.

Many decide to do the complex transformations in SQL mainly because it’s quick and easy. However, in the long run, this quick and easy SQL code costs you quite a bit of maintenance. And will get worse and worse with each iteration, as it is not possible to easily refactor it.

Instead of the query language, use a programing language with tests. This simply is the better approach if you desire iteration speed and accuracy.

Write less SQL, use a programming language.

That’s right, write less SQL. Use a programming language and dry your code. Only use SQL in easy or temporary transformations, and use something like Python when it comes to doing the transformations. There are limitations to this approach, but they are minor in comparison to the improvement it brings to the BI development process. Make a leap of faith and write less SQL!

MySQL query to HTML table with Python

For easy email reporting, I like to send plain HTML tables. An easy implementation is to take the query output and HTML-ize it.

The script below does just that – it connects to MySQL, sends a query, and turns the result into an HTML table. This table can further be used to be sent with the bimailer, or in any other way.

The MySQL connector for Python can be found on the MySQL official website here.

import mysql.connector

def query_mysql(query):
	cnx = mysql.connector.connect(user='myusername', password='mypass',
								  host='myip',port='myport',
								  database='dwh',charset="utf8", use_unicode = True)
	cursor = cnx.cursor()
	cursor.execute(query)
	#get header and rows
	header = [i[0] for i in cursor.description]
	rows = [list(i) for i in cursor.fetchall()]
	#append header to rows
	rows.insert(0,header)
	cursor.close()
	cnx.close()
	return rows

#take list of lists as argument	
def nlist_to_html(list2d):
	#bold header
	htable=u'<table border="1" bordercolor=000000 cellspacing="0" cellpadding="1" style="table-layout:fixed;vertical-align:bottom;font-size:13px;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(130,130,130)" >'
	list2d[0] = [u'<b>' + i + u'</b>' for i in list2d[0]] 
	for row in list2d:
		newrow = u'<tr>' 
		newrow += u'<td align="left" style="padding:1px 4px">'+unicode(row[0])+u'</td>'
		row.remove(row[0])
		newrow = newrow + ''.join([u'<td align="right" style="padding:1px 4px">' + unicode(x) + u'</td>' for x in row])  
		newrow += '</tr>' 
		htable+= newrow
	htable += '</table>'
	return htable
	


def sql_html(query):
	return nlist_to_html(query_mysql(query))
	
#usage example
#query = "select date, sum(sales) as sum_of_sales from table order by 1 desc limit 10"
#print sql_html(query)

import mysql.connector

def query_mysql(query):

cnx = mysql.connector.connect(user='myusername', password='mypass',

host='myip',port='myport',

database='dwh',charset="utf8", use_unicode = True)

cursor = cnx.cursor()

cursor.execute(query)

#get header and rows

header = [i[0] for i in cursor.description]

rows = [list(i) for i in cursor.fetchall()]

#append header to rows

rows.insert(0,header)

cursor.close()

cnx.close()

return rows

#take list of lists as argument

def nlist_to_html(list2d):

#bold header

htable=u'<table border="1" bordercolor=000000 cellspacing="0" cellpadding="1" style="table-layout:fixed;vertical-align:bottom;font-size:13px;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(130,130,130)" >'

list2d[0] = [u'<b>' + i + u'</b>' for i in list2d[0]]

for row in list2d:

newrow = u'<tr>'

newrow += u'<td align="left" style="padding:1px 4px">'+unicode(row[0])+u'</td>'

row.remove(row[0])

newrow = newrow + ''.join([u'<td align="right" style="padding:1px 4px">' + unicode(x) + u'</td>' for x in row])

newrow += '</tr>'

htable+= newrow

htable += '</table>'

return htable

def sql_html(query):

return nlist_to_html(query_mysql(query))

#usage example

#query = "select date, sum(sales) as sum_of_sales from table order by 1 desc limit 10"

#print sql_html(query)

MySQL Federated database creation

MySQL does not support entire federated databases, but it does support federated tables – as long as the option is enabled in my.ini or my.cnf.

To create an entire federated DB, in a non manual, and easy to maintain way (refresh table definition if source table structure changes), we can use the following procedure.

First, the caveats:

1. Federated tables do not use indices in the usual way, so you may need to create a table locally and index before expecting any performance.

2. When using a client such as Heidi, that does show table status, the connection between your DB and the source DB of the fed table remains open. An issue can arise if the number of tables is significant, while max connections on source db is low. To get around this, periodically use flush tables on the destination DB to close the connections. There are alternatives to this, but this is the simplest workaround (may not be the best).

Personally, to get around this issue I scheduled a MySQL event that flushes every federated table (not FLUSH TABLES, but FLUSH TABLE TABLENAME;FLUSH TABLE TABLENAME2), to avoid blocking other queries. FLUSH TABLES flushes all tables, so it has to wait for other queries to finish, and any new queries have to wait for it to finish, causing no possibility of concurrent queries.

1. Get a nodata mysqldump in the same way as you get a normal dump, but with `-d` switch for no data

mysqldump -d -P3306 -h 1.1.1.1 -u username -pPASS dbname > mydump.sql

1	mysqldump -d -P3306 -h 1.1.1.1 -u username -pPASS dbname > mydump.sql

2. Convert the engine part of the tables into federated+connection (here done with Python)

# (.*?) is a capturing group, to allow the contents between that pattern to be reused later
# ´.*?´ which is the non greedy version of ´.*´
# ´\1´ is a backreference for the first capturing group for each occurrence, which will be for example "tablename1", "tablename2" and so on
# the patterns as raw string with the prefix ´r´ (which is a good habit to take)
# the re.DOTALL flag which allows "." to match newlines

import re

def convertdump(input, output, conn):
fin = open(input,'r')
filedata = fin.read()
fin.close()
pattern = r"CREATE TABLE `(.*?)`(.*?)ENGINE=.*?;"
replace_pattern = r"CREATE TABLE `\1`\2ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CONNECTION='"+conn+r"\1';"
newdata = re.sub(pattern, replace_pattern, filedata, flags=re.DOTALL)
fout = open(output,'w')
fout.write(newdata)
fout.close()

if __name__ == '__main__': # only run this part when the file is run, and not when file is imported.
convertdump('mydump.sql', 'fed_dump.sql','mysql://user:pass@ip:port/db/' )

# (.*?) is a capturing group, to allow the contents between that pattern to be reused later

# ´.*?´ which is the non greedy version of ´.*´

# ´\1´ is a backreference for the first capturing group for each occurrence, which will be for example "tablename1", "tablename2" and so on

# the patterns as raw string with the prefix ´r´ (which is a good habit to take)

# the re.DOTALL flag which allows "." to match newlines

import re

def convertdump(input, output, conn):

fin = open(input,'r')

filedata = fin.read()

fin.close()

pattern = r"CREATE TABLE `(.*?)`(.*?)ENGINE=.*?;"

replace_pattern = r"CREATE TABLE `\1`\2ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CONNECTION='"+conn+r"\1';"

newdata = re.sub(pattern, replace_pattern, filedata, flags=re.DOTALL)

fout = open(output,'w')

fout.write(newdata)

fout.close()

if __name__ == '__main__': # only run this part when the file is run, and not when file is imported.

convertdump('mydump.sql', 'fed_dump.sql','mysql://user:pass@ip:port/db/' )

3. Load up your new dump

mysql --host=127.0.0.1 --port=3306 --user=username--password=pass --database=fed_db_name < "fed_dump.sql"

1	mysql --host=127.0.0.1 --port=3306 --user=username--password=pass --database=fed_db_name < "fed_dump.sql"

You can run this periodically or on demand to refresh the table definitions if the source definitions change

MySQL Rank() function

UPDATE: On July 18 2018 the MySql server team announced the introduction of window functions. The article was written before this, offering an workaround to gain some similar functionality. I updated this article now with the latest and best practice way to do this correctly, but sill leave the old article for reference.

AS of Mysql 8, you can now use window functions such as rownum() OVER(PARTITION BY country) . Interestingly, you can just reference your partition by an alias, and define the partition in a separate window clause section (like where clause), allowing reuse:

mysql> SELECT
         val,
         ROW_NUMBER()   OVER w AS 'row_number',
         rank() OVER w AS 'rank'
       FROM numbers
       WINDOW w AS (ORDER BY val);

mysql> SELECT

val,

ROW_NUMBER() OVER w AS 'row_number',

rank() OVER w AS 'rank'

FROM numbers

WINDOW w AS (ORDER BY val);

Happy window aggregating!

OLD ARTICLE FROM 2014 BELOW:

A quick and dirty custom MySQL Rank() function to emulate rank from PostgreSQL into mySQL. Same disclaimers as the custom rownum() function. In this case, I am using it to rank decimals, but feel free to change the input type.


DROP FUNCTION IF EXISTS rank;
DELIMITER $$
CREATE FUNCTION rank(input DECIMAL(11,4))
  RETURNS int(11)
BEGIN

  set @prvinput=if(@prvinput is null, input, @prvinput);
  set @prv=if(@ranklastrun=CURTIME(6) and @prvinput=input,@prv,if(@ranklastrun=CURTIME(6), @prv+1, 1));
  set @ranklastrun=CURTIME(6);
  set @prvinput=input;
  RETURN @prv;
END $$
$$
DELIMITER ;

DROP FUNCTION IF EXISTS rank;

DELIMITER $$

CREATE FUNCTION rank(input DECIMAL(11,4))

RETURNS int(11)

BEGIN

set @prvinput=if(@prvinput is null, input, @prvinput);

set @prv=if(@ranklastrun=CURTIME(6) and @prvinput=input,@prv,if(@ranklastrun=CURTIME(6), @prv+1, 1));

set @ranklastrun=CURTIME(6);

set @prvinput=input;

RETURN @prv;

END $$

DELIMITER ;

to invoke, use rank(column to rank),such as below:


select nr, rank(nr) from (
select 2 as nr
union all select 2
union all select 2
union all select 3
union all select 34
union all select 4)a
order by 1

select nr, rank(nr) from (

select 2 as nr

union all select 2

union all select 3

union all select 34

union all select 4)a

order by 1

output:

|nr|      |rank(nr)|
|2|       |1|
|2|       |1|
|2|       |1|
|3|       |2|
|4|       |3|
|34|      |4|

|nr| |rank(nr)|

|2| |1|

|3| |2|

|4| |3|

|34| |4|

MySQL rownum() row number function

mysql> SELECT
         val,
         ROW_NUMBER()   OVER w AS 'row_number',
         rank() OVER w AS 'rank'
       FROM numbers
       WINDOW w AS (ORDER BY val);

mysql> SELECT

val,

ROW_NUMBER() OVER w AS 'row_number',

rank() OVER w AS 'rank'

FROM numbers

WINDOW w AS (ORDER BY val);

Happy window aggregating!

OLD ARTICLE FROM 2014 BELOW:

MySQL does not have a built in row number function, which makes it an ugly hack to get the row number – an ugly hack that needs to be repeated over and over, every time you need it.

I have taken the liberty of writing a rownum() function, that is just as bad, but at least it will keep your code clean.

DISCLAIMER:

does not work on older versions of mySQL
It is based on using variables that only reset when time changes. If the function is invoked in 2 separate places in the same microsecond(1 millionth of a second), the output will not be as expected. The variable will not reset, the output will not start counting from 1.
If you run the function more than once in the same query, it will increment every time it is run, so possibly multiple times per row.

For the purposes of BI, I find the associated risks, when used as intended, non existent.
For older versions of mySQL server, you can try finding another creative way to reset the counter for the row number.

Row number function


DROP FUNCTION IF EXISTS rownum;
DELIMITER $$
CREATE FUNCTION rownum()
  RETURNS int(11)
BEGIN
  set @prvrownum=if(@ranklastrun=CURTIME(6),@prvrownum+1,1);
  set @ranklastrun=CURTIME(6);
  RETURN @prvrownum;
END $$
$$
DELIMITER ;

DROP FUNCTION IF EXISTS rownum;

DELIMITER $$

CREATE FUNCTION rownum()

RETURNS int(11)

BEGIN

set @prvrownum=if(@ranklastrun=CURTIME(6),@prvrownum+1,1);

set @ranklastrun=CURTIME(6);

RETURN @prvrownum;

END $$

DELIMITER ;

to invoke it, use rownum(), such as below


select cols, rownum()
from table

select cols, rownum()

from table

Good luck!

Get yesterday’s date in a windows batch file – correct version

Hi all,

If you use a windows server and get yesterday’s date for something like a download, you might probably use this popular script that can be found here and there. Unfortunately, the script is somewhat broken when dealing with getting the last day of the month. The logic is solid, but somewhere between the idea and implementation, something was lost.

see below the working, correct version:


@setlocal enableextensions enabledelayedexpansion
rem Get the date from WMI (on one line).

for /f "skip=2 tokens=2-7 delims=," %%A in ('wmic
path win32_localtime get day^,month^,year^ /format:csv') do (
set /a "yest_yyyy = %%C"
set /a "yest_mm = %%B"
set /a "yest_dd = %%A"
)

rem Not the first of the month, just decrement day.

if not %yest_dd%==1 (
set /a yest_dd = yest_dd - 1
goto done
)

rem Jan 1, set to Dec 31 previous year.

if %yest_mm%==1 (
set /a "yest_dd = 31"
set /a "yest_mm = 12"
set /a "yest_yyyy = yest_yyyy - 1"
goto :done
)

rem Any other day, decrement month.

set /a "yest_mm = yest_mm - 1"

rem Need to find last day, default to 31.

set dim=31

rem Apr/Jun/Sep/Nov all have 30 days. Feb gets special handling.

if %yest_mm%==4 set dim=30
if %yest_mm%==6 set dim=30
if %yest_mm%==9 set dim=30
if %yest_mm%==11 set dim=30
if not %yest_mm%==2 goto :got_dim

rem Default Feb to 28 then use rules to override.

set dim=28

set /a "divid=yest_yyyy%%400"
if "%divid%"=="0" goto daysinmonth_29days
set /a "divid=yest_yyyy%%100"
if "%divid%"=="0" goto :done
set /a "divid=yest_yyyy%%4"
if not "%divid%"=="0" goto :got_dim

rem Adjust to 29 days.

:daysinmonth_29days

set dim=29

:got_dim
set yest_dd=%dim%

:done

 

rem Pad out and return value.

if %yest_mm% lss 10 set yest_mm=0%yest_mm%
if %yest_dd% lss 10 set yest_dd=0%yest_dd%

set yesterday=%yest_yyyy%-%yest_mm%-%yest_dd%

endlocal &amp;&amp; set yesterday=%yesterday%

@setlocal enableextensions enabledelayedexpansion

rem Get the date from WMI (on one line).

for /f "skip=2 tokens=2-7 delims=," %%A in ('wmic

path win32_localtime get day^,month^,year^ /format:csv') do (

set /a "yest_yyyy = %%C"

set /a "yest_mm = %%B"

set /a "yest_dd = %%A"

)

rem Not the first of the month, just decrement day.

if not %yest_dd%==1 (

set /a yest_dd = yest_dd - 1

goto done

)

rem Jan 1, set to Dec 31 previous year.

if %yest_mm%==1 (

set /a "yest_dd = 31"

set /a "yest_mm = 12"

set /a "yest_yyyy = yest_yyyy - 1"

goto :done

)

rem Any other day, decrement month.

set /a "yest_mm = yest_mm - 1"

rem Need to find last day, default to 31.

set dim=31

rem Apr/Jun/Sep/Nov all have 30 days. Feb gets special handling.

if %yest_mm%==4 set dim=30

if %yest_mm%==6 set dim=30

if %yest_mm%==9 set dim=30

if %yest_mm%==11 set dim=30

if not %yest_mm%==2 goto :got_dim

rem Default Feb to 28 then use rules to override.

set dim=28

set /a "divid=yest_yyyy%%400"

if "%divid%"=="0" goto daysinmonth_29days

set /a "divid=yest_yyyy%%100"

if "%divid%"=="0" goto :done

set /a "divid=yest_yyyy%%4"

if not "%divid%"=="0" goto :got_dim

rem Adjust to 29 days.

:daysinmonth_29days

set dim=29

:got_dim

set yest_dd=%dim%

:done

rem Pad out and return value.

if %yest_mm% lss 10 set yest_mm=0%yest_mm%

if %yest_dd% lss 10 set yest_dd=0%yest_dd%

set yesterday=%yest_yyyy%-%yest_mm%-%yest_dd%

endlocal && set yesterday=%yesterday%

Normalizing codes, mySQL extracting alphanumeric functions

Wanted to share an sql function I end up using quite frequently when dealing with user input data – normalization to alphanumeric. It can also be modified into a function to extract numeric values. If you do modify it to that, make sure you keep returns varchar for the cases of extracting numeric values beginning with 0. You can also add other characters, such as the minus sign, if required.

Example use cases:

Matching producer codes:

select producer_id, lcase(alphanumeric(producer_id)) from table
&gt;'BLA-bla-541' ; 'blabla541'
&gt;'bla Bla+/541'; 'blabla541'

select producer_id, lcase(alphanumeric(producer_id)) from table

>'BLA-bla-541' ; 'blabla541'

>'bla Bla+/541'; 'blabla541'

Normalizing/matching phone numbers:

select phone_number, alphanumeric(replace(phone_number, '+', '00') from table
&gt;'+45 097 . 4521/4761' ; '004509745214761'
&gt;'0045/(0)97.4521.4761'; '004509745214761'

select phone_number, alphanumeric(replace(phone_number, '+', '00') from table

>'+45 097 . 4521/4761' ; '004509745214761'

>'0045/(0)97.4521.4761'; '004509745214761'

function:

DELIMITER !

DROP FUNCTION IF EXISTS alphanumeric!

CREATE FUNCTION alphanumeric ( str VARCHAR(255) )
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret VARCHAR(255) DEFAULT '';
DECLARE c VARCHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF  c REGEXP '[[:alpha:]]' or c REGEXP '[[:digit:]]' THEN -- Change matching here
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END !
DELIMITER ;

DELIMITER !

DROP FUNCTION IF EXISTS alphanumeric!

CREATE FUNCTION alphanumeric ( str VARCHAR(255) )

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN

DECLARE i, len SMALLINT DEFAULT 1;

DECLARE ret VARCHAR(255) DEFAULT '';

DECLARE c VARCHAR(1);

SET len = CHAR_LENGTH( str );

REPEAT

BEGIN

SET c = MID( str, i, 1 );

IF c REGEXP '[[:alpha:]]' or c REGEXP '[[:digit:]]' THEN -- Change matching here

SET ret=CONCAT(ret,c);

END IF;

SET i = i + 1;

END;

UNTIL i > len END REPEAT;

RETURN ret;

END !

DELIMITER ;

Cleaning noisy data: Postgresql Mode and Modal Share aggregate function

Calculating mode and modal share on postgresql has never been easier than just running these functions.

Some background:
I needed to calculate the aggregate mode and the frequency of that mode to be able to draw some conclusions about the stability of some particularly noisy data. Sample standard deviation can be very misleading for measuring data noise, as it’s a mix of amplitude of variation and frequency.

Imagine measuring the best temperature to drink wine based on what people would report.

Some would say for a particular wine the temperature would be 10 celsius, while many others will say 11-12 degrees. As we are talking about user input, someone may jokingly recommend drinking it at -20 Celsius or at 120 Celsius. The data cannot be trusted.

So let’s take the array recommended_temperature [8,10,10,12,11,10,10,12,10,11,-20,200,10,10]

Finding the right temperature to serve the wine at from this array can be tricky. Data is noisy and not to be trusted.

Total Numbers: 14

Mean (Average): 21.71429

Standard deviation:51.95624

Variance(Standard deviation):2699.45055

Do any of the above numbers even come close? No. You would not serve the wine at an average of 22 Celsius, and the standard deviation is high, but that doesn’t tell you if the values are spread, or if an outlier is messing everything up.

What about the mode? Mode in our case is 10 (the most common value)
Sounds reasonable, wine tastes nice at 10 degrees. But how sure are we that this is the right temperature?
We need more info, mainly modal share (number of values in the mode/all values)

For that, I put together in PostgreSQL a mode_count function, to allow you to find how many values you have in the mode, and possibly calculate a % of all values.
so now we have Mode:10;Modal_share 0.5 (7/14)
We can say with 50% certainty that the appropriate temperature to serve the wine at, based on user recommendations, is 10. Add in the high standard deviation, and you will easily detect the fact that there are strong outliers present in the data, giving you even more confidence towards your mode value.

The function from postgres manual for mode:

CREATE OR REPLACE FUNCTION _final_mode(anyarray)
RETURNS anyelement AS
$BODY$
SELECT a
FROM unnest($1) a
GROUP BY 1
ORDER BY COUNT(1) DESC, 1
LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;

<em id="__mceDel" style="line-height: 1.714285714; font-size: 1rem;"><em id="__mceDel">DROP AGGREGATE IF EXISTS mode(anyelement);</em></em>
CREATE AGGREGATE mode(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=_final_mode,
INITCOND='{}'
);

CREATE OR REPLACE FUNCTION _final_mode(anyarray)

RETURNS anyelement AS

$BODY$

SELECT a

FROM unnest($1) a

GROUP BY 1

ORDER BY COUNT(1) DESC, 1

LIMIT 1;

$BODY$

LANGUAGE 'sql' IMMUTABLE;

<em id="__mceDel" style="line-height: 1.714285714; font-size: 1rem;"><em id="__mceDel">DROP AGGREGATE IF EXISTS mode(anyelement);</em></em>

CREATE AGGREGATE mode(anyelement) (

SFUNC=array_append,

STYPE=anyarray,

FINALFUNC=_final_mode,

INITCOND='{}'

);

and the custom mode count function:

CREATE OR REPLACE FUNCTION _final_mode_count(anyarray)
RETURNS bigint AS
$BODY$
SELECT COUNT(*)
FROM unnest($1) a
GROUP BY a
ORDER BY COUNT(1) DESC, a
LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;

-- Tell Postgres how to use our aggregate
DROP AGGREGATE IF EXISTS mode_count(anyelement);
CREATE AGGREGATE mode_count(anyelement) (
SFUNC=array_append, --Function to call for each row. Just builds the array
STYPE=anyarray,
FINALFUNC=_final_mode_count, --Function to call after everything has been added to array
INITCOND='{}' --Initialize an empty array when starting
);

CREATE OR REPLACE FUNCTION _final_mode_count(anyarray)

RETURNS bigint AS

$BODY$

SELECT COUNT(*)

FROM unnest($1) a

GROUP BY a

ORDER BY COUNT(1) DESC, a

LIMIT 1;

$BODY$

LANGUAGE 'sql' IMMUTABLE;

-- Tell Postgres how to use our aggregate

DROP AGGREGATE IF EXISTS mode_count(anyelement);

CREATE AGGREGATE mode_count(anyelement) (

SFUNC=array_append, --Function to call for each row. Just builds the array

STYPE=anyarray,

FINALFUNC=_final_mode_count, --Function to call after everything has been added to array

INITCOND='{}' --Initialize an empty array when starting

);

They are aggregate functions, so call them as:

SELECT 
mode(some_value) AS modal_value,
mode_count(some_value) as count_at_mode,
mode_count(some_value)/(count(some_value)::float as modal_share
<span style="font-size: 0.857142857rem; line-height: 1.714285714;">FROM t;</span>

SELECT

mode(some_value) AS modal_value,

mode_count(some_value) as count_at_mode,

mode_count(some_value)/(count(some_value)::float as modal_share