sql - 将列名称显示为从 current_date 开始的去年的月份

Question

我正在写一份报告，显示自去年以来每个月的代码总量。

目前，如果我只对过去一年的所有代码进行计数，那么我的结果集将如下所示

name    | code  | total   |  date
build1    x1      10        04-2013
build1    x50     60        05-2013
build1    x1      80        06-2013
build1    x90     450       07-2013

我能够转置所有行，因此所有列都是月份，总计在其下方。我的更新结果现在看起来像这样

name    |  code | apl |  may | jun | jul
build1     x1      10    0     80     0
build1     x50     0     60     0     0
build1     x90     0     0      0     450   

上面的代码是我正在寻找的结果，但我现在想做的是在当月订购所有东西，然后从当月倒退一年。

所以如果当前月份是七月那么我的结果集会这样排序

name    |  code | jul |  jun | may | apl
build1     x1      0     80     0     10
build1     x50     0      0     60     0
build1     x90     450    0     0      0 

我遇到的问题是我使用别名作为月份名称。而且您不能从别名中获取月份。另外，据我所知，别名是静态的，所以一旦你设置了它们就不能改变。将月份作为列名的唯一方法是从数据集中提取它。但是，当我将行转置为列时，我必须使用别名，因为我使用 case 语句来获取每个月的所有总计。

编辑:抱歉，postgresql 版本是 8.4，这是我到目前为止的查询

SELECT 


pname,
code,
SUM(totaljanurary)  AS "Janurary",
SUM(totalfebruary)  AS "February",
SUM(totalmarch)     AS "March",
SUM(totalapril)     AS "April",
SUM(totalmay)       AS "May",
SUM(totaljune)      AS "June",
SUM(totaljuly)      AS "July",
SUM(totalaugust)    AS "August",
SUM(totalseptember) AS "September",
SUM(totaloctober)   AS "October",
SUM(totalnovember)  AS "November",
SUM(totaldecember)  AS "December"

FROM(

SELECT 

    pname,
    code,
    SUM(case when extract (month FROM checked_date)=01 then total else 0 end) AS totaljanurary,
    SUM(case when extract (month FROM checked_date)=02 then total else 0 end) AS totalfebruary,
    SUM(case when extract (month FROM checked_date)=03 then total else 0 end) AS totalmarch,
    SUM(case when extract (month FROM checked_date)=04 then total else 0 end) AS totalapril,
    SUM(case when extract (month FROM checked_date)=05 then total else 0 end) AS totalmay,
    SUM(case when extract (month FROM checked_date)=06 then total else 0 end) AS totaljune,
    SUM(case when extract (month FROM checked_date)=07 then total else 0 end) AS totaljuly,
    SUM(case when extract (month FROM checked_date)=08 then total else 0 end) AS totalaugust,
    SUM(case when extract (month FROM checked_date)=09 then total else 0 end) AS totalseptember,
    SUM(case when extract (month FROM checked_date)=10 then total else 0 end) AS totaloctober,
    SUM(case when extract (month FROM checked_date)=11 then total else 0 end) AS totalnovember,
    SUM(case when extract (month FROM checked_date)=12 then total else 0 end) AS totaldecember



    FROM (

        --START HERE
        SELECT

            pname,
            code,
            COUNT(code)AS total,
            date_trunc('month',checked_date)::date AS checked_date


            FROM table1

            AND checked_date >= current_date-365
            AND checked_date <= current_date


            GROUP BY pname, code, date_trunc('month',checked_date)
    )T1
    GROUP BY pname, code, date_trunc('month',checked_date)

)T2
GROUP BY pname, code
ORDER BY pname, code

Answer 1

您需要 crosstab()，由附加模块 tablefunc 提供。

假设“日期”的类型是date(就像它应该的那样)。
其他详细信息取决于您忘记提供的详细信息。

SELECT * FROM crosstab(
     $$SELECT name, code, to_char("date", 'mon'), total
       FROM   tbl
       WHERE  "date" <  now()
       AND    "date" >= now() - interval '1 year'
       ORDER  BY name, extract(month from now()) DESC$$

    ,$$VALUES
      ('dec'::text), ('nov'), ('oct'), ('sep'), ('aug'), ('jul')
    , ('jun'),       ('may'), ('apr'), ('mar'), ('feb'), ('jan')$$
   )
AS ct (name text, code text
   , dec int, nov int, oct int, sep int, aug int, jul int
   , jun int, may int, apr int, mar int, feb int, jan int);

有关其他说明，请参阅此密切相关的答案:
Sum by month and put months as columns

你不应该使用 date 作为标识符，它是一个保留字。我双引号了它。
您也不应该使用非描述性名称 name。