Timerange Table SQL Presto

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?

Answers


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
;

I would do this:

CREATE TABLE fakehours AS
WITH hours AS (
SELECT * FROM UNNEST(SEQUENCE(0,23,1)) AS t (hour)
)
SELECT   * 
FROM hours
;

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.


Need Your Help

While debugging javascript, is there a way to alert current call stack?

javascript debugging

For simple javascript debugging I'll use alerts to show variable values and the like. Is there a way to get the current call stack in javascript to be able to display it in an alert?

UIVIEW Subclass with UITextViewDelegate

iphone ios xcode

My view controller was getting complicated so i decided to group some controls together and subclass a uiview. In my view controller i'm grabbing the view: