PostgreSQL 其他

Postgres 在某些地方不同于 mysql,所有这里加以说明

RETURN_GENERATED_KEYS UUID

问题描述

  • 数据的库的主键是类型是 UUID,当向数据库添加记录时,数据库会自动生成主键,如何获取生成的主键呢?
  • ApiTable 会自动返回数据库主键的的这,只不过返回主键的有些特殊
  • 我们保存一条记录测试一下
import org.junit.Test;

import com.jfinal.kit.Kv;
import com.litongjava.open.chat.config.DbConfig;
import com.litongjava.open.chat.constants.TableNames;
import com.litongjava.table.model.TableInput;
import com.litongjava.table.model.TableResult;
import com.litongjava.table.services.ApiTable;
import com.litongjava.tio.utils.environment.EnvUtils;
import com.litongjava.tio.utils.json.JsonUtils;

public class UserMessageService {

  @Test
  public void testSaveMessage() {
    EnvUtils.load();
    new DbConfig().config();
    String textQuestion = "How are your";
    String chatId = "4f0d2c67-aef5-44a6-bf25-19d58450f20c";

    TableInput ti = TableInput.by("content", textQuestion).set("role", "user").set("session_id", chatId);
    TableResult<Kv> ts = ApiTable.save(TableNames.rumi_message, ti);
    System.out.println(JsonUtils.toJson(ts));
  }
}

result

class java.util.UUID
a629ae36-100a-48f4-ad0a-3951c77ef42d
{"data":{"uuid":{"leastSignificantBits":"-5977902531978791891","mostSignificantBits":"-6473451442147800844"}},"code":1,"ok":true,"msg":null}

mostSignificantBits and leastSignificantBits 是什么意思

在 Java 等编程语言中，UUID（Universally Unique Identifier）通常表示为一个 128 位的值。UUID 包含两个部分：最显著位（most significant bits）和最不显著位（least significant bits），各占 64 位。你的例子中，这两个部分分别是：

  • mostSignificantBits: 2165688298263563317
  • leastSignificantBits: -5614157302637544926

要从这两个值生成一个 UUID，你可以使用 UUID 类中的构造函数，具体代码如下：

import java.util.UUID;

public class UUIDExample {
    public static void main(String[] args) {
        long mostSignificantBits = 2165688298263563317L;
        long leastSignificantBits = -5614157302637544926L;

        UUID uuid = new UUID(mostSignificantBits, leastSignificantBits);
        System.out.println("Generated UUID: " + uuid.toString());
    }
}

在这个示例中，我们使用UUID类的构造函数UUID(long mostSigBits, long leastSigBits)来生成一个 UUID 对象，并将其转换为字符串形式。

UUID 类型

Postgresql 中的 uuid 类型对应 Java 中的 java.util.UUID 获取

String uuid=kv.set("id", kv.get("id").toString());

删除

import java.util.UUID;

UUID uuid = UUID.fromString(id);
return Db.deleteById(TableNames.model, uuid);

json 类型

读取

PGobject meta = kv.getAs("meta");
if(meta.isNull()) {
  kv.set("meta","{}");
}else {
  kv.set("meta",JsonUtils.parseObject(meta.getValue()));
}

jsonb 类型

设置 json 类型

PGobject meta = new PGobject();
try {
  meta.setType("jsonb");
  meta.setValue("{}");
} catch (SQLException e) {
  e.printStackTrace();
}
embedding.set("meta", meta);

读取 jsonb 类型

    Kv kv = row.toKv();
    Object userInfo = kv.get("user_info");
    if (userInfo instanceof PGobject) {
      String jsonValue = ((PGobject) userInfo).getValue();
      if (jsonValue != null) {
        kv.set("user_info", FastJson2Utils.parseObject(jsonValue));
      }
    }

tsvector 类型

PGobject search_vector = new PGobject();

search_vector.setType("tsvector");
try {
  search_vector.setValue("");
} catch (SQLException e1) {
  e1.printStackTrace();
}
embedding.set("search_vector", search_vector);

now

获取数据时间

package com.litongjava.open.chat.services.sys;

import com.litongjava.db.activerecord.Db;

public class SysService {

  public java.sql.Timestamp now() {
    return Db.queryFirst("select now()");
  }
}


import com.litongjava.annotation.RequestPath;
import com.litongjava.jfinal.aop.Aop;
import com.litongjava.open.chat.services.sys.SysService;

@RequestPath("/api/v1/sys")
public class ApiV1SysController {

  public String now() {
    return Aop.get(SysService.class).now().toString();
  }
}

2024-10-07 16:52:01.905573

高级 SQL

统计问题总量和上周问题数量

SELECT
    COUNT(rm.uuid)/2 AS total_question_count,
    COUNT(CASE
        WHEN rm.created_at >= date_trunc('week', CURRENT_DATE) - interval '1 week'
             AND rm.created_at < date_trunc('week', CURRENT_DATE)
        THEN rm.uuid
    END) / 2 AS question_of_last_week
