单个查询中多个日期范围的总和? [英] Sum for multiple date ranges in a single query?

问题描述

我有以下查询:

SELECT 
   SUM("balance_transactions"."fee") AS sum_id 
   FROM "balance_transactions" 
   JOIN charges ON balance_transactions.source = charges.balance_id 
   WHERE "balance_transactions"."account_id" = 6 
      AND (balance_transactions.type = 'charge' 
      AND charges.refunded = false 
      AND charges.invoice IS NOT NULL) 
      AND ("balance_transactions"."created" BETWEEN '2013-12-20' AND '2014-01-19');

这样做是将这两个日期之间发生的所有费用"相加.伟大的.工作正常.

What that does is adds up all the "fees" that occurred between those two dates. Great. Works fine.

问题是我几乎总是需要一次为数百个日期范围支付这些费用，这相当于我运行了数百次相同的查询.效率不高.

The problem is that I almost always need those fees for hundreds of date ranges at a time, which amounts to me running that same query hundreds of times. Not efficient.

但是有什么方法可以将其压缩为所有日期范围的单个查询吗?

But is there some way to condense this into a single query for all the date ranges?

例如，我会为一系列如下范围调用 SUM:

For instance, I'd be calling SUM for a series of ranges like this:

2013-12-20 to 2014-01-19
2013-12-21 to 2014-01-20
2013-12-22 to 2014-01-21
2013-12-23 to 2014-01-22
2013-12-24 to 2014-01-23
...so on and so on

我需要输出每个日期范围内收取的费用总和(最终需要在一个数组中).

I need to output the sum of fees collected in each date range (and ultimately need that in an array).

那么，您有什么想法可以处理这种情况并减少数据库事务吗?

So, any ideas on a way to handle that and reduce database transactions?

FWIW，这是在 Rails 应用程序中的 Postgres 上.

FWIW, this is on Postgres inside a Rails app.

推荐答案

假设我正确理解您的请求，我认为您需要的是以下方面的内容:

Assuming I understand your request correctly I think what you need is something along these lines:

SELECT "periods"."start_date", 
       "periods"."end_date", 
       SUM(CASE WHEN "balance_transactions"."created" BETWEEN "periods"."start_date" AND "periods"."end_date" THEN "balance_transactions"."fee" ELSE 0.00 END) AS period_sum
  FROM "balance_transactions" 
  JOIN charges ON balance_transactions.source = charges.balance_id 
  JOIN ( SELECT '2013-12-20'::date as start_date, '2014-01-19'::date as end_date UNION ALL
         SELECT '2013-12-21'::date as start_date, '2014-01-20'::date as end_date UNION ALL
         SELECT '2013-12-22'::date as start_date, '2014-01-21'::date as end_date UNION ALL
         SELECT '2013-12-23'::date as start_date, '2014-01-22'::date as end_date UNION ALL
         SELECT '2013-12-24'::date as start_date, '2014-01-23'::date as end_date
         ) as periods
    ON "balance_transactions"."created" BETWEEN "periods"."start_date" AND "periods"."end_date"
 WHERE "balance_transactions"."account_id" = 6 
   AND "balance_transactions"."type" = 'charge' 
   AND "charges"."refunded" = false 
   AND "charges"."invoice" IS NOT NULL
 GROUP BY "periods"."start_date", "periods"."end_date"

这应该会在一个结果集中返回您感兴趣的所有时间段.由于查询是在前端即时生成"的，因此您可以根据需要向句点部分添加任意数量的行.

This should return you all the periods you're interested in in one single resultset. Since the query is 'generated' on the fly in your front-end you can add as many rows to the periods part as you want.

通过一些试验和错误，我设法让它在 [sqlFiddle][1] 中工作并相应地更新了上面的语法.

with some trial and error I managed to get it working [in sqlFiddle][1] and updated the syntax above accordingly.

这篇关于单个查询中多个日期范围的总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！