/// BANGBOO BLOG ///
■21/2/21 1:00AM
BigQuery
■Big queryリファレンス標準SQLとレガシーSQLがある、違いは?
標準 SQL のクエリ構文  |  BigQuery  |  Google Cloud
標準 SQL への移行  |  BigQuery  |  Google Cloud
標準 SQL のデータ型  |  BigQuery  |  Google Cloud
レガシー SQL 関数と演算子  |  BigQuery  |  Google Cloud
レガシー SQL のデータ型  |  BigQuery  |  Google CloudBigQuery: クラウド データ ウェアハウス  |  Google Cloud(チュートリアルみたいな) 

BigQuery解説:https://beyondjapan.com/blog/2016/03/what-is-bigquery/クエリ処理のツリーアーキテクチャによる分散並列処理複数のサーバーに対してツリー状に拡がっていき、並列にサーバー上で同時に分散処理 ルートサーバ>intermediateサーバ>leafサーバBigQuery MLという機能を利用すると、機械学習モデルをCloud AI PlatformのTensorFlowなどに連携させ、クエリ結果を素早くAIと連携Lookerというデータ分析プラットフォームとの連携よりクエリ結果を、データ統合、変換、分析、可視化、レポーティングすることができ、非常に強力なBI
列指向型・カラム型データベース・カラムナストレージ(一般的なRDBMSでは行単位でデータが保存) 必要なカラムのデータを取得するだけでよく、またデータは圧縮できるhttps://dev.classmethod.jp/articles/google-bigquery-debut/
GCPプロジェクト>データセット>テーブル(行row列columnで普通のテーブル、ネイティブbigqueryテーブル/Googleドライブのような外部テーブル、SQLクエリによるビュー)ジョブは非同期で実行され、ステータスをポーリング(データの読み込み、データのエクスポート、データのクエリ、データのコピーなど)
クエリ(ウェブ UI、bq コマンド、BigQuery REST APIの方法がある、SQLと同じ?SELECT   title, answer_count, view_count FROM  `bigquery-public-data.stackoverflow.posts_questions` ORDER BY  view_count DESC LIMIT 10BigQueryはSELECT tag, time FROM [dataset_name.table_name_20151206]のように必要な列だけを選択した場合にはスキャンの幅を狭めることは可能ですが、LIMITやWHERE句には何を書いてもテーブルをフルスキャンしてしまう節約 Amaのs3に入れRedshift内でテーブルを分割した後にBigQuery
Hadoopでも使われていたGoogle開発のエンジンであるMapReduceは、非構造化データをプログラミングモデルを通して扱うが、巨大なテーブルの結合や巨大な出力結果のエクスポートも可能である半面、処理時間は数分間から数日に及んだ、だが、BigQueryは、あらかじめデータを構造化してBigQueryのテーブルに格納しておかねばならないが、ほとんどのクエリは数秒で完了する
サードパーティ ツール(データの読み込みや視覚化を行うツールなど)を使用して BigQuery のデータにアクセス可Google Cloud SDKをインストールすればコマンドラインが使える
BQは同一リージョンでないとJoinができない、ゾーンはマルチで良い
パブリックデータに直でアクセスできるSELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013`BigQuery の一般公開データセット  |  Google Cloud
 →FROM句の書き方:他のプロジェクトを指す名前にハイフンがあるとバッククォートで囲む必要がある  `other-prj`.dataset.table あるいは `other-prj.dataset.table`

■標準SQL
先頭行でレガシーか宣言 #standardSQL あるいは #legacySQLバッククォートでエスケープ、プロジェクト区切りも.(ドット)、From句のカンマはCross joinで全組合せかと思われ通常通りjoinやunionを使う事配列が使える、カラム一つに配列を入れて多元的に扱えるwithで一時テーブルを作れるexceptでカラムを除外、replaceでカラムの置き換えfunctionを作って使える 標準 SQL ユーザー定義関数  |  BigQuery  |  Google Cloud分析関数over()とwindowで計算ができる
 rank() over (order by x)は下記moreのRFMに使用している地理関数とかJSON関数とか色々関数もありそうスクリプトで変数やIfやLoopが使える 標準 SQL のスクリプト  |  BigQuery  |  Google Cloud

■レガシーSQL(標準SQLを使うのが由)予約語は角かっこを使ってエスケープ、プロジェクト区切りは:集計関数で WITHIN キーワードを使用すると、レコード内の繰り返しの値が集計?FROM句のカンマは標準SQLのCross joinとは異なりUNION ALL 演算子通常のSQL処理システムとは異なり、BigQueryは繰り返しデータの処理を前提として設計。繰り返しレコードの構造を操作するクエリを記述。その方法の1つが、FLATTEN 演算子?JOINは、INNER、[FULL|RIGHT|LEFT] OUTER、および CROSS JOIN 演算子をサポート、デフォルトINNER除外できる select + from A OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;TOP を使用するには、SELECT 句に COUNT(*) を含める分析関数over()とwindowで計算ができる?(標準SQLと同様?)functionを作って使える(標準SQLと同様?)
JSON等のネストをフラット化

■DDL データ定義言語ステートメントの使用  |  BigQuery  |  Google Cloudhttps://www.isoroot.jp/blog/1651/auto_incrementもdefaultもprimary keyもindexもshow create tableないのでは?CREATE TABLE IF NOT EXISTS bangboo_data.x_xxx (  `no` INT64 NOT NULL,  `user_no` INT64 NOT NULL,  `name` STRING,  `date` DATETIME,)

■bqコマンドはコンソールで実行できる
ブラウザで該当プロジェクトに入りコンソールボタン、下記ではスキーマをJSONで取得できるbq show --schema --format=prettyjson myProject:myDataset.tbl001

■データアップロード時のスキーマ指定
自動検出はFirestore、Datastore、Avro、Parquet、ORCだけ?ほぼ手動のutf-8のcsvかjsonlかを使う形コンソールで手動スキーマ指定可(jsonスキーマを張付ける)、modeは省略可でデフォはnullable、JSONスキーマファイルupはaqコマンドのみ可、ローカルからup時のコマンドとスキーマ例↓bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json[  {    "description": "quarter",    "mode": "REQUIRED",    "name": "qtr",    "type": "STRING"  },  {    "description": "total sales",    "mode": "NULLABLE",    "name": "sales",    "type": "FLOAT"  }]なお一旦Google Cloud Storageに放り込んでからやると高速 BigQueryにデータをバッチでインポートする - Qiita
COUNT DISTINCTだが、BigQueryでは概算値が返ってくる??。正確な値が必要な場合は、GROUP EACH BYとCOUNT(*)を組み合わせるhttps://www.buildinsider.net/web/bigquery/01

■BQはUTC
ScheduledQueryを終了日6/9 13:00JSTで即時設定→6/9 01:20UTCで実行された(終了時間にJST/UTCの考慮が必要か→SQ実行時間設定についてはJSTかUTCに注意するだけ)実行履歴はUTCのためJSTに読み替える必要がある(UTCはJSTの-9時間)
■BigQuery機能
///クエリ結果を別テーブルに書き込む
その他>クエリの設定>クエリ結果の宛先テーブルを設定する
BigQueryではSELECT結果を他テーブルにInsert / テーブル洗い替えなどができる - コード日進月歩 (hateblo.jp)
クエリ結果の書き込み  |  BigQuery  |  Google Cloud

///Federated Query
スプレッドシートやGCSの外部ソースをBigQueryで範囲の書き方:シート1!A1:B100Auto detectにするとHeader skipを1にして1行目をカラム名として使うといい
注意) シートで構成を変えると滅茶苦茶になる 空欄のセルはnullになる 使う人はBQへもスプレッドシートへも両方権限が必要
///パラメータ(変数)を使う--parameter=min_count:INT64:250SELECT word FROM `prj.ds.t` WHERE AND count >= @min_countパラメータ化されたクエリの実行  |  BigQuery  |  Google Cloud

こういう感じでも使えるのでは
WITH params AS ( SELECT @sheetInput AS p),tmp_pre_processed_src AS ( SELECT * FROM src)SELECT * FROM tmp_pre_processed_src,paramsWHERE tmp_pre_processed_src.a = p
///*を受ける_TABLE_SUFFIXを使う(複数テーブルだとunion allになる)SELECT year FROM `bigquery-public-data.ds.gsod19*`WHERE _TABLE_SUFFIX BETWEEN '29' and '35'ワイルドカード テーブルを使用した複数テーブルに対するクエリ  |  BigQuery  |  Google Cloud
 BTWで絞らないと全結合で課金が厳しいかも

where _TABLE_FUFFIX between format_date('%Y%m%d', date_sub(current_date(), interval 3 day))
 and format_date('%Y%m%d', current_date())

///時間のパラメータを使う
select * from mytable_{run_time-1h|"%Y%m%d"}実行時間run_time(UTC)から1時間引いた日→mytable_20180214クエリのスケジューリング  |  BigQuery  |  Google Cloud

///動的にテーブル名を指定してcreate table
パラメータや変数や_TABLE_FUFFIXだけでは難しい。変数はテーブル名とは解釈されない、_table_fuffixはselect分のfrom句に入れwhere句で内容を指定するがcreate分は無理、execute immediateを用いるDECLARE t STRING;SET t = (SELECT CONCAT('x_emp_at', FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY))));EXECUTE IMMEDIATE format('CREATE OR REPLACE TABLE `%s` AS SELECT * FROM `prj.bangboo_data.x_employee`', t);
ScheduledQueryでは出力テーブルの指定が可能でテーブル指定例:table001_{run_time-1h|"%Y%m%d"}でOK、なおSQL内にはrun_timeが使用できない
///既存のテーブルをコピー(CREATE OR REPLACE TABLEもあり)CREATE TABLE IF NOT EXISTS bangboo_data.x_employee_copy (  `no` INT64 NOT NULL,  `name` STRING,) asselect * from `prj.bangboo_data.x_employee`標準 SQL のクエリ構文  |  BigQuery  |  Google Cloudデータ定義言語ステートメントの使用  |  BigQuery  |  Google Cloud

///timestampとdatetime
datetime型カラムにはCURRENT_DATETIME()、timestamp型カラムにはCURRENT_TIMESTAMP()を使う
 timestampはUTC、datetimeはローカル的で地域指定ができる
 直近3分
 SELECT * FROM `aaa.ds.tbl111`
 WHERE `date` > DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 3 MINUTE)

//stringとdate
func_approved_routine_a('2021-10-31') 引数がstring型
func_approved_routine_a("2021-10-31") 引数がdate型

///日付のキャスト
CAST(date AS STRING)
TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month))
BigQueryのStandardSQLで日付(date, datetime, timestamp)を変換する方法 - 寝ても覚めてもこんぴうた (hatenablog.com)
Bigqueryの日時に関係する関数全部試してみた ①Date編 - Qiita

///timeで入っているものを日でサマるSQLselectcount(table_id),sum(size_bytes),date(record_time) as record_dayfrom bq_metadatawhere record_time > TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 3 month))group by record_dayorder by record_day DESC
///パーティション
パーティション分割テーブルの概要  |  BigQuery  |  Google Cloud
BigQueryのStandardSQLで日付(date, datetime, timestamp)を変換する方法 - 寝ても覚めてもこんぴうた (hatenablog.com)

パーティション分割テーブルは2種類:パーティショニングとシャーディング
1)パーティショニング
BigQueryでパーティション分割テーブルを作成する - goodbyegangsterのブログ (hatenablog.com) を見よ
パーティショニングは事前に作っておくこと
CREATE TABLE sample.n225 (   trading_day DATE NOT NULL OPTIONS(description="取引日"),  closing_quotation NUMERIC NOT NULL OPTIONS(description="終値"),  opening_quotation NUMERIC NOT NULL OPTIONS(description="始値"),  high NUMERIC NOT NULL OPTIONS(description="高値"),  low NUMERIC NOT NULL OPTIONS(description="低値")) PARTITION BY  DATE_TRUNC(trading_day, MONTH)OPTIONS (  partition_expiration_days=1825,  require_partition_filter=true,  friendly_name="日経225時系列データ",  description="月別パーティションされた、201901から202107までの日経225時系列データ",  labels=[("environ", "dev")])

クエリはpartitioned byのヤツで絞れば良いselect * from aaa_history wehre#ParticionIDで絞る(つーかpartitioned byのヤツで日付をキャストしてUTCをJST日付にdate(rec_time) = date(datetime_add(datetime "2000-10-10 00:00:00" interval -9 hour))
AND#実際の時間で絞る、パーティションが日付区切りなので時間検索だけなら全件検索になるdatetime(rec_time) between datetime_add(datetime "2000-10-10 00:00:00" interval -9 hour)
 and datetime_add(datetime "2000-10-10 00:59:59" interval -9 hour)
2)シャーディング
検討中… 上のパーティションも

シャーディングは_TABLE_SUFFIXを使ったり、テーブル名にハードコーディングする。
日付のキャスト select * from `task_*` where _TABLE_SUFFIX = REPLACE(CAST(date AS STRING), '-', '')
///UNNEST
UNNESTを知らないとBigQueryを使えない? | 4番は司令塔 (pep4.net)
BigqueryでUNNESTを使いこなせ!クエリ効率100% | by Eureka EngineeringARRAY を一組の行にフラット化するには、UNNEST 演算子を使用
SELECT id, title FROM games, UNNEST(titles) AS title
idtitles1[skyrim, fortnite]2[atvvsmx, mario]↓フラット化
idtitle1skyrim1fortnite2atvvsmx2mario
sql - How to query multiple nested fields in Bigquery? - Stack Overflow
Unnestでもflattenができず空欄ができる場合、結局left join
 空を含むカラムはSelectに残し、repeatedのカラムはleft joinでくっつける
 VariantsをunnestしてるがPricesもrepeatedなのでleft joinのものを出している
  repeatedもarrayと同じらしいが、、、cross joinやarray_to_stringもやったが駄目だった
   なおrepeated以外はunnestが効かないそれでも駄目ならselect句の指定方法やwhere句で絞ると空欄が抜けたよ
select Productid,Variants.SKU,Variants.Size ,Prices.Currency,Prices.Country from `ga-export-0000.feed.feed_dev` ,UNNEST (Variants) AS Variants LEFT JOIN UNNEST(Variants.Prices) as Prices
///ARRAY型とSTRUCT型
標準 SQL のデータ型  |  BigQuery  |  Google Cloudとある古典的SQLおじさんのBigQuery入門:ARRAY型とSTRUCT型 | DevelopersIO (classmethod.jp)
BigQueryのSTRUCT型とうまく付き合う - QiitaArrayは上のUnnestを参照。
Structは構造体型。順序付きで親子の構造を持つ。各フィールドはデータ型(必須)とフィールド名(オプション)を持つ。
array型 unnestできる、[]なのでarray_length()で数が取れるstruct型 unnestできる、ネストを含みスキーマでrecord型と表記される
struct型(record型)は子や孫でヒットすれば親を含めて表示されてしまう見やすくするため*ではなく、カラムを特定すると空欄が表示されなくなり親が出なくなり理解しやすくなる(必ずカラム指定したい)
例)権限が変わっていないかの確認する等降順で最新の日付のアイテムを見る、そして最終ページの古い日付のアイテムを見るそしてそれらを比較するselect record_time, name, asset_type, m, b.rolefrom cai_iam_policy_history,unnest(iam_policy.bindings) b,unnest(b.members) mwhere record_time between timestamp('2021-05-01') and timestamp('2021-06-30')and b.role in ("roles/bigquery.dataViewer", "roles/bigquery/jobUser")and m like '%ketsu@bangboo.com%'and ancestor_path like '%ketsuproject%'order by record_time descSQL解説)struct型が沢山入っていても全部unnestしfromに入れればいい from a, unnest(iam_policy.bindings) b, unnest(b.members) m    unnest(iam_policy)はできないので2階層目から  一つ階層上ではunnest時に別名を付けて下の階層はその別名でunneststruct型の子へは.ドットで指定すればいい、フラットでなくてもbでも取得ができる

通常SQLは「表.カラム」だが「親カラム.子カラム」なので、出元がどこかテーブルを探すかスキーマ内を探すかで迷う

///json_extract, json_extract_scalar
2番目の引数はパス
BigQueryでの複雑なJSON文字列の扱い方と注意点 - Qiita
標準 SQL の JSON 関数  |  BigQuery  |  Google Cloud

with t as (
    SELECT unco_data AS col_1 FROM `kuso`
    WHERE date = "2021-08-04"
)
SELECT
    json_extract(col_1, '$.color') as unco_color,
    json_extract(col_1, '$.temperature') as temperature,    json_extract(col_1, '$.fart.times[0].stink') as first_stink,
FROM t
///Pivot
BigQueryでPreviewになったPIVOTとUNPIVOTを試す | DevelopersIO (classmethod.jp)
【SQL】クロス集計を扱う。PIVOT句とUNPIVOT句についてコードを踏まえて解説。 | ポテパンスタイル (potepan.com)
集計をして行を列に変換(生ログをある単位でまとめカラムにする)

--toolのactiveがonなら1、nullなら0でユーザAとBの状況を見る
SELECT * FROM (
 SELECT user, tool, active FROM `tools`)
PIVOT(
 MAX( IF (active IS NOT NULL, 1, 0))
 FOR user IN ("a", "b")
)

tool a b
------------
axe 1 0
sword 0 1

※参考にピボットテーブル集計して行を列に変換、生ログをある単位でまとめる 生ログが「日 店 金額」の場合 ↓ ピボットで「日 金額 (店1 店2 店3)」にする等で、各項目を行と列と値に配置し直す
BigQueryでPreviewになったPIVOTとUNPIVOTを試す | DevelopersIO (classmethod.jp)
PIVOTの中は定数でないとだめだが、
Execute Immediate なら動的にイケる、
がGoogleSheetのConnectedSheetではサポートされておらず無理という罠
///新旧の差分比較したいデータの共通してい部分で外部結合をしてnull部分を探すWITH old_e AS (  SELECT * FROM status WHERE user IN ('a@old.com') ), new_e AS (  SELECT * FROM status WHERE user IN ('a@new.com') )SELECT * FROM old_e o FULL OUTER JOIN new_e n ON o.id = n.id AND o.date = n.date WHERE o.id is null OR n.id is null ORDER BY o.id, o.date
///REGEXP_REPLACE 正規表現で文字を削除
WITH markdown AS  (SELECT "# Heading" as heading  UNION ALL  SELECT "# Another Heading" as heading)SELECT  REGEXP_REPLACE(heading, r"^# He", "") AS htmlFROM markdown;標準 SQL の文字列関数  |  BigQuery  |  Google Cloud

///スラッシュで分割するとarrayになるのでオフセットで取得select SPLIT(path, "/")[OFFSET(3)] from www

スラッシュの最後を取るARRAY_REVERSE(SPLIT(aaa, "/"))[SAFE_OFFSET(0)]引き当てが無い場合はSAFE_OFFSETはNullを返し、OFFSETはエラーを返す

BigQueryの標準SQLでGROUP_CONCATしたいときはSTRING_AGG - GAミント至上主義 (hatenablog.com)
逆にまとめるには
SELECT type, STRING_AGG(DISTINCT name) FROM testData GROUP BY type;赤身 | ブリ,いわし,アジ,マグロ,カツオ,サバ白身 | タイ,タラ,フグ,サケ
///Job kill
CALL BQ.JOBS.CANCEL('job_id')
CALL BQ.JOBS.CANCEL('project_id.job_id')

ジョブIDの取得
SELECT
 project_id,
 job_id,
 user_email,
 creation_time,
 start_time,
 --query, total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
 --`region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
 --`region-us`.INFORMATION_SCHEMA.JOBS_BY_FOLDER
 --`region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE state != "DONE"
 --state = "RUNNING"
 --state = "PENDING"
AND user_email = 'my@email.com'
AND project_id = 'paa'
AND start_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MINUTE)
AND total_slot_ms > (1000 * 30)
AND PARTITIONDATE BETWEEN '2021-01-01' AND '2021-01-02'
 --PARTITIONTIME BETWEEN TIMESTAMP('2021-01-01') AND TIMESTAMP('2021-01-02')

///upsert(アップデートか新規インサートhttps://swfz.hatenablog.com/entry/2021/02/08/195024MERGE aaa target USING tmptbl src ON target.time = src.timeWHEN MATCHED AND src.satus = 'rejected' THEN DELETEWHEN MATCHED THEN UPDATE SET ...WHEN NOT MATCHED THEN INSERT ROW
///window関数
集約関数(GROUP BY)だと個別データは出力されず集計データだけでるがwindow関数だと集計データが個別データにouter joinされた形で出力される分析関数のコンセプト  |  BigQuery  |  Google Cloud
SELECT  deptname,  id,  salary,  AVG(salary) OVER (PARTITION BY deptname)FROM emp;
  deptname  | id | salary |  avg_salary-----------+-------+--------+------------- dev        | 11 |   5200 |        5020 dev        |  7 |   4200 |        5020 dev        |  9 |   4500 |        5020 dev        |  8 |   6000 |        5020 dev        | 10 |   5200 |        5020 hr         |  5 |   3500 |        3700 hr         |  2 |   3900 |        3700 sales      |  3 |   4800 |        4866 sales      |  1 |   5000 |        4866 sales      |  4 |   4800 |        4866
deptnameでグループしそのsalaryの集計のAVGが出ている
下のようにover()が空でも良い、4900は大体
SELECT  deptname,  id,  salary,  AVG(salary) OVER () AS avgFROM emp;

  deptname  | id | salary |  avg-----------+-------+--------+------------- dev        | 11 |   5200 |        4900 dev        |  7 |   4200 |        4900 dev        |  9 |   4500 |        4900 dev        |  8 |   6000 |        4900 dev        | 10 |   5200 |        4900 hr         |  5 |   3500 |        4900 hr         |  2 |   3900 |        4900 sales      |  3 |   4800 |        4900 sales      |  1 |   5000 |        4900 sales      |  4 |   4800 |        4900
関数としては集計関数がそのまま使えるようだOVERはwindow関数を使う宣言、OVERの後にどのようにwindowを作るのかを定義PARTITIONでwindowでつまりどの範囲でグループを作るか指定
 AVG(salary) OVER (PARTITION BY deptname, sub_deptname) でサブデプト単位での平均となる

///誰が実行しているかをセッションユーザで出す
標準 SQL のセキュリティ関数  |  BigQuery  |  Google Cloud
SELECT SESSION_USER() as user;+----------------------+| user                 |+----------------------+| jdoe@example.com     |+----------------------+
///プログラムで使う
https://googleapis.dev/python/bigquery/latestfrom google.cloud import bigqueryclient = bigquery.Client()QUERY = ('SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013`')query_job = client.query(QUERY)rows = query_job.result()for row in rows:    print(row.name)

///承認済みビュー
authorized viewを設定するとそのviewを対象とする権限だけ必要で権限をさかのぼり付与しなくていい(通常のviewは参照元の権限も必要)https://qiita.com/t_odash/items/65b9c965031c3120b765 被参照の元テーブル側に許可するview名を設定する

■saturationの場合、詰まっている、サチっている
対象にクエリを発行 select 1
同プロジェクトの他のテーブルにクエリを発行 select 1
別プロジェクトから対象にクエリを発行 select 1
reservationsのoverviewを見る
対象のSQLを発行
別のプロジェクトで同SQLを発行
 時間を比べる
Google側の問題と思われるときはGoogleのサポートへGo
Google Could Status Google Cloud Status Dashboard

///Authrized function
認可済み関数の作成  |  BigQuery  |  Google CloudSELECT `b-sandbox`.test_ds.count_row(1); で実行できるUDFやテーブル関数のルーティンを承認しておくと誰からでも使える(ビューと違い権限管理できずセキュリティがズブズブになると思われ) target_prj.trg_dsに受け入れる関数を共有指定する形 UDFは戻り値がある、テーブル関数は副問い合わせとして使う形か
■SQLはカラム数の増加数で構成考える?
left outer joinはカラム数がカラム数の合計から共通のjoin onのカラム数を引いた数(行数はleftに同じ)
 full outer join はカラム数がカラム数の合計から共通のjoin onのカラム数を引いた数(行数はleftの要素数にrightの要素数を合計したもの)
unionは重複を除外し表を足し合わせるため行数が両表の合計行数(カラム数は合致必要でカラム数は変わらない)unian allは重複を除外せず表を足し合わせるため行数が両表の合計行数(カラム数は合致必要でカラム数は変わらない)cross joinはカラム数が両表のカラム数の合計、行数は両表の行数の掛け算
 再帰的にSQL処理はcross joinし条件を付けるか?  標準SQLのFrom句のカンマはcross joinとなる
with句は副問い合わせを見やすくしたもの
distinctで(組み合わせで)一意になる行のみにし重複を省く
UNION とUNION ALLの違い - Qiita
CROSS JOIN (クロス結合)を使ってデータを取得する - JOIN (結合)を使いこなそう - SQL Server 入門 (sql55.com)
SQLのDISTINCTとは?(OracleやMySQLで使用する方法) | IT職種コラム (it-kyujin.jp)

■課金
クエリ課金:使用しているプロジェクトで課金、データの置き場所ではない
 定額:$2000/100slot/m(全プロジェクトでスロットを共有)、オンデマンド:$5/T=2Gスキャンで1円位 量を減らす:カラムを減らす、パーティショニング
データ保管課金:データ量
 $1/50G/m
6,000スロットを使うBigQueryのリソース配分最適化への挑戦 (plaid.co.jp)
サポートを付けるとGoogleに聞き放題になったりする

■権限
事前定義ロールと権限  |  BigQuery  |  Google Cloud
job user:select文クエリ実行だけでもジョブでjob userとdata viewerが要る(data viewerだけでは不足)
 課金プロジェクトでjob userを持ち、参照先プロジェクトでdata viewerを持つという権限構成だから
IAMかデータセット/tblに必要な権限を付与する
 data editorでも自分で作成したものは自分がOwnerになり削除や変更権限がある

■IAM(Identity and Access Management) 
https://www.bangboo.com/cms/blog/page_347.html


(More)
Comment (0)

■21/2/10 7:30PM
Python
おッPythonやるのか?

ファイル拡張子oppython.py デフォUTF-8、全部オブジェクト(list,dict,set等のミュータブルなら参照になる点に注意、必要ならcopy())#コメント、ドキュメントストリング(三連引用符):"""そのまま表示""" print mymod.__doc__で見れる変数型不要:p = 500 * num、定数はない文字繰り返し、キャスト:"文字列" * 4 + str(p) + "Hi\nお元気ですか?\nSee u"raw文字列でescしない:print(r"インストール先は c:\\code\python\bin です")文字数:len("東京都")→3
文字列[開始:終了]→→ str = "Flower" print(str[1:4]) → low文字列 % (値1, 値2, ...)→→ num= "10進数では %d 、16進数では %x " % (num, num)
"xxxx{index:書式指定子}xxxx".format(値)→→ "名は{:<8s}で年は{:>3d}で".format(name, age)f"xxxx{値:書式指定子}xxxx"→→ f"名は{name:<8s}で年は{age:>3d}で" 0/空の文字列''/値なしはfalse、Noneは? x = None x is None→→true?//→除算切り捨てし整数、**→べき乗
関数宣言はdef kansu():だが、その中からglobal henでグローバル変数henにアクセスできる
返り値複数はcsvでタプルになる、リストが楽か? return a,b → (a, b) あるいは return [a, b] → [a, b]
try/exceptを関数内で設定することも、逆に関数呼び出し時にも使用ができる、else, finally, raiseも使う
とほほのPython入門 - リスト・タプル・辞書 - とほほのWWW入門 (tohoho-web.com)
リストa=[1,2,3]はmap(), filter(), reduce()等が使える、set()は重複の無いリストを扱いセット同士の減算、OR、AND、XOR 操作が可能
tuple→タプルは定数リスト、更新無しならリストより速い a = 1,2,3 a = (1, 2, 3)
dict→辞書は連想配列みたいな{a:1,b:2}はitems(), keys(), valus(), iteritems(), get()を使える
lambdaは無名関数?
str_w = input('何か入力してください-->') #入力させた値を取れるが数字もstr__iter__()はnext()を持つオブジェクトを返し、next()は次の要素を返し、最後に達するとStopIteration例外を返す?yield はイテレータを返すジェネレータを定義?@デコレータは関数を実行する前後に特殊な処理を実行したい場合?withで終了処理を指定できる、ファイル読込とその後の処理とか
assertや__debug__はテストで機体通りかを確認する?
passは中身の無い関数やクラスを作成しkara.p=1で粋なり属性追加等ができる
execは引数の文字列をPythonとして実行 exec "print 'Hello'"
delはオブジェクトを削除 del x, y, z
継承やオーバーライド class MyClass2(MyClass):
多重継承class MyClassC(MyClassA, MyClassB): で纏めて使えるようになる
class MyClass:    """A simple example class"""  # 三重クォートによるコメント    def __init__(self):  # コンストラクタ        self.name = ""    def __del__(self): #インスタンスが消滅する際に呼出でコンストラクタ        print "DEL!"    def __str__(self): #文字列化        return "My name is " + self.name    def getName(self):  # getName()メソッド        return self.name    def setName(self, name):  # setName()メソッド        self.name = name
class MyClass2(MyClass):    def world(self):        print "World"class MyClass3(MyClass):    def hello(self):  # 親クラスのhello()メソッドをオーバーライド        print "HELLO"a = MyClass()  # クラスのインスタンスを生成a.setName("Tanaka")  # setName()メソッドをコールprint a.getName()    # getName()メソッドをコールprint a  #=> My name is Tanaka 文字列化b = MyClass2()  #継承b.hello()    #=> Hellob.world()    #=> Worldc = MyClass3()  #オーバーライドc.hello()    #=> HELLO
super()を使ってオーバーライドする super()は基底クラスのメソッドを継承した上で処理を拡張 super().__init__(x、y)が使えるwith構文で処理の前後で__enter__、__exit__を使う __enter__メソッドで事前処理 __exit__メソッドで事後処理if __name__ == "__main__": モジュール時の勝手実行を抑える  import helloの時hello.py 内部での __name__ は "hello"   python hello.pyのような実行時hello.py の内部の __name__ は "__main__"
from math import pi, radians→mathモジュールから特定のオブジェクト(関数/変数/クラス)をimpoimport urllib.error→urllibパッケージからerrorモジュールをimpo、パッケージはフォルダimport numpy as np→別名でしか使えなくなるnp.array()とかで モジュール=ファイル名.pyでファイルをimpoしている impo順:標準ライブラリ>サードパーティライブラリ>ローカルライブラリ(自作のライブラリ)
関数や変数:小文字スネークケース(sample_func)クラス名、例外、型変数:キャピタルパスカルケース(SampleClass)定数名:大文字アンダースコア区切り(SAMPLE_CONST)モジュール名:小文字(samplemodule, sample_module)パッケージ(フォルダ)名:小文字。アンダースコア非推奨(samplepackage)

インデントは半角スペース4つ1行半角で79文字以内トップレベルの関数やクラスは2行開けるクラス内部では1行ずつ開けてメソッド定義
ドックストリングでクラスや関数についてコメントする(慣習的にダブルクォート)
 コード中は処理についてのコメントをなくし関数化とdocstringで参照するように
 「コメント(#)とdocstring(""")の違いは?」コメントとdocstringについて
 [Python]可読性を上げるための、docstringの書き方を学ぶ(NumPyスタイル) - Qiita
※参照になりコピーされない、必要ならコピー(値を入れた時点で参照が外れるので実際問題少ない?)a = []b = ab.append(1)print(a) #[1]https://qiita.com/ponnhide/items/cda0f3f7ac88262eb31ehttps://nishiohirokazu.hatenadiary.org/entry/20120125/1327461670

※テキスト選択 Shift↑or↓ で行全体 home(+fn)で行頭、end(+fn)で行末移動

【基礎一覧】Pythonの基本文法を全て解説してみた!【初心者】 (suwaru.tokyo)
Python基本文法まとめ - Qiita
とほほのPython入門 - とほほのWWW入門 (tohoho-web.com)Python入門 ~Pythonのインストール方法やPythonを使ったプログラミングの方法について解説します~ | Let'sプログラミング (javadrive.jp)
Welcome to Python.org

HTMLの中に少し埋め込めず、基本的にプログラムの中にHTMLを埋め込む:CGI(Perl然)
 さくらインターネットでPython CGI (mwsoft.jp)
WSGI Python で WSGI (Web Server Gateway Interface) に従ったシンプルな Web サーバで Hello World - Qiita
Python用Webサイト用途フレームワーク:Flask(軽量)、Django
 WSGI について — Webアプリケーションフレームワークの作り方 in Python (c-bata.link)
 GCPでどう使うかは不明だがホスティングは↓
 ウェブ ホスティング | Google Cloud 静的ウェブサイトのホスティング  |  Cloud Storage  |  Google Cloud

==============
ここで動かせるgoogle colaboratory→ Colaboratory へようこそ - Colaboratory (google.com)
コラボラトリはマークダウン Qiita マークダウン記法 一覧表・チートシート - Qiita
半角スペース2個で改行#の数で見出し*で箇条書き数字と.で番号を振る、- でリスト* or - or _ を3つ以上で水平線[ ]でチェックボックス、[x]でチェック| td | td | td |でテーブル**aaa**で太字、*aaa*で斜体~~aaa~~で打消し線[タイトル](URL)でリンク```でコードの挿入、`でインライン挿入> or >> で引用
 [^1]で注釈\バックスラッシュでマークダウンのエスケープ
==============
宗教論争(事実は同じでも他人の認知は違うので意味なし
if self.flag_ok == 1 and self.mode == '1'
↓一見で分からんなら変数名を工夫してこうやんな
if self.file_verify_completed and self.mode == GRANT_PERMISSION:

マジックナンバーを使わない(数字の方が曖昧性が無い場合も)STATUS_ERROR = -1STATUS_SUCCESS = 0self.status_error = STATUS_SUCCESS
初期値をエラー値にし、業務判定エラーでステータスを設定したらreturnで抜けるdef exFunction(self): self.status_error = STATUS_ERROR try: if XX = AAA: self.status_error = STATUS_XX_ERROR retrun self.status_error = STATUS_SUCCESS retrun except: ~エラー処理、ステータスは変更しない
エラーメッセのハードコーディングを避ける方法(ハードが場所と内容が分かり易いかも)MSG_ERROR_OLD_EMAIL = "Error: 旧メール%sです\n"e_message_list.append(MSG_ERROR_OLD_EMAIL % (old_email))self.error_message = '\n'.join(e_message_list)

ケチって分厚い本1冊にしたが全然進まぬ、薄い奴星e、?チッPython、誰がJSONじゃ~い、チェーンソー魔わすっぞ


(More)
Comment (0)

■21/1/27 3:00AM
Framework
群雄割拠か弱肉強食か政治解決か遣りたい放題の中、当事者なら自然に身につくだろうが時代に参加していない貴殿には到底分からない、シルバーブレットはなく結局は愚直しかない、どう愚直になるか/どう立ち回るかを発見するのがフレームワークだ、もしトレンドを感じればそれに乗ればよい、他にシーズがあればプロダクトアウト、ニーズがあればマーケットインだ

■フレームワーク
3C(Customer/Competitor/Company:自社)
SWOT(Strength-Weakness x Opportunity-Threat)
PEST分析(Politics/Economy/Society/Technology)
3M(人物金、Men/Material/Money)3M(ムリ:負荷が人に掛かる/ムダ:資源が余り浪費しコスト高/ムラ:負荷と浪費が交互)
7S(<3sはハード>Structure/Strategy/System、<4sはソフト>Shared value:価値観/Skill/Staff/Style:文化)
VRIO(Value/Rarity/Inimitability:模範困難性/Organization)
MVV(Mission:使命why/Vision:将来像what/Value:価値観how)
マーケ4P4C(4p:product/price/place/promotion, 4c:customer cost/customer cost/convenience/communication)
QCD(Quarity品質/Costコスト/Delivery納期)
ポーターの5forces(Competitive Rivalry:競争企業間の敵対関係/Supplier Power:売り手の交渉力/Buyer Power:買い手=顧客の交渉力/Threat of Substitution:代替品の脅威/Threat of New Entry:新規参入業者の脅威)コトラーのSTP(segmentation:顧客やニーズや特性等で細分化/targeting:セグメ選択/positioning:勝てる位置取り)
競争戦略ポジショニング (コストや差別化や集中戦略等で業界ポジを練る)
PPM:product portfolio mgt(市場成長率xシェア->問題児:花形に育てる/花形:継続投資/金のなる木:投資無し/負け犬:撤退検討)
製品ライフサイクル:PLC(導入期:認知度と需要up/成長期:製品多角化販路拡大/成熟期:差別化とコストダウン/衰退期:選択と集中効率化)
イノベータ理論(Innovators/Early Adopters/Early Majority/Late Majority/Laggards:16%キャズム:段階で戦略を変えていく)
RFM(recency/frequency/monetary->優良顧客を見つけよりマーケ)
ポーターのバリューチェーン(企画/開発/購買/製造/物流/販売/サービス/人事の各々での価値->VRIOで分析)
アンゾフの成長マトリクス(既存市場浸透:既存市場x既存商品/新市場開拓:新規市場x既存商品/新商品開発:既存市場x新規商品/多角化:新規市場x新規商品)
TOWS(クロスSWOT分析:SWOTをSWOTする->SWOTで現状分析、さらにSWOTで戦略化)
SCAMPER(アイデアの展開->substitute代用/combine結合/adapt応用/modify修正-magnify拡大/put to other uses転用/elininate削除-minify削減/reverse逆転-rearrange再編集)
ECRS:イクルス(改善策->排除/統合/交換/簡素)

ギャップ分析(As Is/To Be<-間にaction)
特性要因図:フィッシュボーンチャート(要因を網羅的に調べる、他にも親和図法やマインドマップ)
意思決定マトリックス(縦軸に候補、評価基準を横軸、評価基準に重みをつけて点を出し、合計の総合点で判断)
CS/CE分析(Customer satisfaction/Customer expectation->各機能のcsをceに合わせる、ce高cs低の改善)
リスク評価マップ(インパクトx不確実性、インパクト大に対処し小は静観、不確実性が高いと注意が必要<-pest/5f)
AIDMA...etc...
ユダヤの商法:78対22の法則で上手くいく、契約主義で約束を守るケイパビリティ戦略:ビジネスプロセスを能力化、スピード/整合性/明瞭性/俊敏性/革新性が上がる、CEOがツールや組織を検討ポジショニング戦略:競合との位置付け、STP分析をしどうポジションを取り差別化するかタイムベース競争戦略:時間こそが希少資源、スピードが一番顧客満足が高い、数稼ぐアダプティブ型戦略:多くの新しいアプローチを試し最も有望なものを拡大展開、PDCA、やらな分からん時代
ランチェスターの法則:
奇襲/武器/集中、逆にシェアトップで武器も兵数あると模倣し広域戦 自分を知る、競合を知る、シェア1位を知る 地域限定、顧客志向営業、販路、陽動奇襲の企画、強みに集中
逆転の競争戦略:
業界破壊者(枠を壊す)/侵入者(他業種からの参入)/挑戦者(競合が攻撃)がトップシェア企業を脅かす1)企業資産の負債化=リーダーが持つ競争優位の源泉を攻めリーダーが蓄積してきた優位な経営資源を価値のないものにし負債にしてしまう戦略:モスは店内で調理しマックより優位に、あるいは逆にセントラルキッチン2)市場資産の負債化=リーダー企業に有利に働いていた競争のルールを変更し、顧客が持つ資産(ソフトウェア、交換部品など)を使えなくする戦略:VISAよりQRのPaypay3)論理の自縛化=安易に追随するとリーダー企業が発信してきたメッセージと矛盾することになり、企業イメージを低下させてしまう戦略:手作り丁寧より100均
4)事業の共喰化=リーダー企業が強みとしてきた製品・サービスと共喰い関係にあるような製品・サービスを市場に出すことで、リーダー企業内に追随するか否かの不協和音を引き起こす戦略:カーシェア
ーーーーーーーーーーーーーーーーーーーーーーーーーー
■現代思想
ニーチェ:ルサンチマン、奴隷道徳<>快と力の高揚感が生を肯定、ニヒリズム
マルクス:神格化、砂漠の水を都市で売ろう、紙一枚が命より大事…社会的関係の錯覚
フロイト:無意識<>理性、理性は単なるハリボテであり無意識が主人で自律神経も管理 エス(快楽主義)>自我:Ego(調整役)>超自我:Super ego(親/社会/会社の価値観の同一化)
  親や社会等から裏切られると同一化できずトラウマ、葛藤を無意識化に隠したい
