U-SQLでLOCF

U-SQLの場合というか、標準SQLの場合のLOCF処理をトライしてみました。というかStackoverflowの拾い読みですが。

# UNBOUNDED PRECEDING + LAST_VALUE を使う？

exactなソリューションがいくつかあったのですが、１行で書ける、コレを見つけたときは決定打かと思いましたが、引用元をよく読むとこれは無理であることがわかりました。以下スレッドのNabeelさんの回答から：

``` SQL
@data = SELECT A, LAST_VALUE(Col == "" ? null : Col) 
  OVER (ORDER BY Key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col 
  FROM @input;
```

[How to fill a blank cell with the data above using U-SQL](https://stackoverflow.com/questions/44563714/how-to-fill-a-blank-cell-with-the-data-above-using-u-sql)

nullのRowがLAST_VALUEに無視されるなら、これで書けるのですが、そうはなっていないようです。

標準SQLにはウィンドウ関数にignore nulls というオプションがあって、それこそ条件式もいらないんですが、どうも実装されている環境は限られるようです。

``` SQL
@data = SELECT A, LAST_VALUE(Col ignore nulls) 
  OVER (ORDER BY Key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col 
  FROM @input;
```

[T-SQLにはない](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/last-value-transact-sql?view=sql-server-2017)
[Oracleにはある](https://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-4-323606-ja.html)
[PostgreSQLにはない](https://www.postgresql.jp/document/8.4/html/functions-window.html)
[MySQLにはない](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html)
[SparkSQLにはある](https://spark.apache.org/docs/latest/api/scala/index.html#org.apache.spark.sql.functions$@last(columnName:String,ignoreNulls:Boolean):org.apache.spark.sql.Column)
[HIVE QLにはある](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics)

U-SQLにも残念ながらこのオプションはありません。

# U-SQLなりの解法 連続値フラグを使う例

以下にAnton ShelinさんのU-SQLネイティブな回答があります。３段階で中間結果を使った例ですが、aSwitchやaGrpの意味が今一歩わかりませんので、少々気持ちが悪いですね。要するにNULLが連続するクラスタを抽出しそれとその前の非NULL値をセットにしたグループaGrpを作って、FIRST_VALUEで埋めるみたいな感じです。
[How to fill a blank cell with the data above using U-SQL](https://stackoverflow.com/questions/44563714/how-to-fill-a-blank-cell-with-the-data-above-using-u-sql)

``` SQL
@tb1 =
    SELECT Timestamp,
           [a],
           [a] != null && [a] != LEAD([a], 1) OVER(ORDER BY Timestamp ASC) AS aSwitch
    FROM @tb1;
@tb1 =
    SELECT Timestamp,
           [a],
           SUM(aSwitch ? 1 : 0) OVER(ORDER BY Timestamp ASC ROWS UNBOUNDED PRECEDING) AS aGrp
    FROM @tb1;
@tb1 =
    SELECT Timestamp,
           FIRST_VALUE([a]) OVER(PARTITION BY aGrp ORDER BY Timestamp ASC) AS aFilled
    FROM @tb1;
```
# U-SQLなりの解法 UDF REDUCER

以下に同じAntonさんがFinal Solutionを提供しています。Nullをスキップして最後のnon-nullを得るReducerをC# UDFで作っちゃいます。非常に簡易に書けるので、このくらいならしんどくないですね。

[How to get last nonnull value in usql windowing expression?](https://stackoverflow.com/questions/47012707/how-to-get-last-nonnull-value-in-usql-windowing-expression)

``` C#
public class ReplaceNullReducer : IReducer
{
    string lastValue = null;
    public override IEnumerable<IRow> Reduce(IRowset input, IUpdatableRow output)
    {
        foreach (var row in input.Rows)
        {
            var val = row.Get<string>("a");
            if (val != null) lastValue = val;
            output.Set<string>("a", lastValue);
            output.Set<int>("Timestamp", row.Get<int>("Timestamp"));
            yield return output.AsReadOnly();
        }
    } 
} 
```

REDUCERの場合はORDERがPRESORTに、PARTITIONがONになるのですが、それさえ気を付ければ、OVER(PARTITION... ORDER BY...)と同じような感覚で使えます。使い方が、UNBOUNDED PRECEDINGモード限定になっちゃいますが、まあそれ以外は普通にLAGとかで書けるので、いいですね。

``` SQL
@tb1 = REDUCE @tb1 PRESORT [Timestamp] ON device
       PRODUCE [Timestamp] int, [a] string
       USING new DataLakeTest.ReplaceNullReducer();
```

ignore nullを導入してくれるとありがたいのですが、まあ言語仕様が複雑になってしまうようなら、このままの方が安全なのかもしれませんね。