Fallbeipiele

Datenumfänge

SELECT 'customer' AS "table", count(*) AS anzahl FROM customer
union
SELECT 'sales' AS "table", count(*) AS anzahl FROM sales
union
SELECT 'store' AS "table", count(*) AS anzahl FROM store
union
SELECT 'product' AS "table", count(*) AS anzahl FROM product
union
SELECT 'timebyday' AS "table", count(*) AS anzahl FROM timebyday
ORDER BY anzahl;

Umsatz pro Jahr und Land

SELECT country, tyear, sum(unitsales)
FROM sales s
JOIN timebyday tbd ON tbd.timeid=s.timeid
JOIN customer c ON c.customerid=s.customerid
GROUP BY country, tyear;

Analyse Produkthierarchie 1

SELECT family, count(*)
FROM product
GROUP BY family
ORDER BY family;

SELECT department, count(*)
FROM product
GROUP BY department
ORDER BY department;

SELECT family, department, count(*)
FROM product
GROUP BY family, department
ORDER BY family, department;

Analyse Produkthierarchie 2

SELECT department
FROM
  (SELECT family, department
  FROM product
  GROUP BY family, department)
GROUP BY department
HAVING count(*)>1;

Kundenanalyse

SELECT education,
  CASE
    WHEN yearlyincome 
     in ('$10K - $30K', '$30K - $50K', '$50K - $70K')
    THEN 'L1'
    ELSE 'L2'
  END AS annual_income, count(*)
FROM customer
GROUP BY
  education,
  CASE
    WHEN yearlyincome 
     in ('$10K - $30K', '$30K - $50K', '$50K - $70K')
    THEN 'L1'
    ELSE 'L2'
  END
ORDER BY education, annual_income;

Top 3 aus 2014 - Vergleich mit 2015

WITH p3top AS (
  SELECT productid, sum(unitsales) AS sales2014
  FROM sales s JOIN timebyday t ON s.timeid=t.timeid
  WHERE tyear=2014
  GROUP BY productid
  ORDER BY sum(unitsales) desc fetch first 3 rows only
  )
SELECT
  s.productid, sales2014, sum(unitsales) AS sales2015,
  sum(unitsales) - sales2014 AS diff
FROM sales s 
     JOIN timebyday t ON s.timeid=t.timeid
     JOIN p3top ON s.productid=p3top.productid
WHERE tyear=2015
GROUP BY s.productid, sales2014
ORDER BY s.productid;

Prozentualer Anteil

WITH
  basecube AS (
    SELECT 
      family, tyear, tmonthnumber, 
      sum(unitsales) AS unitsales
    FROM sales s
         JOIN timebyday tbd ON tbd.timeid=s.timeid
         JOIN product p ON p.productid=s.productid
    GROUP BY tyear, tmonthnumber, family
)
SELECT 
  tyear, tmonthnumber, family, unitsales,
  sum(unitsales) OVER(PARTITION BY tyear, tmonthnumber) AS total_month,
  round(unitsales/sum(unitsales) OVER(PARTITION BY tyear, tmonthnumber), 3) * 100 
    AS percent_of_month,  
  sum(unitsales) OVER() AS total,
  round(unitsales/sum(unitsales) OVER(), 3) * 100 AS percent_of_total
FROM basecube
ORDER BY tyear, tmonthnumber, family;

Kumulative Summe

WITH
  basecube AS (
    SELECT 
      tyear, tmonthnumber, 
      sum(unitsales) AS unitsales
    FROM sales s
         JOIN timebyday tbd ON tbd.timeid=s.timeid
         JOIN product p ON p.productid=s.productid
    GROUP BY tyear, tmonthnumber
)
SELECT 
  tmonthnumber, unitsales,
  sum(unitsales) OVER(ORDER BY tmonthnumber) 
    AS cumulative
FROM basecube
WHERE tyear=2014
ORDER BY tmonthnumber;

Kumulative Summe pro Gruppe

WITH
  basecube AS (
    SELECT 
      family, tyear, tmonthnumber, 
      sum(unitsales) AS unitsales
    FROM sales s
         JOIN timebyday tbd ON tbd.timeid=s.timeid
         JOIN product p ON p.productid=s.productid
    GROUP BY family, tyear, tmonthnumber
)
SELECT 
  family, tmonthnumber, unitsales,
  sum(unitsales) 
    OVER(PARTITION BY family ORDER BY tmonthnumber) 
    AS cumulative
FROM basecube
WHERE tyear=2014
ORDER BY family, tmonthnumber;

Top 10 Produkte

WITH
  basecube AS (
    SELECT 
      pname, 
      sum(unitsales) AS unitsales
    FROM sales s
         JOIN product p ON p.productid=s.productid
    GROUP BY pname
)
SELECT 
  rank() OVER(ORDER BY unitsales desc) AS ranklevel,
  pname, 
  unitsales
FROM basecube
ORDER BY ranklevel
FETCH FIRST 10 ROWS ONLY;

ABC-Analyse

WITH
  basecube1 AS (
    SELECT 
      category, 
      sum(unitsales) AS unitsales
    FROM sales s
         JOIN product p ON p.productid=s.productid
    GROUP BY category
  ),
  basecube2 AS (
    SELECT
      category, 
      unitsales,
      sum(unitsales) OVER(ORDER BY unitsales desc) AS cum_unitsales,
      sum(unitsales) OVER() AS total_unitsales
    FROM basecube1
  )
SELECT 
  category, unitsales, cum_unitsales, total_unitsales,
  case 
    WHEN cum_unitsales < 0.5 * total_unitsales THEN 'A'
    WHEN cum_unitsales < 0.7 * total_unitsales THEN 'B'
    ELSE 'C'
  END AS cat    
FROM basecube2
ORDER BY unitsales desc;