【Azure Databricks SQL ダッシュボード】乗車時間分布の可視化の作成

SELECT
CASE
WHEN 「テーブル名」.「列名」 = 「値」 THEN ‘「値」’
WHEN 「テーブル名」.「列名」 = 「値」 THEN ‘「値」’
WHEN 「テーブル名」.「列名」 = 「値」 THEN ‘「値」’
…
ELSE ‘「値」’
END AS 「別名」,
「テーブル名」.「列名」 AS 「別名」
FROM
(SELECT
hour(「列名」) AS 「別名」,
COUNT(*) AS 「別名」
FROM
「カタログ名」.「スキーマ名」.「テーブル名」
WHERE
「列名」 BETWEEN TIMESTAMP ‘{{ 「パラメータ名」.start }}’
AND TIMESTAMP ‘{{ 「パラメータ名」.end }}’
AND 「列名」 IN ({{ 「パラメータ名」 }})
GROUP BY
1) 「別名」

例

USE CATALOG MyCatalog; 

SELECT 
	CASE 
		WHEN T.pickup_hour = 0 THEN '00:00' 
		WHEN T.pickup_hour = 1 THEN '01:00' 
		WHEN T.pickup_hour = 2 THEN '02:00' 
		WHEN T.pickup_hour = 3 THEN '03:00' 
		WHEN T.pickup_hour = 4 THEN '04:00' 
		WHEN T.pickup_hour = 5 THEN '05:00' 
		WHEN T.pickup_hour = 6 THEN '06:00' 
		WHEN T.pickup_hour = 7 THEN '07:00' 
		WHEN T.pickup_hour = 8 THEN '08:00' 
		WHEN T.pickup_hour = 9 THEN '09:00' 
		WHEN T.pickup_hour = 10 THEN '10:00' 
		WHEN T.pickup_hour = 11 THEN '11:00' 
		WHEN T.pickup_hour = 12 THEN '12:00' 
		WHEN T.pickup_hour = 13 THEN '13:00' 
		WHEN T.pickup_hour = 14 THEN '14:00' 
		WHEN T.pickup_hour = 15 THEN '15:00' 
		WHEN T.pickup_hour = 16 THEN '16:00' 
		WHEN T.pickup_hour = 17 THEN '17:00' 
		WHEN T.pickup_hour = 18 THEN '18:00' 
		WHEN T.pickup_hour = 19 THEN '19:00' 
		WHEN T.pickup_hour = 20 THEN '20:00' 
		WHEN T.pickup_hour = 21 THEN '21:00' 
		WHEN T.pickup_hour = 22 THEN '22:00' 
		WHEN T.pickup_hour = 23 THEN '23:00' 
		ELSE 'N/A' END AS `乗車時間`, 
			T.num AS `乗車回数` 
FROM 
	(
		SELECT 
			hour(tpep_pickup_datetime) AS pickup_hour, 
			COUNT(*) AS num 
		FROM 
			`MyCatalog`.`MySchema`.`trips` 
		WHERE 
			tpep_pickup_datetime BETWEEN TIMESTAMP '{{ pickup_date.start }}' 
			AND TIMESTAMP '{{ pickup_date.end }}' 
			AND pickup_zip IN ({{ pickupzip }}) 
			GROUP BY 1
	) T

USE CATALOG MyCatalog;

SELECT

CASE

WHEN T.pickup_hour = 0 THEN '00:00'

WHEN T.pickup_hour = 1 THEN '01:00'

WHEN T.pickup_hour = 2 THEN '02:00'

WHEN T.pickup_hour = 3 THEN '03:00'

WHEN T.pickup_hour = 4 THEN '04:00'

WHEN T.pickup_hour = 5 THEN '05:00'

WHEN T.pickup_hour = 6 THEN '06:00'

WHEN T.pickup_hour = 7 THEN '07:00'

WHEN T.pickup_hour = 8 THEN '08:00'

WHEN T.pickup_hour = 9 THEN '09:00'

WHEN T.pickup_hour = 10 THEN '10:00'

WHEN T.pickup_hour = 11 THEN '11:00'

WHEN T.pickup_hour = 12 THEN '12:00'

WHEN T.pickup_hour = 13 THEN '13:00'

WHEN T.pickup_hour = 14 THEN '14:00'

WHEN T.pickup_hour = 15 THEN '15:00'

WHEN T.pickup_hour = 16 THEN '16:00'

WHEN T.pickup_hour = 17 THEN '17:00'

WHEN T.pickup_hour = 18 THEN '18:00'

WHEN T.pickup_hour = 19 THEN '19:00'

WHEN T.pickup_hour = 20 THEN '20:00'

