sql - 如何将 DISTINCT 与 string_agg() 和 to_timestamp() 一起使用？

Question

我想在一行中使用逗号分隔的唯一 from_date。

所以我在 TO_TIMESTAMP() 中使用 distinct() 函数，但出现错误。

SELECT string_agg(TO_CHAR(TO_TIMESTAMP(distinct(from_date) / 1000), 'DD-MM-YYYY'), ',')
FROM trn_day_bookkeeping_income_expense 
GROUP BY from_date,enterprise_id having enterprise_id = 5134650;

我想要这样的输出:

01-10-2017,01-11-2017,01-12-2017

但我收到如下错误:

ERROR:  DISTINCT specified, but to_timestamp is not an aggregate function
LINE 1: SELECT string_agg(TO_CHAR(TO_TIMESTAMP(distinct(from_date) /...**

Answer 1

DISTINCT 既不是函数也不是运算符，而是 SQL 构造或语法元素。可以添加为前导关键字 to the whole SELECT list或 within most aggregate functions .

将其添加到子选择中的 SELECT 列表(在您的案例中由单个列组成)，您还可以在其中廉价地添加 ORDER BY。应该产生最佳性能:

SELECT string_agg(to_char(the_date, 'DD-MM-YYYY'), ',') AS the_dates
FROM  (
   SELECT DISTINCT to_timestamp(from_date / 1000)::date AS the_date
   FROM   trn_day_bookkeeping_income_expense 
   WHERE  enterprise_id = 5134650
   ORDER  BY the_date   -- assuming this is the order you want 
   ) sub;

首先生成日期(多个不同的值可能会导致相同的日期!)。
然后是 DISTINCT 步骤(或 GROUP BY)。
(同时，可选择添加 ORDER BY。)
最后聚合。

(enterprise_id) 或更好的 (enterprise_id, from_date) 上的索引应该会大大提高性能。

理想情况下，时间戳以 timestamp 类型存储。或者 timestamptz。见:

• Ignoring time zones altogether in Rails and PostgreSQL

DISTINCT ON 是标准 SQL DISTINCT 功能的特定于 Postgres 的扩展。见:

• Select first row in each GROUP BY group?

或者，您还可以添加DISTINCT(和 ORDER BY)直接到聚合函数string_agg():

SELECT string_agg(DISTINCT to_char(to_timestamp(from_date / 1000), 'DD-MM-YYYY'), ',' ORDER BY to_char(to_timestamp(from_date / 1000), 'DD-MM-YYYY')) AS the_dates
FROM   trn_day_bookkeeping_income_expense 
WHERE  enterprise_id = 5134650

但这会很丑陋，难以阅读和维护，而且成本更高。 (使用 EXPLAIN ANALYZE 进行测试)。