Translating an Excel concept into SQL-CodePudding

The

This isn't a table by any means, it's more a collection of Variant values entered into cells. Excel makes it easy to sum these values doing =SUM(B3:D6) which gives 25. Let's not go into the details of type checking or anything like that and just figure that sum will easily skip values that don't make sense.

If we were translating this concept into SQL, what would be the most natural way to do this? The few approaches that came to mind are (ignore type errors for now):

MyRange returns an array of values:

-- myRangeAsList = [1,1,1,2, ...] SELECT SUM(elem) FROM UNNEST(myRangeAsList) AS r (elem);

MyRange returns a table-valued function of a single column (basically the opposite of a list):

-- myRangeAsCol = (SELECT 1 UNION ALL SELECT 1 UNION ALL ... SELECT SUM(elem) FROM myRangeAsCol as r (elem);

Or, perhaps more 'correctly', return a 3-columned table such as:

-- myRangeAsTable = (SELECT 1,1,1 UNION ALL SELECT 2,'other',2 UNION ALL ... SELECT SUM(a b c) FROM SELECT a FROM myRangeAsTable (a,b,c)

Unfortunately, I think this makes things the most difficult to work with, as we now have to combine an unknown number of columns.

Perhaps returning a single column is the easiest of the above to work with, but even that takes a very simple concept -- SUM(myRange) and converts into something that is anything but that: SELECT SUM(elem) FROM myRangeAsCol as r (elem).

Perhaps this could also just be rewritten to a function for convenience, for example:

CodePudding user response：

Just possible direction to think

create temp function extract_values (input string) returns array<string> language js as """ return Object.values(JSON.parse(input)); """; with myrangeastable as ( select '1' a, '1' b, '1' c union all select '2', 'other', '2' union all select 'true', '3', '3' union all select '4', '4', '4' ) select sum(safe_cast(value as float64)) range_sum from myrangeastable t, unnest(extract_values(to_json_string(t))) value

with output

Note: no columns explicitly used so should work for any sized range w/o any changes in code

Depends on specific use case, I think above can be wrapped into something more friendly for someone who knows excel to do

CodePudding user response：

Using regular expression to extract all number values from a row could be another option, I guess.

DECLARE rectangular_table ARRAY<STRUCT<A STRING, B STRING, C STRING>> DEFAULT [ ('1', '1', '1'), ('2', 'other', '2'), ('TRUE', '3', '3'), ('4', '4', '4') ]; SELECT SUM(SAFE_CAST(v AS FLOAT64)) AS `sum` FROM UNNEST(rectangular_table) t, UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':"?([0-9.]*)"?[,}]')) v output: ------ ------ | Row | sum | ------ ------ | 1 | 25.0 | ------ ------