Início / dba / Perguntas / 95773
Accepted
xupv5
Asked: 2015-03-21 01:15:04 +0800 CST

Contando registros exclusivos em 2 colunas e calculando uma pontuação com base em valores booleanos

  • 772

Eu tenho uma tabela que se parece com isso no 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

..que preciso transformar para a seguinte estrutura:

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

..onde nesta segunda tabela a coluna de sucesso é definida como

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

e as vendas são apenas a soma.

Sou bastante novo em linguagens BQ e não-SQL. Simplesmente não sei como fazer isso, pesquisei e vi exemplos que sugerem o uso de partição, mas simplesmente não vi nenhum exemplo que faça exatamente isso. Muito obrigado antecipadamente.

google-bigquery

2 respostas

  1. Best Answer

    Isso seria muito simples em SQL. Tanto quanto eu posso ler a referência do BigQuery Query , ela suporta todos ( GROUP BYcláusula, COUNT()e SUM()funções, CASEexpressões):

    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. Você pode usar este:

    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)

    de forma mais simples:

    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

relate perguntas