Bạn có thể làm những điều này bằng SQL, dừng việc viết thêm code đi
SQL, Lisp và Haskell là những ngôn ngữ lập trình duy nhất mà tôi thấy người ta thường dành nhiều thời gian để suy nghĩ hơn là đánh máy — Philip Greenspun. Ngay cả khi vậy, SQL ( Structured Query Language ) vẫn được sử dụng bởi các kỹ sư phần mềm chỉ để kéo dữ liệu ra. ...
SQL, Lisp và Haskell là những ngôn ngữ lập trình duy nhất mà tôi thấy người ta thường dành nhiều thời gian để suy nghĩ hơn là đánh máy — Philip Greenspun.
Ngay cả khi vậy, SQL (Structured Query Language) vẫn được sử dụng bởi các kỹ sư phần mềm chỉ để kéo dữ liệu ra.
Chúng tôi thường không sử dụng SQL để tăng khả năng thao tác dữ liệu và thực hiện các thay đổi cần thiết trong code.
Điều này khá phổ biến đối với các kỹ sư phần mềm làm việc với các ứng dụng web. Một điều khác mà chúng ta bỏ lỡ là, nếu chúng ta thực hiện thao tác trực tiếp trong SQL, dữ liệu được kéo ra sẽ có cùng format với bất kỳ ngôn ngữ lập trình nào. Bài này nhằm mục đích khai sáng cho bạn về khả năng của SQL mà có thể bạn biết nhưng không sử dụng thường xuyên.
](/pictures/picfullsizes/2019/01/07/cgl1546844731.jpg)
TLDR;
Sử dụng SQL để tính toán như tính tổng, trung bình, v.v … Sử dụng nó để gộp từ một đến nhiều giá trị liên quan nhau như tạo các danh mục sản phẩm. Tận dụng SQL để thao tác chuỗi như sử dụng CONCAT_WS để nối tên và họ. Khai thác SQL để sắp xếp theo một công thức ưu tiên tùy chỉnh. Ví dụ như dưới đây…
Ví dụ
Sẽ dễ dàng hơn để giải thích các siêu năng lực của SQL bằng ví dụ thực tế. Dưới đây là một sơ đồ cơ bản với 2 bảng trong MYSQL cho microservice hoàn trả:
Có 2 khoản hoàn trả và 7 khoản thanh toán liên quan như data mẫu.
Một số giả định
Sơ đồ mẫu về microservice hoàn trả và các ứng dụng được thực hiện theo giả định sau:
- Microservice hoàn trả và data structure lưu trữ fk_item (id của mặt hàng đã được đặt/giao), nhưng nó không phải là khóa ngoại cứng.
- Item có thể được hoàn lại bằng tiền mặt hoặc thẻ tín dụng với cùng lượng tiền đã được trả trước đó.
- Các item có thể được hoàn lại bao nhiêu lần cũng được miễn là số dư còn lại có thể bao gồm số tiền hoàn trả được yêu cầu cho mỗi khoản tiền mặt và tín dụng. Ví dụ: item được trả 50 bằng tiền mặt và 50 bằng tín dụng. 2 khoản hoàn lại gồm 20 tiền mặt và 20 tín dụng. Vậy sau cùng, số dư các giao dịch sẽ là 10 tiền mặt và 10 tín dụng cho item đó (50-20-20).
- Mỗi khoản hoàn trả có thể có nhiều khoản thanh toán. Mỗi khoản thanh toán có thể là tiền mặt hoặc tín dụng.
- Tất cả số tiền được lưu trữ bằng xu để chúng là số nguyên.
Bây giờ hãy sử dụng một số chức năng của SQL. Bạn có thể tìm thấy ví dụ về các queries liên quan đang chạy trên SQL Fiddle.
Tính toán trong SQL
Là kỹ sư phần mềm, nếu chúng ta cần tìm tổng số tiền và tín dụng được hoàn trả cho một mặt hàng, chúng ta sẽ làm gì? Chúng ta sẽ chạy code như này:
|
1 2 3 4 |
SELECT fk_item, fk_refund, amount, is_cash FROM payment WHERE fk_item=2001; |
Với dữ liệu hiện tại, kết quả ra 3 hàng như dưới đây:
Với 3 hàng này, chúng ta sẽ lặp lại chúng. Nếu đó là tiền mặt thì tích lũy nó vào biến cashBalance, nếu không thì cộng nó vào biến creditBalace. Nhưng thay vì làm như vừa rồi, sẽ dễ dàng hơn rất nhiều (có thể nhanh hơn) khi ta thực hiện trong SQL như:
|
1 2 3 4 |
SELECT fk_item, SUM(amount) AS total_paid, IF(is_cash = 1, 'cash', 'credit') as type FROM payment WHERE fk_item = 2001 GROUP BY fk_item, is_cash; |
Kết quả là:
Bây giờ muốn biết kết quả rất dễ dàng. Nếu bạn cần biết tổng số tiền hoàn lại cho item, chỉ cần thay GROUP BY thành fk_item và xong. Có thực hiện thêm 2 hay 3 hồ sơ nữa cũng không tốn bao nhiêu công sức. Nếu như có 20 khoản hoàn lại cho item đó, giải pháp đầu tiên với vòng lặp là viết thêm mã mà không có tác dụng gì. Giống như tính tổng, các hàm SQL khác cũng được sử dụng tương tự. Thực hiện các phép toán đơn giản như tính tổng, bội, trung bình, v.v có thể dễ dàng hơn với SQL. Điều này có nghĩa là không còn vòng lặp nữa.
Sử dụng GROUP_CONCAT để tìm về các giá trị quan hệ 1:m liên quan
Group concat là một operation mạnh mẽ trong cơ sở dữ liệu SQL. Nó là công cụ giúp bạn lấy data từ một đến nhiều relationship. Chẳng hạn, bạn muốn lấy tất cả các tag trong một bài đăng trên blog hoặc bạn muốn biết tất cả các danh mục của một sản phẩm. Quay lại ví dụ hoàn trả này, một item có thể được hoàn lại nhiều lần. Vì vậy, chúng ta sẽ đem tất cả các khoản hoàn lại liên quan đến id item. Để có được điều này, chúng ta sẽ chỉ chạy 1 query và có kết quả mà không có bất kỳ vòng lặp nào trong code như dưới đây:
|
1 2 3 4 |
SELECT fk_item, GROUP_CONCAT(DISTINCT fk_refund) refund_ids FROM payment WHERE fk_item = 2001; |
Kết quả là:
Bây giờ chúng ta biết được rằng item 2001 đã được hoàn lại hai lần cho 2 khoản hoàn trả. Sẽ dễ dàng để loại bỏ Id hoàn trả, và tiến hành mọi operation liên quan.
Thao tác chuỗi
Nhiều task thao tác chuỗi như substring, concatenation, change case, và string compare có thể thực hiện được trong SQL. Với ví dụ này, tôi sẽ chỉ cách sử dụng CONCAT_WS. Đó là concat với một dải phân cách. Nó cũng có thể được sử dụng để chọn ví dụ như Tên và Họ với khoảng trắng ở giữa.
Trong trường hợp có tên đệm tùy chọn, COALESCE có thể được sử dụng với CONCAT_WS. Nếu bạn vẫn chưa biết :).
Trong ví dụ này, tôi sẽ chọn refund_nr và lý do liên quan của nó:
|
1 2 3 4 |
SELECT CONCAT_WS("-", refund_nr, reason) AS refund_nr_with_reason FROM refund; |
Kết quả:
Ví dụ nếu kết quả này cần được hiển thị trên tài liệu ghi chú tín dụng, thì ta không cần thêm code để kết hợp với các giá trị nữa. SQL một lần nữa làm cho mọi chuyện dễ dàng hơn.
Sắp xếp với công thức tùy chỉnh
Tất cả các kỹ sư phần mềm đều biết sắp xếp theo cột. Nhưng nếu bạn được cung cấp một công thức ưu tiên tùy chỉnh để sắp xếp, bạn sẽ làm gì? Có lẽ bạn lại quay trở lại code và vòng lặp để sắp xếp. Vì vậy, hãy đặt quy tắc công thức ưu tiên cho ví dụ trên:
- Tiền hoàn lại của khách hàng cao cấp được ưu tiên nhất (chúng ta hack nó với mức ưu tiên 9999999999)
- Ngoài khách hàng cao cấp, hoàn trả tiền được ưu tiên * 25, còn tín dụng là * 20.
Theo quy tắc trên, khách hàng cao cấp và ưu tiên trên 50000 (tính bằng xu) sẽ được xử lý trước. Sau đó, những khoản hoàn trả khác mới được xử lý. Ta tính được các khoản hoàn trả ưu tiên như sau:
|
1 2 3 4 5 6 7 |
SELECT r.refund_nr, r.reason, p.fk_item, p.amount, p.is_cash, IF(p.premium_customer = 1, 9999999999, p.amount * (IF(is_cash = 1, 25, 20))) AS priority FROM refund AS r INNER JOIN payment AS p ON r.id = p.fk_refund HAVING priority > 50000 ORDER BY priority DESC |
Kết quả trong bảng dưới:
Việc sử dụng IF đúng cách trong SQL theo công thức ưu tiên tùy chỉnh sẽ dễ dàng hơn rất nhiều so với việc cố gắng thực hiện việc đó với các vòng lặp trong code. Lưu ý rằng thậm chí những số tiền nhỏ hơn như 7,5 (750 xu) và 9.0 (900 xu) vẫn được ưu tiên nhất vì các khoản thanh toán hoàn lại này được liên kết với các khách hàng cao cấp.
Sử dụng siêu năng lực của SQL làm cho cuộc sống của bạn dễ dàng hơn khi là một kỹ sư phần mềm.
Bạn có thể thử ví dụ trên và chạy các query của mình trên SQL fiddle.
Kết luận
Và còn những thủ thuật khác của SQL có thể giúp bạn nhiều khi là một kỹ sư phần mềm. Giống như, UPDATE và INSERT sử dụng ON DUPLICATE KEY UPDATE. Bất cứ khi nào bạn cảm thấy khó khăn khi thực hiện một số thao tác đối với data được lấy từ cơ sở dữ liệu bằng các vòng lặp, hãy nghĩ về SQL. Điểm chính của bài viết này là:
Khai thác sức mạnh của SQL để viết ít code hơn bởi vì “code tốt nhất là code không bao giờ được viết ra”. Nếu nó không được viết thì không cần phải giữ nó.
Techtalk via dev.to
