Skip to main content
Septvean's Documents
Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage
  1. Documents
  2. /
  3. Database
  4. /
  5. PostgreSQL
  6. /
  7. PostgreSQL SELECT

PostgreSQL SELECT

PostgreSQL 使用 SELECT 查询数据：

SELECT 字段
FROM 表
WHERE 条件;

“查”不仅是读取数据，还包括：

  • 字段选择与计算
  • 条件筛选
  • 排序和分页
  • 去重
  • 分组统计
  • 多表关联
  • 子查询与 CTE
  • 窗口函数
  • JSONB、数组查询
  • 行锁
  • 执行计划和索引优化

完整语法见 PostgreSQL 18 SELECT。

一、示例表结构

下面继续使用两张核心表。

股票基本信息：

market.security
├── security_id
├── exchange_code
├── code
├── name
├── board
├── industry
├── is_st
├── is_active
├── extra
├── created_at
└── updated_at

日线行情：

market.daily_quote
├── security_id
├── trade_date
├── open_price
├── high_price
├── low_price
├── close_price
├── pre_close
├── volume
├── amount
├── change_rate
├── turnover
└── created_at

其中：

PRIMARY KEY (security_id, trade_date)

二、最基本的查询

查询所有字段 

SELECT *
FROM market.security;

* 表示全部字段。

调试时可以使用，但正式代码建议明确写出字段：

SELECT
    security_id,
    exchange_code,
    code,
    name,
    industry
FROM market.security;

明确字段的优点：

  • 减少网络传输
  • 避免读取大文本和 JSONB
  • 表结构变化时结果更稳定
  • 更容易使用覆盖索引
  • Python 返回结构更清晰

查询单个字段 

SELECT code
FROM market.security;

查询多个字段：

SELECT
    code,
    name,
    industry
FROM market.security;

三、字段别名

使用 AS 设置结果字段名：

SELECT
    code AS stock_code,
    name AS stock_name,
    industry AS stock_industry
FROM market.security;

计算字段也可以设置别名：

SELECT
    security_id,
    trade_date,
    close_price,
    close_price - pre_close AS change_amount
FROM market.daily_quote;

AS 可以省略：

SELECT code stock_code
FROM market.security;

但建议保留 AS，可读性更好。

表别名 

SELECT
    s.code,
    s.name,
    s.industry
FROM market.security AS s;

多表查询时，别名非常重要：

s.code
q.close_price

定义别名后，应使用别名引用表：

FROM market.security AS s

后面使用：

s.code

而不是：

market.security.code

四、SELECT 的逻辑执行顺序

SQL 的书写顺序和逻辑执行顺序不同。

书写顺序：

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

逻辑上可以理解为：

WITH
  ↓
FROM / JOIN
  ↓
WHERE
  ↓
GROUP BY
  ↓
HAVING
  ↓
SELECT
  ↓
DISTINCT
  ↓
UNION / INTERSECT / EXCEPT
  ↓
ORDER BY
  ↓
LIMIT / OFFSET
  ↓
行锁

因此下面通常不能直接使用别名：

SELECT
    close_price - pre_close AS change_amount
FROM market.daily_quote
WHERE change_amount > 0;

因为执行 WHERE 时，change_amount 还没有在 SELECT 阶段产生。

应写成：

SELECT
    close_price - pre_close AS change_amount
FROM market.daily_quote
WHERE close_price - pre_close > 0;

或者使用子查询：

SELECT *
FROM (
    SELECT
        security_id,
        trade_date,
        close_price - pre_close AS change_amount
    FROM market.daily_quote
) AS q
WHERE q.change_amount > 0;

五、使用 WHERE 筛选

等于 

SELECT *
FROM market.security
WHERE code = '600519';

股票代码是字符串，必须保留引号和前导零：

WHERE code = '000001'

不能写成：

WHERE code = 000001

不等于

标准 SQL 写法：

WHERE industry <> '银行'

PostgreSQL 也支持：

WHERE industry != '银行'

推荐使用标准写法 <>。

数值比较 

SELECT *
FROM market.daily_quote
WHERE close_price > 100;

支持：

=    等于
<>   不等于
>    大于
>=   大于等于
<    小于
<=   小于等于

六、逻辑条件

AND

所有条件同时成立：

SELECT *
FROM market.security
WHERE exchange_code = 'SSE'
  AND is_active = true;

OR

任意一个条件成立：

SELECT *
FROM market.security
WHERE code LIKE '00%'
   OR code LIKE '60%';

NOT 

SELECT *
FROM market.security
WHERE NOT is_st;

推荐使用括号明确复杂逻辑：

SELECT *
FROM market.security
WHERE is_active = true
  AND (
      code LIKE '00%'
      OR code LIKE '60%'
  );

否则下面的语句：

WHERE is_active = true
  AND code LIKE '00%'
   OR code LIKE '60%'

等价于：

WHERE (
    is_active = true
    AND code LIKE '00%'
)
OR code LIKE '60%'

可能把非正常状态的 60 开头股票也查询出来。

七、NULL 查询

NULL 表示未知值，不能使用普通等号比较。

