BigQuery: How to calculate the running count of distinct visitors for each day and category?
google-bigquery
Solution
Try this:
ts:timestamp, visitor:string, category:string
ts visitor category
----------------------- ------- --------
2013-11-27 00:00:00 UTC A X
2013-11-27 00:00:00 UTC A X
2013-11-27 00:00:00 UTC B X
2013-11-28 00:00:00 UTC C X
2013-11-27 00:00:00 UTC A Y
2013-11-28 00:00:00 UTC B Y
2013-11-29 00:00:00 UTC C Y
query:
select
day, category, sum(cd)
over
(partition by category order by day) as running_total
from (select date(ts) as day, category, count(distinct visitor) as cd from
[test.runningtotal] group by day, category)
this will produce:
day category running_total
---------- -------- -------------
2013-11-27 X 2
2013-11-28 X 3
2013-11-27 Y 1
2013-11-28 Y 2
2013-11-29 Y 3
I didn't test this on large dataset but it might be faster than the JOIN solution.
Problem
In Google BigQuery I have a table like this: startTime:STRING, visitorId:STRING, category:STRING Example for this content: ``` startTime visitorId category ------------------- --------- -------- 2013-11-27 00:00:00 A X 2013-11-27 05:00:00 A X 2013-11-27 07:00:00 B X 2013-11-28 08:00:00 C X ``` I would like to have the following result: ``` day category runningCountOfDistinctVisitors --------- -------- ------------------------------ 2013-11-27 X 2 2013-11-28 X 3 ``` I have tried the following query but it does not seems to work (it's been running for over 3 hours on 1.2M rows table and still hasn't finished) : ``` SELECT left(a.startTime,10) as day, a.category, count(distinct a.visitorId) as runningCountOfDistinctVisitors FROM [MyDataset.MyTable] a LEFT JOIN EACH [MyDataset.MyTable] b ON a.category = b.category WHERE left(b.startTime,10) < left(a.startTime,10) GROUP EACH BY a.category, day ORDER BY a.category, day ``` I also tried to work with the partition function but count distinct does not seems to be supported.