Treasure Data でハッシュ関数計算(MD5/SHA1)

諸事情ありまして、先日 Treasure Data (Presto) で 文字列のSHA1を計算する必要が発生しました。(滅多に無いことなのですが。)
その時に方法を検証したので紹介します。ついでにMD5のやり方も紹介します。PythonやMySQLでやる場合は、MD5もSHA1もたいして変わらないのですが、Treasure Data はこの二つは事情がかなり違います。その辺も書いていきたいと思います。

まず、他のDBではどうなっているかということで、MySQLの場合を見ていきます。(僕はAWS RDSのMySQL互換のインスタンスで試してます。)
MySQLには md5 と sha1 という関数が実装されており、これらが「文字列」を受け取り、結果を「16進法表記の文字列」で返してくれます。コメントでつけてるのは結果です。
ドキュメントはこちら: 12.13 暗号化関数と圧縮関数

SELECT
    MD5('abcde'), -- ab56b4d92b40713acc5af89985d4b786
    SHA1('abcde') -- 03de6c570bfe24bfc328ccd7ca46b76eadaf4334

文字列をそのまま受け取ってくれるし、結果も文字列なので簡単ですね。

Pythonで行う方法は昔記事にしました。
参考: pythonでMD5
文字列をバイト型に変換して、専用の関数を呼び出し、結果がバイト型なのでそれを文字列に変換するのでしたね。

さて、いよいよ本題のTreasure Dataです。まず、MD5の方なのですが、実はTD_MD5というUDFが実装されており、MySQLのMD5と同じように動作します。
参考: Supported Presto and TD Functions – Product Documentation

SELECT
    TD_MD5('abcde') -- ab56b4d92b40713acc5af89985d4b786

簡単に使えて結果もMySQLと同じですね。

さて、TD_MD5の存在を知っていたので僕は SHA1についても TD_SHA1が存在すると勘違いしていました。しかし、ドキュメントの先ほどのUDFのページを見ると TD_SHA1はありません。無理矢理書いて実行しても、「Function td_sha1 not registered」と言われます。

では、Treasure Data で SHA1は計算できないのかとも思ったのですが、実はPresto自体に、SHA1という関数が定義さてれいます。(もちろんMD5もあります。)
参考: Binary Functions and Operators — Presto 0.263.1 Documentation

しかし、これつかえばMySQLと同じように動かせる!と思って試したところエラーになりました。

SELECT
    SHA1('abcde')
-- 以下エラーメッセージ
-- Unexpected parameters (varchar(5)) for function sha1. Expected: sha1(varbinary) 

ドキュメントを見ていただけるとわかる通り、PrestoのMD5やSHA1はバイナリを受け取ってバイナリを返します。要するにPythonのハッシュ関数と似たような動きをするのです。

md5(binary) → varbinary
Computes the md5 hash of binary.

sha1(binary) → varbinary
Computes the sha1 hash of binary.

ということで、文字列は受け取ってくれないで事前にキャストしましょう。

SELECT
    SHA1(CAST('abcde' AS VARBINARY)) -- A95sVwv+JL/DKMzXyka3bq2vQzQ=

CASTしてから渡すと無事に動きました。でも結果が違いますね(実は表記方法が違うだけで中身は違わないのですが。) 結果がバイナリで戻り、(たぶん)BASE64表記で帰ってきたようです。

TO_HEXっていうバイナリを16進法表記の文字列に変換する関数もあるので、それも追加します。

SELECT
    TO_HEX(SHA1(CAST('abcde' AS VARBINARY))) -- 03DE6C570BFE24BFC328CCD7CA46B76EADAF4334

MySQLの結果に近づいてきました。ただ、A-Fのアルファベットが大文字になっていますね。LOWERで小文字にしましょう。

SELECT
    LOWER(TO_HEX(SHA1(CAST('abcde' AS VARBINARY)))) -- 03de6c570bfe24bfc328ccd7ca46b76eadaf4334

これでMySQLのSHA1と同じ結果が得られました。
ちなみに、MD5の方もこれと同じ流儀で計算できます。

SELECT
    LOWER(TO_HEX(MD5(CAST('abcde' AS VARBINARY)))) -- ab56b4d92b40713acc5af89985d4b786

どう考えても TD_MD5 一発で済ませる方が簡単ですね。
将来的にTD_SHA1も実装されることを期待したいです。

Pythonの文字列を分割するメソッドたちの紹介

このブログでMeCabを使った形態素解析をするとき、MeCabが出力する文字列を行単位に分割するときはいつもstr.split(“\n”)のような書き方をしていました。しかし、最近Pythonのstrオブジェクトが、splitlines() という行単位で区切る専用のメソッドを持っているのに気づいたのでその紹介です。といっても、splitlines()で改行コードで区切れるぜ、だけだと記事が一瞬で終わってしまうので、ついでにPythonのstr型が持っている3つの分割メソッド[split()/ rsplit()/ splitlines()]をそれぞれ紹介しようと思います。

ドキュメントはこちらのページにあります。
参考: 組み込み型 — Python 3.9.4 ドキュメント

まずはいつも使っている str.split() です。
呼び出しは、 str.split(sep=None, maxsplit=-1) のように定義されています。sepが区切り文字で、maxsplit が最大何箇所で分割るかの指定です。分割された結果は最大でmaxsplit+1個の要素になるので注意してください。 デフォルトの -1 の場合は、区切れる場所全部で区切ります。

# 文字列を, (カンマ) で区切る
print("a,b,c,d,e,f,g".split(","))
# ['a', 'b', 'c', 'd', 'e', 'f', 'g']

# スペースで区切る場合
print(" a b c  d e f g ".split(" "))  # cとdの間にはスペース2個入れた
# ['', 'a', 'b', 'c', '', 'd', 'e', 'f', 'g', '']

# maxsplit = 3 を指定。 3箇所で区切られ、長さ4の配列になる
print("a,b,c,d,e,f,g".split(",", 3))
# ['a', 'b', 'c', 'd,e,f,g']

# maxsplit が十分大きい時は指定しないのと同じ
print("a,b,c,d,e,f,g".split(",", 100))
# ['a', 'b', 'c', 'd', 'e', 'f', 'g']

一つ目のセパレーター引数(sep)ですが、これを省略することができます。省略すると、半角スペースやタブ、改行などの空白文字で区切られます。そして、さらに特殊な挙動として、連続する空白文字をひとつのセパレーターとして見なすようになります。また、文字列の先頭や末尾に空白があっても、結果の最初や最後に空文字列は含まれません。それぞれ見ていきましょう。

text = """
最初に改行がある複数行のテキストです。
この行には  連続した半角スペースがあります。

タブも\t入れてみました。
この下に空白行があります。


"""
print(text.split())
# ['最初に改行がある複数行のテキストです。', 'この行には', '連続した半角スペースがあります。', 'タブも', '入れてみました。', 'この下に空白行があります。']

split(” “)だと、スペースが連続してたらり、先頭/末尾にスペースがあると結果の配列に空白文字列が含まれていましたが、それがないのがわかりますね。また、複数の種類の文字(半角スペースやタブや改行)で、全部区切ってくれるのも便利です。

続いて、str.rsplit() を見ていきましょう。 使い方は str.rsplit(sep=None, maxsplit=-1) であり、splitと同じです。 挙動もほぼ同じなのですが、splitとの違いは右から順番に分割していくことです。maxsplit を指定しないと、分割できるところは全部分割するので、splitとrsplitの結果は同じになります。それでは、maxsplit に少し小さめの数値を設定して違いを見てみましょう。

