如何在snowflake中选择多个包含假数据的行

tquggr8v  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(209)

在snowflake中,有没有更简单的方法来选择内存中的多行假数据,而不将实际数据加载到表中?下面是一个示例查询,显示了我当前如何生成一个包含多行伪数据的对象。

with
  fake_row_1 as (
    select
      1 as num,
      'one' as txt
  ),
  fake_row_2 as (
    select
      2 as num,
      'two' as txt
  ),
  fake_row_3 as (
    select
      3 as num,
      'three' as txt
  ),
  fake_table as (
    select * from fake_row_1 union
    select * from fake_row_2 union
    select * from fake_row_3
  )
select *
from fake_table

我尝试测试对查询逻辑的更改,而不是将测试数据加载和卸载到测试表中,而是尝试在内存中暂存一个假表,以便更快地验证预期结果。
理想情况下,我可以运行类似以下的查询。

with
  fake_table as (
    select
      columns (num, txt)
      values (1, 'one'),
             (2, 'two'),
             (3, 'three')
  )
select *
from fake_table
niwlg2el

niwlg2el1#

你能在cte做工会吗?

with
  fake_rows as (
    select
      1 as num,
      'one' as txt
 union
    select
      2,
      'two'
 union
    select
      3,
      'three'
  )
select *
from  fake_rows

这可能会更干净一些:
用假的行作为(

select $1 AS txt,
       $2 as num
FROM 
(VALUES  
 (1,'one'),
 (2,'two'),
 (3,'three') 
     ))
select * from  fake_rows
bfnvny8b

bfnvny8b2#

我认为您在这里回答了您的问题,您可以在CREATETABLE语句中生成如上所述的硬编码数据,这应该可以满足您的需要。

相关问题