Обсуждение: [GENERAL] Nice to have features: Percentage function

On 04/15/2017 10:47 PM, Ron Ben wrote:
> Hi,
> I'm always finiding myself writing many varations of functions to
> calculate percentage.
> I think it would be nice if postgresql would have build in functions for
> that.

If you have already written the functions, why not just use them?

To make them available across a cluster install them in a template
database(www.postgresql.org/docs/9.6/static/manage-ag-templatedbs.html)
and create your new databases using that. The default template is the
template1 database, but you can create your own.

> I think the major functionality is something like the 3 ooptions here:
> https://percentagecalculator.net/
>
> It may help to keep code simple and clean and it seem like something
> simple to implement.
>
> If you think it's a good idea it would be nice if someone can implement
> this.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/15/2017 10:47 PM, Ron Ben wrote:
> Hi,
> I'm always finiding myself writing many varations of functions to
> calculate percentage.
> I think it would be nice if postgresql would have build in functions for
> that.
> I think the major functionality is something like the 3 ooptions here:
> https://percentagecalculator.net/
>
> It may help to keep code simple and clean and it seem like something
> simple to implement.

Plan B, CREATE your own extension. What follows is my first attempt at
creating an extension and the functions included are simple placeholders
more then anything else:

File name: calc_percents--1.0.sql

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION calc_percents" to load this file. \quit

CREATE OR REPLACE FUNCTION public.percent_of(val1 numeric, val2 numeric)
  RETURNS numeric
  LANGUAGE sql
AS $function$
   select (val1 / 100) *  val2;
$function$
;

CREATE OR REPLACE FUNCTION public.what_percent(val1 numeric, val2 numeric)
  RETURNS numeric
  LANGUAGE sql
AS $function$
   SELECT (val1 / val2) * 100;
$function$
;

CREATE OR REPLACE FUNCTION public.percent_diff(val1 numeric, val2 numeric)
  RETURNS numeric
  LANGUAGE sql
AS $function$
   select (val2 - val1) / val1 * 100;
$function$
;

File name: calc_percents.control

# calc_percents extension
comment = 'Functions for calculating percentages'
default_version = '1.0'
relocatable = true


Install the above in $SHARE/extension, in my case
/usr/local/pgsql/share/extension/

Then:

test=# create extension calc_percents;
CREATE EXTENSION
test=# \df percent_of
                               List of functions
  Schema |    Name    | Result data type |    Argument data types     |
Type
--------+------------+------------------+----------------------------+--------
  public | percent_of | numeric          | val1 numeric, val2 numeric |
normal

test=# select * from round(percent_of(10, 100), 2) ;
  round
-------
  10.00


test=# \df percent_diff
                                List of functions
  Schema |     Name     | Result data type |    Argument data types
|  Type
--------+--------------+------------------+----------------------------+--------
  public | percent_diff | numeric          | val1 numeric, val2 numeric
| normal


test=# select * from round(percent_diff(100, 109), 2) ;
  round
-------
   9.00
(1 row)


test=# \df what_percent
                                List of functions
  Schema |     Name     | Result data type |    Argument data types
|  Type
--------+--------------+------------------+----------------------------+--------
  public | what_percent | numeric          | val1 numeric, val2 numeric
| normal
(1 row)

test=# select * from round(what_percent(10, 109), 2) ;
  round
-------
   9.17


>
> If you think it's a good idea it would be nice if someone can implement
> this.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/16/2017 09:37 AM, Melvin Davidson wrote:
>
>
> On Sun, Apr 16, 2017 at 12:23 PM, Adrian Klaver

>
>
> *Or, you could just as easily compute inline in SQL:
>
> SELECT datname,
>        pg_size_pretty(pg_database_size(datname))as size_pretty,
>        pg_database_size(datname) as size,
>        (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
>           FROM pg_database)  AS total,
>       ((pg_database_size(datname) / (SELECT SUM(
> pg_database_size(datname))
>                                        FROM pg_database) ) *
> 100)::numeric(6,3) AS pct
>   FROM pg_database
>   ORDER BY datname;*

Yeah, that is doable but I believe the OP is looking for generic
functions that eliminate the need to write out the math for each query.
A quick and dirty example:

test=# create table percent_test(id int, subtotal numeric, sales_tax
numeric);
CREATE TABLE
test=# insert into percent_test values (1, 128, 8.7), (2, 90, 8.5), (3,
256.35, 8.7), (4, 25.50, 8.5);
INSERT 0 4

test=# select id, subtotal, sales_tax as sales_tax_rate,
percent_of(sales_tax, subtotal)::numeric(7, 2) as tax from percent_test;
  id | subtotal | sales_tax_rate |  tax
----+----------+----------------+-------
   1 |      128 |            8.7 | 11.14
   2 |       90 |            8.5 |  7.65
   3 |   256.35 |            8.7 | 22.30
   4 |    25.50 |            8.5 |  2.17

>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/16/2017 02:33 PM, Michael Nolan wrote:
> I also have some pre-defined percentage functions, they check the
> denominator and return null if it is zero, to avoid 'divide by zero'
> errors.

Are they available somewhere?

My previous examples where more proof of concept then complete.


> --
> Mike Nolan
>


--
Adrian Klaver
adrian.klaver@aklaver.com