英文:
SQL recursive query to find datewise count
问题
以下是您要翻译的内容：
"我正在寻找一个SQL查询，根据已经存在的日期表，获取整年（按日期计）的well_count。
请查看下面的表格、代码以及我要查找的结果。
Table_1
Pad name Well_count Rig_release_date
---------------------------------------
Pad A 1 1/28/2023
Pad B 4 5/4/2023
Pad C 7 6/10/2023
Pad D 3 4/10/2023
我正在寻找的代码：
日期 Well Count
5/1/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/1/2023
5/2/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/2/2023
5/3/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/3/2023
5/4/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/4/2024
5/5/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/5/2024
5/6/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/6/2024
...
Result
日期 Well Count
-------------------
5/1/2023 4
5/2/2023 4
5/3/2023 4
5/4/2023 4
5/5/2023 8
5/6/2023 8
我不确定如何在SQL Server中实现这一点，我是否需要使用递归CTE或滑动窗口函数？
目前，我已经构建了以下代码来获取递归日期：
WITH cte_numbers AS
(
SELECT
@SD as Date_
UNION ALL
SELECT
DATEADD(day, 1, Date_)
FROM
cte_numbers
WHERE
Date_ < @ED
)
SELECT
*
FROM
cte_numbers
OPTION (maxrecursion 400);
英文:
I am looking for a SQL query to get well_count for the whole year (date wise) based on an already existing date table.
Please find below table, code and the result I am looking for.
Table_1
Pad name Well_count Rig_release_date
------------------------------------
Pad A 1 1/28/2023
Pad B 4 5/4/2023
Pad C 7 6/10/2023
Pad D 3 4/10/2023
Code I am looking for
Date Well Count
5/1/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/1/2023
5/2/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/2/2023
5/3/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/3/2023
5/4/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/4/2024
5/5/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/5/2024
5/6/2023 Select sum(Well_count) from Table_1 where Rig_release_date < 5/6/2024
.
.
Result
Date Well Count
-------------------
5/1/2023 4
5/2/2023 4
5/3/2023 4
5/4/2023 4
5/5/2023 8
5/6/2023 8
I am not sure how to do this in SQL Server, do I need to use a recursive CTE or sliding window function?
As of now I have built this code to get the recursive dates
WITH cte_numbers AS
(
SELECT
@SD as Date_
--@SD as n , (sum(Total_wells) from duc_schedule where Rig_release_date < n)
UNION ALL
SELECT
DATEADD(day, 1, Date_)
FROM
cte_numbers
WHERE
Date_ < @ED
)
SELECT
*
FROM
cte_numbers
OPTION (maxrecursion 400);
答案1
得分: 1
以下是您要翻译的内容：
对于这个问题，您很可能需要使用“日历表”进行连接，这样的表可以使用递归公用表表达式（CTE）轻松创建：
;with consecutiveDays as (
select cast('2023-05-01' as date) [day]
union all
select dateadd(d, 1, [day]) from consecutiveDays
where [day] < cast('2023-06-30' as date)
)
然后，假设您的数据如下所示：
declare @tbl table (
PadName nvarchar(100),
WellCount int,
RigReleaseDate date
)
insert into @tbl values
('Pad A', 1, '2023-01-28'),
('Pad B', 4, '2023-05-04'),
('Pad C', 7, '2023-06-10'),
('Pad D', 3, '2023-04-10')
您可以编写以下查询以获得所需结果：
select
[day],
(select sum(WellCount) from @tbl
where RigReleaseDate < [day]) WellCount
from consecutiveDays
英文:
For that most probably you would need "calendar table" to join to, such table is easy to create with recursive CTE:
;with consecutiveDays as (
select cast('2023-05-01' as date) [day]
union all
select dateadd(d, 1, [day]) from consecutiveDays
where [day] < cast('2023-06-30' as date)
)
Then, having your data represented as below:
declare @tbl table (
PadName nvarchar(100),
WellCount int,
RigReleaseDate date
)
insert into @tbl values
('Pad A', 1, '2023-01-28'),
('Pad B', 4, '2023-05-04'),
('Pad C', 7, '2023-06-10'),
('Pad D', 3, '2023-04-10')
you can write such query to get desired results:
select
[day],
(select sum(WellCount) from @tbl
where RigReleaseDate < [day]) WellCount
from consecutiveDays
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论