在SQL中统计两列不同值的数量

Question

我在数据库中有一个表，其中包含主键的相应值。

我想从两列中计算不同的值。

我已经知道一种方法，就是使用union all，并对结果表应用groupby。

Select Id,Brand1
into #Temp
from data
union all
Select Id,Brand2
from data

Select ID,Count(Distinct Brand1)
from #Temp
group by ID

我们可以同样使用临时表在BigQuery中进行操作。

示例表：

ID  Brand1  Brand2
1    A       B
1    B       C
2    D       A
2    A       D

结果表

ID  Distinct_Count_Brand
1    3
2    2

正如您在此列中所见，Distinct_count_Brand正在计算Brand1和Brand2两列中品牌的唯一数量。

我已经知道一种方法（基本上是解开枢轴），但想知道是否有其他方法来计算两列中的唯一值。

- BrownBatman

---

你的问题是关于SQL Server还是BigQuery？ - Simon

你可以提供任何其中之一的解决方案。 - BrownBatman

好的，我不了解BigQuery，但是我看到你已经有一个应该可以工作的答案了。 - Simon

是的，我已经有答案了，但我想知道是否有其他方法可以不使用数据逆转来完成。 - BrownBatman

Answer 1

我不太了解BigQuery的特性，但也许你可以直接内联Union查询语句：

SELECT ID, COUNT(DISTINCT Brand)
FROM
(
    SELECT ID, Brand1 AS Brand FROM data
    UNION ALL
    SELECT ID, Brand2 FROM data
) t
GROUP BY ID;

- Tim Biegeleisen

---

嗨，Tim，你的解决方案几乎和我的一样，因为你正在做连接两个表的相同操作。有没有其他方法可以做到不需要将列进行转换或者基本上将所有值放在单个列中呢？顺便说一下，感谢你的快速回复。 - BrownBatman

如果你发现自己经常或持续需要执行这个查询，那么我会质疑你的数据库是否被正确规范化。理想情况下，如果你只有一个品牌列，你就不需要使用联合查询了。 - Tim Biegeleisen

@TimBiegeleisen . . . 这个查询在SQL Server和BigQuery中都可以工作。我倾向于在每个数据库中以不同的逻辑编写代码，这也是为什么我提供了一个答案的原因。 - Gordon Linoff

Answer 2

在SQL Server中，我会使用：

Select b.id, count(distinct b.brand)
from data d cross apply
     (values (id, brand1), (id, brand2)) b(id, brand)
group by b.id;

这里有一个db<>fiddle。

在BigQuery中，等效的表达方式如下：

select t.id, count(distinct brand)
from t cross join
     unnest(array[brand1, brand2]) brand
group by t.id;

这里是一个 BigQuery 查询，可证明此操作有效：

with t as (
      select 1 as id, 'A' as brand1, 'B' as brand2 union all
      select 1, 'B', 'C' union all
      select 2, 'D', 'A' union all
      select 2, 'A', 'D'
     )
select t.id, count(distinct brand)
from t cross join
     unnest(array[brand1, brand2]) brand
group by t.id;

- Gordon Linoff

---

谢谢。一直在寻找不同的方法，现在找到了一个。但是第二种解决方案不会创建每个品牌对应每个ID的表格，因为你正在使用交叉应用。 - BrownBatman

@BrownBatman……我不明白你的评论。我已经包含了一个BQ查询，证明它可以做到你所要求的。 - Gordon Linoff

Answer 3

我只是把这两列连接在一起，就像这样：

SELECT
  date,
  COUNT( DISTINCT( CONCAT( storeId, '---', userId) ) ) as visits
FROM
  main.reports
GROUP BY
  date

- noam sondak