运行总计列的复杂 SQL 查询 [英] Complicated SQL query for a running total column

问题描述

我正在尝试在 SQL Server 2008 中计算出一个非常复杂的查询.我想在这里提供一些 SQL 专家的意见.

I'm trying to work out a pretty complex query in SQL Server 2008. I'd like some input from SQL experts here.

假设我有一个包含这些字段的 Payments 表:

Imagine I had a Payments table with these fields:

PaymentID int,客户 ID 整数，付款日期日期时间，金额小数

PaymentID int, CustomerID int, PaymentDate datetime, Amount decimal

因此，本质上，它是客户在特定日期进行的付款表.需要注意的重要一点是，在某些情况下，付款金额可能是负值.因此，随着时间的推移，任何给定客户支付的总金额可能会上升或下降.

So essentially, it is a table of payments made by a customer on specific dates. An important thing to note is that in some cases, a payment amount can be a negative value. So, over time, the total amount paid by any given customer, can go up or down.

我们试图找出的是计算每位客户支付总额最高点的 SQL.

What we're trying to figure out is the SQL to calculate the high point of the total amount paid per customer.

因此，如果 Fred 支付了 3 笔款项:第一笔支付 5 美元，第二笔支付 5 美元，第三笔支付 - 3 美元.该报告将显示 Fred 的最高总支付金额为 10 美元(第二次支付时)，他的最终支付金额为 7 美元.

So, if Fred made 3 payments: first for $5, second for $5, third for -$3. The report will show that Fred's peak total paid amount was $10 (on his second payment), and his final paid amount was $7.

我们需要为 10 万客户(每个客户可能支付了一百到一千笔款项)运行此报告，所以它必须很快.

We need to run this report for a hundred thousand customers (who've potentially made a hundred to a thousand payments each), so it's got to be fast.

有没有一种好方法可以在不将运行总数存储在数据库中的情况下构建此查询?如果可能，我们希望避免存储预先计算的值.

Is there a good way to structure this query without storing the running totals in the db? We'd like to avoid storing precalculated values if at all possible.

推荐答案

你的问题好像是这样的:

Your question seems to be this:

SELECT CustomerID, SUM(Ammount) FROM table WHERE Amount > 0 GROUP BY CustomerID
SELECT CustomerID, SUM(Ammount) FROM table GROUP BY CustomerID

但是，我认为您的意思是您想要一个看起来像这样的表格

However, I think you mean that you want a table that appears like this

Customer  Payment  HighPoint  RunningTotal
123       5        5          5
123       5        10         10
123       -3       10         7

在这种情况下，我将使用上面的两个选择创建一个视图，以便该视图类似于.

In which case I would create a view with the two selects above so that the view is something like.

SELECT CusotmerID, 
  PaymentDate, 
  Ammount, 
  (SELECT SUM(Ammount) 
    FROM table as ALIAS 
    WHERE ALIAS.Amount > 0 
      AND ALIAS.PaymentDate <= PaymentDate 
      AND ALIAS.CustomerID = CustomerID), 
  (SELECT SUM(Ammount) 
    FROM table as ALIAS 
    WHERE ALIAS.CustomerID = CustomerID 
    AND ALIAS.PaymentDate <= PaymentDate)
FROM table

此外，您可以考虑在表的 Amount 列上使用非唯一索引以加快查看速度.

Also, you may consider a non-unique index on the Amount column of the table to speed up the view.

这篇关于运行总计列的复杂 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！