SQL计算Venmo交易数据指定平方和指标的方法咨询

你的原有查询有两个核心错误：

• SQL执行顺序决定了同层级SELECT中定义的别名无法被同层其他计算逻辑直接引用，这是报Total_Amount不存在的直接原因
• 分组粒度不符合计算逻辑：你需要的Total_Amount是「单个付款方+单个日期」维度的全量转账总额，原查询分组带了Receiver字段，计算出的SUM结果只是付款方当日给某一个收款方的转账额，不是当日全局总额。

以下写法最终输出粒度为「付款方+日期」，每一行对应一个付款方单日的目标指标值，数据量远小于明细级导出，完全适配低性能设备后续Stata分析：

支持窗口函数的环境（主流SQL版本：MySQL8.0+、PostgreSQL、SQLite3.25+、BigQuery等） #

用窗口函数可以避免表自连接，计算效率最高：

WITH daily_pair_agg AS (
    SELECT
        Sender,
        Receiver,
        Date,
        SUM(Amount) AS pair_transfer_amt,
        -- 窗口函数直接取同付款人同日期的总转账额
        SUM(SUM(Amount)) OVER (PARTITION BY Sender, Date) AS sender_daily_total
    FROM venmo_transactions -- 替换为你的实际表名
    GROUP BY Sender, Receiver, Date
)
SELECT
    Sender,
    Date,
    sender_daily_total,
    SUM(POWER(pair_transfer_amt / sender_daily_total, 2)) AS non_self_transfer_sq_sum
FROM daily_pair_agg
WHERE Sender != Receiver -- 排除给自己转账的记录
GROUP BY Sender, Date, sender_daily_total
ORDER BY Sender, Date;

兼容老版本SQL（无窗口函数支持） #

如果你的运行环境不支持窗口函数，可以用双聚合左连接的写法实现，性能也能满足常规交易数据集的计算需求：

SELECT
    pair_agg.Sender,
    pair_agg.Date,
    total_agg.sender_daily_total,
    SUM(POWER(pair_agg.pair_transfer_amt / total_agg.sender_daily_total, 2)) AS non_self_transfer_sq_sum
FROM (
    -- 第一层聚合：付款方-收款方-日期维度的转账总额
    SELECT
        Sender,
        Receiver,
        Date,
        SUM(Amount) AS pair_transfer_amt
    FROM venmo_transactions -- 替换为你的实际表名
    GROUP BY Sender, Receiver, Date
) pair_agg
LEFT JOIN (
    -- 第二层聚合：付款方-日期维度的当日总转账额
    SELECT
        Sender,
        Date,
        SUM(Amount) AS sender_daily_total
    FROM venmo_transactions
    GROUP BY Sender, Date
) total_agg
ON pair_agg.Sender = total_agg.Sender AND pair_agg.Date = total_agg.Date
WHERE pair_agg.Sender != pair_agg.Receiver
GROUP BY pair_agg.Sender, pair_agg.Date, total_agg.sender_daily_total
ORDER BY pair_agg.Sender, pair_agg.Date;

计算逻辑完全匹配给出的规则：

1. 先聚合得到付款方当日给每个不同收款方的总转账额
2. 匹配到对应付款方当日所有转账（含给自己转账）的总金额
3. 排除收款方为自己的转账记录后，计算每笔对外转账占当日总转账额的比例，取平方后求和，得到最终指标。

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