2024.04.30 07:30【SQL】日付、犯罪タイプ、位置情報を取得（Get date, crime type and location information）位置情報を取り出します。可視化のために情報を制限します。10年ぶん、犯罪上位５タイプ、NULL除外です。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)犯罪件数トップ５（THEFT（窃盗）, BATTERY（暴行）, CRIMINAL DAMAGE（器物損壊）, NARCOTICS（麻薬）, ASSAULT（脅迫））位置情報（緯度／経度、それぞれ）上記条件で2,075,883件、うち緯度経度NULLは11,3...
2024.04.27 23:20【SQL】ウィンドウ関数：累積相対度数（window function:Cumulative relative degree）WITH句を使わないFROM入れ子形式、ウィンドウ関数SUMを使って累積相対度数を出します。ツールによっては自動計算してくれそうだが。年ごとに最後は1になるので、ダブルチェック兼ねて。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)年別の犯罪タイプ上位からの足し上げと累積相対度数（ウィンドウ関数）【SQL】SELECT year, primary_type, sum_year,SUM(sum_year) OV...
2024.04.24 19:25【Flourish】年別／タイプ別件数（Number of cases by year/type） 過去記事「【SQL】年別総数とタイプ別割合」で作ったデータソースをもとに、Flourishで可視化します。Flourishは、テンプレ群がスタイリッシュでカラフルで目を引きます。動的なので、うまくはまれば自分がちょっと仕事出来る感じに思えてくる（錯覚）、ゲーマー向きなプラットフォームです。個人的視点。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)棒グラフエリアグラフ１．棒グラフまず完成形を。なにぶん犯罪タ...
2024.04.23 04:20【Exploratry】ロング型からワイド型へのデータ変換③（Data conversion from long to wide type(3)） 犯罪タイプが多いから、SQLで縦横変換はちょっと煩雑。データラングリングに長けたExploratryで時短します。常に進化し続けるBIツール、正しく使えれば強力すぎる武器。
2024.04.21 12:00【SQL】 ロング型からワイド型へのデータ変換②（Data conversion from long to wide type(2)）①の続き。Flourish での可視化のために、データの形を変えることにした。SQLで書く場合の記述です。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)「年」×「犯罪タイプ」×「件数」のロングデータ現状データ（過去記事参照）を、「年」×「犯罪タイプ件数」のワイドデータに変換します。【SQL】一部 SELECT year, MAX(sum_year) AS sum_year_total, MAX(case p...
2024.04.19 10:40ロング型からワイド型へのデータ変換①（Data conversion from long to wide type(1)）同じデータソースを使って複数のプラットフォームを扱うとき、必ずあるのが独自ルール。「何を」「どの視点で」「誰のために」可視化するかで、準備も変わります。今回は、Flourishで可視化するために、TableauとExploratryで用いたデータの形を変えます。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)Flourishって、豊富なテンプレに合えば即可視化できます。以下はバー＆パイチャートのテンプレ一部。...
2024.04.17 08:15カテゴリの重複：データクリーニングの必要性(Category overlap: the need for data cleaning)Flourish での可視化途中にデータの乱れを見つけてしまった。データ分析において、データクリーニングが８割。身をもって体感です。今回は趣味だからいいものの。人が入力してるリアルタイム更新データ、いい反省体験になりました。カテゴリー分けの名前に重複カテゴリが重複しているんですよね。半角スペースの有り無し、略語とか。日本語と違うややこしさたるや。以下に図（見やすいようyearを添えて）「NON-CRIMINAL」/「NON - CRIMINAL」：ハイフン前後の半角スペース「NON-CRIMINAL(SUBJECT SPECIFIED)」：複数容疑でメーンに据えたと思われ。「CRIMINAL SEXUIAL ASSAULT」/「CRIM SEXUAL ...
2024.04.15 06:00【Exploraty】年別／タイプ別件数（Number of cases by year/type）過去記事「【SQL】年別総数とタイプ別割合」で作ったデータソースをもとに、Exploratyで可視化します。ほんとUIフレンドリーなツール、使ってて楽しい。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)棒グラフエリアグラフ１．棒グラフデータフレームをインポート後、テーブル操作で微調整します。CSVローカル（インポート）計算を作成（整数になっていたyearを年に変換＝-01-01）グループ化（year）チャー...
2024.04.12 03:20【Tableau】年別／タイプ別件数（Number of cases by year/type）過去記事「【SQL】年別総数とタイプ別割合」で作ったデータソースをもとに、Tableauで可視化します。細かいとこはやりながら覚えよう。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)棒グラフライングラフ【テーブル】ディメンション：Primary Count , Pimary Type, Sum year , Year , メジャーネームメジャー：Type Percent , データソース , メジャーバリュ...
2024.04.10 01:10【SQL】CTE：年別総数とタイプ別割合（Total number by year and ratio by type）データが随時更新だった、という前回の反省を踏まえて、今後はあらかじめ期間を指定してから作業します。使用データ：[ Google Cloud Public Datasets] chicago_crime_data (Chicago Police Department crime data from 2001 to present)期間：2010-01-01 ～ 2020-12-31(10年間)
2024.04.07 23:00【SQL】CTE：全体件数とタイプ別割合(Total number of cases and ratio by type)ここからは、公開データ「chicago_crime_data」を使って、データの加工と可視化をしていきます。 NULLのないきれいなデータです。全体の件数を出して、タイプ別の割合を表示（サブクエリ）データの開始－終了日時、全体の件数
2024.04.06 07:00KaggleでSQL-PFへの道⑥結論（The Road to SQL Portfolios with Kaggle(6)Conclusion）SQLポートフォリオにKaggleは向かない。理由：Notebook 40分毎のALL RUNGoogle SQL接続pip初回エラーKaggleというプラットフォームコンペとコーディングレベルを魅せる場所はあ。６回かかって結論。理由これだけあればよくないー。。サンクコスト効果は排除、実際試してよかったと思おう。いずれR使って画面上で分析したいときにまた試そう。１．Notebook 40分毎のALL RUNKaggleで書きかけのNotebookを開きっぱなしにして40分たつと、「まだやってる？」とポップアップで問うてくる。大抵SQLやらほかのことと同時進行してるので、すぐ聞かれてしまう。そして毎回のALL RUN、ユーザー多いし仕方ないよね。。