Heap使用Postgres SQL後的經驗教訓

banq發表於2022-01-22
SQL

今年我們為 Advent of Code 做了一些奇怪的事情：我們在 javascript 和 PostgreSQL 中解決了一些挑戰。我們學到了一些關於 SQL 的有趣的東西，我們想在這裡分享。

免責宣告：我們沒有完成 25 天的 SQL -專家。如果你是專家，你可能在這裡學不到任何東西。

 

視窗函式範圍很棒

首先，快速複習一下視窗函式：您可能知道視窗函式在一組與當前行有某種關聯的錶行上執行計算。Postgres 文件中的示例是計算特定員工的部門特定平均工資。SQL 如下所示： 

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

在上面的示例中，所考慮的行的“視窗”是其部門與當前行的部門匹配的所有行，但是在處理挑戰時，我們瞭解到也可以使用範圍定義視窗。

例如，在第 1 天，我們被要求計算三個元素的滑動視窗中值的總和增加的次數。您可以使用視窗函式Ranges範圍表示三個元素的滑動視窗之和。SQL 如下所示： 

SELECT row_number, sum(value) OVER (ORDER BY row_number RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) AS value
         FROM input_with_rows

整體解決方案如下所示： 

WITH input_with_rows AS (
    SELECT row_number() over () AS row_number, value
    FROM day1_input
),
     windows AS (
         SELECT row_number, sum(value) OVER (ORDER BY row_number RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) AS value
         FROM input_with_rows
     ),
     lagged AS (
         SELECT row_number, lag(value) OVER () AS value
         FROM windows
     )
SELECT count(*)
FROM windows
         JOIN lagged ON (windows.row_number = lagged.row_number AND lagged.value < windows.value);

範圍Ranges只是冰山一角。還有許多其他定義視窗的方法。檢視Postgres 文件的這個頁面瞭解更多資訊。

 

用於可讀性和迭代的通用表表示式

公用表表示式或 CTE 是通過簡單查詢表示式建立的臨時、範圍和別名表。在大多數情況下，子查詢可以達到相同的結果，儘管子查詢在教科書和資料庫文件中比 CTE 更常見，但我們發現使用 CTE 而不是子查詢消除了大量的巢狀。

有很多資源可以解釋為什麼深度巢狀的程式碼是一種程式碼異味。巢狀很快就會變得難以推理。當讀者必須跳來跳去以獲取上下文時，遵循執行流程會增加額外的認知負擔。CTE 是順序的，我們人類更容易遵循順序步驟。

RECURSIVE使用 CTE 的另一個優點是，如果您使用關鍵字，它們可以引用自己的輸出。文件建議使用遞迴 CTE 來處理任意巢狀的分層資料，但它們也可以用於迭代。

我們在第 3 天的工作中學習瞭如何使用 CTE 進行迭代。在第二部分，我們被要求：

  1. 從二進位制數列表開始，只考慮這些數字的第一位。
  2. 丟棄第一位與所有剩餘數字的第一位位置中最常見位不匹配的數字
  3. 如果您只剩下一個號碼，請停止；這是您正在尋找的價值
  4. 否則，重複該過程，考慮右邊的下一位。

用於此的 SQL 如下。我們使用遞迴 CTE 的有趣部分是第 14 - 40 行： 

\copy day3_input FROM 'day3.txt';


WITH bits_with_row_number AS (
    SELECT row_number() over ()            AS row_number,
           regexp_split_to_array(bits, '') AS bits
    FROM day3_input
),
     bits_with_column_number AS (
         SELECT *, row_number() over (partition by row_number) AS col_number
         FROM bits_with_row_number
     ),
     recurse AS (
         WITH RECURSIVE filter AS (
             (WITH target_bit AS (SELECT bits,
                                         CASE
                                             WHEN (sum(bits[1]::integer) OVER ()) < ((count(*) OVER ())::float / 2) THEN 0
                                             ELSE 1 END AS o2,
                                         CASE
                                             WHEN (sum(bits[1]::integer) OVER ()) < ((count(*) OVER ())::float / 2) THEN 1
                                             ELSE 0 END AS co2
                                  FROM bits_with_column_number)
              SELECT *, 1 AS i
              FROM target_bit
              WHERE bits[1]::integer = co2
             )
             UNION ALL
             (WITH target_bit AS (SELECT bits,
                                         i,
                                         CASE
                                             WHEN sum(bits[i + 1]::integer) OVER () < ((count(*) OVER ())::float / 2) THEN 0
                                             ELSE 1 END AS o2,
                                         CASE
                                             WHEN sum(bits[i + 1]::integer) OVER () < ((count(*) OVER ())::float / 2) THEN 1
                                             ELSE 0 END AS co2
                                  FROM filter)
              SELECT bits, o2, co2, i + 1 AS i
              FROM target_bit
              WHERE bits[i + 1]::integer = target_bit.co2)
         )
         SELECT *
         FROM filter
     )
