More elegant way to write this w/ CTE's?

Gagnon · #1 (**permalink**) 09-07-10, 14:47

Is there a more elegant way to write this cross join to create 5 minute intervals over 24 hours using a CTE? I need to write the same query for 1 minute intervals and I am not looking forward to it.

Code:

SELECT CAST ((CAST(Q1.HH AS VARCHAR(2)) + ':' + CAST(Q2.MM AS VARCHAR(2)) + ':00') AS TIME)
FROM
(
	-- HOUR
	SELECT 0 AS HH
	UNION
	SELECT 1
	UNION
	SELECT 2
	UNION
	SELECT 3
	UNION
	SELECT 4
	UNION
	SELECT 5
	UNION
	SELECT 6
	UNION
	SELECT 7
	UNION
	SELECT 8
	UNION
	SELECT 9
	UNION
	SELECT 10
	UNION
	SELECT 11
	UNION
	SELECT 12
	UNION
	SELECT 13
	UNION
	SELECT 14
	UNION
	SELECT 15
	UNION
	SELECT 16
	UNION
	SELECT 17
	UNION
	SELECT 18
	UNION
	SELECT 19
	UNION
	SELECT 20
	UNION
	SELECT 21
	UNION
	SELECT 22
	UNION
	SELECT 23
) Q1
CROSS JOIN
(
	SELECT 0 AS MM
	UNION
	SELECT 5
	UNION
	SELECT 10
	UNION
	SELECT 15
	UNION
	SELECT 20
	UNION
	SELECT 25
	UNION
	SELECT 30
	UNION
	SELECT 35
	UNION
	SELECT 40
	UNION
	SELECT 45
	UNION
	SELECT 50
	UNION
	SELECT 55
) Q2
ORDER BY 
1

results...

Code:

