SQL BigQuery - SUM over ARRAY_AGG - Error: No matching signature for argument type: ARRAY

Question

I'm trying to run this line of code:

SUM(
array_agg(
        case when type = "jump" then 1 else 0 end
        )
    over (partition by case_id order by start_at_local_true_01
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
)
as jump_count_per_case_id_in_future,

But I'm running into this error:

No matching signature for aggregate function SUM
for argument types: ARRAY<INT64>.
Supported signatures: SUM(INT64); SUM(FLOAT64); SUM(NUMERIC); SUM(BIGNUMERIC); SUM(INTERVAL) at [33:1]

This is the table I'm starting with:

Row	case_id	start_at_local_true_01	type
1	12123	2022-02-01 11:00:00	null
2	12123	2022-02-01 11:15:00	run
3	12123	2022-02-01 11:21:00	jump
4	12123	2022-02-04 11:31:00	run
5	12123	2022-02-05 11:34:00	jump
6	12555	2022-02-08 12:38:00	credit
7	12555	2022-02-01 11:15:00	null

And this is the table I'd want to end with:

Row	case_id	start_at_local_true_01	type	jump_count_per_case_id_in_future
1	12123	2022-02-01 11:00:00	null	2
2	12123	2022-02-01 11:15:00	run	2
3	12123	2022-02-01 11:21:00	jump	2
4	55555	2022-02-04 11:31:00	run	0
5	12123	2022-02-05 11:34:00	jump	1
6	12123	2022-02-08 12:38:00	credit	0
7	55555	2022-02-01 11:15:00	null	0

This is my code so far (including sample table):

with data_table as(
    select * FROM UNNEST(ARRAY<STRUCT<
    case_id INT64, start_at_local_true_01 DATETIME, type STRING>>
    [
         (12123, DATETIME("2022-02-01 11:00:00"), null)
        ,(12123, DATETIME("2022-02-01 11:15:00"), 'run')
        ,(12123, DATETIME("2022-02-01 11:21:00"), 'jump')
        ,(55555, DATETIME("2022-02-04 11:31:00"), 'run')
        ,(12123, DATETIME("2022-02-05 11:34:00"), 'jump')
        ,(12123, DATETIME("2022-02-08 12:38:00"), 'credit')
        ,(55555, DATETIME("2022-02-01 11:15:00"), null)
    ]
    )
)

select 
data_table.*,

array_agg(
        IFNULL(type,"-")
        )
    over (partition by case_id order by start_at_local_true_01
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
as type_array_per_case_id,

array_agg(
        case when type = "jump" then 1 else 0 end
        )
    over (partition by case_id order by start_at_local_true_01
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
as type_array_per_case_id_to_numbers,

SUM(
array_agg(
        case when type = "jump" then 1 else 0 end
        )
    over (partition by case_id order by start_at_local_true_01
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
)
as jump_count_per_case_id_in_future,

from data_table

Thanks for the help!

Mikhail Berlyant · Accepted Answer · 2022-03-02 18:05:07Z

1

use below instead

COUNTIF(type = "jump")
    over (partition by case_id order by start_at_local_true_01
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
as jump_count_per_case_id_in_future,

answered Mar 2, 2022 at 18:05

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

2 Comments

Phil Over a year ago

This is actually just part of my larger problem. And, sadly, it isn't working to solve my larger problem. I'll post another question about the larger problem soon.

Phil Over a year ago

I just created this to describe the next step in my larger problem stackoverflow.com/questions/71330324/…

Collectives™ on Stack Overflow

SQL BigQuery - SUM over ARRAY_AGG - Error: No matching signature for argument type: ARRAY

I'm trying to run this line of code:

But I'm running into this error:

This is the table I'm starting with:

And this is the table I'd want to end with:

This is my code so far (including sample table):

Thanks for the help!

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

I'm trying to run this line of code:

But I'm running into this error:

This is the table I'm starting with:

And this is the table I'd want to end with:

This is my code so far (including sample table):

Thanks for the help!

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related