Текущие итоги BigQuery

У меня не получается заставить работать промежуточные итоги в BigQuery.

Я нашел здесь пример, который работает: Текущие итоги BigQuery SQL

SELECT word, word_count, SUM(word_count) OVER(ORDER BY word DESC)
FROM [publicdata:samples.shakespeare]
WHERE corpus  = 'hamlet'
AND word > 'a' LIMIT 30

Но что я действительно хочу сделать - это подсчитать количество самых популярных слов, которые покрывают 80% от общего количества слов. Итак, я попытался подсчитать общую сумму при заказе сначала по word_count:

SELECT word, word_count, SUM(word_count) OVER(ORDER BY word_count DESC)
FROM [publicdata:samples.shakespeare]
WHERE corpus  = 'hamlet'
AND word > 'a' LIMIT 30

Но я понял:

Row word    word_count  f0_  
1   o'er    18          18   
2   answer  13          31   
3   meet    8           39   
4   told    5           44   
5   treason 4           **52**   
6   quality 4           **52**   
7   brave   3           55

Текущая сумма не увеличивается со строки 5 до строки 6. Вероятно, потому что в обоих случаях word_count равно 4.

Что я делаю неправильно?

Может есть способ получше? Мой план состоял в том, чтобы подсчитать промежуточную сумму. Затем разделите его на сумму (word_count) OVER () и отфильтруйте только строки с менее чем 80%. Затем посчитайте количество этих строк.


google-bigquery window-functions cumulative-sum
person Nikolay Novozhlov    schedule 27.05.2015    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Во-первых, удалите «LIMIT 30» - это помешает предложению OVER ().

Вы хотите соотношение? Попробуйте RATIO_TO_REPORT:

SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER(ORDER BY word_count DESC)
FROM [publicdata:samples.shakespeare]
WHERE corpus  = 'hamlet'
AND word > 'a'

Вы все равно хотите, чтобы количество последовательных строк с одинаковыми значениями увеличивалось? Определите порядок этих строк со второстепенным порядком:

SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER(ORDER BY word_count DESC, word)
FROM [publicdata:samples.shakespeare]
WHERE corpus  = 'hamlet'
AND word > 'a'

Вы хотите, чтобы самые популярные слова покрывали 80%? Возьмите эти соотношения, просуммируйте их и отфильтруйте остальные:

SELECT word, word_count, sum_ratio
FROM (
 SELECT word, word_count, SUM(ratio) OVER(ORDER BY ratio, word) sum_ratio
 FROM (
    SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER(ORDER BY word_count DESC, word) ratio
    FROM [publicdata:samples.shakespeare]
    WHERE corpus  = 'hamlet'
    AND word > 'a' 
 )
)
WHERE sum_ratio>0.8

Row word    word_count  sum_ratio    
1   is      313         0.8125175752219499   
2   it      361         0.827019644076648    
3   in      400         0.8430884184308841   
4   my      441         0.8608042421564295   
5   you     499         0.8808500381633391   
6   of      630         0.906158357771261    
7   to      635         0.9316675370586108   
8   and     706         0.9600289237938375   
9   the     995         0.9999999999999999
person Felipe Hoffa    schedule 27.05.2015
comment
Большое спасибо! Это настоящий увлекательный курс по оконным функциям. - person Nikolay Novozhlov; 27.05.2015