T-SQL - OVER句のROWS句のサンプル

OVER句のROWS句がすごいなと思ったので、少し整理していくつかサンプルを残しておきます。

OVER 句 (Transact-SQL)

OVER句を使うと行をグルーピングしたり、行に順位付けをしたり、行セットの範囲を指定したりといったことができます。

OVER句の括弧の中には次にようにPARTITION BY句、ORDER BY句、ROWS句を書きますが、今回はグルーピングするためのPARTITION BY句は使わず、集約関数とあわせて使うORDER BY句、ROWS句を見ていきたいと思います。

OVER(
    PARTITION BY カラム名
    ORDER BY カラム名
    ROWS(RANGE) なんとか
)

ROWS句

ROWS句は結果セットの範囲を指定する機能です。現在の行からみた前後の行の範囲を指定します。おそらくこの範囲のことを「ウィンドウ」と言うのかなと。

次の書き方で行セットをAからBの範囲内に絞ります。

rows between A and B

AとBの書き方としては5パターンあります。

-- 最初の行
unbounded preceding

-- 現在の行からみてn行前の行
n preceding

-- 現在の行
current row

-- 現在の行からみてn行後の行
n following

-- 最後の行
unbounded following

AとBを埋めていくつかROWS句を書いてみます。

-- 最初の行から現在の行まで
rows between unbounded preceding and current row

-- 1つ前の行から1つの後の行まで
rows between 1 preceding and 1 following

-- 1つ前の行から1つ前の行まで(つまり1つ前の行)
rows between 1 preceding and 1 preceding

ROWS句を使ったサンプル

ROWS句を使ったクエリを書いてみます。まずは月別の売上みたいなテストデータがあるとします。

drop table if exists #Sale;

select *
into #Sale
from (values
    (N'201610', 100),
    (N'201611', 200),
    (N'201612', 300),
    (N'201701', 200),
    (N'201702', 400),
    (N'201703', 200)) as Src([Date], Sale);

select *
from #Sale
order by [Date];

/*
Date   Sale
------ -----------
201610 100
201611 200
201612 300
201701 200
201702 400
201703 200
*/

移動平均を求める

例えば直近3ヶ月間の平均といったものを移動平均と言うようです。

ROWS句に2行前の行から現在の行を指定して、直近3ヶ月の移動平均を計算してみます。わかりやすいように直近3ヶ月の合計も計算しておきます。

-- 移動平均
select *,
    sum(Sale) over(
        order by [Date]
        rows between 2 preceding and current row
    ) as 直近3ヶ月の合計,
    avg(Sale) over(
        order by [Date]
        rows between 2 preceding and current row
    ) as 直近3ヶ月の移動平均
from #Sale
order by [Date];

/*
Date   Sale        直近3ヶ月の合計    直近3ヶ月の移動平均
------ ----------- ----------- -----------
201610 100         100         100
201611 200         300         150
201612 300         600         200
201701 200         700         233
201702 400         900         300
201703 200         800         266
*/

1行目(Dateが201610)は前月と前々月の行がないので、合計も平均も現在の行の値そのままになっています。2行目(Dateが201611)は前々月の行がないので2ヶ月分の合計と平均になっています。

累計を求める

最初の行からその行までの累計を計算してみます。

-- 累計
select *,
    sum(Sale) over(
        order by [Date]
        rows between unbounded preceding and current row
    ) as 累計
from #Sale
order by [Date];

/*
Date   Sale        累計
------ ----------- -----------
201610 100         100
201611 200         300
201612 300         600
201701 200         800
201702 400         1200
201703 200         1400
*/

実はこれ数年前に試しました。

ichiroku11.hatenablog.jp

このとき書いたクエリはこのROWS句を省略したようなものでした。(このときはROWS句をよく理解していなかったような気が・・・。)

別の行と比較する

ROWS句では1つ前の1行、1つ後の1行というようにピンポイントで行を指定できるので、ある行と現在の行を比較することもできます。1つの前の行と比較して、前月からの増減を計算してみます。わかりやすいように前月のSaleの値も取得しています。