text = "a,b,c,d,e,f,g"
print(text.split(",", 2))
# ['a', 'b', 'c,d,e,f,g']

print(text.rsplit(",", 2))
# ['a,b,c,d,e', 'f', 'g']

雰囲気伝わったでしょうか。

URLを / で分解して、右からn番目の要素を取るなどの使い方ができそうですね。
うちのブログだと、右から2番目がカテゴリを表す文字列です。

# このブログの記事URLの例
url ="https://analytics-note.xyz/machine-learning/scikit-learn-n-gram/"

# 右から4分割した結果。
print(url.rsplit("/", 3))
# ['https://analytics-note.xyz', 'machine-learning', 'scikit-learn-n-gram', '']

# インデックス0 にhttpsからドメイン名まで入る
print(url.rsplit("/", 3)[0])
# https://analytics-note.xyz

# インデックス1がカテゴリ名
print(url.rsplit("/", 3)[1])
# machine-learning

そして、最後が str.splitlines()です。 使い方は str.splitlines([keepends]) であり、セパレーターも分割回数も指定できません。keepends は 分割結果に末尾の改行コードを残すかどうかで、Trueと判定される何かを入れておくと改行コードが残ります。あまり必要ないので、何も入れなくていいでしょう。

import MeCab
tagger = MeCab.Tagger()
result = tagger.parse("すもももももももものうち")


# MeCabの結果を表示しておく
print(result)
"""
すもも	名詞,一般,*,*,*,*,すもも,スモモ,スモモ
も	助詞,係助詞,*,*,*,*,も,モ,モ
もも	名詞,一般,*,*,*,*,もも,モモ,モモ
も	助詞,係助詞,*,*,*,*,も,モ,モ
もも	名詞,一般,*,*,*,*,もも,モモ,モモ
の	助詞,連体化,*,*,*,*,の,ノ,ノ
うち	名詞,非自立,副詞可能,*,*,*,うち,ウチ,ウチ
EOS
"""

# splitlines() で分割した配列
print(result.splitlines())
"""
[
'すもも\t名詞,一般,*,*,*,*,すもも,スモモ,スモモ',
'も\t助詞,係助詞,*,*,*,*,も,モ,モ',
'もも\t名詞,一般,*,*,*,*,もも,モモ,モモ',
'も\t助詞,係助詞,*,*,*,*,も,モ,モ',
'もも\t名詞,一般,*,*,*,*,もも,モモ,モモ',
'の\t助詞,連体化,*,*,*,*,の,ノ,ノ',
'うち\t名詞,非自立,副詞可能,*,*,*,うち,ウチ,ウチ',
'EOS'
]
"""

splitlines() でサクッと行ごとに分離できていますね。いつも使っているsplit(“\n”)との違いも見ておきましょう。

print(result.split("\n"))
"""
[
'すもも\t名詞,一般,*,*,*,*,すもも,スモモ,スモモ',
'も\t助詞,係助詞,*,*,*,*,も,モ,モ',
'もも\t名詞,一般,*,*,*,*,もも,モモ,モモ',
'も\t助詞,係助詞,*,*,*,*,も,モ,モ',
'もも\t名詞,一般,*,*,*,*,もも,モモ,モモ',
'の\t助詞,連体化,*,*,*,*,の,ノ,ノ',
'うち\t名詞,非自立,副詞可能,*,*,*,うち,ウチ,ウチ',
'EOS',
''
]
"""

一見同じ結果に見えますが、最後に”という空白文字列の要素が入ってます。不要な’EOS’部分を切り捨てるときに切り落とす長さがかわるのでこれは注意して使いましょう。

こうしてみると、 splitlines でも split でもどちらを使っても良さそうですが、splitlinesには明確なメリットがあります。ドキュメントを見ていただけると一覧表が載っているのですが、splitlinesはかなり多くの環境の様々な改行コードや垂直タブ、改ページなど多くの特殊文字で区切ってくれます。split(“\n”)だと、改行コードが違う別の環境ではちょっと動作が不安なので、splitlinesを使った方が汎用性の高いコードになると期待できます。

少し気になる挙動なのですが、「{改行コード}テキスト{改行コード}」という文字列を分解すると、先頭の{改行コード}では区切って1要素目に空白文字列を返してくるのに、末尾の{改行コード}は、その後ろに何もなければ{改行コード}を消して終わります。

print("""
先頭と末尾に改行コード
""".splitlines()
)
# ['', '先頭と末尾に改行コード']

なぜこのような挙動になるのか調べたのですが、結果的に、keepends を指定して動きを見ると理解できました。

"""
aaa
bbb
""".splitlines(True)
# ['\n', 'aaa\n', 'bbb\n']

上記のコードの通り、splitlinesは「改行コードの後ろ」で区切ってるんですね。そして、keependsがFalse(もしくは未指定)の場合は、この区切られた結果から末尾の改行コードを消しているようです。

"""
aaa
bbb
""".splitlines()
# ['', 'aaa', 'bbb']

split() (sep指定なし)は、連続する空白文字をまとめて一つのセパレーターとして扱っていましたが、splitlinesにはそのような機能はなく、普通に改行の数だけ区切って空白文字列だけの要素を返してきます。

print("""この下に連続した改行



この上に連続した改行""".splitlines()
)
# ['この下に連続した改行', '', '', '', 'この上に連続した改行']

今回の記事で紹介したメソッドたちは大変シンプルな機能なのですが、不注意に使うと思ってた結果と要素数やインデックス等がずれたりするので、慣れるまではよく確認しながら使いましょう。

Pythonを使ってよく連続する文字列を検索する

前回の記事で紹介したn-gram(といっても今回使うのはユニグラムとバイグラム)の応用です。
テキストデータの中から高確率で連続して登場する単語を探索する方法を紹介します。
参考: scikit-learnで単語nグラム

コーパスとして、昔作成したライブドアニュースコーパスをデータフレームにまとめたやつを使います。
参考: livedoorニュースコーパスのファイルをデータフレームにまとめる

今回の記事で使うライブラリの読み込み、データの読み込み、さらに分かち書きに使う関数の準備とそれを使った単語の形態素解析まで済ませておきます。

import re
import pandas as pd
import MeCab
from sklearn.feature_extraction.text import CountVectorizer

# データの読み込み
df = pd.read_csv("./livedoor_news_corpus.csv")
# ユニコード正規かとアルファベットの小文字統一
df.text = df.text.str.normalize("NFKC").str.lower()

# 分かち書きの中で使うオブジェクト生成
tagger = MeCab.Tagger("-d /usr/local/lib/mecab/dic/mecab-ipadic-neologd")


def mecab_tokenizer(text):
    # テキストを分かち書きする関数を準備する
    parsed_lines = tagger.parse(text).split("\n")[:-2]
    surfaces = [l.split('\t')[0] for l in parsed_lines]
    features = [l.split('\t')[1] for l in parsed_lines]
    # 原型を取得
    bases = [f.split(',')[6] for f in features]
    # 各単語を原型に変換する
    token_list = [b if b != '*' else s for s, b in zip(surfaces, bases)]
    return " ".join(token_list)

# 分かち書き
df["tokens"] = df.text.apply(mecab_tokenizer)

さて、データの準備が整ったので本題の処理の方に移っていきましょう。
やることは簡単で、ユニグラムモデル(単語単位のBoW)とバイグラムモデル(2単語連続で学習したBoW)を作成します。そして、「単語1」の出現回数で、「単語1 単語2」の出現回数を割ることによって、「単語1」の後に「単語2」が出現する確率を求めてそれがある程度より高かったらこの2単語は連続しやすいと判断します。また逆に、「単語2」の出現回数で、「単語1 単語2」の出現回数を割って同様の判定もかけます。

