Vec<T>
8 methods一种连续可增长的数组类型，写作 Vec<T>，读作 'vector'。
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)构造一个新的空 Vec。在向其中推入元素之前，该向量不会分配内存。
Returns
bigint
Example
sql
SELECT
name,
department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;RANK() OVER (PARTITION BY ... ORDER BY ...)构造一个新的空 Vec，至少具有指定的容量。
Returns
bigint
Example
sql
-- Tied rows get the same rank; next rank skips
-- 1, 2, 2, 4
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM players;DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)将一个元素追加到集合的末尾。
Returns
bigint
Example
sql
-- Tied rows get the same rank; next rank is consecutive
-- 1, 2, 2, 3
SELECT
name,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players;LAG(expr, offset, default) OVER (PARTITION BY ... ORDER BY ...)移除并返回向量的最后一个元素，如果为空则返回 None。
Parameters
| Name | Type | Description |
|---|---|---|
| expr | any | Column or expression. |
| offset | int | Rows to look back (default 1). |
| default | any | Value when out of partition (default NULL). |
Returns
any
Example
sql
SELECT
day,
sales,
LAG(sales, 1, 0) OVER (ORDER BY day) AS prev_sales,
sales - LAG(sales, 1, 0) OVER (ORDER BY day) AS diff
FROM daily_sales;LEAD(expr, offset, default) OVER (PARTITION BY ... ORDER BY ...)返回向量中元素的数量，也称为其“长度”。
Parameters
| Name | Type | Description |
|---|---|---|
| expr | any | Column or expression. |
| offset | int | Rows to look ahead (default 1). |
| default | any | Value when out of partition (default NULL). |
Returns
any
Example
sql
SELECT
day,
sales,
LEAD(sales, 1) OVER (ORDER BY day) AS next_sales
FROM daily_sales;NTILE(n) OVER (PARTITION BY ... ORDER BY ...)根据索引类型返回对元素或子切片的引用，越界时返回 None。
Parameters
| Name | Type | Description |
|---|---|---|
| index | int | 要获取的元素的索引。 |
Returns
int
Example
sql
-- Split employees into 4 salary quartiles
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;SUM(expr) OVER (PARTITION BY ... ORDER BY ...)返回一个迭代器，按顺序产出向量元素的引用。
Parameters
| Name | Type | Description |
|---|---|---|
| expr | numeric | Column or expression. |
Returns
numeric
Example
sql
-- Running total per department, ordered by date
SELECT
date,
department,
amount,
SUM(amount) OVER (
PARTITION BY department
ORDER BY date
) AS running_total
FROM transactions;
-- Partition total (no ORDER BY):
SELECT
department,
amount,
SUM(amount) OVER (PARTITION BY department) AS dept_total
FROM transactions;AVG(expr) OVER (PARTITION BY ... ORDER BY ...)用迭代器的内容扩展集合。
Parameters
| Name | Type | Description |
|---|---|---|
| iter | numeric | 要追加其项的迭代器。 |
Returns
numeric
Example
sql
-- 3-day moving average
SELECT
day,
sales,
AVG(sales) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM daily_sales;