Giúp em về xử lý với cơ sở dữ liệu

dhuykhoi · 5/4/18

Dear các anh chị,

Tình hình là em có 1 bảng dữ liệu SQL Server:

Giờ em muốn thực hiện câu lệnh query để có được bảng kết quả là điểm của từng lần thi mà tìm mãi không ra cách.

Hoặc thực hiện lập trình trên VBA ở Excel cũng được.

Em rất mong được các anh chị chỉ giáo..

Em xin cám ơn!

VetMini · 5/4/18

dhuykhoi đã viết:
Dear các anh chị,
...
Em rất mong được các cao thủ chỉ giáo..

Sửa lại các từ trên thì tôi sẽ nói chuyện tiếp.
(nếu không biết sửa như thế nào thì cứ tưởng tượng bạn đang viết văn bản đưa cho sếp ký)

Chú: diễn đàn này cũng có nhiều người giỏi về T-SQL. Chịu khó chờ cũng chả sao.

quanluu · 6/4/18

dhuykhoi đã viết:
Dear các anh chị,

Tình hình là em có 1 bảng dữ liệu SQL Server:
View attachment 193458
Giờ em muốn thực hiện câu lệnh query để có được bảng kết quả là điểm của từng lần thi mà tìm mãi không ra cách.

Hoặc thực hiện lập trình trên VBA ở Excel cũng được.

Em rất mong được các cao thủ chỉ giáo..

Em xin cám ơn!

Thử thế này coi

SELECT DISTINCT A.TEN,
SUBSTRING(
(
SELECT '-' + CAST(B.DIEM AS NVARCHAR(5)) AS [TEXT()]
FROM BANGDIEM B WHERE B.TEN = A.TEN
ORDER BY B.TEN
FOR XML PATH ('')
), 2, 100) AS DIEM_CAC_LAN

FROM BANGDIEM A

dhuykhoi · 6/4/18

Chính xác rồi ạ. Em cảm ơn anh nhiều lắm !!! hihii

quanluu1989 đã viết:
Thử thế này coi

SELECT DISTINCT A.TEN,
SUBSTRING(
(
SELECT '-' + CAST(B.DIEM AS NVARCHAR(5)) AS [TEXT()]
FROM BANGDIEM B WHERE B.TEN = A.TEN
ORDER BY B.TEN
FOR XML PATH ('')
), 2, 100) AS DIEM_CAC_LAN

FROM BANGDIEM A

Em sửa lại rồi anh ạ. Em cám ơn!

VetMini đã viết:
Sửa lại các từ trên thì tôi sẽ nói chuyện tiếp.
(nếu không biết sửa như thế nào thì cứ tưởng tượng bạn đang viết văn bản đưa cho sếp ký)

Chú: diễn đàn này cũng có nhiều người giỏi về T-SQL. Chịu khó chờ cũng chả sao.

Hai Lúa Miền Tây · 6/4/18

dhuykhoi đã viết:
Chính xác rồi ạ. Em cảm ơn anh nhiều lắm !!! hihii

View attachment 193483

Bạn thử như sau:

Mã:

SELECT DISTINCT Ten, stuff
                          ((SELECT     ' - ' + CONVERT(VARCHAR(50), DiemCacLan, 128)
                              FROM         tblBangDiem t1
                              WHERE     t1.Ten = t2.Ten FOR XML PATH('')), 1, 2, '') AS DiemCacLan
FROM         tblBangDiem t2

VetMini · 6/4/18

@quanluu1989:
[text()] là cái từ khoá đặc biệt báo cho XML builder biết rằng cột này là cột không có tên. Nếu không có ký hiệu này, và cột lấy ra từ một trường nguyên thủy, XML builder sẽ dùng tên trường để tạo thẻ bọc dữ liệu <Diem>4</Diem>

Ở bài này, vì cột là con toán cộng dấu trừ ('-') nên tự nó đã không có tên rồi.

Nói cách khác, trường hợp này, bạn có thể bỏ đi từ "AS [text()]".

