SQLxD Part 14 — Grouping – Random IT Utensils

This is the fourteenth 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 grouping. Since we can group in multiple ways, we need to have grouping sets:

using System.Collections.Generic;
using System.Linq;
using Model;

namespace QueryLogic.Grouping
{
    public class GroupingSet
    {
        public GroupingSet(IEnumerable<ColumnHeader> columns)
        {
            Columns = columns;
        }

        public IEnumerable<ColumnHeader> Columns { get; private set; }

        protected bool Equals(GroupingSet other)
        {
            return Columns.SequenceEqual(other.Columns);
        }

        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((GroupingSet)obj);
        }

        public override int GetHashCode()
        {
            return (Columns != null ? Columns.GetHashCode() : 0);
        }
    }
}

using System.Collections.Generic;

using System.Linq;

using Model;

namespace QueryLogic.Grouping

{

public class GroupingSet

{

public GroupingSet(IEnumerable<ColumnHeader> columns)

{

Columns = columns;

}

public IEnumerable<ColumnHeader> Columns { get; private set; }

protected bool Equals(GroupingSet other)

{

return Columns.SequenceEqual(other.Columns);

}

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((GroupingSet)obj);

}

public override int GetHashCode()

{

return (Columns != null ? Columns.GetHashCode() : 0);

}

Grouping set is just a set of columns which we group by. Next comes the operator:

using System.Collections.Generic;
using System.Linq;
using Model;
using QueryLogic.RelationProviding;

namespace QueryLogic.Grouping
{
    public class GroupBy
    {
        public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable<GroupingSet> columnSets)
        {
            SourceRelationProvider = sourceRelationProvider;
            ColumnSets = columnSets;
        }

        public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable<ColumnHeader> columns)
            : this(sourceRelationProvider, new[] { new GroupingSet(columns) })
        {
        }

        public IRelationProvider SourceRelationProvider { get; private set; }
        public IEnumerable<GroupingSet> ColumnSets { get; private set; }

        protected bool Equals(GroupBy other)
        {
            return Equals(SourceRelationProvider, other.SourceRelationProvider) &&
                   ColumnSets.SequenceEqual(other.ColumnSets);
        }

        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((GroupBy)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((SourceRelationProvider != null ? SourceRelationProvider.GetHashCode() : 0) * 397) ^
                       (ColumnSets != null ? ColumnSets.GetHashCode() : 0);
            }
        }

        public IEnumerable<Relation> CreateRelations(Node source)
        {
            return ColumnSets.SelectMany(g => CreateRelation(g, source));
        }

        private IEnumerable<Relation> CreateRelation(GroupingSet groupingSet, Node source)
        {
            IEnumerable<IEnumerable<Row>> rowListsList = new List<IEnumerable<Row>>
            {
                SourceRelationProvider.CreateRelation(source).Rows
            };

            foreach (ColumnHeader column in groupingSet.Columns)
            {
                rowListsList = rowListsList.SelectMany(rowList => rowList.GroupBy(row => row.GetCellValue(column)));
            }

            foreach (var rowsList in rowListsList)
            {
                var result = new Relation();
                result.AddRows(rowsList);
                yield return result;
            }
        }
    }
}

using System.Collections.Generic;

using System.Linq;

using Model;

using QueryLogic.RelationProviding;

namespace QueryLogic.Grouping

{

public class GroupBy

{

public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable<GroupingSet> columnSets)

{

SourceRelationProvider = sourceRelationProvider;

ColumnSets = columnSets;

}

public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable<ColumnHeader> columns)

: this(sourceRelationProvider, new[] { new GroupingSet(columns) })

{

}

public IRelationProvider SourceRelationProvider { get; private set; }

public IEnumerable<GroupingSet> ColumnSets { get; private set; }

protected bool Equals(GroupBy other)

{

return Equals(SourceRelationProvider, other.SourceRelationProvider) &&

ColumnSets.SequenceEqual(other.ColumnSets);

}

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((GroupBy)obj);

}

public override int GetHashCode()