----------------
00:00:00.0000000
00:05:00.0000000
00:10:00.0000000
00:15:00.0000000
00:20:00.0000000
00:25:00.0000000
00:30:00.0000000
00:35:00.0000000
00:40:00.0000000
00:45:00.0000000
00:50:00.0000000
00:55:00.0000000
01:00:00.0000000
01:05:00.0000000
01:10:00.0000000
01:15:00.0000000
01:20:00.0000000
01:25:00.0000000
01:30:00.0000000
01:35:00.0000000
01:40:00.0000000
01:45:00.0000000
01:50:00.0000000
01:55:00.0000000
02:00:00.0000000
02:05:00.0000000
02:10:00.0000000
02:15:00.0000000
02:20:00.0000000
02:25:00.0000000
02:30:00.0000000
02:35:00.0000000
02:40:00.0000000
02:45:00.0000000
02:50:00.0000000
02:55:00.0000000
03:00:00.0000000
03:05:00.0000000
03:10:00.0000000
03:15:00.0000000
03:20:00.0000000
03:25:00.0000000
03:30:00.0000000
03:35:00.0000000
03:40:00.0000000
03:45:00.0000000
03:50:00.0000000
03:55:00.0000000
04:00:00.0000000
04:05:00.0000000
04:10:00.0000000
04:15:00.0000000
04:20:00.0000000
04:25:00.0000000
04:30:00.0000000
04:35:00.0000000
04:40:00.0000000
04:45:00.0000000
04:50:00.0000000
04:55:00.0000000
05:00:00.0000000
05:05:00.0000000
05:10:00.0000000
05:15:00.0000000
05:20:00.0000000
05:25:00.0000000
05:30:00.0000000
05:35:00.0000000
05:40:00.0000000
05:45:00.0000000
05:50:00.0000000
05:55:00.0000000
06:00:00.0000000
06:05:00.0000000
06:10:00.0000000
06:15:00.0000000
06:20:00.0000000
06:25:00.0000000
06:30:00.0000000
06:35:00.0000000
06:40:00.0000000
06:45:00.0000000
06:50:00.0000000
06:55:00.0000000
07:00:00.0000000
07:05:00.0000000
07:10:00.0000000
07:15:00.0000000
07:20:00.0000000
07:25:00.0000000
07:30:00.0000000
07:35:00.0000000
07:40:00.0000000
07:45:00.0000000
07:50:00.0000000
07:55:00.0000000
08:00:00.0000000
08:05:00.0000000
08:10:00.0000000
08:15:00.0000000
08:20:00.0000000
08:25:00.0000000
08:30:00.0000000
08:35:00.0000000
08:40:00.0000000
08:45:00.0000000
08:50:00.0000000
08:55:00.0000000
09:00:00.0000000
09:05:00.0000000
09:10:00.0000000
09:15:00.0000000
09:20:00.0000000
09:25:00.0000000
09:30:00.0000000
09:35:00.0000000
09:40:00.0000000
09:45:00.0000000
09:50:00.0000000
09:55:00.0000000
10:00:00.0000000
10:05:00.0000000
10:10:00.0000000
10:15:00.0000000
10:20:00.0000000
10:25:00.0000000
10:30:00.0000000
10:35:00.0000000
10:40:00.0000000
10:45:00.0000000
10:50:00.0000000
10:55:00.0000000
11:00:00.0000000
11:05:00.0000000
11:10:00.0000000
11:15:00.0000000
11:20:00.0000000
11:25:00.0000000
11:30:00.0000000
11:35:00.0000000
11:40:00.0000000
11:45:00.0000000
11:50:00.0000000
11:55:00.0000000
12:00:00.0000000
12:05:00.0000000
12:10:00.0000000
12:15:00.0000000
12:20:00.0000000
12:25:00.0000000
12:30:00.0000000
12:35:00.0000000
12:40:00.0000000
12:45:00.0000000
12:50:00.0000000
12:55:00.0000000
13:00:00.0000000
13:05:00.0000000
13:10:00.0000000
13:15:00.0000000
13:20:00.0000000
13:25:00.0000000
13:30:00.0000000
13:35:00.0000000
13:40:00.0000000
13:45:00.0000000
13:50:00.0000000
13:55:00.0000000
14:00:00.0000000
14:05:00.0000000
14:10:00.0000000
14:15:00.0000000
14:20:00.0000000
14:25:00.0000000
14:30:00.0000000
14:35:00.0000000
14:40:00.0000000
14:45:00.0000000
14:50:00.0000000
14:55:00.0000000
15:00:00.0000000
15:05:00.0000000
15:10:00.0000000
15:15:00.0000000
15:20:00.0000000
15:25:00.0000000
15:30:00.0000000
15:35:00.0000000
15:40:00.0000000
15:45:00.0000000
15:50:00.0000000
15:55:00.0000000
16:00:00.0000000
16:05:00.0000000
16:10:00.0000000
16:15:00.0000000
16:20:00.0000000
16:25:00.0000000
16:30:00.0000000
16:35:00.0000000
16:40:00.0000000
16:45:00.0000000
16:50:00.0000000
16:55:00.0000000
17:00:00.0000000
17:05:00.0000000
17:10:00.0000000
17:15:00.0000000
17:20:00.0000000
17:25:00.0000000
17:30:00.0000000
17:35:00.0000000
17:40:00.0000000
17:45:00.0000000
17:50:00.0000000
17:55:00.0000000
18:00:00.0000000
18:05:00.0000000
18:10:00.0000000
18:15:00.0000000
18:20:00.0000000
18:25:00.0000000
18:30:00.0000000
18:35:00.0000000
18:40:00.0000000
18:45:00.0000000
18:50:00.0000000
18:55:00.0000000
19:00:00.0000000
19:05:00.0000000
19:10:00.0000000
19:15:00.0000000
19:20:00.0000000
19:25:00.0000000
19:30:00.0000000
19:35:00.0000000
19:40:00.0000000
19:45:00.0000000
19:50:00.0000000
19:55:00.0000000
20:00:00.0000000
20:05:00.0000000
20:10:00.0000000
20:15:00.0000000
20:20:00.0000000
20:25:00.0000000
20:30:00.0000000
20:35:00.0000000
20:40:00.0000000
20:45:00.0000000
20:50:00.0000000
20:55:00.0000000
21:00:00.0000000
21:05:00.0000000
21:10:00.0000000
21:15:00.0000000
21:20:00.0000000
21:25:00.0000000
21:30:00.0000000
21:35:00.0000000
21:40:00.0000000
21:45:00.0000000
21:50:00.0000000
21:55:00.0000000
22:00:00.0000000
22:05:00.0000000
22:10:00.0000000
22:15:00.0000000
22:20:00.0000000
22:25:00.0000000
22:30:00.0000000
22:35:00.0000000
22:40:00.0000000
22:45:00.0000000
22:50:00.0000000
22:55:00.0000000
23:00:00.0000000
23:05:00.0000000
23:10:00.0000000
23:15:00.0000000
23:20:00.0000000
23:25:00.0000000
23:30:00.0000000
23:35:00.0000000
23:40:00.0000000
23:45:00.0000000
23:50:00.0000000
23:55:00.0000000

Brett Kaiser · #2 (**permalink**) 09-07-10, 16:42

I contest the reason for doing this in the first place...

can you explain why?

Gagnon · #3 (**permalink**) 09-07-10, 17:10

I have tick data for futures contracts, it lists transactions down to the second (sometimes multiple transactions for any given second).

I want to roll it up for 1-minute, 5-minute, 30-minute(, etc...) intervals so I can chart against it.

There could be gaps in the data (low volume ticker), I will be left outer joining against this derived table.

Alternatively I could make this a regular table (instead of calculating it on the fly every time), but I still run into the same problem of the quickest way to generate the initial data load.

