抓取

PostgreSQL 統計摘要與視窗函式

Michel Semaan

Data Scientist

四個函式

相對位置

LAG(column, n) 回傳當前列往前第 n 列的 column 值
LEAD(column, n) 回傳當前列往後第 n 列的 column 值

絕對位置

FIRST_VALUE(column) 回傳整張表或分割區中的第一個值
LAST_VALUE(column) 回傳整張表或分割區中的最後一個值

LEAD

查詢

WITH Hosts AS (
  SELECT DISTINCT Year, City
  FROM Summer_Medals)

SELECT
  Year, City,
  LEAD(City, 1) OVER (ORDER BY Year ASC)
    AS Next_City,
  LEAD(City, 2) OVER (ORDER BY Year ASC)
    AS After_Next_City
FROM Hosts
ORDER BY Year ASC;

結果

| Year | City      | Next_City | After_Next_City |
|------|-----------|-----------|-----------------|
| 1896 | Athens    | Paris     | St Louis        |
| 1900 | Paris     | St Louis  | London          |
| 1904 | St Louis  | London    | Stockholm       |
| 1908 | London    | Stockholm | Antwerp         |
| 1912 | Stockholm | Antwerp   | Paris           |
| ...  | ...       | ...       | ...             |

FIRST_VALUE 與 LAST_VALUE

查詢

SELECT
  Year, City,
  FIRST_VALUE(City) OVER
    (ORDER BY Year ASC) AS First_City,
  LAST_VALUE(City) OVER (
   ORDER BY Year ASC
   RANGE BETWEEN
     UNBOUNDED PRECEDING AND
     UNBOUNDED FOLLOWING
  ) AS Last_City
FROM Hosts
ORDER BY Year ASC;

結果

| Year | City      | First_City | Last_City       |
|------|-----------|------------|-----------------|
| 1896 | Athens    | Athens     | London          |
| 1900 | Paris     | Athens     | London          |
| 1904 | St Louis  | Athens     | London          |
| 1908 | London    | Athens     | London          |
| 1912 | Stockholm | Athens     | London          |

預設視窗從表或分割區的開頭開始，到當前列結束
RANGE BETWEEN ... 子句把視窗延伸到表或分割區的最後

用 LEAD 分割區

未使用 PARTITION BY 的 LEAD(Champion, 1)

| Year | Event        | Champion | Next_Champion |
|------|--------------|----------|---------------|
| 2004 | Discus Throw | LTU      | EST           |
| 2008 | Discus Throw | EST      | GER           |
| 2012 | Discus Throw | GER      | SWE           |
| 2004 | Triple Jump  | SWE      | POR           |
| 2008 | Triple Jump  | POR      | USA           |
| 2012 | Triple Jump  | USA      | null          |

使用 PARTITION BY Event 的 LEAD(Champion, 1)

| Year | Event        | Champion | Next_Champion |
|------|--------------|----------|---------------|
| 2004 | Discus Throw | LTU      | EST           |
| 2008 | Discus Throw | EST      | GER           |
| 2012 | Discus Throw | GER      | null          |
| 2004 | Triple Jump  | SWE      | POR           |
| 2008 | Triple Jump  | POR      | USA           |
| 2012 | Triple Jump  | USA      | null          |

用 FIRST_VALUE 分割區

未使用 PARTITION BY Event 的 FIRST_VALUE(Champion)

| Year | Event        | Champion | First_Champion |
|------|--------------|----------|----------------|
| 2004 | Discus Throw | LTU      | LTU            |
| 2008 | Discus Throw | EST      | LTU            |
| 2012 | Discus Throw | GER      | LTU            |
| 2004 | Triple Jump  | SWE      | LTU            |
| 2008 | Triple Jump  | POR      | LTU            |
| 2012 | Triple Jump  | USA      | LTU            |

使用 PARTITION BY Event 的 FIRST_VALUE(Champion)

| Year | Event        | Champion | First_Champion |
|------|--------------|----------|----------------|
| 2004 | Discus Throw | LTU      | LTU            |
| 2008 | Discus Throw | EST      | LTU            |
| 2012 | Discus Throw | GER      | LTU            |
| 2004 | Triple Jump  | SWE      | SWE            |
| 2008 | Triple Jump  | POR      | SWE            |
| 2012 | Triple Jump  | USA      | SWE            |

一起來練習吧！

PostgreSQL 統計摘要與視窗函式