我有以下查询:
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');
其作用是将这两个日期之间发生的所有“费用”相加。伟大的。工作正常。
问题是我几乎总是一次需要数百个日期范围的这些费用,这相当于我运行相同的查询数百次。效率不高。
但是有什么方法可以将其压缩为所有日期范围的单个查询吗?
例如,我会打电话SUM
对于这样的一系列范围:
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
我需要输出每个日期范围内收取的费用总和(最终需要将其放入数组中)。
那么,有什么办法可以处理这个问题并减少数据库事务吗?
FWIW,这是在 Rails 应用程序内的 Postgres 上。
假设我正确理解您的请求,我认为您需要的是以下内容:
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"
这应该会在一个结果集中返回您感兴趣的所有期间。
由于查询是在前端动态“生成”的,因此您可以根据需要向周期部分添加任意多的行。
编辑:经过一些试验和错误,我设法让它工作 [in sqlFiddle][1] 并相应地更新了上面的语法。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)