复杂SQL计算

Question

我有一堆Venmo数据，我想在Stata中使用它之前先在SQL中计算某些指标，主要是由于大小限制。数据包括:"发件人"、"收件人"、"金额"、"日期"。在本例中，发送者和";Receiver">可以是同一个人。我想写一个SQL语句，它计算发送给除发送方以外的所有个人的金额的平方和，以及发送给所有各方(包括他们自己)的总金额。数学:

sum (i=/=j) (Amount{i,j,d}/Amount{i,d})^2

其中Amount{i,j,d}表示从一个人发送给另一个人的金额，其中i和j不是同一个人。简单地说，就是找出每个人发送给其他人的邮件总数，将其平方除以那个人在某一天发送的邮件总数，然后将所有这些项加起来。我尝试了以下查询(它不寻求做这个计算，只是为了获得我可以用来这样做的数据):

SELECT Amount, Sender, Receiver, Date,
SUM(Amount) as Total_Amount
FROM Table
GROUP BY Sender Receiver Date
ORDER BY Sender Receiver Date

然而，这种拉动产生了太多的数据点，我那微不足道的电脑无法在Stata中处理。然后我尝试了以下操作:

SELECT Amount, Sender, Receiver, Date,
SUM(Amount) as Total_Amount,
SUM(POWER(SUM(Amount)/(Total_Amount), 2)) as Sum_Squares
FROM Table
GROUP BY Sender Receiver Date
ORDER BY Sender Receiver Date

我收到一个错误，告诉我它不将Total_Amount识别为列，这大致是我所期望的。所以现在我卡住了，因为我不知道如何构造这个Total_Amount变量，然后在我想要运行的另一个计算中使用它。任何关于如何直接计算我上面描述的术语的建议都将是真诚的感谢。如有任何澄清，请评论。谢谢你。

Answer 1

要根据第一个求和的结果进行求和，您可以使用子查询，并使用窗口函数(需要mysql 8或mariadb 10.2+)来获得该子查询中所有收件人的发件人总数。

SELECT Date, Sender, SUM(POWER(Sender_Receiver_Total/Sender_Total, 2)) Sum_Squares
FROM (
SELECT Date, Sender, Receiver,
SUM(Amount) Sender_Receiver_Total,
SUM(SUM(Amount)) OVER (PARTITION BY Date, Sender) Sender_Total
FROM `Table`
GROUP BY Date, Sender, Receiver
) Date_Sender_Receiver
GROUP BY Date, Sender