6

I need to use a temporary timerange table for my SQL query in treasure data presto:

CREATE TEMPORARY TABLE fakehours (Hour BIGINT);

INSERT INTO Hour VALUES (0);
INSERT INTO Hour VALUES (1);
INSERT INTO Hour VALUES (2);
INSERT INTO Hour VALUES (3);
INSERT INTO Hour VALUES (4);
INSERT INTO Hour VALUES (5);
INSERT INTO Hour VALUES (6);
INSERT INTO Hour VALUES (7);
INSERT INTO Hour VALUES (8);
INSERT INTO Hour VALUES (9);
INSERT INTO Hour VALUES (10);
INSERT INTO Hour VALUES (11);
INSERT INTO Hour VALUES (12);
INSERT INTO Hour VALUES (13);
INSERT INTO Hour VALUES (14);
INSERT INTO Hour VALUES (15);
INSERT INTO Hour VALUES (16);
INSERT INTO Hour VALUES (17);
INSERT INTO Hour VALUES (18);
INSERT INTO Hour VALUES (19);
INSERT INTO Hour VALUES (20);
INSERT INTO Hour VALUES (21);
INSERT INTO Hour VALUES (22);
INSERT INTO Hour VALUES (23);

What can I do in Presto like this?

Oskar Austegard
  • 4,599
  • 4
  • 36
  • 50
Javier
  • 249
  • 1
  • 6
  • 16
  • 4
    Kind of wish someone would change the subject line on this one-- it's not a general question about temporary tables, it's specifically how to handle a Hours table or a similar range (days of month, for example). – Carnot Antonio Romero Sep 13 '18 at 21:21

3 Answers3

4

Pro tipp, you might even be able to get away without a temporary table using the WITH syntax. And then in the following SELECT statement you can use hours as if it were a table.

WITH hours AS (SELECT * FROM UNNEST(ARRAY[0,1, ... 22,23]) AS t (hour))
SELECT 
    * 
FROM 
    hours
;
akuhn
  • 27,477
  • 2
  • 76
  • 91
3

I would do this:

CREATE TABLE fakehours AS
WITH hours AS (
SELECT * FROM UNNEST(SEQUENCE(0,23,1)) AS t (hour)
)
SELECT   * 
FROM hours
;
Giacomo
  • 1,796
  • 1
  • 24
  • 35
2

Depending on your Presto version try:

SELECT * FROM UNNEST(SEQUENCE(0,23))

or

SELECT * FROM UNNEST(ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23])

Should achieve what you want in a join, short of having temp tables available in Presto.

econnormist
  • 80
  • 7