Truy vấn CSDL?

[LearnExcel]

Xin chào mọi người, tôi đang tìm cách truy vấn tin như sau, mong được mọi người giúp đỡ.

Có CSDL như sau
Ngày Ca Mã_NV Mã_SF Sản_lượng
01/08/2008 1 A2 M54 11
05/08/2008 2 A2 M71 97
09/08/2008 1 A1 M48 60
09/08/2008 1 A6 M63 84
12/08/2008 1 A2 M54 58
12/08/2008 1 A9 M67 58
13/08/2008 3 A1 M52 53
14/08/2008 3 A3 M98 76
16/08/2008 2 A12 M55 61
25/08/2008 1 A3 M87 74

Một ngày có 3 ca làm việc, mỗi ca có nhiều NV, mỗi NV làm nhiều mã SF.

Nay cần có số liệu trung bình sản lượng của mỗi NV theo ca thì dùng công thức trong Excel như thế nào? hay vấn tin SQL thì cú pháp ra sao? Xin cảm ơn trước!

 

[dat2007]

Nếu dữ liệu trong Excel thì anh dùng PivotTable là được

 

[loihua_tutraitim]

Csdl

Hì! Tui mới học về cơ sở dữ liệu nên làm thế này không biết là có đúng không? Mong các bạn đọc xong cho ý kiến nha!
các bước là Data/pivot table and pivot chart report sau đó chọn next tiếp thui!!!
Được thày giáo dạy thế
Ở phần ca bạn có thể tính trung bình cho từng ca
Có thể dùng hàm sum, average, sumproduct,...
Bạn thử llàm nha

 

[Nguyễn Duy Tuân]

Bác tham khảo cách làm bằng SQL:
SELECT ca, nhan_vien, AVG(slg) FROM dulieu GROUP BY ca, nhan_vien

 

[LearnExcel]

@ Dat2007 & loihua_tutraitim

Cảm ơn hai bạn về gợi ý Pivot Table

