Cross Table Sum (CrossTableAggregateGenerator)
The Cross Table Sum generator sets the value of the column to the sum of the values of another column aggregated across rows that have a foreign key value that matches the primary key in the current record.
For example, in a
users table, a total_transactions value is obtained from the transactions table by combining all of the transaction_amount values from rows that have a user_id value that matches the primary key value for the current users record.The Cross Table Sum generator does not support linking or consistency. You cannot configure differential privacy.
The generator-specific configuration includes:
- The schema and table that contain the column to sum against.
- The foreign key column to compare against the primary key for the current table.
- The column that contains the values to sum.
- The primary key column in the current table.
{
"generatorId": "CrossTableAggregateGenerator",
"schema": "string",
"table": "string",
"column": "string",
"metadata": {
"foreignSchema": "string",
"foreignTable": "string",
"foreignKeyColumns": [ "string" ],
"sumColumn": "string",
"primaryKeyColumns": [ "string" ]
},
"encryptionProcessor": "x-on", //To use configured Tonic data encryption
"customValueProcessor": "string" //If custom value processor applied
}
In the following example replacement for the Cross Table Sum generator, the value of
total_transactions in the users table is set to the sum of the values of the amount column in the transactions table for rows where user_id has the same value as the id column in the current users table row.{
"name": "total-transactions",
"schema": "public",
"table": "users",
"links": [
{
"generatorId": "CrossTableAggregateGenerator",
"schema": "public",
"table": "users",
"column": "total_transactions",
"metadata": {
"foreignSchema": "public",
"foreignTable": "transactions",
"foreignKeyColumns": [ "user_id" ],
"sumColumn": "amount",
"primaryKeyColumns": [ "id" ]
}
}
]
}
Last modified 1mo ago