R/sql.pk.R

In cezden/rpostgrexplorer: Simplification of analysis in PostgreSQL and Redshift DBs

#' Attribute characteristics
#' 
#' Returns the query returning:
#' \itemize{
#'  \item row_cnt number of rows returned by \code{query}
#'  \item distinct_val_cnt number of distinct values of \code{query.colname}
#'  \item mincnt minimal number of instances for some distinct value of \code{query.colname}
#'  \item avgcnt average number of instances of distinct values of \code{query.colname}
#'  \item maxcnt maximal number of instances for some distinct value of \code{query.colname}
#'  \item sdevcnt standard deviation of the number of distinct value instances \code{query.colname}
#'  \item null_cnt number of NULL instances of \code{query.colname}
#' }
#' @param query the query to analyze
#' @param query.colname the attribute name
#' @param query.countname the alias for counting variable (some valid SQL name not among the attributes generated by query)
#' @param query.nullitymarker the alias for counting variable (some valid SQL name not among the attributes generated by query)
sql.attribute.characteristics <- function(query, query.colname, query.countname, query.nullitymarker){
  sql.fill.template(
    "attribute_primarykeyability", 
    query.names = query,
    param.list = list(
      "%%QUERY%%" = query, 
      "%%VALNAME%%" = query.colname, 
      "%%VALCOUNTER%%" = query.countname, 
      "%%NULLITYMARKER%%" = query.nullitymarker)
    )
}


#' @export
db.attribute.groupping.stats <- function(db.connection, atts.to.check){
  atts.ext <- atts.to.check %>% dplyr::mutate(
    schemed.tab = sql.table.schemed(tab.name = tablename, schema.name = schemaname), 
    query = sql.attribute.characteristics(
      query = paste0("select ", attname, " from ", schemed.tab), 
      query.colname = attname,
      query.countname = "cnt", 
      query.nullitymarker = "nullity"
      )
    )
  ## query generated, let's process... ##TODO: write
  atts.ext
}