protected override string BuildAxisAggregate(AggregateCustomLineCollection query) { string interval; switch (query.Axis.AxisIncrement) { case AxisIncrement.Day: interval = "1 day"; break; case AxisIncrement.Month: interval = "1 month"; break; case AxisIncrement.Year: interval = "1 year"; break; case AxisIncrement.Quarter: interval = "3 months"; break; default: throw new ArgumentOutOfRangeException(); } string countAlias = query.CountSelect.GetAliasFromText(query.SyntaxHelper); string axisColumnAlias = query.AxisSelect.GetAliasFromText(query.SyntaxHelper) ?? "joinDt"; WrapAxisColumnWithDatePartFunction(query, axisColumnAlias); string sql = string.Format(@" {0} SELECT {1} AS ""joinDt"",dataset.{6} FROM generate_series({3}, {4}, interval '{5}') LEFT JOIN ( {2} ) dataset ON dataset.{7} = {1} ORDER BY {1} ", //Anything before the SELECT string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)), GetDatePartOfColumn(query.Axis.AxisIncrement, "generate_series.date"), //the entire query string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)), query.Axis.StartDate, query.Axis.EndDate, interval, countAlias, axisColumnAlias); return(sql); }
protected override string BuildPivotAndAxisAggregate(AggregateCustomLineCollection query) { IQuerySyntaxHelper syntaxHelper = query.SyntaxHelper; string pivotAlias; string countAlias; string axisColumnAlias; var part1 = GetPivotPart1(query, out pivotAlias, out countAlias, out axisColumnAlias); //The dynamic query in which we assemble a query string and EXECUTE it string part2 = string.Format(@" /*DYNAMIC PIVOT*/ declare @Query varchar(MAX) SET @Query = ' {0} {1} /*Would normally be Select * but must make it IsNull to ensure we see 0s instead of null*/ select '+@FinalSelectList+' from ( SELECT {5} as joinDt, {4}, {3} FROM @dateAxis axis LEFT JOIN ( {2} )ds on {5} = ds.{6} ) s PIVOT ( sum({3}) for {4} in ('+@Columns+') --The dynamic Column list we just fetched at top of query ) piv' EXECUTE(@Query) ", syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert < QueryComponent.SELECT))), syntaxHelper.Escape(GetDateAxisTableDeclaration(query.Axis)), //the entire select query up to the end of the group by (omitting any Top X) syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert < QueryComponent.OrderBy && c.Role != CustomLineRole.TopX))), syntaxHelper.Escape(countAlias), syntaxHelper.Escape(pivotAlias), syntaxHelper.Escape(GetDatePartOfColumn(query.Axis.AxisIncrement, "axis.dt")), axisColumnAlias ); return(part1 + part2); }
protected override string BuildAxisAggregate(AggregateCustomLineCollection query) { //we are trying to produce something like this: /* * with calendar as ( * select add_months(to_date('20010101','yyyymmdd'),12* (rownum - 1)) as dt * from dual * connect by rownum <= 1+ * floor(months_between(to_date(to_char(CURRENT_TIMESTAMP, 'YYYYMMDD'), 'yyyymmdd'), to_date('20010101','yyyymmdd')) /12) * ) * select * to_char(dt ,'YYYY') dt, * count(*) NumRecords * from calendar * join * "TEST"."HOSPITALADMISSIONS" on * to_char(dt ,'YYYY') = to_char("TEST"."HOSPITALADMISSIONS"."ADMISSION_DATE" ,'YYYY') * group by * dt * order by dt*/ var countAlias = query.CountSelect.GetAliasFromText(query.SyntaxHelper); var axisColumnAlias = query.AxisSelect.GetAliasFromText(query.SyntaxHelper) ?? "joinDt"; WrapAxisColumnWithDatePartFunction(query, axisColumnAlias); string calendar = GetDateAxisTableDeclaration(query.Axis); return(string.Format( @" {0} {1} SELECT {2} AS ""joinDt"",dataset.{3} FROM calendar LEFT JOIN ( {4} ) dataset ON dataset.{5} = {2} ORDER BY {2} ", //add everything pre SELECT string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)), //then add the calendar calendar, GetDatePartOfColumn(query.Axis.AxisIncrement, "dt"), countAlias, //the entire query string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)), axisColumnAlias )); }
protected override string BuildPivotAndAxisAggregate(AggregateCustomLineCollection query) { string axisColumnWithoutAlias = query.AxisSelect.GetTextWithoutAlias(query.SyntaxHelper); string part1 = GetPivotPart1(query); return(string.Format(@" {0} {1} {2} SET @sql = CONCAT( ' SELECT {3} as joinDt,',@columnsSelectFromDataset,' FROM dateAxis LEFT JOIN ( {4} {5} AS joinDt, ' ,@columnsSelectCases, ' {6} group by {5} ) dataset ON {3} = dataset.joinDt ORDER BY {3} '); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;", string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), GetDateAxisTableDeclaration(query.Axis), part1, query.SyntaxHelper.Escape(GetDatePartOfColumn(query.Axis.AxisIncrement, "dateAxis.dt")), string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert == QueryComponent.SELECT)), //the from including all table joins and where but no calendar table join query.SyntaxHelper.Escape(GetDatePartOfColumn(query.Axis.AxisIncrement, axisColumnWithoutAlias)), //the order by (should be count so that heavy populated columns come first) query.SyntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.FROM && c.LocationToInsert <= QueryComponent.WHERE))) )); }
protected override string BuildPivotOnlyAggregate(AggregateCustomLineCollection query, CustomLine nonPivotColumn) { string part1 = GetPivotPart1(query); string nonPivotColumnSql = nonPivotColumn.GetTextWithoutAlias(query.SyntaxHelper); return(string.Format(@" {0} {1} SET @sql = CONCAT( ' SELECT {2}',@columnsSelectCases,' {3} GROUP BY {4} ORDER BY {4} {5} '); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;", string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), part1, nonPivotColumn, //everything inclusive of FROM but stopping before GROUP BY query.SyntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.FROM && c.LocationToInsert < QueryComponent.GroupBy))), nonPivotColumnSql, //any HAVING SQL query.SyntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert == QueryComponent.Having))) )); }
protected override string BuildAxisAggregate(AggregateCustomLineCollection query) { string countAlias = query.CountSelect.GetAliasFromText(query.SyntaxHelper); string axisColumnAlias = query.AxisSelect.GetAliasFromText(query.SyntaxHelper) ?? "joinDt"; base.WrapAxisColumnWithDatePartFunction(query, axisColumnAlias); return(string.Format( @" {0} {1} SELECT {2} AS joinDt,dataset.{3} FROM @dateAxis axis LEFT JOIN ( {4} ) dataset ON dataset.{5} = {2} ORDER BY {2} " , string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)), GetDateAxisTableDeclaration(query.Axis), GetDatePartOfColumn(query.Axis.AxisIncrement, "axis.dt"), countAlias, //the entire query string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)), axisColumnAlias ).Trim()); }
protected override string BuildPivotAndAxisAggregate(AggregateCustomLineCollection query) { throw new NotImplementedException(); }
protected override string BuildPivotOnlyAggregate(AggregateCustomLineCollection query, CustomLine nonPivotColumn) { throw new NotImplementedException(); }
private string GetPivotPart1(AggregateCustomLineCollection query, out string pivotAlias, out string countAlias, out string axisColumnAlias) { var syntaxHelper = query.SyntaxHelper; //find the pivot column e.g. 'hb_extract AS Healthboard' var pivotSelectLine = query.PivotSelect; string pivotSqlWithoutAlias = pivotSelectLine.GetTextWithoutAlias(syntaxHelper); pivotAlias = pivotSelectLine.GetAliasFromText(syntaxHelper); //ensure it has an RHS if (string.IsNullOrWhiteSpace(pivotAlias)) { pivotAlias = syntaxHelper.GetRuntimeName(pivotSqlWithoutAlias); } string countSqlWithoutAlias = query.CountSelect.GetTextWithoutAlias(syntaxHelper); countAlias = query.CountSelect.GetAliasFromText(syntaxHelper); string axisColumnWithoutAlias = query.AxisSelect?.GetTextWithoutAlias(query.SyntaxHelper); axisColumnAlias = query.AxisSelect?.GetAliasFromText(query.SyntaxHelper) ?? "joinDt"; //if there is an axis we don't want to pivot on values that are outside that axis restriction. if (query.Axis != null) { base.WrapAxisColumnWithDatePartFunction(query, axisColumnAlias); } else { axisColumnAlias = null; axisColumnWithoutAlias = null; } //Part 1 is where we get all the unique values from the pivot column (after applying the WHERE logic) bool anyFilters = query.Lines.Any(l => l.LocationToInsert == QueryComponent.WHERE); string orderBy = countSqlWithoutAlias + " desc"; if (query.TopXOrderBy != null) { orderBy = query.TopXOrderBy.Text; } string havingSqlIfAny = string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert == QueryComponent.Having).Select(l => l.Text)); string part1 = string.Format( @" /*DYNAMICALLY FETCH COLUMN VALUES FOR USE IN PIVOT*/ DECLARE @Columns as VARCHAR(MAX) {0} /*Get distinct values of the PIVOT Column if you have columns with values T and F and Z this will produce [T],[F],[Z] and you will end up with a pivot against these values*/ set @Columns = ( {1} ',' + QUOTENAME({2}) as [text()] {3} {4} {5} ( {2} IS NOT NULL and {2} <> '' {7}) group by {2} {8} order by {6} FOR XML PATH(''), root('MyString'),type ).value('/MyString[1]','varchar(max)') set @Columns = SUBSTRING(@Columns,2,LEN(@Columns)) DECLARE @FinalSelectList as VARCHAR(MAX) SET @FinalSelectList = {9} --Split up that pesky string in tsql which has the column names up into array elements again DECLARE @value varchar(8000) DECLARE @pos INT DECLARE @len INT set @pos = 0 set @len = 0 WHILE CHARINDEX('],', @Columns +',', @pos+1)>0 BEGIN set @len = CHARINDEX('],[', @Columns +'],[', @pos+1) - @pos set @value = SUBSTRING(@Columns, @pos+1, @len) --We are constructing a version that turns: '[fish],[lama]' into 'ISNULL([fish],0) as [fish], ISNULL([lama],0) as [lama]' SET @FinalSelectList = @FinalSelectList + ', ISNULL(' + @value + ',0) as ' + @value set @pos = CHARINDEX('],[', @Columns +'],[', @pos+@len) +1 END if LEFT(@FinalSelectList,1) = ',' SET @FinalSelectList = RIGHT(@FinalSelectList,LEN(@FinalSelectList)-1) ", //select SQL and parameter declarations string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert == QueryComponent.SELECT)), pivotSqlWithoutAlias, //FROM and JOINs that are not to the calendar table string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert == QueryComponent.FROM || l.LocationToInsert == QueryComponent.JoinInfoJoin && l.Role != CustomLineRole.Axis)), string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert == QueryComponent.WHERE)), anyFilters ? "AND" : "WHERE", orderBy, axisColumnWithoutAlias == null ? "": "AND " + axisColumnWithoutAlias + " is not null", havingSqlIfAny, query.Axis != null ? "'joinDt'":"''" ); return(part1); }
protected override string BuildPivotOnlyAggregate(AggregateCustomLineCollection query, CustomLine nonPivotColumn) { IQuerySyntaxHelper syntaxHelper = query.SyntaxHelper; string pivotAlias; string countAlias; string axisColumnAlias; var part1 = GetPivotPart1(query, out pivotAlias, out countAlias, out axisColumnAlias); string nonPivotColumnAlias; string nonPivotColumnSelect; syntaxHelper.SplitLineIntoSelectSQLAndAlias(nonPivotColumn.Text, out nonPivotColumnSelect, out nonPivotColumnAlias); //ensure we have an alias for the non pivot column if (string.IsNullOrWhiteSpace(nonPivotColumnAlias)) { nonPivotColumnAlias = syntaxHelper.GetRuntimeName(nonPivotColumnSelect); } //The dynamic query in which we assemble a query string and EXECUTE it string part2 = string.Format(@" /*DYNAMIC PIVOT*/ declare @Query varchar(MAX) SET @Query = ' {0} /*Would normally be Select * but must make it IsNull to ensure we see 0s instead of null*/ select {1}, '+@FinalSelectList+' from ( {2} ) s PIVOT ( sum({3}) for {4} in ('+@Columns+') --The dynamic Column list we just fetched at top of query ) piv ORDER BY {1}' EXECUTE(@Query) ", //anything before the SELECT (i.e. parameters) syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert < QueryComponent.SELECT))), syntaxHelper.Escape(nonPivotColumnAlias), //the entire select query up to the end of the group by (ommitting any Top X) syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert < QueryComponent.OrderBy && c.Role != CustomLineRole.TopX))), syntaxHelper.Escape(countAlias), syntaxHelper.Escape(pivotAlias)); return(part1 + part2); }
/// <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(AggregateCustomLineCollection query) { string pivotSqlWithoutAlias = query.PivotSelect.GetTextWithoutAlias(query.SyntaxHelper); string countSqlWithoutAlias = query.CountSelect.GetTextWithoutAlias(query.SyntaxHelper); string aggregateMethod; string aggregateParameter; query.SyntaxHelper.SplitLineIntoOuterMostMethodAndContents(countSqlWithoutAlias, out aggregateMethod, out aggregateParameter); if (aggregateParameter.Equals("*")) { aggregateParameter = "1"; } //if there is an axis we must ensure we only pull pivot values where the values appear in that axis range string whereDateColumnNotNull = ""; if (query.AxisSelect != null) { var axisColumnWithoutAlias = query.AxisSelect.GetTextWithoutAlias(query.SyntaxHelper); whereDateColumnNotNull += query.Lines.Any(l => l.LocationToInsert == QueryComponent.WHERE) ? "AND " : "WHERE "; whereDateColumnNotNull += axisColumnWithoutAlias + " IS NOT 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 = query.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 = query.Lines.SingleOrDefault(c => c.LocationToInsert == QueryComponent.Postfix && c.Role == CustomLineRole.TopX); string topXLimitSqlIfAny = topXLimitLine != null ? topXLimitLine.Text : ""; string havingSqlIfAny = string.Join(Environment.NewLine, query.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, query.Lines.Where(l => l.LocationToInsert >= QueryComponent.FROM && l.LocationToInsert <= QueryComponent.WHERE && l.Role != CustomLineRole.Axis)), whereDateColumnNotNull, topXLimitSqlIfAny, orderBy, havingSqlIfAny )); }