Running count of apperance of customer id Bigquery -

there similar questions on here either cannot figure out how convert situation (likely) or not similar read close want (bigquery: how calculate running count of distinct visitors each day , category?)

anyway...

i have order table in bigquery has many column headers need use of them list few of them here

orderid, customerid, transactiondate, revenue

(i need of fields)

i want work out instance of customer id in table new column, if had place 3 orders, , customer id 1234, 1st instance in data table 1 in new column, 2nd 2 , 3rd 3

for example data looks this

> orderid     ||    customerid    ||    transactiondate    ||    revenue > 1           ||    1             ||     01/01/15          ||     £20  > 2           ||    2             ||     01/01/15          ||     £20  > 3           ||    3             ||     01/01/15          ||     £20  > 4           ||    1             ||     01/01/15          ||     £20  > 5           ||    1             ||     01/01/15          ||     £20  > 6           ||    2             ||     01/01/15          ||     £20  > 7           ||    4             ||     01/01/15          ||     £20 

i run query against adds in new column stating instance if customerid record like

> orderid     ||    customerid    ||    transactiondate    ||    revenue ||instance > 1           ||    1             ||     01/01/15          ||     £20    ||1  > 2           ||    2             ||     01/01/15          ||     £20    ||1 > 3           ||    3             ||     01/01/15          ||     £20    ||1  > 4           ||    1             ||     01/01/15          ||     £20    ||2 > 5           ||    1             ||     01/01/15          ||     £20    ||3  > 6           ||    2             ||     01/01/15          ||     £20    ||2  > 7           ||    4             ||     01/01/15          ||     £20    ||1 

each time seen customerid happens instance increments 1

also need run against ever growing table @ 1.6 million rows.

i hope 1 can me out.

cheers

john

window functions helping here:

window functions enable calculations on specific partition, or "window", of result set. each window function expects on clause specifies partition, in following syntax:

over (       [partition <expr>]       [order <expr>]       [rows <expr> | range <expr>]      ) 

partition by optional. order by optional in cases, window functions, such rank() or dense_rank(), require clause.

join each , group each by clauses can't used on output of window functions. generate large query results when using window functions, must use partition by.

select *, row_number() on (partition customerid order transactiondate) instance  (select 1 orderid, 1 customerid, '01/01/15' transactiondate,'£20' revenue),   (select 2 orderid, 2 customerid, '01/01/15' transactiondate,'£20' revenue),   (select 3 orderid, 3 customerid, '01/01/15' transactiondate,'£20' revenue),   (select 4 orderid, 1 customerid, '01/01/15' transactiondate,'£20' revenue),   (select 5 orderid, 1 customerid, '01/01/15' transactiondate,'£20' revenue),   (select 6 orderid, 2 customerid, '01/01/15' transactiondate,'£20' revenue),   (select 7 orderid, 4 customerid, '01/01/15' transactiondate,'£20' revenue)  order orderid 

returns:

+-----+---------+------------+-----------------+---------+----------+---+ | row | orderid | customerid | transactiondate | revenue | instance |   | +-----+---------+------------+-----------------+---------+----------+---+ |   1 |       1 |          1 | 01/01/15        | £20     |        1 |   | |   2 |       2 |          2 | 01/01/15        | £20     |        1 |   | |   3 |       3 |          3 | 01/01/15        | £20     |        1 |   | |   4 |       4 |          1 | 01/01/15        | £20     |        2 |   | |   5 |       5 |          1 | 01/01/15        | £20     |        3 |   | |   6 |       6 |          2 | 01/01/15        | £20     |        2 |   | |   7 |       7 |          4 | 01/01/15        | £20     |        1 |   | +-----+---------+------------+-----------------+---------+----------+---+