Heap使用Postgres SQL後的經驗教訓

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

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);

\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);

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

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);

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