SELECT *
FROM recurse
WHERE i = (SELECT max(i) FROM recurse);

關於遞迴 CTE 的工作原理有很多 很好[url=https://www.citusdata.com/blog/2018/05/15/fun-with-sql-recursive-ctes/]的[/url] 解釋，所以我們不會在這裡重複這些解釋。

 

如何進行關聯而不是迭代思考

在第 4 天，我們得到了一個數字列表和一個bingo boards ，我們被要求找出哪個bingo boards首先獲勝。當我們開始編寫 SQL 部分時，我們假設我們需要使用遞迴 CTE 以便我們可以迭代boards ，但是在 SQL 中解決問題的練習幫助我們從新的角度看待問題。我們瞭解到，僅僅因為問題陳述中存在迭代方面並不意味著我們不能將其抽象掉。

將問題分解成更小的部分讓我們看到了另一種方式。我們需要找到第一個獲勝的boards ，但這意味著什麼？好吧，首先，我們需要考慮winning board會如何獲勝。當一列或一行中的所有數字都被呼叫時，一個board獲勝。因此，我們需要找出每一列和每一行的獲勝時間。

以這種方式分解它讓我們看到這是一個排序問題。我們已經知道號碼被呼叫的順序。您可以使用號碼順序為每一列和每一行分配一個“時間”，代表該列或行獲勝的時間。然後我們只需找到每個板的那個時間的最小值。一旦你有了它，你就知道winning board會什麼時候贏了。然後你按照他們獲勝的時間對boards 進行排序，你就有了答案！ 

WITH boards_ordered_with_call_order AS (
         SELECT *
         FROM boards_ordered
                  JOIN called_numbers_with_row
                       ON called_numbers_with_row.called_number = boards_ordered.value
     ),
     col_win_orders AS (
         SELECT board_no, col_number, max(call_order) AS max_col_call_order
         FROM boards_ordered_with_call_order
         GROUP BY board_no, col_number
     ),
     row_win_orders AS (
         SELECT board_no, row_number, max(call_order) AS max_row_call_order
         FROM boards_ordered_with_call_order
         GROUP BY board_no, row_number
     ),
     row_and_col_win_orders AS (
         SELECT *
         FROM col_win_orders
                  JOIN row_win_orders
                       USING (board_no)
         ORDER BY board_no, row_number, col_number
     ),
     winning_boards AS (
         SELECT board_no,
                min(LEAST(max_col_call_order, max_row_call_order)) AS winning_call_number
         FROM row_and_col_win_orders
         GROUP BY board_no
         ORDER BY 2
         LIMIT 1
     ),
     last_called_number AS (
         SELECT min(called_numbers_with_row.called_number) AS number
         FROM winning_boards
                  JOIN called_numbers_with_row ON (called_numbers_with_row.call_order = winning_call_number)
     )
SELECT board_no, sum(value::integer * called_numbers_with_row.called_number::integer)
FROM boards_ordered_with_call_order
         JOIN winning_boards USING (board_no)
         JOIN called_numbers_with_row ON called_numbers_with_row.call_order = winning_call_number
WHERE boards_ordered_with_call_order.call_order > winning_call_number
GROUP BY 1

這個練習是向我們的演算法工具箱新增其他工具的好方法。

 

SQL 出奇的緊湊

在用 SQL 重寫我們的 javascript 解決方案時，我們對用 SQL 表達解決方案的緊湊性和安全性感到震驚。例如，一個挑戰要求您在潛艇執行一系列上下前進命令後計算它的位置（如果您真的想知道問題的細節，這是第 2 天，第 1 部分）。這是javascript中的解決方案： 

let depth = 0;
let horizontal = 0;
instructions.forEach(({ direction, distance})=> {
  switch (direction) {
    case "up":
      depth -= distance;
      break;
    case "down":
      depth += distance;
      break;
    case "forward":
      horizontal += distance;
      break;
  }
})

console.log(depth * horizontal);

這是 SQL 中的相同解決方案： 

SELECT sum(distance) FILTER ( WHERE direction = 'forward' ) *
       (- sum(distance) FILTER ( WHERE direction = 'up' ) +
        sum(distance) FILTER ( WHERE direction = 'down' )) AS position
FROM day2_input

SQL 實際上在這裡看起來比 javascript 解決方案更具宣告性和緊湊性。我們預計我的幾乎所有 SQL 解決方案都會很笨拙，但驚喜地發現它在這個問題和其他情況下都非常優雅。

 

相關文章