Edited at

U-SQLでLOCF

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


UNBOUNDED PRECEDING + LAST_VALUE を使う?

exactなソリューションがいくつかあったのですが、1行で書ける、コレを見つけたときは決定打かと思いましたが、引用元をよく読むとこれは無理であることがわかりました。以下スレッドの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ネイティブな回答があります。3段階で中間結果を使った例ですが、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を導入してくれるとありがたいのですが、まあ言語仕様が複雑になってしまうようなら、このままの方が安全なのかもしれませんね。