public string BuildAggregate(List <CustomLine> queryLines, IQueryAxis axisIfAny) { var lines = new AggregateCustomLineCollection(queryLines, axisIfAny, GetQuerySyntaxHelper()); //no axis no pivot if (lines.AxisSelect == null && lines.PivotSelect == null) { return(BuildBasicAggregate(lines)); } //axis (no pivot) if (lines.PivotSelect == null) { return(BuildAxisAggregate(lines)); } //pivot (no axis) if (lines.AxisSelect == null) { return(BuildPivotOnlyAggregate(lines, GetPivotOnlyNonPivotColumn(lines))); } //pivot and axis return(BuildPivotAndAxisAggregate(lines)); }
private string GetDateAxisTableDeclaration(IQueryAxis axis) { //if pivot dimension is set then this code appears inside dynamic SQL constant string that will be Exec'd so we have to escape single quotes string startDateSql = axis.StartDate; string endDateSql = axis.EndDate; return(String.Format( @" DECLARE @startDate DATE DECLARE @endDate DATE SET @startDate = {0} SET @endDate = {1} DECLARE @dateAxis TABLE ( dt DATE ) DECLARE @currentDate DATE = @startDate WHILE @currentDate <= @endDate BEGIN INSERT INTO @dateAxis SELECT @currentDate SET @currentDate = DATEADD({2}, 1, @currentDate) END ", startDateSql, endDateSql, axis.AxisIncrement)); }
private string BuildPivotAndAxisAggregate(List <CustomLine> lines, IQueryAxis axis) { IQuerySyntaxHelper syntaxHelper; string pivotAlias; string countAlias; string axisColumnAlias; var part1 = GetPivotPart1(lines, axis, out syntaxHelper, 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, lines.Where(c => c.LocationToInsert < QueryComponent.SELECT))), syntaxHelper.Escape(GetDateAxisTableDeclaration(axis)), //the entire select query up to the end of the group by (ommitting any Top X) syntaxHelper.Escape(string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert < QueryComponent.OrderBy && c.Role != CustomLineRole.TopX))), syntaxHelper.Escape(countAlias), syntaxHelper.Escape(pivotAlias), syntaxHelper.Escape(GetDatePartOfColumn(axis.AxisIncrement, "axis.dt")), axisColumnAlias ); return(part1 + part2); }
public AggregateCustomLineCollection(List <CustomLine> queryLines, IQueryAxis axisIfAny, IQuerySyntaxHelper querySyntaxHelper) { Lines = queryLines; Axis = axisIfAny; SyntaxHelper = querySyntaxHelper; Validate(); }
private string GetDateAxisTableDeclaration(IQueryAxis axis) { //if the axis is days then there are likely to be thousands of them but if we start adding thousands of years //mysql date falls over with overflow exceptions string thousands = axis.AxisIncrement == AxisIncrement.Day ? @"JOIN (SELECT 0 thousands UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000 UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000 ) thousands" : ""; string plusThousands = axis.AxisIncrement == AxisIncrement.Day ? "+ thousands":""; //QueryComponent.JoinInfoJoin return ($@" SET @startDate = {axis.StartDate}; SET @endDate = {axis.EndDate}; drop temporary table if exists dateAxis; create temporary table dateAxis ( dt DATE ); insert into dateAxis SELECT distinct (@startDate + INTERVAL c.number {axis.AxisIncrement}) AS date FROM (SELECT singles + tens + hundreds {plusThousands} number FROM ( SELECT 0 singles UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) singles JOIN (SELECT 0 tens UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 ) tens JOIN (SELECT 0 hundreds UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900 ) hundreds {thousands} ORDER BY number DESC) c WHERE c.number BETWEEN 0 and 10000; delete from dateAxis where dt > @endDate;"); }
private string BuildPivotAndAxisAggregate(List <CustomLine> lines, IQueryAxis axis) { string axisColumnWithoutAlias; MySqlQuerySyntaxHelper syntaxHelper; string part1 = GetPivotPart1(lines, out syntaxHelper, out axisColumnWithoutAlias); 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, lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), GetDateAxisTableDeclaration(axis), part1, syntaxHelper.Escape(GetDatePartOfColumn(axis.AxisIncrement, "dateAxis.dt")), string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert == QueryComponent.SELECT)), //the from including all table joins and where but no calendar table join syntaxHelper.Escape(GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias)), //the order by (should be count so that heavy populated columns come first) syntaxHelper.Escape(string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert >= QueryComponent.FROM && c.LocationToInsert <= QueryComponent.WHERE))) )); }
public override string BuildAggregate(List <CustomLine> queryLines, IQueryAxis axisIfAny, bool pivot) { if (!pivot && axisIfAny == null) { return(string.Join(Environment.NewLine, queryLines)); } //axis only if (!pivot) { return(BuildAxisOnlyAggregate(queryLines, axisIfAny)); } throw new System.NotImplementedException(); }
private string BuildAxisAggregate(List <CustomLine> lines, IQueryAxis axis) { var syntaxHelper = new MicrosoftQuerySyntaxHelper(); 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); CustomLine axisColumn; string axisColumnWithoutAlias; CustomLine axisGroupBy; string axisColumnAlias; AdjustAxisQueryLines(lines, axis, syntaxHelper, out axisColumn, out axisColumnWithoutAlias, out axisColumnAlias, out axisGroupBy); 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, lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)), GetDateAxisTableDeclaration(axis), GetDatePartOfColumn(axis.AxisIncrement, "axis.dt"), countAlias, //the entire query string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)), axisColumnAlias ).Trim()); }
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()); }
public override string BuildAggregate(List <CustomLine> queryLines, IQueryAxis axisIfAny, bool pivot) { if (axisIfAny == null && !pivot) { return(string.Join(Environment.NewLine, queryLines)); } if (!pivot) { //axis but no pivot return(BuildAxisAggregate(queryLines, axisIfAny)); } //axis and pivot (cannot pivot without axis) if (axisIfAny == null) { return(BuildPivotOnlyAggregate(queryLines)); } return(BuildPivotAndAxisAggregate(queryLines, axisIfAny)); }
private void AdjustAxisQueryLines(List <CustomLine> lines, IQueryAxis axis, IQuerySyntaxHelper syntaxHelper, out CustomLine axisColumn, out string axisColumnWithoutAlias, out string axisColumnAlias, out CustomLine axisGroupBy) { //Deal with the axis dimension which is currently [mydb].[mytbl].[mycol] and needs to become YEAR([mydb].[mytbl].[mycol]) As joinDt axisColumn = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Axis); syntaxHelper.SplitLineIntoSelectSQLAndAlias(axisColumn.Text, out axisColumnWithoutAlias, out axisColumnAlias); axisGroupBy = lines.Single(l => l.LocationToInsert == QueryComponent.GroupBy && l.Role == CustomLineRole.Axis); if (string.IsNullOrWhiteSpace(axisColumnAlias)) { axisColumnAlias = "joinDt"; } var axisColumnEndedWithComma = axisColumn.Text.EndsWith(","); axisColumn.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + " AS " + axisColumnAlias + (axisColumnEndedWithComma ? "," : ""); var groupByEndedWithComma = axisGroupBy.Text.EndsWith(","); axisGroupBy.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + (groupByEndedWithComma ? "," : ""); }
private string GetDateAxisTableDeclaration(IQueryAxis axis) { //https://stackoverflow.com/questions/8374959/how-to-populate-calendar-table-in-oracle //expect the date to be either '2010-01-01' or a function that evaluates to a date e.g. CURRENT_TIMESTAMP string startDateSql; //is it a date in some format or other? if (DateTime.TryParse(axis.StartDate.Trim('\'', '"'), out DateTime start)) { startDateSql = $"to_date('{start.ToString("yyyyMMdd")}','yyyymmdd')"; } else { startDateSql = $"to_date(to_char({axis.StartDate}, 'YYYYMMDD'), 'yyyymmdd')";//assume its some Oracle specific syntax that results in a date } string endDateSql; if (DateTime.TryParse(axis.EndDate.Trim('\'', '"'), out DateTime end)) { endDateSql = $"to_date('{end.ToString("yyyyMMdd")}','yyyymmdd')"; } else { endDateSql = $"to_date(to_char({axis.EndDate}, 'YYYYMMDD'), 'yyyymmdd')";//assume its some Oracle specific syntax that results in a date e.g. CURRENT_TIMESTAMP } switch (axis.AxisIncrement) { case AxisIncrement.Year: return (string.Format( @" with calendar as ( select add_months({0},12* (rownum - 1)) as dt from dual connect by rownum <= 1+ floor(months_between({1}, {0}) /12) )", startDateSql, endDateSql)); case AxisIncrement.Day: return (string.Format( @" with calendar as ( select {0} + (rownum - 1) as dt from dual connect by rownum <= 1+ floor({1} - {0}) )", startDateSql, endDateSql)); case AxisIncrement.Month: return (string.Format( @" with calendar as ( select add_months({0},rownum - 1) as dt from dual connect by rownum <= 1+ floor(months_between({1}, {0})) )", startDateSql, endDateSql)); case AxisIncrement.Quarter: return (string.Format( @" with calendar as ( select add_months({0},3* (rownum - 1)) as dt from dual connect by rownum <= 1+ floor(months_between({1}, {0}) /3) )", startDateSql, endDateSql)); default: throw new NotImplementedException(); } }
/// <summary> /// Changes the axis column in the GROUP BY section of the query (e.g. "[MyDb]..[mytbl].[AdmissionDate],") and /// the axis column in the SELECT section of the query (e.g. "[MyDb]..[mytbl].[AdmissionDate] as Admt,") with /// the appropriate axis increment (e.g. "YEAR([MyDb]..[mytbl].[AdmissionDate])," and "YEAR([MyDb]..[mytbl].[AdmissionDate]) as Admt,") /// </summary> /// <param name="axisColumn"></param> /// <param name="lines"></param> /// <param name="axis"></param> /// <param name="axisColumnWithoutAlias"></param> /// <param name="axisColumnAlias"></param> protected void WrapAxisColumnWithDatePartFunction(CustomLine axisColumn, List <CustomLine> lines, IQueryAxis axis, string axisColumnWithoutAlias, string axisColumnAlias) { var axisGroupBy = lines.Single(l => l.LocationToInsert == QueryComponent.GroupBy && l.Role == CustomLineRole.Axis); var axisColumnEndedWithComma = axisColumn.Text.EndsWith(","); axisColumn.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + " AS " + axisColumnAlias + (axisColumnEndedWithComma ? "," : ""); var groupByEndedWithComma = axisGroupBy.Text.EndsWith(","); axisGroupBy.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + (groupByEndedWithComma ? "," : ""); }
public abstract string BuildAggregate(List <CustomLine> queryLines, IQueryAxis axisIfAny, bool pivot);
private string GetPivotPart1(List <CustomLine> lines, IQueryAxis axis, out IQuerySyntaxHelper syntaxHelper, out string pivotAlias, out string countAlias, out string axisColumnAlias) { syntaxHelper = new MicrosoftQuerySyntaxHelper(); //find the pivot column e.g. 'hb_extract AS Healthboard' var pivotSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Pivot); //the LHS e.g. hb_extract string pivotSqlWithoutAlias; //the RHS e.g. Healthboard syntaxHelper.SplitLineIntoSelectSQLAndAlias(pivotSelectLine.Text, out pivotSqlWithoutAlias, out pivotAlias); //ensure it has an RHS if (string.IsNullOrWhiteSpace(pivotAlias)) { pivotAlias = syntaxHelper.GetRuntimeName(pivotSqlWithoutAlias); } var countSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction); string countSqlWithoutAlias; syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias); CustomLine axisColumn; string axisColumnWithoutAlias; CustomLine axisGroupBy; //if there is an axis we don't want to pivot on values that are outside that axis restriction. if (axis != null) { AdjustAxisQueryLines(lines, axis, syntaxHelper, out axisColumn, out axisColumnWithoutAlias, out axisColumnAlias, out axisGroupBy); } 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 = lines.Any(l => l.LocationToInsert == QueryComponent.WHERE); string orderBy = countSqlWithoutAlias + " desc"; var topXOrderByLine = lines.SingleOrDefault(l => l.LocationToInsert == QueryComponent.OrderBy && l.Role == CustomLineRole.TopX); if (topXOrderByLine != null) { orderBy = topXOrderByLine.Text; } string havingSqlIfAny = string.Join(Environment.NewLine, 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, lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.SELECT)), pivotSqlWithoutAlias, //FROM and JOINs that are not to the calendar table string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.FROM || l.LocationToInsert == QueryComponent.JoinInfoJoin && l.Role != CustomLineRole.Axis)), string.Join(Environment.NewLine, lines.Where(l => l.LocationToInsert == QueryComponent.WHERE)), anyFilters ? "AND" : "WHERE", orderBy, axisColumnWithoutAlias == null ? "": "AND " + axisColumnWithoutAlias + " is not null", havingSqlIfAny, axis != null ? "'joinDt'":"''" ); return(part1); }
private string BuildAxisOnlyAggregate(List <CustomLine> lines, IQueryAxis axis) { //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 syntaxHelper = new OracleQuerySyntaxHelper(); 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); string calendar = GetDateAxisTableDeclaration(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, lines.Where(c => c.LocationToInsert < QueryComponent.SELECT)), //then add the calendar calendar, GetDatePartOfColumn(axis.AxisIncrement, "dt"), countAlias, //the entire query string.Join(Environment.NewLine, lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert <= QueryComponent.Having)), axisColumnAlias )); }