ユング:Collective unconscious(人類は人種に限らず同じ記憶を持っている)

ーーーーーーーーーーーーーーーーーーーーーーーーーー
■リーダーシップで面白いほど結果が出る本
(仕事の種類)リーダシップ分類:指示/率先/構想/合意/育成/関係構築
  リーダシップ→変化、コントロール→維持(基本どちらもPDCA)
  若手に新しい変化のリーダシップが期待されている
(担当)鷹の眼と蟻の眼(大きな視点の戦略+現場の改善)
(頭脳)1)構想力、2)推進力、3)育成力
  →アイデア発表、ダメ出し、頼りにされる
1)構想力:戦略をゲームとして導入する、仕事の意味を伝える、今を楽しむフロー没入
2)推進力:手段の目的化を防ぐダメ出し、メンバーに寄り添う:会話の時間が足りない、現場のアイデアや成功事例を吸い出し
3)育成力:個々人の心のベクトルを少しプラスに、共通言語の定着(これがないと心の障壁や違和感)
(発想は言葉から、リーダは言葉が大事)

ーーーーーーーーーーーーーーーーーーーーーーーーーー
JCOM回線不安定:インターネット回線の信号レベルはタブレットで通信でモデムに入って測れる宅外でモデムを繋いででも宅外で調査しろ同時接続数等々でスロットリングしているだろうが

Comment (0)

Navi: <  2 | 3 | 4 | 5  >
-Home
-Column [122]
-Europe [9]
-Gadget [73]
-Web [111]
-Bike [3]

@/// BANGBOO BLOG ///