mysql pivot 到 postgres 数据透视表

Question

在我最近将我的一个 Rails 应用程序切换到 heroku 并且不得不转换之前，我一直在使用 mysql。几乎一切都按预期工作，除了我有一个查询做了一些完全时髦的事情。

这是 postgres，但在 mysql 下，除了 EXTRACT DOW 和一些 group by additions 外，它大部分是相同的，但这不是问题，问题是它用于对列出的一周中的天数求和，现在它对整个表格求和...并且 AVG 也关闭，因为它也获得表格平均值而不是列出的天数。

有没有办法得到列出的天数的总和而不必做另一个选择，我错过了什么？......我想避免做 SELECT ( SELECT ... ) as SUBQUERY 只是为了得到一个列的总和。

谢谢

SELECT rooms.name, rooms.id,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -3 THEN (availables.price*1) ELSE 0 END) AS day1,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -2 THEN (availables.price*1) ELSE 0 END) AS day2,
MAX(CASE WHEN (EXTRACT(DOW FROM availables.bookdate) - EXTRACT(DOW FROM DATE '2010-01-20')) = -1 THEN (availables.price*1) ELSE 0 END) AS day3,
(AVG(availables.price)*1) AS avg,
(SUM(availables.price)*1) * 2 AS sum, 
MAX((SIGN(spots)-1) + 2) AS beds
 FROM availables
 INNER JOIN rooms
 ON availables.room_id=rooms.id
 WHERE availables.room_id = '1818' AND availables.price > 0
 GROUP BY rooms.id, rooms.name

Answer 1

你没有说模式是什么，所以我假装所有数据都在一个表中，省略了连接。用您的加入替换“东西”应该没有问题。

我创建了一个简单的表来代表您的加入:

wayne=# \d stuff
                            Table "pg_temp_1.stuff"
  Column  |     Type      |                     Modifiers
----------+---------------+----------------------------------------------------
 id       | integer       | not null default nextval('stuff_id_seq'::regclass)
 room_id  | integer       | not null
 bookdate | date          | not null
 price    | numeric(10,2) | not null
Indexes:
    "stuff_pkey" PRIMARY KEY, btree (id)

向其中添加了一些数据:

wayne=# select * from stuff;
 id | room_id |  bookdate  | price
----+---------+------------+-------
  1 |       1 | 2010-01-11 | 60.00
  2 |       1 | 2010-01-10 | 60.00
  3 |       2 | 2010-01-10 | 55.00
  4 |       2 | 2010-01-09 | 55.00
  5 |       3 | 2010-01-09 | 70.00
  6 |       3 | 2010-01-08 | 70.00
(6 rows)

这是过去两天加上今天的查询，按日期分组，包含计数、总和和平均价格。

wayne=# select bookdate, count(*), sum(price), avg(price) from stuff \
where bookdate >= date_trunc('day', now()) - interval '2 days' \
group by bookdate order by bookdate;
  bookdate  | count |  sum   |         avg
------------+-------+--------+---------------------
 2010-01-09 |     2 | 125.00 | 62.5000000000000000
 2010-01-10 |     2 | 115.00 | 57.5000000000000000
 2010-01-11 |     1 |  60.00 | 60.0000000000000000
(3 rows)