一个查询中多个日期范围的和？
一个查询中多个日期范围的和？
提问于 2014-01-29 04:16:23
我有以下查询：
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，这是在Postgres的一个Rails应用程序。
回答 10
Stack Overflow用户
回答已采纳
发布于 2014-01-30 09:38:18
假设我正确理解了您的请求，我认为您需要的是以下内容：
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"这将返回在一个结果集中感兴趣的所有句点。由于查询是在前端动态生成的，所以您可以根据需要向句点部分添加多少行。
编辑:经过一些尝试和错误，我设法使它在sqlFiddle中工作，并相应地更新了上面的语法。
Stack Overflow用户
发布于 2014-01-29 04:22:30
好的，来自Server背景，我将您的where子句更改为
...
AND (
"balance_transactions"."created" BETWEEN '2013-12-20' AND '2014-01-19'
OR
"balance_transactions"."created" BETWEEN '2013-12-21' AND '2014-01-20'
OR
"balance_transactions"."created" BETWEEN '2013-12-23' AND '2014-01-22'
OR
"balance_transactions"."created" BETWEEN '2013-12-24' AND '2014-01-23'
);只是要确保你有一个很好的日期索引！:)
Stack Overflow用户
发布于 2014-01-29 04:42:52
这是一个未经测试的过程，你可以使用。
CREATE OR REPLACE PROCEDURE sum_fees(v_start IN Date, v_end in Date) IS
BEGIN
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 v_start AND v_end);
END;然后打电话给你的范围日期的程序。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21421973
复制相关文章
相似问题
腾讯云开发者
