泥庭

2013年7月7日

SQLWorldの問題をLINQで解いてみる

Filed under: .NET, LINQ, SQL, 勉強会 — タグ: , — yone64 @ 10:41 PM

SQLWorldのお題を、LINQで解いてみようという試み。まぁ基本反則ですけどね。

問題1

よく考えたら、LINQでJoin書いたことないなぁ、と思いつつ。いつも書いてるのはこんな感じで、一度Dictionaryに受けて後はキーで引く感じです。キーが無いときに例外が発生するので、TryGetValueなどを使う必要がありちょいと扱いが面倒ですが、

public static void Solve()
{
    var limit = new DateTime(2013, 4, 1);

    var 得意先dic = Get得意先マスタ().Where(t => !t.削除済み).ToDictionary(t => t.Id, t => t.名前);

    string name;
    var q = Get売上().Where(u => u.売上日 > limit).Select(u =>
        new
        {
            u.売上番号,
            u.売上日,
            得意先名 = 得意先dic.TryGetValue(u.得意先Id, out name) ? name : null,
            u.売上金額
        });

    foreach (var item in q)
    {
        Console.WriteLine(item);
    }
}

せっかくなので、外部結合相当をLINQで書くべく調べてみたところ、MSDNを発見しました。joinとDefaultIfEmptyを使うと良いらしい。早速、それに倣って書いてみます。

var q = from x in Get売上().Where(u => u.売上日 > limit)
        join y in Get得意先マスタ().Where(t => !t.削除済み) on x.得意先Id equals y.Id into z
        from a in z.DefaultIfEmpty()
        select new { x.売上番号, x.売上日, 得意先名 = a != null ? a.名前 : null, x.売上金額 };

なるほど、こうなるのかぁと思いつつも、でもクエリ構文よりメソッド構文が好きなので置き換え。

var q = Get売上().Where(u => u.売上日 > limit)
    .GroupJoin(Get得意先マスタ().Where(t => !t.削除済み),
            u => u.得意先Id, t => t.Id, (u, et) => new { u, et })
    .SelectMany(a => a.et.DefaultIfEmpty()
        .Select(t => new { a.u.売上番号, a.u.売上日, 得意先名 = t != null ? t.名前 : null, a.u.売上金額 }));

あと、Idが得意先の主キーであることを利用すると、SelectMany→DefaultIfEmptyの流れが、もう少し短くなりますね。

var q = Get売上().Where(u => u.売上日 > limit)
    .GroupJoin(Get得意先マスタ().Where(t => !t.削除済み),
            u => u.得意先Id, t => t.Id,
            (u, et) => new { u.売上番号, u.売上日, 得意先名 = et.Select(t => t.名前).FirstOrDefault(), u.売上金額 });

FirstOrDefaultより前にSelectすると、「t == null ? null : t.名前」っていう三項演算子が不要になるのが好きだったりします。と、ここまで書いたけど、一度ToDictionaryするのが分かりやすいよねぇ。

問題2

問題文の内容を素直にLINQで書くと、下のようになるような気がします。

public static void Solve()
{
    var q = Get所属()
        .Where(s => Get所属().Any(a => s.PK != a.PK && s.選手 == a.選手 && s.在籍期間From <= a.在籍期間To && s.在籍期間To >= a.在籍期間From))
        .Select(s => new { s.PK, s.チーム, s.選手, s.在籍期間From, s.在籍期間To });

    foreach (var item in q)
    {
        Console.WriteLine(item);
    }

}

これでも、答えは出るんですけど、Anyの中で回す範囲が大きいのが若干気になります。そこで、一度Lookup。

var temp = Get所属().ToLookup(s => s.選手);
var q = Get所属()
    .Where(s => temp[s.選手].Any(a => s.PK != a.PK && s.在籍期間From <= a.在籍期間To && s.在籍期間To >= a.在籍期間From))
    .Select(s => new { s.PK, s.チーム, s.選手, s.在籍期間From, s.在籍期間To });

