的窗口函数、CTE、JSON 查询与 PostgreSQL 高级 SQL 实战

在前几篇中，我们已经逐步完成了：

tio-boot + jOOQ 基础整合
事务管理
连接池整合
Codegen 类型安全
Record / POJO CRUD
批量、分页、动态 SQL
PostgreSQL UPSERT、returning
多表关联查询、DTO 投影、聚合统计与视图封装

到这里，已经具备了完整的“常规工程级数据库访问能力”。

但如果项目进入真正的复杂业务阶段，只会单表 CRUD 还远远不够。这时候最常见、也最能体现 PostgreSQL 能力的高级特性，往往是：

JSONB：半结构化数据存储与检索
CTE：把复杂 SQL 拆成可组合、可读的查询块
窗口函数：排行、TopN、滚动统计、分页总数
PostgreSQL 高级表达式：returning、on conflict、JSON 操作符、exists、分组统计

而 jOOQ 的价值恰恰就在这里：

不是把 SQL 藏起来，而是把 PostgreSQL 的高级 SQL 能力，以强类型 DSL 的方式保留下来。

本文会系统讲清：

JSONB 的写入、查询、索引建议
PostgreSQL UPSERT 的正确打开方式
窗口函数在排行榜、分组 TopN、分页总数中的使用
CTE 在复杂查询中的组织方式
在 tio-boot + jOOQ 项目中的推荐落地方式

一、为什么这一篇是“真正的 jOOQ 高阶能力”

很多人刚接触 jOOQ 时，会先把它当成：

比 MyBatis XML 更现代的 SQL 写法
比字符串拼接更安全的 DSL
一个适合 CRUD 的工具

这些理解都没错，但还只是 jOOQ 的“基础层”。

jOOQ 真正强的地方在于：

当 SQL 进入复杂阶段时，依然能保持：
强类型
可组合
可重构
接近原生 SQL 的表达力

特别是在 PostgreSQL 生态里，下面这些能力会非常高频：

用 JSONB 存扩展属性
用 UPSERT 解决并发写入竞争
用窗口函数做排名、TopN、滚动统计
用 CTE 拆解复杂报表 SQL
用 returning 避免“写后再查一次”

这正是本篇的重点。

二、JSONB：关系型表中的半结构化能力

在真实业务里，常会遇到这种情况：

有些字段结构稳定，例如：

id
user_id
created_at

但有些字段经常变化，例如：

用户标签
个性化偏好
设备信息
AB 实验参数
业务扩展属性

如果每次扩展都去改表结构，成本会很高。这时候 PostgreSQL 的 JSONB 就很适合。

2.1 示例表设计

sql

