SQL template to calculate summary of any MHW metric
mhw_metric_summary_sql.Rdthis function to constructs the sql to calculate a summary of an existing MHW metric over space (eg. summaries for each lat/lon coordinate), with optional filtering of ensemble and for a period by year. This inserts these into a string template to generate SQL that can be executed in an MHW database connection, using duckdb SQL dialect. The column that contains the summarized metric will be named with the pattern sql_function_mhw_metric ( example avg_int_max)
Usage
mhw_metric_summary_sql(
mhw_table,
mhw_metric = "int_mean",
sql_function = "avg",
start_year = 2040,
end_year = 2069,
ensemble_list_string = NA,
group_id_value = NULL
)Arguments
- mhw_table
name of the table to use
- mhw_metric
character name of metric ( column ) in the table to summarize: mhw_dur, int_mean, etc
- start_year
= 2040; integer year to start, inclusive (dates will include this start year), for example 2040
- end_year
= 2049 integer year to end, inclusive (dates will include up to 12/31 of the end year), for example 2049
- ensemble_list_string
= NA; optional character string. If NA, then it's ignored and all ensembles are included. if it's string that is a list of ensembles to include in format "006,007,008" only those with matching ensemble will be included. ensembles have leading zeros, are 3 digits and this must be a separated list with leading zeros
- sqlfun
= avg; character aggregate function for duckdb, for example avg, count or median, see https://duckdb.org/docs/sql/functions/aggregates,
- group_id_value=NULL
optional value to set as an arbitrary 'group' value for all of the rows for these results this is useful if these results will be combined with other results and need to be identified. for example the gorup=2040 for the decade. The default is NULL then it's ignored and not group column is NULL. Note that this is not used for grouping in this step, this step only produces on group