Chú thích 1: ngừoi ta chỉ truy vấn gộp cột khi các cột không tương ứng với nhau. Ở bài này, các điểm thực ra có thứ tự rõ rệt, lần 1, lần 2... và có giới hạn số (không lẽ thi 10 lần?)
Trình bày theo kiểu pivot mới là đúng phương pháp báo cáo.
Chú thích 2: vì trường hợp này đặc biệt là các dòng có thứ tự rõ rệt cho nên cũng có thể dùng câu truy vấn CASE WHEN.

quanluu · 6/4/18

VetMini đã viết:
@quanluu1989:
[text()] là cái từ khoá đặc biệt báo cho XML builder biết rằng cột này là cột không có tên. Nếu không có ký hiệu này, và cột lấy ra từ một trường nguyên thủy, XML builder sẽ dùng tên trường để tạo thẻ bọc dữ liệu <Diem>4</Diem>

Ở bài này, vì cột là con toán cộng dấu trừ ('-') nên tự nó đã không có tên rồi.

Nói cách khác, trường hợp này, bạn có thể bỏ đi từ "AS [text()]".

Chú thích 1: ngừoi ta chỉ truy vấn gộp cột khi các cột không tương ứng với nhau. Ở bài này, các điểm thực ra có thứ tự rõ rệt, lần 1, lần 2... và có giới hạn số (không lẽ thi 10 lần?)
Trình bày theo kiểu pivot mới là đúng phương pháp báo cáo.
Chú thích 2: vì trường hợp này đặc biệt là các dòng có thứ tự rõ rệt cho nên cũng có thể dùng câu truy vấn CASE WHEN.

Cảm ơn anh, cái XML này em không rành lắm.

dhuykhoi · 6/4/18

em lại phát sinh vấn đề là.. database của em lớn quá, 3 triệu dòng, mà mỗi học sinh thi lại đến tận 5, 6 lần liền. em chạy query 4 tiếng 20p rồi mà nó chưa xong.. các anh có cách nào để database chạy nhanh hơn được không ạ??

Em xin cám ơn nhiều !!!!

black1211 · 6/4/18

dhuykhoi đã viết:
em lại phát sinh vấn đề là.. database của em lớn quá, 3 triệu dòng, mà mỗi học sinh thi lại đến tận 5, 6 lần liền. em chạy query 4 tiếng 20p rồi mà nó chưa xong.. các anh có cách nào để database chạy nhanh hơn được không ạ??

Em xin cám ơn nhiều !!!!

Bạn chạy ở môi trường Database nào, hay Excel
Nếu là SQL Server thì đâu chậm thế, và cứ từ từ ngủ 1 giấc , sáng mai xong cũng được.

VetMini · 6/4/18

dhuykhoi đã viết:
em lại phát sinh vấn đề là.. database của em lớn quá, 3 triệu dòng, mà mỗi học sinh thi lại đến tận 5, 6 lần liền. em chạy query 4 tiếng 20p rồi mà nó chưa xong.. các anh có cách nào để database chạy nhanh hơn được không ạ??

Em xin cám ơn nhiều !!!!

Ước lượng: 3 triệu / 6 lần thi = 500.000 học sinh.

Thứ nhất: với SQL Server thì vài triệu chả có nghĩa lý gì.

Thứ hai: để quản lý 500.000 học sinh thì cái CSDL khá phức tạp.
Chả có một trường nào có đến con số này. Họa may là tổ hợp của nhiều trường. Consolidated Database là công việc rất cao cấp. Nếu bạn đã lên đây hỏi là chắc chắn nó nằm ngoài khả năng của bạn. Về bảo sếp cơ quan tìm chuyên viên thôi.

AutoReply · 7/4/18

black1211 đã viết:
Bạn chạy ở môi trường Database nào, hay Excel
Nếu là SQL Server thì đâu chậm thế, và cứ từ từ ngủ 1 giấc , sáng mai xong cũng được.

