CODEWITHSUNDEEP
sqlgoogle-bigquery
Josh Laird
Josh Laird

Reputation: 7214

SQL function on all rows using row parameter

I have a table that has 3 columns I care about: Name, Id and ParentId. I would like to find out how many children an Id has which will require me to go through each row.

Here's what I have so far:

CREATE TEMPORARY FUNCTION numChildren(givenId INT64)
  RETURNS INT64
  AS ((SELECT count(*) FROM `playground.table` WHERE ParentId = givenId));

SELECT numChildren(Id) OVER() as Test FROM `playground.table`

The exact error I'm getting is: Error: Function USER_DEFINED_FUNCTIONS:NUMCHILDREN does not support an OVER clause at [5:8].

I want to go through each row, pass the Id into the function and then get back the number of rows that have that Id as the ParentId.

Am I going about this the right way? Would I have to use the APIs to do a query like this?

Upvotes: 0

Views: 584

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Just remove OVER() as below 

CREATE TEMPORARY FUNCTION numChildren(givenId INT64)
RETURNS INT64
  AS ((SELECT count(*) FROM `playground.table` WHERE ParentId = givenId));

SELECT numChildren(Id) as Test FROM `playground.table`

Upvotes: 2

Related Questions