[MSSQL] Spaß mit rekursiven CTE's im SQL Server

[Thomas Darimont]

Hallo,

hier mal ein kleines Beispiel wie man mit Rekursiven CTE's (Common Table Expression, aka Subquery Factoring) Daten generieren kann:

(Eine Schleife mit T-SQL hätte es hier natürlich auch getan, aber für dieses Beispiel brauche ich einen Anwendungsfall für die CTE's ;-) )

In dem Beispiel generieren wir uns Daten um Uhrzeiten abbilden zu können auf der Granularität: Stunde, Minute, Sekunde.

Da das Limit für maximale Rekursionen im SQL Server bei 32767 liegt musste ich die Zeilengenerierung in 4-Blöcke aufsplitten...

with 
time_data0_6(hr,mm,ss,secs) as (
 select 0,0,0,0 as hr
 union all
 select ((secs+1) / (60*60)) % 24 as hr, ((secs+1) / (60)) % 60 as mm, (secs+1) % 60 as ss, secs+1 from time_data0_6 where secs < 6*60*60
)
,time_data6_12(hr,mm,ss,secs) as (
 select 0,0,0,6*60*60 as hr
 union all
 select ((secs+1) / (60*60)) % 24 as hr, ((secs+1) / (60)) % 60 as mm, (secs+1) % 60 as ss, secs+1 from time_data6_12 where secs < 12*60*60
)
,time_data12_18(hr,mm,ss,secs) as (
 select 0,0,0,12*60*60 as hr
 union all
 select ((secs+1) / (60*60)) % 24 as hr, ((secs+1) / (60)) % 60 as mm, (secs+1) % 60 as ss, secs+1 from time_data12_18 where secs < 18*60*60
)
,time_data18_24(hr,mm,ss,secs) as (
 select 0,0,0,18*60*60 as hr
 union all
 select ((secs+1) / (60*60)) % 24 as hr, ((secs+1) / (60)) % 60 as mm, (secs+1) % 60 as ss, secs+1 from time_data18_24 where secs < 24*60*60-1
)
, clock_time as (
select * from time_data0_6
union all 
select * from time_data6_12
union all 
select * from time_data12_18
union all 
select * from time_data18_24
)
select hr,mm,ss from clock_time
OPTION (MAXRECURSION 21600) --6*60*60

Ausgabe:

hr          mm          ss
----------- ----------- -----------
0           0           0
0           0           1
0           0           2
0           0           3
0           0           4
...
12          8           57
12          8           58
12          8           59
12          9           0
12          9           1
12          9           2
12          9           3
12          9           4
...
23          59          54
23          59          55
23          59          56
23          59          57
23          59          58
23          59          59

(86403 Zeile(n) betroffen)

So kann man sich auch ganz einfach einen Kalender generieren lassen:

WITH 
days(daydate, start_date, day_offset) AS (
    SELECT 
      CONVERT(DATE, '01.01.2012') AS daydate,
      CONVERT(DATE, '01.01.2012') AS start_date,
      0  AS day_offset
  UNION ALL
    SELECT 
      DATEADD(DAY, day_offset +1, start_date ) AS daydate,
      start_date,
      day_offset + 1 AS day_offset
    FROM days
    WHERE day_offset < 1827
)
SELECT 
  daydate AS datum,
  DATENAME(dw,daydate) AS tag
FROM days 
OPTION (MAXRECURSION 1827) --365 * 5 + 2 für die nächsten 5 Jahre

Ausgabe:

datum      tag
---------- ------------------------------
2012-01-01 Sonntag
2012-01-02 Montag
2012-01-03 Dienstag
2012-01-04 Mittwoch
2012-01-05 Donnerstag
2012-01-06 Freitag
2012-01-07 Samstag
2012-01-08 Sonntag
...

Gruß Tom

 

[Thomas Darimont]

... kleiner Zusatz...
mit der Option

...
OPTION (MAXRECURSION 0)

Kann man die Grenze der Maximalen Rekursionstiefe von 32767 umgehen...

Beispiel:

with
vals(val) as (
  select 0 as val
  union all 
  select val + 1 from vals where val < 100000
)
select * from vals
OPTION (MAXRECURSION 0)

Ausgabe:

0
1
2
3
4
5
6
...
99997
99998
99999
100000

(100001 Zeile(n) betroffen)