Tagi: WITH

Zadanie 24.

Korzystając z widoku utworzonego w zadaniu 23, podsumujmy sprzedaż według produktów.

a) które produkty sprzedawały się najlepiej – ranking top 10

s24a

 

 

 

 

b) który produkt sprzedawał się najlepiej w konkretnych latach – ranking top 5 produktów na dany rok

s24b

 

 

 

 

 

c) który produkt sprzedawał się najlepiej w każdej kategorii?

s24c

 

 

 

 

 

d) który produkt sprzedawał się najlepiej w pierwszych trzech miesiącach lat 2006-2007? Ranking top 3 produktów.

s24d

 

 

 

 

Schemat: OE, Tabela: Product_Information oraz widok utworzony w zadaniu 23, Kolumny: product_id, quantity, ord_year, ord_month, category_id, Wynik: a) 10 rows b) 20 rows c) 17 rows d) 15 rows

Rozwiązanie

a) SELECT product_id, SUM(quantity) AS quantity, dense_rank() over(order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id
ORDER BY quantity DESC
FETCH FIRST 10 rows WITH ties;

b) WITH Subquery AS (
SELECT ord_year, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id, ord_year
)

SELECT * FROM Subquery
WHERE rank <= 5
ORDER BY ord_year, rank;

c) WITH Subquery AS (
SELECT pi.category_id, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by pi.category_id order by SUM(quantity) DESC) AS rank
FROM my_order_view inner join product_information pi using(product_id)
GROUP BY pi.category_id, product_id
)

SELECT category_id, product_id, quantity
FROM Subquery
WHERE rank = 1;

d) WITH Subquery AS (
SELECT ord_year, ord_month, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year, ord_month order by SUM(quantity) DESC) AS rank
FROM my_order_view
WHERE ord_month in(1,2,3) and ord_year in(2006, 2007)
GROUP BY ord_year, ord_month, product_id
)

SELECT * FROM Subquery
WHERE rank <= 3;

[collapse]

Zadanie 22.

Oblicz łączną sumę tych zamówień, których wartość wynosi powyżej 35000. Zadanie rozwiąż na kilka sposobów.

Schemat: OE, Tabela: Orders, Kolumny: order_total, Wynik: 1

s22

 

Rozwiązanie

1. Wersja najprostsza:

SELECT SUM(order_total) AS wynik
FROM orders
WHERE order_total > 35000;

2. Wersja z CASE:

SELECT SUM(
CASE
WHEN order_total < 35000
THEN 0
ELSE order_total
END) AS wynik
FROM orders;

3. Z subquery factoring:

WITH subquery AS (
SELECT order_total
FROM orders
WHERE order_total > 35000
)
SELECT SUM(order_total) AS wynik FROM subquery;

4. Z podzapytaniem w klauzuli FROM:

SELECT SUM(order_total) AS wynik
FROM
( SELECT order_total
FROM orders
WHERE order_total > 35000
) Tab;

[collapse]

Zadanie 7.

Stwórz kwerendę, która zliczy niepowtarzające się nazwiska klientów. W wyniku chcemy uzyskać konkretną liczbę. Zadanie rozwiąż na co najmniej dwa sposoby.

Schemat: OE, Tabela: Customers, Kolumny: cust_last_name, Wynik: 176 rows

s7

 

Rozwiązanie

1. Z klauzulą WITH:

WITH Lname AS
(
SELECT DISTINCT cust_last_name
FROM customers
)

SELECT COUNT(*) as LAST_NAME_COUNT FROM Lname;

2. Z podzapytaniem w klauzuli WHERE:

SELECT COUNT(*) as LAST_NAME_COUNT
FROM (
SELECT DISTINCT cust_last_name
FROM customers
);

3. Z grupowaniem:

SELECT COUNT(DISTINCT cust_last_name) AS last_name_count, 1
FROM customers
GROUP BY 1;

[collapse]

Zadanie 2.

Czy więcej zamówień było złożonych przed godziną 14, czy później?

Schemat: OE, Tabela: Orders, Kolumny: order_id, order_date, Wynik: 2 rows

s2

 

 

 

Rozwiązanie

WITH

Przed14 AS (
SELECT COUNT(order_id) as Zamowienia, EXTRACT(hour from order_date) as Godzina
FROM orders
WHERE EXTRACT(hour from order_date) < 14
GROUP BY EXTRACT(hour from order_date)
),

Po14 AS (
SELECT COUNT(order_id) as Zamowienia, EXTRACT(hour from order_date) as Godzina
FROM orders
WHERE EXTRACT(hour from order_date) >= 14
GROUP BY EXTRACT(hour from order_date)
)

SELECT 'Zamowienia przed godz 14: '||SUM(Zamowienia) as wynik FROM Przed14
UNION
SELECT 'Zamowienia po godz 14: '||SUM(Zamowienia) FROM Po14;

[collapse]