postgres中的CROSSTAB和DATEPART?它有效吗

Question

我有一个日期格式类似2015-02-19的列，我正试图将年份拉出来，并将年份作为单独的列来显示当年的总销售额。我的源查询运行良好，但在尝试运行完整查询时，我遇到了yearly_sales 的语法错误

SELECT *
FROM CROSSTAB(
'SELECT sales_agent, date_part('year', sales_date) AS yearly_sales, SUM(daily_sales)
FROM agent_sales_table
GROUP BY sales_agent, yearly_sales
ORDER BY sales_agent, yearly_sales') AS source (sales_agent text, 2015 numeric, 2016 numeric);

ERROR:  syntax error at or near "year"
LINE 3:  'SELECT sales_agent, date_part('year', sales_date) AS yearly_sales, SUM(daily_s...
^

那么，日期部分是否适用于交叉表？

Answer 1

我只需要在这里使用条件聚合。我发现语法更容易理解；此外，这可能是一个更有效的解决方案，特别是如果您使用正确的语法来过滤日期，即：

select
sales_agent,
sum(daily_sales) filter(where sales_date >= date '2015-01-01' and sales_date < date '2016-01-01') sales_2015
sum(daily_sales) filter(where sales_date >= date '2016-01-01' and sales_date < date '2017-01-01') sales_2016
from mytable
group by sales_agent

此查询可能会利用sales_agent, sales_date)上的索引。

您可以很容易地扩展select子句来处理更多的年份。添加一个where子句来限制要提前聚合的行可能也是一个好主意。事实上，如果你只想要2015年和2016年，查询可以写成：

select
sales_agent,
sum(daily_sales) filter(where sales_date <  date '2016-01-01') sales_2015
sum(daily_sales) filter(where sales_date >= date '2016-01-01') sales_2016
from mytable
where sales_date >= date '2015-01-01' and sales_date < date '2017-01-01'
group by sales_agent

Answer 2

SELECT*来自交叉表('选择sales_agent，date_part('year'，sales_date(AS yearly_sales，SUM(daily_sales(FROM agent_sales_table按销售代理、年度销售分组ORDER BY sales_agent，yearly_sales'(作为来源(sales_agent文本，2015数字，2016数字(；