そのためにまず、バイグラムとユニグラムの単語の出現回数の辞書を作成します。
それぞれモデルを作ってBoWにし、語彙と出現回数のペアの辞書へと変換します。

# モデル作成
uni_model = CountVectorizer(
        token_pattern='(?u)\\b\\w+\\b',
        ngram_range=(1, 1),
        min_df=30,
    )
bi_model = CountVectorizer(
        token_pattern='(?u)\\b\\w+\\b',
        ngram_range=(2, 2),
        min_df=30,
    )

# BoWへ変換
uni_bow = uni_model.fit_transform(df["tokens"])
bi_bow = bi_model.fit_transform(df["tokens"])

# 学習した語彙数
print(len(uni_model.get_feature_names()), len(bi_model.get_feature_names()))
# 6921 12227

# 出現回数の辞書へ変換
uni_gram_count_dict = dict(zip(
        uni_model.get_feature_names(),
        uni_bow.toarray().sum(axis=0)
    ))
bi_gram_count_dict = dict(zip(
        bi_model.get_feature_names(),
        bi_bow.toarray().sum(axis=0)
    ))

これで計算に必要な情報が揃いました。min_df は少し大きめの30にしていますが、これは利用するコーパスの大きやさかける時間、求める精度などによって調整してください。(少し大きめの値にしておかないと、特にバイグラムの語彙数が膨れ上がり、次の処理が非常に時間がかかるようになります。)

さて、これで出現回数の情報が得られたのでこれを使って「単語1」の次に来やすい「単語2」を探してみましょう。余りたくさん出てきても困るので95%以上の確率で続くなら出力するようにしたのが次のコードです。

for uni_word, uni_count in uni_gram_count_dict.items():
    # uni_word: ユニグラムモデルでカウントした単語
    # uni_count: 上記単語が出現した回数

    # 対象の単語で始まる単語ペアにマッチする正規表現
    pattern = f"^{uni_word}\\b"
    target_bi_gram = {k: v for k, v in bi_gram_count_dict.items() if re.match(pattern, k)}
    for bi_words, bi_count in target_bi_gram.items():
        # bi_words: バイグラムモデルでカウントした単語ペア
        # bi_count: 上記単語ペアが出現した回数

        if bi_count / uni_count >= 0.95:
            print(bi_words, f"{bi_count}回/{uni_count}回")

# 以下出力の先頭の方の行
"""
1677 万 73回/73回
2106 bpm 107回/107回
75m bps 68回/70回
84回 アカデミー賞 94回/96回
888 毎日 41回/42回
angrybirds 風 41回/41回
bci 3 81回/81回
blu ray 339回/339回
deji 通 614回/619回
details id 160回/162回
digi 2 322回/326回
icecream sandwich 939回/941回
kamikura digi 89回/89回
katsuosh digi 56回/56回
let s 76回/79回
-- 以下省略 -- 
"""

途中正規表現を使っていますが、これは、選択中の「単語1」に対して「単語1 単語2」という文字列に一致させるものです。^は先頭、\\b(エスケープされて実際は\b)は単語区切りにマッチします。

出力はたくさん出ますので、先頭の方を上のコード中に例示しました。
84回 アカデミー賞 94回/96回 は 「84回」って単語が96回登場し、そのうち、94回は「84回 アカデミー賞」と続いたという意味です。
1677 万 などは 1677万画素って単語の一部ですね。

続いて、ある単語の後ろではなく前に登場しやすい単語も探してみましょう。
これは、正規表現のpatternが少し違うだけです、と行きたかったのですがもう1箇所違います。re.matchが先頭マッチの探索しかしてくれないので、正規表現でマッチさせるところのメソッドがre.searchになります。

for uni_word, uni_count in uni_gram_count_dict.items():

    # 対象の単語で終わる単語ペアにマッチする正規表現
    pattern = f"\\b{uni_word}$"
    target_bi_gram = {k: v for k, v in bi_gram_count_dict.items() if re.search(pattern, k)}
    for bi_words, bi_count in target_bi_gram.items():
        if bi_count / uni_count >= 0.95:
            print(bi_words, f"{bi_count}回/{uni_count}回")

# 以下出力の先頭の方の行
"""
iphone 3gs 38回/38回
第 84回 96回/96回
成長率 888 41回/42回
msm 8960 118回/118回
with amazlet 38回/38回
の angrybirds 41回/41回
パック bci 81回/81回
2106 bpm 107回/109回
apps details 160回/162回
after effects 63回/64回
パッケージ ffp 77回/77回
wi fi 869回/882回
モバイルwi fiルーター 136回/136回
-- 以下省略 -- 
"""

こちらもうまく出力されましたね。読み解き方はは先ほどと同じです。

そもそも、なぜこのような処理を作ろうと思ったかと言うと、MeCab等で分かち書きした時に、本当は1単語なのに複数単語に分かれてしまっているようなものを効率よく検索したかったためです。

この記事のコードでは新語辞書(mecab-ipadic-neologd)を使ってるので、あからさまなものは少ないですが、デフォルトのIPA辞書を使うと、 クラウド が 「クラ」と「ウド」に分かれている例などがポロポロ見つかります。

この記事の目的は上のコードで果たしたので以下は補足です。

さて、今回の結果を辞書の改善等に使うことを考えると、ここで出力された単語ペアを結合させて放り込んでいけば良さそうに見えます。しかし、実際は話はそう単純ではありません。
例えば、「wi fi」とか、「モバイルwi fiルーター」といった単語が出てきていますが、なるほど、辞書にWiFIやモバイルWiFiルーターが含まれてないんだな、と勘違いしそうになります。

しかし実際は、Neologdを使うと、どちらも正しく形態素解析できていて、「Wi-Fi」や「モバイルWi-Fiルーター」と言う単語で出力されるんですね。いつ分かれているかと言うと、sickit-learnが学習する時に-(ハイフン)を単語境界文字として扱っているのでここで切ってしまっています。

同様の例として、「ウォルト ディズニー」などもあります。これも実はMeCabは「ウォルト・ディズニー」と一単語にしているのに、scikit-learnが「・」で勝手に区切ってます。

このほかにも、出力を見ていくと「くだける 充電」と言うのが出てきますが、世の中に「くだける充電」という単語があるのか、と考えると間違えます。
これは元のテキストを見ると「おくだけ充電」という言葉があり、これが、「お」「くだけの原型のくだける」「充電」と形態素解析されて後半の2単語がくっついて出てきたものです。

このほか、「特有 の」とか「非常 に」のように、確かによく連続するんだろうけど、これは単語として分かれるのが正常だよね、って言う例も多く確認はかなり手間なようでした。

そもそも出力が意外に多かったです。(サンプルコードでは0.95以上としましたが、本気で探すならこの閾値はもっと下げた方が良さそうです。しかしそれをやるとどんどん出力が増えます。)

本当は1単語なのに間違って分かれてしまっている単語を探す、と言う目的に対しては、思ったよりノイズが多かったのですが、それでもかなり有効な方法だと思うので同様の課題をお持ちの方は試してみてください。細かくは書いていませんが、形態素解析する段階で品詞を絞っておく(例えば名詞のみする)とか、てにをは的なワードを除いておくなど改善の余地多いので色々試すのも楽しいです。

scikit-learnで単語nグラム

