今日の学び@2020/3/4
SQL＆BigQuery
長文SQL解読
600行近くの既存長文SQLを解読
理解速度が確実にUPしている要件に落とし込む為、テーブル等、各条件も整理した
自分以外の人のコードを読むと、色々と学ぶ点がある基本処理とプログラミング知識を絡ませ、効率的なSQL文作成が理想
Python
- シリコンバレーエンジニアが教えるPython入門
セクション3
みんなのPythonを少しやった事があり、セクション5の途中までは、ほぼ既習
知らない知識や捉え方もあり、とてもいい感じ
Udemyは初めて使ったが、酒井さんの声と教え方が心地良く、最後までいけそうｗDS講座とうまく絡ませて、並走していこうと思う
数学
今日はお休み
所感
用事等もあり、プライベートの学び時間が減り気味
まぁ、ベターを尽くそう
週末は、目標の更新、進捗の定点観測について、整理しよう
今日の学び@2020/3/2
SQL＆BigQuery
SELECT product_id, score, -- ランク：一意 ROW_NUMBER() OVER(ORDER BY score DESC) AS ROW, -- ランク：同順許容＆飛ばす RANK() OVER(ORDER BY score DESC) AS rank, -- ランク：同順許容＆飛ばさない DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank, -- LAG(対象列,n)：ｎ行前参照 LAG(product_id) OVER(ORDER BY score DESC) AS lag1, LAG(product_id,2) OVER(ORDER BY score DESC) AS lag2, -- LEAD(対象列,n)：ｎ行後参照 LEAD(product_id) OVER(ORDER BY score DESC) AS lead1, LEAD(product_id,2) OVER(ORDER BY score DESC) AS lead2, -- ランク上位からの累計 SUM(score) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cum_score, -- 現在行と前後1行の平均 AVG(score) OVER( ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS local_avg, -- ランク最上位 FIRST_VALUE(product_id) OVER( ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_value, -- ランク最下位 LAST_VALUE(product_id) OVER( ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_value FROM `popular_products`
WITH test_table AS ( SELECT category, product_id, ROW_NUMBER() OVER( PARTITION BY category ORDER BY score DESC ) AS rank, FROM `popular_products` ) SELECT * FROM test_table -- ウインドウ関数結果の絞り込みは、関数内部で不可の為、サブクエリにして絞り込む WHERE rank <= 2 ORDER BY category, rank
SELECT -- 上位1件のランク抽出は、DISTINCT使用 DISTINCT category, FIRST_VALUE(product_id) OVER( PARTITION BY category ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS product_id, FROM `popular_products`
SELECT dt, -- 横持ち変換 MAX(CASE WHEN indicator = 'impressions' THEN val END) AS impressions, MAX(CASE WHEN indicator = 'sessions' THEN val END) AS sessions, MAX(CASE WHEN indicator = 'users' THEN val END) AS users FROM `daily_kpi` GROUP BY dt ORDER BY dt
-- めちゃくちゃメンドクサイｗ SELECT -- ラベル用列作成 CASE WHEN p.idx = 1 THEN 'q1' WHEN p.idx = 2 THEN 'q2' WHEN p.idx = 3 THEN 'q3' WHEN p.idx = 4 THEN 'q4' END AS quarter, -- 売上横持ちを縦持ち変換 CASE WHEN p.idx = 1 THEN q.q1 WHEN p.idx = 2 THEN q.q2 WHEN p.idx = 3 THEN q.q3 WHEN p.idx = 4 THEN q.q4 END AS sales FROM `sql-bigdata-recipe.rec.quarterly_sales` AS q CROSS JOIN ( SELECT -- インデックス用ピボットテーブル 1 AS idx UNION ALL SELECT 2 AS idx UNION ALL SELECT 3 AS idx UNION ALL SELECT 4 AS idx ) AS p ORDER BY quarter
-- これは慣れるべき？ WITH p AS ( SELECT purchase_id, STRING_AGG(product_id) AS product_ids, FROM `purchase_detail_log` GROUP BY purchase_id ) SELECT purchase_id, product_id FROM p CROSS JOIN UNNEST(SPLIT(product_ids,',')) AS product_id
ウインドウ関数は便利だし、難しくもないと感じる
縦持ち⇔横持ち変換は、ちょっと面倒くさい
UDFを作れないかと考えた
3章の基本は、かなり時間掛かっている
4章以降の実践で、縦持ち横持ちを実践していけば、慣れるはず
数学
所感
思ったより全体的にペースが遅いが、少しずつ改善していこう
SQLの3章は基礎練なので、4章以降の実践で楽しめる様、地道にいこう
今日の学び@2020/2/29
Python
Pandasへ入り、早くも面白いw
数学
Docker
先週の復習＆ＤＳ講座1－7
VM(Docker Toolbox)
- 新規作成：docker-machine create default --driver virtualbox
- SSH接続&起動：docker-machine ssh マシン名
- ローカルDocker
Docker超入門講座 1－6
正直、1週間ぶりで思ったより忘れていた
DS講座に無いWindowsの部分は、自分のブログを見て思い出したｗ
Windowsの場合、ローカルVirtualBox内でローカルDockerを立ち上げる
その為、Win上のエディタが使えず、やむなくVimを使った
Vimの勉強にも良さそうｗ
Dokcerは大体イメージが付いた気がするし、確かに便利そう
明日は、DS講座のGit/Hubをやる予定
忘れない様に、時折触れる事にしよう
改善点
今日は、疲れていたのか、遅めの起床
学びのエンジンが温まってきた所で、地元の親友からTELw
一瞬迷ったが、4h程歓談
とても楽しく、色々と知恵や気付きや笑いの時を過ごした
学ぶ時間は少なくなったけど、大切な時間はまた別物
あくまで楽しむ事を最優先にしている
とは言え、ここ数日だれている感もある
ストイックになって楽しむ感覚を失う事は避けつつ、もう少し目標をクリアにして、満足感ある学びにしていこう
明日は、半日予定があるので、出来る範囲で
さぁ、明日も楽しもう
今日の学び@2020/2/27
SQL＆BigQuery(BQ)
- ビッグデータ分析レシピ(参考元）
- 3章
- 3-2-5 日時データ
- 3-3 ウインドウ関数
- 3章
SELECT user_id, -- TIMESTAMP関数：型変換（string ⇒ timestamp） TIMESTAMP(regi_timestamp) AS regist_day, TIMESTAMP_ADD(TIMESTAMP(regi_timestamp),INTERVAL 1 HOUR) AS after_1_hour , TIMESTAMP_SUB(TIMESTAMP(regi_timestamp),INTERVAL 30 MIUTE) AS before_30_min, -- BQでは、TIMESTAMP ⇒ DATEの直接変換が不可 -- DATE(TIMESTAMP(timestamp))で二重変換 DATE(TIMESTAMP(timestamp))：型変換（string ⇒ timestamp） DATE(TIMESTAMP(regi_timestamp)) AS regster_date, DATE_ADD(DATE(TIMESTAMP(regi_timestamp)),INTERVAL 1 DAY) AS after_1day, DATE_SUB(DATE(TIMESTAMP(regi_timestamp)),INTERVAL 1 MONTH) AS before_1month DATE_DIFF(CURRENT_DATE,DATE(TIMESTAMP(register_stamp)),day) AS diff_days -- BQの年齢計算は、連番で数値変換後、差分計算して10000で割る -- かなり面倒なのでUDF作るｗ FLOOR( (CAST(REPLACE(SUBSTR(regi_stamp , 1,10), '-', '') as INT64) - CAST(REPLACE(birth_date , '-', '') as INT64) ) / 10000 ) as regi_age FROM `users_with_birthday`
SELECT user_id, product_id, scores, -- 全体の平均 AVG(scores) OVER() AS avg_scores, -- セグメント別平均 AVG(scores) OVER(PARTITION BY user_id) AS user_avg_scores, -- 全体平均とセグメント別平均の差 scores - AVG(scores) OVER(PARTITION BY user_id) AS diff_avg_scores FROM `review`
3－2－6 IPアドレスは、今の所、不要なのでパス
分析活用で、ウインドウ関数の便利さを感じてはいた
あれから知識と経験を得て、レシピのウインドウ関数
めちゃ便利やんこれｗ
使い倒そう
そして、面倒な基本と思っていた3章が、想像より重要と分かった
コアと言うか根本
4章以降の実践編でも、時折振り返る事になりそう
BigQueryに転送されたGoogle Analyticsログデータの確認
1つ1つクエリをかけ、GAの解説を確認
結果、大体は頭の中にマッピング出来た
GAデータがとっつきにくい原因は、特殊なSTRUCT型が原因
特に、hitsはSTRUCT型のネストが深く、検証して用途を把握した
JSONと同じですねｗ
と言うか、内部でJSONで送られてきてるのかもここら辺は、近々、GA側のデベロッパーヘルプを確認する
Python
残念ながらタイムアウトでやれず(2日目）
早くPandasをやりたいので、改善していこう
数学
- ふたたびの高校(参照元)
改善点
少しずつ、早寝早起きにしているものの、朝のペースに改善余地あり
数学から始めるのは、鉄板になってきた
頭のエンジンを温めるのに、本当にいい
PythonやJsにもっと時間を使いたいが、業務上、SQLが最優先
Pythonだけでも確保出来る様、工夫したい
さぁ、明日の朝も楽しもう
今日の学び@2020/2/26
SQL＆BigQuery(BQ)
- ビッグデータ分析レシピ(参考元）
- 3章
- 3-2 NULL伝播・0除算・母数のズレ回避
- 3章
SELECT dt, a1,a2,a3,a4, -- coalesceでNULL伝播回避 (coalesce(a1,0)+coalesce(a2,0)+coalesce(a3,0)+coalesce(a4,0)) / -- 更にsignで母数を調整👈俊逸ｗ (sign(coalesce(a1,0))+sign(coalesce(a2,0))+sign(coalesce(a3,0))+sign(coalesce(a4,0))) as avg FROM `sales`
SELECT dt, ad_id, -- 0除算対策 -- nullif(a,b) a = b ⇒ aをNULLに変換 100 * clicks / nullif(impressions,0) as ctr_as_percent FROM `sql-bigdata-recipe.rec.advertising_stats` ORDER BY dt, ad_id
分析レシピになってから、割とペースが落ちている
学び方を変えて、考えたり試したりしてるのもある
とは言え、少しペースが遅いので、改善策を考えよう
あと、昨日気になったJs-UDFのライブラリ管理
結論的には、ローカルで管理せよとの事ｗ
まぁ、その内、いい方法が見つかるだろう
ちなみに、大文字にする時間が勿体ないので、
プライベートでは小文字のままにする事にした
早く一冊通して概観を把握したい
Python
残念ながらタイムアウトでやれず
数学
改善点
今日は、用事もあり、あまり学ぶ時間を確保できず
一方、もっと学びたいと言う感覚があって良い傾向
改善自体も楽しんでいこう
今日の学び@2020/2/25
SQL＆BigQuery(BQ)
- ビッグデータ分析レシピ(参考元）
- 5章
- 途中まで通読
- 5章
- BQヘルプ
SQL記法で良い気もしたが、Jsだとライブラリが便利
Jsライブラリへの関心も沸いた
GCSにライブラリを上げてもいいが、パッケージ管理が面倒ｗ
npmとか使えないのかな。一時UDFは用途がいまいちな気がした
基本的には、永続UDFで良さそうあと、BQのSQLクエリをバージョン管理したい
と思ったら、Cloud Source Repositries(GCR)を発見
5ユーザーまで無料らしいので、ほんのり提案してみようｗ- 調べる事
- UDF-JSライブラリのパッケージ管理方法
- 各UDF、β版スクリプト、プロシージャの制約・用途
- GCRの使い方
Python
- データサイエンスのためのPython入門(参照元)
- 6-7
- Numpy
- array.shape
- np.arange([start,] stop[, step])
- np.linspace(start, stop, 等分数)
- array.copy
- Numpy
いよいよNumpy突入
Numpyの行列計算で既に面白い
Pandasは良く名前を聞いていたので、楽しみ - 6-7
数学
今日も、少しだけ
入門レベルなので、短期間でやる事を意識しよう（2回目ｗ）
改善点
今日から、メイン言語（SQL、Python、Js）にシフトし始めた
GCPは、一旦、業務と週末の気分転換程度に抑える事にしよう
1か月ちょっと前に始めたSQLが、割と伸びてて嬉しい
更に高めていこう
あと、復習用に、ブログかGoogleドライブを工夫する予定
今日は眠いので、明日の朝楽しもう
今日の学び@2020/2/24
SQL＆BigQuery(BQ)
- ビッグデータ分析レシピ(参考元）
- 3章
-- URLからの文字抽出 SELECT timestamp_log, url, -- NET関数で取得可能なpath NET.HOST(url) AS host, NET.PUBLIC_SUFFIX(url) AS suffix, NET.REG_DOMAIN(url) AS domain, -- その他の抽出 REGEXP_EXTRACT(url,'//[^/]+([^?#]+)') AS path, SPLIT(REGEXP_EXTRACT(url,'//[^/]+([^?#]+)'),'/')[SAFE_OFFSET(1)] AS path1, SPLIT(REGEXP_EXTRACT(url,'//[^/]+([^?#]+)'),'/')[SAFE_OFFSET(2)] AS path2, REGEXP_EXTRACT(url,'id=([^&]*)') AS id, FROM `log`
正規表現マストやなぁーこれはｗ
別途学ぶ必要がある。
-- 日時データ変換 SELECT -- 現在日時の取得 CURRENT_DATE AS current_dt, CURRENT_TIMESTAMP AS current_stamp, -- 日時データ変換 CAST('2020-02-01' AS date) AS dt, FORMAT_DATE('%F','2020-02-01') AS dt2, DATE('2020-02-01') AS dt3, CAST('2020-02-01 12:00:00' AS TIMESTAMP) AS timestamp, TIMESTAMP('2020-01-01 12:00:00') AS timestamp2, FROM `log`
日時データ変換方法は沢山ある。 汎用性を重視するとFORMAT_かな？
-- 各時間帯抽出 SELECT timestamp_log, TIMESTAMP(timestamp_log) as TIMESTAMP, EXTRACT(YEAR from TIMESTAMP(timestamp_log)) AS year, EXTRACT(MONTH from TIMESTAMP(timestamp_log)) AS month, EXTRACT(WEEK from TIMESTAMP(timestamp_log)) AS week, EXTRACT(HOUR from TIMESTAMP(timestamp_log)) AS hour, EXTRACT(MINUTE from TIMESTAMP(timestamp_log)) AS minute, EXTRACT(SECOND from TIMESTAMP(timestamp_log)) AS second, FROM `log`
- 4章通読
かなり面白いｗ
3章の前処理が面倒に感じたが、頻出かつ根本的な部分と分かったので固めよう
Python
- データサイエンスのためのPython入門
- 4-6
内包表記：便利ｗ
[i * 30 for i in range(0,10)]lambda関数(Jsで言う無名関数)
x = lambda 引数: 戻り値可変長引数
- *(args):list型
- **(kwargs):dect型
- kwargs.get()
- 値取得
- None対策
- 第2引数でデフォ値
- kwargs.get()
- 4-6
次の週末に、Python周りの環境構築(Docker,Github)をやろう
ふたたびの高校数学
今日は、ほとんどやっていない
入門レベルなので、短期間でやる事を意識しよう
改善点
3連休の改善点は、優先度を意識する事
今回、メイン言語（SQL、Python、Js）より、GCP、Dockerに時間を掛け過ぎた
楽しむ事を大切にしているが、優先度は重要
計画に縛られず、計画をコントロールしていきたい
Jsのモチベを上げる為、必要なUDFを洗い出すのが良いかも
優先度、ペースを少しずつ改善して、計画の確度を高めていこう
とにかく分析SQLを極めるのが最優先