--- 前月からの増減
select *,
    sum(Sale) over(
        order by [Date]
        rows between 1 preceding and 1 preceding
    ) as 前月のSale,
    Sale - sum(Sale) over(
        order by [Date]
        rows between 1 preceding and 1 preceding
    ) as 前月からの増減
from #Sale
order by [Date];
/*
Date   Sale        前月のSale     前月からの増減
------ ----------- ----------- -----------
201610 100         NULL        NULL
201611 200         100         100
201612 300         200         100
201701 200         300         -100
201702 400         200         200
201703 200         400         -200
*/

1行目(Dateが201610)は前月の行がないので前月の値がNULLになっています。

ROWS句すごいなと。

Getterのみ自動実装プロパティの初期化子のメモ

うっかりやってしまいそうだなと思ったのでサンプルを残しておきます。

C# 6.0からの機能に

  • 自動実装プロパティの初期化子(Getterのみの自動実装プロパティ)
  • ラムダ式本体によるプロパティの記述

があります。

使う側からするとどちらも同じGetterプロパティですが、動きは微妙に違います。ちゃんと理解して使いわけたいと思います。

というより初期化するつもりでプロパティ内でメソッドを呼び出す場合は、うっかりラムダ式を書かないように。

class Sample {
    private static int GetValue([CallerMemberName]string caller = null) {
        // どのプロパティから呼ばれたか
        Console.WriteLine($"#{nameof(GetValue)} from {caller}");
        return 1;   // 値に意味なし
    }

    public Sample() {
        Console.WriteLine($"#{nameof(Sample)} Constructor");
    }

    // 自動実装プロパティの初期化子
    public int Value1 { get; } = GetValue();

    // ラムダ式本体によるプロパティ
    public int Value2 => GetValue();
}

class Program {
    static void Main(string[] args) {
        // Value1プロパティのGetValueメソッドは、
        // newしたタイミングでコンストラクタ本体より先に実行される
        Console.WriteLine($"new {nameof(Sample)}()");
        var sample = new Sample();
        // 結果:
        //new Sample()
        //#GetValue from Value1
        //#Sample Constructor

        // Value1プロパティを参照してもGetValueメソッドは実行されない
        Console.WriteLine($"{nameof(sample.Value1)}");
        var value1 = sample.Value1;
        // 結果:
        //Value1

        // Value2プロパティを参照するごとにGetValueメソッドが実行される
        Console.WriteLine($"{nameof(sample.Value2)}");
        var value2 = sample.Value2;
        // 結果:
        //Value2
        //#GetValue from Value2
    }
}

Path.GetDirectoryNameでフォルダパスを取得

パス文字列からフォルダパスを取得したいときはPath.GetDirectoryNameを使います。

Path.GetDirectoryName メソッド (String) (System.IO)

このメソッド名だとフォルダパスじゃなくてフォルダ名を取得できる感じが・・・というのは置いておいて。

上記にもサンプルがのっていますが、他にもいくつかペタっとしておきます。

// よくある絶対パス
Console.WriteLine(Path.GetDirectoryName(@"c:\folder\file.txt"));
// c:\folder

