Reputation: 94
In addition to Adding up header information while querying detailed information
I have similar requirement now that the last two fields are STRUCT
SELECT 'ABC' username, cast('1234' as int64) QueryID, cast('100' as int64) CPU, cast('123' as int64) IO, [STRUCT(['DB1','DB2','DB1'] as DatabaseReferred,['TB1','TB2','TB3'] as TablesReferred)] Struct_DataType
UNION ALL
SELECT 'ABC' username, cast('8454' as int64) QueryID, cast('589' as int64) CPU, cast('565' as int64) IO, [STRUCT(['DB1','DB2'] as DatabaseReferred, ['TB3','TB6'] as TablesReferred)]
UNION ALL
SELECT 'ABC' username, cast('3564' as int64) QueryID, cast('145' as int64) CPU, cast('243' as int64) IO, [STRUCT(['DB3','DB5'] as DatabaseReferred, ['TB4','TB3'] as TablesReferred)]
UNION ALL
SELECT 'PQR' username, cast('6352' as int64) QueryID, cast('737' as int64) CPU, cast('562' as int64) IO, [STRUCT(['DB2','DB1','DB1'] as DatabaseReferred, ['TB6','TB7','TB2'] as TablesReferred)]
UNION ALL
SELECT 'PQR' username, cast('2345' as int64) QueryID, cast('200' as int64) CPU, cast('126' as int64) IO, [STRUCT(['DB2','DB1'] as DatabaseReferred, ['TB5','TB1'] as TablesReferred)]
I am looking for output as
Username Count(DistinctQueryID) Sum(CPU) SUM(IO) DistinctDatabaseReferred DistinctTablesReferred
ABC 3 834 931 4 5
PQR 2 937 688 2 5
Upvotes: 0
Views: 18
Reputation: 173036
Below is for BigQuery Standard SQL
Note; I am using STRUCTS as it is claimed in question and its title (while data sample was - I hope wrongly - constructed as array of structs
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'ABC' username, CAST('1234' AS int64) QueryID, CAST('100' AS int64) CPU, CAST('123' AS int64) IO, STRUCT(['DB1','DB2','DB1'] AS DatabaseReferred,['TB1','TB2','TB3'] AS TablesReferred) Struct_DataType UNION ALL
SELECT 'ABC' username, CAST('8454' AS int64) QueryID, CAST('589' AS int64) CPU, CAST('565' AS int64) IO, STRUCT(['DB1','DB2'] AS DatabaseReferred, ['TB3','TB6'] AS TablesReferred) UNION ALL
SELECT 'ABC' username, CAST('3564' AS int64) QueryID, CAST('145' AS int64) CPU, CAST('243' AS int64) IO, STRUCT(['DB3','DB5'] AS DatabaseReferred, ['TB4','TB3'] AS TablesReferred) UNION ALL
SELECT 'PQR' username, CAST('6352' AS int64) QueryID, CAST('737' AS int64) CPU, CAST('562' AS int64) IO, STRUCT(['DB2','DB1','DB1'] AS DatabaseReferred, ['TB6','TB7','TB2'] AS TablesReferred) UNION ALL
SELECT 'PQR' username, CAST('2345' AS int64) QueryID, CAST('200' AS int64) CPU, CAST('126' AS int64) IO, STRUCT(['DB2','DB1'] AS DatabaseReferred, ['TB5','TB1'] AS TablesReferred)
)
SELECT
Username,
Count_of_Distinct_QueryId,
Sum_CPU,
Sum_IO,
(SELECT COUNT(DISTINCT db) FROM t.dbs AS db) AS DistinctDatabaseReferred,
(SELECT COUNT(DISTINCT tbl) FROM t.tbls AS tbl) AS DistinctTablesReferred
FROM (
SELECT -- *
Username,
COUNT(DISTINCT QueryId) AS Count_of_Distinct_QueryId,
SUM(CPU) AS Sum_CPU,
SUM(IO) AS Sum_IO,
ARRAY_CONCAT_AGG(Struct_DataType.DatabaseReferred) dbs,
ARRAY_CONCAT_AGG(Struct_DataType.TablesReferred) tbls
FROM `project.dataset.table`
GROUP BY Username
) t
with output
Row Username Count_of_Distinct_QueryId Sum_CPU Sum_IO DistinctDatabaseReferred DistinctTablesReferred
1 ABC 3 834 931 4 5
2 PQR 2 937 688 2 5
Upvotes: 1