自然言語処理の前処理に、nグラム(n-gram)という概念があります。
これは隣り合って出現したn単語のことです。nの値が小さいときは特別な名前がついていて、
n=1の場合をユニグラム(unigram)、n=2の場合をバイグラム(bigram)、n=3の場合をトライグラム(trigram)と呼びます。

ネットで検索すると、「n単語」のことではなく、隣り合って出現した「n文字」をnグラムと言うという説明も見かけた(例えば、Wikipediaもそうです)ので、誤解を避けるためこの記事のタイトルは単語nグラムとしましたが、面倒なので以下記事中でnグラムと書いたら単語nグラムを指すものとします。
「言語処理のための機械学習入門」などの書籍でも、単語nグラムのことをnグラムと呼んでいるのでおかしくはないと思っています。(P. 62)

自然言語処理を勉強し始めた駆け出しの頃、テキストをBoWでベクトル化する時に、ユニグラムだけでなく、バイグラムを加えて単語間のつながりを考慮するというアイデアを知って、これはいいアイデアだと思って試したりしました。しかし、バイグラムを使って機械学習の精度が上がった経験というのはほとんどなく、その後もたまに試すけど有効だった覚えがほぼ無くだんだん試さなくなってきていました。
しかし、最近機械学習とは少し違う目的でnグラムを使いたいことがあったので、この機会にsciki-learnでBoWを作る時のnグラム関連の引数の挙動をまとめておこうと思ったのでこの記事に整理していきます。

今回は、サンプルに使うテキストデータはあらかじめ分かち書きしたやつを用意しておきます。題材はいつもメロスなので今回は幸福の王子にしました。(青空文庫から拝借)

corpus = [
    "町 の 上 に 高い 柱 が そびえる 、 その 上 に 幸福 の 王子 の 像 が 立つ て いる ます た 。",
    "王子 の 像 は 全体 を 薄い 純金 で 覆う れる 、 目 は 二つ の 輝く サファイア で 、 王子 の 剣 の つ か に は 大きな 赤い ルビー が 光る て いる ます た 。",
    "王子 は 皆 の 自慢 です た 。",
    "「 風見鶏 と 同じ くらい に 美しい 」 と 、 芸術 的 だ センス が ある という 評判 を 得る たい がる て いる 一 人 の 市会 議員 が 言う ます た 。",
    "「 もっとも 風見鶏 ほど 便利 じゃ ない が ね 」 と 付け加える て 言う ます た 。",
    "これ は 夢想 家 だ と 思う れる ない よう に 、 と 心配 する た から です 。",
    "実際 に は 彼 は 夢想 家 なんか じゃ ない た の です が 。",
]

さて、早速やっていきましょう。
scikit-learnのテキストの前処理には、BoWを作るCountVectorizer と、 tf-idfを作るTfidfVectorizer がありますが、nグラムに関しては両方とも同じくngram_range という引数で設定することができます。(最小)何グラムから(最大)何グラムまでを学習に含めるかをタプルで指定するもので、デフォルトは、 ngram_range=(1, 1) です。(ユニグラムのみ)。

バイグラムを学習させたければ(2, 2)と指定すればよく、ユニグラムからトライグラムまで学習したいなら(1, 3)です。

とりあえず、(1, 2)でやってみます。1文字の単語も学習させるため、token_patternも指定します。

from sklearn.feature_extraction.text import CountVectorizer


# モデル作成
bow_model = CountVectorizer(
    token_pattern="(?u)\\b\\w+\\b",
    ngram_range=(1, 2),
)

# 学習
bow_model.fit(corpus)

# 学習した単語の先頭10個
print(bow_model.get_feature_names()[:10])
# ['ある', 'ある という', 'いる', 'いる ます', 'いる 一', 'か', 'か に', 'から', 'から です', 'が']

get_feature_names() で学習した単語を取得してみましたが、「ある」「いる」「か」などの1単語の語彙に混ざって、「ある という」「いる ます」などのバイグラムの語彙も混ざっていますね。

ユニグラムとバイグラムで合計、194の語彙を学習しているので、このモデルを使ってBoWを作ると、テキスト数(7) * 語彙数(194)の疎行列になります。

print(len(bow_model.get_feature_names()))
# 194

print(bow_model.transform(corpus).shape)
# (7, 194)

さて、もう少しライブラリの挙動を詳しくみていきましょう。次はstopwordとの関連です。
CountVectorizer は stop_wordsという引数で学習に含めない単語を明示的に指定できます。
試しにてにをは的な文字をいくつか入れていみます。(ここでは学習対象はバイグラムだけにします)

# モデル作成
bow_model = CountVectorizer(
    token_pattern="(?u)\\b\\w+\\b",
    ngram_range=(2, 2),
    stop_words=["て", "に", "を", "は", "が", "の", "た",]
)

# 学習
bow_model.fit(corpus)

# 学習した単語の一部
print(bow_model.get_feature_names()[50: 60])
# ['夢想 家', '大きな 赤い', '実際 彼', '家 だ', '家 なんか', '市会 議員', '幸福 王子', '彼 夢想', '得る たい', '心配 する']

学習した語彙の一部を表示していますが、わかりやすいのは「幸福 王子」というペアが含まれていることです。これはもちろん「幸福 の 王子」の部分から学習されたものです。
「の」が stopwordに含まれているので、まず、「の」が取り除かれて、「幸福 王子」になってからバイグラムの学習が行われたのだとわかります。
「幸福 の」と「の 王子」を学習してからstopwordを除くわけでは無いということが確認できました。

ちなみに、token_patternがデフォルトの場合、1文字の単語は学習されませんが、この時も似たような挙動になります。

# モデル作成
bow_model = CountVectorizer(
    ngram_range=(2, 2),
)

# 学習
bow_model.fit(corpus)

# 学習した単語の一部
print(bow_model.get_feature_names()[30: 40])
# ['同じ くらい', '夢想 なんか', '夢想 思う', '大きな 赤い', '実際 夢想', '市会 議員', '幸福 王子', '得る たい', '心配 する', '思う れる']

続いて、学習結果に含まれる単語を出現頻度で間引くmid_df/max_df の挙動を確認しておきます。min_dfの方がわかりやすいのでそちらを例に使います。
min_df=3(3テキスト以上に含まれる単語だけ学習する。3回以上では無いので注意)と指定してみます。

# モデル作成
bow_model = CountVectorizer(
    token_pattern="(?u)\\b\\w+\\b",
    ngram_range=(1, 2),
    min_df=3,
)

# 学習
bow_model.fit(corpus)

# 学習した単語
print(bow_model.get_feature_names())
# ['いる', 'が', 'た', 'て', 'て いる', 'です', 'と', 'ない', 'に', 'の', 'は', 'ます', 'ます た', '王子']

「て いる」は3テキストに含まれているので学習されていますね。その一方で「王子 は」は1テキストにしか含まれていないので学習結果に含まれていません。「王子」と「は」はそれぞれ3テキスト以上に含まれていますが、「王子 は」という並びで登場したのが1回だけだったので対象外になっているのです。

このことから、min_dfによる間引きはまずn-gramを学習してその後に行われていることがわかりますね。stop_wordsと実行タイミングが違うので注意しましょう。

例示はしませんがmax_dfも話は同様です。ちなみに、 max_df=0.7 とすると、 ユニグラムの「た」は学習結果らから除外されて、バイグラムの「ます た」などは含まれることが確認できます。

Pythonのリストをn個に分割する