{

unchecked

{

return ((SourceRelationProvider != null ? SourceRelationProvider.GetHashCode() : 0) * 397) ^

(ColumnSets != null ? ColumnSets.GetHashCode() : 0);

}

public IEnumerable<Relation> CreateRelations(Node source)

{

return ColumnSets.SelectMany(g => CreateRelation(g, source));

}

private IEnumerable<Relation> CreateRelation(GroupingSet groupingSet, Node source)

{

IEnumerable<IEnumerable<Row>> rowListsList = new List<IEnumerable<Row>>

{

SourceRelationProvider.CreateRelation(source).Rows

};

foreach (ColumnHeader column in groupingSet.Columns)

{

rowListsList = rowListsList.SelectMany(rowList => rowList.GroupBy(row => row.GetCellValue(column)));

}

foreach (var rowsList in rowListsList)

{

var result = new Relation();

result.AddRows(rowsList);

yield return result;

}

And here are the tests:

using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Grouping;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Grouping
{
    [TestFixture]
    public class GroupByTests
    {
        [Test]
        public void CreateRelation_OneGroupSetPassed_ShouldReturnOneGroup()
        {
            // Arrange
            var expected = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                }),
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                }),
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                })
            });

            var groupBy = new GroupBy(new DummyRelationProvider(expected), new[] { new ColumnHeader("schema", "A") });

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(new[] { expected }, actual);
        }

        [Test]
        public void CreateRelations_ManyColumnsPassed_ShouldGroupByManyColumns()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                firstRow,
                secondRow,
                secondRow,
                thirdRow
            });

            var groupBy = new GroupBy(new DummyRelationProvider(source),
                new[] { new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B") });

            var expected = new[]
            {
                new Relation(new[] {firstRow, firstRow}),
                new Relation(new[] {secondRow, secondRow}),
                new Relation(new[] {thirdRow})
            };

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }

        [Test]
        public void CreateRelations_ManyGroupingSetsPassed_ShouldJoinResultsFromManyGroupingSets()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value1"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                firstRow,
                secondRow,
                secondRow,
                thirdRow
            });

            var groupBy = new GroupBy(new DummyRelationProvider(source), new[]
            {
                new GroupingSet(new[] {new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B")}),
                new GroupingSet(new[] {new ColumnHeader("schema", "A")})
            });

            var expected = new[]
            {
                new Relation(new[] {firstRow, firstRow}),
                new Relation(new[] {secondRow, secondRow}),
                new Relation(new[] {thirdRow}),
                new Relation(new[] {firstRow, firstRow, secondRow, secondRow}),
                new Relation(new[] {thirdRow})
            };

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }

        [Test]
        public void CreateRelations_OneColumnPassed_ShouldGroupByOneColumn()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                firstRow,
                secondRow
            });

            var groupBy = new GroupBy(new DummyRelationProvider(source), new[] { new ColumnHeader("schema", "A") });

            var expected = new[]
            {
                new Relation(new[] {firstRow, firstRow}),
                new Relation(new[] {secondRow})
            };

            // Act
            IEnumerable<Relation> actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

using System.Collections.Generic;

using Model;

using NUnit.Framework;

using QueryLogic.Grouping;

using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Grouping

{

[TestFixture]

public class GroupByTests

{

[Test]

public void CreateRelation_OneGroupSetPassed_ShouldReturnOneGroup()

{

// Arrange

var expected = new Relation(new[]

{

new Row(new[]

{

new Cell("schema", "A", "value")

}),

new Row(new[]

{

new Cell("schema", "A", "value")

}),

new Row(new[]

{

new Cell("schema", "A", "value")

})

});

var groupBy = new GroupBy(new DummyRelationProvider(expected), new[] { new ColumnHeader("schema", "A") });

// Act

IEnumerable<Relation> actual = groupBy.CreateRelations(null);

// Assert

CollectionAssert.AreEquivalent(new[] { expected }, actual);

}

[Test]

public void CreateRelations_ManyColumnsPassed_ShouldGroupByManyColumns()

{

// Arrange

var firstRow = new Row(new[]

{

new Cell("schema", "A", "value1"),

new Cell("schema", "B", "value1")

});

var secondRow = new Row(new[]

{

new Cell("schema", "A", "value1"),

new Cell("schema", "B", "value2")

});

var thirdRow = new Row(new[]

{

new Cell("schema", "A", "value2"),

new Cell("schema", "B", "value2")

});

var source = new Relation(new[]

{

firstRow,

secondRow,

thirdRow

});

var groupBy = new GroupBy(new DummyRelationProvider(source),

new[] { new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B") });

var expected = new[]

{

new Relation(new[] {firstRow, firstRow}),

new Relation(new[] {secondRow, secondRow}),

new Relation(new[] {thirdRow})

};

// Act

IEnumerable<Relation> actual = groupBy.CreateRelations(null);

// Assert

CollectionAssert.AreEquivalent(expected, actual);

}

[Test]

public void CreateRelations_ManyGroupingSetsPassed_ShouldJoinResultsFromManyGroupingSets()

{

// Arrange

var firstRow = new Row(new[]

{

new Cell("schema", "A", "value1"),

new Cell("schema", "B", "value1")

});

var secondRow = new Row(new[]

{

new Cell("schema", "A", "value1"),

new Cell("schema", "B", "value2")

});

var thirdRow = new Row(new[]

{

new Cell("schema", "A", "value2"),

new Cell("schema", "B", "value2")

});

var source = new Relation(new[]

{

firstRow,

secondRow,

thirdRow

});

var groupBy = new GroupBy(new DummyRelationProvider(source), new[]

{

new GroupingSet(new[] {new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B")}),

new GroupingSet(new[] {new ColumnHeader("schema", "A")})

});

var expected = new[]

{

new Relation(new[] {firstRow, firstRow}),

new Relation(new[] {secondRow, secondRow}),

new Relation(new[] {thirdRow}),

new Relation(new[] {firstRow, firstRow, secondRow, secondRow}),

new Relation(new[] {thirdRow})

};

// Act

IEnumerable<Relation> actual = groupBy.CreateRelations(null);

// Assert

CollectionAssert.AreEquivalent(expected, actual);

}

[Test]

public void CreateRelations_OneColumnPassed_ShouldGroupByOneColumn()

{

// Arrange

var firstRow = new Row(new[]

{

new Cell("schema", "A", "value1")

});

var secondRow = new Row(new[]

{

new Cell("schema", "A", "value2")

});

var source = new Relation(new[]

{

firstRow,

secondRow

});

var groupBy = new GroupBy(new DummyRelationProvider(source), new[] { new ColumnHeader("schema", "A") });

var expected = new[]

{

new Relation(new[] {firstRow, firstRow}),

new Relation(new[] {secondRow})

};

// Act

IEnumerable<Relation> actual = groupBy.CreateRelations(null);

// Assert

CollectionAssert.AreEquivalent(expected, actual);

}