こんな感じ。同じ選手という条件を毎度チェックしなくて良い分ちょっとだけこっちの方がよさそうですね。

この辺の結合は、SQLの場合オプティマイザが勝手にやるんでしょうけど、LINQの場合はプログラマが考えてあげないといけない分面倒なような。

問題3

LINQの場合、先頭から一行ずつ処理ができるので、面倒なこと考えなくてよいですね。Aggregateで処理して終了。

public static void Solve()
{
    var q = Get試合結果().Aggregate(new { 最大連勝 = 0, 最大連敗 = 0, 現連勝 = 0, 現連敗 = 0 },
        (before, current) =>
            new
            {
                最大連勝 = Math.Max(before.最大連勝, before.現連勝 + current.勝敗値),
                最大連敗 = Math.Max(before.最大連敗, before.現連敗 + 1 - current.勝敗値),
                現連勝 = (before.現連勝 + 1) * current.勝敗値,
                現連敗 = (before.現連敗 + 1) * (1 - current.勝敗値)
            });


    Console.WriteLine("0," + q.最大連敗);
    Console.WriteLine("1," + q.最大連勝);
}

ずるいな。

データとか

データ作成用のコードは置いておくので、チャレンジしたい方はどうぞ。

#region 問題1
static IEnumerable<売上> Get売上()
{
    yield return new 売上 { 売上番号 = "U0001", 売上日 = DateTime.Parse("2013/01/05"), 得意先Id = 1, 売上金額 = 10000 };
    yield return new 売上 { 売上番号 = "U0002", 売上日 = DateTime.Parse("2013/02/01"), 得意先Id = 2, 売上金額 = 20000 };
    yield return new 売上 { 売上番号 = "U0003", 売上日 = DateTime.Parse("2013/02/15"), 得意先Id = 1, 売上金額 = 30000 };
    yield return new 売上 { 売上番号 = "U0004", 売上日 = DateTime.Parse("2013/03/07"), 得意先Id = 3, 売上金額 = 40000 };
    yield return new 売上 { 売上番号 = "U0005", 売上日 = DateTime.Parse("2013/03/18"), 得意先Id = 2, 売上金額 = 50000 };
    yield return new 売上 { 売上番号 = "U0006", 売上日 = DateTime.Parse("2013/04/06"), 得意先Id = 4, 売上金額 = 60000 };
    yield return new 売上 { 売上番号 = "U0007", 売上日 = DateTime.Parse("2013/04/11"), 得意先Id = 1, 売上金額 = 70000 };
    yield return new 売上 { 売上番号 = "U0008", 売上日 = DateTime.Parse("2013/05/01"), 得意先Id = 3, 売上金額 = 80000 };
    yield return new 売上 { 売上番号 = "U0009", 売上日 = DateTime.Parse("2013/06/23"), 得意先Id = 2, 売上金額 = 90000 };
}

static IEnumerable<得意先マスタ> Get得意先マスタ()
{
    yield return new 得意先マスタ { Id = 1, 名前 = "得意先1", Tel = "00-0000-0001", 住所 = "大阪", 削除済み = false };
    yield return new 得意先マスタ { Id = 2, 名前 = "得意先2", Tel = "00-0000-0002", 住所 = "兵庫", 削除済み = false };
    yield return new 得意先マスタ { Id = 3, 名前 = "得意先3 - 廃棄", Tel = "00-0000-0003", 住所 = "三重", 削除済み = true };
    yield return new 得意先マスタ { Id = 4, 名前 = "得意先4", Tel = "00-0000-0004", 住所 = "京都", 削除済み = false };
    yield return new 得意先マスタ { Id = 5, 名前 = "得意先5", Tel = "00-0000-0005", 住所 = "奈良", 削除済み = false };
}
class 売上
{
    public string 売上番号 { get; set; }
    public DateTime 売上日 { get; set; }
    public int 得意先Id { get; set; }
    public int 売上金額 { get; set; }
}
class 得意先マスタ
{
    public int Id { get; set; }
    public string 名前 { get; set; }
    public string Tel { get; set; }
    public string 住所 { get; set; }
    public bool 削除済み { get; set; }
}
#endregion 

