sql - 我如何调整此查询以生成显示随时间推移按月计算平均值的结果

Question

我很难用我的一个查询产生预期的结果。

我希望能够根据以下条件逐月显示每个用户产生的平均收入:

• 用户必须属于特定群组，定义为在过去 90 天内预订超过 20 次的用户(因此，例如，只有预订超过 20 次的用户才会被计入 1 月群组跨越 11 月、12 月和 1 月)

下面的查询是我现在拥有的，它提取了 1 月份群组的每位用户的平均收入:

WITH bookings as (SELECT u.id as user_id, count(*) as bookings_last_90, sum(total)/100 as revenue_last_90
            FROM revenue r
            JOIN users u on r.user_id = u.id
            WHERE (CAST(r.created_at AS date) BETWEEN CAST((NOW() + INTERVAL '-90 day') AS date)
                AND CAST(now() AS date))
            GROUP BY u.id 
            HAVING COUNT(*) >= 20)
SELECT avg(b.revenue_last_90)
FROM bookings b;

我基本上需要调整上述查询，以逐月滚动提取每个同类群组用户的平均收入，同时保持过去 90 天的同类群组定义时间范围不变。

Answer 1

当您有一个使用一个时间戳的查询时，一般方法是:

1. 生成日期或时间戳列表以在表、 View 、CTE 等中使用
2. 加入时间戳列表
3. 用列表中的时间戳替换您正在使用的时间戳

没有模式，我无法测试它，但结果可能类似于:

WITH --first generate list of dates from the created_at field in revenue
month_list as (select date_trunc('month' , r.created_at) as m from revenue r group by 1 )

--then use that in the bookings query
, bookings as (SELECT u.id as user_id, m.m as cohort_month, count(*) as bookings_last_90, sum(total)/100 as revenue_last_90
            FROM revenue r
            JOIN users u on r.user_id = u.id
            join month_list m on r.created_at between m.m + interval'-60 day' and m.m + interval'1 month'
            WHERE true
            GROUP BY u.id , m.m
            HAVING COUNT(*) >= 20)

--finally, use the date in the result query
SELECT avg(b.revenue_last_90), cohort_month
FROM bookings b group by cohort_month;