U-SQLでLOCF

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

UNBOUNDED PRECEDING + LAST_VALUE を使う？

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

@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

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

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

@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にはない
Oracleにはある
PostgreSQLにはない
MySQLにはない
SparkSQLにはある
HIVE QLにはある

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

@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?

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とかで書けるので、いいですね。

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

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