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);
        }
Ejemplo n.º 3
0
        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

                       ));
        }
Ejemplo n.º 4
0
        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)))
                                 ));
        }
Ejemplo n.º 5
0
        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());
        }
Ejemplo n.º 7
0
 protected override string BuildPivotAndAxisAggregate(AggregateCustomLineCollection query)
 {
     throw new NotImplementedException();
 }
Ejemplo n.º 8
0
 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);
        }
Ejemplo n.º 11
0
        /// <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
                                 ));
        }