Menu
One of the nice things relational databases support are the aggregate functions like count, sum, avg etc. But it seems that if you are using GAE, when inserting or updating a record you must calculate and store the count, sum, avg, etc. values of the whole table. But what if you have many conditional groupings? Given a Person:
class Person {
@Id
Integer age;
String city;
}
If I want
Is it correct that everytime I create, update or delete a person I should also calculate both aggregates and store them as separate columns in the same table. If I also want the total and average values per city, should I store these values for each city also as separate columns in the same table?
433
ARRAY_AGG. Returns an ARRAY of expression values. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the OVER clause and how to use it, see Window function calls.
An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*) , aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. All aggregate functions are deterministic.
The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression. In this syntax: ALL instructs the SUM() function to return the sum of all values including duplicates.
BigQuery STRING_AGG function returns a value by gathering all expressions in a row and generating a concatenated string of non-null values. The value can be either STRING or BYTES. The result generally depends on the first expression taken by the BigQuery STRING_AGG function.
Right: to use GAE storage properly, you need to selectively de-normalize some aspects of your model, keeping "redundant" data which, in a DB in normal form, you'd recompute on the fly, such as aggregates (overall and "grouped by" ones).
However, don't add such fields to the Person table in your case -- that makes little sense! Make another PersonAggregates table with columns such as City (null/missing for overall totals), Count, TotalAges (easier to maintain: compute the average at any time as total divided by count).
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
