Chi phí và Common Table Expressions (CTE)

Phân tích dữ liệu kinh doanh bằng SQL

Michel Semaan

Data Scientist

Chi phí

Số tiền công ty chi ra
Ví dụ
- Lương nhân viên
- Mua và bảo dưỡng đội xe giao hàng
- Chi phí món ăn

Các bảng cần dùng

meals

meal_id   eatery                    meal_price  meal_cost
--------  ------------------------  ----------  ---------
0         'Leaning Tower of Pizza'  4           2         
1         'Leaning Tower of Pizza'  3.5         1.25      
2         'Leaning Tower of Pizza'  4.5         1.75
...       ...                         ...         ...

stock

stocking_date  meal_id   stocked_quantity
-------------  --------  ----------------
2018-06-01     0         76               
2018-06-01     1         42               
2018-06-01     2         56
...            ...       ...

Tính chi phí

Truy vấn

SELECT
  meals.meal_id, 
  SUM(meal_cost * stocked_quantity) AS cost 
FROM meals
JOIN stock ON meals.meal_id = stock.meal_id
GROUP BY meals.meal_id
ORDER BY meals.cost DESC
LIMIT 3;

Kết quả

meal_id  cost      
  --------
9        3820.0
7        3592.5    
8        2332.5

Kết hợp doanh thu và chi phí như thế nào?

Lợi nhuận = Doanh thu - Chi phí
Đã có truy vấn riêng cho doanh thu và chi phí

Common Table Expressions (CTE)

Lưu kết quả truy vấn vào bảng tạm
Tham chiếu bảng tạm trong truy vấn tiếp theo

Truy vấn

WITH table_1 AS
  (SELECT ...
   FROM ...),
  table_2 AS
  (SELECT ...
   FROM   ...)

SELECT ...
FROM table_1
JOIN table_2 ON ...
...

CTE trong thực tế

Truy vấn

WITH costs_and_quantities AS (
 SELECT
   meals.meal_id,
   SUM(stocked_quantity) AS quantity,
   SUM(meal_cost * stocked_quantity) AS cost
 FROM meals
 JOIN stock ON meals.meal_id = stock.meal_id
 GROUP BY meals.meal_id)


SELECT 
  meal_id,
  quantity,
  cost
FROM costs_and_quantities
ORDER BY cost DESC
LIMIT 3;

Kết quả

meal_id  quantity  cost      
-------  --------  -------
5        3062      12248     
4        3142      10211.5   
6        2989      8219.75

Chi phí và Common Table Expressions (CTE)

Phân tích dữ liệu kinh doanh bằng SQL