Hôm nay mình gửi đến mọi người một kỹ thuật giúp chuyển đổi giá trị ở hàng thành cột trong MySQL mà không cần sử dụng đến DB của bên thứ ba (elasticsearch, ..) hỗ trợ cho việc thống kê dữ liệu.

Problem

Bài toán đặt ra như sau, bạn cần thống kê rằng từng người đã mượn sách được bao nhiêu lần ứng với mỗi quyển. Giả sử DB đang lưu những thông tin sau đây:

Và output sẽ có dạng (để có thể sort theo số lượt mượn sách):

MySQL

Vậy cách làm như thế nào? Ở đây mình sẽ chia thành 2 dạng:

Dạng 1: Số lượng sách, tên sách là cố định không đổi

Đầu tiên dựa vào sự quan hệ của các bảng trong DB, tạo ra một bảng mới với các thông tin như sau:

SELECT users.id, name, number_of_times_borrowed
FROM users LEFT OUTER JOIN books_renters ON books_renters.deleted_at IS NULL AND books_renters.renter_id = users.id LEFT OUTER JOIN books ON books.deleted_at IS NULL AND books.id = books_renters.book_id
WHERE users.deleted_at IS NULL
LIMIT 5

Để có thể custom được tên của một column, sử dụng aggregate functions rồi đặt cho nó một alias như ví dụ ở dưới:

Ở đây, thay vì SUM(number_of_times_borrowed) (tên mặc định) bạn có thể đặt cho nó một cái tên mới bằng cách thêm vào sau một alias

SUM(number_of_times_borrowed) AS 'name'

Tuy nhiên, để có thể lấy tên của một record làm column chúng ta cần một ít trick

Sử dụng kết hợp group_concat() với case when sẽ đưa ra được output mong muốn. Tới đây, việc tạo ra bảng với tạm đã hoàn tất, bạn có thể LEFT JOIN với users để lấy ra thông tin cần thiết

Câu query cuối cùng (1) sẽ như sau:

SELECT id, jitu_name, gender, phone_number, GROUP_CONCAT(CASE WHEN name = 'Behold the Man' THEN number_of_times_borrowed END) AS `Behold the Man`, GROUP_CONCAT(CASE WHEN name = 'The Doors of Perception' THEN number_of_times_borrowed END) AS `The Doors of Perception`
FROM (SELECT users.id, jitu_name, gender, phone_number, name, number_of_times_borrowed FROM users LEFT OUTER JOIN books_renters ON books_renters.deleted_at IS NULL AND books_renters.renter_id = users.id LEFT OUTER JOIN books ON books.deleted_at IS NULL AND books.id = books_renters.book_id) infos_tbl
GROUP BY id, jitu_name, gender, phone_number
LIMIT 6

Tuy nhiên, vì đây là tên của record nên thông thường nó sẽ update lúc đó tên của cột cũng cần phải thay đổi theo và hiển nhiên rằng số lượng record cũng vậy. Do đó, bài toán ở đây sẽ mở rộng thành làm sao để các cột có thể thay đổi tên và số lượng khi record được thêm mới hay cập nhạt (khi thêm một cuốn sách mới thì sẽ có cột mới xuất hiện).

Dạng 2: Thay đổi tên và số lượng sách

Nôm na câu query trong MySQL sẽ như sau