I know cross join will work fine, just seeing if there is a cool recursive CTE trick I can use to save me a lot of copy/pasting/typing.

Stealth_DBA · #4 (**permalink**) 09-07-10, 20:36

Gagnon, Here is an example (using DB2 syntax but the basic concept of the CTE is the same).

Code:

WITH HOUR_TAB (HH)
  AS (SELECT 0 FROM SYSIBM.SYSDUMMY1
      UNION ALL
      SELECT HH + 1 FROM HOUR_TAB
      WHERE HH < 23
     )
   , MIN_TAB (MN)
  AS (SELECT 0 FROM SYSIBM.SYSDUMMY1
      UNION ALL
      SELECT MN + 5 FROM MIN_TAB
      WHERE MN < 55
     )
SELECT CAST(CASE WHEN HH BETWEEN  0 AND  9 THEN '0' || HH ELSE '' || HH END AS CHAR(2))
    || ':'
    || CAST(CASE WHEN MN BETWEEN  0 AND  9 THEN '0' || MN ELSE '' || MN END AS CHAR(2))
    || ':00.000000'
FROM HOUR_TAB
   , MIN_TAB
ORDER BY 1

You can remove the FROM SYSIBM.SYSDUMMY1 and the various formatting I did to get the proper output.

The basic concept is 2 CTE queries. One to generate a list of Hours (0-23) and one for Minutes (0-55 at 5 minute intervals).

The main query joins the 2 CTE 'table' without a join predicate (which you normally don't want but in this case it works for you) to apply every set of Minutes to every set of Hours.

Code:

00:00:00.000000
00:05:00.000000
00:10:00.000000
00:15:00.000000
00:20:00.000000
00:25:00.000000
00:30:00.000000
00:35:00.000000
00:40:00.000000
00:45:00.000000
00:50:00.000000
00:55:00.000000
01:00:00.000000
01:05:00.000000
01:10:00.000000
01:15:00.000000
(list truncated to save space)
22:35:00.000000
22:40:00.000000
22:45:00.000000
22:50:00.000000
22:55:00.000000
23:00:00.000000
23:05:00.000000
23:10:00.000000
23:15:00.000000
23:20:00.000000
23:25:00.000000
23:30:00.000000
23:35:00.000000
23:40:00.000000
23:45:00.000000
23:50:00.000000
23:55:00.000000

The intervals can be tweaked by changing the amount added.
If you Seconds, you can use a third CTE to generate a seconds list.

PS you can make the main query a CTE since you probably want to Left Outer Join it to another table.

Gagnon · #5 (**permalink**) 09-08-10, 21:40

Much appreciated Stealth, I have enclosed the final SQL for anyone interested

Code:

WITH CTE_HOUR (HH)
  AS (SELECT 0 
      UNION ALL
      SELECT HH + 1 FROM CTE_HOUR
      WHERE HH < 23
     )
   , CTE_MIN (MN)
  AS (SELECT 0 
      UNION ALL
      SELECT MN + 5 FROM CTE_MIN
      WHERE MN < 55
     )

SELECT 
	CAST(
		CASE 
			WHEN H.HH BETWEEN  0 AND  9 
			THEN '0' 
			ELSE '' 
		END 
	+ CAST(H.HH AS VARCHAR(2))
	+ ':' 
	+
		CASE 
			WHEN M.MN BETWEEN  0 AND  9 
			THEN '0' 
			ELSE '' 
		END 
	+ CAST(M.MN AS VARCHAR(2)) 
	+ ':00.000000' AS TIME
	) AS TIME_SHORT
FROM 
	CTE_HOUR H
CROSS JOIN	CTE_MIN M
ORDER BY 1

Wim · #6 (**permalink**) 09-09-10, 06:27

Code:

    CAST(
        CASE 
            WHEN H.HH BETWEEN  0 AND  9 
            THEN '0' 
            ELSE '' 
        END 
    + CAST(H.HH AS VARCHAR(2))

This also works:

Code:

  RIGHT('0' + CAST(H.HH AS VARCHAR(2)), 2)

Teddy · #7 (**permalink**) 09-09-10, 09:57

I'd use a physical numbers table, no CTE required. You'd be surprised how often a simple table of integers comes in handy (even more so before 2005 when we got nifty ranking functionality). A cross join with numbers 1-24 for hours and 0-60, where "value mod 5 = 0" should knock it out. I may be able to finagle an example later, morning meetings...

Gagnon · #8 (**permalink**) 09-09-10, 22:31

interesting Wim & Teddy, the CTE did not perform well at all, took exactly 14 minutes to generate 1-, 5-, 15-, and 30-minute chart data for 10 different contracts. Once I put the CTE data into a permanent table it took 9 seconds.

Still send it tho Teddy, I like elegant solutions like that.

Internet Services

Web Development

Digital Marketing

Consumer Tech