如何用单条SQL查询不同时间周期的最后一条日志？

针对你的需求，这里有个更简洁优雅的通用实现，只需要修改一个参数就能切换查询的时间周期：

-- 替换 'day' 为 'week'/'month'/'quarter'/'year' 即可切换统计周期
SELECT DISTINCT ON (date_trunc('day', log_datetime)) *
FROM "logs"
ORDER BY date_trunc('day', log_datetime) DESC, log_datetime DESC;

优化点说明： #

• 去掉了冗余的类型转换：log_datetime如果是timestamp或timestamptz类型，date_trunc可以直接处理，不需要额外转::timestamp；同时date_trunc('day', ...)的结果已经能作为分组依据，没必要再转::date
• 统一分组与排序字段：确保分组逻辑和排序逻辑完全一致，避免出现潜在的逻辑不一致问题
• 真正的通用化：只需修改date_trunc的第一个参数，就能覆盖所有你需要的周期：
  • 'day' → 每日最后一条日志
  • 'week' → 每周最后一条日志（PostgreSQL默认周起始为周一，若需调整可通过SET datestyle = 'ISO, DMY';修改）
  • 'month' → 每月最后一条日志
  • 'quarter' → 每季度最后一条日志
  • 'year' → 每年最后一条日志

如果是在应用程序中使用，还可以做成参数化查询，避免硬编码周期值：

-- PostgreSQL参数化查询示例，$1为传入的周期参数（比如'day'/'month'）
SELECT DISTINCT ON (date_trunc($1, log_datetime)) *
FROM "logs"
ORDER BY date_trunc($1, log_datetime) DESC, log_datetime DESC;

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