英文:
How to calculate the date when the sum of entries exceeded the threshold?
问题
我想知道何时营业额达到30，以前面的情况为例，它在第2天发生。如何在SQL中查询此信息？我猜我必须使用窗口函数，但不确定是哪个。
英文:
Let's assume there is a table:
| day | amount |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
I want to know when turnover reached 30, in case above it's happened on 2 day. How to query this in SQL? I guess I have to use window function but dont' know which one exactly
答案1
得分: 2
你可以获得累积总和，然后使用LIMIT 1进行筛选和限制记录，或者你可以在一天中使用MIN：
WITH cumulative_sum_table AS (
SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total
FROM your_table
)
SELECT day
FROM cumulative_sum_table
WHERE running_total >= 30
ORDER BY day
LIMIT 1;
英文:
You could get running Total and then put filter and limit the record with LIMIT 1 or you could use MIN for the day:
WITH cumulative_sum_table AS (
SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total
FROM your_table
)
SELECT day
FROM cumulative_sum_table
WHERE running_total >= 30
ORDER BY day
LIMIT 1;
答案2
得分: 1
你可以通过聚合来实现它。
英文:
You can achieve it via aggregation
select yt.day
from yourtable yt
join yourtable y2
on yt2.day <= yt
group by day
having SUM(yt2.amount) >= 30
limit 0, 1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论