めったに使わないのですが、前回の記事がリストをn個ずつに分割するだったので今回はリストをn個のリストに分割する方法を紹介します。
ちなみに、目的が機械学習のクロスバリデーションであれば、scikit-learnに専用のメソッドがあるのでそちらを使いましょう。
今回の記事はそれ以外の用途で、何かしらの事情があってリストをn分割する必要が発生した時に使います。

さて、まず簡単に思いつくのは前回の記事同様にリストのスライスを使う方法です。
元のデータのサイズをnで割って区切り位置を決め、その位置で区切ります。
コードにすると次のようになりますね。
例として、サイズが23のデータを5分割しています。
途中、スライスする位置をintで整数に丸めているのは、単にスライスの表記が整数しか受け付けないからです。

# サンプルのデータ生成
data = list(range(23))
print(data)
# [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22]
print(len(data))
# 23

# 5分割する
n = 5
size = len(data)

for i in range(n):
    start = int((size*i/n))
    end = int((size*(i+1)/n))
    print(data[start:end])
"""
[0, 1, 2, 3]
[4, 5, 6, 7, 8]
[9, 10, 11, 12]
[13, 14, 15, 16, 17]
[18, 19, 20, 21, 22]
"""

特に何も変哲のないコードですし、無事にリストが5分割されました。

ただ、一点気持悪いというか少なくとも僕の好みには合わない点があります。
それが分割結果の各リストのサイズです。数えてみると、4個、5個、4個、5個、5個、となっています。23が5で割り切れないので、数が不揃いになるのは仕方ないのですが、個人的には、4/4/5/5/5 か、 5/5/5/4/4 のどちらかで切りたいです。

しかし、これを実装するのはそこそこ手間がかかります。元のデータ長を分けたいグループ数で整数除算し、商とを余を求めて分割後の各グループに属する要素数を求め、その要素数から区切り位置を決め、その位置で切る手順をコードに起こす必要があるからです。
やってみたのが次のコードです。(確認用のprint文や説明のコメントのせいで余計に面倒なコードに見えてしまっていますね。)

import numpy as np


# data は上のコード例と同じものを使う。
data = list(range(23))
n = 5
size = len(data)

# データの件数を分けたいグループ数で割って商と余りを求める
quotient, remainder = divmod(size, n)
print("商:", quotient)
# 商: 4
print("余り:", remainder)
# 余り: 3

# [0] に続けて各グループの要素数を指定するリストを作る
section_sizes = ([0] + remainder * [quotient+1] + (n-remainder) * [quotient])
print(section_sizes)
# [0, 5, 5, 5, 4, 4]

# 累積和をとって、スライスする点のリストにする
slice_points = list(np.cumsum(section_sizes))
print(slice_points)
# [0, 5, 10, 15, 19, 23]

# 作成したスライス位置を使ってリストを切る
for i in range(n):
    start = slice_points[i]
    end = slice_points[i+1]
    print(data[start:end])

"""
[0, 1, 2, 3, 4]
[5, 6, 7, 8, 9]
[10, 11, 12, 13, 14]
[15, 16, 17, 18]
[19, 20, 21, 22]
"""

はい、これで、5個、5個、5個、4個、4個、に区切れましたね。

途中累積和を取るためにnumpyをインポートしてcumsumまで使っています。
ただ、どうせnumpyを使うことになるのであれば、実はnumpyに専用のメソッドが用意されているので断然そちらがお勧めです。

参考: numpy.array_split

numpyのarray用に実装されたメソッドだと思いますが、ただのlistに対しても動作してくれます。これを使うと、たったこれだけのコードになります。

data = list(range(23))
n = 5
print(np.array_split(data, n))
"""
[array([0, 1, 2, 3, 4]),
 array([5, 6, 7, 8, 9]),
 array([10, 11, 12, 13, 14]),
 array([15, 16, 17, 18]),
 array([19, 20, 21, 22])]
"""

めっちゃ簡単ですね。メソッドの戻り値はn分割した各グループのリストになります。
分割された各グループは array 型に変換されるのでその点だけ注意してください。
元のデータがarray型でなくても結果はarray型になります。

Pythonのリストをn個ずつに分割する

今回の記事はPythonのlistのスライスの小ネタです。リストを長さnのリストに分割する方法を紹介します。

これは次のようなコードで実現できます。
例として、長さが17のリストを生成し、n=5個ずつに分けてprintしています。

data = list(range(17))
print(data)
# [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]
print(len(data))
# 17

n = 5
for i in range(0, len(data), n):
    print(data[i: i+n])

"""
[0, 1, 2, 3, 4]
[5, 6, 7, 8, 9]
[10, 11, 12, 13, 14]
[15, 16]
"""

range(0, len(data), n)によって、 0, 5, 10, 15 というn=5 ずつ増える数列を生成して、[i: i+5]というスライスで配列を切り出しているだけですね。

さっと書いて動くのを見ると何の変哲もないコードのように思えますが、実はPythonのlistのスライスの非常に便利な仕様を活用しています。
それは、スライスの範囲が元のリストのインデックスからはみ出していても問題なく動くと言うことです。

i=15の時、[i: i+5]は[15: 20]ですが、元のlist は長さが17なので、20番目の要素などありません。しかし、このスライスは、切り出せた分だけ切り出して動いてくれるのでこのコードがエラーにならずに動作するのです。

もしこの仕様がなければ、i+nが配列の長さを超えていないかどうかで分岐を一つ書く必要があるところでした。

ちなみに、スライスではなくインデックスで要素を取り出す場合は当然ですがインデックスの最大値を超えた値を入れるとエラーになります。

print(data[15: 20])  # スライスの範囲がlistの長さを超えていても動く
# [15, 16]

print(data[100: 200])  # これも動く
# []

try:
    print(data[20])  # これはエラーになる
except Exception as e:
    print(e)
# list index out of range

正直この技を使う機会はあまりないのですが、例えばAmazon Comprehendような1回に渡せるデータ数に上限があるAPIで大量のデータを処理するときなどに利用できます。
APIが受け入れてくれる上限のデータ数でリストを区切って順に実行したりできますね。

Pythonの所属検査演算(in)について

Pythonでは、ある要素が集合や配列に存在しているかどうか、inという式を使って判定できます。この度改めてドキュメントを読んでみたのと、配列の配列などちょっと特殊な用途について挙動を調べたのでまとめておきます。

ドキュメントはこちらです。in のことは所属検査演算と呼ぶようです。URLから推測すると英語名は、 membership test operations のようですね。
参考: 6.10.2. 所属検査演算 式 (expression) — Python 3.9.4 ドキュメント

演算子 in および not in は所属関係を調べます。とある通りで、
x in s は xがsの要素だったらTrue、そうでない場合はFalseを返します。not in は in の否定です。この記事ではこの s を色々変えながら挙動を見ていきましょう。

配列や集合、タプルに対する挙動

まずは一番基本的な配列や集合に対する挙動です。まず配列についてみていきますが、これは特に説明することもなく、配列sの要素のどれかとxが一致すれば x in s はTrueになります。

list_data = [1, 2, 3, 4, 5]
print(3 in list_data)
# True
print(8 in list_data)
# False
print(2 not in list_data)
# False

集合やタプルの場合も同様です。タプルはこれ以降コード例を省略しますが配列と同じように動きます。

set_data = {1, 2, 3, 4, 5}
print(3 in set_data)
# True
print(8 in set_data)
# False
print(2 not in set_data)
# False

tuple_data = (1, 2, 3, 4, 5)
print(3 in tuple_data)
# True
print(8 in tuple_data)
# False
print(2 not in tuple_data)
# False

