Tối ưu SQL (phần 9): tính tổng các dòng ở trên

Tình hình là hôm qua trong lúc làm việc căng cmn thẳng thì gặp ngay 1 bài toán liên quan đến thằng SQL mà đã được kéo lê lết đến 8 9 phần rồi thì phải. Anh em nào chưa rõ thì ghé qua các link bên dưới để tổng hợp cho bản thân đống kiến thức nhiều như lá mùa thu của mình :3

Tối ưu hiệu năng SQL (phần 1): DATETIME

Tối ưu hiệu năng SQL (phần 2): DELETE

Tối ưu hiệu năng SQL (phần 3): Exists, IN và người thứ ba

Tối ưu hiệu năng SQL (phần 4): Trigger

Tối ưu hiệu năng SQL phần 5: RANDOM

Tối ưu hiệu năng SQL (phần 6): Update hay Merge?

Tối ưu SQL (phần 7): Chuyện ngắn của ID

Tối ưu SQL phần 8: 1 câu chuyện đơn gian về việc cộng chuỗi

À nói lại vào chủ đề chính thay vì đi luyên thuyên PR mấy bài viết cũ thôi nhỉ!!! Hôm qua trong khi cả Hà Nội đang ngập chìm trong biển nước của cơn bão số 2 thì tôi (1 nam thanh niên đẹp giai đang đau đáu những vấn đề lớn của đất nước =)) ) cũng đang chìm trong những dòng suy tưởng về cách lấy dữ liệu trong cái DB tràn ngập những con số mà chẳng hiểu được ý nghĩa các cột trong đó haizzz.

Theo yêu cầu chỉ đạo của những cấp lãnh đạo ngang hàng thì tôi sẽ phải lấy ra dữ liệu của 1 cột (cột X đi) ở dòng trên, cộng với 1 cột B ở dòng dưới và trừ đi cột C cũng ở dòng dưới rồi gán giá trị lại cho cột X. Nghe lòng vòng vãi đúng không 😐 Đại ý ý dụ như này này

              A B X

Dòng 1: 0 0 10

Dòng 2: 5 0 15 ( 10 + 5 -0 = 15)

Dòng 3: 0 2 13 (15 + 0 -2 = 13)

Dòng 4: 4 1 16 (13 + 4 – 1 = 16)

Nghĩ 1 hồi tưởng khó ghê gớm lắm ai ngờ lại khó gớm ghê :)) Thực ra sau 1 lúc loay hoay thì cách làm của nó đơn giản là chúng ta chỉ là cộng tổng qua từng dòng với tổng  bằng cột A – B thôi. Nghĩa là thằng cột X cứ cộng với ( A-B) ở dòng dưới thôi (Quái nghe như vẻ chả khác gì cái đề bài cho ra ấy 😕 có gì đó sai sai hay sao ấy :)) ) Thì đúng là đề nó là thế mà :)) Tóm lại cách làm sẽ là cứ cộng lần lượt từ trên xuống dưới với giá trị cột Y để chứa tổng đi, trừ thằng đầu tiên là thẳng bắt đầu sẽ có giá trị ở cột X, còn các dòng khác sẽ là 0

              A B X  Y

Dòng 1: 0 0 10 10

Dòng 2: 5 0 0   5 (5-0)

Dòng 3: 0 2 0 -2 (0-2)

Dòng 4: 4 1 0 3 (4-1)

Đấy vẽ ra như này thì anh em sẽ đỡ hỏi lại cách giải thích nhé :v Đúng là vẽ ra nhìn nó dễ hiểu hơn nhỉ :3 Công việc còn lại là viết query để cộng theo từng dòng thôi là xong. Dòng 2 = Dòng 1 + dòng 2, dòng 3 = dòng 1 + dòng 2 + dòng 3, dòng 4 = dòng 1 + dòng 2+ dòng 3 + dòng 4…

Để làm được điều này chúng ta sẽ sử dụng 1 tính năng siêu ưu việt của SQL mà hầu như khắp chốn võ lâm không ai không biết đó chính là ROW_NUMBER. Đầu tiên sẽ đánh row num cho từng dòng đúng không nào? Rồi từ từng dòng đó ta sẽ SUM giá trị của những thằng có row num nhỏ hơn hoặc bằng với row num đang chạy. Lại nói lắm rồi thồi anh em xem code cho dễ hiểu :3 Code sử dụng CTE  (Common Table Express) đấy nhé

WITH A (SELECT ROW_NUMBER() OVER (ORDER BY X) AS ROW_INDEX, X FROM #TEMP)

SELECT X, SUM(SELECT X FROM A  AS T1 WHERE T.1ROW_INDEX <= T.ROW_INDEX)

FROM A AS T

Đó thế là xong rồi đó :)) Đơn giản như đan rổ đúng không :v (Chém thôi chứ lúc đó nghĩ mãi chả ra đâu). Anh em cứ chủ động copy về mà dùng đi nhé :3 Giờ mình còn đi lau xuồng tí còn đi làm đây. Thân ái và chào tạm biệt

Advertisements

One thought on “Tối ưu SQL (phần 9): tính tổng các dòng ở trên

  1. Pingback: Kĩ thuật “Quirky update” | Code, code and more code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s