Bảng tính Excel có 3 triệu dòng rồi chăng ?

(vui thôi không có gì đâu)

AutoReply · 7/4/18

VetMini đã viết:
Ước lượng: 3 triệu / 6 lần thi = 500.000 học sinh.

Thứ nhất: với SQL Server thì vài triệu chả có nghĩa lý gì.

Thứ hai: để quản lý 500.000 học sinh thì cái CSDL khá phức tạp.
Chả có một trường nào có đến con số này. Họa may là tổ hợp của nhiều trường. Consolidated Database là công việc rất cao cấp. Nếu bạn đã lên đây hỏi là chắc chắn nó nằm ngoài khả năng của bạn. Về bảo sếp cơ quan tìm chuyên viên thôi.

Người hỏi đang là chuyên viên của nơi ấy rồi. Tìm chuyên viên mới thì thớt đi về nơi đâu anh ?

AutoReply · 7/4/18

dhuykhoi đã viết:
em lại phát sinh vấn đề là.. database của em lớn quá, 3 triệu dòng, mà mỗi học sinh thi lại đến tận 5, 6 lần liền. em chạy query 4 tiếng 20p rồi mà nó chưa xong.. các anh có cách nào để database chạy nhanh hơn được không ạ??

Em xin cám ơn nhiều !!!!

Truy vấn như các đề nghị ở trên không đến nỗi tệ đâu bạn, và chắc không thể kéo dài vài tiếng như bạn nói, trừ khi máy chủ quá yếu hoặc phiên bản SQL server là bản miễn phí.
Bạn có thể thử với từ khóa PIVOT SQL SERVER google xem sao, nếu vẫn chậm thì ta sẽ phải tính cách khác, có thể phải sử dụng Procedure

VetMini · 7/4/18

AutoReply đã viết:
Truy vấn như các đề nghị ở trên không đến nỗi tệ đâu bạn, và chắc không thể kéo dài vài tiếng như bạn nói, trừ khi máy chủ quá yếu hoặc phiên bản SQL server là bản miễn phí.
Bạn có thể thử với từ khóa PIVOT SQL SERVER google xem sao, nếu vẫn chậm thì ta sẽ phải tính cách khác, có thể phải sử dụng Procedure

Đặt một cái cấu trúc tạm cho XML với 3 triệu tags, 500.000 roots là SQL Server Express chết chắc.
Mặt khác, với Server trả tiền thì không nghĩa lý gì. Nếu chậm là do đường dẫn và hạ tầng. Nhưng nếu do hạ tầng thì Procedure chả giúp ích gì.

1. Khi số records lên đến triệu thì SSE quên đi. Bắt buộc phải mua bản quyền.
2. Khi số học sinh cần quản lý lên đến gần chục ngàn thì ngừoi quản lý phải nhiều kinh nghiệm, hoặc ít nhất học qua khoá đào tạo chuyên nghiệp. Ở đây đến nửa triệu học sinh, 50 lần con số giới hạn trên. Nói thật, cỡ này là bậc thầy tôi.
3. Khi CSDL lớn đến thế thì có rất nhiều lý do để chạy chậm và có rất nhiều chỗ để cải tiến. Một vấn đề có thể cả tháng mới giải quyết xong, và phải làm trực tiếp. Ngừoi ta chỉ hỏi những chỗ bí. Hỏi đại trà trên diễn đàn là mò trăng đáy nước.

AutoReply · 7/4/18

VetMini đã viết:
Đặt một cái cấu trúc tạm cho XML với 3 triệu tags, 500.000 roots là SQL Server Express chết chắc.
Mặt khác, với Server trả tiền thì không nghĩa lý gì. Nếu chậm là do đường dẫn và hạ tầng. Nhưng nếu do hạ tầng thì Procedure chả giúp ích gì.

