Calculating the current amount for a section in BigQuery

Question

I am trying to calculate the current amount for a section. This seems simpler and faster than the method proposed in BigQuery SQL, which works with totals .

For example:

SELECT corpus, corpus_date, word_count, sum (word_count) over (partition by corpus, corpus_date order by word_count, word DESC) as run_sum FROM [Publicdata: samples.shakespeare]

I ran into two problems:

• I cannot allow the sum to begin with the most common word (the word with the highest word). Installing DESC or ASC just doesn't change anything, and the sum starts with the least common word (s). If I changed the order by including only "order by word_count" than the current amount is not correct, since lines with the same order (== same word_count) give the same current amount.

• In a similar query that I execute (see below), the first line of the current amount gives the sum 0, although the sum of the field I am not equal to 0 for the first line. Why is this happening? How can I solve the problem to show the correct current amount? Request:

select * from
(the SELECT
mongo_id,
account_id,
event_date,
trx_amount_sum_per_day,
the SUM (trx_amount_sum_per_day) the OVER (the PARTITION by mongo_id, account_id the ORDER BY clause event_date the DESC) the AS running_sum,
the ROW_NUMBER () the OVER (the PARTITION by mongo_id, account_id the ORDER BY clause event_date the DESC) the AS row_num
the FROM [xs-polar-gasket-4: publicdataset.publictable]
) order by event_date desc

Answer 1

In question 1:

Edit:

SELECT
  corpus, corpus_date, word_count, SUM(word_count)
OVER
  (PARTITION BY corpus, corpus_date
  ORDER BY word_count, word DESC) AS running_sum
FROM [publicdata:samples.shakespeare]

To:

SELECT
  corpus, corpus_date, word_count, SUM(word_count)
OVER
  (PARTITION BY corpus, corpus_date
  ORDER BY word_count DESC, word) AS running_sum
FROM [publicdata:samples.shakespeare]

(The original query is sorted by word, but you want to sort it by word_count)