错误：

WHERE industry = NULL

正确：

WHERE industry IS NULL

查询非空：

WHERE industry IS NOT NULL

将 NULL 当作可比较值 

SELECT *
FROM market.security
WHERE industry IS DISTINCT FROM '银行';

它会返回：

  • 行业不是银行的记录
  • 行业为 NULL 的记录

而：

WHERE industry <> '银行'

不会返回 industry IS NULL 的记录。

IS DISTINCT FROM 和 IS NOT DISTINCT FROM 可以把 NULL 当作普通值进行安全比较。PostgreSQL 比较运算

布尔字段 

WHERE is_active IS TRUE
WHERE is_active IS FALSE
WHERE is_active IS NULL

如果布尔字段允许 NULL，下面两种写法含义不同：

WHERE NOT is_active

只匹配 false。

WHERE is_active IS NOT TRUE

匹配：

false
NULL

八、范围查询

BETWEEN 

SELECT *
FROM market.daily_quote
WHERE close_price BETWEEN 10 AND 20;

等价于：

WHERE close_price >= 10
  AND close_price <= 20

BETWEEN 包含两个边界。

日期范围 

SELECT *
FROM market.daily_quote
WHERE trade_date BETWEEN
      DATE '2026-01-01'
  AND DATE '2026-12-31';

对于时间戳，更推荐左闭右开：

SELECT *
FROM market.stock_tick
WHERE recorded_at >= TIMESTAMPTZ '2026-09-14 00:00:00+08'
  AND recorded_at <  TIMESTAMPTZ '2026-09-15 00:00:00+08';

这样不会遗漏带小时、分钟和微秒的数据。

九、集合查询：IN 

SELECT *
FROM market.security
WHERE code IN (
    '600519',
    '000001',
    '601318'
);

等价于：

WHERE code = '600519'
   OR code = '000001'
   OR code = '601318'

反向条件：

WHERE code NOT IN (
    '600519',
    '000001'
)

NOT IN 与 NULL 陷阱

如果集合中包含 NULL：

WHERE security_id NOT IN (1, 2, NULL)

可能一行也匹配不到。

子查询排除场景更推荐使用：

SELECT *
FROM market.security AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM market.watchlist AS w
    WHERE w.security_id = s.security_id
);

十、模糊查询

LIKE 

SELECT *
FROM market.security
WHERE code LIKE '60%';

通配符：

符号 含义
% 任意数量字符
_ 任意一个字符

例如：

WHERE name LIKE '%科技%'

查询名称中包含“科技”的股票。

WHERE code LIKE '60____'

表示以 60 开头，后面正好四个字符。

ILIKE

ILIKE 是 PostgreSQL 提供的大小写不敏感匹配：

SELECT *
FROM users
WHERE username ILIKE 'martin%';

中文本身没有大小写区别，因此中文查询通常不需要 ILIKE。

正则表达式 

SELECT *
FROM market.security
WHERE code ~ '^(00|60)[0-9]{4}$';

常用操作符：

操作符 含义
~ 区分大小写匹配
~* 不区分大小写匹配
!~ 区分大小写不匹配
!~* 不区分大小写不匹配

查询名称以 ST 或 *ST 开头：

SELECT *
FROM market.security
WHERE name ~ '^\*?ST';

十一、条件表达式

CASE 

SELECT
    code,
    name,
    CASE
        WHEN code LIKE '60%' THEN '沪市主板'
        WHEN code LIKE '00%' THEN '深市主板'
        WHEN code LIKE '30%' THEN '创业板'
        WHEN code LIKE '68%' THEN '科创板'
        ELSE '其他'
    END AS board_name
FROM market.security;

COALESCE

返回第一个非空值：

SELECT
    code,
    name,
    coalesce(industry, '未分类') AS industry
FROM market.security;

计算时处理空值：

SELECT
    coalesce(sum(amount), 0) AS total_amount
FROM market.daily_quote
WHERE trade_date = DATE '1990-01-01';

NULLIF

两个值相同时返回 NULL：

SELECT
    amount / nullif(volume, 0) AS average_price
FROM market.daily_quote;

如果 volume = 0，nullif(volume, 0) 返回 NULL，可以避免除零错误。

GREATEST 和 LEAST 

SELECT
    greatest(open_price, close_price) AS body_high,
    least(open_price, close_price) AS body_low
FROM market.daily_quote;

十二、排序：ORDER BY

升序：

SELECT
    code,
    name
FROM market.security
ORDER BY code ASC;

降序：

SELECT
    security_id,
    trade_date,
    close_price
FROM market.daily_quote
WHERE security_id = 100
ORDER BY trade_date DESC;

多字段排序：

SELECT
    code,
    name,
    industry
FROM market.security
ORDER BY
    industry ASC,
    code ASC;

只有明确使用 ORDER BY，结果顺序才有保证。没有 ORDER BY 时，PostgreSQL 可以按照它认为最快的顺序返回数据。PostgreSQL 排序

空值排序 

ORDER BY industry ASC NULLS LAST

ORDER BY industry DESC NULLS FIRST

