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