1. Khi số records lên đến triệu thì SSE quên đi. Bắt buộc phải mua bản quyền.
2. Khi số học sinh cần quản lý lên đến gần chục ngàn thì ngừoi quản lý phải nhiều kinh nghiệm, hoặc ít nhất học qua khoá đào tạo chuyên nghiệp. Ở đây đến nửa triệu học sinh, 50 lần con số giới hạn trên. Nói thật, cỡ này là bậc thầy tôi.
3. Khi CSDL lớn đến thế thì có rất nhiều lý do để chạy chậm và có rất nhiều chỗ để cải tiến. Một vấn đề có thể cả tháng mới giải quyết xong, và phải làm trực tiếp. Ngừoi ta chỉ hỏi những chỗ bí. Hỏi đại trà trên diễn đàn là mò trăng đáy nước.

Em không nghĩ số học sinh đến mức 500 000. Đó là số học sinh của cả 1 tỉnh chứ đâu phải 1 trường.
Cái này không chắc. Chỉ là đoán mò thôi. Người hỏi chỉ muốn nhấn mạnh vào vấn đề ghép điểm để đưa lên xem, nên lược bớt các vấn đề liên quan cho dễ hình dung.
Chứ bảng dữ liệu làm sao chỉ có vài cột được. Điểm môn gì ? 1 tiết hay học kỳ ? học kỳ nào ? năm nào ? chỉ nội những thuộc tính cơ bản đó cho vài nghìn học sinh là đủ tới vài triệu dòng dữ liệu rồi. Ở đây chúng ta chỉ đưa ra các hướng giải quyết chứ không cụ thể được. Có giải quyết được không tùy vào trình độ và kinh nghiệm của người quản lý CSDL thôi.

be_09 · 7/4/18

Tôi nghĩ cái chủ Topic theo dõi có thể là hội đồng thi cấp tỉnh (mới có con số đến 3 triệu).
Nếu sử dụng Excel thì có thể theo dõi mỗi huyện là 1 sheet (chắc không khó).
Chủ Topic nên đính kèm 1 File để các thành viên có thể hình dung công việc và góp ý hoặc giúp thì sẽ chính xác hơn.

VetMini · 7/4/18

AutoReply đã viết:
Em không nghĩ số học sinh đến mức 500 000. Đó là số học sinh của cả 1 tỉnh chứ đâu phải 1 trường.
Cái này không chắc. Chỉ là đoán mò thôi. Người hỏi chỉ muốn nhấn mạnh vào vấn đề ghép điểm để đưa lên xem, nên lược bớt các vấn đề liên quan cho dễ hình dung.
Chứ bảng dữ liệu làm sao chỉ có vài cột được. Điểm môn gì ? 1 tiết hay học kỳ ? học kỳ nào ? năm nào ? chỉ nội những thuộc tính cơ bản đó cho vài nghìn học sinh là đủ tới vài triệu dòng dữ liệu rồi. Ở đây chúng ta chỉ đưa ra các hướng giải quyết chứ không cụ thể được. Có giải quyết được không tùy vào trình độ và kinh nghiệm của người quản lý CSDL thôi.

Có thể đây là CSDL đã phân cấp. Tức là có từng bảng điểm chỉ cho 1 năm, 1 học kỳ nào đó. Khi ấy nó càng ít cột càng tốt. Chỉ khác là cột tên thì đáng lẽ phải là mã số. Chứ 500 ngàn thì trùng tên là cái chắc. Nếu CSDL lớn như tôi nghĩ thì đây rất có thể là cái View - tốc độ của view có thể tuỳ thuộc vào tay nghề của admin.

Điểm tôi nhấn mạnh là quản lý CSDL tầm vóc này phải là tay cừ khôi. Mà đã cừ khôi thì phải biết lập một View cho nó pivot bảng ra (tự phản chuẩn). Ở đây chỉ riêng cái yêu cầu đã thấy trình độ sơ khai rồi. Khi dữ liệu có thứ tự và số crosstab có giới hạn thì ngừoi báo cáo khong bao giờ ghép chúng lại mà phải xếp từng cột (đủ 6 cột). Trường hợp ghép chỉ xảy ra khi dữ liệu không có thứ tự hoặc giới hạn.
Tôi nghĩ thớt không phải là quản lý mà chỉ là truy vấn. Nếu đúng trường hợp này thì quy trình chính chắn là nhờ quản lý nó lập cho cái proc, hoặc cái view rồi từ đó mà xài.

