一次通话中多个日期范围的总和? [英] Sum for multiple date ranges in a single call?
问题描述
我有以下查询:
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屋!