有扩展表的SQL函数吗? [英] Is there a SQL function to expand table?

问题描述

我隐约记得有一个函数可以执行此操作，但我想我可能会发疯.

说我有一个数据表，称它为table1.它具有三列:column1，column2，column3.查询

Say I have a datatable, call it table1. It has three columns: column1, column2, column3. The query

 SELECT * FROM table1 

返回表1中的所有行/列.是否没有某种类型的EXPAND函数可以让我重复该结果?例如，如果我想将SELECT * FROM table1查询中的所有内容重复三次，则可以执行EXPAND(3)吗?

returns all rows/columns from table1. Isn't there some type of EXPAND function that allows me to duplicate that result? For example, if I want to duplicate everything from the SELECT * FROM table1 query three times, I can do something like EXPAND(3) ?

推荐答案

在BigQuery中，我建议使用CROSS JOIN:

In BigQuery, I would recommend a CROSS JOIN:

SELECT t1.*
FROM table1 CROSS JOIN
     (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3) n;

这对于很多副本可能会很麻烦，但是您可以通过生成数字来简化此操作:

This can get cumbersome for lots of copies, but you can simplify this by generating the numbers:

SELECT t1.*
FROM table1 CROSS JOIN
     UNNEST(GENERATE_ARRAY(1, 3)) n

这将创建一个包含三个元素的数组，并将其取消嵌套成行.

This creates an array with three elements and unnests it into rows.

在这两种情况下，都可以在SELECT中包含n来区分副本.

In both these cases, you can include n in the SELECT to distinguish the copies.

这篇关于有扩展表的SQL函数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！