Trong bài kiểm tra mã này, phải tạo 3 báo cáo kiểm kê bằng cách sử dụng truy vấn SQL (MySQL).
Một số thuật ngữ cần nắm:
Lược đồ cơ sở dữ liệu và dữ liệu mẫu được cung cấp dưới đây.
Lược đồ cơ sở dữ liệu và bảng dữ liệu
Cơ sở dữ liệu có 3 bảng chứa các chuyển động của hàng hóa và dữ liệu khách hàng.
Bảng 1: Bảng Movement
Là bảng quan trọng nhất. Bảng ghi lại lịch sử các kiện hàng di chuyển trong tất cả nhà kho, dữ liệu chứa định danh các hàng hóa, số lượng thay đổi, …
Bảng 2: Bảng Document
Có 2 loại document đó là đơn đặt hàng và đơn bán hàng.
Bảng 3: Bảng Customer
Bao gồm ID khách hàng và địa chỉ Email
Lược đồ SQL và mẫu dữ liệu
Tạo bảng và ghi dữ liệu vào các bảng trên mysql bằng đoạn code sau đây :
Tạo bảng
CREATE TABLE movement (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
document_id INT,
warehouse VARCHAR(100),
sku VARCHAR(100),
quantity INT,
balance INT,
created_at DATETIME
); CREATE TABLE document (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
type TEXT
); CREATE TABLE customer (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
contact VARCHAR(100)
); Chèn dữ liệu
INSERT INTO movement (document_id, sku, warehouse, quantity, balance, created_at)
VALUES
(1, 'iPhone', 'HK', 10, 10, '2021-1-1'),
(1, 'iPod', 'HK', 3, 3, '2021-1-1'),
(2, 'iPod', 'HK', -1, 2, '2021-1-2'),
(3, 'iPod', 'HK', -2, 0, '2021-1-3'),
(4, 'iPod', 'HK', 5, 5, '2021-2-1'),
(5, 'iMac', 'US', 5, 5, '2021-2-1'),
(5, 'iPhone', 'US', 2, 2, '2021-2-1'),
(6, 'iMac', 'HK', 5, 5, '2021-2-2'),
(7, 'iPod', 'HK', -4, 1, '2021-2-8'),
(8, 'iMac', 'HK', -1, 4, '2021-2-9'),
(9, 'iPhone', 'US', -1, 1, '2021-2-17'),
(10, 'iMac', 'HK', 1, 5, '2021-3-2'),
(11, 'iMac', 'HK', -1, 4, '2021-3-8'),
(11, 'iPod', 'HK', -1, 0, '2021-3-8'),
(12, 'iMac', 'US', 5, 10, '2021-3-10'); INSERT INTO document (id, customer_id, type)
VALUES
(1, NULL, "purchase"),
(2, 1, 'sales_order'),
(3, 2, 'sales_order'),
(4, NULL, 'purchase'),
(5, NULL, 'purchase'),
(6, NULL, 'purchase'),
(7, 3, 'sales_order'),
(8, 1, 'sales_order'),
(9, 4, 'sales_order'),
(10, NULL, 'purchase'),
(11, NULL, 'sales_order'),
(12, NULL, 'purchase'),
(13, NULL, 'purchase'); INSERT INTO customer (id, contact)
VALUES
(1, 'boris0407@gmail.com'),
(2, 'candywong@gmail.com'),
(3, 'flora2002@gmail.com'),
(4, 'glory@gmail.com'),
(5, 'himsonfong@gmail.com');Xem trước mẫu dữ liệu
Dưới đây là ảnh bảng dữ liệu sẽ trông như thế nào khi sử dụng join các bảng lại với nhau.
Câu hỏi đặt ra
Câu hỏi 1 : Bảng xếp hạng khách hàng
Xếp hạng khách hàng theo số lượng họ đã mua. Trong bảng bao gồm luôn cả địa chỉ email của khách ( hiển thị « guest » nếu khách không cung cấp hoặc bị null) và hiển thị thêm số lượng họ mua trong báo cáo.
Đoạn code thực thi :
/* Question 1: Bảng xếp hạng khách hàng */
select coalesce(c.contact,"guest") as "contanct", sum(abs(m.quantity)) as `sold`
from (customer c
right join document d on c.id = d.customer_id)
inner join movement m on d.id = m.document_id
where d.`type` like "sales_order"
group by coalesce(c.id,0), contanct
order by sold descKết quả hiển thị
Câu hỏi 2 : Bảng ghi tình trạng hàng tồn kho theo ngày chỉ định
Truy vấn số lượng hàng tồn cuối cùng được cập nhật trong nhà kho HK, trả về từng mã sku theo từng dòng. Đầu vào sẽ nhập một chuỗi text với 1 định dạng ngày như sau « yyyy-mm-dd 00:00:00 » và đầu ra sẽ là bảng theo dõi cho ngày hôm đó.
Đoạn code thực thi
/* Question 2: Bảng ghi tình trạng hàng tồn kho*/
create procedure get_hk_warehouse_balance_at_date (in `date` datetime)
begin
select warehouse, sku, balance from (
select warehouse, sku, balance,created_at,
row_number() over(partition by sku order by created_at desc) as rank_no
from movement
where warehouse like 'HK' and created_at < `date`
) as A
where rank_no = 1;
end;
/* run proc*/
call get_hk_warehouse_balance_at_date('2021-2-1 00:00:00');
call get_hk_warehouse_balance_at_date('2021-4-1 00:00:00');Kết quả hiển thị
“2021-2-1 00:00:00”
“2021-4-1 00:00:00”
Câu hỏi 3 : Tuổi của hàng tồn kho
Hiển thị tuổi của hàng tồn kho trong một thời gian nhất định và nhóm số lượng theo tuổi "0-30 ngày", "31-60 ngày", "61 - 90 ngày" và "90 ngày trở lên".
Tuổi của hàng hóa là số ngày sau khi hàng hóa được đưa vào kho.
Hàng hóa trong kho đến và đi. Khi khấu trừ hàng tồn kho, vui lòng tuân theo quy tắc "Nhập trước xuất trước", nghĩa là hàng cũ nhất sẽ được khấu trừ trước.
Đoạn code thực thi
Tạo procedure stored
create procedure statictis_inventory (in `date` datetime, ware_house varchar(5))
begin
create temporary table inventory_age_table(
sku varchar(15),
inventory_fifo int,
inventory_age int);
insert into inventory_age_table (sku, inventory_fifo, inventory_age)
with calculate_table as(
select warehouse, sku,quantity,created_at,
sum(quantity) over(partition by sku order by created_at desc) as inventory_upto,
sum(quantity) over(partition by sku order by created_at asc) as inventory_downto
from movement
where warehouse like ware_house and created_at < `date` and quantity > 0
), total_in_out as (
select warehouse, sku,
sum(case when quantity > 0 then quantity else 0 end) as total_in,
abs(sum(case when quantity < 0 then quantity else 0 end)) as total_out
from movement
group by warehouse, sku
)
select c.sku,
case
when c.inventory_downto <= t.total_out then 0
when c.inventory_upto > t.total_out then t.total_in - t.total_out - c.inventory_upto + c.quantity
else c.quantity
end as inventory_fifo,
datediff(`date`,c.created_at) as inventory_age
from calculate_table c
join total_in_out t
on c.warehouse = t.warehouse and c.sku = t.sku;
select sku,
sum(case when inventory_age <= 30 then inventory_fifo else 0 end) as "0-30 days",
sum(case when inventory_age <= 60 and inventory_age > 30 then inventory_fifo else 0 end) as "31-60 days",
sum(case when inventory_age <= 90 and inventory_age > 60 then inventory_fifo else 0 end) as "61-90 days",
sum(case when inventory_age > 90 then inventory_fifo else 0 end) as "90 days+"
from inventory_age_table
group by sku;
drop temporary table if exists inventory_age_table;
end;Gọi procedure
call statictis_inventory("2021-4-1 00:00:00","HK")Kết quả hiển thị
File code:
Comments