Presto使用'with'查询创建表

typically to create a table in Presto (from existing db tables), I do:

通常要在Presto中创建一个表（来自现有的数据库表），我这样做：

create table abc as (
select...
)

But to make my code simple, I've broken out subqueries like this:

但为了使我的代码简单，我打破了这样的子查询：

with sub1 as (
select...
),

sub2 as (
select...
),

sub3 as (
select...
)

select
from sub1 join sub2 on ...
          join sub3 on ...

Where do I put the create table statement here? The actual query is more complex than the above so I am trying to avoid having to put the subqueries within the main query.

我在哪里放置create table语句？实际查询比上面的更复杂，所以我试图避免必须将子查询放在主查询中。

4 个解决方案

#1

This is possible with an INSERT INTO not sure about CREATE TABLE:

这可能是INSERT INTO不确定CREATE TABLE：

INSERT INTO s1 WITH q1 AS (...) SELECT * FROM q1

使用q1 AS（...）SELECT * FROM q1插入到s1中

Maybe you could give this a shot:

也许你可以试一试：

CREATE TABLE s1 as WITH q1 AS (...) SELECT * FROM q1

CREATE TABLE s1 as WITH q1 AS（...）SELECT * FROM q1

#2

I believe you need to 'wrap' the entire query like this:

我相信你需要像这样“包装”整个查询：

create table EXAMPLE as (
with sub1 as (
select ...
),
.......

select 
from sub1....

)

#3

Syntax is just as if you prepend create table .. as to the select. E.g. the following worked for me on Presto 0.170:

语法就像你在select table之前添加了create table ..例如。以下在Presto 0.170上为我工作：

create table memory.default.a as
with w as (
    select * from (values 1) t(x)
)
select * from w;

(I use experimental memory connector so that this is copy-pastable to try it out.)

（我使用实验性内存连接器，这样可以复制它来试用它。）

#4

If Strings are concerned, then following works

如果涉及字符串，则后续工作

WITH sample AS (
    SELECT * FROM (VALUES ('strA', 'strB'), ('strC', 'strD'), ('strE', 'strF')) AS account (name, cat)
)

SELECT name, cat from sample;

if integers are only concerned values , then following works: -

如果整数只涉及值，那么以下工作： -

WITH  slab (SNo,Amount) AS (VALUES (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(6,6000),(7,7000),(8,8000),(9,9000),(10,10000),(11, 11000),(12,12000),(13,13000),(14,14000),(15,15000),(16,16000),(17,17000),(18,18000),(19,19000),(20,20000),(21,21000),(22,22000),(23,23000),(24,24000),(25,25000),(26,26000),(27,27000),(28,28000),(29,29000),(30,30000),(31,31000),(32,32000),(33,33000),(34,34000),(35,35000),(36,36000),(37,37000),(38,38000),(39,39000),(40,40000),(41,41000),(42,42000),(43,43000),(44,44000),(45,45000),(46,46000),(47,47000),(48,48000),(49,49000),(50,50000),(51,51000)
) 
SELECT * FROM slab;

#1