jeudi 26 mars 2015

Counting daily distinct records


I have a table that looks like this in 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


..that I need to transform to the following structure:



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


..where in this second table the success column is defined as



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


and sales is just the sum.


I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.





Aucun commentaire:

Enregistrer un commentaire