英文:
How to create a table that counts unique IDs from multiple tables and displays their respective origins?
问题
我正在尝试创建一个表格，用于统计多个其他表格中唯一ID的数量。我已经成功获取数量并按降序对结果进行排序。我遇到的问题是如何添加一个新列来标识每个计数来自哪个表格。
我希望我的表格看起来像这样：
| 来源 | 数量 |
|---|---|
| 表格 1 | 60 |
| 表格 2 | 50 |
| 表格 3 | 40 |
到目前为止，这是我写的代码：
CREATE TABLE MyProject.New_Table
AS (
SELECT
COUNT(DISTINCT Id) AS Number, 'Table 1' AS Origin
FROM 'MyProject.Table1'
UNION ALL
SELECT
COUNT(DISTINCT Id) AS Number, 'Table 2' AS Origin
FROM 'MyProject.Table2'
UNION ALL
SELECT
COUNT(DISTINCT Id) AS Number, 'Table 3' AS Origin
FROM 'MyProject.Table3'
)
ORDER BY Number DESC
这个代码会给你一个包含Number和Origin列的表格，以显示每个计数的来源表格。
英文:
I am trying to create a table that counts unique ID's among several other tables. I have gotten as far as getting the count and sorting the results in descending order. What I am having trouble figuring out is how to add a new column that identifies which table each count has come from.
I want my table to look something like:
| Origin | Number |
|---|---|
| Table 1 | 60 |
| Table 2 | 50 |
| Table 3 | 40 |
So far this is what I've written:
CREATE TABLE MyProject.New_Table
AS (
SELECT
COUNT(DISTINCT Id) AS Number
FROM 'MyProject.Table1'
UNION ALL
SELECT
COUNT(DISTINCT Id) AS Number
FROM 'MyProject.Table2'
UNION ALL
SELECT
COUNT(DISTINCT Id) AS Number
FROM 'MyProject.Table3'
)
ORDER BY Number DESC
This gets me the Number column but not the table associated with it.
答案1
得分: 0
你需要将以下内容翻译为中文：
需要在每个子查询中添加新字段，如下所示：
CREATE TABLE MyProject.New_Table AS
SELECT '表 1' AS 源,
COUNT(DISTINCT Id) AS 数量
FROM 'MyProject.Table1'
UNION ALL
SELECT '表 2' AS 源,
COUNT(DISTINCT Id) AS 数量
FROM 'MyProject.Table2'
UNION ALL
SELECT '表 3' AS 源,
COUNT(DISTINCT Id) AS 数量
FROM 'MyProject.Table3'
按数量降序排序
另一种选择是首先合并所有表，然后仅应用一次聚合：
CREATE TABLE MyProject.New_Table AS
SELECT 源,
COUNT(DISTINCT Id) AS 数量
FROM (SELECT '表 1' AS 源, Id FROM 'MyProject.Table1'
UNION ALL
SELECT '表 2' AS 源, Id FROM 'MyProject.Table2'
UNION ALL
SELECT '表 3' AS 源, Id FROM 'MyProject.Table3') cte
GROUP BY 源
英文:
You need to add a new field to each of your subqueries as follows:
CREATE TABLE MyProject.New_Table AS
SELECT 'Table 1' AS Origin,
COUNT(DISTINCT Id) AS Number
FROM 'MyProject.Table1'
UNION ALL
SELECT 'Table 2' AS Origin,
COUNT(DISTINCT Id) AS Number
FROM 'MyProject.Table2'
UNION ALL
SELECT 'Table 3' AS Origin,
COUNT(DISTINCT Id) AS Number
FROM 'MyProject.Table3'
ORDER BY Number DESC
Yet another option would be to first merge all your tables, and then apply aggregation once only:
CREATE TABLE MyProject.New_Table AS
SELECT Origin,
COUNT(DISTINCT Id) AS Number
FROM (SELECT 'Table 1' AS Origin, Id FROM 'MyProject.Table1'
UNION ALL
SELECT 'Table 2' AS Origin, Id FROM 'MyProject.Table2'
UNION ALL
SELECT 'Table 3' AS Origin, Id FROM 'MyProject.Table3') cte
GROUP BY Origin
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论