ここで、少し注意が必要なのは、 None についても機能するということです。SQLの挙動に慣れていると、NULLが絡むとTrueでもFalseでもなくNULLが返ってくるので、Noneが絡むとNoneが返ってくるような気がしてしまいますが、None == None とみなすようでSQLとは違った動きになります。

print(None in [1, 2, None, 3])
# True
print(None in [1, 2, 4, 5])
# False
print(None in {1, 2, None, 3})
# True

もっと言うと、numpyの nan についても使えます。np.nan == np.nan は False なのでこれは不思議な挙動です。

import numpy as np


print(np.nan == np.nan)
# False
print(np.nan in [1, 2, 3, np.nan])
# True

配列の部分列や、集合の部分集合については使えません。 xがsの部分列や部分集合の場合も
x in s はFalseが返ってきます。
部分集合のジャッジをしたい場合は不等号が使えるのでそちらを使いましょう。

# 部分列はFalseになる
print([2, 3] in [1, 2, 3, 4])
# False

# 部分集合もFalseになる
print({2, 3} in {1, 2, 3, 4})
# False

# 部分集合は不等号で判定できる。
print({2, 3} <= {1, 2, 3, 4})
# True

次に、配列の配列について検証しましたが、なんとこれが正常に動作します。hashableな形でないとダメだと思い込んでいたので意外でした。

# 配列の配列も動く
print([1, 2] in [[1, 2], [3, 4], [5, 6]])
# True

# もちろん、含まない場合はFalse
print([2, 3] in [[1, 2], [3, 4], [5, 6]])
# False

# 要素の要素についてはFalseになる
print(3 in [[1, 2], [3, 4], [5, 6]])
# False

では、集合の集合は?と思ったのですが、集合(set)はhashableなものしか要素に持てないので、集合の集合自体作れません。なので気にしなくて大丈夫です。
タプルのタプルは、当然配列と同じように動作してくれます。

辞書(dict)に対する挙動

辞書sに対して、x in s を使うと、xが辞書sのキーに含まれていた場合にTrue、含まれていない場合にFalseを返してくれます。キーではなく値の中にあるかどうかを知りたいってばあいはvalues()、キーだけでなくキーと値のペアで含まれているかどうかを知りたいって場合はitems()をそれぞれ併用しましょう。

dict_data = {
    "apple": "りんご",
    "orange": "みかん",
    "banana": "バナナ" 
}

# キーの中に一致するものがあればTrue
print("apple" in dict_data)
# True

# keys()メソッドでキーの一覧を取得して判定しても挙動は同じ
print("apple" in dict_data.keys())
# True

# 値の中に一致するものがあったとしてもこれはFalse
print("みかん" in dict_data)
# False

# 値の中に一致するものがあるかどうか見る場合は、values()メソッドを使う
print("みかん" in dict_data.values())
# True

# キーと値のペアで判定をしたい場合はitems()メソッドを使う。
print(("apple", "りんご") in dict_data.items())
# True

# キーと値がそれぞれ存在していても組み合わせが違うとFalseになる
print(("apple", "バナナ") in dict_data.items())
# False

文字列に対する挙動

文字列sと、文字x対してinを使うと、xがsに含まれている場合にTrueを返してきます。これだけだと、配列と要素の場合と同じように見えるのですが、実は文字列の独特の挙動として、文字列xが文字列sの部分文字列の場合もTrueを返してくれると言うものがあります。便利ですね。実装としては、 x in y は y.find(x) != -1 と等価になっているそうです。

# 文字が含まれていればTrue
print("c" in "abcde" )
# True

# 部分列であればTrue
print("bcd" in "abcde" )
# True

# 個々の文字が含まれていても順番が違うとFalse
print("ba" in "abcde" )
# False

文字列についてはもう一つ注意があって、空文字列は他の任意の文字列の部分文字列とみなされます。要するに次の式はどちらもTrueです。

print("" in "abcde")
# True

print("" in "")
# True

その他の型 (ユーザー定義型)における in

これまで、Pythonの基本的な各型における所属検査演算子の使い方を見てきましたが、各ライブライで実装されているようなクラスにおいても in は使えますし、自分で実装するクラスにおいても、inの振る舞いを定義して実装することができます。
その方法は、 class において、 __contains__() メソッドを実装することです。

__contains__() メソッドが実装されているクラスにおいては、
x in y は、 y.__contains__(x) が Trueを返す場合にTrueになり、そうでない場合にFalseになります。

実験したところ、__contains__が、if文でTrueと判定されるようなもの、(空白ではない文字列、0ではない数値、空ではない配列など)を返した場合は Trueになり、if文でFalseと判定されるようなもの(False,None,0など)を返した場合はFalseになるので、 __contains__ と in の結果が一致する、と言うわけではないようです。

大変奇妙な例で恐縮ですが実験したのが次の結果です。

class myclass():
    def __contains__(self, y):
        return "含みます"


mc = myclass()

# __contains__ メソッドを呼び出すとメソッドの結果がそのまま返される
print(mc.__contains__("a"))
# 含みます

# in だと True か False に変換される
print("a" in mc)
# True

__contains__ が実装されていないが __iter__ が実装されているクラスの場合(要するにイテレーター)の場合は、反復の途中で x に等しい要素が登場した場合に Trueになります。
また変な例なのですが、__iter__(と、セットで使う__next__)だけ実装したようなクラスを作ったのでそれで実験します。このクラスは[1,2,3,4,5]を順番に返します。

class myclass2():
    def __init__(self):
        self._i = 0

    def __iter__(self):
        return self

    def __next__(self):
        if self._i == 5:
            raise StopIteration()
        self._i += 1
        return self._i


mc2 = myclass2()
print(2 in mc2)
# True
mc2 = myclass2()
print(6 in mc2)
# False

__contains__も__iter__も実装されていない場合は、最後に、__getitem__()が試されます。
__getitem__() は 辞書型のように[]でアクセスしてきた時の挙動を定義する特殊メソッドですね。これは単に x == y[i] となる iが見つかれば True, そうでない場合はFalseとなるようです。
これもまた変な例ですが、__getitem__だけ実装されたクラスで実験しました。

class myclass3():
    def __getitem__(self, i):
        # 無限ループを避けるためにiが大きくなったらエラーにする
        if i >= 100:
            raise
        return i**2


mc3 = myclass3()

# 平方数ならTrue、 mc3[4] == 16 だから。
print(16 in mc3)
# True

# 平方数でない場合はエラーになるまで探し続ける
print(18 in mc3)
# RuntimeError: No active exception to reraise

改めてドキュメントを読んでみて色々試した結果、それなりに理解が深まった気がします。

VALUESを使ったダミーデータの生成

WINDOW関数の話はひと段落しましたが、今回もPrestoの話です。例によって僕はトレジャーデータで検証しています。(今回の記事で紹介する内容はMySQLでは動かないという話も見かけました。)

ここ最近の記事で掲載したサンプルのSQLでは、テーブルに保存されたデータではなくSQLで生成したデータを使っていました。事前にこういうデータを用意してますとか書くのが面倒だったので。その際、ARRAY関数で作った配列を並べて、UNNESTでテーブル型に変換していたのですが、実はVALUES関数をつかってダミーデータを作ることもできるそうです。

この機会に、VALUES関数について調べたので、わかったことを記事にまとめておきます。PrestoのVALUES関数のドキュメントはこちらです。
参考: VALUES — Presto 0.261 Documentation

Description を読むと、VALUESは問い合わせ(query)が使用できる場所ならどこでも使用できる、トップレベルでさえ使える、といきなり衝撃的なことが書かれています。僕は、INSERT文などの特定の構文の中でしか使えないと思っていました。

