Advanced SQL in Rails - Part 1

Khi càng làm việc với các ứng dụng Rails, tôi càng thích ActiveRecord. Đó là một sự trừu tượng trên lớp dữ liệu của bạn và cho phép bạn tập trung vào logic nghiệp vụ thay vì tạo ra các câu lệnh SQL. Đối với phần lớn các trường hợp sử dụng, công việc thực sự là rất lớn. Nhưng khi các ứng dụng phát triển trong cả kích thước và độ phức tạp của cơ sở dữ liệu, chúng ta có thể bắt đầu thấy một số lý do thuyết phục để "closer to the metal" và làm việc trực tiếp với cơ sở dữ liệu. Không có gì bí mật khi nó giúp việc thao tác với cơ sở dữ liệu nhanh hơn . Đối với các chức năng tổng hợp phức tạp liên quan đến xử lý dữ liệu từ hàng ngàn hoặc hàng trăm ngàn bản ghi, databases có thể dễ dàng vượt trội hơn bất kỳ triển khai nào trong Ruby. ActiveRecord hỗ trợ việc này khá tốt. Nhưng nếu chúng ta muốn đi xa hơn thì sao? Trong phần 1 của bài này, tôi sẽ giới thiệu hai tính năng mạnh mẽ phổ biến cho hầu hết các cơ sở dữ liệu quan hệ hiện nay: window functions and views. Trong phần 2, tôi sẽ thảo luận làm thế nào bạn có thể tận dụng sức mạnh của nó ngay trong Rails. Tôi đang sử dụng Postgres, nhưng các ví dụ tôi hiển thị nên làm việc trong RDBMS của bạn lựa chọn (với một vài tinh chỉnh để cú pháp để phù hợp hơn).

Window Functions

Hàm aggregate của bạn sẽ trả về một kết quả dạng: aggregated. Ví dụ: giả sử tôi muốn có được số dư tài khoản ngân hàng bằng cách tổng hợp tất cả các giao dịch:

SELECT sum(amount) FROM transactions WHERE account = 'debit';

  sum
--------
 387.0

Sử dụng hàm sum (), chúng ta sẽ nhận được một kết quả duy nhất. Bây giờ, nếu chúng ta muốn trả lại tất cả các hồ sơ cho account 'debit', với một tổng số chạy? Ví dụ: chúng ta có thể muốn xây dựng một kết quả như sau:

    date    | amount | balance
------------+--------+---------
 2016-07-01 |  50.25 |   50.25
 2016-07-01 |  17.35 |   67.60
 2016-07-01 |  21.56 |   89.16
 2016-07-02 |  14.01 |  103.17
 2016-07-02 |  79.23 |  182.40
 2016-07-02 | -15.00 |  167.40
 2016-07-02 |  46.23 |  213.63
 2016-07-03 | 100.74 |  314.37
 2016-07-03 |  72.67 |  387.04

Đây là nơi mà các window functions được đưa vào. Chúng cho phép bạn tính toán các chức năng tổng hợp cho từng hàng riêng lẻ sử dụng 'window' vào truy vấn có thể cắt dữ liệu theo các cách khác nhau. Trong ví dụ này, đối với bất kỳ hàng nào, chúng ta có thể yêu cầu cơ sở dữ liệu tính giá trị cho 'balance' bằng cách lấy kết quả từ truy vấn ban đầu của chúng ta và vẽ một 'window' xung quanh một tập con của các hàng, sau đó tổng hợp kết quả. Đây là những gì mà truy vấn sẽ như sau:

SELECT date, amount,
sum(amount) OVER(ORDER BY date, id) AS balance
FROM transactions
WHERE account = 'debit';

Chúng ta có thể xây dựng mộtwindow function bằng cách sử dụng OVER. Tất cả mọi thứ trong ngoặc đơn định nghĩa cách window sẽ được tự động xây dựng cho mỗi hàng. Ở đây, chúng ta nói rằng chúng ta muốn tạo ra một cột có tên là 'balance', nó sẽ chứa tổng của cột amount, nhưng chúng ta muốn tính toán nó bằng cách xem xét các hàng chứa và bao gồm hàng hiện tại, như sắp xếp theo ngày và ID .

Ngoài việc chỉ phân loại kết quả bằng nhiều cách khác nhau, chúng ta cũng có thể tính toán các giá trị bằng cách phân chia bộ kết quả, về cơ bản nhóm mỗi hàng vào các 'buckets' khác nhau trước khi tính toán. Ví dụ: nếu bảng giao dịch của chúng ta có cột 'account' và chúng ta muốn hiển thị bảng chứa các giao dịch từ mọi tài khoản, với số dư đang hoạt động cho mỗi account:

SELECT date, amount, account,
sum(amount) OVER(PARTITION BY account ORDER BY date, id) AS balance
FROM transactions;

    date    | amount | account | balance
------------+--------+---------+---------
 2016-07-01 |  50.25 | debit   |   50.25
 2016-07-01 |  17.35 | debit   |   67.60
 2016-07-01 |  21.56 | debit   |   89.16
 2016-07-02 |  14.01 | debit   |  103.17
 2016-07-02 |  79.23 | debit   |  182.40
 2016-07-02 | -15.00 | debit   |  167.40
 2016-07-02 |  46.23 | debit   |  213.63
 2016-07-03 | 100.74 | debit   |  314.37
 2016-07-03 |  72.67 | debit   |  387.04
 2016-06-15 |     25 | savings |      25
 2016-06-22 |     25 | savings |      50
 2016-07-01 |     25 | savings |      75
 2016-07-08 |     25 | savings |     100
 2016-07-16 |     25 | savings |     125
(14 rows)

Chúng ta giữ mệnh đề ORDER BY nhưng đã thêm PARTITION BY. Dưới đây là những gì xảy ra:

Cả hai ví dụ này là cái gì đó có thể được thực hiện ở lớp ActiveRecord của bạn, có thể với cách sử dụng thông minh của phạm vi và các thuộc tính ảo. Nhưng khi tập dữ liệu của bạn phát triển, các tính toán này trở nên tốn kém để thực hiện trong bộ nhớ.

Views

Trong các ví dụ ở trên, cột 'balance' luôn là cột ảo. Chúng ta không lưu trữ nó trong database; Chúng ta tính toán nó on-the-fly mỗi khi chạy truy vấn. Chúng ta có thể thêm một cột cho nó trong bảng, nhưng sau đó cần đảm bảo rằng nó được tính chính xác mỗi khi một bản ghi trong bảng được thêm mới, cập nhật hoặc xóa. Thêm vào đó, vì chúng ta biết sẽ cần những truy vấn này rất nhiều để hiển thị cho người dùng, sẽ tốt hơn nếu có thể lưu trữ nó để truy cập dễ dàng.

Database views cho phép bạn thực hiện điều đó: lưu trữ truy vấn và truy cập nó như thể nó là một bảng. Bạn sẽ có được lợi ích của một giao diện chung thông qua đó để truy cập dữ liệu của bạn, mà không phải lo lắng về sự phức tạp của các giá trị động.

Tạo một view khá dễ dàng trong Postgres. Chúng ta hãy tạo một view với truy vấn ví dụ của chúng ta ở trên:

CREATE VIEW debit_account_activity AS (
  SELECT date, amount,
  sum(amount) OVER(ORDER BY date, id) AS balance
  FROM transactions WHERE account = 'debit'
);

Lần tiếp theo chúng ta muốn truy cập dữ liệu đó, chúng ta chỉ có thể truy vấn view như thể nó là một bảng thay vì viết lại toàn bộ truy vấn:

SELECT * FROM debit_account_activity;

    date    | amount | balance
------------+--------+---------
 2016-07-01 |  50.25 |   50.25
 2016-07-01 |  17.35 |   67.60
 2016-07-01 |  21.56 |   89.16
 2016-07-02 |  14.01 |  103.17
 2016-07-02 |  79.23 |  182.40
 2016-07-02 | -15.00 |  167.40
 2016-07-02 |  46.23 |  213.63
 2016-07-03 | 100.74 |  314.37
 2016-07-03 |  72.67 |  387.04
(9 rows)

Lưu ý rằng bằng cách sử dụng một cách nhìn theo cách này là tương tự như tạo ra một method; Khi bạn gọi SELECT * FROM debit_account_activity, Postgres chỉ đơn giản chạy các truy vấn bạn đã đưa ra nó khi bạn tạo ra xem.

Nhiều cơ sở dữ liệu, bao gồm Postgres, có một kiểu xem khác, được gọi là một 'materialized view'. Materialized views sẽ tiếp tục tồn tại các kết quả từ truy vấn như thể nó là một bảng. Do đó, tuy nhiên, chúng cần được làm mới bất cứ khi nào các dữ liệu cơ bản thay đổi, vì vậy chúng tốt nhất cho các kịch bản mà dữ liệu thời gian thực không phải là ưu tiên.

Summary Cho đến nay, chúng ta đã thấy cách chúng ta có thể sử dụng view và window để xây dựng các truy vấn hiệu quả trong SQL, tránh được nhiều sai lầm phổ biến gặp phải khi kéo dữ liệu cho một view.