带有CASE和聚合的PostgreSQL crosstab()替代 [英] PostgreSQL crosstab() alternative with CASE and aggregates

问题描述

我想创建一个数据透视表视图，以显示每个travel_mode的每月预订总金额.

I want to create a pivot table view showing month on month sum of bookings for every travel_mode.

表bookings:

  timestamp
, bookings
, provider_id

表providers:

  provider_id
, travel_mode

不要使用数据透视表功能和交叉表功能.所以我正在尝试使用JOIN和CASE.以下是查询:

Pivot table function and crosstab functions are not to be used to do this. So I am trying to use JOIN and CASE. Following is the query:

  SELECT b.month, 
  (CASE WHEN p.travel_mode=train then b.amount end)train,
  (CASE WHEN p.travel_mode=bus then b.amount end)bus, 
  (CASE WHEN p.travel_mode=air then b.amount end)air
  FROM 
  (SELECT  to_char(date_,month) as month, travel_mode, sum(bookings) as amount
  from bookings as b
  join providers as p
  on b.provider_id=p.provider_id
  group by b.month, p.travel_mode)
  group by b.month;

但是我收到一条错误消息:

However I am getting an error which says:

subquery in FROM must have an alias LINE 6:

当我添加一个别名时，它会抛出一个错误:

And when I add an alias it throws an error saying:

column p.travel_mode must appear in the GROUP BY clause or be used in an aggregate function 
LINE 2:

最终结果应该是这样

Month       Air             Bus            Train  
01          Amount(air)     Amount(Bus)    Amount(train)

我觉得在某个地方这是一个小错误，但我根本无法弄清楚.

I have a feeling it is a minor error somewhere but I am unable to figure it out at all.

P.S.我不得不删除问题中的所有引号，因为它不允许我发布此内容.但是在实际查询中会解决这些问题.

P.S. I had to remove all quotations in the question as it was not allowing me to post this. But those are being taken care of in the actual query.

推荐答案

多个问题.缺少的表别名只是其中之一.此查询应该可以工作:

Multiple problems. The missing table alias is just one of them. This query should work:

SELECT month
     , sum(CASE WHEN travel_mode = 'train' THEN amount END) AS train
     , sum(CASE WHEN travel_mode = 'bus'   THEN amount END) AS bus
     , sum(CASE WHEN travel_mode = 'air'   THEN amount END) AS air
FROM  (
   SELECT to_char(timestamp, 'MM') AS month, travel_mode, sum(bookings) AS amount
   FROM   bookings  b
   JOIN   providers p USING (provider_id)
   GROUP  BY month, p.travel_mode
   ) sub
GROUP  BY month;

• 缺少字符串文字的单引号. (您似乎已删除了那些无法发布报价的错误印象.)

  • Missing single quotes for string literals. (You seem to have removed those being under the wrong impression you couldn't post quotations.)

    缺少子查询的表别名-就像第一条错误消息所说的一样.

    Missing table alias for the subquery - just like the 1st error message says.

    在外部查询中，子查询中基础表的表名(或别名)不可见.只有子查询的表别名是.由于只有一个 子查询，因此您根本不需要表限定.

    In the outer query, table names (or aliases) of underlying tables in the subquery are not visible. Only the table alias of the subquery is. Since there is only one subquery, you don't need table-qualification at all there.

    month是输出列名称(不在基础表中)，因此表限定符b.month也是错误的.

    month is an output column name (not in the underlying table), so the table qualification b.month was wrong, too.

    您似乎想要几个月的2位数字.使用模板模式'MM'而不是to_char()的月".

    You seem to want 2-digit numbers for months. Use the template pattern 'MM' instead of 'month' with to_char().

    外部查询中的聚合不能像您遇到的那样工作-就像您的第二条错误消息所说的那样.您必须将外部CASE表达式包装在聚合函数中.在这种情况下，您也可以使用min()或max()，因为子查询之后的行永远不会超过一排.

    The aggregation in the outer query does not work like you had it - just like your 2nd error message says. You have to wrap the outer CASE expression in a aggregate function. You might as well use min() or max() in this case, because there are never more than one rows after the subquery.

    仍不清楚date_来自何处?您是说timestamp? (这不是一个很好的标识符).

    Still unclear where date_ is coming from? You mean timestamp? (which is not a good identifier).

    但是您不需要子查询开头，并且可以简化为:

    But you don't need the subquery to begin with and can simplify to:

    SELECT to_char(timestamp, 'MM') AS month
         , sum(CASE WHEN p.travel_mode = 'train' THEN b.bookings END) AS train
         , sum(CASE WHEN p.travel_mode = 'bus'   THEN b.bookings END) AS bus
         , sum(CASE WHEN p.travel_mode = 'air'   THEN b.bookings END) AS air
    FROM   bookings  b
    JOIN   providers p USING (provider_id)
    GROUP  BY 1;
    

    为获得最佳性能，您仍应使用crosstab()，

    For best performance you should still use crosstab(), though:

    • PostgreSQL交叉表查询

    这篇关于带有CASE和聚合的PostgreSQL crosstab()替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！