public string Compile(QueryBuilder.QuerySetup query, Facet facet, string intervalQuery, string countColumn)
{
string sql = $@"
WITH categories(category, lower, upper) AS (
{intervalQuery}
), outerbounds(lower, upper) AS (
SELECT MIN(lower), MAX(upper)
FROM categories
)
SELECT c.category, c.lower, c.upper, COALESCE(r.count_column, 0) as count_column
FROM categories c
LEFT JOIN (
SELECT category, COUNT(DISTINCT {countColumn}) AS count_column
FROM {query.Facet.TargetTable.ResolvedSqlJoinName}
CROSS JOIN outerbounds
JOIN categories
ON categories.lower <= cast({facet.CategoryIdExpr} as decimal(15, 6))
AND categories.upper >= cast({facet.CategoryIdExpr} as decimal(15, 6))
AND (NOT (categories.upper < outerbounds.upper AND cast({facet.CategoryIdExpr} as decimal(15, 6)) = categories.upper))
{query.Joins.Combine("\n\t\t\t\t\t")}
WHERE TRUE
{ "AND ".GlueTo(query.Criterias.Combine(" AND ")) }
GROUP BY category
) AS r
ON r.category = c.category
ORDER BY c.lower";
return(sql);
}
public string Compile(QueryBuilder.QuerySetup querySetup, Facet facet)
{
string sql = $@"
SELECT MIN({facet.CategoryIdExpr}) AS lower, MAX({facet.CategoryIdExpr}) AS upper
FROM {facet.TargetTable.ResolvedSqlJoinName}
";
return(sql);
}
public virtual string Compile(QueryBuilder.QuerySetup query, Facet facet, string categoryNameFilter)
{
string sql = $@"
SELECT cast({facet.CategoryIdExpr} AS varchar) AS category, {facet.CategoryNameExpr} AS name
FROM {query.Facet.TargetTable.ResolvedSqlJoinName}
{query.Joins.Combine("")}
WHERE 1 = 1
{"AND ".GlueTo(CategoryNameLike(facet, categoryNameFilter)) }
{"AND ".GlueTo(query.Criterias.Combine(" AND "))}
GROUP BY 1, 2
{SortBy(facet)}";
return(sql);
}
public string Compile(QueryBuilder.QuerySetup query, Facet facet, Facet countFacet, string aggType)
{
string sql = $@"
SELECT category, {aggType}(value) AS count
FROM (
SELECT {facet.CategoryIdExpr} AS category, {countFacet.CategoryIdExpr} AS value
FROM {query.Facet.TargetTable.ResolvedSqlJoinName}
{query.Joins.Combine("\t\t\t\t\t\n")}
WHERE 1 = 1
{"AND ".GlueTo(query.Criterias.Combine(" AND "))}
GROUP BY {facet.CategoryIdExpr}, {countFacet.CategoryIdExpr}
) AS x
GROUP BY category;
";
return(sql);
}
