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