主页 / dba / 问题 / 95773
Accepted
xupv5
Asked: 2015-03-21 01:15:04 +0800 CST

计算 2 列中的唯一记录并根据布尔值计算分数

  • 772

我在 Google Big Query 中有一个如下所示的表：

custID  InteractionDate Purchased?  Sales
1       20150312           F         0
1       20150312           T         200
1       20150314           T         150
2       20150221           T         400
2       20150221           F         0
2       20150221           T         120

..我需要转换为以下结构：

 custID InteractionDate Success Sales
 1     20150312          0.5    200
 1     20150314           1     150
 2     20150221         0.66    520

..在第二个表中，成功列定义为

count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),

销售额只是总和。

我对 BQ 和非 SQL 语言很陌生。我根本不知道如何做到这一点，我已经搜索并看到了建议使用分区的示例，但我根本没有看到任何这样做的示例。提前非常感谢。

google-bigquery

2 个回答

  1. Best Answer

    这在 SQL 中非常简单。据我可以阅读BigQuery 查询参考，它支持所有（GROUP BY子句，COUNT()和SUM()函数，CASE表达式）：

    SELECT 
    custID, InteractionDate,
    1.0 * COUNT(CASE WHEN Purchased = 'T' THEN 1 END) / COUNT(*) AS Success,
    SUM(Sales) AS Sales
FROM
    tableName
GROUP BY
    custID, InteractionDate ;
    • 3

  2. 你可以使用这个：

    SELECT custID,
       InteractionDate,
       IFNULL(ROUND((yes)/(yes+NO), 2),0) AS Success,
       sales
FROM
  (SELECT custID,
          InteractionDate,
          sum(if(Purchased=='T',1,0)) AS yes,
          sum(if(Purchased!='T',1,0)) AS NO,
          sum(sales) AS sales
   FROM
     (SELECT 1 AS custID,
             20150312 AS InteractionDate,
             'F' AS Purchased,
             0 AS Sales),
     (SELECT 1 AS custID,
             20150312 AS InteractionDate,
             'T' AS Purchased,
             200 AS Sales),
     (SELECT 1 AS custID,
             20150314 AS InteractionDate,
             'T' AS Purchased,
             150 AS Sales),
     (SELECT 2 AS custID,
             20150221 AS InteractionDate,
             'T' AS Purchased,
             400 AS Sales),
     (SELECT 2 AS custID,
             20150221 AS InteractionDate,
             'F' AS Purchased,
             0 AS Sales),
     (SELECT 2 AS custID,
             20150221 AS InteractionDate,
             'T' AS Purchased,
             120 AS Sales)
   GROUP BY custID,
            InteractionDate)

    以更简单的方式：

    SELECT custID,
       InteractionDate,
       round(sum(if(Purchased=='T',1,0))/(sum(if(Purchased=='T',1,0))+sum(if(Purchased!='T',1,0))),2),
       sum(sales) AS sales
FROM TABLE
GROUP BY custID,
         InteractionDate
    • 2

相关问题