PostgreSQL 默认把 NULL 视为大于普通非空值，因此通常：

ASC  → NULLS LAST
DESC → NULLS FIRST

建议需要稳定结果时明确写出。

稳定排序

分页时不能只按可能重复的字段排序：

ORDER BY trade_date DESC

应补充唯一字段：

ORDER BY
    trade_date DESC,
    security_id DESC

这样即使同一日期有多条记录，顺序也保持确定。

十三、限制返回数量 

SELECT *
FROM market.security
ORDER BY security_id
LIMIT 10;

跳过前20行，再取10行：

SELECT *
FROM market.security
ORDER BY security_id
LIMIT 10
OFFSET 20;

等价的标准 SQL 写法：

SELECT *
FROM market.security
ORDER BY security_id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

LIMIT 必须配合确定性的 ORDER BY，否则不同查询可能返回不同子集。PostgreSQL LIMIT 与 OFFSET

WITH TIES

查询涨幅最高的前10名，并保留与第10名并列的数据：

SELECT
    security_id,
    change_rate
FROM market.daily_quote
WHERE trade_date = DATE '2026-09-14'
ORDER BY change_rate DESC
FETCH FIRST 10 ROWS WITH TIES;

如果第10名有多个相同涨跌幅，实际返回可能超过10行。

十四、大分页问题

下面的深分页：

SELECT *
FROM market.daily_quote
ORDER BY trade_date DESC, security_id DESC
LIMIT 50
OFFSET 1000000;

数据库仍然需要找到并跳过前面大量记录，通常越往后越慢。

更适合使用游标分页，也叫 Keyset Pagination。

第一页：

SELECT *
FROM market.daily_quote
ORDER BY trade_date DESC, security_id DESC
LIMIT 50;

假设最后一行是：

trade_date  = 2026-09-01
security_id = 1200

下一页：

SELECT *
FROM market.daily_quote
WHERE (
    trade_date,
    security_id
) < (
    DATE '2026-09-01',
    1200
)
ORDER BY trade_date DESC, security_id DESC
LIMIT 50;

对应索引：

CREATE INDEX idx_daily_quote_trade_security
ON market.daily_quote (trade_date, security_id);

Keyset 分页的优点：

  • 深分页速度更稳定
  • 不需要跳过大量记录
  • 并发插入时更不容易重复或遗漏

缺点是不能直接任意跳转到第10000页。

十五、去重：DISTINCT

查询所有行业：

SELECT DISTINCT industry
FROM market.security
WHERE industry IS NOT NULL
ORDER BY industry;

多字段去重：

SELECT DISTINCT
    exchange_code,
    industry
FROM market.security;

DISTINCT 针对整个查询结果组合去重，不是只针对第一个字段。

不要用 DISTINCT 掩盖错误的多表关联。如果结果意外重复，应先检查表关系和关联条件。

十六、PostgreSQL 特有的 DISTINCT ON

查询每只股票最新一条行情：

SELECT DISTINCT ON (q.security_id)
    q.security_id,
    q.trade_date,
    q.close_price,
    q.volume
FROM market.daily_quote AS q
ORDER BY
    q.security_id,
    q.trade_date DESC;

执行逻辑：

  1. 按 security_id 分组；
  2. 根据 ORDER BY 决定每组第一行；
  3. 每只股票保留第一行。

DISTINCT ON 中的字段必须对应 ORDER BY 最左侧字段：

DISTINCT ON (security_id)
ORDER BY security_id, trade_date DESC

没有明确排序时，“保留哪一行”是不确定的。

十七、聚合函数

常用聚合函数：

函数 作用
count(*) 统计行数
count(column) 统计非空值数量
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
string_agg() 字符串聚合
array_agg() 数组聚合
jsonb_agg() JSONB数组聚合

行数统计 

SELECT count(*)
FROM market.security;

统计非空行业：

SELECT count(industry)
FROM market.security;

两者不同：

count(*)        统计所有行
count(industry) 只统计 industry 非空的行

行情统计 

SELECT
    max(close_price) AS highest_close,
    min(close_price) AS lowest_close,
    avg(close_price) AS average_close,
    sum(volume) AS total_volume,
    sum(amount) AS total_amount
FROM market.daily_quote
WHERE security_id = 100;

除 count 外，大多数聚合函数在没有输入行时返回 NULL，而不是零：

SELECT coalesce(sum(amount), 0)
FROM market.daily_quote
WHERE trade_date = DATE '1900-01-01';

PostgreSQL 聚合函数

十八、分组：GROUP BY

统计每个行业的股票数量：

SELECT
    industry,
    count(*) AS stock_count
FROM market.security
WHERE industry IS NOT NULL
GROUP BY industry
ORDER BY stock_count DESC;

按交易日统计成交额：

SELECT
    trade_date,
    sum(amount) AS total_amount,
    sum(volume) AS total_volume,
    avg(change_rate) AS average_change_rate
FROM market.daily_quote
GROUP BY trade_date
ORDER BY trade_date DESC;

使用 GROUP BY 后，SELECT 中的普通字段必须：

  • 出现在 GROUP BY 中；
  • 或者放在聚合函数内；
  • 或者能够由分组键函数依赖确定。

