Overview of Numeric FunctionsΒΆ

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

  • abs - always return positive number

  • round - rounds off to specified precision

  • ceil, floor - always return integer.

  • greatest

  • sum, avg

  • min, max

  • random

  • pow, sqrt

Some of the functions highlighted are aggregate functions, eg: sum, avg, min, max etc.

%load_ext sql

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

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

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

%%sql

SELECT abs(-10.5), abs(10)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
rnd flr cl
11 10 11 
%%sql

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
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)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
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()

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
random
0.03222073158160299 
%sql SELECT (random() * 100)::int + 1

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
?column?
98 
%sql SELECT pow(2, 2)::int, sqrt(4)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
pow sqrt
4 2.0
Date Manipulation Functions Data Type Conversion