SQL 在 Oracle 中计算条件累积总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14987534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating a conditional cumulative sum in Oracle
提问by the Internet
The broad view of what I'm trying to do is find out how many reservations that have not yet happened yet are on the books in the entire system, by the date that the reservation was booked. This means counting the number of all records that exist with a redemption_dateafter or equal to booking_date, grouping by booking_date. Please see the following hypothetical example for a better explanation:
我正在尝试做的事情的广泛观点是，在预订被预订的日期之前，找出整个系统中的账簿上还有多少尚未发生的预订。这意味着计算存在于redemption_date之后或等于的所有记录的数量booking_date，分组为booking_date。请参阅以下假设示例以获得更好的解释：
redemption_date booking_date
2013-01-01 2013-01-01
2013-01-06 2013-01-01
2013-01-06 2013-01-01
2013-01-07 2013-01-02
2013-01-08 2013-01-03
2013-01-09 2013-01-04
2013-01-10 2013-01-05
2013-01-10 2013-01-05
2013-01-10 2013-01-05
2013-01-10 2013-01-05
2013-01-10 2013-01-05
2013-01-11 2013-01-05
I would like the result:
我想要结果：
booking_date number_of_reservations
2013-01-01 3
2013-01-02 3
2013-01-03 4
2013-01-04 5
2013-01-05 11
But my brain is completely failing me as to how the query should be structured. Any tips? Thanks!
但是我的大脑完全不知道应该如何构建查询。有小费吗？谢谢！
Edit: To clarify, number_of_reservations should be the number of reservations that were booked on that date, as well as those booked on days AFTER that. In other words, number_of_reservations is the number of reservations that are in the database as of booking_date (that have not yet happened). My original results did have mistakes. Sorry for the confusion
编辑：澄清一下，number_of_reservations 应该是当天预订的预订数量，以及之后几天预订的预订数量。换句话说，number_of_reservations 是截至预订日期（尚未发生）在数据库中的预订数量。我原来的结果确实有错误。对困惑感到抱歉
回答by Michael Fredrickson
SELECT
booking_date,
COUNT(
CASE WHEN redemption_date >= booking_date
THEN 1 END
) AS number_of_reservations
FROM
Reservations
GROUP BY
booking_date
Sql Fiddle
Sql小提琴
Edit:
编辑：
Based on the updated description, I believe this should give the desired results:
根据更新的描述，我相信这应该会给出预期的结果：
SELECT DISTINCT
r."booking_date",
(SELECT COUNT(*)
FROM reservations r2
WHERE
r2."booking_date" <= r."booking_date"
AND r2."redemption_date" >= r."booking_date"
) AS number_of_reservations
FROM
Reservations r
ORDER BY r."booking_date"
Sql Fiddle
Sql小提琴
回答by Gordon Linoff
You can do this readily by using casewith a sum.
您可以通过使用容易做到这一点case了sum。
select booking_date,
SUM(case when redemption_date >= booking_date then 1 else 0 end)
from bookings b
group by booking_date
order by booking_date
By the way, this isn't really a cumulative sum. The would have the syntax:
顺便说一下，这并不是真正的累积总和。将具有以下语法：
sum(whatever) over (partition by . . . order by . . . )
It would put a calculated value on every row.
它会在每一行上放置一个计算值。
回答by Matt Busche
this should get you what you're looking for
这应该让你得到你正在寻找的东西
SELECT booking_date, SUM(CASE WHEN booking_date >= redemption_date THEN 1 ELSE 0 END) AS number_of_reservations
FROM yourtable
GROUP BY booking_date
ORDER BY booking_date
回答by Joe
try this (I am assuming that you want all of them, not ones that appear more than a certain number)
试试这个（我假设你想要所有这些，而不是出现超过一定数量的那些）
select booking_date, count(*) as number_of_reservations
from table_name
group by booking_date order by booking_date
回答by Ram
Here is a simple solution.
这是一个简单的解决方案。
select a.trans_id,
a.quantity+nvl((select sum(quantity) from table1 where trans_id<a.trans_id),0) quantity
from table1 a
order by a.sys_trans_id
