Tutorial Window Function con DuckDB (2ª parte)¶

pip install duckdb

import duckdb

%load_ext sql
%config SqlMagic.autopandas = True
%config SqlMagic.displaycon = False

conn = duckdb.connect('BBDD/WindowFunction.duckdb')
%sql conn --alias duckdb

# con.sql("show databases")
conn.sql("show tables")

┌─────────────┐
│    name     │
│   varchar   │
├─────────────┤
│ order_items │
│ orders      │
│ products    │
└─────────────┘

%sql SELECT model_year, AVG(list_price) avg_price\
     FROM products\
     GROUP BY model_year

%sql SELECT model_year, product_name, list_price,\
     AVG(list_price) OVER (PARTITION BY model_year) avg_price\
     FROM products\
     ORDER BY product_name

%%sql
  SELECT year(order_date) y, COUNT(DISTINCT order_id) num_orders
  FROM orders
  GROUP BY year(order_date)

%%sql
/* Rank price from LOW->HIGH  */
  SELECT product_name, list_price,
         RANK() OVER (ORDER BY list_price ASC) rank
  FROM products

%%sql
/* Rank price from HIGH->LOW  */
  SELECT product_name, list_price,
         RANK() OVER (ORDER BY list_price DESC) rank
  FROM products

%%sql
/* Find the difference of price from cheapest alternative */
SELECT
  product_name,
  list_price,
  FIRST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) cheapest_price,
  list_price - FIRST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) diff
FROM products

%%sql
/* Find the difference of price from the priciest alternative */
SELECT
   product_name,
   list_price,
   LAST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) highest_price,
   LAST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) - list_price diff
FROM products

%%sql
/* Find number of orders in a year */
WITH yearly_orders AS (
   SELECT
    year(order_date) y,
    COUNT(DISTINCT order_id) num_orders
    FROM orders
    GROUP BY year(order_date)
)

/* Compare this year's sales to last year's */
SELECT
*,
LAG(num_orders) OVER (ORDER BY y) last_year_order,
LAG(num_orders) OVER (ORDER BY y) - num_orders diff_from_last_year
FROM yearly_orders

%%sql
/* Find number of orders in a year */
WITH yearly_orders AS (
   SELECT
    year(order_date) y,
    COUNT(DISTINCT order_id) num_orders
    FROM orders
    GROUP BY year(order_date)
)

/* Compare number of years compared to next year */
SELECT *,
   LEAD(num_orders) OVER (ORDER BY y) next_year_order,
   LEAD(num_orders) OVER (ORDER BY y) - num_orders diff_from_next_year
FROM yearly_orders

%%sql
/* Rank all products by price */
SELECT
product_name,
list_price,
ROW_NUMBER() OVER (ORDER BY list_price) row_num,
DENSE_RANK() OVER (ORDER BY list_price) dense_rank,
RANK() OVER (ORDER BY list_price) rank,
PERCENT_RANK() OVER (ORDER BY list_price) pct_rank,
NTILE(75) OVER (ORDER BY list_price) ntile,
CUME_DIST() OVER (ORDER BY list_price) cume_dist
FROM products

%%sql
SELECT
   order_id,
   product_id,
   discount,
   AVG(discount) OVER (PARTITION BY product_id) avg_discount,
   MIN(discount) OVER (PARTITION BY product_id) min_discount,
   MAX(discount) OVER (PARTITION BY product_id) max_discount
FROM order_items
WHERE order_id != 1
ORDER BY order_id

# Explicitly close the connection
conn.close()