Tôi đã thử dùng tr][cs khi post bài nhưng kết quả vẫn chưa đúng.

Ví dụ như sau:

Ngày Ca Mã_NV Mã_SF Sản_lượng
14/10/2008 2 A1 M33 98
07/09/2008 1 A1 M14 99
09/08/2008 1 A1 M48 60
13/08/2008 3 A1 M52 53
3 310

Thì kết quả là NV A1 có số ca là 3, tổng Sản lượng là 310, Trung bình SL phải là 310/3, còn kết quả của Pivot Table trả về là 310/4

@ TuanVNUNI [QUOTE ]Bác tham khảo cách làm bằng SQL:
SELECT ca, nhan_vien, AVG(slg) FROM dulieu GROUP BY ca, nhan_vien[/QUOTE]

Truy vấn SQL theo cú pháp này cũng vẫn giống như Pivot. Tôi vẫn nhớ hình như phải có thêm từ khóa DISTINCT vào trước ca nữa thì mới được tuy nhiên vẫn chưa đạt kq như mong đợi.

 

[Nguyễn Duy Tuân]

Công thức đúng đây

Thì kết quả là NV A1 có số ca là 3, tổng Sản lượng là 310, Trung bình SL phải là 310/3, còn kết quả của Pivot Table trả về là 310/4

@ TuanVNUNI [QUOTE ]Bác tham khảo cách làm bằng SQL:
SELECT ca, nhan_vien, AVG(slg) FROM dulieu GROUP BY ca, nhan_vien

Oh, tôi hiểu sai yêu cầu của bác. Bài này chỉ có thể làm bằng SQL chứ Pivot e rằng không thể. Bài này là một trong những bài khó của SQL (với tôi).

Công thức đúng đây:

SELECT CANV.MA_NV, SLGNV.sSLG/CANV.cCA FROM (SELECT MA_NV, COUNT(*) AS cCA FROM (SELECT DULIEU.MA_NV FROM DULIEU GROUP BY DULIEU.MA_NV, DULIEU.CA) GROUP BY MA_NV) AS CANV INNER JOIN (SELECT MA_NV, SUM(SLG) AS sSLG FROM DULIEU GROUP BY MA_NV) AS SLGNV ON CANV.MA_NV = SLGNV.MA_NV

File tôi gửi chạy SQL bởi A-Tools, bác có thể dùng ADO để thực hiện.

 

[hoangvuluan]

Tính trung bình theo ca

Ngày Ca Mã_NV Mã_SF Sản_lượng
14/10/2008 2 A1 M33 98
07/09/2008 1 A1 M14 99
09/08/2008 1 A1 M48 60
13/08/2008 3 A1 M52 53
3 310

[/table]

Thì kết quả là NV A1 có số ca là 3, tổng Sản lượng là 310, Trung bình SL phải là 310/3, còn kết quả của Pivot Table trả về là 310/4

Tôi thắc mắc ở chỗ: nếu vì 1 lý do nào đó mà có người trong 1 tháng chỉ làm 1 loại ca, ví dụ ca 1, mỗi ngày làm ước chừng 70 sp, thế thì trung bình SL chính là tổng số SL đã làm ra??? vì chỉ tính có 1 loại ca.

 

[LearnExcel]

Tôi thắc mắc ở chỗ: nếu vì 1 lý do nào đó mà có người trong 1 tháng chỉ làm 1 loại ca, ví dụ ca 1, mỗi ngày làm ước chừng 70 sp, thế thì trung bình SL chính là tổng số SL đã làm ra??? vì chỉ tính có 1 loại ca.

Vấn đề là nếu trong 1 ca làm 1 loại SF= 70 thì TB đúng là 70 nhưng làm 2 hay 3 loại SF, mỗi SF = 70 thì chắc chắn TB > 70
trình tự cũ tôi vẫn làm là:
tính 1 bảng phụ có mã NV, tổng Sản lượng trên 1 ca rồi sau đó lấy trung bình của bảng này, nhóm theo mã NV.

Nay cần 1 giải pháp "1 cửa" để CCTTHC cho gọn.

Tôi đang thử cú pháp của TuanVNUI và sẽ thông báo kết quả sớm.

Cảm ơn mọi người đã thảo luận và góp ý.

 

[LearnExcel]

Oh, tôi hiểu sai yêu cầu của bác. Bài này chỉ có thể làm bằng SQL chứ Pivot e rằng không thể. Bài này là một trong những bài khó của SQL (với tôi).

Công thức đúng đây:



File tôi gửi chạy SQL bởi A-Tools, bác có thể dùng ADO để thực hiện.

Dear TuanVNUI

Kết quả vẫn chưa ổn lắm, vì tôi biét chắc chắn Kq trả về chỉ trong khoảng 3000 - 15000 mà thôi.

Tôi có gửi kèm File Excel và Access.

Nếu có thời gian, mong bạn xem lại giúp.

Quả thật là cú pháp SQL này thì tôi có nằm mơ cũng không nghĩ ra được, , bái phục!

A-tool cũng rất hay!

 

[hai2hai]

Đây là vấn đề rất hay (và đúng như Tuân nói là tương đối khó)

Ví dụ về Crosstab query:

MONTHLY SALES SQL QUERY
=======================

SELECT year(tb_T_Transaction.TransDate) AS [TransYear], month(tb_T_Transaction.TransDate) AS [TransMonth], sum(tb_T_Shipment.TotalAmount) AS Sales
FROM tb_T_Transaction
INNER JOIN tb_T_Shipment ON tb_T_Transaction.ID = tb_T_Shipment.ID
GROUP BY year(tb_T_Transaction.TransDate), month(tb_T_Transaction.TransDate)
ORDER BY year(tb_T_Transaction.TransDate), month(tb_T_Transaction.TransDate);


QUARTERLY SALES SQL QUERY:
==========================

SELECT year(tb_T_Transaction.TransDate) AS [TransYear], DatePart(q,tb_T_Transaction.TransDate) AS Quarter, sum(tb_T_Shipment.TotalAmount) AS Sales
FROM tb_T_Transaction
INNER JOIN tb_T_Shipment ON tb_T_Transaction.ID = tb_T_Shipment.ID
GROUP BY year(tb_T_Transaction.TransDate), DatePart(q,tb_T_Transaction.TransDate);

Tham khảo thêm: http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/

Hoặc có 1 bài cũng hay ở đây: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23999654.html

 

[Nguyễn Duy Tuân]

Dear TuanVNUI

Kết quả vẫn chưa ổn lắm, vì tôi biét chắc chắn Kq trả về chỉ trong khoảng 3000 - 15000 mà thôi.

Tôi có gửi kèm File Excel và Access.

Nếu có thời gian, mong bạn xem lại giúp.

Quả thật là cú pháp SQL này thì tôi có nằm mơ cũng không nghĩ ra được, , bái phục!

A-tool cũng rất hay!

Bác giải thích rõ hơn về yêu cầu tính toán được không? Như thế nào là đúng, sai ?

 

[LearnExcel]

Bác giải thích rõ hơn về yêu cầu tính toán được không? Như thế nào là đúng, sai ?

Tôi biết chắc chắn 1 ca , mỗi NV sẽ làm từ 1-4 mã SF, tổng SL mỗi ca không bao giờ quá 15000, như vậy SL trung bình ca không bao giờ vượt quá 15000 được

MA_NV Expr1001
V0255 14,698
V0322 7,948
V0492 33,083
V0496 315,138
V0508 279,141
V0515 244,352
V0521 23,191
V0525 289,833
V0843 273,060

v1144 17,387
v1240 9,656
v1241 9,457
v1243 9,701
v1715 8,066
v1719 8,046
v1723 8,301

Kết quả trả về bảng 1 rất không hợp lý.

Bảng 2 NV từ V1240 trở xuống có vẻ hợp lý nhưng NV V1144 thì không đúng. Tôi cũng đã KT lại dữ liệu gốc thì vẫn không thấy sai.

 

[ptm0412]

Tôi không hiểu chỗ này:

Đối với mã NV V0843:

Sum = 819.180
Count = 149
Average = 819.180/ 149 = 5.497,85
Ca làm việc (theo yêu cầu đề bài) = 3
Trung bình theo ca = 819.180/ 3 = 273.060

Vậy thì sai chỗ nào?

Theo tôi, còn 1 tham số ngày chưa được tính vào, thí dụ 1 nhân viên làm ca nào không cần biết, nhưng nhiều ngày thì phải chia nhiều hơn, tại sao lại chỉ chia cho tối đa là 3?

Hoặc là: Thêm tham số Count(ca), tính trung bình từng ca riêng? (có ý nghĩa về mặt thống kê 1 tí)

 

[LearnExcel]

Tôi không hiểu chỗ này:

Đối với mã NV V0843:

Sum = 819.180
Count = 149

Count ở đây theo Pivot là số toàn bộ số bản ghi, thực ra NV này làm có 93 ca và TB = 8808 mới đúng. Ở đây tôi làm theo kiểu "nhà quê" phải dùng 1 bảng Pivot cho toàn bộ, trích lục mỗi NV V0843 ra bảng riêng, count số ca theo ngày, counta tiếp thì mới được số ca đi trong từng ấy ngày (vì có ngày đi làm 2 ca). Lấy số tổng chia số ca.

 

[Nguyễn Duy Tuân]

Ví dụ như sau:

Ngày Ca Mã_NV Mã_SF Sản_lượng
14/10/2008 2 A1 M33 98
07/09/2008 1 A1 M14 99
09/08/2008 1 A1 M48 60
13/08/2008 3 A1 M52 53
3 310

Thì kết quả là NV A1 có số ca là 3, tổng Sản lượng là 310, Trung bình SL phải là 310/3, còn kết quả của Pivot Table trả về là 310/4

Bác giải thích cho mọi người vì sao NV A1 lại có số ca là 3 với 4 ngày khác nhau ? Theo tôi hiểu thì số ca phải là 4 mới đúng?

Mất 1h giờ cố làm giúp bác ai ngờ hình như lại hiểu sai yêu cầu, thế là lại công cốc rồi!
Muốn thử sức và giúp bác nhưng phải đợi bác làm rõ yêu cầu hơn mới làm tiếp được.

Thực sự bài của bác đã khó rồi, cách đặt yêu cầu của bác cũng khó không kém

 

[dat2007]

Bác giải thích cho mọi người vì sao NV A lại có số ca là 3 với 4 ngày khác nhau ? Theo tôi hiểu thì số ca phải là 4 mới đúng?

Bác LearnEx nhà ta có lẽ là người làm công tác quản trị sản xuất Tuân ạ (chắc là quản đốc)

Theo mình hiểu thì một ngày làm việc có 3 ca, từ Ca1, Ca2 đến Ca3. Như vậy ngày đầu tiên nhân viên A sẽ đi làm việc ở Ca2, 2 ngày tiếp theo nhân viên này làm việc ở Ca1, ngày tiếp theo nhân viên này làm việc ở Ca3 (gọi là đảo ca trong sản xuất ấy mà).
Như vậy số ca mà nhân viên này làm việc trong các ngày thống kê được là 3 ca: gồm ca1, ca2 và ca3 với số ca 1 là 2, số ca 2 là 1, số ca 3 là 1.
Con số sản lượng trung bình của nhân viên ở đây chính là sản lượng bình quân của mỗi ca mà nhân viên này thực hiện được trong tháng

 

[Nguyễn Duy Tuân]

Mình hiểu là Slg trung bình mỗi ca của NV A = Tổng Slg (A) / Số ca (A)

Vấn đề đang thắc mắc ở Số ca (A). Như Tuấn giải thích thì một ngày NV A chỉ có làm 1 ca ? Mình biết NV A có thể làm tối đa 3 ca/ ngày. Trong bảng dữ liệu, NV A có làm trong 1 ca có thể có thể được các Slg SP khác nhau, khi đó cũng chỉ tính là 1 ca.

Mình đang định thống kê về số ca theo ngày, ví dụ số liệu NV A:
1/12/08 ca1 2 SPA
2/12/08 ca2 4 SPB
2/12/08 ca2 2 SPA
2/12/08 ca3 2 SPC
3/12/08 ca3 2 SPB

SLg trung bình mỗi ca = 12/4
Ngày 2/12/08 được tính có 2 ca.

Không biết hiểu thế đã đúng ý người hỏi chưa?

 

[ptm0412]

Nếu dữ liệu có dạng như sau thì Ptm có thể thực hiện:

Mỗi ngày có thể có nhiều record cho 1 maNV, nhưng chỉ khác Ma_SF, cùng 1 ca: nghĩa là chỉ làm 1 ca nhưng ra nhiều loại SP, thí dụ mã NV V0843: 93 ca, tổng 819.180, TB 8808

(Kiểu như Dat2007 nói). File đính kèm là theo kiểu này, không có trường hợp 1 ngày 2 hay 3 ca.

Còn nếu như Tuân nói: 1 ngày có thể làm 2 hoặc 3 ca, mỗi ca có thể 1 hoặc nhiều loại sản phẩm, thì phức tạp quá. Lý do là cấu trúc dữ liệu không tốt. Nếu quả thực như vậy thì dữ liệu nên có cấu trúc như sau, mới không gây khó khăn khi tính toán:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

1​

​

|

Ngay​

​

|

MaNV​

​

|

MaSp​

​

|

SlCa1​

​

|

SlCa2​

​

|

SlCa3​

​

|

2​

​

|

01/12/08​

|NV01|SPA|

2​

| | |

3​

​

|

02/12/08​

|NV01|SPB| |

4​

| |

4​

​

|

02/12/08​

|NV01|SPA| |

2​

| |

5​

​

|

02/12/08​

|NV01|SPC| | |

2​

|

6​

​

|

03/12/08​

|NV01|SPB| | |

2​

|

Tuy nhiên, không phải là khó khăn thì không thể làm!

 

[ptm0412]

Đã làm xong:
Có 9 trường hợp 1 NV làm 1 ngày 2 ca:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

1​

​

|

Ngay​

​

|

Ca​

​

|

MA_NV​

​

|

Ma_SF​

​

|

SLG​

​

|

2​

​

|

01/11/2008​

|

3​

| |

4.318​

|

11101​

|

3​

​

|

30/11/2008​

|

3​

|V1241|

765​

|

1591​

|

4​

​

|

13/12/2008​

|

3​

|V1262|

123​

|

2201​

|

5​

​

|

18/08/2008​

|

3​

|V1718|

4.533​

|

1300​

|

6​

​

|

11/07/2008​

|

2​

|V1721|

4.711​

|

9869​

|

7​

​

|

10/08/2008​

|

3​

|V1890|

522​

|

9199​

|

8​

​

|

08/09/2008​

|

3​

|V1897|

4.311​

|

4403​

|

9​

​

|

21/10/2008​

|

3​

|V1939|

439​

|

7922​

|

10​

​

|

07/11/2008​

|

3​

|V1962|

852​

|

6340​

|

Kết quả là mỗi NV trong DS trên sẽ tăng số ca lên 1, số TB giảm xuống.

Làm lại ra kết quả như file đính kèm, có kèm kết quả lần trước để so sánh.

Nói thêm: Query làm bằng Access, import vào Excel.

 

[LearnExcel]

Xin lỗi các bác vì tôi phải đi làm đêm qua như anh DAT2007 nói, không trả lời ngay được. Ngoài ra đúng là phần dữ liệu trình bày chưa rõ. Trường Ca chính xác là Mã số ca (bên tôi vẫn gọi là ca A,B,C).

Bác giải thích cho mọi người vì sao NV A1 lại có số ca là 3 với 4 ngày khác nhau ? Theo tôi hiểu thì số ca phải là 4 mới đúng?

Chỗ này tôi có bị nhầm, không để ý phần mã ngày.

Tuy nhiên cũng đã nói ở đoạn đầu

Một ngày có 3 ca làm việc, mỗi ca có nhiều NV, mỗi NV làm nhiều mã SF.

Nay cần có số liệu trung bình sản lượng của mỗi NV theo ca thì dùng công thức trong Excel như thế nào

@ bác Pmt, vì dữ liệu gốc còn nhiều trường khác, ví dụ thống kê mã SF, thống kê SL của Trưởng ca, các chỉ tiêu SL hỏng của SF theo ca, ngày nên tôi không muốn tách bảng ra mất tính thống nhất của DL.

Ở bài này như đã trình bày trên tôi có thể dùng bảng Pivot phụ để đếm số ca, lấy tổng SL/ số ca nhưng tôi vẫn muốn tìm kiếm giải thuật kiểu truy vấn SQL. Tôi sẽ kt kết quả của bác và thông báo sớm

Cảm ơn CÁC bác nhiệt tình giúp đỡ. Tuy nhiên thỉnh thoảng có những VĐ khó thì thảo luận chắc sôi nổi hơn.