如何连接用户、存款、取款三张表完成求和并按多字段分组查询

原始表说明 #

用户表（User Table） #

存款表（Deposit Table） #

取款表（Withdrawal Table） #

#

查询需求 #

需要关联上述三张表，按以下四个维度分组统计：

• Month（月份）
• Sex（性别）
• Age（年龄）
• State（州）
  统计指标包含：
• 分组总存款金额：sum(Deposit.amount)
• 分组总取款金额：sum(Withdrawal.amount)
• 分组内用户数量
  分组示例：

分组1：月份=一月、性别=女、年龄=18、所在州=亚利桑那州
分组2：月份=一月、性别=男、年龄=21、所在州=得克萨斯州

补充说明 #

预期输出的月份字段由存款表和取款表的交易月份合并得出，用户表中新增的「账户创建月份」字段可作为三张表按月份关联的可选字段。
预期输出效果参考：
#

实现方案 #

核心思路 #

为了避免多表直接关联产生笛卡尔积导致金额计算重复，先分别聚合存、取款表的单用户单月交易数据，再合并交易数据关联用户属性，最后按目标维度分组汇总即可。

示例SQL代码（标准SQL语法） #

WITH monthly_deposit AS (
    -- 预聚合单用户单月存款总额
    SELECT
        user_id,
        DATE_TRUNC('month', transaction_date) AS tran_month,
        SUM(amount) AS total_deposit
    FROM deposit
    GROUP BY user_id, DATE_TRUNC('month', transaction_date)
),
monthly_withdrawal AS (
    -- 预聚合单用户单月取款总额
    SELECT
        user_id,
        DATE_TRUNC('month', transaction_date) AS tran_month,
        SUM(amount) AS total_withdrawal
    FROM withdrawal
    GROUP BY user_id, DATE_TRUNC('month', transaction_date)
),
user_monthly_tran AS (
    -- 合并存、取款的月度用户交易数据
    SELECT
        COALESCE(d.user_id, w.user_id) AS user_id,
        COALESCE(d.tran_month, w.tran_month) AS stat_month,
        COALESCE(d.total_deposit, 0) AS total_deposit,
        COALESCE(w.total_withdrawal, 0) AS total_withdrawal
    FROM monthly_deposit d
    FULL OUTER JOIN monthly_withdrawal w
        ON d.user_id = w.user_id AND d.tran_month = w.tran_month
)
-- 关联用户属性后按目标维度分组统计
SELECT
    TO_CHAR(umt.stat_month, 'FMMonth') AS "Month",
    u.sex AS "Sex",
    u.age AS "Age",
    u.state AS "State",
    SUM(umt.total_deposit) AS total_deposit_amount,
    SUM(umt.total_withdrawal) AS total_withdrawal_amount,
    COUNT(DISTINCT umt.user_id) AS group_user_count
FROM user_monthly_tran umt
LEFT JOIN "user" u ON umt.user_id = u.user_id
GROUP BY "Month", "Sex", "Age", "State"
ORDER BY "Month", "State", "Sex", "Age";

可选调整 #

如果需要统计仅创建账户、当月没有任何交易的用户分组，只需将最后一步的关联逻辑改为从用户表左连user_monthly_tran，关联条件额外添加u.account_created_month = umt.stat_month即可。

内容的提问来源于stack exchange，提问作者BreadBoard.ini