Oracle SQL - 将表行数据透视表并在数据透视表中使用子查询

2022-01-22 00:00:00 pivot sql oracle rollup

我正在开发 Oracle 12c R1 db，并有一个包含示例数据的示例视图，如下所示:
视图名称:CUST_HOTEL_VIEW

Am working on Oracle 12c R1 db and have a sample view with sample data as below:
View Name: 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        |
+----------------+---------------+---------------+

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

From the data above, am trying to get below pivot output with Row Grand Total, Column Grand Total and Number of Hotels booked per customer:

+----------------+-------------+---------------+--------------+-------------+----------+
|    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 |
+----------------+-------------+---------------+--------------+-------------+----------+

我尝试了下面的查询来生成数据透视数据

I tried below query to generate pivot data

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. 如何包含行总计
2. 如何包含列总计
3.如何包括预订酒店数量和
3. 是否可以在 PIVOT FOR HOTEL IN 子句中编写子查询.(我尝试了子查询但出现错误)

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

I would like to know:
1. How to include Row Grand Total
2. How to include Column Grand Total
3. How to include Number of Booked hotels and
3. Is it possible to write subquery inside PIVOT FOR HOTEL IN clause. (I tried subquery but getting error)

I appreciate any help on this.

谢谢，
里查

推荐答案

只要使用条件聚合即可:

Just use conditional aggregation:

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 语句那样是构建块.

Conditional aggregation is much more flexible then pivot. Personally, I see no reason for the pivot syntax: it does one thing well, but is not a building block the way tradition SQL statements are.

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

ROLLUP() is also quite helpful. You can also use:

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

相关文章