错误：

SELECT
    trade_date,
    security_id,
    sum(amount)
FROM market.daily_quote
GROUP BY trade_date;

一个交易日对应多只股票，数据库无法确定应该返回哪个 security_id。

十九、WHERE 和 HAVING

WHERE 过滤分组前的明细行：

SELECT
    trade_date,
    sum(amount) AS total_amount
FROM market.daily_quote
WHERE amount > 0
GROUP BY trade_date;

HAVING 过滤分组后的聚合结果：

SELECT
    trade_date,
    sum(amount) AS total_amount
FROM market.daily_quote
GROUP BY trade_date
HAVING sum(amount) > 100000000000
ORDER BY trade_date DESC;

区别：

WHERE  → 过滤原始记录
HAVING → 过滤分组结果

能放在 WHERE 的条件不要无意义地放在 HAVING，因为尽早过滤通常能减少参与聚合的数据。

二十、条件聚合：FILTER

统计每天上涨、下跌和平盘数量：

SELECT
    trade_date,

    count(*) FILTER (
        WHERE change_rate > 0
    ) AS rising_count,

    count(*) FILTER (
        WHERE change_rate < 0
    ) AS falling_count,

    count(*) FILTER (
        WHERE change_rate = 0
    ) AS flat_count,

    count(*) AS total_count
FROM market.daily_quote
GROUP BY trade_date
ORDER BY trade_date DESC;

这很适合计算市场情绪中的：

  • 上涨家数
  • 下跌家数
  • 平盘家数
  • 涨跌比

计算上涨比例：

SELECT
    trade_date,
    count(*) FILTER (WHERE change_rate > 0)::numeric
        / nullif(count(*), 0) AS rising_ratio
FROM market.daily_quote
GROUP BY trade_date;

这里把计数转换为 numeric，避免整数除法截断。

二十一、多表关联

1. INNER JOIN

查询股票名称和行情：

SELECT
    s.code,
    s.name,
    q.trade_date,
    q.close_price,
    q.volume
FROM market.security AS s
INNER JOIN market.daily_quote AS q
    ON q.security_id = s.security_id
WHERE s.code = '600519'
ORDER BY q.trade_date DESC;

INNER JOIN 只返回两边都匹配的记录。

INNER 可以省略：

JOIN market.daily_quote AS q

2. LEFT JOIN

查询所有股票，以及指定交易日的行情：

SELECT
    s.code,
    s.name,
    q.trade_date,
    q.close_price
FROM market.security AS s
LEFT JOIN market.daily_quote AS q
    ON q.security_id = s.security_id
   AND q.trade_date = DATE '2026-09-14'
ORDER BY s.code;

即使某只股票当天没有行情，也会返回股票信息，只是行情字段为 NULL。

LEFT JOIN 条件位置陷阱

正确：

LEFT JOIN market.daily_quote AS q
    ON q.security_id = s.security_id
   AND q.trade_date = DATE '2026-09-14'

如果写成：

LEFT JOIN market.daily_quote AS q
    ON q.security_id = s.security_id
WHERE q.trade_date = DATE '2026-09-14'

没有行情的股票，其 q.trade_date 为 NULL，会被 WHERE 过滤掉，结果实际上接近 INNER JOIN。

原则：

  • 决定右表如何匹配的条件放 ON
  • 对最终结果整体筛选的条件放 WHERE

3. RIGHT JOIN

返回右表全部记录：

SELECT *
FROM table_a AS a
RIGHT JOIN table_b AS b
    ON b.id = a.id;

通常可以交换表顺序，改写成更容易理解的 LEFT JOIN。

4. FULL JOIN

返回两边全部数据：

SELECT *
FROM table_a AS a
FULL JOIN table_b AS b
    ON b.id = a.id;

适合：

  • 两个数据源差异核对
  • 找出双方缺失数据
  • 数据迁移检查

5. CROSS JOIN

笛卡尔积：

SELECT *
FROM table_a
CROSS JOIN table_b;

如果 A 有100行、B有200行，结果有：

100 × 200 = 20,000 行

多表查询漏写关联条件，可能意外产生巨大的笛卡尔积。

6. 不建议 NATURAL JOIN

NATURAL JOIN 会自动使用两张表中所有同名字段关联。以后新增同名字段时，查询含义可能悄悄改变。

推荐明确写：

JOIN ... ON ...

或者：

JOIN ... USING (security_id)

PostgreSQL 对各类连接的行为有完整说明。表表达式与 JOIN

二十二、EXISTS 子查询

查询有行情数据的股票：

SELECT
    s.security_id,
    s.code,
    s.name
FROM market.security AS s
WHERE EXISTS (
    SELECT 1
    FROM market.daily_quote AS q
    WHERE q.security_id = s.security_id
);

查询没有行情数据的股票：

SELECT
    s.security_id,
    s.code,
    s.name
FROM market.security AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM market.daily_quote AS q
    WHERE q.security_id = s.security_id
);

SELECT 1 表示只关心记录是否存在，不需要子查询返回实际字段。

