SQL Query -需要帮助来运行最后一个查询

Question

with hotels as (
select * from hotel_revenue_historical_full
union
SELECT * FROM projects.`db.2019`
union
SELECT * FROM projects.`2020`);
select * from hotels;
select stays_in_weekend_nights + stays_in_week_nights from hotels;
select arrival_date_year,hotel, sum ((stays_in_weekend_nights + stays_in_week_nights) * adr) as revenue from hotels group by arrival_year, hotel;

Answer 1

-- save the data into temptable
CREATE TEMPORARY TABLE hotels
select * from hotel_revenue_historical_full
union
SELECT * FROM projects.`db.2019`
union
SELECT * FROM projects.`2020`;
-- query needed information
select *, stays_in_weekend_nights + stays_in_week_nights from hotels;
select arrival_date_year,hotel, sum ((stays_in_weekend_nights + stays_in_week_nights) * adr) as revenue from hotels group by arrival_year, hotel;
-- drop temptable
DROP TEMPORARY TABLE hotels;

如果表hotel不是很大，那么你可以在它的定义中添加ENGINE = Memory，这将改善进程。

我也推荐你使用不UNION但UNION ALL。