SELECT
traffic_source.name AS medium,
COUNT(DISTINCT user_pseudo_id) AS new_users
FROM
analytics_<property_id>.events_YYYYMMDD
WHERE
event_name = "first_visit"
GROUP BY
medium
SELECT
event_params.value.string_value AS page_path,
COUNT(*) AS pageviews,
COUNT(DISTINCT user_pseudo_id) AS users,
FROM
analytics_<property_id>.events_YYYYMMDD
,UNNEST(event_params) AS event_params
WHERE
event_name = 'page_view'
AND event_params.key = 'page_location'
GROUP BY
page_path
SELECT
event_name,
event_params.value.string_value as page_location,
COUNT(*) AS click_count
FROM
analytics_<property_id>.events_YYYYMMDD
,UNNEST(event_params) AS event_params
WHERE
event_name = 'click'
and event_params.key = "page_location"
GROUP BY
event_name,
page_location
（前略 意図した仕組みと作り方を伝え、懸念点等を聞いていた）
NHK APIキーの取得はできたので、大きく以下のブロックに分けて進めたいと考えています。
1. NHK APIキーをたたいて、番組名一覧を取得し、登録した番組名と一致する番組名があるかをチェックするPythonスクリプトを作成する
2. Slackに通知する仕組みを追加する
まずは1.について、コードを示してください。なお、1つのスクリプトにまとめてしまわず複数のファイルに適切に分割してくださるとありがたいです。
SELECT
event_date,
event_timestamp,
DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") as event_datetime,
event_name
FROM
analytics_<property_id>.events_20231010
WHERE
user_pseudo_id = <user_pseudo_id>
WITH duplicate_order_combinations AS(
SELECT
order_items.order_id,
DATE(TIMESTAMP_TRUNC(order_items.created_at, MONTH)) AS created_at,
CASE
WHEN products_destination.category IS NULL
THEN products.category
WHEN products.category < products_destination.category
THEN CONCAT(products.category, "-", products_destination.category)
ELSE CONCAT(products_destination.category, "-", products.category)
END AS categories,
FROM
bigquery-public-data.thelook_ecommerce.order_items
INNER JOIN
bigquery-public-data.thelook_ecommerce.products
ON order_items.product_id = products.id
LEFT JOIN
bigquery-public-data.thelook_ecommerce.order_items AS order_destination
ON order_items.order_id = order_destination.order_id
AND order_items.product_id != order_destination.product_id
LEFT JOIN
bigquery-public-data.thelook_ecommerce.products AS products_destination
ON order_destination.product_id = products_destination.id
),
order_combinations AS(
SELECT
order_id,
created_at,
categories,
FROM
duplicate_order_combinations
GROUP BY
order_id,
created_at,
categories
)
SELECT
created_at,
SPLIT(categories, "-")[offset(0)] AS category,
CASE WHEN categories LIKE "%-%"
THEN SPLIT(categories, "-")[offset(1)] ELSE NULL END AS other_category,
COUNT(*) AS _count
FROM
order_combinations
GROUP BY
created_at,
categories
レーベンシュタイン距離は、文字列間の類似度を距離として示すもの（距離が近いほど類似していることを示す）。挿入・削除・置換の各操作を合計で何回行えばもう一方の文字列に変換できるかで距離を測る。
挿入・削除・置換はそれぞれ以下の操作を指す。
・挿入：1文字を挿入する e.g. たこ → たいこ ・削除：1文字を削除する e.g. たいこ → たこ ・置換：1文字を置換する e.g. たこ → たて
これらの操作を最小の回数だけ行い、もう一方の単語に変換する。
例えば「とまと」と「たまご」なら、以下のように2回置換を行うのが一番手数が少なく変換できるのでレーベンシュタイン距離は2となる（※）
「とまと」 → とをたに変換して「たまと」 → とをごに変換して「たまご」
※ 用途により挿入・削除・置換それぞれの重み付けを変えることもできるが、等しい重みとしている（以下も同様）。
レーベンシュタイン距離を求めるには
以下のような2次元配列を用意して、各セルに各単語のクロスする部分文字列間のレーベンシュタイン距離を入れていく。
左上は□（空文字列）と□（空文字列）のレーベンシュタイン距離で、同じ文字列なのでレーベンシュタイン距離は0と確定する。そこから右下に向かって順次求めていくと、最後に元々求めたかった文字列間のレーベンシュタイン距離が求まる。
具体的な流れを見ていくと
① 空文字との距離である0行目・0列目はそれぞれ削除と挿入により至るセルなので、それぞれ一つ上/左のセルの距離+1が入る
② ①以外のセルは、置換によってもたどり着けるセルのため、削除と挿入に加え置換も考える。置換については斜め上のセルの距離から+1あるいは+0となる。挿入・削除・置換のうち、一番短い距離がそのセルの値となる（なるべく短くなるような距離が求めたいため）。
③ 最終的に右下までたどり着くと、2単語間のレーベンシュタイン距離が求まったことになる。
※ 一般に文字列が長いほど距離が長くなるため、レーベンシュタイン距離同士を比較する際に文字列の長さで割って標準化する場合もある
Pythonでレーベンシュタイン距離を求める処理を書く
上記の流れを素直にそのままPythonで書いた。
def levenshtein(word_A, word_B):
# 挿入・削除・置換のコストを定義しておく
INSERT_COST = 1
DELETE_COST = 1
SUBSTITUTE_COST = 1
# 2次元配列を用意しておく
distances = []
len_A = len(word_A)
len_B = len(word_B)
dp = [[0] * (len_B + 1) for _ in range(len_A + 1)]
# 上記①の工程
for i in range(len_A + 1):
dp[i][0] = i * INSERT_COST
for i in range(len_B + 1):
dp[0][i] = i * DELETE_COST
# 上記②の工程
for i_A in range(1, len_A + 1):
for i_B in range(1, len_B + 1):
insertion = dp[i_A - 1][i_B] + INSERT_COST
deletion = dp[i_A][i_B - 1] + DELETE_COST
substitution = (
dp[i_A - 1][i_B - 1]
if word_A[i_A - 1] == word_B[i_B - 1]
else dp[i_A - 1][i_B - 1] + SUBSTITUTE_COST
)
dp[i_A][i_B] = min(insertion, deletion, substitution)
# 上記③の工程
distance = dp[len_A][len_B] / max(len_A, len_B) # 標準化している
return distance
$ man bash
This system has been minimized by removing packages and content that are
not required on a system that users do not log into.
To restore this content, including manpages, you can run the 'unminimize'
command. You will still need to ensure the 'man-db' package is installed.
Failed to fetch <http://security.ubuntu.com/ubuntu/pool/main/s/sqlite3/libsqlite3-0_3.31.1-4ubuntu0.2_amd64.deb> 404 Not Found [IP: 91.189.91.38 80]
#12 17.78 E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?
------
executor failed running [/bin/sh -c apt install -y vim]: exit code: 100
Prepends a directory to the system PATH variable and automatically makes it available to all subsequent actions in the current job; the currently running action cannot access the updated path variable.
- name: Lint with flake8
run: poetry run flake8 .
- name: Format with black
run: poetry run black --check .
↑ Flake8 と Black を実行している。
③ pushしてワークフローが実行されるか試してみる
git push するとワークフローが実行された。ActionsタブでGitHub Actions の実行結果を確認することができる。
各ステップの名称をクリックすると、その詳細を確認することができる。例えば Format with black をクリックすると、poetry run black –check . を実行した結果を確認できる。
Run poetry run black --check .
Skipping .ipynb files as Jupyter dependencies are not installed.
You can fix this by running ``pip install black[jupyter]``
All done! ✨ 🍰 ✨
13 files would be left unchanged.
By default Black reformats the files given and/or found in place. Sometimes you need Black to just tell you what it would do without actually rewriting the Python files.
There’s two variations to this mode that are independently enabled by their respective flags. Both variations can be enabled at once.
Passing --check will make Black exit with:
・ code 0 if nothing would change;
・ code 1 if some files would be reformatted; or
・ code 123 if there was an internal error
実際に実行してみる。
$poetry run black household_accounts/calc.py --check
would reformat household_accounts/calc.py
Oh no! 💥 💔 💥
1 file would be reformatted.
$echo $?
1
$poetry run flake8 household_accounts/calc.py
household_accounts/calc.py:12:80: E501 line too long (87 > 79 characters)
household_accounts/calc.py:25:80: E501 line too long (80 > 79 characters)
household_accounts/calc.py:32:80: E501 line too long (86 > 79 characters)
Create the virtualenv inside the project’s root directory. Defaults to None.
If set to true, the virtualenv will be created and expected in a folder named .venv within the root directory of the project.
To change or otherwise add a new configuration setting, you can pass a value after the setting’s name:
poetry config virtualenvs.path /path/to/cache/directory/virtualenvs
Instead of creating a new project, Poetry can be used to ‘initialise’ a pre-populated directory. To interactively create a pyproject.toml file in directory pre-existing-project:
cd pre-existing-project poetry init
※ BigQueryのDATE型での特定の日時の書き方は以下を参照： BigQueryリファレンス: Date functions
対象のstartTimeがTIMESTAMP型だという認識がそもそも持てていない時の失敗。（ No matching signature for operator = for argument types: TIMESTAMP, DATE. と怒られる）
A TIMESTAMP object represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time with microsecond precision.
TIMESTAMPはマイクロ秒の精度であることがわかる。範囲としては 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 UTCとある。
そのため、記載したクエリだと2016-07-01の23時59分59.5秒のデータなどが範囲に含まれないことになってしまう。
from google.cloud import bigquery
def query_stackoverflow(request):
client = bigquery.Client()
query_job = client.query(
"""
SELECT
CONCAT(
'https://stackoverflow.com/questions/',
CAST(id as STRING)) as url,
view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10"""
)
results = query_job.result() # Waits for job to complete.
for row in results:
print("{} : {} views".format(row.url, row.view_count))
return "finish!"
requirements.txt
google-cloud-bigquery>=1.28.0
② 関数をテストする
200が返ってきてprint関数が出力されていることが確認できる。
つまずいた点
上記手順で実行できるまでにつまずいた点を記載する。 1) requirements.txtの不足
requirements.txtを記載していなかったところ、以下のようなエラーが出た。
"/workspace/main.py", line 1, in from google.cloud import bigquery ImportError:
cannot import name 'bigquery' from 'google.cloud' (unknown location)
上記の課題（デメリット）を解決するには、以下の情報が整理されれば良いと考えた。 ① 学びたい分野として何があって ② その分野ごとに取り組みたいことに何があり、いつ着手する予定で ③ 各取り組み内容に対して今日やるタスクは何か
これを整理するために、3つのテーブルを親 – 子 – 孫の関係でリンクさせて管理するようにした。
重複レコードが許されたり正規化されていなかったりするので、イメージ図