#region 問題2
static IEnumerable<所属> Get所属()
{
    yield return new 所属 { PK = 1, チーム = "阪神", 選手 = "和田", 在籍期間From = new DateTime(1985, 4, 1), 在籍期間To = new DateTime(2001, 3, 31) };
    yield return new 所属 { PK = 2, チーム = "広島", 選手 = "金本", 在籍期間From = new DateTime(1992, 4, 1), 在籍期間To = new DateTime(2003, 3, 31) };
    yield return new 所属 { PK = 3, チーム = "阪神", 選手 = "金本", 在籍期間From = new DateTime(2003, 4, 1), 在籍期間To = new DateTime(2012, 3, 31) };
    yield return new 所属 { PK = 4, チーム = "広島", 選手 = "新井", 在籍期間From = new DateTime(1999, 4, 1), 在籍期間To = new DateTime(2008, 3, 31) };
    yield return new 所属 { PK = 5, チーム = "阪神", 選手 = "新井", 在籍期間From = new DateTime(2008, 4, 1), 在籍期間To = DateTime.MaxValue };
    yield return new 所属 { PK = 6, チーム = "阪神", 選手 = "能見", 在籍期間From = new DateTime(2005, 4, 1), 在籍期間To = DateTime.MaxValue };
    yield return new 所属 { PK = 7, チーム = "ロッテ", 選手 = "西岡", 在籍期間From = new DateTime(2003, 4, 1), 在籍期間To = new DateTime(2012, 3, 31) };
    yield return new 所属 { PK = 8, チーム = "MLB", 選手 = "西岡", 在籍期間From = new DateTime(2011, 4, 1), 在籍期間To = new DateTime(2013, 3, 31) };
    yield return new 所属 { PK = 9, チーム = "阪神", 選手 = "西岡", 在籍期間From = new DateTime(2013, 4, 1), 在籍期間To = DateTime.MaxValue };
    yield return new 所属 { PK = 10, チーム = "阪神", 選手 = "井川", 在籍期間From = new DateTime(1998, 4, 1), 在籍期間To = new DateTime(2007, 3, 31) };
    yield return new 所属 { PK = 11, チーム = "MLB", 選手 = "井川", 在籍期間From = new DateTime(2007, 4, 1), 在籍期間To = new DateTime(2012, 3, 31) };
    yield return new 所属 { PK = 12, チーム = "オリックス", 選手 = "井川", 在籍期間From = new DateTime(2012, 4, 1), 在籍期間To = DateTime.MaxValue };
}

class 所属
{
    public int PK { get; set; }
    public string チーム { get; set; }
    public string 選手 { get; set; }
    public DateTime 在籍期間From { get; set; }
    public DateTime 在籍期間To { get; set; }
}
#endregion

#region 問題3
static IEnumerable<試合結果> Get試合結果()
{
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 1), 勝敗値 = 0, 星 = "★" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 3), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 4), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 5), 勝敗値 = 0, 星 = "★" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 6), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 7), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 8), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 10), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 11), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 12), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 14), 勝敗値 = 0, 星 = "★" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 15), 勝敗値 = 0, 星 = "★" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 17), 勝敗値 = 0, 星 = "★" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 18), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 19), 勝敗値 = 1, 星 = "☆" };
    yield return new 試合結果 { 日付 = new DateTime(2013, 4, 20), 勝敗値 = 0, 星 = "★" };
}

class 試合結果
{
    public DateTime 日付 { get; set; }
    public int 勝敗値 { get; set; }
    public string 星 { get; set; }
}
#endregion
広告

コメントする »

まだコメントはありません。

RSS feed for comments on this post. TrackBack URI

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

WordPress.com で無料サイトやブログを作成.

%d人のブロガーが「いいね」をつけました。