This is the sixteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation
Today we implement various aggregates. Let’s start with the interface:
|
1 2 3 4 5 6 7 8 9 |
using Model; namespace QueryLogic.Aggregates { public interface IAggregate { Cell Calculate(Relation relation); } } |
We can see that the aggregate returns one cell for whole relation. You might wonder why is it implemented this way but it will be clear when we come to selection. And now the implementations, we begin with simple COUNT working on the whole relation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
using System.Globalization; using System.Linq; using Model; using QueryLogic.Expressions.CellExpressions; namespace QueryLogic.Aggregates { public class CountAggregate : IAggregate { private readonly ICellExpression _outerCellExpression; private readonly ColumnHeader _resultColumn; public CountAggregate(ICellExpression outerCellExpression, string alias = "") { _outerCellExpression = outerCellExpression; _resultColumn = new ColumnHeader("", alias); } public Cell Calculate(Relation relation) { return _outerCellExpression.Calculate(new Row (new[] {new Cell(_resultColumn, relation.Rows.Count().ToString(CultureInfo.InvariantCulture))})); } protected bool Equals(CountAggregate other) { return Equals(_outerCellExpression, other._outerCellExpression) && Equals(_resultColumn, other._resultColumn); } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; if (obj.GetType() != this.GetType()) return false; return Equals((CountAggregate) obj); } public override int GetHashCode() { unchecked { return ((_outerCellExpression != null ? _outerCellExpression.GetHashCode() : 0)*397) ^ (_resultColumn != null ? _resultColumn.GetHashCode() : 0); } } } } |
We simply return the number of rows.
In order to implement other aggregates, let’s start with base class for aggregate working on single column:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
using Model; using QueryLogic.Expressions.CellExpressions; namespace QueryLogic.Aggregates { public abstract class ColumnAggregate : IAggregate { private readonly ICellExpression _cellExpression; private readonly ICellExpression _outerCellExpression; protected ColumnAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias) { _cellExpression = cellExpression; _outerCellExpression = outerCellExpression; ResultColumn = new ColumnHeader("", alias); } public ColumnHeader ResultColumn { get; private set; } public Cell Calculate(Relation relation) { ResetAggregate(); foreach (Row row in relation.Rows) { Cell resultFromRow = _cellExpression.Calculate(row); UpdateValue(resultFromRow); } object value = GetValue(); return _outerCellExpression.Calculate( new Row(new[] { new Cell(ResultColumn, value != null ? value.ToString() : null) })); } protected bool Equals(ColumnAggregate other) { return Equals(_outerCellExpression, other._outerCellExpression) && Equals(ResultColumn, other.ResultColumn); } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; if (obj.GetType() != GetType()) return false; return Equals((ColumnAggregate)obj); } public override int GetHashCode() { unchecked { int hashCode = (_outerCellExpression != null ? _outerCellExpression.GetHashCode() : 0); hashCode = (hashCode * 397) ^ (ResultColumn != null ? ResultColumn.GetHashCode() : 0); return hashCode; } } protected abstract object GetValue(); protected abstract void UpdateValue(Cell cell); protected virtual void ResetAggregate() { } } } |
And now specific implementation for COUNT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
using Model; using QueryLogic.Expressions.CellExpressions; namespace QueryLogic.Aggregates { public class CountColumnAggregate : ColumnAggregate { private int _count; public CountColumnAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "") : base(cellExpression, outerCellExpression, alias) { } protected bool Equals(CountColumnAggregate other) { return base.Equals(other); } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; if (obj.GetType() != GetType()) return false; return Equals((CountColumnAggregate)obj); } public override int GetHashCode() { return base.GetHashCode(); } protected override object GetValue() { return _count; } protected override void UpdateValue(Cell cell) { if (cell.Value != null) { _count++; } } protected override void ResetAggregate() { _count = 0; } } } |
As we can see, base class iterates over rows and the specific implementation handles logic. That’s all. Other aggregates are pretty straightforward:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
using System; using Model; using QueryLogic.Exceptions; using QueryLogic.Expressions.CellExpressions; namespace QueryLogic.Aggregates { public class AverageAggregate : ColumnAggregate { private bool _isInitialized; private int _count; private double _total; public AverageAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "") : base(cellExpression, outerCellExpression, alias) { } protected bool Equals(SumAggregate other) { return base.Equals(other); } protected override void ResetAggregate() { base.ResetAggregate(); _total = 0; _count = 0; _isInitialized = false; } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; if (obj.GetType() != GetType()) return false; return Equals((AverageAggregate)obj); } public override int GetHashCode() { return base.GetHashCode(); } protected override void UpdateValue(Cell cell) { if (cell.Value == null) { return; } UpdateValueOrThrow(cell); _isInitialized = true; } private void UpdateValueOrThrow(Cell cell) { ThrowIfNotDouble(cell); _total += Convert.ToDouble(cell.Value); _count++; } private static void ThrowIfNotDouble(Cell cell) { TypeCode actualTypeCode = cell.GetTypeCode(); if (actualTypeCode != TypeCode.Double) { throw new InvalidCellTypeException(TypeCode.Double, actualTypeCode); } } protected override object GetValue() { return _isInitialized ? (object)(_total / _count) : null; } } }< |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
using System; using Model; using QueryLogic.Exceptions; using QueryLogic.Expressions.CellExpressions; namespace QueryLogic.Aggregates { public class MaximumAggregate : ColumnAggregate { private DateTime _currentDateTime; private double _currentDouble; private TypeCode _currentTypeCode; private bool _isInitialized; private TypeCode? _storedTypeCode; public MaximumAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "") : base(cellExpression, outerCellExpression, alias) { } protected bool Equals(SumAggregate other) { return base.Equals(other); } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; if (obj.GetType() != GetType()) return false; return Equals((MaximumAggregate)obj); } public override int GetHashCode() { return base.GetHashCode(); } protected override void UpdateValue(Cell cell) { if (cell.Value == null) { return; } _currentTypeCode = cell.GetTypeCode(); if (_isInitialized) { UpdateValueOrThrow(cell); } else { UpdateWithFirstValueOrThrow(cell); } _isInitialized = true; } private void UpdateWithFirstValueOrThrow(Cell cell) { ThrowIfIncorrectType(); if (_currentTypeCode == TypeCode.Double) { _currentDouble = Convert.ToDouble(cell.Value); } else if (_currentTypeCode == TypeCode.DateTime) { DateTime actualDateTime = Convert.ToDateTime(cell.Value); _currentDateTime = actualDateTime; } _storedTypeCode = _currentTypeCode; } private void UpdateValueOrThrow(Cell cell) { ThrowIfIncorrectType(); if (_currentTypeCode == TypeCode.Double) { _currentDouble = Math.Max(_currentDouble, Convert.ToDouble(cell.Value)); } else if (_currentTypeCode == TypeCode.DateTime) { DateTime actualDateTime = Convert.ToDateTime(cell.Value); _currentDateTime = _currentDateTime > actualDateTime ? _currentDateTime : actualDateTime; } } private void ThrowIfIncorrectType() { if ((_currentTypeCode != TypeCode.Double && _currentTypeCode != TypeCode.DateTime) || (_storedTypeCode != null && _currentTypeCode != _storedTypeCode)) { throw new InvalidCellTypeException(_currentTypeCode); } } protected override object GetValue() { return _isInitialized ? (_currentTypeCode == TypeCode.Double ? (object)_currentDouble : _currentDateTime) : null; } protected override void ResetAggregate() { base.ResetAggregate(); _isInitialized = false; _storedTypeCode = null; } } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
using System; using Model; using QueryLogic.Exceptions; using QueryLogic.Expressions.CellExpressions; namespace QueryLogic.Aggregates { public class MinimumAggregate : ColumnAggregate { private DateTime _currentDateTime; private double _currentDouble; private TypeCode _currentTypeCode; private bool _isInitialized; private TypeCode? _storedTypeCode; public MinimumAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "") : base(cellExpression, outerCellExpression, alias) { } protected bool Equals(SumAggregate other) { return base.Equals(other); } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; if (obj.GetType() != GetType()) return false; return Equals((MinimumAggregate)obj); } public override int GetHashCode() { return base.GetHashCode(); } protected override void UpdateValue(Cell cell) { if (cell.Value == null) { return; } _currentTypeCode = cell.GetTypeCode(); if (_isInitialized) { UpdateValueOrThrow(cell); } else { UpdateWithFirstValueOrThrow(cell); } _isInitialized = true; } private void UpdateWithFirstValueOrThrow(Cell cell) { ThrowIfIncorrectType(); if (_currentTypeCode == TypeCode.Double) { _currentDouble = Convert.ToDouble(cell.Value); } else if (_currentTypeCode == TypeCode.DateTime) { DateTime actualDateTime = Convert.ToDateTime(cell.Value); _currentDateTime = actualDateTime; } _storedTypeCode = _currentTypeCode; } private void UpdateValueOrThrow(Cell cell) { ThrowIfIncorrectType(); if (_currentTypeCode == TypeCode.Double) { _currentDouble = Math.Min(_currentDouble, Convert.ToDouble(cell.Value)); } else if (_currentTypeCode == TypeCode.DateTime) { DateTime actualDateTime = Convert.ToDateTime(cell.Value); _currentDateTime = _currentDateTime < actualDateTime ? _currentDateTime : actualDateTime; } } private void ThrowIfIncorrectType() { if ((_currentTypeCode != TypeCode.Double && _currentTypeCode != TypeCode.DateTime) || (_storedTypeCode != null && _currentTypeCode != _storedTypeCode)) { throw new InvalidCellTypeException(_currentTypeCode); } } protected override object GetValue() { return _isInitialized ? (_currentTypeCode == TypeCode.Double ? (object)_currentDouble : _currentDateTime) : null; } protected override void ResetAggregate() { base.ResetAggregate(); _isInitialized = false; _storedTypeCode = null; } } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
using System; using Model; using QueryLogic.Exceptions; using QueryLogic.Expressions.CellExpressions; namespace QueryLogic.Aggregates { public class SumAggregate : ColumnAggregate { private bool _isInitialized; private double _total; public SumAggregate(ICellExpression cellExpression, ICellExpression outerCellExpression, string alias = "") : base(cellExpression, outerCellExpression, alias) { } protected bool Equals(SumAggregate other) { return base.Equals(other); } protected override void ResetAggregate() { base.ResetAggregate(); _total = 0; _isInitialized = false; } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; if (obj.GetType() != GetType()) return false; return Equals((SumAggregate)obj); } public override int GetHashCode() { return base.GetHashCode(); } protected override void UpdateValue(Cell cell) { if (cell.Value == null) { return; } UpdateValueOrThrow(cell); _isInitialized = true; } private void UpdateValueOrThrow(Cell cell) { ThrowIfNotDouble(cell); _total += Convert.ToDouble(cell.Value); } private static void ThrowIfNotDouble(Cell cell) { TypeCode actualTypeCode = cell.GetTypeCode(); if (actualTypeCode != TypeCode.Double) { throw new InvalidCellTypeException(TypeCode.Double, actualTypeCode); } } protected override object GetValue() { return _isInitialized ? (object)_total : null; } } } |
And that’s all.