top of page
検索
  • 執筆者の写真M.Y
    • 1月18日
    • 読了時間: 8分

QuickSight 暦と異なる日付で昨対を追加する

更新日：2月23日

はじめに

 

QuickSightでは昨対を計算する便利な関数があります。

関数名

説明

periodOverPeriodDifference

期間粒度とオフセットで指定された 2 つの異なる期間におけるメジャーの差分を計算します。

periodOverPeriodLastValue

期間粒度とオフセットで指定された前の期間からのメジャーの最後の (直近の) 値を計算します。

periodOverPeriodPercentDifference

期間粒度とオフセットで指定された 2 つの異なる期間におけるメジャーのパーセント差分を計算します。

とても便利な関数ですが、こちらの関数は暦に応じて計算される為、例えば1ヶ月ごとの昨対の数値を計算させる場合、1日〜月末を1ヶ月として計算されます。


しかし、月の締めが1日〜月末ではなく、別の日付（21日〜翌20日）で計算させたいという要件があったため、解決する方法を検討しました。



対応方法の検討

 

今回の要件を叶えるための方法として以下2つの方法を検討しました。


1. 計算するための元のテーブルに昨対実績を保持しておく

date

kingaku

sakunen_kingaku

2023-04-21

2,000

1,800

2023-04-22

2,200

2,100

昨対となる値を集計し、事前に結合させる必要がある


メリット：利用者からは何も考えずに昨対の項目があるため、利用しやすい形にはなる

　　　　　計算がシンプルになるため、QuickSightで計算結果が表示されるまでの時間が

　　　　　早いと思われる


デメリット：昨対の集計粒度（年、月、週、日）に合わせた項目を事前に保有しておく

　　　　　　必要があり、変更が発生した場合に開発コストが発生する



2. ひと月の起点から終点（21日〜翌20日）に対し、1〜月末となるようにダミー日付をテーブルに持たせる

date

dummy_date

kingaku

2023-04-21

2023-04-01

2,000

2023-04-22

2023-04-02

2,200

2022-04-21

2022-04-01

1,800

2022-04-22

2022-04-02

2,100

21日〜翌20に対して該当月の1日〜月末の日付を作成する必要がある


メリット：昨対の集計粒度（年、月、週、日）が関数で変更が可能なので、開発コストが

　　　　　かからない


デメリット：QuickSightで計算をさせることによって、計算結果の取得までに時間がかかる

　　　　　　可能性がある


※計算結果の表示の速度については、保有するデータ期間やデータ量により異なるため、データが少ない場合は速度への影響はありません。


今回は計算結果の表示よりも、開発コストを抑えることが優先だったため、「2. ひと月の起点から終点（21日〜翌20日）に対し、1〜月末となるようにダミー日付をテーブルに持たせる」を採用しました。



ダミー日付を作成

 

実際にダミー日付を作成してみます。


今回はAthenaとQuicksightを利用しているので、QuickSightからカスタムSQLでAthenaを叩き、ダミー日付追加しました。