SELECT GROUP_CONCAT(DISTINCT CONCAT('group_concat(case when name = \'', name, '\' then number_of_times_borrowed end) AS `', name, '`'))
INTO @sql FROM (SELECT users.id, name, number_of_times_borrowed FROM users LEFT OUTER JOIN books_renters ON books_renters.deleted_at IS NULL AND books_renters.renter_id = users.id LEFT OUTER JOIN books ON books.deleted_at IS NULL WHERE books.id = books_renters.book_id) infos_tbl; SET @sql = CONCAT(
'SELECT id, ', @sql, 'FROM ( SELECT users.id, name, number_of_times_borrowed FROM users LEFT OUTER JOIN books_renters ON books_renters.deleted_at IS NULL AND books_renters.renter_id = users.id LEFT OUTER JOIN books ON books.deleted_at IS NULL AND books.id = books_renters.book_id ) infos_tbl
GROUP BY id
LIMIT 5'); PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

... Thực sự giờ nhìn vào mình cũng muốn tuy nhiên vì KH là thượng đế nên vẫn phải tiếp tục thôi. Chia câu query thành 2 phần rồi phân tích nó nào:

Phần 1:

SELECT GROUP_CONCAT(DISTINCT CONCAT('group_concat(case when name = \'', name, '\' then number_of_times_borrowed end) AS `', name, '`'))
INTO @sql FROM (SELECT users.id, name, number_of_times_borrowed FROM users LEFT OUTER JOIN books_renters ON books_renters.deleted_at IS NULL AND books_renters.renter_id = users.id LEFT OUTER JOIN books ON books.deleted_at IS NULL WHERE books.id = books_renters.book_id) infos_tbl; SELECT @sql;

Như vậy, hàm SELECT INTO sẽ sao chép chuỗi results vào biến @sql bên cạnh việc MySQL cũng tạo ra một chuỗi các câu GROUP_CONCAT ...

Phần 2:

SET @sql = CONCAT(
'SELECT id, ', @sql, 'FROM ( SELECT users.id, name, number_of_times_borrowed FROM users LEFT OUTER JOIN books_renters ON books_renters.deleted_at IS NULL AND books_renters.renter_id = users.id LEFT OUTER JOIN books ON books.deleted_at IS NULL AND books.id = books_renters.book_id ) infos_tbl
GROUP BY id
LIMIT 5'); SELECT @sql;

Vậy là biến @sql là chuỗi kết hợp các string để tạo ra câu query hoàn chỉnh và kết quả là giống hoàn toàn với (1).

Cuối cùng excute @sql nữa là xong. Vậy là bài toán đã được giải quyết triệt để cho cả dữ liệu cứng và động

Rails

Áp dụng vào Rails, lần lượt làm theo 2 bước như bài toán dạng 2 trong MySQL:

• Tạo một câu tập hợp của group_concat
• Tạo query hoàn chỉnh

class Book::NumberOfTimesBorrowedQuery def initialize users = User.all, sort_params = {} @users = users @sort_params = sort_params end def all generate_select_query users.from("(#{query_str}) users") end def sort sort_params.present? ? all.order("book_#{key.to_s.to_i} #{direction}") : all end private attr_reader :users, :sort_params def query_str <<-SQL SELECT #{@select_query.to_s} FROM ( SELECT .. FROM users LEFT OUTER JOIN ( ... ) users INNER JOIN ... WHERE ... GROUP BY users.id SQL end def key sort_params.keys.first end def direction sort_params.values.first end def generate_select_query @select_query = "..." book_ids.each do |book_id| dynamic_book_id_col = ", GROUP_CONCAT(CASE WHEN book_id = #{book_id} THEN number_of_times_borrowed END) 'book_#{book_id}'" @select_query += dynamic_book_id_col end end
end

Trong trường hợp tên của cuốn sách là tiếng Nhật, Trung hay không thuộc Latinh có thể sẽ gặp một số lỗi bất cập (font ...), nên mình sử dụng id để thay thế.

Tất nhiên cách nào cũng có 2 mặt của nó, cùng đến với ưu, nhược điểm:

Ưu:

• Không cần phải sử dụng đến bên thứ 3.
• Chuyển hóa SQL thành NoSQL.

Nhược:

• Performance (cần cẩn thận trong việc đánh index) đôi lúc làm request timed out do query cost khá lớn.
• Viết cần kiểm tra id khi client request lên server phục vụ việc sort vì không tồn tại id phù hợp sẽ dẫn đến query lỗi.

Tổng kết

Hi vọng bài viết này sẽ giải quyết được vấn đề bạn gặp phải. Happy coding!