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 )); }