WHEN T.pickup_hour = 21 THEN '21:00'

WHEN T.pickup_hour = 22 THEN '22:00'

WHEN T.pickup_hour = 23 THEN '23:00'

ELSE 'N/A' END AS `乗車時間`,

T.num AS `乗車回数`

FROM

(

SELECT

hour(tpep_pickup_datetime) AS pickup_hour,

COUNT(*) AS num

FROM

`MyCatalog`.`MySchema`.`trips`

WHERE

tpep_pickup_datetime BETWEEN TIMESTAMP '{{ pickup_date.start }}'

AND TIMESTAMP '{{ pickup_date.end }}'

AND pickup_zip IN ({{ pickupzip }})

GROUP BY 1

) T

③総走行数の可視化と同じように「pickupzip」と「pickup_date」の２つテキストボックスを以下の2つテキストボックスに変更します。情報を入力して「変更を適用」をクリックすると、クエリの結果が表示されます。

④結果は以下の画像の通りです。「保存」をクリックしてクエリを保存します。

⑤クエリに「乗車時間の分布」等の名前を付けて「保存」をクリックします。

2-2.乗車時間分布の可視化の作成

①サイドバーで「ダッシュボード」をクリックして、「NYCタクシーダッシュボード」を選択します。

②ダッシュボードで　　をクリックして、「編集」を選択します。

③「追加」をクリックし、「可視化」を選択してダッシュボードに可視化を追加します。

④ダイアログでクエリ「乗車時間の分布」を選択します。

⑤「タイトル」項目で「乗車時間の分布」等の情報を入力して、「ダッシュボードに追加」をクリックします。

⑥結果は以下の通り取得できます。

⑦ビジュアライゼーション「乗車時間の分布」にマウスを移動してをクリックして、「ビジュアライゼーションを編集」を選択します。

⑧「Visualization type」項目で「Bar」を選択します。

⑨「X column」で「乗車時間」を選択して、「Y columns」で「乗車回数」を選択します。

⑩「X axis」タブに移動して、「Name」項目で「乗車時間」等の期待値を入力すると、その値がグラフの横軸の下に表示されます。

⑪「Y axis」タブに移動して、「Name」項目で「乗車回数」等の期待値を入力すると、その値がグラフの縦軸の下に表示されます。「保存」をクリックします。

⑫ビジュアライゼーションが追加され、以下の通りダッシュボードに表示されます。

⑬ダッシュボードを改善するために、ビジュアライゼーションを調整します。

3.まとめ

本連載では、
「NYCタクシーデータセット」を使用して乗車時間分布の可視化の作成方法について詳細に説明していきます。

第1回：【Azure Databricks SQL ダッシュボード】サンプルダッシュボードのインポートと基本操作

第2回：【Azure Databricks SQL ダッシュボード】総走行数の可視化の作成

第3回：【Azure Databricks SQL ダッシュボード】曜日ごとの運賃と距離の可視化の作成

第4回：【Azure Databricks SQL ダッシュボード】乗車時間分布の可視化の作成（今回）

第5回：【Azure Databricks SQL ダッシュボード】ルート別運賃の分析の可視化の作成

第6回：【Azure Databricks SQL ダッシュボード】降車時間別の乗車回数の可視化の作成

今回の記事が少しでもDatabricksを知るきっかけや、業務のご参考になれば幸いです。

日商エレクトロニクスでは、Azure Databricksの環境構築パッケージを用意しています。

Azure DatabricksやAzure活用、マイクロソフト製品の活用についてご相談事がありましたらぜひお問い合わせください！

・Azure Databricks連載シリーズはこちら

お問い合わせはこちら

この記事を読んだ方へのオススメコンテンツはこちら

アセスメントサービス

この記事を書いた人

quanna

この投稿者の最新の記事

この投稿者の記事一覧

2024年3月7日ブログ【Azure Databricks】Apache Airflow を使用して Azure Databricks ジョブを調整する
2024年2月27日ブログ【Azure Databricks】Auto Loader と Delta Live Table を組み合わせて使用してみる
2024年1月4日ブログ【Azure Databricks】Auto Loaderの概要、自動取込方法を使用してみる
2023年11月30日ブログ【Azure Databricks】「Databricks Container Servicesの設定」をしてみる

【Azure Databricks SQL ダッシュボード】乗車時間分布の可視化の作成

1.はじめに

2.ダッシュボードの作成

2-1.クエリを作成

2-2.乗車時間分布の可視化の作成

3.まとめ

この記事を読んだ方へのオススメコンテンツはこちら

この記事を書いた人

この投稿者の最新の記事

Add comment コメントをキャンセル

03-6272-5011