Chú: tôi đã nhắc nhiều lần, quản lý cỡ này phải là bậc thầy tôi. Đưa cho tay mơ làm thì đương nhiên báo chí cứ rêu rao mãi tại sao bảng điểm rối lộn tung cả lên.

dhuykhoi · 9/4/18

Em cám ơn các bác.

Để trả lời thắc mắc tại sao database lớn thế vì dữ liệu này là của 20 năm kết hợp lại, cứ tích lũy dần dần nên nó lớn như vậy. Còn làm cái kia là sếp em yêu cầu ạ..

Bên em lại ko có máy chủ, chỉ có SQL Server bản quyền nhưng chạy trên .. laptop Thinkpad T430 và Desktop nên phần cứng ko được mạnh như server.

Em lại không chuyên lắm về IT nên cũng muốn học hỏi!!

VetMini · 9/4/18

Phân tích 20 năm dữ liệu thì mất vài ngày là chuyện thường. Mới có mấy tiếng đồng hồ mà đã than chậm.

Đây không phải do "không chuyên lắm về IT". Bởi vì chính dân IT cũng không làm nổi.
Công việc khai thác dữ liệu tích luỹ nhiều năm là công việc của dân... chuyên môn khai thác dữ liệu. Ngừoi ta không nhào vào dùng 1 câu query để lấy báo cáo. Một báo cáo thường được thiết kế và phân tích ngược lại về nguồn dữ liệu và lập query từng giai đoạn. Và công việc thiết kế phải được hoàn chỉnh lại nhiều lần để tiến tới mục đích cuối. Đã nói từ đầu, công việc của bậc thầy tôi mà.

(nhìn cái mẫu báo cáo ở bài #1 tôi đã nghi là sếp dốt rồi. Truy vấn bảng này đáng lẽ phải ra kiểu pivot mới có thể phân tích và tìm hiểu tiếp)

black1211 · 9/4/18

dhuykhoi đã viết:
Em cám ơn các bác.

Để trả lời thắc mắc tại sao database lớn thế vì dữ liệu này là của 20 năm kết hợp lại, cứ tích lũy dần dần nên nó lớn như vậy. Còn làm cái kia là sếp em yêu cầu ạ..

Bên em lại ko có máy chủ, chỉ có SQL Server bản quyền nhưng chạy trên .. laptop Thinkpad T430 và Desktop nên phần cứng ko được mạnh như server.

Em lại không chuyên lắm về IT nên cũng muốn học hỏi!!

Nhiêu năm, nhân lực vật lực đều thiếu và yếu thì nên thuê làm đi bạn. Số liệu 20 năm để đánh giá thì cần người phân tích. IT hay máy tính cũng chỉ là trợ giúp.

VetMini đã viết:
Phân tích 20 năm dữ liệu thì mất vài ngày là chuyện thường. Mới có mấy tiếng đồng hồ mà đã than chậm.

Có khi còn mất vài tháng để rút trích ra cái phân tích

Giúp em về xử lý với cơ sở dữ liệu

Thành viên mới

Ăn cùng góc phố

Thành viên gắn bó

Thành viên mới

❆❆❆❆❆❆❆❆

Ăn cùng góc phố

Thành viên gắn bó

Thành viên mới

Thành viên chính thức

Ăn cùng góc phố

Thành viên tiêu biểu

Thành viên tiêu biểu

Thành viên tiêu biểu

Ăn cùng góc phố

Thành viên tiêu biểu

Biên Hòa, Đồng Nai

Ăn cùng góc phố

Thành viên mới

Ăn cùng góc phố

Thành viên chính thức

Facebook

Group