PostgreSQL用户预订序列生成及聚合查询报错解决方案

我刚开始学习PostgreSQL，想要构建一张包含以下字段的聚合表：

• user_id
• booking_sequence
• booking_created_time
• booking_paid_time
• booking_price_amount
• total_spent

除booking_sequence字段外，其余所有字段均已提供。我需要编写查询语句，筛选出购买次数不少于指定次数、消费总额超过指定金额的每个用户的前5笔机票订单，查询结果先按用户消费总额排序，再按booking_sequence字段排序。

我最初尝试编写了如下代码：

select user_id,
       row_number() over(partition by user_id order by user_id) as booking_sequence,
       booking_created_time as booking_created_date,
       booking_price_amount,
       sum(booking_price_amount) as total_booking_price_amount
from fact_flight_sales 
group by user_id, booking_created_time, booking_price_amount 
having count(user_id) > 5
   and total_booking_price_amount > 1000
order by total_booking_price_amount;

运行时遇到两个问题：添加count(user_id) > 5条件后查询结果为空，且在HAVING子句中使用别名total_booking_price_amount时提示字段不存在。

最终实现方案 #

后续我调整代码实现了预期功能，参考代码如下：

select x.user_id, row_number() over(partition by x.user_id) 
as booking_sequence, x.booking_created_time::date as booking_created_date, x.booking_price_amount, 
sum(y.booking_price_amount) as total_booking_price_amount from 
(
    select user_id, booking_created_time, booking_price_amount from fact_flight_sales 
    group by user_id, booking_created_time, booking_price_amount
) as x
join 
(
    select user_id, booking_price_amount 
    from fact_flight_sales group by user_id, booking_price_amount
) as y
on x.user_id = y.user_id
group by x.user_id, x.booking_created_time, x.booking_price_amount 
having count(x.user_id) >= 1 and sum(y.booking_price_amount) >250000 
order by total_booking_price_amount desc, booking_sequence asc;

感谢Laurenz的帮助。

内容的提问来源于stack exchange，提问作者Rivaldo