二十三、标量子查询

查询每只股票最后交易日：

SELECT
    s.code,
    s.name,
    (
        SELECT max(q.trade_date)
        FROM market.daily_quote AS q
        WHERE q.security_id = s.security_id
    ) AS last_trade_date
FROM market.security AS s;

标量子查询必须返回：

  • 零行：结果为 NULL
  • 一行：正常使用
  • 多行：报错

需要返回多行时，不能把子查询直接当成单个字段。

二十四、LATERAL 查询

查询每只股票最新一条行情：

SELECT
    s.code,
    s.name,
    latest.trade_date,
    latest.close_price,
    latest.volume
FROM market.security AS s
LEFT JOIN LATERAL (
    SELECT
        q.trade_date,
        q.close_price,
        q.volume
    FROM market.daily_quote AS q
    WHERE q.security_id = s.security_id
    ORDER BY q.trade_date DESC
    LIMIT 1
) AS latest ON true
ORDER BY s.code;

LATERAL 允许右侧子查询引用左侧当前行：

q.security_id = s.security_id

它很适合：

  • 每个用户最新一条订单
  • 每只股票最近一条行情
  • 每个分类排名前几条记录
  • 每个父记录的局部查询

二十五、CTE：WITH

CTE 可以把复杂查询拆成多个可读步骤：

WITH active_security AS (
    SELECT
        security_id,
        code,
        name
    FROM market.security
    WHERE is_active = true
),
latest_trade_date AS (
    SELECT max(trade_date) AS trade_date
    FROM market.daily_quote
)
SELECT
    s.code,
    s.name,
    q.close_price,
    q.change_rate
FROM active_security AS s
CROSS JOIN latest_trade_date AS d
JOIN market.daily_quote AS q
    ON q.security_id = s.security_id
   AND q.trade_date = d.trade_date
ORDER BY q.change_rate DESC;

CTE 的主要价值：

  • 拆解复杂逻辑
  • 减少重复子查询
  • 提高可读性
  • 便于逐步调试
  • 支持递归查询

PostgreSQL 18 中，普通无副作用且只使用一次的 CTE，优化器可能将它合并到主查询中。因此不要再简单认为“CTE 一定会物化”。

需要明确物化：

WITH data AS MATERIALIZED (
    SELECT ...
)
SELECT ...
FROM data;

允许合并优化：

WITH data AS NOT MATERIALIZED (
    SELECT ...
)
SELECT ...
FROM data;

只有在执行计划证明有必要时，才应主动控制物化行为。

二十六、集合运算

UNION

合并结果并去重：

SELECT security_id
FROM market.watchlist_a

UNION

SELECT security_id
FROM market.watchlist_b;

UNION ALL

合并结果但保留重复：

SELECT security_id
FROM market.watchlist_a

UNION ALL

SELECT security_id
FROM market.watchlist_b;

如果业务不需要去重，优先使用 UNION ALL，避免额外去重开销。

INTERSECT

查询两边共有的数据：

SELECT security_id
FROM market.watchlist_a

INTERSECT

SELECT security_id
FROM market.watchlist_b;

EXCEPT

查询第一边有、第二边没有的数据：

SELECT security_id
FROM market.watchlist_a

EXCEPT

SELECT security_id
FROM market.watchlist_b;

两边必须具有：

  • 相同字段数量
  • 对应字段类型兼容

最终排序应写在集合运算之后。

二十七、窗口函数

聚合函数会把多行合并为一行；窗口函数在保留明细行的同时进行跨行计算。

基本结构：

函数() OVER (
    PARTITION BY 分组字段
    ORDER BY 排序字段
    ROWS ...
)

PostgreSQL 窗口函数

1. 行号 

SELECT
    security_id,
    trade_date,
    close_price,
    row_number() OVER (
        PARTITION BY security_id
        ORDER BY trade_date DESC
    ) AS row_num
FROM market.daily_quote;

每只股票内部从最新日期开始编号。

2. 每只股票最新行情

窗口函数不能直接放在同级 WHERE 中：

-- 错误思路
WHERE row_number() OVER (...) = 1

应使用 CTE：

WITH ranked AS (
    SELECT
        q.*,
        row_number() OVER (
            PARTITION BY security_id
            ORDER BY trade_date DESC
        ) AS row_num
    FROM market.daily_quote AS q
)
SELECT
    security_id,
    trade_date,
    close_price,
    volume
FROM ranked
WHERE row_num = 1;

3. 排名 

SELECT
    security_id,
    trade_date,
    change_rate,
    rank() OVER (
        PARTITION BY trade_date
        ORDER BY change_rate DESC
    ) AS market_rank
FROM market.daily_quote;

区别：

函数 并列后是否跳号
row_number() 不并列，每行唯一编号
rank() 并列，后面跳号
dense_rank() 并列，后面不跳号

4. 上一交易日收盘价 

SELECT
    security_id,
    trade_date,
    close_price,
    lag(close_price) OVER (
        PARTITION BY security_id
        ORDER BY trade_date
    ) AS previous_close
FROM market.daily_quote;

计算涨跌幅：

