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나는 그것을 발견했습니다.SELECT문은 항상 절을 포함합니다.WHERE [type]='[magic code]'.
따라서 쿼리가 올바른 결과를 반환하지만 잘못된 이유로 올바른 결과를 전달한다고 판단했습니다.
SQL Server를 수 .[type]▁has▁가▁also도 포함하는 값NULL의 [name], 비연속적인 수 .0-2047 , 적도수결있다단잘니습우못순될과히가에속연어contig위▁outside0-다있니수습▁would심-잘못될순47히단▁non▁result▁be결,▁the▁wrong▁simply.
다음은 재귀 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.
@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
이 솔루션은 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 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)
이거 먹어봐요.Looping, 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
중첩은 보다 쉽게 제어하고 보기로 변환하기 위한 것입니다.
다른 옵션은 .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;
}
이것은 기본적으로 프로토타입이고 훨씬 더 똑똑하게 만들 수 있지만, 그 아이디어를 설명합니다.제 경험에 따르면, 이 기능은 T-SQL에서 구현된 기능보다 (몇 년 정도) 작거나 중간 정도의 기간 동안 더 나은 성능을 발휘합니다.CLR 버전의 또 다른 좋은 기능은 임시 테이블을 만들지 않는다는 것입니다.
개요
여기 제 버전이 있습니다(2005 호환).이 접근 방식의 이점은 다음과 같습니다.
- 날짜에 국한되지 않고 여러 유사한 시나리오에 사용할 수 있는 범용 기능을 얻을 수 있습니다.
- 범위는 기존 테이블의 내용에 의해 제한되지 않습니다.
- 증분을 쉽게 변경할 수 있습니다(예: 매일이 아닌 7일마다 날짜 가져오기).
- 다른 카탈로그(즉, 마스터)에 액세스할 필요가 없습니다.
- SQL 엔진은 한동안 설명할 수 없었던 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
- 옵션 최대 재귀 힌트: 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
- 날짜: http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
- 캐스팅: http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx
다음을 사용합니다.
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
);
이미 제안된 많은 솔루션과 크게 다르지 않지만 다음과 같은 몇 가지가 마음에 듭니다.
- 테이블이 필요하지 않습니다.
- 인수는 임의의 순서로 전달할 수 있습니다.
- 65,536일의 제한은 임의이며 Range와 같은 기능으로 스왑하여 쉽게 확장할 수 있습니다.인트
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);
MAXRECURCSURESS를 설정하는 것을 잊지 마십시오.
0부터 두 날짜 사이의 차이까지 정수를 사용하여 임시 표를 만듭니다.
SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;
이거면 될 것 같아요.
sysobject에서 상위 1000 DATE ADD(d, ROW_NUMBER() OVER(ID로 주문), getdate()를 선택합니다.
제가 추천하는 것은 보조 숫자 표를 만들어 날짜 목록을 생성하는 데 사용하는 것입니다.재귀 CTE를 사용할 수도 있지만 보조 숫자 표에 결합하는 것만큼 잘 수행되지 않을 수도 있습니다.두 옵션에 대한 자세한 내용은 SQL, 보조 숫자 표를 참조하십시오.
위 (+1)의 KM 솔루션을 정말 좋아하지만, 당신의 "루프 없음" 가정에 의문을 제기해야 합니다. 당신의 앱이 작동할 그럴듯한 날짜 범위를 고려할 때 루프를 갖는 것이 실제로 그렇게 비싸지 않아야 합니다.주요 방법은 매우 큰 쿼리 집합이 동일한 정확한 날짜를 다시 계산하여 시스템 속도를 늦추지 않도록 준비/캐시 테이블에 루프 결과를 저장하는 것입니다.예를 들어, 각 쿼리는 아직 캐시에 저장되어 있지 않고 필요한 날짜 범위만 계산/캐시합니다(2년 전과 같은 현실적인 날짜 범위로 테이블을 미리 채우며 애플리케이션 비즈니스 요구에 따라 범위를 결정합니다).
가장 좋은 대답은 CTE를 사용하는 것이지만, CTE를 사용할 수 있다는 보장은 없습니다.제 경우에는 쿼리 생성기에서 동적으로 생성한 기존 쿼리에 이 목록을 삽입해야 했습니다. CTE나 저장 프로시저를 사용할 수 없었습니다.
데비오의 답변은 정말 유용했지만, 제 환경에서 작동하도록 수정해야 했습니다.
마스터 데이터베이스에 대한 접근 권한이 없는 경우, 데이터베이스의 다른 테이블을 사용할 수 있습니다.앞의 예와 같이, 최대 날짜 범위는 선택한 테이블 내의 행 수에 따라 지정됩니다.
내 예에서는 row_number를 사용하여 실제 int 열 없이 테이블을 사용할 수 있습니다.
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
SQL Server 2000에서 실행해야 하는 데 필요한 Devio의 솔루션과 마찬가지로(따라서 CTE를 사용할 수 없음), 어떻게 특정 요일에 맞는 날짜만 생성하도록 수정할 수 있습니까?예를 들어, 월요일, 수요일, 금요일과 일치하는 날짜 또는 다음 숫자 체계를 기준으로 선택한 특정 시퀀스만 원합니다.
Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7
예:
StartDate = '2015-04-22' EndDate = '2017-04-22' --2 years worth
Filter on: 2,4,6 --Monday, Wednesday, Friday dates only
제가 코딩하려고 하는 것은 day, day_code 두 개의 필드를 추가하는 것입니다. 그런 다음 조건을 사용하여 생성된 목록을 필터링합니다.
저는 다음을 생각해냈습니다.
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
최대 10,000일(27년)의 날짜 목록이 생성됩니다.
declare @startDateTime datetime = '2000-06-02 00:00:00';
declare @endDateTime datetime = '2028-06-02 23:59:59';
SELECT DATEADD(DAY, (Thousands+Hundreds+Tens+Units) , @startDateTime) D
FROM (
SELECT 0 Thousands
UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000
UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000
UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
) Thousands
CROSS JOIN (
SELECT 0 Hundreds
UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
) Hundreds
CROSS JOIN (
SELECT 0 Tens
UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30
UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60
UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) Tens
CROSS JOIN (
SELECT 0 Units
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) Units
WHERE
DATEADD(DAY, (Thousands+Hundreds+Tens+Units), @startDateTime) <= @endDateTime
ORDER BY (Thousands+Hundreds+Tens+Units)
언급URL : https://stackoverflow.com/questions/1478951/generate-a-resultset-of-incrementing-dates-in-tsql
'programing' 카테고리의 다른 글
| NextJS Myslog INSERT는 phpmyadmin을 통한 명령에서는 작동하지만 api의 쿼리에서는 작동하지 않습니다. (0) | 2023.06.21 |
|---|---|
| 병합 사용 중...source.id 과 target.id 사이의 매핑을 가져오는 출력 (0) | 2023.06.21 |
| '#selector'는 Objective-C에 노출되지 않는 방법을 말합니다. (0) | 2023.06.21 |
| 파이썬 함수 호출에서 stdout 출력을 캡처하는 방법은 무엇입니까? (0) | 2023.06.21 |
| 영업일(예: 주말 무시)만 고려하여 날짜에 일 수를 추가하는 방법은 무엇입니까? (0) | 2023.06.21 |