每月事务处理用户占滚动12个月期间的用户比例，但具有分组- Postgres

要在PostgreSQL中计算每月事务处理用户占滚动12个月期间用户的比例，并且具有分组功能，可以使用窗口函数和CTE（Common Table Expressions）。以下是一个示例查询，假设你有一个名为transactions的表，其中包含user_id和transaction_date字段。

WITH monthly_users AS (
    SELECT
        DATE_TRUNC('month', transaction_date) AS month,
        user_id
    FROM
        transactions
    GROUP BY
        DATE_TRUNC('month', transaction_date),
        user_id
),
rolling_12_month_users AS (
    SELECT
        month,
        user_id
    FROM
        monthly_users
    WHERE
        month >= CURRENT_DATE - INTERVAL '11 month'
    GROUP BY
        user_id
    HAVING
        COUNT(DISTINCT DATE_TRUNC('month', transaction_date)) = 12
),
monthly_active_users AS (
    SELECT
        DATE_TRUNC('month', transaction_date) AS month,
        COUNT(DISTINCT user_id) AS active_users
    FROM
        transactions
    WHERE
        transaction_date >= CURRENT_DATE - INTERVAL '11 month'
    GROUP BY
        DATE_TRUNC('month', transaction_date)
),
monthly_total_users AS (
    SELECT
        month,
        COUNT(DISTINCT user_id) AS total_users
    FROM
        rolling_12_month_users
    GROUP BY
        month
)
SELECT
    ma.month,
    ma.active_users,
    mt.total_users,
    (ma.active_users::float / mt.total_users::float) * 100 AS percentage
FROM
    monthly_active_users ma
JOIN
    monthly_total_users mt
ON
    ma.month = mt.month
ORDER BY
    ma.month;

解释

monthly_users: 这个CTE将事务按月份和用户ID分组，确保每个用户在每个月只计算一次。
rolling_12_month_users: 这个CTE筛选出在过去12个月内有交易记录的用户，并确保这些用户在这12个月中每个月都有交易记录。
monthly_active_users: 这个CTE计算每个月活跃的用户数（即在该月有交易记录的用户数）。
monthly_total_users: 这个CTE计算每个月在滚动12个月期间内的总用户数。
最终查询: 将monthly_active_users和monthly_total_users连接起来，并计算每个月活跃用户占总用户的百分比。

分组功能

如果你需要按某个字段（例如category）进行分组，可以在每个CTE中添加相应的GROUP BY子句，并在最终查询中进行相应的调整。

例如，如果你的transactions表有一个category字段，你可以这样修改查询：

WITH monthly_users AS (
    SELECT
        DATE_TRUNC('month', transaction_date) AS month,
        category,
        user_id
    FROM
        transactions
    GROUP BY
        DATE_TRUNC('month', transaction_date),
        category,
        user_id
),
rolling_12_month_users AS (
    SELECT
        month,
        category,
        user_id
    FROM
        monthly_users
    WHERE
        month >= CURRENT_DATE - INTERVAL '11 month'
    GROUP BY
        category,
        user_id
    HAVING
        COUNT(DISTINCT DATE_TRUNC('month', transaction_date)) = 12
),
monthly_active_users AS (
    SELECT
        DATE_TRUNC('month', transaction_date) AS month,
        category,
        COUNT(DISTINCT user_id) AS active_users
    FROM
        transactions
    WHERE
        transaction_date >= CURRENT_DATE - INTERVAL '11 month'
    GROUP BY
        DATE_TRUNC('month', transaction_date),
        category
),
monthly_total_users AS (
    SELECT
        month,
        category,
        COUNT(DISTINCT user_id) AS total_users
    FROM
        rolling_12_month_users
    GROUP BY
        month,
        category
)
SELECT
    ma.month,
    ma.category,
    ma.active_users,
    mt.total_users,
    (ma.active_users::float / mt.total_users::float) * 100 AS percentage
FROM
    monthly_active_users ma
JOIN
    monthly_total_users mt
ON
    ma.month = mt.month AND ma.category = mt.category
ORDER BY
    ma.month, ma.category;

这样，你就可以按月份和类别计算每月事务处理用户占滚动12个月期间用户的比例。