with dummy as (
		select 31 as mst,21 as day,1 as dummy 
union all select 31 as mst,22 as day,2 as dummy 
union all select 31 as mst,23 as day,3 as dummy 
union all select 31 as mst,24 as day,4 as dummy 
union all select 31 as mst,25 as day,5 as dummy 
union all select 31 as mst,26 as day,6 as dummy 
union all select 31 as mst,27 as day,7 as dummy 
union all select 31 as mst,28 as day,8 as dummy 
union all select 31 as mst,29 as day,9 as dummy 
union all select 31 as mst,30 as day,10 as dummy 
union all select 31 as mst,31 as day,11 as dummy 
union all select 31 as mst,1 as day,12 as dummy 
union all select 31 as mst,2 as day,13 as dummy 
union all select 31 as mst,3 as day,14 as dummy 
union all select 31 as mst,4 as day,15 as dummy 
union all select 31 as mst,5 as day,16 as dummy 
union all select 31 as mst,6 as day,17 as dummy 
union all select 31 as mst,7 as day,18 as dummy 
union all select 31 as mst,8 as day,19 as dummy 
union all select 31 as mst,9 as day,20 as dummy 
union all select 31 as mst,10 as day,21 as dummy 
union all select 31 as mst,11 as day,22 as dummy 
union all select 31 as mst,12 as day,23 as dummy 
union all select 31 as mst,13 as day,24 as dummy 
union all select 31 as mst,14 as day,25 as dummy 
union all select 31 as mst,15 as day,26 as dummy 
union all select 31 as mst,16 as day,27 as dummy 
union all select 31 as mst,17 as day,28 as dummy 
union all select 31 as mst,18 as day,29 as dummy 
union all select 31 as mst,19 as day,30 as dummy 
union all select 31 as mst,20 as day,31 as dummy 
union all select 30 as mst,21 as day,1 as dummy 
union all select 30 as mst,22 as day,2 as dummy 
union all select 30 as mst,23 as day,3 as dummy 
union all select 30 as mst,24 as day,4 as dummy 
union all select 30 as mst,25 as day,5 as dummy 
union all select 30 as mst,26 as day,6 as dummy 
union all select 30 as mst,27 as day,7 as dummy 
union all select 30 as mst,28 as day,8 as dummy 
union all select 30 as mst,29 as day,9 as dummy 
union all select 30 as mst,30 as day,10 as dummy 
union all select 30 as mst,1 as day,11 as dummy 
union all select 30 as mst,2 as day,12 as dummy 
union all select 30 as mst,3 as day,13 as dummy 
union all select 30 as mst,4 as day,14 as dummy 
union all select 30 as mst,5 as day,15 as dummy 
union all select 30 as mst,6 as day,16 as dummy 
union all select 30 as mst,7 as day,17 as dummy 
union all select 30 as mst,8 as day,18 as dummy 
union all select 30 as mst,9 as day,19 as dummy 
union all select 30 as mst,10 as day,20 as dummy 
union all select 30 as mst,11 as day,21 as dummy 
union all select 30 as mst,12 as day,22 as dummy 
union all select 30 as mst,13 as day,23 as dummy 
union all select 30 as mst,14 as day,24 as dummy 
union all select 30 as mst,15 as day,25 as dummy 
union all select 30 as mst,16 as day,26 as dummy 
union all select 30 as mst,17 as day,27 as dummy 
union all select 30 as mst,18 as day,28 as dummy 
union all select 30 as mst,19 as day,29 as dummy 
union all select 30 as mst,20 as day,30 as dummy 
union all select 29 as mst,21 as day,1 as dummy 
union all select 29 as mst,22 as day,2 as dummy 
union all select 29 as mst,23 as day,3 as dummy 
union all select 29 as mst,24 as day,4 as dummy 
union all select 29 as mst,25 as day,5 as dummy 
union all select 29 as mst,26 as day,6 as dummy 
union all select 29 as mst,27 as day,7 as dummy 
union all select 29 as mst,28 as day,8 as dummy 
union all select 29 as mst,29 as day,9 as dummy 
union all select 29 as mst,1 as day,10 as dummy 
union all select 29 as mst,2 as day,11 as dummy 
union all select 29 as mst,3 as day,12 as dummy 
union all select 29 as mst,4 as day,13 as dummy 
union all select 29 as mst,5 as day,14 as dummy 
union all select 29 as mst,6 as day,15 as dummy 
union all select 29 as mst,7 as day,16 as dummy 
union all select 29 as mst,8 as day,17 as dummy 
union all select 29 as mst,9 as day,18 as dummy 
union all select 29 as mst,10 as day,19 as dummy 
union all select 29 as mst,11 as day,20 as dummy 
union all select 29 as mst,12 as day,21 as dummy 
union all select 29 as mst,13 as day,22 as dummy 
union all select 29 as mst,14 as day,23 as dummy 
union all select 29 as mst,15 as day,24 as dummy 
union all select 29 as mst,16 as day,25 as dummy 
union all select 29 as mst,17 as day,26 as dummy 
union all select 29 as mst,18 as day,27 as dummy 
union all select 29 as mst,19 as day,28 as dummy 
union all select 29 as mst,20 as day,29 as dummy 
union all select 28 as mst,21 as day,1 as dummy 
union all select 28 as mst,22 as day,2 as dummy 
union all select 28 as mst,23 as day,3 as dummy 
union all select 28 as mst,24 as day,4 as dummy 
union all select 28 as mst,25 as day,5 as dummy 
union all select 28 as mst,26 as day,6 as dummy 
union all select 28 as mst,27 as day,7 as dummy 
union all select 28 as mst,28 as day,8 as dummy 
union all select 28 as mst,1 as day,9 as dummy 
union all select 28 as mst,2 as day,10 as dummy 
union all select 28 as mst,3 as day,11 as dummy 
union all select 28 as mst,4 as day,12 as dummy 
union all select 28 as mst,5 as day,13 as dummy 
union all select 28 as mst,6 as day,14 as dummy 
union all select 28 as mst,7 as day,15 as dummy 
union all select 28 as mst,8 as day,16 as dummy 
union all select 28 as mst,9 as day,17 as dummy 
union all select 28 as mst,10 as day,18 as dummy 
union all select 28 as mst,11 as day,19 as dummy 
union all select 28 as mst,12 as day,20 as dummy 
union all select 28 as mst,13 as day,21 as dummy 
union all select 28 as mst,14 as day,22 as dummy 
union all select 28 as mst,15 as day,23 as dummy 
union all select 28 as mst,16 as day,24 as dummy 
union all select 28 as mst,17 as day,25 as dummy 
union all select 28 as mst,18 as day,26 as dummy 
union all select 28 as mst,19 as day,27 as dummy 
union all select 28 as mst,20 as day,28 as dummy 
),
mst_day as (
select		
	date,
	day(date_trunc('month',date) + interval '1' month - interval '1' day) as mst_day,
	case
		when day(date) >=21 then month(date_add('month',1,date)
		else month(date)
	end month,
	day(date) as day,
	kingaku
from tran
),
year as(
	date,
	mst_day,
	case
		when month(date)>=4 or month>=4 then year(date)
		else year(date_add('year',-1,date)
	end year,
	day,
	kingaku
from mst_day
)	
select
	mst_day.date,
	date_parse(concat(cast(year as varchar),lpad(cast(month(date) as varchar),2,'0'),lpad(cast(dummy_date.dummy as varchar),2,'0')),'%Y%m%d') as dummy_date,
	kingaku
from mst_day
inner join dummy on
	mst_day.mst_day=dummy.mst and
	mst.date=dummy.day
;


QuickSightで確認

 

実際にQuickSightの分析画面から、作成したダミー日付を使った分析を行ってみます。


どちらも暦日付で21日〜翌20日でフィルタを行ったピボットテーブルの分析ですが、ダミー日付を利用した場合、想定どおりまとまって集計することができました。




実際に昨対の関数を利用してみます。


＋計算フィールド から昨対実績（年）という名前で項目を作成しました。

periodOverPeriodLastValue(sum(kingaku),{dummy_date},YEAR,1)



分析で利用してみると、昨対実績の値が集計され、確認のため手動でフィルターした実績の集計一致しました。




残りの2つの関数も利用して差額と比率の計算フィールドを作成しました。

-- 昨対差額
periodOverPeriodDifference(sum(kingaku),{dummy_date},YEAR,1)

-- 昨対比差分
periodOverPeriodPercentDifference(sum(kingaku),{dummy_date},YEAR,1)

-- 昨対比
sum(kintaku)/昨年実績（年）




関数を利用するだけで簡単に昨対の実績を取得することができました。


今回は 'YEAR' で計算をさせたので昨年の取得を行いましたが、period を QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECONDS など変更することで求めている粒度に合わせて集計が可能です。


ただし昨対の計算を行うため、フィルターの絞り込みは1年未満にしてしまうと計算ができないので注意が必要です。


数年分をまとめてグラフで見たいという要望には適しているが、1ヶ月単位の帳票には不向きですね。



おまけ（週の起点の変更）

 

週で分析を行う際には、デフォルト機能（編集→分析設定）で週の起点を変更することができるようになりました。



週の数え方も1月からとなっており、月の考え方もデフォルト機能で対応ができるようになれば、こだわりユーザーにも優しいBIになりそうなので、アップデートに期待しています。



以上です。



閲覧数：85回0件のコメント

最新記事

すべて表示

データ状態により異なるSQLを実行させたい

はじめに 近頃担当する業務は夜間バッチでのデータ更新処理が多く、特にDWH的にテーブル再構築（TRUNCATE/INSERT）のパターンを多く使用しています。 その中でSQLで処理を組み上げる時、エラー処理などで条件分岐で異なるSQLを実行したくなる事は珍しくありません。 多くのシステムでは呼び出し側でSQLの実行結果を参照し、次に実行するSQLを選択／実行していると思います。 また、SQLだけで

bottom of page