How to create a GroupBy function that is better aligned with relational algebra?

Question

I want a GroupBy function that behaves more in alignment with relational algebra principles. I can get what I want if I do it in 2 steps:

Preparation:

data = {<|"A" -> 1, "B" -> 2, "C" -> 10|>, <|"A" -> 1, "B" -> 2, 
    "C" -> 20|>, <|"A" -> 1, "B" -> 3, "C" -> 30|>, <|"A" -> 2, 
    "B" -> 4, "C" -> 40|>, <|"A" -> 2, "B" -> 4, "C" -> 50|>};

ds = Dataset[data];

Step 1:

groupedData = ds[GroupBy[{#A, #B} &]];

Step 2:

nestedRelationDataset = 
 Dataset@
  KeyValueMap[
   Function[{key, value}, <|"A" -> key[[1]], "B" -> key[[2]], 
     "Nested" -> value|>], groupedData]

Output:

Dataset[{<|A->1,B->2,Nested->{<|A->1,B->2,C->10|>,<|A->1,B->2,C->20|>}|>,<|A->1,B->3,Nested->{<|A->1,B->3,C->30|>}|>,<|A->2,B->4,Nested->{<|A->2,B->4,C->40|>,<|A->2,B->4,C->50|>}|>}]

But when I try to combine them, result is nonsensical:

relationalGroupBy[ds_Dataset, groupCols_List] := 
 Module[{groupedData, nestedRelationDataset},
  groupedData = ds[GroupBy[groupCols]];
  nestedRelationDataset = 
   Dataset@
    KeyValueMap[
     Function[{key, value}, 
      Association[groupCols -> key, "Nested" -> value]], 
     groupedData];
  nestedRelationDataset]

Single Call:

relationalGroupBy[ds, {"A", "B"}]

Output:

Dataset[{<|{A,B}->A(<|A->1,B->2,C->10|>),Nested-><|B(<|A->1,B->2,C->10|>)->{<|A->1,B->2,C->10|>}|>|>,<|{A,B}->A(<|A->1,B->2,C->20|>),Nested-><|B(<|A->1,B->2,C->20|>)->{<|A->1,B->2,C->20|>}|>|>,<|{A,B}->A(<|A->1,B->3,C->30|>),Nested-><|B(<|A->1,B->3,C->30|>)->{<|A->1,B->3,C->30|>}|>|>,<|{A,B}->A(<|A->2,B->4,C->40|>),Nested-><|B(<|A->2,B->4,C->40|>)->{<|A->2,B->4,C->40|>}|>|>,<|{A,B}->A(<|A->2,B->4,C->50|>),Nested-><|B(<|A->2,B->4,C->50|>)->{<|A->2,B->4,C->50|>}|>|>}]

What am I doing wrong?

---

Update. This:

relationalGroupBy[ds_Dataset, grouper_Function, groupCols_List] := 
 Module[{groupedData, nestedRelationDataset}, 
  groupedData = ds[GroupBy[grouper]];
  nestedRelationDataset = 
   Dataset@
    KeyValueMap[
     Function[{key, value}, 
      Association[groupCols -> key, "Nested" -> value]], 
     groupedData];
  nestedRelationDataset]

with this:

relationalGroupBy[ds, {#A, #B} &, {"A", "B"}]

fails too:

Dataset[{<|{A,B}->{1,2},Nested->{<|A->1,B->2,C->10|>,<|A->1,B->2,C->20|>}|>,<|{A,B}->{1,3},Nested->{<|A->1,B->3,C->30|>}|>,<|{A,B}->{2,4},Nested->{<|A->2,B->4,C->40|>,<|A->2,B->4,C->50|>}|>}]

Answer 1

You can use RightComposition. Well, you can also use Composition but I prefer the former for Query which is what you are using in Dataset.

Your set of functions can work with a few tweaks.

ClearAll[relationalGroupBy]
relationalGroupBy[ds_Dataset, groupCols_List] :=
 ds[
  GroupBy[Evaluate[Slot /@ groupCols] &]/*
   KeyValueMap[{Sequence @@ Thread[groupCols -> #], "Nested" -> #2} &]/*
   Map[Association]
  ]

Then with ds as in OP.

relationalGroupBy[ds, {"A", "B"}] // Normal

{ <|"A" -> 1, "B" -> 2, 
   "Nested" -> {<|"A" -> 1, "B" -> 2, "C" -> 10|>, <|"A" -> 1, "B" -> 2, "C" -> 20|>}|>
 ,<|"A" -> 1, "B" -> 3, 
   "Nested" -> {<|"A" -> 1, "B" -> 3, "C" -> 30|>}|>
 ,<|"A" -> 2, "B" -> 4, 
  "Nested" -> {<|"A" -> 2, "B" -> 4, "C" -> 40|>, <|"A" -> 2, "B" -> 4, "C" -> 50|>}|> }

I used Postfix and Normal to unpack the Dataset for the post.

Hope this helps.

Answer 2

This is probably a bit self-serving, but based on the comments I think there are some relevant alternatives to explore. The OP, in a comment, linked to a grouping operator described by C.J. Date. However, Date's grouping operator doesn't repeat all of the original columns within the relations that become the values in the new group column. I think this is a reasonable semantic, because it allows futher "natural" operations on these relational values that the inclusion of the original columns would impede. In effect, we're picking a key for the new relation, and having that key be duplicated in the grouped value (a relation itself) would usually constitute a functional dependency (I mean, maybe the semantic context would make this okay, but I think in normal circumstances this would end up being denormalized).

With that preamble, here is an implementation that I think is closer to the spirit of Date's grouping operator:

groupRelationally[ds_Dataset, cols_List] :=
  With[
    {keyGroup = Thread[cols -> (Slot /@ cols)]},
    ds[All, <|keyGroup -> {"Group" -> KeyDrop[#, cols]}|> &][Merge[Identity]][KeyValueMap[<|#1, Merge[Identity][#2]|> &]]]

Someone with more proficienty with Dataset might be able to simplify this. Also, "Group" is a bit lazy, but I didn't want to obfuscate the code just to create a better column name. Anyawy, let's compare this with the OP's original expectation:

original expectation

relationalGroupBy[ds, {"A", "B"}]

variant aligned with Date's grouping operator

groupRelationally[ds, {"A", "B"}]