英文:
Oracle SQL select sum from last 30 days showing actual day
问题
听起来像是一个奇怪的查询，但概念非常简单，只是我无法弄清如何实现。
我的实际表格类似于这个：
| 年份 | 月份 | 天 | 日期 | 值 |
| -----| ------|-----|------|-------|
| 2023| 2| 22| 2023/02/22| 50|
| 2023| 2| 21| 2023/02/21| 150|
我想创建一个视图，其中每天显示过去30天的值。
我使用“Over Order by rows between”实现了类似的功能，但现在我们需要显示确切的最近30天，而不是我们最后的30行。
select
t3.year_,
t3.month_,
t3.day_,
sum(t3.V1) over (order by to_date(t3.year_||lpad(t3.month_,2,'0')||lpad(t3.day_,2,'0'),'yyyymmdd') rows between 29 preceding and current row) as VALUE_LAST_30,
from (select t2.year_,t2.month_,t2.day_,
sum(value_) as V1
from table1 t2
group by t2.year_,t2.month_,t2.day_) t3
谢谢。
英文:
It sounds like a weird query, but the concept is pretty simple, just that I can't figure out how to achieve it.
My actual table is similar to this one:
| YEAR | MONTH | DAY | DATE | VALUE |
|---|---|---|---|---|
| 2023 | 2 | 22 | 2023/02/22 | 50 |
| 2023 | 2 | 21 | 2023/02/21 | 150 |
And I would like to create a view where each day shows the value of last 30 days.
I achieved something similar using "Over Order by rows between", but now we need to show exactly 30 last, not our last 30 rows.
select
t3.year_,
t3.month_,
t3.day_,
sum(t3.V1) over (order by to_date(t3.year_||lpad(t3.month_,2,'0')||lpad(t3.day_,2,'0'),'yyyymmdd') rows between 29 preceding and current row) as VALUE_LAST_30,
from (select t2.year_,t2.month_,t2.day_
sum(value_) as V1
from table1 t2
group by t2.year_,t2.month_,t2.day_) t3
Thanks in advance.
答案1
得分: 1
如果您希望获取过去30天的sum(value)，那么我会这样说：
select t.x_date, t.value
,(select sum(t2.value)
from table_1 t2
where t2.x_date between t.x_date-30 and t.x_date) value_30_days
from table_1 t;
英文:
In case you wish to get the sum(value) for the last 30 days, then I'd say something like below
select t.x_date, t.value
,(select sum(t2.value)
from table_1 t2
where t2.x_date between t.x_date-30 and t.x_date) value_30_days
from table_1 t;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论