时间戳

首页 > 解决方案 > SQL Distinct 具有多个条件

sql - SQL Distinct 具有多个条件

问题描述

我想根据SampleData Table & Output生成一个表输出。我的 SQL 查询如下，但它没有给我最后三列的正确结果

SELECT
  UserLogin,
  COUNT(DISTINCT consumerID) AS TotalUniqueconsumerIDs,  
  COUNT(DISTINCT (CASE
        WHEN ActivitiesCompleted > 0 AND QuizCompleted <= 0 THEN consumerID END)) AS YesActivityNoQuiz,
  COUNT(DISTINCT (CASE
        WHEN ActivitiesCompleted <= 0 AND QuizCompleted > 0 THEN consumerID END)) AS NoActivityYesQuiz,  
COUNT(DISTINCT (CASE
        WHEN ActivitiesCompleted >= 1 AND QuizCompleted >= 1 THEN consumerID END)) AS YesActivityYesQuiz
FROM
  TableName
GROUP BY
  UserLogin

样本数据表和输出

非常感谢任何帮助。

更多信息：

也许我上面不清楚，所以在这里提供更多信息：

我创建了另一个示例表，其中只有 1 个 UserLogin 的数据，看起来像这样，如果我在 Excel 中从外部旋转这个示例数据集，我会看到这个

如您所见，此表中的 consumerID 的此透视屏幕截图都有超过 1 个 ActivitiesCompleted 和 1 QuizCompleted。

基于我上面的查询的 SQL 输出如下所示

但是，根据 Pivot 屏幕截图，我期待 YesActivityYesQuiz 列中的 1 和 YesActivityNoQuiz 和 NoActivityYesQuiz 中的零

样本数据：

Date,UserLogin,consumerID,ActivitiesCompleted,QuizCompleted
6/8/2018,aasufhar,consumerA,0,1
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,2,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,1,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerA,0,0
6/8/2018,aasufhar,consumerB,0,1
6/8/2018,aasufhar,consumerB,0,0
6/8/2018,aasufhar,consumerB,2,0
6/8/2018,aasufhar,consumerB,1,0
6/8/2018,aasufhar,consumerB,1,0
6/8/2018,aasufhar,consumerB,1,0
6/8/2018,aasufhar,consumerB,0,0
6/8/2018,aasufhar,consumerB,0,0
6/8/2018,aasufhar,consumerB,0,0
6/8/2018,aasufhar,consumerB,0,0
6/8/2018,aasufhar,consumerB,0,0

标签： sqlgoogle-bigqueryansi-sqlbigquery-standard-sql

解决方案

有问题的查询对我来说似乎是正确的（至少它返回了预期结果中显示的内容）

不过，我可以提出它的不那么冗长的版本（BigQuery 标准 SQL） 

#standardSQL
SELECT UserLogin, 
  COUNT(DISTINCT consumerID) AS TotalUniqueconsumerIDs,  
  COUNT(DISTINCT IF(ActivitiesCompleted > 0 AND QuizCompleted <= 0, consumerID, NULL)) AS YesActivityNoQuiz,
  COUNT(DISTINCT IF(ActivitiesCompleted <= 0 AND QuizCompleted > 0, consumerID, NULL)) AS NoActivityYesQuiz,  
  COUNT(DISTINCT IF(ActivitiesCompleted >= 1 AND QuizCompleted >= 1, consumerID, NULL)) AS YesActivityYesQuiz
FROM TableName
GROUP BY UserLogin

根据 OP 最近的示例/解释更新查询 

#standardSQL
SELECT UserLogin, 
  COUNT(DISTINCT consumerID) AS TotalUniqueconsumerIDs,  
  COUNT(DISTINCT IF(ActivitiesCompleted > 0 AND QuizCompleted <= 0, consumerID, NULL)) AS YesActivityNoQuiz,
  COUNT(DISTINCT IF(ActivitiesCompleted <= 0 AND QuizCompleted > 0, consumerID, NULL)) AS NoActivityYesQuiz,  
  COUNT(DISTINCT IF(ActivitiesCompleted >= 1 AND QuizCompleted >= 1, consumerID, NULL)) AS YesActivityYesQuiz,
  COUNT(DISTINCT IF(ActivitiesCompleted <= 0 AND QuizCompleted <= 0, consumerID, NULL)) AS NoActivityNoQuiz
FROM (
  SELECT UserLogin,consumerID,
    SUM(ActivitiesCompleted) ActivitiesCompleted,
    SUM(QuizCompleted) QuizCompleted
  FROM TableName
  GROUP BY UserLogin,consumerID
)
GROUP BY UserLogin

推荐阅读