BigQuery で最大最小が overlap しているものをまとめる(その最大最小を得る)
背景
最小と最大のペアの集合があるとする。
例えば、[最小, 最大]とすると、
[1, 2], [3, 4], [4, 5], [6, 7], [6, 8], [9, 10]
というような集合。
[3, 4], [4, 5]
は、まとめて、
[3, 5]
にしたい。
他にも、
[6, 7], [6, 8]
も、まとめて、
[6, 8]
にしたい。
最終的に、
[1, 2], [3, 5], [6, 8], [9, 10]
と出力したい。
つまり、ある要素の最大が、別の要素の最小値と同じかそれより大きい場合は、 その2つの要素を一つの要素にまとめたいということがある。
そのクエリ
WITH sample_data as ( SELECT s.a AS a, s.z as z FROM ( SELECT [ STRUCT(1 as a, 2 AS z), STRUCT(3 as a, 4 AS z), STRUCT(4 as a, 5 AS z), STRUCT(6 as a, 7 AS z), STRUCT(6 as a, 8 AS z), STRUCT(9 as a, 10 AS z) ] _a) JOIN UNNEST(_a) s), w as ( SELECT a, z, LAG(z) OVER (ORDER BY a, z) AS pre_z, LAG(a) OVER (ORDER BY z desc, a desc) AS post_a FROM sample_data ORDER BY a, z), head AS ( SELECT DISTINCT a FROM w WHERE pre_z IS NULL OR a > pre_z), tail AS ( SELECT DISTINCT z FROM w WHERE post_a IS NULL OR post_a > z), head_tail AS ( SELECT a, MIN(z) AS z FROM head CROSS JOIN tail WHERE a <= z GROUP BY a ) -- -- SELECT * FROM head_tail
解説
ウィンドウ関数を使って、並べた一つ上(もしくはひとつ下の)最大値(最小値)がその列の最小値より小さい(最大値より大きい)場合、まとめた要素の最小値(最大値)となる。
統計でいう treatment
treatment とは、因子の組み合わせでできる(実験の)集計グループのー要素。
参考
夏休みの宿題のスプリントプランニング
背景と導入
夏休みの宿題のようなすぐに終わらない課題を計画的に行うのは非常に難しい。受験もまた同様である。
アジャイルでは時間に対してタスクを割り当てる。この考えを導入する。予定が分かっている直近の2,3日の時間に対して優先順位の高い勉強を割り当てる。その日の割り当てられた勉強が終わればフリータイムだ。
やること
タイムボックスの見積もり
- 一日に勉強できる時間を割り出してもらう。例:10時間
タスクの見積もり
- 勉強する内容を量(ページ毎)とかかる時間の見積もり(単位は時間)をタスクとしてポストイットに書いてもらう。
- 0.5時間以外の端数、例えば1ページ10分くらいのものは3倍した量をタスクとして、ポストイットにする。
- 逆に2時間を超えるものは2時間以下に抑えるように分割する。
- 上記の作業は10分以内に終えるようにする。
計画
- 2~3日後までの予定をたてる。以下は一日毎の話である。
- 既に何か予定(習い事やプール、映画、花火なども含め)があれば、1日の勉強時間から引く。
- その勉強可能な時間×0.6をかけた時間を算出する。例えば10時間なら6時間となる。習い事で5時間使うなら5時間に対して0.6をかけて3時間となる。
- 毎日やる勉強と夏休みの宿題などがこの時間に達するように、貼っていく。
- 合計してその一日の合計に達していれば終わり。
- そんなに時間はかからないと思うが5分くらいでまとめる。
実行
- その日のタスクが終了すれば、残りはフリータイムで、テレビでもゲームでもやってもいい。
- もちろん次の日のタスクをやって、次の日のタスクの終了を早めてもいい。
- 夜に、計画を終わったかどうかの確認レビューと、振り返りを瞬時にすます。
- もし終わっていないものがあれば次の日に持ち越される。 (特に時間に関して)改善点などの振り返りを聞く。特になれば終わる。
- もし明日の予定が変更されたなら明日の計画の変更をする。
- 明後日の予定も計画する。
- 全体を5分くらいですます。長くても10分。
注意点
- その日のタスクが全て終了したからといって追加されることはない。
- もしやった場合は次のタスクを減らす
- 長期的な計画はたてない。時間があって、そこに勉強時間が割り当てられる。勉強に対して時間を割り当てない。
- いつも時間を測る。百均タイマーが便利。
BQで最長前方共通文字列を抽出する
背景
住所などで、前方一致する共通文字列を取り出したい。
方法
考え
前方部分文字列を1文字つずつ増やしながら生成して、同じ文字数で生成した文字列が何タイプあるか調べて、1以外なら共通していないので破棄する。(ここまでが前方共通文字列)
そのうち最長のものが欲しいわけだから、その文字数の最大値で、前方部分文字列を生成するとそれが最小共通文字列となる。
例
WITH data AS ( SELECT x.latlon, x.address FROM UNNEST([ STRUCT('東京都江東区有明1' AS address, 1 as latlon), STRUCT('東京都江東区有明2' AS address, 1 as latlon), STRUCT('東京都江東区東雲' AS address, 2 as latlon), STRUCT('東京都江東区東雲1' AS address, 2 as latlon), STRUCT('東京都江東区東雲2' AS address, 2 as latlon), STRUCT('東京都江東区豊洲4' AS address, 3 as latlon) ]) AS x), n_list AS ( SELECT n FROM UNNEST(GENERATE_ARRAY(1, 30)) AS n ), substr_list AS ( SELECT latlon, n, SUBSTR(address, 1, n) address FROM data CROSS JOIN n_list ), common_n_list AS ( SELECT latlon, n, COUNT(DISTINCT address) type FROM substr_list GROUP BY latlon, n HAVING type = 1), max_common_n AS ( SELECT latlon, MAX(n) n FROM common_n_list GROUP BY latlon ) -- -- SELECT latlon, address, SUBSTR(address, 1, n) common_address FROM data JOIN max_common_n USING (latlon)
Row | latlon | address | common_address |
---|---|---|---|
1 | 1 | 東京都江東区有明1 | 東京都江東区有明 |
2 | 1 | 東京都江東区有明2 | 東京都江東区有明 |
3 | 2 | 東京都江東区東雲 | 東京都江東区東雲 |
4 | 2 | 東京都江東区東雲1 | 東京都江東区東雲 |
5 | 2 | 東京都江東区東雲2 | 東京都江東区東雲 |
6 | 3 | 東京都江東区豊洲4 | 東京都江東区豊洲4 |
colab を使って Jira cloud をOAuth で接続
背景
colab から、Jira に接続したい。
方法
RSAのカギを作る。
次のサイトの情報に従って、鍵を作成する。
$ openssl genrsa -out jira.pem 1024 $ openssl rsa -in jira.pem -pubout -out jira.pub
アプリケーションリンクを作る。
次の方法(Step1)に従ってダミーのアプリケーションリンクを作る。 ただしアプリケーションのリンクは、「歯車」→「製品」→「アプリケーションのリンク」と辿る。
ここで先 jira.pub
の内容をコピペする。
colab 用にコードをコピペする。
次のサイトの回答をコピペする。
コピペすべき内容をこちらにも貼っておく。
!pip install jira
from oauthlib.oauth1 import SIGNATURE_RSA from requests_oauthlib import OAuth1Session from jira.client import JIRA # The Consumer Key created while setting up the "Incoming Authentication" in # JIRA for the Application Link. CONSUMER_KEY = 'oauth-sample-consumer' CONSUMER_SECRET = 'dont_care' VERIFIER = 'jira_verifier' # The contents of the rsa.pem file generated (the private RSA key) RSA_KEY = """ -----BEGIN RSA PRIVATE KEY----- -----END RSA PRIVATE KEY----- """ # The URLs for the JIRA instance JIRA_SERVER = 'https://YOUR_JIRA_SERVER' REQUEST_TOKEN_URL = JIRA_SERVER + '/plugins/servlet/oauth/request-token' AUTHORIZE_URL = JIRA_SERVER + '/plugins/servlet/oauth/authorize' ACCESS_TOKEN_URL = JIRA_SERVER + '/plugins/servlet/oauth/access-token' # Step 1: Get a request token oauth = OAuth1Session(CONSUMER_KEY, client_secret= CONSUMER_SECRET, signature_method=SIGNATURE_RSA, rsa_key=RSA_KEY) request_token = oauth.fetch_request_token(REQUEST_TOKEN_URL) resource_owner_key = request_token['oauth_token']; resource_owner_secret = request_token['oauth_token_secret']; print("STEP 1: GET REQUEST TOKEN") print(" oauth_token={}".format(resource_owner_key)) print(" oauth_token_secret={}".format(resource_owner_secret)) print("\n") # Step 2: Get the end-user's authorization print("STEP2: AUTHORIZATION") print(" Visit to the following URL to provide authorization:") print(" {}?oauth_token={}".format(AUTHORIZE_URL, request_token['oauth_token'])) print("\n")
ここで、colab では、別のコードを一旦切って、
STEP2: AUTHORIZATION Visit to the following URL to provide authorization:
と表示されるリンクをクリックし、許可をする。 その後、次のコードとして実行する。
oauth = OAuth1Session(CONSUMER_KEY, client_secret= CONSUMER_SECRET, resource_owner_key=resource_owner_key, resource_owner_secret=resource_owner_secret, verifier=VERIFIER, signature_method=SIGNATURE_RSA, rsa_key=RSA_KEY) # Step 3: Get the access token access_token = oauth.fetch_access_token(ACCESS_TOKEN_URL) print("STEP2: GET ACCESS TOKEN") print(" oauth_token={}".format(access_token['oauth_token'])) print(" oauth_token_secret={}".format(access_token['oauth_token_secret'])) print("\n") # Now you can use the access tokens with the JIRA client. Hooray! jira = JIRA(options={'server': JIRA_SERVER}, oauth={ 'access_token': access_token['oauth_token'], 'access_token_secret': access_token['oauth_token_secret'], 'consumer_key': CONSUMER_KEY, 'key_cert': RSA_KEY }) # print all of the project keys just as an exmaple for project in jira.projects(): print(project.key)
まとめ
ドキュメントが古いのか嘘が書いてあったりして難しいがなんとか上記の方法でできる(2019/07/17現在)