SQL function on all rows using row parameter

Question

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?

Mikhail Berlyant · Accepted Answer · 2018-06-25 14:47:42Z

2

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`

answered Jun 25, 2018 at 14:47

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

Josh Laird Over a year ago

Perfect, thanks. When would I need to use OVER() as I thought it's used when you want to execute a query on all rows of a table? Is it because I'm using a function where I am already passing in a field from each row of the table?

Collectives™ on Stack Overflow

SQL function on all rows using row parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related