在PostgreSQL中按时间获取每日首条记录的实现方法咨询

可以通过窗口函数+条件聚合或者**交叉表（crosstab）**两种方式实现需求，以下是具体方案：

基础思路 #

先按日期分组，用窗口函数筛选出每天的最早记录，再将不同日期的记录转换为first_record_today、first_record_yesterday这类指定列。

假设你的表名为your_table，时间字段为created_at，需要提取的记录字段为id和content（可根据实际调整）。

方案一：条件聚合（无需额外扩展） #

这种方式无需安装扩展，写法直观，适合固定日期范围的场景：

SELECT
    -- 今日首条记录，无记录则显示指定默认值
    COALESCE(MAX(CASE WHEN record_date = CURRENT_DATE THEN CONCAT('ID:', id, ' | 内容:', content) END), '无记录') AS first_record_today,
    -- 昨日首条记录
    COALESCE(MAX(CASE WHEN record_date = CURRENT_DATE - INTERVAL '1 day' THEN CONCAT('ID:', id, ' | 内容:', content) END), '无记录') AS first_record_yesterday,
    -- 前日首条记录
    COALESCE(MAX(CASE WHEN record_date = CURRENT_DATE - INTERVAL '2 days' THEN CONCAT('ID:', id, ' | 内容:', content) END), '无记录') AS first_record_day_before_yesterday
FROM (
    -- 子查询：筛选出每天的第一条记录
    SELECT
        date_trunc('day', created_at) AS record_date,
        id,
        content
    FROM (
        -- 内层子查询：按天分区，给每条记录按时间排序编号
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY date_trunc('day', created_at) ORDER BY created_at ASC) AS rn
        FROM your_table
        -- 仅查询最近3天的数据，减少计算量
        WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
    ) t
    WHERE rn = 1  -- 取每天的第一条（编号为1的记录）
) daily_first_records;

关键说明： #

• date_trunc('day', created_at)：将时间字段截断到日期维度，用于按天分组。
• ROW_NUMBER() OVER (...)：按天分区后，按时间升序给记录编号，rn=1就是当天最早的记录。
• CASE+MAX：将不同日期的记录映射到指定列，MAX用于确保每个日期只取一条记录（因为每个日期只有一条符合条件的记录，MAX等价于直接取值）。
• COALESCE：处理无记录的情况，替换为自定义默认文本。

方案二：交叉表（crosstab）（适合灵活扩展日期） #

如果需要支持更多日期或更灵活的列转换，可以使用PostgreSQL的crosstab函数，但需要先安装tablefunc扩展：

-- 先安装tablefunc扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 执行交叉查询
SELECT * FROM crosstab(
    -- 第一个参数：源查询，输出分类、日期标签、记录内容
    $$
    SELECT
        'daily_first' AS category,  -- 固定分类，用于交叉表分组
        CASE
            WHEN date_trunc('day', created_at) = CURRENT_DATE THEN 'today'
            WHEN date_trunc('day', created_at) = CURRENT_DATE - INTERVAL '1 day' THEN 'yesterday'
            WHEN date_trunc('day', created_at) = CURRENT_DATE - INTERVAL '2 days' THEN 'day_before_yesterday'
        END AS day_label,
        CONCAT('ID:', id, ' | 内容:', content) AS record_content
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY date_trunc('day', created_at) ORDER BY created_at ASC) AS rn
        FROM your_table
        WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
    ) t
    WHERE rn = 1
    ORDER BY 1, 2
    $$,
    -- 第二个参数：指定要转换的列标签
    $$VALUES ('today'), ('yesterday'), ('day_before_yesterday')$$
) AS ct(
    category text,
    first_record_today text,
    first_record_yesterday text,
    first_record_day_before_yesterday text
);

关键说明： #

• crosstab函数需要两个参数：源查询（提供行、列、值）和列标签列表，最终将行数据转换为指定列。
• 适合需要动态扩展日期范围的场景，只需调整源查询的日期条件和列标签即可。

注意事项 #

• 确保created_at字段的时区与数据库时区一致，避免日期计算错误（如果是带时区的字段，可使用date_trunc('day', created_at AT TIME ZONE 'Asia/Shanghai')指定时区）。
• 如果需要提取单字段而非拼接内容，直接替换CONCAT(...)为对应的字段名即可（比如直接写id）。

内容的提问来源于stack exchange，提问作者rithuwa