Overview of Numeric Functions

Topic
Materials

Here are some of the numeric functions we might use quite often.

  • abs - always return positive number

  • round - rounds off to specified precision

  • ceilfloor - always return integer.

  • greatest

  • sumavg

  • minmax

  • random

  • powsqrt

Some of the functions highlighted are aggregate functions, eg: sumavgminmax etc.

%load_ext sql

Copy to clipboard

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Copy to clipboard

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

%%sql

SELECT abs(-10.5), abs(10)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

abs abs_1
10.5 10
%%sql

SELECT avg(order_item_subtotal) AS order_revenue_avg FROM order_items
WHERE order_item_order_id = 2

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

order_revenue_avg
193.32666666666668
%%sql

SELECT order_item_order_id, 
    sum(order_item_subtotal) AS order_revenue_sum
FROM order_items
GROUP BY order_item_order_id
ORDER BY order_item_order_id
LIMIT 10

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

Copy to clipboard

order_item_order_id order_revenue_sum
1 299.98
2 579.98
4 699.85
5 1129.8600000000001
7 579.9200000000001
8 729.8399999999999
9 599.96
10 651.9200000000001
11 919.79
12 1299.8700000000001
%%sql

SELECT
    round(10.58) rnd,
    floor(10.58) flr,
    ceil(10.58) cl

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

rnd flr cl
11 10 11
%%sql

SELECT
    round(10.48, 1) rnd,
    floor(10.48) flr,
    ceil(10.48) cl

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

rnd flr cl
10.5 10 11
%%sql

SELECT round(avg(order_item_subtotal)::numeric, 2) AS order_revenue_avg 
FROM order_items
WHERE order_item_order_id = 2

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

order_revenue_avg
193.33
%%sql

SELECT order_item_order_id, 
    round(sum(order_item_subtotal)::numeric, 2) AS order_revenue_avg 
FROM order_items
GROUP BY order_item_order_id
LIMIT 10

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

Copy to clipboard

order_item_order_id order_revenue_avg
1 299.98
2 579.98
4 699.85
5 1129.86
7 579.92
8 729.84
9 599.96
10 651.92
11 919.79
12 1299.87
%%sql

SELECT greatest(10, 11, 10.5)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

greatest
11
%%sql

SELECT order_item_order_id, 
    round(sum(order_item_subtotal)::numeric, 2) AS order_revenue_sum,
    min(order_item_subtotal) AS order_item_subtotal_min,
    max(order_item_subtotal) AS order_item_subtotal_max 
FROM order_items
GROUP BY order_item_order_id
LIMIT 10

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

Copy to clipboard

order_item_order_id order_revenue_sum order_item_subtotal_min order_item_subtotal_max
1 299.98 299.98 299.98
2 579.98 129.99 250.0
4 699.85 49.98 299.95
5 1129.86 99.96 299.98
7 579.92 79.95 299.98
8 729.84 50.0 299.95
9 599.96 199.98 199.99
10 651.92 21.99 199.99
11 919.79 49.98 399.96
12 1299.87 100.0 499.95
%sql SELECT random()

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

random
0.03222073158160299
%sql SELECT (random() * 100)::int + 1

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

?column?
98
%sql SELECT pow(2, 2)::int, sqrt(4)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

pow sqrt
4 2.0