##はじめに
先日業務にて表題の件について調べたので共有します。
本記事は LINQ to Entities にて、テーブルデータをグルーピングしたのち、グループごとに列の最大値を持つレコードを抽出するという内容となっています。
##本題
例えば下の表のように元テーブルのデータをName列でグルーピングし、グループごと(Nameごと)にScore列の最大値を持つレコードを抽出したい場合を想定します。
####元テーブル
| Id | Name | Score | Age |
|---|---|---|---|
| 1 | taro | 60 | 20 |
| 2 | hanako | 90 | 19 |
| 3 | hanako | 50 | 19 |
| 4 | taro | 100 | 20 |
| 5 | jiro | 70 | 22 |
####抽出したいデータ
| Name | Score | Age |
|---|---|---|
| hanako | 90 | 19 |
| taro | 100 | 20 |
| jiro | 70 | 22 |
###SQL Server で書いた場合
サブクエリではGroupBy句によりName列でグルーピングし、Max関数によりグループごとのScore列の最大値を取得します。
メインクエリではサブクエリで取得したScore値を持つレコードを抽出します。
Select Name, Score, Age From Table1 As T1 /* メインクエリ */
Where Score = (
Select Max(Score) From Table1 /* サブクエリ */
Group By Name Having Name = T1.Name
);
###LINQ to Entities で書いた場合（１）
2～5行目のgroup句によりName列とAge列でグルーピングし、into句によりグルーピング結果を G1 に格納します。
6～9行目のselect句によりName列、Score列、Age列を持つ新しいオブジェクトを作成します。
なお、7, 9行目のように TKey を指定することで TElement にアクセスすることができます。
8行目のScore列にはサブクエリにて取得した、グループごとにScore列の最大値を持つレコードを格納します。
※9行目のselect句ではAge列を使用したいのでグルーピングのKeyにAge列を指定しました。
1: List<T> record = (from T1 in db.Table1
2: group T1 by new {
3: NAME = T1.Name,
4: AGE = T1.Age
5: } into G1
6: select new {
7: Name = G1.Key.NAME,
8: Score = (from T2 in G1 select T2.Score).Max(),
9: Age = G1.Key.AGE
10: }).ToList();
###LINQ to Entities で書いた場合（２）
2行目のgroup句によりName列でグルーピングした結果を G1 に格納しておきます。
7行目のlet句によりサブクエリの抽出結果を範囲変数itemに格納します。
なお、サブクエリでは G1 ごとにScore列の最大値を持つレコードを抽出しています。
8行目のselect句では範囲変数itemを指定しているため、元々のデータ構造を保持した状態で対象のデータを取得することができます。
Id列などもまとめて取得できるのでこちらの方が実用的かもしれませんね。
1: var group1 = from T1 in db.Table1
2: group T1 by T1.Name
3: into G1
4: select G1;
5:
6: List<T> record = (from G1 in group1
7: let item = (from itm in G1 where itm.Score == (from sub in G1.Score).Max() select itm)
8: select item).ToList();
9:
10:
####補足
group句の戻り値は IGrouping<TKey, TElement> のオブジェクトとなります。
select句の戻り値は IQueryable<IGrouping<TKey, TElement>> のオブジェクトとなります。
ToListメソッドの戻り値は List<T> のオブジェクトとなります。
####ちなみに
group句のKey要素にName列のみを指定した場合、TKeyを指定してアクセスできるTElementはName列のみとなります。
また、IGrouping<TKey, TElement> のオブジェクトは基本的に二重リストで実現されているため、各グループのアイテムにアクセスするには二重ループによって反復処理する必要があります。
※以下のプログラムではデータセットのグルーピングのみを実装しています。
1: var record = from T1 in db.Table1
2: group T1 by T1.Name
3: into G1
4: select G1;
5:
6: foreach(var itemKey in record)
7: {
8: foreach(var item in itemKey)
9: {
10: Console.WriteLine("{0} {1} {2}", item.Name, item.Score, item.Age);
11: // item.Name は itemKey.key としてもアクセスできます
12: }
13: }
14:
15: // OutPut
16: // taro 60 20
17: // hanako 90 19
18: // hanako 50 19
19: // taro 100 20
20: // jiro 70 22
##さいごに
今回の記事内容に対して至らない点がありましたら、ご指摘いただけると幸いです。
最後までお読みいただきありがとうございました。
また逢う日まで。
##参考文献
https://docs.microsoft.com/ja-jp/dotnet/csharp/language-reference/keywords/group-clause