Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What is going on with this article?
@Masutani

U-SQLでLOCF

More than 1 year has passed since last update.

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

0
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Masutani
データサイエンティストをしています。地理空間情報系の処理を主に扱っています。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
0
Help us understand the problem. What is going on with this article?