FROM
    course_chats cm
JOIN
    rumi_message rm ON cm.ID = rm.session_id::uuid
WHERE
    cm.app_id = 430973612836159488

这段 SQL 查询的目的是从两个表 course_chats（简称 cm）和 rumi_message（简称 rm）中获取特定应用（app_id = 430973612836159488）下的总问题数以及上周的问题数。以下是对该 SQL 语句的详细解释：

1. 数据来源和连接

FROM
    course_chats cm
JOIN
    rumi_message rm ON cm.ID = rm.session_id::uuid
  • course_chats cm：这是主表，别名为 cm。假设这个表记录了课程聊天会话的信息。
  • rumi_message rm：这是被连接的表，别名为 rm。假设这个表记录了与课程聊天相关的消息。
  • 连接条件 cm.ID = rm.session_id::uuid：
    • cm.ID 是 course_chats 表中的主键，表示一个会话的唯一标识符。
    • rm.session_id 是 rumi_message 表中的外键，表示消息所属的会话。::uuid 表示将 session_id 强制转换为 UUID 类型，以匹配 cm.ID 的数据类型。
    • 通过这个连接条件，将每条消息关联到其所属的课程聊天会话。

2. 过滤条件

WHERE
    cm.app_id = 430973612836159488
  • cm.app_id = 430973612836159488：这个条件过滤出 course_chats 表中 app_id 为 430973612836159488 的记录。即只查询特定应用 ID 下的课程聊天和相关消息。

3. 选择的字段及其计算

SELECT
    COUNT(rm.uuid)/2 AS total_question_count,
    COUNT(CASE
        WHEN rm.created_at >= date_trunc('week', CURRENT_DATE) - interval '1 week'
             AND rm.created_at < date_trunc('week', CURRENT_DATE)
        THEN rm.uuid
    END) / 2 AS question_of_last_week

  • COUNT(rm.uuid)/2 AS total_question_count：

    • COUNT(rm.uuid) 统计连接后符合条件的所有消息的数量。
    • 除以 2：假设每个问题由两条消息组成（例如，一条问题消息和一条回复消息），因此总消息数除以 2 得到总问题数。
    • 别名 total_question_count：表示总问题数。

  • COUNT(CASE ... END) / 2 AS question_of_last_week：

    • CASE 语句：用于条件统计，仅计算在特定时间范围内的消息。
      • 时间条件：
        • rm.created_at >= date_trunc('week', CURRENT_DATE) - interval '1 week'：消息创建时间大于等于上周的周一零点（假设一周从周一开始）。
        • rm.created_at < date_trunc('week', CURRENT_DATE)：消息创建时间小于本周的周一零点。
      • 综上，这个条件筛选出上周（完整的一周时间段内）的消息。
      • 如果消息满足上述时间条件，则返回 rm.uuid，否则返回 NULL。
    • COUNT(...) / 2：统计满足条件的消息数，并除以 2 以得到上周的问题数。
    • 别名 question_of_last_week：表示上周的问题数。

4. 综合说明

该 SQL 查询通过连接 course_chats 和 rumi_message 两个表，过滤出特定 app_id 下的所有相关消息。然后：

  • 计算所有相关消息的总数，并假设每个问题由两条消息组成，得出总问题数。
  • 进一步筛选出上周内的消息，同样假设每个问题由两条消息组成，得出上周的问题数。

这种计算方法适用于消息数量与问题数量成固定比例的场景，如每个问题有一个提问消息和一个回答消息。

示例输出

假设查询结果如下：

total_question_countquestion_of_last_week
10020

这意味着在特定 app_id 下，总共有 200 条相关消息（即 100 个问题），其中上周有 40 条相关消息（即 20 个问题）。

统计上周新增用户

WITH first_occurrences AS (
    SELECT "user_id", MIN("created_at") AS first_created_at
    FROM "course_chats"
        WHERE type = 0 AND app_id = 430973612836159488
    GROUP BY "user_id"
)
SELECT COUNT(*)
FROM first_occurrences
WHERE "first_created_at" >= date_trunc('week', current_date) - interval '7 days'
  AND "first_created_at" < date_trunc('week', current_date);

1. 查询概述

该 SQL 查询的主要目的是统计在上一周内首次发生特定事件（在本例中为 type = 0 的聊天记录，且属于特定应用 app_id = 430973612836159488）的唯一用户数量。

2. 查询详解

