Tworzenie raportów wykonawczych

Analiza danych biznesowych w SQL

Michel Semaan

Data Scientist

Czytelność

Daty: Używaj czytelnych formatów dat (August 2018, nie 2018-08-01)
Liczby: Zaokrąglaj do maksymalnie dwóch miejsc dziesiętnych (98.76, nie 98.761234)
Kształt tabeli: Przekształcaj długie tabele na szerokie, przestawiając według daty
Kolejność: Pamiętaj o sortowaniu!

Raport wykonawczy – zapytanie

Zapytanie

SELECT
  eatery,
  TO_CHAR(order_date, 'MM-Mon YYYY') AS delivr_month,
  COUNT(DISTINCT order_id) AS count_orders
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
WHERE order_date >= '2018-10-01'
GROUP BY eatery, delivr_month
ORDER BY eatery, delivr_month;

Wynik

eatery               delivr_month  count_orders
-----------------  ------------  ------------
Bean Me Up Scotty  10-Oct 2018   709         
Bean Me Up Scotty  11-Nov 2018   1143        
Bean Me Up Scotty  12-Dec 2018   2168        
Burgatorio         10-Oct 2018   679         
...                ...           ...

Raport wykonawczy (II)

WITH eatery_orders AS (
  SELECT
    eatery,
    TO_CHAR(order_date, 'MM-Mon YYYY') AS delivr_month,
    COUNT(DISTINCT order_id) AS count_orders
  FROM meals
  WHERE order_date >= '2018-10-01'
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY eatery, delivr_month)

SELECT
  eatery,
  delivr_month,
  RANK() OVER
    (PARTITION BY delivr_month
     ORDER BY count_orders DESC) :: INT AS orders_rank
FROM eatery_orders
ORDER BY eatery, delivr_month;

eatery               delivr_month  orders_rank
-----------------  ------------  -----------
Bean Me Up Scotty  10-Oct 2018     2          
Bean Me Up Scotty  11-Nov 2018     4          
Bean Me Up Scotty  12-Dec 2018   2          
Burgatorio         10-Oct 2018     4            
...                ...           ...

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  ...
$$) AS ct (eatery TEXT,
           "10-Oct 2018" INT,
           "11-Nov 2018" INT,
           "12-Dec 2018" INT)
ORDER BY eatery ASC;

Raport wykonawczy – wynik

eatery                  Q2 2018  Q3 2018  Q4 2018
----------------------  -------  -------  -------
The Moon Wok            1        1        1      
Bean Me Up Scotty       3        2        2      
Leaning Tower of Pizza  4        4        3      
Burgatorio              2        3        4      
Life of Pie             5        5        5

Tworzenie raportów wykonawczych

Analiza danych biznesowych w SQL