英文:
How to replicate dummy data inside a table?
问题
我有一个包含数百行虚拟数据的BQ表，但对我的需求来说不够。我正在寻找一种快速复制数据多次的方法，例如将每一行复制10次并将新行添加到现有表中。如何一次性执行这个SQL操作？
英文:
I have a BQ table with several hundreds of rows of dummy data but it's not enough for my needs. I'm looking for a way to quickly replicate the data multiple times, as in for example copy each row 10 times and add the new rows to the existing table. What's the SQL to do it at once?
答案1
得分: 1
例如，将每一行复制10次并将新行添加到现有表中。
可以使用数组进行有意的复制，如下所示。
SELECT t.* FROM `your_table` t, UNNEST(GENERATE_ARRAY(1, 10))
例如，下面的示例查询将sample_table复制3次。
WITH sample_table AS (
SELECT 1 id, 'foo' name UNION ALL
SELECT 2 id, 'bar' name
)
SELECT t.* FROM `sample_table` t, UNNEST(GENERATE_ARRAY(1, 3))
--查询结果
+----+------+
| id | name |
+----+------+
| 1 | foo |
| 1 | foo |
| 1 | foo |
| 2 | bar |
| 2 | bar |
| 2 | bar |
+----+------+
- 并且你可以使用CREATE OR REPLACE TABLE `your_table` AS语句替换现有表。
英文:
> for example copy each row 10 times and add the new rows to the existing table.
You can use an array for intentional duplication like below.
SELECT t.* FROM `your_table` t, UNNEST(GENERATE_ARRAY(1, 10))
For example, below sample query will duplicate sample_table 3 times.
WITH sample_table AS (
SELECT 1 id, 'foo' name UNION ALL
SELECT 2 id, 'bar' name
)
SELECT t.* FROM `sample_table` t, UNNEST(GENERATE_ARRAY(1, 3))
--Query results
+----+------+
| id | name |
+----+------+
| 1 | foo |
| 1 | foo |
| 1 | foo |
| 2 | bar |
| 2 | bar |
| 2 | bar |
+----+------+
- and you can replace existing table with CREATE OR REPLACE TABLE `your_table` AS statement.
答案2
得分: 1
以下是您要的中文翻译：
尝试类似这样的内容：
WITH users AS (
SELECT 1 id, 'ali' name UNION ALL
SELECT 2 id, 'joe' name
)
SELECT u.* FROM `users` u, UNNEST(GENERATE_ARRAY(1, 3))
英文:
Try something like this:
WITH users AS (
SELECT 1 id, 'ali' name UNION ALL
SELECT 2 id, 'joe' name
)
SELECT u.* FROM `users` u, UNNEST(GENERATE_ARRAY(1, 3))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论