CREATE TABLE user_profile (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE,
  profile JSONB NOT NULL DEFAULT '{}'::jsonb,
  updated_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX idx_user_profile_profile_gin
ON user_profile USING GIN (profile);

这里：

user_id 是业务唯一键
profile 用 JSONB 存扩展属性
updated_at 记录更新时间
GIN(profile) 用于加速 JSONB 查询

例如 profile 里可能存：

json

{
  "tags": ["vip", "sports"],
  "age": 18,
  "device": {
    "os": "ios",
    "ver": "17.2"
  },
  "ab": {
    "expA": "B"
  }
}

2.2 为什么推荐 JSONB 而不是 JSON

PostgreSQL 里同时有 JSON 和 JSONB。

一般工程里更推荐：

text

JSONB

原因很简单：

支持更强的操作符
支持更成熟的索引能力
查询性能更好
更适合生产查询场景

可以把它理解为：

JSON 更像“原始 JSON 文本”
JSONB 更像“可查询的二进制 JSON 结构”

2.3 Codegen 后 JSONB 字段是什么样

如果启用了 jOOQ codegen，通常会生成类似：

java

USER_PROFILE.PROFILE

字段类型一般会映射为：

java

org.jooq.JSONB

这样在 Java 里，写入时就可以直接：

java

JSONB.valueOf(json)

三、JSONB 写入：插入与 UPSERT

在生产里，JSONB 很少是“只插一次永不更新”的。更常见的是：

首次写入
再次上报时覆盖或合并
用唯一键 user_id 做 upsert

所以最常见的写法其实是：

JSONB + UPSERT 一起用

3.1 基础写入

java

import static demo.jooq.gen.Tables.USER_PROFILE;

import org.jooq.DSLContext;
import org.jooq.JSONB;

public int insertProfile(DSLContext dsl, long userId, String json) {
  return dsl.insertInto(USER_PROFILE)
      .set(USER_PROFILE.USER_ID, userId)
      .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
      .execute();
}

这里的重点是：

java

JSONB.valueOf(json)

它把 JSON 字符串包装成 jOOQ 的 JSONB 类型。

实际项目里，通常不建议手写 JSON 字符串，而是由：

fastjson2
Jackson

这样的库先把对象序列化为 JSON，再传给 jOOQ。

3.2 JSONB + UPSERT

这是最常见的生产写法。

java

import static demo.jooq.gen.Tables.USER_PROFILE;

import org.jooq.DSLContext;
import org.jooq.JSONB;

public int saveProfile(DSLContext dsl, long userId, String json) {
  return dsl.insertInto(USER_PROFILE)
      .set(USER_PROFILE.USER_ID, userId)
      .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
      .onConflict(USER_PROFILE.USER_ID)
      .doUpdate()
      .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
      .set(USER_PROFILE.UPDATED_AT, dsl.currentTimestamp())
      .execute();
}

这段逻辑等价于：

user_id 不存在：插入
user_id 已存在：更新 profile 和 updated_at

它解决了“先查再写”的问题，也避免了并发竞争条件。

3.3 JSONB + UPSERT + RETURNING

如果业务在写入后希望立刻拿到最终结果，可以继续配合 returning()。

java

public Long saveProfileAndReturnId(DSLContext dsl, long userId, String json) {
  return dsl.insertInto(USER_PROFILE)
      .set(USER_PROFILE.USER_ID, userId)
      .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
      .onConflict(USER_PROFILE.USER_ID)
      .doUpdate()
      .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
      .set(USER_PROFILE.UPDATED_AT, dsl.currentTimestamp())
      .returning(USER_PROFILE.ID)
      .fetchOne(USER_PROFILE.ID);
}

这样就不需要：

先写
再额外 select 一次

四、JSONB 查询：按字段提取

JSONB 真正的价值在于“不只是能存，还能查”。

4.1 按字段提取：age >= 18

PostgreSQL 原生 SQL 常写成：

sql

(profile->>'age')::int >= 18

在 jOOQ 中，一个非常实用的写法是使用表达式字段：

java

import static demo.jooq.gen.Tables.USER_PROFILE;

import org.jooq.Field;
import org.jooq.impl.DSL;

Field<Integer> age =
    DSL.field("({0} ->> 'age')::int", Integer.class, USER_PROFILE.PROFILE);

var list = dsl.select(USER_PROFILE.USER_ID, USER_PROFILE.PROFILE)
    .from(USER_PROFILE)
    .where(age.ge(18))
    .fetch();

这个写法为什么推荐

因为：

{0} 不是字符串拼接，而是安全占位
USER_PROFILE.PROFILE 仍然是强类型字段引用
外层条件仍然保留 jOOQ DSL 结构

这是一种非常适合 PostgreSQL JSON 表达式的折中写法。

4.2 查询嵌套字段：device.os = 'ios'

可以继续用类似方式：

java

Field<String> os =
    DSL.field("{0} -> 'device' ->> 'os'", String.class, USER_PROFILE.PROFILE);

var rows = dsl.select(USER_PROFILE.USER_ID)
    .from(USER_PROFILE)
    .where(os.eq("ios"))
    .fetch();

这对应 PostgreSQL 语义：

sql

profile -> 'device' ->> 'os' = 'ios'

4.3 判断 JSON 数组包含 vip 标签

PostgreSQL 里非常经典的 JSONB 检索方式是：

sql

profile @> '{"tags":["vip"]}'::jsonb

jOOQ 写法：

java

import org.jooq.JSONB;
import org.jooq.impl.DSL;

var vipUsers = dsl.select(USER_PROFILE.USER_ID)
    .from(USER_PROFILE)
    .where(
        DSL.condition(
            "{0} @> {1}::jsonb",
            USER_PROFILE.PROFILE,
            JSONB.valueOf("{\"tags\":[\"vip\"]}")
        )
    )
    .fetchInto(Long.class);

为什么 `@>` 很重要

因为它通常比“逐层拆 key 比较”更适合走 GIN 索引。

所以在 JSONB 查询里，很多时候可以优先思考：

能不能把查询改写成 @> 包含关系

这通常会更适合 PostgreSQL 优化。

4.4 JSONB 字段存在性判断

有时候业务只关心某个 key 是否存在。

PostgreSQL 常用：

sql

profile ? 'age'

jOOQ 可以写成：

java

var rows = dsl.select(USER_PROFILE.USER_ID)
    .from(USER_PROFILE)
    .where(DSL.condition("{0} ? 'age'", USER_PROFILE.PROFILE))
    .fetch();

如果 key 是动态传入，也可以这样：

java

public List<Long> findUsersHasKey(DSLContext dsl, String key) {
  return dsl.select(USER_PROFILE.USER_ID)
      .from(USER_PROFILE)
      .where(DSL.condition("{0} ? {1}", USER_PROFILE.PROFILE, DSL.inline(key)))
      .fetchInto(Long.class);
}

五、JSONB 索引建议

JSONB 查询能不能快，很大程度上取决于索引设计。

5.1 通用索引：GIN(profile)

最常见的起步配置就是：

sql

CREATE INDEX idx_user_profile_profile_gin
ON user_profile USING GIN (profile);

适合：

@> 包含查询
key 存在性判断
通用 JSONB 检索

这是最推荐的第一步。

5.2 表达式索引：针对热点字段

如果某个 JSON key 查询非常高频，例如年龄：

sql

CREATE INDEX idx_user_profile_age
ON user_profile (((profile->>'age')::int));

这样对：

sql

(profile->>'age')::int >= 18

这种查询性能会明显更好。

也就是说：

通用检索：GIN
热点 key 条件：表达式索引

这两者并不冲突。

5.3 JSONB 设计建议

JSONB 很强，但也不要滥用。

推荐用 JSONB 的场景：

扩展属性不稳定
字段结构变动频繁
查询模式相对明确

不太推荐的场景：

每个 key 都要高频 join
每个 key 都要严格约束
每个 key 都是核心统计维度

因为这些场景，本质上更像关系型字段，而不是扩展属性。

六、UPSERT：避免“先查再写”

这一部分和第 7 篇有衔接，但这里结合 PostgreSQL 高级能力再系统讲一遍。

UPSERT 的意义主要有两点：

避免双 SQL
避免并发竞争条件

6.1 基础 UPSERT

java

import static demo.jooq.gen.Tables.SYSTEM_ADMIN;

public int upsertAdmin(DSLContext dsl, String loginName, String password) {
  return dsl.insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .onConflict(SYSTEM_ADMIN.LOGIN_NAME)
      .doUpdate()
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .execute();
}

语义：

login_name 不存在：新增
login_name 已存在：更新密码

6.2 使用 `excluded(...)`

如果更新值来自“本次插入尝试值”，推荐写成：

java

public int upsertAdmin2(DSLContext dsl, String loginName, String password) {
  return dsl.insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .onConflict(SYSTEM_ADMIN.LOGIN_NAME)
      .doUpdate()
      .set(SYSTEM_ADMIN.PASSWORD, DSL.excluded(SYSTEM_ADMIN.PASSWORD))
      .execute();
}

它更贴近 PostgreSQL 原生 SQL 语义。

6.3 带条件的 UPSERT

有些业务要求：

只有更“新”的数据才能覆盖旧数据
旧版本不能覆盖新版本

这时可以在 doUpdate() 后加 where(...)。

java

public int upsertProfileWithTs(DSLContext dsl, long userId, String json) {
  return dsl.insertInto(USER_PROFILE)
      .set(USER_PROFILE.USER_ID, userId)
      .set(USER_PROFILE.PROFILE, org.jooq.JSONB.valueOf(json))
      .set(USER_PROFILE.UPDATED_AT, dsl.currentTimestamp())
      .onConflict(USER_PROFILE.USER_ID)
      .doUpdate()
      .set(USER_PROFILE.PROFILE, org.jooq.JSONB.valueOf(json))
      .set(USER_PROFILE.UPDATED_AT, dsl.currentTimestamp())
      .where(USER_PROFILE.UPDATED_AT.lt(dsl.currentTimestamp()))
      .execute();
}

实际项目里，更常见的是比较：

请求带来的版本号
请求带来的更新时间
数据库当前版本

但这里先用思路说明。

6.4 UPSERT + RETURNING

如果业务希望写完就拿结果：

java

public Integer upsertAndReturnId(DSLContext dsl, String loginName, String password) {
  return dsl.insertInto(SYSTEM_ADMIN)
      .set(SYSTEM_ADMIN.LOGIN_NAME, loginName)
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .onConflict(SYSTEM_ADMIN.LOGIN_NAME)
      .doUpdate()
      .set(SYSTEM_ADMIN.PASSWORD, password)
      .returning(SYSTEM_ADMIN.ID)
      .fetchOne()
      .getId();
}

这种写法非常适合：

外部同步任务
幂等写入
写后立即依赖主键继续业务

七、窗口函数：真正解决“分组内排序与统计”

窗口函数是 PostgreSQL 也是 SQL 世界非常重要的一类能力。它特别适合解决：

排行榜
TopN
每组最近几条
滚动统计
分页总数
排名与并列处理

很多原本写起来很绕的 SQL，一旦换成窗口函数，会立刻变得清晰。

7.1 示例表：用户积分流水

sql

CREATE TABLE user_score_log (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  score INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX idx_user_score_log_user_time
ON user_score_log(user_id, created_at DESC);

7.2 排名：`row_number`、`rank`、`dense_rank`

先看最经典的排行榜场景。

`row_number`

java

import static demo.jooq.gen.Tables.USER_SCORE_LOG;
import org.jooq.Field;
import org.jooq.impl.DSL;

Field<Integer> rn = DSL.rowNumber()
    .over(DSL.orderBy(USER_SCORE_LOG.SCORE.desc()))
    .as("rn");

var top20 = dsl.select(USER_SCORE_LOG.USER_ID, USER_SCORE_LOG.SCORE, rn)
    .from(USER_SCORE_LOG)
    .orderBy(USER_SCORE_LOG.SCORE.desc())
    .limit(20)
    .fetch();

语义

分数高的排前面
序号严格递增
即使分数相同，也不会并列

`rank`

java

Field<Integer> r = DSL.rank()
    .over(DSL.orderBy(USER_SCORE_LOG.SCORE.desc()))
    .as("r");

特点：

相同分数并列
后续名次跳号

例如：

100 分：第 1 名
100 分：第 1 名
90 分：第 3 名

`dense_rank`

java

Field<Integer> dr = DSL.denseRank()
    .over(DSL.orderBy(USER_SCORE_LOG.SCORE.desc()))
    .as("dr");

特点：

相同分数并列
后续名次不跳号

例如：

100 分：第 1 名
100 分：第 1 名
90 分：第 2 名

7.3 分组 TopN：每个用户最近 3 条记录

这是窗口函数最经典、也最有用的场景之一。

如果不用窗口函数，通常会写得非常绕。

jOOQ 写法

java

import static demo.jooq.gen.Tables.USER_SCORE_LOG;
import org.jooq.Table;
import org.jooq.impl.DSL;

var rn = DSL.rowNumber()
    .over(
        DSL.partitionBy(USER_SCORE_LOG.USER_ID)
           .orderBy(USER_SCORE_LOG.CREATED_AT.desc())
    )
    .as("rn");

Table<?> t = dsl
    .select(
        USER_SCORE_LOG.ID,
        USER_SCORE_LOG.USER_ID,
        USER_SCORE_LOG.SCORE,
        USER_SCORE_LOG.CREATED_AT,
        rn
    )
    .from(USER_SCORE_LOG)
    .asTable("t");

var result = dsl.selectFrom(t)
    .where(DSL.field("rn", Integer.class).le(3))
    .orderBy(
        DSL.field("user_id"),
        DSL.field("created_at").desc()
    )
    .fetch();

逻辑拆解

第一步：

按 user_id 分区
每个用户内部按 created_at desc 排序
用 row_number() 编号

第二步：

外层只保留 rn <= 3

于是就得到：

每个用户最近 3 条记录

这是窗口函数最值得掌握的模板之一。

7.4 分页 + 总数：一次 SQL 返回 total

后台分页通常会写两条 SQL：

查询当前页数据
查询总数

但有时也可以用窗口函数一次搞定。

java

import static demo.jooq.gen.Tables.SYSTEM_ADMIN;
import org.jooq.Field;
import org.jooq.impl.DSL;

int pageNo = 1;
int pageSize = 20;

Field<Integer> total = DSL.count().over().as("total");

var rows = dsl.select(
      SYSTEM_ADMIN.ID,
      SYSTEM_ADMIN.LOGIN_NAME,
      total
    )
    .from(SYSTEM_ADMIN)
    .orderBy(SYSTEM_ADMIN.ID.desc())
    .limit(pageSize)
    .offset((pageNo - 1) * pageSize)
    .fetch();

说明

这里：

java

count().over()

会给当前结果集里的每一行都附带一个总数。

所以：

列表数据有了
总数也有了
取第一行的 total 即可

优点

少一次 count SQL
数据一致性更自然
对中等规模分页场景很实用

注意

超大表上，count(*) 本身仍可能昂贵。所以窗口函数不是“免费午餐”，只是让表达更集中。

八、CTE：把复杂 SQL 拆成可读的查询块

CTE，也就是：

sql

WITH ...

它的最大价值不是“更快”，而是：

让复杂 SQL 更可读、更可维护、更适合分阶段组织。

在多层统计、复杂报表、分组筛选里非常有用。

8.1 最简单的 CTE 思路

例如，我们先把“每个用户的最近记录”做成一个中间结果，再从中筛选。

SQL 逻辑类似：

sql

with latest_score as (
  ...
)
select * from latest_score where ...

8.2 jOOQ 中的 CTE 写法

下面继续用 user_score_log 举例：需求是查询“每个用户最新一条积分记录”。

java

import static demo.jooq.gen.Tables.USER_SCORE_LOG;
import org.jooq.CommonTableExpression;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record4;
import org.jooq.impl.DSL;

Field<Integer> rn = DSL.rowNumber()
    .over(
        DSL.partitionBy(USER_SCORE_LOG.USER_ID)
           .orderBy(USER_SCORE_LOG.CREATED_AT.desc())
    )
    .as("rn");

CommonTableExpression<Record4<Long, Integer, java.sql.Timestamp, Integer>> latestScore =
    DSL.name("latest_score")
       .fields("user_id", "score", "created_at", "rn")
       .as(
           dsl.select(
                 USER_SCORE_LOG.USER_ID,
                 USER_SCORE_LOG.SCORE,
                 USER_SCORE_LOG.CREATED_AT,
                 rn
               )
              .from(USER_SCORE_LOG)
       );

var result = dsl.with(latestScore)
    .select()
    .from(latestScore)
    .where(DSL.field(DSL.name("latest_score", "rn"), Integer.class).eq(1))
    .fetch();

这个例子说明什么

CTE 非常适合和窗口函数结合：

先在 CTE 里算排名
再在外层筛选 rn = 1
逻辑很清楚

8.3 为什么复杂查询推荐 CTE

因为如果所有逻辑都堆在一个巨长的 SQL 里，会出现几个问题：

难读
难调试
难复用
难映射到 DAO 方法含义

而 CTE 的优势是：

把 SQL 分阶段命名
每一段都有业务语义
方便继续组合

在 jOOQ 中尤其适合：

复杂统计
分组筛选
多层子查询
报表类查询

九、JSONB + CTE + 窗口函数的组合思路

真实项目里，高级 SQL 往往不是单独出现的，而是组合使用。

例如一个查询可能是：

从 JSONB 里提取 age
用窗口函数给每个用户按更新时间排名
再用 CTE 包装成中间结果
最后筛出每个用户最新一条且年龄 >= 18 的记录

这正是 jOOQ 高阶能力的体现：

不是某一个 API，而是让复杂 SQL 的结构依然清晰。

下面给一个组合思路示例。

9.1 查询每个用户最新 profile，且 age >= 18

假设有一张带历史版本的 profile 表：

sql

CREATE TABLE user_profile_history (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  profile JSONB NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

jOOQ 思路：

从 JSONB 提取 age
用 row_number() over(partition by user_id order by created_at desc) 排序
放进 CTE
外层过滤 rn = 1 and age >= 18

这类查询在普通 ORM 里会很痛苦，但在 jOOQ 中是很自然的延伸。

十、在 tio-boot + jOOQ 项目中的推荐分层

到了高级 SQL 阶段，分层更重要。

10.1 DAO 层：只负责 SQL

DAO 层应该做的事：

封装 JSONB 查询
封装 UPSERT
封装窗口函数查询
封装 CTE 报表 SQL

不要把：

事务控制
HTTP 参数处理
业务流程判断

塞进 DAO。

10.2 Service 层：负责事务与业务编排

例如：

先 UPSERT profile
再写审计日志
再触发别的表更新

这些应该放在 Service 层统一控制事务。

10.3 Controller 层：只处理输入输出

Controller 更适合做：

参数解析
DTO 封装
返回对象构建

不要让 Controller 直接拼复杂 SQL。

十一、一个完整的 DAO 示例

下面给出一个把 JSONB、UPSERT、窗口函数、存在性判断串起来的 DAO 示例。

java

package demo.jooq.dao;

import static demo.jooq.gen.Tables.SYSTEM_ADMIN;
import static demo.jooq.gen.Tables.USER_PROFILE;
import static demo.jooq.gen.Tables.USER_SCORE_LOG;

import java.util.List;

import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.JSONB;
import org.jooq.impl.DSL;

import nexus.io.annotation.Inject;

import demo.jooq.tx.TransactionContext;

public class PgAdvancedDao {

  @Inject
  private DSLContext dsl;

  private DSLContext useDsl() {
    DSLContext txDsl = TransactionContext.get();
    return txDsl != null ? txDsl : dsl;
  }

  public int saveProfile(long userId, String json) {
    return useDsl().insertInto(USER_PROFILE)
        .set(USER_PROFILE.USER_ID, userId)
        .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
        .onConflict(USER_PROFILE.USER_ID)
        .doUpdate()
        .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
        .set(USER_PROFILE.UPDATED_AT, useDsl().currentTimestamp())
        .execute();
  }

  public Long saveProfileAndReturnId(long userId, String json) {
    return useDsl().insertInto(USER_PROFILE)
        .set(USER_PROFILE.USER_ID, userId)
        .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
        .onConflict(USER_PROFILE.USER_ID)
        .doUpdate()
        .set(USER_PROFILE.PROFILE, JSONB.valueOf(json))
        .set(USER_PROFILE.UPDATED_AT, useDsl().currentTimestamp())
        .returning(USER_PROFILE.ID)
        .fetchOne(USER_PROFILE.ID);
  }

  public List<Long> findAdultUsers() {
    Field<Integer> age =
        DSL.field("({0} ->> 'age')::int", Integer.class, USER_PROFILE.PROFILE);

    return useDsl().select(USER_PROFILE.USER_ID)
        .from(USER_PROFILE)
        .where(age.ge(18))
        .fetchInto(Long.class);
  }

  public List<Long> findVipUsers() {
    return useDsl().select(USER_PROFILE.USER_ID)
        .from(USER_PROFILE)
        .where(
            DSL.condition(
                "{0} @> {1}::jsonb",
                USER_PROFILE.PROFILE,
                JSONB.valueOf("{\"tags\":[\"vip\"]}")
            )
        )
        .fetchInto(Long.class);
  }

  public boolean existsAdminByLoginName(String loginName) {
    return useDsl().fetchExists(
        useDsl()
            .selectOne()
            .from(SYSTEM_ADMIN)
            .where(SYSTEM_ADMIN.LOGIN_NAME.eq(loginName))
    );
  }

  public List<?> top20ScoreRank() {
    Field<Integer> rn = DSL.rowNumber()
        .over(DSL.orderBy(USER_SCORE_LOG.SCORE.desc()))
        .as("rn");

    return useDsl().select(USER_SCORE_LOG.USER_ID, USER_SCORE_LOG.SCORE, rn)
        .from(USER_SCORE_LOG)
        .orderBy(USER_SCORE_LOG.SCORE.desc())
        .limit(20)
        .fetch();
  }

  public List<?> latest3ScoreByUser() {
    var rn = DSL.rowNumber()
        .over(
            DSL.partitionBy(USER_SCORE_LOG.USER_ID)
               .orderBy(USER_SCORE_LOG.CREATED_AT.desc())
        )
        .as("rn");

    var t = useDsl()
        .select(
            USER_SCORE_LOG.ID,
            USER_SCORE_LOG.USER_ID,
            USER_SCORE_LOG.SCORE,
            USER_SCORE_LOG.CREATED_AT,
            rn
        )
        .from(USER_SCORE_LOG)
        .asTable("t");

    return useDsl().selectFrom(t)
        .where(DSL.field("rn", Integer.class).le(3))
        .orderBy(DSL.field("user_id"), DSL.field("created_at").desc())
        .fetch();
  }
}

十二、几个非常容易踩的坑

12.1 JSONB 很灵活，但不要把一切都塞进去

JSONB 适合扩展属性，不适合替代整个关系模型。

如果某字段：

高频 join
高频分组统计
有强约束
是核心关系字段

那它更适合做成真实列。

12.2 `@>` 查询很常用，但前提是 JSON 结构要稳定

如果 JSON 结构本身非常混乱，例如：

有时是数组
有时是对象
key 名不统一

那查询会越来越难维护。

所以 JSONB 灵活不等于可以无约束。

12.3 窗口函数很强，但不是所有查询都该上

如果只是简单分页、简单排序，就不要为了“高级”而硬上窗口函数。

窗口函数最适合的，是普通 SQL 很难优雅表达的场景：

分组 TopN
排名
滚动统计
一次返回总数

12.4 CTE 的价值首先是可读性，不一定总是性能优化

有些人会误以为 WITH 天然更快。其实它首先解决的是：

结构清晰
逻辑分层
易于维护

性能仍然要看实际 SQL、数据量、索引和执行计划。

12.5 PostgreSQL 高级能力是“方言优势”，不是缺点

像：

JSONB
on conflict
returning
窗口函数增强
表达式索引

都明显带有 PostgreSQL 方言特色。

这不是问题，反而是优势。前提是你要明确项目目标：

如果你的数据库就是 PostgreSQL，那就应该主动拥抱 PostgreSQL 的强项。

而 jOOQ 正是最适合做这件事的 Java 工具之一。

十三、本篇总结

这一篇真正进入了 jOOQ 的高阶能力区间。

通过本文，我们完成了：

使用 JSONB 存储扩展属性
使用 JSONB.valueOf(...) 写入 JSONB
使用 ->>、@>、? 等 PostgreSQL JSON 能力进行查询
理解 GIN 索引和表达式索引的适用场景
使用 onConflict(...).doUpdate() 实现 PostgreSQL UPSERT
使用 returning(...) 实现写后读
使用 row_number、rank、dense_rank 做排行榜
使用窗口函数完成“每组 TopN”
使用 count().over() 一次返回分页总数
使用 CTE 把复杂 SQL 拆成可读的阶段化查询

一句话总结：

jOOQ 的真正价值，不是在 Java 里“模拟 ORM”，而是在 Java 里优雅地表达 PostgreSQL 的原生高级 SQL。

到这里，整个 tio-boot + jOOQ 系列已经覆盖了：

基础整合
事务控制
强类型 codegen
CRUD
批量与分页
UPSERT
多表查询与 DTO
JSONB
窗口函数
CTE 与高级 PostgreSQL SQL

这已经是一套相当完整的现代 Java 数据访问方案。

的窗口函数、CTE、JSON 查询与 PostgreSQL 高级 SQL 实战 ​

一、为什么这一篇是“真正的 jOOQ 高阶能力” ​

二、JSONB：关系型表中的半结构化能力 ​

2.1 示例表设计 ​

2.2 为什么推荐 JSONB 而不是 JSON ​

2.3 Codegen 后 JSONB 字段是什么样 ​

三、JSONB 写入：插入与 UPSERT ​

3.1 基础写入 ​

3.2 JSONB + UPSERT ​

3.3 JSONB + UPSERT + RETURNING ​

四、JSONB 查询：按字段提取 ​

4.1 按字段提取：age >= 18 ​

这个写法为什么推荐 ​

4.2 查询嵌套字段：device.os = 'ios' ​

4.3 判断 JSON 数组包含 vip 标签 ​

为什么 @> 很重要 ​

4.4 JSONB 字段存在性判断 ​

五、JSONB 索引建议 ​

5.1 通用索引：GIN(profile) ​

5.2 表达式索引：针对热点字段 ​

5.3 JSONB 设计建议 ​

六、UPSERT：避免“先查再写” ​

6.1 基础 UPSERT ​

6.2 使用 excluded(...) ​

6.3 带条件的 UPSERT ​

6.4 UPSERT + RETURNING ​

七、窗口函数：真正解决“分组内排序与统计” ​

7.1 示例表：用户积分流水 ​

7.2 排名：row_number、rank、dense_rank ​

row_number ​

语义 ​

rank ​

dense_rank ​

7.3 分组 TopN：每个用户最近 3 条记录 ​

jOOQ 写法 ​

逻辑拆解 ​

7.4 分页 + 总数：一次 SQL 返回 total ​

说明 ​

优点 ​

注意 ​

八、CTE：把复杂 SQL 拆成可读的查询块 ​

8.1 最简单的 CTE 思路 ​

8.2 jOOQ 中的 CTE 写法 ​

这个例子说明什么 ​

8.3 为什么复杂查询推荐 CTE ​

九、JSONB + CTE + 窗口函数的组合思路 ​

9.1 查询每个用户最新 profile，且 age >= 18 ​

十、在 tio-boot + jOOQ 项目中的推荐分层 ​

10.1 DAO 层：只负责 SQL ​

10.2 Service 层：负责事务与业务编排 ​

10.3 Controller 层：只处理输入输出 ​

十一、一个完整的 DAO 示例 ​

十二、几个非常容易踩的坑 ​

12.1 JSONB 很灵活，但不要把一切都塞进去 ​

12.2 @> 查询很常用，但前提是 JSON 结构要稳定 ​

12.3 窗口函数很强，但不是所有查询都该上 ​

12.4 CTE 的价值首先是可读性，不一定总是性能优化 ​

12.5 PostgreSQL 高级能力是“方言优势”，不是缺点 ​

十三、本篇总结 ​

的窗口函数、CTE、JSON 查询与 PostgreSQL 高级 SQL 实战

一、为什么这一篇是“真正的 jOOQ 高阶能力”

二、JSONB：关系型表中的半结构化能力

2.1 示例表设计

2.2 为什么推荐 JSONB 而不是 JSON

2.3 Codegen 后 JSONB 字段是什么样

三、JSONB 写入：插入与 UPSERT

3.1 基础写入

3.2 JSONB + UPSERT

3.3 JSONB + UPSERT + RETURNING

四、JSONB 查询：按字段提取

4.1 按字段提取：age >= 18

这个写法为什么推荐

4.2 查询嵌套字段：device.os = 'ios'

4.3 判断 JSON 数组包含 vip 标签

为什么 `@>` 很重要

4.4 JSONB 字段存在性判断

五、JSONB 索引建议

5.1 通用索引：GIN(profile)

5.2 表达式索引：针对热点字段

5.3 JSONB 设计建议

六、UPSERT：避免“先查再写”

6.1 基础 UPSERT

6.2 使用 `excluded(...)`

6.3 带条件的 UPSERT

6.4 UPSERT + RETURNING

七、窗口函数：真正解决“分组内排序与统计”

7.1 示例表：用户积分流水

7.2 排名：`row_number`、`rank`、`dense_rank`

`row_number`

语义

`rank`

`dense_rank`

7.3 分组 TopN：每个用户最近 3 条记录

jOOQ 写法

逻辑拆解

7.4 分页 + 总数：一次 SQL 返回 total

说明

优点

注意

八、CTE：把复杂 SQL 拆成可读的查询块

8.1 最简单的 CTE 思路

8.2 jOOQ 中的 CTE 写法

这个例子说明什么

8.3 为什么复杂查询推荐 CTE

九、JSONB + CTE + 窗口函数的组合思路

9.1 查询每个用户最新 profile，且 age >= 18

十、在 tio-boot + jOOQ 项目中的推荐分层

10.1 DAO 层：只负责 SQL

10.2 Service 层：负责事务与业务编排

10.3 Controller 层：只处理输入输出

十一、一个完整的 DAO 示例

十二、几个非常容易踩的坑

12.1 JSONB 很灵活，但不要把一切都塞进去

12.2 `@>` 查询很常用，但前提是 JSON 结构要稳定

12.3 窗口函数很强，但不是所有查询都该上

12.4 CTE 的价值首先是可读性，不一定总是性能优化

12.5 PostgreSQL 高级能力是“方言优势”，不是缺点

十三、本篇总结