【Postgre】连续值变量行转列的方法

大米2H

于 2025-06-23 11:11:53 发布

阅读量412

点赞数 3

CC 4.0 BY-SA版权

文章标签： postgresql

本文链接：https://blog.csdn.net/m0_57446978/article/details/148842298

PostgreSQL `JOIN LATERAL` 使用总结

🔍 什么是 `LATERAL`

LATERAL 关键字允许子查询或函数使用 主查询中当前行的字段。
它常用于：
- 对每一行生成子表
- 动态展开数组、JSON
- 使用生成器函数（如 generate_series）

表A

在这里插入图片描述

表B

在这里插入图片描述

🧠 使用场景举例

1. 按天展开时间范围（节假日等）

SELECT 
  a.startdate,
  a.enddate,
  gs.date
FROM holidays a
JOIN LATERAL generate_series(a.startdate, a.enddate, interval '1 day') AS gs(date) 
  ON TRUE;

2. 拆分文本内容

SELECT 
  a.id,
  word
FROM articles a
JOIN LATERAL unnest(string_to_array(a.content, ' ')) AS t(word)
  ON TRUE;

3. 使用 JSON 拆分内容

SELECT 
  p.id,
  json_array_elements_text(p.tags) AS tag
FROM products p;

如果用在 FROM 子句中并需要引用 p，则需写成：
FROM products p,
LATERAL json_array_elements_text(p.tags) AS tag

🧱 原理解释

正常的 JOIN 不允许子表引用外层字段。
加上 LATERAL，就允许子表“看到”主表当前行的字段。
执行顺序类似于“对主表每一行，运行一次子查询”。

⚠️ 注意事项

情况	是否需要 `LATERAL`
子查询中引用主查询字段	✅ 需要
子查询中完全独立，不引用外层	❌ 不需要
使用 `generate_series`, `unnest`, `json_agg` 等依赖主表	✅ 必须加 LATERAL

🔁 对应 Python 类比

for row in rows:
    for x in generate(row.start, row.end):
        print(x)

就像对每一行运行 generate()，展开成子表。

📌 常见函数搭配

generate_series(start, end, step)
unnest(array)
json_array_elements(json)
regexp_split_to_table(string, pattern)

✅ 示例：标记“bridgeday”

SELECT 
  gs.date,
  a.countrycode,
  CASE 
    WHEN a.needbridgeday AND gs.date = ANY(string_to_array(replace(replace(a.bridgedays, '{', ''), '}', ''), ',')::date[]) 
    THEN 'Y' 
    ELSE NULL 
  END AS bridgeday_flag
FROM holidays a
JOIN LATERAL generate_series(a.startdate, a.enddate, interval '1 day') AS gs(date) 
  ON TRUE;