TSQL에서 증가하는 날짜의 결과 집합 생성
날짜 결과 집합을 만들어야하는 필요성을 고려하십시오. 시작일과 종료일이 있으며 그 사이의 날짜 목록을 생성하려고합니다.
DECLARE @Start datetime
,@End datetime
DECLARE @AllDates table
(@Date datetime)
SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'
--need to fill @AllDates. Trying to avoid looping.
-- Surely if a better solution exists.
WHILE루프가 있는 현재 구현을 고려하십시오 .
DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
INSERT INTO @AllDates VALUES (@dCounter)
SELECT @dCounter=@dCounter+1
END
질문 : T-SQL을 사용하여 사용자 정의 범위 내에있는 날짜 집합을 어떻게 만들 수 있습니까? SQL 2005 이상을 가정합니다. 귀하의 답변이 SQL 2008 기능을 사용하는 경우, 그렇게 표시하십시오.
날짜가 2047 일 이내 인 경우 :
declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)
select dateadd(day, number, @dt)
from
(select number from master.dbo.spt_values
where [type] = 'P'
) n
where dateadd(day, number, @dt) < @dtEnd
여러 번 요청한 후 답변을 업데이트했습니다. 왜?
원래 답변에는 하위 쿼리가 포함되었습니다.
select distinct number from master.dbo.spt_values
where name is null
SQL Server 2008, 2012 및 2016에서 테스트 한 것과 동일한 결과를 제공합니다.
그러나에서 쿼리 할 때 내부적으로 MSSQL이 사용하는 코드를 분석하려고했을 때 문에 항상 절이 포함되어 spt_values있음을 발견했습니다 .SELECTWHERE [type]='[magic code]'
따라서 쿼리가 올바른 결과를 반환하더라도 잘못된 이유로 올바른 결과를 제공한다고 결정했습니다.
다른 정의 SQL Server의 이후 버전이있을 수 있습니다 [type]또한이 값을 NULL값으로 [name]결과가 단순히 잘못 될 경우에, 0-2047의 외부 범위, 또는 비 연속.
다음은 재귀 적 CTE (SQL Server 2005+)를 사용합니다.
WITH dates AS (
SELECT CAST('2009-01-01' AS DATETIME) 'date'
UNION ALL
SELECT DATEADD(dd, 1, t.date)
FROM dates t
WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')
SELECT ...
FROM TABLE t
JOIN dates d ON d.date = t.date --etc.
이 방법이 작동하려면 다음 일회성 테이블 설정을 수행해야합니다.
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
Numbers 테이블이 설정되면 다음 쿼리를 사용합니다.
SELECT
@Start+Number-1
FROM Numbers
WHERE Number<=DATEDIFF(day,@Start,@End)+1
캡처하려면 다음을 수행하십시오.
DECLARE @Start datetime
,@End datetime
DECLARE @AllDates table
(Date datetime)
SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'
INSERT INTO @AllDates
(Date)
SELECT
@Start+Number-1
FROM Numbers
WHERE Number<=DATEDIFF(day,@Start,@End)+1
SELECT * FROM @AllDates
산출:
Date
-----------------------
2009-03-01 00:00:00.000
2009-03-02 00:00:00.000
2009-03-03 00:00:00.000
2009-03-04 00:00:00.000
2009-03-05 00:00:00.000
2009-03-06 00:00:00.000
2009-03-07 00:00:00.000
2009-03-08 00:00:00.000
2009-03-09 00:00:00.000
2009-03-10 00:00:00.000
....
2009-07-25 00:00:00.000
2009-07-26 00:00:00.000
2009-07-27 00:00:00.000
2009-07-28 00:00:00.000
2009-07-29 00:00:00.000
2009-07-30 00:00:00.000
2009-07-31 00:00:00.000
2009-08-01 00:00:00.000
(154 row(s) affected)
@KM의 대답은 먼저 숫자 표를 만들고 날짜 범위를 선택하는 데 사용합니다. 임시 번호 테이블없이 동일한 작업을 수행하려면 :
DECLARE @Start datetime
,@End datetime
DECLARE @AllDates table
(Date datetime)
SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009';
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT @Start+n-1 as Date
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= DATEDIFF(day,@Start,@End)+1 ;
물론 테스트를 자주한다면 영구 테이블이 더 성능이 좋을 수도 있습니다.
위의 쿼리는 시퀀스 생성에 대해 설명하고 가능한 많은 방법을 제공하는 이 기사 의 수정 된 버전입니다 . 나는 임시 테이블을 생성하지 않고 테이블의 요소 수에 제한되지 않기 때문에 이것을 좋아했습니다 sys.objects.
이 시도. 루핑, CTE 제한 등이 없으며 거의 불가능할 수 있습니다. 생성 된 레코드 수. 필요한 사항에 따라 교차 조인 및 상단을 관리합니다.
select top 100000 dateadd(d,incr,'2010-04-01') as dt from
(select incr = row_number() over (order by object_id, column_id), * from
(
select a.object_id, a.column_id from sys.all_columns a cross join sys.all_columns b
) as a
) as b
중첩은보다 쉬운 제어 및 뷰로의 변환 등을위한 것입니다.
이 솔루션은 MySQL에 대한 동일한 질문에 대한 놀라운 답변을 기반으로합니다. 또한 MSSQL에서도 매우 성능이 뛰어납니다. https://stackoverflow.com/a/2157776/466677
select DateGenerator.DateValue from (
select DATEADD(day, - (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)), CONVERT(DATE, GETDATE()) ) as DateValue
from (select a.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a
cross join (select b.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b
cross join (select c.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c
cross join (select d.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as d(a)) as d
) DateGenerator
WHERE DateGenerator.DateValue BETWEEN 'Mar 1 2009' AND 'Aug 1 2009'
ORDER BY DateGenerator.DateValue ASC
DATEADD 함수의 기호 빼기 미래 변경 날짜의 경우 과거 날짜에만 작동합니다. 쿼리는 SQL Server 2008+에서만 작동하지만 2005에서도 "select from values"구문을 공용체로 바꾸면 다시 작성할 수 있습니다.
또 다른 옵션은 .NET에서 해당 함수를 만드는 것입니다. 다음과 같이 보입니다.
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.None,
FillRowMethodName = "fnUtlGetDateRangeInTable_FillRow",
IsDeterministic = true,
IsPrecise = true,
SystemDataAccess = SystemDataAccessKind.None,
TableDefinition = "d datetime")]
public static IEnumerable fnUtlGetDateRangeInTable(SqlDateTime startDate, SqlDateTime endDate)
{
// Check if arguments are valid
int numdays = Math.Min(endDate.Value.Subtract(startDate.Value).Days,366);
List<DateTime> res = new List<DateTime>();
for (int i = 0; i <= numdays; i++)
res.Add(dtStart.Value.AddDays(i));
return res;
}
public static void fnUtlGetDateRangeInTable_FillRow(Object row, out SqlDateTime d)
{
d = (DateTime)row;
}
이것은 기본적으로 프로토 타입이며 훨씬 더 똑똑하게 만들 수 있지만 아이디어를 보여줍니다. 내 경험상 중소 기간 (예 : 2 년) 동안이 함수는 T-SQL에서 구현 된 것보다 더 잘 수행됩니다. CLR 버전의 또 다른 좋은 기능은 임시 테이블을 생성하지 않는다는 것입니다.
개요
여기 내 버전 (2005 호환)이 있습니다. 이 접근 방식의 장점은 다음과 같습니다.
- 여러 유사한 시나리오에 사용할 수있는 범용 기능을 얻게됩니다. 날짜에만 국한되지 않음
- 범위는 기존 테이블의 내용에 의해 제한되지 않습니다.
- 증분을 쉽게 변경할 수 있습니다 (예 : 매일이 아닌 7 일마다 날짜 가져 오기).
- 다른 카탈로그 (예 : 마스터)에 액세스 할 필요가 없습니다.
- SQL 엔진은 while 문으로는 할 수 없었던 TVF의 최적화를 수행 할 수 있습니다.
- generate_series는 다른 db에서 사용되므로 코드를 더 많은 청중에게 본능적으로 친숙하게 만드는 데 도움이 될 수 있습니다.
SQL Fiddle : http://sqlfiddle.com/#!6/c3896/1
암호
주어진 매개 변수를 기반으로 숫자 범위를 생성하는 재사용 가능한 함수 :
create function dbo.generate_series
(
@start bigint
, @stop bigint
, @step bigint = 1
, @maxResults bigint = 0 --0=unlimitted
)
returns @results table(n bigint)
as
begin
--avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
if @step = 0 return
if @start > @stop and @step > 0 return
if @start < @stop and @step < 0 return
--ensure we don't overshoot
set @stop = @stop - @step
--treat negatives as unlimited
set @maxResults = case when @maxResults < 0 then 0 else @maxResults end
--generate output
;with myCTE (n,i) as
(
--start at the beginning
select @start
, 1
union all
--increment in steps
select n + @step
, i + 1
from myCTE
--ensure we've not overshot (accounting for direction of step)
where (@maxResults=0 or i<@maxResults)
and
(
(@step > 0 and n <= @stop)
or (@step < 0 and n >= @stop)
)
)
insert @results
select n
from myCTE
option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this
--all good
return
end
이를 시나리오에 사용 :
declare @start datetime = '2013-12-05 09:00'
,@end datetime = '2014-03-02 13:00'
--get dates (midnight)
--, rounding <12:00 down to 00:00 same day, >=12:00 to 00:00 next day
--, incrementing by 1 day
select CAST(n as datetime)
from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default)
--get dates (start time)
--, incrementing by 1 day
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, 24, default)
--get dates (start time)
--, incrementing by 1 hour
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, default, default)
2005 호환
- 공통 테이블 표현식 : http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
- 옵션 MaxRecursion 힌트 : http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
- 테이블 값 함수 : http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
- 기본 매개 변수 : http://technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
- DateTime : http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
- 캐스팅 : http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx
읽기 및 유지 관리가 쉽기 때문에 CTE를 좋아합니다.
Declare @mod_date_from date =getdate();
Declare @mod_date_to date =dateadd(year,1,@mod_date_from);
with cte_Dates as (
SELECT @mod_date_from as reqDate
UNION ALL
SELECT DATEADD(DAY,1,reqDate)
FROM cte_Dates
WHERE DATEADD(DAY,1,reqDate) < @mod_date_to
)
SELECT * FROM cte_Dates
OPTION(MAXRECURSION 0);
MAXRECURSION을 설정하는 것을 잊지 마십시오
0에서 두 날짜의 차이까지의 정수로 임시 테이블을 만듭니다.
SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;
다음을 사용합니다.
SELECT * FROM dbo.RangeDate(GETDATE(), DATEADD(d, 365, GETDATE()));
-- Generate a range of up to 65,536 contiguous DATES
CREATE FUNCTION dbo.RangeDate (
@date1 DATE = NULL
, @date2 DATE = NULL
)
RETURNS TABLE
AS
RETURN (
SELECT D = DATEADD(d, A.N, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
FROM dbo.RangeSmallInt(0, ABS(DATEDIFF(d, @date1, @date2))) A
);
-- Generate a range of up to 65,536 contiguous BIGINTS
CREATE FUNCTION dbo.RangeSmallInt (
@num1 BIGINT = NULL
, @num2 BIGINT = NULL
)
RETURNS TABLE
AS
RETURN (
WITH Numbers(N) AS (
SELECT N FROM(VALUES
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
) V (N)
)
SELECT TOP (
CASE
WHEN @num1 IS NOT NULL AND @num2 IS NOT NULL THEN ABS(@num1 - @num2) + 1
ELSE 0
END
)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
FROM Numbers A
, Numbers B
WHERE ABS(@num1 - @num2) + 1 < 65537
);
It isn't all that different from many of the solutions proposed already but there are several things I like about it:
- No tables required
- Arguments can be passed in any order
- Limit of 65,536 dates is arbitrary and can easily be expanded by swapping to a function such as RangeInt
This one should work.
select Top 1000 DATEADD(d, ROW_NUMBER() OVER(ORDER BY Id),getdate()) from sysobjects
What I'd recommend: create an auxiliary table of numbers and use it to generate your list of dates. You can also use a recursive CTE, but that may not perform as well as joining to an auxiliary table of numbers. See SQL, Auxiliary table of numbers for info on both options.
While I really like KM's solution above (+1), I must question your "no loop" assumption - given the plausible date ranges that your app will work with, having a loop should not really be all that expensive. The main trick is to strore the results of the loop in staging/cache table, so that extremely large sets of queries do not slow down the system by re-calculating the same exact dates. E.g. each query only computes/caches the date ranges that are NOT already in cache and that it needs (and pre-populate the table with some realistic date range like ~2 years in advance, with range determined by your application business needs).
The best answer is probably to use the CTE, but there is no guarantee you are able to use that. In my case, I had to insert this list inside an existing query created dinamically by a query generator...couldn't use CTE nor stored procedures.
So, the answer from Devio was really useful, but I had to modify it to work in my environment.
In case you don't have access to the master db, you may use another table in your database. As for the example before, the maximum date range is given by the number of rows inside the table choosen.
In my example tough, using the row_number, you can use tables without an actual int column.
declare @bd datetime --begin date
declare @ed datetime --end date
set @bd = GETDATE()-50
set @ed = GETDATE()+5
select
DATEADD(dd, 0, DATEDIFF(dd, 0, Data)) --date format without time
from
(
select
(GETDATE()- DATEDIFF(dd,@bd,GETDATE())) --Filter on the begin date
-1 + ROW_NUMBER() over (ORDER BY [here_a_field]) AS Data
from [Table_With_Lot_Of_Rows]
) a
where Data < (@ed + 1) --filter on the end date
Really like Devio's solution as I needed exactly something like this that needs to run on SQL Server 2000 (so cannot use CTE) however, how could it be modified to ONLY generate dates that align with a given set of days of the week. For example, I only want the dates that fall in line with Monday, Wednesday and Friday or whatever particular sequence I choose based on the following number Scheme:
Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7
Example:
StartDate = '2015-04-22' EndDate = '2017-04-22' --2 years worth
Filter on: 2,4,6 --Monday, Wednesday, Friday dates only
What I'm trying to code is to add two additional fields: day,day_code Then filter the generated list with a condition...
I came up with the following:
declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 1095, @dt)
select dateadd(day, number, @dt) as Date, DATENAME(DW, dateadd(day, number, @dt)) as Day_Name into #generated_dates
from
(select distinct number from master.dbo.spt_values
where name is null
) n
where dateadd(day, number, @dt) < @dtEnd
select * from #generated_dates where Day_Name in ('Saturday', 'Friday')
drop table #generated_dates
ReferenceURL : https://stackoverflow.com/questions/1478951/generate-a-resultset-of-incrementing-dates-in-tsql
'programing' 카테고리의 다른 글
| 사용자가 양식의 크기를 조정하지 못하도록하려면 어떻게합니까? (0) | 2021.01.18 |
|---|---|
| jQuery UI 슬라이더 이벤트 트리거 (0) | 2021.01.18 |
| WPF의 활성 창을 참조하십시오. (0) | 2021.01.18 |
| VBScript에서 문자열에 따옴표 추가 (0) | 2021.01.18 |
| node.js-코드 보호? (0) | 2021.01.18 |