单个SQL表上的聚合计算查询 [英] Aggregate calculation query on single SQL table

问题描述

在我的数据库中，我有一个表 payers_payments，它由一些外键(payer_id、payment_id)和一些数据透视字段(amount, pays).

In my database I have a table, payers_payments, consisting of some foreign keys (payer_id, payment_id) and some pivot fields (amount, pays).

| id | payer_id | payment_id | amount | pays |
|----|----------|------------|--------|------|
| 1  | 1        | 1          | 20     | 0    |
| 2  | 2        | 1          | 23     | 1    |
| 4  | 1        | 2          | 14     | 1    |
| 5  | 2        | 2          | 17     | 1    |
| 6  | 1        | 3          | 10     | 1    |
| 7  | 2        | 3          | 0      | 0    |

每一行代表payer对payment的贡献.pays 字段指定该 payer 是否应包含在付款中.

Each row represents a contribution a payer made towards a payment. The pays field specifies whether that payer should be included in the payment.

payment 的总成本是给定 payment_id 的 amount 总和.因此，如果我想查看支付 2 的费用，我会运行:

The total cost of a payment is the sum of amount for a given payment_id. So if I wanted to see how much payment 2 cost I would run:

SELECT SUM(amount) AS sumOfPayment 
  FROM payers_payments  
 WHERE payment_id=2;

同样，给定的payer(比如付款人 1)贡献的总金额是:

Likewise the total amount a given payer (say, payer 1) has contributed is:

SELECT SUM(amount) AS sumOfPayment 
  FROM payers_payments 
 WHERE payer_id=1;

现在，我想做的是使用公平分享的概念.公平份额是付款的总成本除以拥有该付款的 pay=1 的 payers 的数量.我可以表达的最简单的方法是使用子查询:

Now, what I want to do is use the concept of fair share. Fair share is the total cost of the payment divided by the number of payers who have pay=1 for that payment. The simplest way I can express this is with a sub-query:

SELECT SUM(payers_payments.amount) / (SELECT count(*) 
                                        FROM payers_payments 
                                       WHERE pays 
                                             AND payers_payments.payment_id = 3
                                             ) AS FairShare
  FROM payers_payments
 WHERE payment_id=3
GROUP BY 
       payers_payments.payment_id;

对于给定的payer和payment，他们的公平份额定义为:

For a given payer and payment their fair share is defined as:

SELECT IF(pays, FairShare, 0) as payerFairShare 
  FROM payers_payments 
 WHERE payer_id = 1 
       AND payment_id=3; --FairShare is the query as above

我的问题是，我想要一个查询，根据每笔付款的公平份额以及它们是否包含在每个payer 中获得总公平份额付款.(如果 pays=false 则他们的公平份额为 0)

My question is that I want a query to get the total fair share for each payer based on the fair share of each payment and whether or not they are included in the payment. (If pays=false then their fair share is 0)

根据上述数据，这是我对 2 个付款人所追求的结果:

Based on the above data this is the kind of result I'm after for the 2 payers:

| payer_id | total_paid | fair_share |
|----------|------------|------------|
| 1        | 44         | 25.5       |
| 2        | 40         | 58.5       |

有没有办法在单个查询中实现这一点，或者我必须对结果集进行一些循环?我对 RDMS 不可知，但类似 ​​MySQL 的东西很好.

Is there a way to achieve this in a single query or must I do some looping of result sets? I am agnostic on RDMS but something MySQL-like is good.

推荐答案

我会首先编写一个查询，该查询可以处理支付的单一份额.也就是说，每个 payment_id，所有金额的总和除以它需要支付的人数.然后可以将该结果连接回原始数据.

I would start by writing a query that works what a single share of a payment is. That is, per payment_id, the sum of all the amounts, divided by the number of people it needs to pay. That result can then be joined back to the original data.

SELECT
  payers_payments.payer_id,
  SUM(payers_payments.amount                      )   AS total_paid,
  SUM(payers_payments.pays * payments.single_share)   AS fair_share
FROM
  payers_payments
INNER JOIN
(
  SELECT
    payment_id,
    SUM(amount) / SUM(pays)   AS single_share
  FROM
    payers_payments
  GROUP BY
    payment_id
)
  AS payments
    ON  payers_payments.payment_id = payments.payment_id
GROUP BY
   payers_payments.payer_id

在 (payment_id) 和 (payer_id) 上都有索引会很有好处.

It will be of benefit to have indexes on both (payment_id) and (payer_id).

在 DECIMAL 数据类型中包含 amount 字段将是有益的，尽管您需要考虑要对舍入做什么.(一共支付10.00需要分三种方式，每种3.33，然后剩下的0.01你想怎么办?)

It will be of benefit to have the amount field in a DECIMAL data-type, though you need to consider what you want to do with rounding. (A total payment of 10.00 needs to be divided three ways, 3.33 each and then what do you want to happen to the spare 0.01?)

这篇关于单个SQL表上的聚合计算查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！