WITH quote_change AS (
    SELECT
        security_id,
        trade_date,
        close_price,
        lag(close_price) OVER (
            PARTITION BY security_id
            ORDER BY trade_date
        ) AS previous_close
    FROM market.daily_quote
)
SELECT
    security_id,
    trade_date,
    close_price,
    previous_close,
    close_price / nullif(previous_close, 0) - 1
        AS change_rate
FROM quote_change;

5. 后一个交易日 

lead(close_price) OVER (
    PARTITION BY security_id
    ORDER BY trade_date
)

6. 5日、20日、60日均线 

SELECT
    security_id,
    trade_date,
    close_price,

    avg(close_price) OVER (
        PARTITION BY security_id
        ORDER BY trade_date
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS ma5,

    avg(close_price) OVER (
        PARTITION BY security_id
        ORDER BY trade_date
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
    ) AS ma20,

    avg(close_price) OVER (
        PARTITION BY security_id
        ORDER BY trade_date
        ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
    ) AS ma60

FROM market.daily_quote
ORDER BY security_id, trade_date;

ROWS BETWEEN 59 PRECEDING AND CURRENT ROW 表示：

当前行 + 前59行 = 60条交易记录

前59个交易日不足时，PostgreSQL 会使用当前已有记录计算平均值。如果要求必须满60日，可以同时计算窗口记录数，再进行判断。

WITH indicators AS (
    SELECT
        security_id,
        trade_date,
        close_price,

        count(*) OVER (
            PARTITION BY security_id
            ORDER BY trade_date
            ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
        ) AS sample_count,

        avg(close_price) OVER (
            PARTITION BY security_id
            ORDER BY trade_date
            ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
        ) AS ma60

    FROM market.daily_quote
)
SELECT
    security_id,
    trade_date,
    close_price,
    CASE
        WHEN sample_count = 60 THEN ma60
        ELSE NULL
    END AS ma60
FROM indicators;

二十八、日期与时间查询

当前日期：

SELECT CURRENT_DATE;

当前时间：

SELECT now();

最近7天：

SELECT *
FROM market.daily_quote
WHERE trade_date >= CURRENT_DATE - 7;

最近30天时间戳：

SELECT *
FROM market.stock_tick
WHERE recorded_at >= now() - interval '30 days';

按月分组：

SELECT
    date_trunc('month', created_at) AS month,
    count(*) AS row_count
FROM market.security
GROUP BY date_trunc('month', created_at)
ORDER BY month;

提取年月：

SELECT
    extract(year FROM trade_date) AS year,
    extract(month FROM trade_date) AS month
FROM market.daily_quote;

保持索引友好的日期条件

不推荐：

WHERE date_trunc('year', created_at)
      = TIMESTAMPTZ '2026-01-01 00:00:00+00'

它对字段执行函数，普通 created_at 索引可能难以直接利用。

推荐：

WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2027-01-01 00:00:00+00'

二十九、JSONB 查询

假设 extra 内容：

{
  "province": "贵州",
  "company_type": "国企",
  "concepts": ["白酒", "消费"]
}

获取 JSON 值 

SELECT extra->'concepts'
FROM market.security;

-> 返回 JSONB。

获取文本：

SELECT extra->>'province'
FROM market.security;

->> 返回 text。

按 JSON 字段筛选 

SELECT *
FROM market.security
WHERE extra->>'province' = '贵州';

包含查询：

SELECT *
FROM market.security
WHERE extra @> '{"company_type": "国企"}'::jsonb;

判断键是否存在：

SELECT *
FROM market.security
WHERE extra ? 'province';

对应 GIN 索引：

CREATE INDEX idx_security_extra_gin
ON market.security
USING gin (extra);

如果 province 是稳定并且频繁查询的属性，更适合设计成普通字段，而不是长期藏在 JSONB 中。

三十、数组查询

假设：

concepts text[]

查询包含“机器人”：

SELECT *
FROM market.security
WHERE '机器人' = ANY(concepts);

数组包含指定元素：

SELECT *
FROM market.security
WHERE concepts @> ARRAY['机器人'];

同时包含多个题材：

WHERE concepts @> ARRAY['机器人', '人工智能'];

任意一个重叠：

WHERE concepts && ARRAY['机器人', '人工智能'];

对应 GIN 索引：

CREATE INDEX idx_security_concepts_gin
ON market.security
USING gin (concepts);

如果题材还包含分类、来源、更新时间等信息，应使用题材表和多对多关联表，而不是数组。

三十一、行锁查询

普通 SELECT 不会锁住记录阻止其他事务修改。

如果查询后需要基于当前值继续修改，可以使用：

BEGIN;

SELECT *
FROM account
WHERE account_id = 1
FOR UPDATE;

UPDATE account
SET balance = balance - 100
WHERE account_id = 1;

COMMIT;

常用模式：

FOR UPDATE
FOR NO KEY UPDATE
FOR SHARE
FOR KEY SHARE

不等待锁：

FOR UPDATE NOWAIT

跳过已被其他事务锁定的任务：

SELECT *
FROM task
WHERE status = 'pending'
ORDER BY task_id
FOR UPDATE SKIP LOCKED
LIMIT 10;

SKIP LOCKED 适合任务队列，不适合普通报表，因为它可能有意跳过部分数据。

三十二、执行计划：EXPLAIN

查看数据库准备如何执行查询：

EXPLAIN
SELECT *
FROM market.security
WHERE exchange_code = 'SSE'
  AND code = '600519';

执行并显示真实统计：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM market.security
WHERE exchange_code = 'SSE'
  AND code = '600519';

常见节点：

节点 含义
Seq Scan 顺序扫描表
Index Scan 使用索引并回表
Index Only Scan 尽量只读取索引
Bitmap Index Scan 位图索引扫描
Bitmap Heap Scan 按位图访问表页
Nested Loop 嵌套循环连接
Hash Join 哈希连接
Merge Join 归并连接
Sort 排序
Aggregate 聚合
WindowAgg 窗口函数

重点查看：

  • cost
  • rows
  • actual rows
  • actual time
  • loops
  • Buffers
  • 是否出现磁盘排序
  • 预估行数与实际行数是否相差很大

EXPLAIN ANALYZE 会真正执行语句。用于 SELECT 时一般只是读取数据，但查询中如果调用具有副作用的函数，仍需谨慎。PostgreSQL EXPLAIN

三十三、常见查询与索引对应关系

根据交易所和代码查股票 

WHERE exchange_code = ?
  AND code = ?

对应：

UNIQUE (exchange_code, code)

查询一只股票的历史行情 

WHERE security_id = ?
ORDER BY trade_date DESC

对应：

PRIMARY KEY (security_id, trade_date)

B-tree 可以反向扫描，因此通常不必仅为了 trade_date DESC 重复建立相同字段的降序索引。

查询某个交易日全部行情 

WHERE trade_date = ?

对应：

CREATE INDEX idx_daily_quote_trade_date
ON market.daily_quote (trade_date);

日期和股票稳定分页 

ORDER BY trade_date DESC, security_id DESC

对应：

CREATE INDEX idx_daily_quote_trade_security
ON market.daily_quote (trade_date, security_id);

JSONB 包含查询 

WHERE extra @> ...

通常考虑 GIN。

PostgreSQL 提供 B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN 等索引类型。PostgreSQL 索引类型

三十四、查询性能常见问题

1. SELECT *

读取了不需要的大文本、JSONB 或二进制字段。

2. 没有 ORDER BY

误以为数据库会永久按主键或插入顺序返回。

3. 深度 OFFSET

页码越大，跳过的数据越多。

4. 在索引字段上执行函数 

WHERE lower(name) = 'abc'

普通 name 索引未必能直接使用。可以考虑表达式索引：

CREATE INDEX idx_security_lower_name
ON market.security (lower(name));

5. 前置通配符 

WHERE name LIKE '%科技%'

普通 B-tree 通常难以用于这种任意位置包含查询，可根据需求评估 pg_trgm 和 GIN/GiST。

6. 类型不一致 

WHERE code::integer = 1

既破坏前导零语义，也可能影响普通代码索引使用。

7. 错误关联产生重复

不要用 DISTINCT 暂时遮住，应检查关联关系。

8. LEFT JOIN 条件放错位置

右表条件放进 WHERE 后，可能失去外连接效果。

9. 精确 count(*) 大表很慢

PostgreSQL 的精确全表 count(*) 通常需要扫描整张表或覆盖全部记录的索引，其工作量与数据规模相关。

10. N+1 查询

先查询100只股票，再为每只股票单独查询行情，会产生101次数据库请求。

应考虑：

  • JOIN
  • LATERAL
  • 批量 IN
  • SQLAlchemy 预加载

三十五、Python psycopg 3 查询 

import psycopg


sql = """
SELECT
    security_id,
    exchange_code,
    code,
    name,
    industry
FROM market.security
WHERE exchange_code = %s
  AND code = %s
"""


with psycopg.connect(
    "postgresql://postgres:password@localhost:5432/stock"
) as connection:
    with connection.cursor() as cursor:
        cursor.execute(
            sql,
            ("SSE", "600519"),
        )

        row = cursor.fetchone()

        if row is None:
            print("没有找到数据")
        else:
            print(row)

查询多行：

cursor.execute(
    """
    SELECT code, name
    FROM market.security
    WHERE exchange_code = %s
    ORDER BY code
    """,
    ("SSE",),
)

rows = cursor.fetchall()

单参数元组必须保留逗号：

("SSE",)

大结果集不要直接 fetchall()

对于大量数据，可以分批读取：

while rows := cursor.fetchmany(1000):
    for row in rows:
        print(row)

或者使用服务端游标：

with connection.cursor(name="security_cursor") as cursor:
    cursor.execute(
        """
        SELECT *
        FROM market.daily_quote
        ORDER BY security_id, trade_date
        """
    )

    cursor.itersize = 1000

    for row in cursor:
        print(row)

禁止字符串拼接

不推荐：

sql = f"""
SELECT *
FROM market.security
WHERE code = '{code}'
"""

推荐参数化：

cursor.execute(
    """
    SELECT *
    FROM market.security
    WHERE code = %s
    """,
    (code,),
)

参数只能替代数据值，不能直接替代表名和字段名。

动态排序字段应使用白名单：

allowed_columns = {
    "code": "code",
    "name": "name",
    "created_at": "created_at",
}

order_column = allowed_columns.get(user_input)

if order_column is None:
    raise ValueError("非法排序字段")

不能把用户输入直接拼接到 ORDER BY。

三十六、SQLAlchemy 2.0 查询

基本查询：

from sqlalchemy import select


statement = (
    select(Security)
    .where(
        Security.exchange_code == "SSE",
        Security.code == "600519",
    )
)

security = session.execute(
    statement
).scalar_one_or_none()

查询指定字段：

statement = (
    select(
        Security.code,
        Security.name,
        Security.industry,
    )
    .where(Security.is_active.is_(True))
    .order_by(Security.code)
)

rows = session.execute(statement).all()

关联查询：

statement = (
    select(
        Security.code,
        Security.name,
        DailyQuote.trade_date,
        DailyQuote.close_price,
    )
    .join(
        DailyQuote,
        DailyQuote.security_id == Security.security_id,
    )
    .where(Security.code == "600519")
    .order_by(DailyQuote.trade_date.desc())
)

分页：

statement = (
    select(Security)
    .where(Security.is_active.is_(True))
    .order_by(Security.security_id)
    .limit(50)
)

三十七、完整实战：最新行情与均线

查询每只正常股票的最新行情，并计算5日、20日和60日均线：

WITH indicators AS (
    SELECT
        q.security_id,
        q.trade_date,
        q.close_price,
        q.volume,
        q.change_rate,

        avg(q.close_price) OVER (
            PARTITION BY q.security_id
            ORDER BY q.trade_date
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) AS ma5,

        avg(q.close_price) OVER (
            PARTITION BY q.security_id
            ORDER BY q.trade_date
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS ma20,

        avg(q.close_price) OVER (
            PARTITION BY q.security_id
            ORDER BY q.trade_date
            ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
        ) AS ma60,

        count(*) OVER (
            PARTITION BY q.security_id
            ORDER BY q.trade_date
            ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
        ) AS ma60_count

    FROM market.daily_quote AS q
),
latest AS (
    SELECT
        i.*,
        row_number() OVER (
            PARTITION BY i.security_id
            ORDER BY i.trade_date DESC
        ) AS row_num
    FROM indicators AS i
)
SELECT
    s.exchange_code,
    s.code,
    s.name,
    s.industry,

    l.trade_date,
    l.close_price,
    l.volume,
    l.change_rate,

    round(l.ma5, 4) AS ma5,
    round(l.ma20, 4) AS ma20,

    CASE
        WHEN l.ma60_count = 60
        THEN round(l.ma60, 4)
        ELSE NULL
    END AS ma60

FROM latest AS l
JOIN market.security AS s
    ON s.security_id = l.security_id

WHERE l.row_num = 1
  AND s.is_active IS TRUE
  AND s.is_st IS FALSE

ORDER BY
    l.change_rate DESC NULLS LAST,
    s.code;

这条查询综合使用了：

  • CTE
  • 窗口函数
  • 移动平均
  • 每组最新一行
  • 多表关联
  • 布尔筛选
  • 条件表达式
  • 空值排序

三十八、查询检查清单

执行查询前后检查：

  • 是否只查询需要的字段？
  • 股票代码是否按字符串处理？
  • NULL 是否使用 IS NULL？
  • AND 和 OR 是否加了清晰括号？
  • 日期和时间范围是否使用了正确边界？
  • 分页是否有稳定的 ORDER BY？
  • 深分页是否应该改为 Keyset？
  • LEFT JOIN 的右表条件是否应放在 ON？
  • 关联条件是否会产生一对多重复？
  • 是否错误地使用 DISTINCT 掩盖重复？
  • WHERE 和 HAVING 是否使用正确？
  • count(*) 与 count(column) 是否区分？
  • 聚合无数据时是否处理了 NULL？
  • 窗口函数是否需要明确 ROWS 范围？
  • 是否出现 N+1 查询？
  • 参数是否使用参数化绑定？
  • 筛选、关联、排序字段是否有合理索引？
  • 是否用 EXPLAIN (ANALYZE, BUFFERS) 验证？
  • 查询是否一次返回了过多数据？

“查”的完整思路是：

明确需要什么结果
   ↓
确定数据来自哪些表
   ↓
JOIN 形成数据来源
   ↓
WHERE 过滤明细
   ↓
GROUP BY 聚合
   ↓
HAVING 过滤分组
   ↓
SELECT 计算输出字段
   ↓
DISTINCT 去重
   ↓
ORDER BY 确定顺序
   ↓
LIMIT / Keyset 控制数量
   ↓
EXPLAIN 验证性能

核心原则：

查询不是“把数据全部拿出来再处理”，而是尽量让 PostgreSQL 在最靠近数据的地方完成筛选、关联、聚合和排序，只把真正需要的结果返回给应用程序。