BigQuery SQL functions not working properly

Question

I have a problem with functions such as maxif and sumif. When I try to use any of them in my project the console returns 'Function not found: sumif/maxif; Did you mean sum/max?'

It is odd, because function countif works perfectly fine, and both of maxif and sumif are described in the BigQuery documentation, so I'm kind of worried what to do with them in order to run the code properly.

Beneath is a part of my code, any suggestions would be most welcome:

SELECT
  DISTINCT *,
  COUNTIF(status ='completed') OVER (PARTITION BY id ORDER BY created_at) cpp,  --this works
  sumif(value,status='completed') OVER (PARTITION BY id ORDER BY created_at) spp, -- this doesn't
  maxif(created_at, status = 'completed') OVER (PARTITION BY id ORDER BY created_at DESC) lastpp,
FROM
  `production.payment_transactions`

Mikhail Berlyant · Accepted Answer · 2020-08-27 13:54:50Z

1

Below is for BigQuery Standard SQL

#standardSQL
SELECT
  DISTINCT *,
  COUNTIF(status = 'completed') OVER (PARTITION BY id ORDER BY created_at) cpp,  --this works
  SUM(IF(status = 'completed', value, NULL)) OVER (PARTITION BY id ORDER BY created_at) spp, -- this now works
  MAX(IF(status = 'completed', value, NULL)) OVER (PARTITION BY id ORDER BY created_at DESC) lastpp, -- this now works
FROM `production.payment_transactions`

answered Aug 27, 2020 at 13:54

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-08-27 11:50:24Z

0

SUMIF() and MAXIF() are not a big query functions. Use a CASE expression:

maxif(case when status = 'completed' then created_at end) over (partition by id order by created_at desc)

This is confusing because the functions are used in other parts of the GCP environment, particularly a component called Dataprep.

answered Aug 27, 2020 at 11:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

BigQuery SQL functions not working properly

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related