試しにトップレベルで使ってみましょう。

VALUES 1, 2, 3

-- 出力結果
_col0
1
2
3

確かに、無名列(_col0)に3行のレコードが生成されました。

複数列のデータを作ることもできます。

VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c')

-- 出力結果
_col0, _col1
1,     'a'
2,     'b'
3,     'c'

ドキュメントには、ASを使えばテーブルと列に名前も付けれる、と書かれているので、色々試したのですが、トップレベルでVALUES を使った場合にASで列名をつける方法は結局見つけられませんでした。いろんな位置にカッコをつけたりして検証したのですが。

無名テーブルではなく、テーブル名と列名をつけたい場合は、次のようにSELECT文のFROM句の中で使うことで実現できます。

SELECT
  id,
  name
FROM
  (
    VALUES
      (1, 'a'),
      (2, 'b'),
      (3, 'c')
  ) AS t(id, name)

-- 出力結果
id, name
1,  'a'
2,  'b'
3,  'c'

この結果は、UNNESTを使って書いた下のクエリの結果と同じになります。

SELECT
  id,
  name
FROM
  UNNEST(
    ARRAY[1, 2, 3],
    ARRAY['a', 'b', 'c']
  ) AS t(id, name)

UNNEST と VALUES のどちらがいいか、という話ですが 読みやすさというか、直感的でぱっと見で結果の予想がつきやすいのは VALUESの方なんじゃないかなと思います。ただ、10行くらいのダミーデータを生成しようとすると、UNNESTの方が行数が少なくなりやすく、このブログのサンプルコードとしてはそちらの方が使いやすかったりします。非本質的な部分なのであまりスペースを取りたくないので。

処理速度等の性能面の比較は行っていません。性能が気になるほどの莫大なデータをこのような方法でSQLに直接書くべきではないと思いますので、もしそのような大規模データをサンプルとして用意したい場合は、VALUESを使うとか言ってないで、素直にどの下のテーブルに格納してそこからSELECTした方が良いと思います。

Prestoの残りのWINDOW関数について紹介

6記事連続ですが今回もPrestoのWINDOW関数の記事です。例によって僕はトレジャーデータで検証しています。

実は前回のframeの記事でWINDOW関数シリーズは終わりにしようかと思っていたのですが、ドキュメントを見ると、まだ紹介してない関数はあと3つだけになっており、試しに動かしてみたら予想とちょっと違う動きも見られたので切りよくこれらまで紹介することにしました。

ドキュメントはこの記事もここです。
参考: Window Functions

この記事で紹介する関数は次の3つです。
- first_value(x)
- last_value(x)
- nth_value(xoffset)

first_value の説明は、 Returns the first value of the window. とあり、
last_value の説明は、Returns the last value of the window. とどちらも簡潔に書かれています。WINDOW内の最初の値と最後の値を返してくれると読めますね。

そして、 nth_value は offset で指定した数値番目(数値は1以上)の値を返してくれます。

さて、いつもみたいにダミーデータを使ってやってみましょう。
試したコードは下記です。

WITH
    sample_table AS(
        SELECT
            id,
            category,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
                ARRAY['A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
                ARRAY[20, 10, 20, 30, 5, 50, 15, 30, 20, 40]
            ) AS t(
                id,
                category,
                value
            )
    )
SELECT
    id,
    category,
    value,
    first_value(value) OVER(PARTITION BY category ORDER BY value) AS first_value_,
    last_value(value) OVER(PARTITION BY category ORDER BY value) AS last_value_,
    nth_value(value, 4) OVER(PARTITION BY category ORDER BY value) AS nth_value_4
FROM
    sample_table

さて、この結果はどうなるでしょうか?3関数とも、OVER(PARTITION BY category ORDER BY value) をつけたので、 categoryの値で、’A’ と ‘B’ に分けられ、 value 列の値でソートされた上で、関数が実行されそうですね。

僕は、 first_value(value) の結果は、category ‘A’と’B’ でそれぞれ一番小さい値である、5と15が返され、 last_value(value) の結果は、それぞれのカテゴリで一番大きい、50と40が返されると予想していました。
そして、nth_value(value, 4) は’A’,’B’それぞれの小さい方から4番目の値である、20と40が帰ってくると思っていました。

ところが実行結果は以下の通りです。

id, category, value, first_value_, last_value_, nth_value_4
5,  'A',      5,     5,            5,           NULL
2,  'A',      10,    5,            10,          NULL
1,  'A',      20,    5,            20,          20
3,  'A',      20,    5,            20,          20
4,  'A',      30,    5,            30,          20
6,  'A',      50,    5,            50,          20
7,  'B',      15,    15,           15,          NULL
9,  'B',      20,    15,           20,          NULL
8,  'B',      30,    15,           30,          NULL
10, 'B',      40,    15,           40,          40

いかがでしょうか。この結果が予想通りだった!って人は(元々仕様を正確に理解していた人以外に)いらっしゃいますか?

first_value_ 列はいいですね。行をcategory列でグループ分けして、valueでソートした後、それぞれのcategoryのグループの最初の値である 5と15 を返してくれています。

問題は、last_value_ 列と、 nth_value_4 列です。
last_value_ の方なんて、同じ行の value 列の値をそのまま返してきていて、関数を使った意味がないです。

なぜこのような挙動になるのかを理解するには、前回の記事で紹介したframeについて知る必要があります。
参考: WINDOW関数のframeのROWSモードについて

WINDOW関数には frameというオプションがあり、何も指定しないとこれが、
RANGE UNBOUNDED PRECEDING
になるのでした。

つまり、PARTITION BY で区切ったグループ内の、最初の行からその行と同じ値の行までを関数の適用範囲とするわけです。これにより、 last_value が指し示す最後の行とは、その行と(ORDER BYで指定した行の値が)同じ値の行まで、の範囲で見た場合の最後の行となり、その結果として同じ行のvalue列の値をそのまま返してきてきたわけです。

ちなみに、 last_value(value) ではなく、 last_value(id) とするとまた微妙に結果が変わります。
category Aで、value列が20の行が二つありますが、2行とも 3が返ってきます。

nth_value_4 の方も原理は同じです。最初の行からその行と同じ値の行まで、の範囲に含まれる行数が4行未満の場合、返す値がないのでNULLになってしまうのです。
未指定の場合 frame は ROWSモードではなくRANGEモードなので、 その行までに4行含まなくても、その行と同じ値の行まで含めて4行確保できれば値を返してくれます。
だから、結果の3行目(id:1の行)は20が返ってきているのです。

ここまでの説明で、 last_value と nth_value って使えない、もしくは使いにくいなと感じられた人も多いかと思います。
これらの関数でイメージ通りに PARTITION BY で区切った範囲の最終行や、 n番目の行の値を素直に出力したい場合は、 合わせて frameを指定してあげることで可能になります。
PARTITION BY で区切った範囲の最初から最後までなので、
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ですね。

