Why does nth_value function in BigQuery on display for rows >= n? -

code below produces result variable nt4 null rows 1,2, , 3. looks strange. bug? value should 'of' of rows in window function.

select    word,    word_count, corpus,    nth_value(word,4) on (partition corpus order word_count desc) nt4,    rank(word) on (partition corpus order word_count desc) rank    [publicdata:samples.shakespeare] 

this not bug. expected

the reason - if use order without specifying rows or range, order implies window extends beginning of partition current row. see window functions details

meantime, quick explanation:

for example, "kinghenryviii" partition
first row has window 1 row word - "the"
second row has 2 rows in window - "the" , "i"
third - "the", "i" , "and"

so far see there no 4th row here value null

for forth row thare 4 rows in window - "the", "i", "and" , "of" fifth - "the", "i", "and", "of" , "to"

as see, starting forth rows - forth value available , "of"

hope clear , helped you