sql - Oracle SQL - 将表行转换为列并在数据透视表中使用子查询

Question

我正在使用 Oracle 12c R1 数据库，并有一个包含示例数据的示例 View ，如下所示:
View 名称:CUST_HOTEL_VIEW

+----------------+---------------+---------------+
|    Customer    |     Hotel     | Booked Status |
+----------------+---------------+---------------+
| John Smith     | Beverly Hills | Booked        |
| John Smith     | Royal Palms   |               |
| Marilyn Lawson | Beverly Hills |               |
| John Smith     | Ritz-Carlton  |               |
| Marilyn Lawson | Royal Palms   |               |
| Sarah Elliot   | Royal Palms   |               |
| Sarah Elliot   | Ritz-Carlton  | Booked        |
| Sarah Elliot   | Royal Palms   | Booked        |
+----------------+---------------+---------------+

根据上面的数据，我试图通过行总计、列总计和每个客户预订的酒店数量获得低于枢轴的输出:

+----------------+-------------+---------------+--------------+-------------+----------+
|    Customer    | Royal Palms | Beverly Hills | Ritz-Carlton | Grand Total | # Booked |
+----------------+-------------+---------------+--------------+-------------+----------+
| John Smith     |           1 |             1 |            1 |           3 |        1 |
| Marilyn Lawson |           1 |             1 |              |           2 |        - |
| Sarah Elliot   |           2 |               |            1 |           3 |        2 |
| Grand Total    |           4 |             2 |            2 |           8 |        3 |
+----------------+-------------+---------------+--------------+-------------+----------+

我尝试通过以下查询生成数据透视表

SELECT * FROM
(
  SELECT CUSTOMER, HOTEL
  FROM CUST_HOTEL_VIEW
)
PIVOT
(
  COUNT(HOTEL)
  FOR HOTEL IN ('Royal Palms' as "Royal Palms",'Beverly Hills' as "Beverly Hills",'Ritz-Carlton' as "Ritz-Carlton")
)
ORDER BY CUSTOMER

我想知道:
1.如何包含Row Grand Total
2.如何包含Column Grand Total
3.如何包括预订酒店数量和
3. 是否可以在 PIVOT FOR HOTEL IN 子句中编写子查询。 (我尝试了子查询但出现错误)

我很感激这方面的任何帮助。

谢谢，
里查

Answer 1

只需使用条件聚合:

SELECT COALESCE(customer, 'Grand Total') as customer,
       SUM(CASE WHEN Hotel = 'Royal Palms' THEN 1 ELSE 0 END) as "Royal Palms",
       SUM(CASE WHEN Hotel = 'Beverly Hills' THEN 1 ELSE 0 END) as "Beverly Hills",       
       SUM(CASE WHEN Hotel = 'Ritz-Carlton' THEN 1 ELSE 0 END) as "Ritz-Carlton" ,
       COUNT(*) as "Grand Total",
       COUNT(Booked_Status) as "Num Booked"
FROM CUST_HOTEL_VIEW
GROUP BY ROLLUP(CUSTOMER)
ORDER BY CUSTOMER;

条件聚合比 pivot 灵活得多。就我个人而言，我认为没有理由使用 pivot 语法:它在一件事上做得很好，但不像传统 SQL 语句那样是构建 block 。

ROLLUP() 也很有帮助。您还可以使用:

GROUP BY GROUPING SETS ( (CUSTOMER), () )