sql - 如何在查询的 AS 部分包含一个函数？

Question

我查询 Postgres 数据库以获取过去 4 年的统计信息。
但是我无法找出替换硬编码日期的正确语法在带有函数的查询的 AS 部分。

说:date_part('year',current_date)-1 而不是 "2012"

SELECT * FROM crosstab('
SELECT client,date_part (''year'',date) as SalesDate
       ,Sum(total)::integer AS Synthese
FROM statistic
WHERE date_part(''year'',date)>date_part(''year'',current_date)-5
AND date_part(''year'',date)<date_part(''year'',current_date)
GROUP BY client,SalesDate
ORDER BY 1,2',
$$VALUES 
(date_part('year',current_date)-4),
(date_part('year',current_date)-3),
(date_part('year',current_date)-2),
(date_part('year',current_date)-1)$$)
AS ( client text, "2009" text, "2010" text , "2011" text, "2012" text);

Answer 1

回答

SQL 的一个原则:您可以动态提供值，但标识符是静态的。

外部调用是一个基本的SQL语句，列定义列表由标识符组成，而不是值。 (这就是为什么你被迫双引号以数字开头的非法标识符。)因此，你不能在这个地方使用函数调用。只是不可能。

可能的解决方法

有一些棘手的方法可以解决这个问题..您可以创建一个返回 polymorphic composite type 的 plpgsql 函数并提交一个定义明确的复合类型的参数，该参数还带有列名。但那是非常先进的东西。为此，您需要跟上 plpgsql 的速度。

考虑一下我在这个相关问题下的综合回答:
Refactor a PL/pgSQL function to return the output of various SELECT queries
答案的后半部分是给你的。

由于您需要一个定义明确的复合类型，因此您必须在以这种方式调用函数之前创建一个类型。或者只是创建一个临时表(自动提供一个类型)。您可以使用 DO 语句自动执行此操作，该语句使用动态 SQL 从当前日期派生列名称……我从未说过这很简单，但这是可能的。

基础查询

与此同时，您的crosstab() 查询可以改进:

SELECT * FROM crosstab(
    $$
    SELECT client
          ,date_part ('year', date) AS salesdate
          ,sum(total)::int AS synthese
    FROM   statistic
    WHERE  date >= date_trunc('year', now()) - interval '5y'
    AND    date <  date_trunc('year', now())
    GROUP  BY 1,2
    ORDER  BY 1,2
    $$

    ,$$VALUES 
         (date_part('year', now()) - 4)
        ,(date_part('year', now()) - 3)
        ,(date_part('year', now()) - 2)
        ,(date_part('year', now()) - 1)
    $$
    )
AS  (client text
     ,"2009" text
     ,"2010" text
     ,"2011" text
     ,"2012" text);

• 使用美元报价避免引用 hell 。
• 重写您的 WHERE 条件，以便它们可以使用索引。
  Detailed explanation in this closely related answer.

剩下的就是品味和风格了。