要するに次のように書けば良いです。
(記述量が多くなるので、SELECT句内の該当行だけ書きました)

  last_value(VALUE) OVER(
    PARTITION BY
          category
      ORDER BY
      VALUE
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value_,
  nth_value(VALUE, 4) OVER(
    PARTITION BY
      category
    ORDER BY
      VALUE
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS nth_value_4

これで出力結果を載せる前に書いてた、イメージしてた通りの結果が得られます。

WINDOW関数のframeのROWSモードについて

またWINDOW関数の記事です。例によって、SQLクエリエンジンはPrestoを仮定し、僕はトレジャーデータで検証して記事を書いています。
今回は前回の記事でチラチラと登場していたframeについて説明を試みます。
(正直、僕もこれまであまり使ってこなかったので詳しくはありません。)

ドキュメントは最近ずっと参照しているここ。
参考: Window Functions

さて、改めてWINDOW関数の説明を見ると、WINDOW関数は次の形で呼び出すと書かれています。

function(args) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [frame]
)

function は SUMとか ROW_NUMBER などのことですね。
大括弧書きの部分はオプションなので省略可能です。よく見ると、ORDER BY の次に [frame] というのがあります。これが今回の記事のテーマです。

WINDOW関数で集計する時は、まず各レコードを、PARTITION BY で指定した列の値によってグループ分け、ORDER BY で指定した列と、指定したソート順(ASC/DESC)によって整列します。
そして、 「frame で指定した範囲」で集計するのです。

前回の記事で見た通りframe を何も指定しないと、ORDER BY がなければPARTITION BY で区切ったグループ全体を集計し、ORDER BYがあれば、PARTITION BYで区切ったグループ内の最初の行から、その行と同順位の行までを集計します。

これが、frameを指定することで、 ORDER BYを指定した上でも、「PARTITION BY で区切ったグループ全体」とか、「その行からPARTITION BY で区切った最後の行まで」とか、「その行の3行前から5行後ろまで」など、さまざまなレンジで集計ができます。これによって「直近7レコードの移動平均」みたいなこともできるわけですね。

frameの指定方法ですが、ドキュメントには次のように記載されています。(次の2行のどちらか)

{RANGE|ROWS} frame_start
{RANGE|ROWS} BETWEEN frame_start AND frame_end

最初に、RANGEモードかROWSモードをを指定し、1番目の記法では開始行(frame_start)のみ指定、2番目の記法では開始行(frame_start)と終了行(frame_end)を指定します。1番目の記法を採用した場合は、終了行はCURRENT ROW を指定するのと同じ動きになります。

そして、frame_start/ frame_start は 次の5種類の表記が使えます。 (ROWSモードでしかサポートされていないのが2種類あるのでRANGEモードでは3種類)

UNBOUNDED PRECEDING
expression PRECEDING  -- only allowed in ROWS mode
CURRENT ROW
expression FOLLOWING  -- only allowed in ROWS mode
UNBOUNDED FOLLOWING

UNBOUNDED PRECEDING は一番最初の行からで、UNBOUNDED FOLLOWINGは一番最後の行までの意味です。CURRENT ROW は ROWSモードであればその行まで、RANGEモードであれば、その行と同じ順位の行までです。(前回の記事で試してるのは未指定なのでデフォルトのRANGEモードの方です。)

expression PRECEDING と expression FOLLOWING は ROWSモードでしか使えませんが、expression に数字を入れて、 3 PRECEDING とか、 5 FOLLOWING のように書きます。それぞれ3行前、5行後ろの意味です。

さて、だらだら説明を書いてきたのでこの辺で一つやってみましょう。設定できるオプションが多くておそらく用途も多いROWS モードの方をやってみます。
例が無駄に複雑になるので次の実行例ではPARTITION BY は使いませんでしたが、もちろん本当は使えます。集計関数は挙動を確認しやすいSUMを例にしていますがこちらももちろん他の関数も使えます。

WITH
    sample_table AS(
        SELECT 
            id,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
                ARRAY[20, 10, 40, 30, 50, 30, 20, 60, 10, 30]
            ) AS t(
                id,
                value
            )
)
SELECT 
    id,
    value,
    SUM(value) OVER() AS sum_1,
    SUM(value) OVER(ROWS UNBOUNDED PRECEDING) AS sum_2,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sum_3,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_4,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS sum_5
FROM
    sample_table

-- 以下出力結果
id, value, sum_1, sum_2, sum_3, sum_4, sum_5
1,  20,    300,   20,    300,   30,    NULL
2,  10,    300,   30,    280,   70,    20
3,  40,    300,   70,    270,   80,    30
4,  30,    300,   100,   230,   120,   70
5,  50,    300,   150,   200,   110,   80
6,  30,    300,   180,   150,   100,   120
7,  20,    300,   200,   120,   110,   110
8,  60,    300,   260,   100,   90,    100
9,  10,    300,   270,   40,    100,   110
10, 30,    300,   300,   30,    40,    90

色々試したので順番に見ていきましょう。
sum_1 列はただの比較用です。前回の記事でも見ましたが、ORDER BY含めて何も指定しなければ全体の和になります。これは、デフォルトのRANGEモードが作動しており、行同士の間に順序が定義されていないので、全行が同一順位として扱われ、同じ順位の行まで足すという挙動により全行が足されたからです。

sum_2 以降が ROWSモードになります。 まず sum_2 ではROWSモードへの変更のみ行いました。(RANGEモードも未指定の場合の挙動は RANGE UNBOUNDED PRECEDING なので、本当にモードだけ変えています。)
ご覧の通り、ROWS モードでは ORDER BYがなくてもvalue列の累積和になっています。
これはROWSモードでは、「先頭の行から、その行まで」集計するという挙動になったからです。前回の記事でソートに指定した列に同じ値ががあった場合に少し不思議な挙動をするという話を書きましたが、ROWSモードではそれも発生せず、普通に累積和になります。

さて、この後のsum_3以降がframe機能の本番で初期設定ではなく、frameの記法を使って範囲を指定しています。
sum_3 では、 BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING として、その行から(例ではPARTITION BY使ってないけど、使ってればそのグループ内の)最後の行まで、という指定になっています。逆順の累積和っぽい挙動になっているのが見て取れると思います。

sum_4 は、 BETWEEN 1 PRECEDING AND 1 FOLLOWING と指定しており、これは1行前から1行後ろまで、合計3行の和をとっています。
例えば id 5 の行を見ると、 30+50+30 = 110 になっていますね。

sum_5 は、 BETWEEN 3 PRECEDING AND 1 PRECEDING と指定していて、これは3行前から1行前までの合計3行の和を取るものです。1行目(id:1の行)は集計対象のデータがないのでNULLになってますね。
ここで言いたかったことは、BETWEENで指定するレンジの中にその行自身を含める必要はないということです。
これを使うと、例えば日別の時系列データで、BETWEEN 7 PRECEDING AND 1 PRECEDING とすることで、前日までの過去1週間の集計値(平均など)とその日の値を比べる、といったことも可能になります。

さて、ここまでの説明で、 frameのROWSモードの使い方は概ね網羅できたのではないかと思います。集計の幅がグンと広がると思うのでぜひ使ってみてください。

一方で、 RANGE モードについてはほぼ放置しています。

Presto 以外の エンジンの中には、 実はRANGEモードをよりしっかりサポートしているものもあるそうです。その場合は、ORDER BYで指定した列の値に従って、値の差分が何以下の行を集計対象にするとか、そういう指定ができるそうです。(expression PRECEDING/ expression FOLLOWING がRANGEモードでもサポートされているとか。)

ただ、Prestoではサポートされておらず、トレジャーデータに試しに打ち込んでみても
Window frame RANGE PRECEDING is only supported with UNBOUNDED
などと、エラーが返ってくるだけなので試せていません。
(ではなぜRANGEモードをデフォルトにしているのかとか言いたいことはあるのですが)動かないものは仕方ないので、今回の記事はROWSモード中心に紹介させていただきました。
実際、ROWSモードが動けば大抵の要件には対応可能だと思います。