private string BuildAxisOnlyAggregate(List <CustomLine> lines, IQueryAxis axis)
{
var syntaxHelper = new MySqlQuerySyntaxHelper();
GetAggregateAxisBits(syntaxHelper, lines, out CustomLine countSelectLine,
out string countSqlWithoutAlias,
out string countAlias,
out CustomLine axisColumn,
out string axisColumnWithoutAlias,
out string axisColumnAlias);
WrapAxisColumnWithDatePartFunction(axisColumn, lines, axis, axisColumnWithoutAlias, axisColumnAlias);
return(string.Format(
@"
{0}
{1}
SELECT
{2} AS joinDt,dataset.{3}
FROM
dateAxis
LEFT JOIN
(
{4}
) dataset
ON dataset.{5} = {2}
ORDER BY
{2}
"
,
string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)),
GetDateAxisTableDeclaration(axis),
GetDatePartOfColumn(axis.AxisIncrement, "dateAxis.dt"),
countAlias,
//the entire query
string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)),
axisColumnAlias
).Trim());
}
/// <summary>
/// Returns the section of the PIVOT which identifies unique values. For MySql this is done by assembling a massive CASE statement.
/// </summary>
/// <param name="lines"></param>
/// <param name="syntaxHelper"></param>
/// <returns></returns>
private static string GetPivotPart1(List <CustomLine> lines, out MySqlQuerySyntaxHelper syntaxHelper, out string axisColumnWithoutAlias)
{
syntaxHelper = new MySqlQuerySyntaxHelper();
var pivotSelectLine = lines.Single(l =>
l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Pivot);
string pivotSqlWithoutAlias;
string pivotAlias;
syntaxHelper.SplitLineIntoSelectSQLAndAlias(pivotSelectLine.Text, out pivotSqlWithoutAlias, out pivotAlias);
var countSelectLine = lines.Single(l =>
l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction);
string countSqlWithoutAlias;
string countAlias;
syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias);
string aggregateMethod;
string aggregateParameter;
syntaxHelper.SplitLineIntoOuterMostMethodAndContents(countSqlWithoutAlias, out aggregateMethod,
out aggregateParameter);
if (aggregateParameter.Equals("*"))
{
aggregateParameter = "1";
}
var joinDtColumn = lines.SingleOrDefault(l =>
l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Axis);
//if there is an axis we must ensure we only pull pivot values where the values appear in that axis range
string whereDateColumnNotNull = "";
if (joinDtColumn != null)
{
string axisColumnAlias;
syntaxHelper.SplitLineIntoSelectSQLAndAlias(joinDtColumn.Text, out axisColumnWithoutAlias, out axisColumnAlias);
whereDateColumnNotNull += lines.Any(l => l.LocationToInsert == QueryComponent.WHERE) ? "AND " : "WHERE ";
whereDateColumnNotNull += axisColumnWithoutAlias + " IS NOT NULL";
}
else
{
axisColumnWithoutAlias = null;
}
//work out how to order the pivot columns
string orderBy = countSqlWithoutAlias + " desc"; //default, order by the count(*) / sum(*) etc column desc
//theres an explicit topX so order by it verbatim instead
var topXOrderByLine =
lines.SingleOrDefault(c => c.LocationToInsert == QueryComponent.OrderBy && c.Role == CustomLineRole.TopX);
if (topXOrderByLine != null)
{
orderBy = topXOrderByLine.Text;
}
//if theres a topX limit postfix line (See MySqlQuerySyntaxHelper.HowDoWeAchieveTopX) add that too
var topXLimitLine =
lines.SingleOrDefault(c => c.LocationToInsert == QueryComponent.Postfix && c.Role == CustomLineRole.TopX);
string topXLimitSqlIfAny = topXLimitLine != null ? topXLimitLine.Text : "";
string havingSqlIfAny = string.Join(Environment.NewLine,
lines.Where(l => l.LocationToInsert == QueryComponent.Having).Select(l => l.Text));
return(string.Format(@"
SET SESSION group_concat_max_len = 1000000;
DROP TEMPORARY TABLE IF EXISTS pivotValues;
/*Get the unique values in the pivot column into a temporary table ordered by size of the count*/
CREATE TEMPORARY TABLE pivotValues AS (
SELECT
{1} as piv
{3}
{4}
group by
{1}
{7}
order by
{6}
{5}
);
/* Build case when x='fish' then 1 end as 'fish', case when x='cammel' then 1 end as 'cammel' etc*/
SET @columnsSelectCases = NULL;
SELECT
GROUP_CONCAT(
CONCAT(
'{0}(case when {1} = ''', REPLACE(pivotValues.piv,'\'','\\\''), ''' then {2} end) AS `', pivotValues.piv,'`'
)
) INTO @columnsSelectCases
FROM
pivotValues;
/* Build dataset.fish, dataset.cammel etc*/
SET @columnsSelectFromDataset = NULL;
SELECT
GROUP_CONCAT(
CONCAT(
'dataset.`', pivotValues.piv,'`')
) INTO @columnsSelectFromDataset
FROM
pivotValues;
",
aggregateMethod,
pivotSqlWithoutAlias,
aggregateParameter,
//the from including all table joins and where but no calendar table join
string.Join(Environment.NewLine,
lines.Where(l =>
l.LocationToInsert >= QueryComponent.FROM && l.LocationToInsert <= QueryComponent.WHERE &&
l.Role != CustomLineRole.Axis)),
whereDateColumnNotNull,
topXLimitSqlIfAny,
orderBy,
havingSqlIfAny
));
}