a. 公共表表达式（CTE）：first_occurrences
WITH first_occurrences AS (
    SELECT "user_id", MIN("created_at") AS first_created_at
    FROM "course_chats"
        WHERE type = 0 AND app_id = 430973612836159488
    GROUP BY "user_id"
)

  • 目的： 定义一个名为 first_occurrences 的临时结果集，用于获取每个用户在满足特定条件下的首次聊天时间。

  • 组成部分：

    • SELECT "user_id", MIN("created_at") AS first_created_at:

      • "user_id": 标识 course_chats 表中的每个唯一用户。
      • MIN("created_at") AS first_created_at: 对每个用户，找到其最早的 (MIN) 聊天记录时间，并将其命名为 first_created_at。

    • FROM "course_chats":

      • 数据来源于 course_chats 表，该表可能记录了与课程相关的聊天互动。

    • WHERE type = 0 AND app_id = 430973612836159488:

      • type = 0: 过滤出类型为 0 的聊天记录（例如，可能代表某种特定类型的聊天或互动）。
      • app_id = 430973612836159488: 进一步过滤出属于特定应用 app_id 的聊天记录。

    • GROUP BY "user_id":

      • 按 user_id 分组，以确保 MIN("created_at") 函数针对每个用户计算其最早的聊天时间。

  • CTE 的结果：

    • 一个名为 first_occurrences 的临时表，包含两列：
      • user_id: 每个唯一用户的标识。
      • first_created_at: 该用户在指定 type 和 app_id 下的最早聊天时间。
b. 主查询：统计上一周内的用户数量
SELECT COUNT(*)
FROM first_occurrences
WHERE "first_created_at" >= date_trunc('week', current_date) - interval '7 days'
  AND "first_created_at" < date_trunc('week', current_date);

  • 目的： 统计在上一周内首次发生符合条件的聊天记录的用户数量。

  • 组成部分：

    • SELECT COUNT(*):

      • 计算满足条件的记录总数，即符合条件的用户数量。

    • FROM first_occurrences:

      • 使用前面定义的 CTE first_occurrences 作为数据源。

    • WHERE 条件：

      • "first_created_at" >= date_trunc('week', current_date) - interval '7 days':

        • date_trunc('week', current_date): 将当前日期截断到本周的开始（通常是星期一，取决于数据库的设置）。
        • - interval '7 days': 从本周的开始日期减去 7 天，得到上一周的开始日期。
        • >=: 确保 first_created_at 的时间是在上一周的开始日期或之后。

      • "first_created_at" < date_trunc('week', current_date):

        • 确保 first_created_at 的时间是在本周开始日期之前。

  • WHERE 子句的逻辑解释：

    • 条件 "first_created_at" >= date_trunc('week', current_date) - interval '7 days' AND "first_created_at" < date_trunc('week', current_date) 旨在捕捉所有 first_created_at 时间戳位于上一周的整个时间范围内的记录。

    • 示例：

      • 假设今天是 2024 年 10 月 13 日（星期日），且一周从星期一开始：
        • date_trunc('week', current_date) 会返回 2024 年 10 月 7 日（本周的开始）。
        • date_trunc('week', current_date) - interval '7 days' 会返回 2024 年 9 月 30 日（上一周的开始）。
        • 因此，查询会统计所有 first_created_at 在 2024 年 9 月 30 日（含）至 2024 年 10 月 6 日（不含）之间的用户数量。

3. 综合分析

  • 整体功能：

    1. 步骤一： 通过 CTE first_occurrences，为每个用户找到其在指定 type 和 app_id 下的首次聊天记录时间。
    2. 步骤二： 从这些首次聊天记录中，筛选出那些在上一周内首次发生的用户，并统计其数量。

  • 使用场景：

    • 如果您管理一个教育平台，并希望分析用户参与度，这个查询可以帮助您了解在过去一周内有多少新用户首次参与了特定类型的聊天。这对于评估用户的注册效果、初次参与率或最近市场推广活动的影响都非常有用。

4. 其他考虑因素

  • 时区问题：

    • current_date 函数基于数据库服务器的时区。如果您的用户分布在多个时区，可能需要调整查询以确保时间计算的准确性。

  • 性能优化：

    • 确保 course_chats 表在 type、app_id 和 user_id 列上有适当的索引。这将显著提高查询性能，特别是在处理大数据量时。

  • 动态参数化：

    • 如果您需要频繁对不同的 app_id 或 type 进行类似的分析，考虑将这些值参数化，以提高查询的灵活性和可复用性。

  • 边界情况处理：

    • 无符合条件的用户： 从未有过符合条件的聊天记录的用户不会出现在 first_occurrences CTE 中，因此自然被排除在统计之外。
    • 同一天的多次聊天： 如果用户在同一天有多条符合条件的聊天记录，MIN("created_at") 会确保只考虑最早的一条。

5. 总结

这段 SQL 查询通过利用公共表表达式（CTE）和时间截断函数，精确地统计了在特定条件下，上一周内首次参与指定类型聊天的唯一用户数量。它在用户参与度分析、市场推广效果评估和用户行为研究等方面具有重要的应用价值。根据实际需求，您还可以调整日期范围、过滤条件或参数化部分，以适应不同的分析场景。