// 区切り文字で終わっているパス
// 先頭から最後の区切り文字まで(区切り文字は含まない)の文字列を取得する
Console.WriteLine(Path.GetDirectoryName(@"c:\folder1\folder2\"));
// c:\folder1\folder2

// 区切り文字は"\"でも"/"でも大丈夫
Console.WriteLine(Path.GetDirectoryName(@"c:/folder/file.txt"));
// c:\folder

// 相対パスでも大丈夫みたい
Console.WriteLine(Path.GetDirectoryName(@"folder\file.txt"));
// folder

// 相対パスその2
Console.WriteLine(Path.GetDirectoryName(@"..\..\file.txt"));
// ..\..

// ルート
Console.WriteLine(Path.GetDirectoryName(@"c:\") == null ? "null" : "nullではない");
// null

// ルートその2
Console.WriteLine(Path.GetDirectoryName(@"\\server\") == null ? "null" : "nullではない");
// null

Path.GetDirectoryNameはパス文字列の先頭から最後の区切り文字まで(区切り文字は含まない)の文字列を取得するメソッドということで。ただしパス文字列がルートの場合はnullになると。

ASP.NET MVC - 部分ビューにあるinput要素のname属性にプレフィックスを付ける

探していたことはまさにコレでした。

stackoverflow.com

TemplateInfoもよくわかっていなかったので真似してサンプルを書いてみました。

やりたいこと

まずはやりたいことを整理します。

次のようにAddressクラスのプロパティを2つ持つモデル、SampleInputModelがあるとします。コントローラのアクションでこのモデルをバインドしたいとします。

// 住所氏名
public class Address {
    [Display(Name = "住所")]
    public string Place { get; set; }

    [Display(Name = "名前")]
    public string Name { get; set; }
}

// 入力モデル
// Addressクラスを2つ持つ
public class SampleInputModel {
    [Display(Name = "送付先")]
    public Address ToAddress { get; set; }

    [Display(Name = "送付元")]
    public Address FromAddress { get; set; }
}

そしてAddressクラス用に部分ビューを用意したいとします。(このサンプルだと部分ビューにするまでも・・・という気がしますが、モデルもビューのマークアップももっと複雑だという体でお願いします。)

@* _Address.cshtml *@
@* Addressクラス用の部分ビュー *@

@model Address

<div>@Html.LabelFor(model => model.Place):@Html.TextBoxFor(model => model.Place)</div>
<div>@Html.LabelFor(model => model.Name):@Html.TextBoxFor(model => model.Name)</div>

RenderPartialメソッドを使って部分ビューを呼び出しますが、input要素のname属性が重複してしまいます。

<form action="@Url.Action()" method="post">
    @Html.LabelFor(model => model.ToAddress)
    @{
        Html.RenderPartial("_Address", Model.ToAddress);
    }
    <hr />
    @Html.LabelFor(model => model.FromAddress)
    @{
        Html.RenderPartial("_Address", Model.FromAddress);
    }
    <hr />
    <button type="submit">保存</button>
</form>

<!-- 生成されるhtml -->
<!-- input要素のname属性が重複している -->
<form action="/" method="post">
    <label for="ToAddress">送付先</label>
    <div><label for="Place">住所</label><input id="Place" name="Place" type="text" value="" /></div>
    <div><label for="Name">名前</label><input id="Name" name="Name" type="text" value="" /></div>
    <hr />
    <label for="FromAddress">送付元</label>
    <div><label for="Place">住所</label><input id="Place" name="Place" type="text" value="" /></div>
    <div><label for="Name">名前</label><input id="Name" name="Name" type="text" value="" /></div>
    <hr />
    <button type="submit">保存</button>
</form>

これではモデルにうまくバインドできません。id属性も重複していてダメですし。name属性をToAddress.NameFromAddress.Nameといった感じにする必要があります。

解決するには

モデルにバインドできるようにname属性を設定するには、TemplateInfo.HtmlFieldPrefixプロパティを使います。

このプロパティはTemplateInfo.GetFullHtmlFieldメソッド内で使われています。TemplateInfo.GetFullHtmlFieldメソッドはTextBoxForといったHtmlHelperの拡張メソッド内でname属性を作るのに使われています。

ということでTemplateInfo.HtmlFieldPrefixプロパティを使うRenderPartialFor拡張メソッドを書いてみます。

public static class HtmlHelperExtensions {
    // RenderPartialのラムダ式版
    public static void RenderPartialFor<TModel, TProperty>(
        this HtmlHelper<TModel> htmlHelper,
        string partialViewName,
        Expression<Func<TModel, TProperty>> expression) {

        // ラムダ式からモデルのメタデータを取得して、
        // さらにメタデータからモデルを取得する
        var metadata = ModelMetadata.FromLambdaExpression(expression, htmlHelper.ViewData);
        var model = metadata.Model;

        // TemplateInfoは、TextBoxForなどのHtmlHelperメソッドの拡張メソッドで
        // name属性を取得するのに使っているクラス
        var templateInfo = new TemplateInfo {
            // ラムダ式から「プロパティをドットで連結する文字列」を取得して、
            // プレフィックスに指定する
            HtmlFieldPrefix = ExpressionHelper.GetExpressionText(expression),
        };

        // TemplateInfoを差し替えるためにビューデータも作成する
        var viewData = new ViewDataDictionary(htmlHelper.ViewData) {
            TemplateInfo = templateInfo,
        };

        htmlHelper.RenderPartial(partialViewName, model, viewData);
    }
}

ビューでRenderPartialメソッドの代わりにRenderPartialForメソッドを使ってみると。

<form action="@Url.Action("Edit")" method="post">
    @Html.LabelFor(model => model.ToAddress)
    @{
        Html.RenderPartialFor("_Address", model => model.ToAddress);
    }
    <hr />
    @Html.LabelFor(model => model.FromAddress)
    @{
        Html.RenderPartialFor("_Address", model => model.FromAddress);
    }
    <hr />
    <button type="submit">保存</button>
</form>

<!-- 生成されるhtml -->
<form action="/" method="post">
    <label for="ToAddress">送付先</label>
    <div><label for="ToAddress_Place">住所</label><input id="ToAddress_Place" name="ToAddress.Place" type="text" value="" /></div>
    <div><label for="ToAddress_Name">名前</label><input id="ToAddress_Name" name="ToAddress.Name" type="text" value="" /></div>
    <hr />
    <label for="FromAddress">送付元</label>
    <div><label for="FromAddress_Place">住所</label><input id="FromAddress_Place" name="FromAddress.Place" type="text" value="" /></div>
    <div><label for="FromAddress_Name">名前</label><input id="FromAddress_Name" name="FromAddress.Name" type="text" value="" /></div>
    <hr />
    <button type="submit">保存</button>
</form>

name属性がいい感じに出力されています。これでモデルへのバインドもうまくできます。

さすがMVC

.NETでUDPを使って通信する(UdpClientを使う)

前回はTCPを使って通信してみたので今回はUDPを使って通信するサンプルを書いてみました。

ichiroku11.hatenablog.jp

.NETでUDPを使って通信するにはUdpClientを使います。Socketを使う方法はまたそのうち…というフラグ。

UdpClient クラス (System.Net.Sockets)

UDPコネクションレスTCPよりシンプルなプロトコルなので、UdpClientを使ったコードもTCPに比べるとシンプルでわかりやすいかなと思います。TcpListenerのようなクラスもないですし、NetworkStreamも使いません。言ってしまえばバイト配列を送信する、受信するだけです。

クライアント

UDPのサンプルもHTTPのようにクライアントとサーバでリクエストとレスポンスのメッセージをやりとりする動きにしました。まずはクライアントから見ていきましょう。

クライアントの動き

  1. サーバにリクエストを送信
  2. サーバからレスポンスを受信

書くまでもない感じですね。

クライアントのサンプルコード

SendAsyncで一方的にリクエストを送信して、ReceiveAsyncでレスポンスを受信しています。

// クライアント
public class Client<TRequest, TResponse> {
    // 送信先のエンドポイント
    private readonly IPEndPoint _endpoint;

    public Client(IPEndPoint endpoint) {
        _endpoint = endpoint;
    }

    // クライアントを実行する
    // リクエストを送信してレスポンスを受信する
    public async Task<TResponse> SendAsync(TRequest request) {
        // UdpClientを作成するときにはエンドポイントを指定しない
        using (var client = new UdpClient()) {
            // 1. リクエストを送信
            // (送信先のエンドポイントを指定して)
            Console.WriteLine($"Client send {nameof(request)}: {request}");
            var requestBytes = new ObjectConverter<TRequest>().ToByteArray(request);
            await client.SendAsync(requestBytes, requestBytes.Length, _endpoint);

            // 2. レスポンスを受信
            var result = await client.ReceiveAsync();

            // ここで受信した結果のリモートエンドポイントが送信先かをチェックした方がいい気がする

            var responseBytes = result.Buffer;
            var response = new ObjectConverter<TResponse>().FromByteArray(responseBytes);
            Console.WriteLine($"Client receive {nameof(response)}: {response}");

            return response;
        }
    }
}

レスポンスを受信したタイミングで、送信したエンドポイントから返ってきたレスポンスかどうか、UdpReceiveResult.RemoteEndPointを確認した方がいい気がします。上記のサンプルでは書いていませんが。

サーバ

続いてサーバ。

サーバの動き

  1. クライアントからリクエストを受信する
  2. リクエストを処理してレスポンスを作る
  3. クライアントにレスポンスを送信する

サーバのサンプルコード

2と3は複数のクライアントからのリクエストを並列に処理することを考えてTaskを使いました。ただサンプルではTaskを管理していません。投げっぱなしは良くないので管理する必要があるとは思いますが、どうするのがいいのかなと。

// サーバ
public class Server<TRequest, TResponse> {
    // リクエストを受信するエンドポイント
    private readonly IPEndPoint _endpoint;

    public Server(IPEndPoint endpoint) {
        _endpoint = endpoint;
    }

    // サーバを実行する
    public async Task RunAsync(Func<TRequest, TResponse> processor) {
        // リクエストを受信するエンドポイントを指定してUdpClientを作成
        using (var client = new UdpClient(_endpoint)) {
            while (true) {
                // 1. リクエストを受信
                var result = await client.ReceiveAsync();
                var requestBytes = result.Buffer;
                var request = new ObjectConverter<TRequest>().FromByteArray(requestBytes);
                Console.WriteLine($"Server receive {nameof(request)}: {request}");

                var sender = Task.Run(async () => {
                    // 2. リクエストからレスポンスを作成
                    var response = processor(request);

                    // 3. リクエストの送信元にレスポンスを送信
                    Console.WriteLine($"Server send {nameof(response)}: {response}");
                    var responseBytes = new ObjectConverter<TResponse>().ToByteArray(response);
                    await client.SendAsync(responseBytes, responseBytes.Length, result.RemoteEndPoint);
                });

                // Taskの管理やエラー処理は省略
            }
        }
    }
}

クライアントとサーバを実行してみる

そして実行してみると。

class Program {
    // サーバの作成
    public static Server<string, string> Server(IPEndPoint endpoint)
        => new Server<string, string>(endpoint);

    // クライアントの作成
    public static Client<string, string> Client(IPEndPoint endpoint)
        => new Client<string, string>(endpoint);

    // 文字列を並びを反対にする
    private static string Reverse(string original)
        => new string(original.Reverse().ToArray());

    static void Main(string[] args) {
        var endpoint = new IPEndPoint(IPAddress.Loopback, 54321);

        // サーバを実行
        var server = Task.Run(() => Server(endpoint).RunAsync(Reverse));

        // クライアントを実行
        Task.WaitAll(
            Client(endpoint).SendAsync("あいうえお"),
            Client(endpoint).SendAsync("かきくけこ"));

        // サーバのTaskをきれいに終了するにはどうしたらいいのか...
    }
}
// 実行結果
/*
Client send request: あいうえお
Server receive request: あいうえお
Client send request: かきくけこ
Server receive request: かきくけこ
Server send response: おえういあ
Server send response: こけくきか
Client receive response: おえういあ
Client receive response: こけくきか
*/

TCPのサンプルを書いたときもそうでしたが、サーバの終了処理(キャンセル処理?)はどう書くのがいいかまだよく分からずです。

ソース全体はこちら。

.NETでUDP(UdpClient) · GitHub