Analytic Functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. The "over" in the select statement tells oracle that the function is a analytical function, not a group by function. The advantage to using analytics is that you can collect sums, counts, and a lot more
WINDOW FUNCTIONS
window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.
PARTITION BY and ORDER BY These are key to window functions. Not every window function uses PARTITION BY; we can also use ORDER BY or no statement at all depending on the query we want to run.
Note: You can’t use window functions and standard aggregations in the same query. More specifically, you can’t include window functions in a GROUP BY clause.
Eg. Boss says "get me number of items we have in stock grouped by brand" You say: "no problem"
SELECT BRAND ,COUNT(ITEM_ID) FROM ITEMS GROUP BY BRAND; Result:
+--------------+---------------+ | Brand | Count | +--------------+---------------+ | H&M | 50 | +--------------+---------------+ | Hugo Boss | 100 | +--------------+---------------+ | No brand | 22 | +--------------+---------------+ The boss says "Now get me a list of all items, with their brand AND number of items that the respective brand has" You may try:
SELECT ITEM_NR ,BRAND ,COUNT(ITEM_ID) FROM ITEMS GROUP BY BRAND; But you get:
ORA-00979: not a GROUP BY expression
This is where the OVER (PARTITION BY BRAND) comes in:
SELECT ITEM_NR ,BRAND ,COUNT(ITEM_ID) OVER (PARTITION BY BRAND) FROM ITEMS;
Which means:
COUNT(ITEM_ID) - get the number of items OVER - Over the set of rows (PARTITION BY BRAND) - that have the same brand And the result is:
+--------------+---------------+----------+ | Items | Brand | Count() | +--------------+---------------+----------+ | Item 1 | Hugo Boss | 100 | +--------------+---------------+----------+ | Item 2 | Hugo Boss | 100 | +--------------+---------------+----------+ | Item 3 | No brand | 22 | +--------------+---------------+----------+ | Item 4 | No brand | 22 | +--------------+---------------+----------+ | Item 5 | H&M | 50 | +--------------+---------------+----------+
EG. DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you're de-nomalising the emp table; you still return every record in the emp table.)
emp_no dept_no DEPT_COUNT 1 10 3 2 10 3 3 10 3 <- three because there are three "dept_no = 10" records 4 20 2 5 20 2 <- two because there are two "dept_no = 20" records
SELECT standard_amt_usd, DATE_TRUNC('month',occurred_at) AS month, -- To extract month in DateTime. SUM(standard_amt_usd) OVER (PARTITION BY DATE_TRUNC('month',occurred_at) ORDER BY occurred_at) AS running_total FROM demo.orders
-- Creating a Partitioned Running Total Using Window Functions
SELECT standard_amt_usd, DATE_TRUNC('year', occurred_at) as year, SUM(standard_amt_usd) OVER (PARTITION BY DATE_TRUNC('year', occurred_at) ORDER BY occurred_at) AS running_total FROM orders
ROW_NUMBER & RANK
-- Select the id, account_id, and total variable from the orders table, then create a column called total_rank that ranks this total amount of paper ordered (from highest to lowest) for each account using a partition.
SELECT id, account_id, total, RANK() OVER (PARTITION BY account_id ORDER BY total DESC) AS total_rank FROM orders
-- create a running total of standard_amt_usd (in the orders table) over order time with no date truncation. Your final table should have two columns: one with the amount being added for each new row, and a second with the running total.
SELECT standard_amt_usd,
SUM(standard_amt_usd)
OVER(ORDER BY occurred_at) AS running_total
FROM orders
-- Creating a Partitioned Running Total Using Window Functions... Create a running total of standard_amt_usd (in the orders table) over order time, but this time, date truncate occurred_at by year and partition by that same year-truncated occurred_at variable.
SELECT standard_amt_usd, DATE_TRUNC('year', occurred_at) as year, SUM(standard_amt_usd)
OVER(PARTITION BY DATE_TRUNC('year', occurred_at) ORDER BY occurred_at) AS running_total
FROM orders
ROW NUMBER DISPLAYS THE NUMBER OF A GIVEN ROWS WITHIN THE WINDOW YOU DEFINE. IT STARTS AT 1 AND NUMBERS ROW ACCORDING TO THE ORDER BY PART OF THE WINDOW STATEMENT. NOTE: ROW NUMBER DOES NOT REQUIRE TO SPECIFY A VARIABLE WITHIN THE PARENTHESES.
DENSE_RANK doesn't skip values after assigning several rows with the same rank. Eg you see how the previouse rows skips from rank 2 to rank 4, with dense_rank it goes from 2 to 3.
SELECT standard_amt_usd, DATE_TRUNC('year', occurred_at) as year, SUM(standard_amt_usd)
OVER(PARTITION BY DATE_TRUNC('year', occurred_at) ORDER BY occurred_at) AS running_total
FROM orders
RANK WHEN TWO LINES IN A ROW HAVE THE SAME VALUE FOR TOTAL, THEY ARE GIVEN THE SAME RANK WHEREAS ROWNUM GIVES DIFF NUM. EG
SELECT standard_amt_usd, DATE_TRUNC('year', occurred_at) as year,
RANK() OVER(PARTITION BY DATE_TRUNC('year', occurred_at) ORDER BY occurred_at) AS row_num
FROM orders
Entries with the same year are given the same rank and the ran column then skips some values to make up for it.
ROW_NUMBER & RANK -- Create a column called total_rank that ranks this total amount of paper ordered (from highest to lowest) for each account using a partition.
SELECT id, account_id, total,
RANK() OVER(PARTITION BY account_id ORDER BY total DESC) AS total_rank
FROM orders
id account_id total total_rank (Invalid URL) 4308 1001 1410 1 4309 1001 1405 2 4316 1001 1384 3 4317 1001 1347 4 4314 1001 1343 5 4307 1001 1321 6 4311 1001 1307 7..... 11 1001 137 26 3 1001 132 27 9 1001 129 28 17 1011 541 1 23 1021 578 1 21 1021 569 2 19 1021 558 3