如何在PostgreSQL中统计每日条目总数并按日期降序排列

问题原因 #

你的SQL存在3个问题：

• 缺少GROUP BY子句：使用count这类聚合函数时，必须指定按哪个维度分组统计，否则只会返回全表总条数，不会按日期拆分统计
• 语法错误：第二个to_char调用缺少格式参数和右括号，执行会直接报错
• 缺少正确的排序逻辑：未加ORDER BY子句，且直接对DD-MM-YYYY格式的字符串排序会出现顺序错误，必须按日期类型的值排序

正确实现 #

推荐写法如下，性能和正确性都更优：

SELECT
  COUNT(*) AS count,
  TO_CHAR(download_date, 'DD-MM-YYYY') AS date
FROM (
  -- 先把时间戳转成日期维度，去除时分秒的影响
  SELECT DATE_TRUNC('day', TO_TIMESTAMP(last_downloaded / 1000)) AS download_date
  FROM stats
) t
-- 按日期分组
GROUP BY download_date
-- 按日期降序排列，最新的日期在前
ORDER BY download_date DESC;

如果不想用子查询，也可以直接写为：

SELECT 
  COUNT(*) AS count,
  TO_CHAR(TO_TIMESTAMP(last_downloaded / 1000), 'DD-MM-YYYY') AS date
FROM stats
GROUP BY TO_CHAR(TO_TIMESTAMP(last_downloaded / 1000), 'DD-MM-YYYY')
ORDER BY TO_TIMESTAMP(last_downloaded / 1000)::DATE DESC;

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