Overview of Numeric Functions¶
Here are some of the numeric functions we might use quite often.
abs– always return positive numberround– rounds off to specified precisionceil,floor– always return integer.greatestsum,avgmin,maxrandompow,sqrt
Some of the functions highlighted are aggregate functions, eg: sum, avg, min, max etc.
In [1]:
%%HTML
<iframe width="560" height="315" src="https://www.youtube.com/embed/8I7S1PuJEO0?rel=0&controls=1&showinfo=0" frameborder="0" allowfullscreen></iframe>
In [5]:
%load_ext sql
In [6]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [7]:
%%sql
SELECT abs(-10.5), abs(10)
1 rows affected.
Out[7]:
| abs | abs_1 |
|---|---|
| 10.5 | 10 |
In [8]:
%%sql
SELECT avg(order_item_subtotal) AS order_revenue_avg FROM order_items
WHERE order_item_order_id = 2
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[8]:
| order_revenue_avg |
|---|
| 193.32666666666668 |
In [9]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[9]:
| 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 |
In [10]:
%%sql
SELECT
round(10.58) rnd,
floor(10.58) flr,
ceil(10.58) cl
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[10]:
| rnd | flr | cl |
|---|---|---|
| 11 | 10 | 11 |
In [11]:
%%sql
SELECT
round(10.48, 1) rnd,
floor(10.48) flr,
ceil(10.48) cl
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[11]:
| rnd | flr | cl |
|---|---|---|
| 10.5 | 10 | 11 |
In [12]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[12]:
| order_revenue_avg |
|---|
| 193.33 |
In [13]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[13]:
| 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 |
In [14]:
%%sql
SELECT greatest(10, 11, 10.5)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[14]:
| greatest |
|---|
| 11 |
In [15]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[15]:
| 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 |
In [16]:
%sql SELECT random()
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[16]:
| random |
|---|
| 0.9150925202357989 |
In [17]:
%sql SELECT (random() * 100)::int + 1
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[17]:
| ?column? |
|---|
| 92 |
In [18]:
%sql SELECT pow(2, 2)::int, sqrt(4)
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[18]:
| pow | sqrt |
|---|---|
| 4 | 2.0 |