🤖
RDBのテーブルから直近とその前後の日付を取得する
概要
RDB(リレーショナルデータベース)で日付のカラムがあるデーブルに保存されているデータから、指定した日から一番近い日付と、その前後の日付を取得する方法のサンプルです。
例
| id | date |
|---|---|
| 1 | 2023-01-01 |
| 2 | 2023-01-10 |
| 3 | 2023-01-20 |
| 4 | 2023-01-30 |
上記のようなテーブル・データのとき
2023-01-18を指定すると
直近は2023-01-20、直近の前は2023-01-10、直近の次は2023-01-30になる
サマリ
| 方法 | メリット | デメリット |
|---|---|---|
| 直近をSQLで取得後、前後をSQLで取得 | アプリケーションコードをシンプルにできる | RDBへの問い合わせが増える |
| 全日程をSQLで取得後、直近と前後をアプリケーションコードで取得 | RDBへの問い合わせが1回で済む | アプリケーションコードが複雑になる |
サンプルコード
サンプルは簡単にRDBが作れて呼び出せるため、PythonとSQLiteを使って実装しています。
方法1. 直近をSQLで取得後、前後をSQLで取得
アプリケーションコード
def find_nearest_and_its_prev_and_next_date_v1(
self, target: date
) -> Tuple[date, date, date]:
# 直近の日付取得
nearest_date = self.repository.find_nearest_date(target)
if nearest_date is None:
return None, None, None
# 直近の前後の日付取得
prev_date = self.repository.find_prev_date(nearest_date)
next_date = self.repository.find_next_date(nearest_date)
return nearest_date, prev_date, next_date
SQL
find_nearest_date - 直近の日付を取得
SELECT date
FROM demo
ORDER BY ABS(JULIANDAY(date) - JULIANDAY(?)) LIMIT 1;
※ サンプルではSQLiteを使っているためJULIANDAYを使って計算していますが、MySQL等であればDATEDIFFを使ってABS(DATEDIFF(date, (?)))のようにすることができます。
find_prev_date - 直近の前の日付を取得
SELECT date
FROM demo
WHERE date < (?)
ORDER BY date DESC LIMIT 1;
find_next_date - 直近の次の日付を取得
SELECT date
FROM demo
WHERE date > (?)
ORDER BY date LIMIT 1;
方法2. 全日程をSQLで取得後、直近と前後をアプリケーションコードで取得
アプリケーションコード
def find_nearest_and_its_prev_and_next_date_v2(
self, target: date
) -> Tuple[date, date, date]:
# ソートされた全日程を取得
all_dates = self.repository.find_sorted_all_dates()
# 直近の日付のインデックスを取得して、直近とその前後の日付を取得
nearest_index = self._find_nearest_index_from_dates(all_dates, target)
return (
self._get_date_or_none_from_dates(all_dates, nearest_index),
self._get_date_or_none_from_dates(all_dates, nearest_index - 1),
self._get_date_or_none_from_dates(all_dates, nearest_index + 1),
)
def _find_nearest_index_from_dates(
self, sorted_dates: List[date], target: date
) -> int:
nearest_index = 0
for index in range(1, len(sorted_dates)):
date_diff_days = abs((sorted_dates[index] - target).days)
nearest_diff_days = abs(
(sorted_dates[nearest_index] - target).days)
if date_diff_days < nearest_diff_days:
nearest_index = index
else:
break
return nearest_index
def _get_date_or_none_from_dates(
self, dates: List[date], index: int
) -> date:
return dates[index] if 0 <= index < len(dates) else None
SQL
find_sorted_all_dates - ソートされた全日程を取得
SELECT DISTINCT date
FROM demo
ORDER BY date;
2つの方法の比較
アプリケーションのコード
- 方法1のほうはそれぞれの日付を取得していてシンプルに見える
- 方法2の方は探索をアプリケーション側のコードで書いている分、複雑になっている
- DateListのようなクラスを作って、その中で直近とその前後の日付を取得するメソッドを用意すれば複雑な部分を隠蔽することも可能
RDBへのリクエスト
- 方法1は3回問い合わせしている
- UNIONを使ったりサブクエリを使えばRDBへのリクエストは2回で済む(実質のSQLは変わらないのでパフォーマンスにそんなに差はでない)
- 前後の日付を取得する処理を並列化することでアプリケーションのパフォーマンスを上げることは可能(RDBへの負荷は変わらない)
- 方法2の方は1回の問い合わせがだけで済んでいるので、RDBには優しい
- データ構造が複雑だったり、データ量が多くて1回のクエリに時間がかかるのであれば、こっちのほうがパフォーマンスがいい場合もある
サンプルアプリのパフォーマンス
1年分の日程のデータをオンメモリのSQLiteに入れて、それぞれの処理のパフォーマンスを測定してみました。
SQLite、シンプルなテーブル、Pythonな環境だと常に方法1の方が良い結果になりました。
※ パフォーマンステストにはpytest-benchmarkを使っています。
※ {x}timesとなっているのはデータ量をx倍にして実行しているという意味です。
※ ファイルに書き出すほうも試してみましたが、結果はあまり変わりませんでした。
-------------------------------------------------------------------------------------------------- benchmark: 8 tests --------------------------------------------------------------------------------------------------
Name (time in us) Min Max Mean StdDev Median IQR Outliers OPS Rounds Iterations
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
test_performance_v1 137.9170 (1.0) 217.0000 (1.0) 140.9675 (1.0) 5.6482 (1.0) 138.7920 (1.0) 2.5420 (1.0) 147;178 7,093.8335 (1.0) 1290 1
test_performance_v2 284.5410 (2.06) 380.3330 (1.75) 290.8256 (2.06) 9.0705 (1.61) 288.3750 (2.08) 5.2090 (2.05) 247;274 3,438.4871 (0.48) 3089 1
test_performance_10times_v1 777.4580 (5.64) 865.3330 (3.99) 783.1501 (5.56) 7.3491 (1.30) 781.3750 (5.63) 4.1250 (1.62) 125;133 1,276.8944 (0.18) 1166 1
test_performance_10times_v2 784.6670 (5.69) 886.2500 (4.08) 792.5621 (5.62) 7.4769 (1.32) 790.9580 (5.70) 6.2295 (2.45) 155;108 1,261.7307 (0.18) 1169 1
test_performance_100times_v2 6,992.6250 (50.70) 7,266.6250 (33.49) 7,046.2638 (49.99) 45.4177 (8.04) 7,034.5000 (50.68) 29.3333 (11.54) 18;11 141.9192 (0.02) 139 1
test_performance_100times_v1 7,221.2080 (52.36) 7,406.8750 (34.13) 7,254.2330 (51.46) 35.0431 (6.20) 7,245.5420 (52.20) 22.1870 (8.73) 11;10 137.8505 (0.02) 135 1
test_performance_1000times_v1 71,576.7910 (518.98) 72,438.1670 (333.82) 71,975.4673 (510.58) 229.6484 (40.66) 72,014.3750 (518.87) 357.9170 (140.80) 5;0 13.8936 (0.00) 14 1
test_performance_1000times_v2 92,922.2080 (673.75) 94,703.6660 (436.42) 93,533.9051 (663.51) 473.2428 (83.79) 93,431.1660 (673.17) 386.7605 (152.15) 2;1 10.6913 (0.00) 11 1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Legend:
Outliers: 1 Standard Deviation from Mean; 1.5 IQR (InterQuartile Range) from 1st Quartile and 3rd Quartile.
OPS: Operations Per Second, computed as 1 / Mean
ちなみに仕事で遭遇したシステム(MySQL、カラム多いテーブル、JOINや他の条件文が多いSQL、Java+SpringBoot+MyBatis)では方法２の方がパフォーマンスが良かったです。
サンプルコード詳細
全体
usecase.py
- Usecaseクラス
- アプリケーションのメインのコード
-
find_nearest_and_its_prev_and_next_date_v1が方法1で実装した処理 -
find_nearest_and_its_prev_and_next_date_v2が方法2で実装した処理
repository.py
- Repositoryクラス
- 抽象クラス
- 直近とその前後の日付を取得するのに必要なデータを探すメソッドを定義
-
find_nearest_dateが直近の日付を直接取得するメソッド -
find_prev_dateが直近の前の日付を直接取得するメソッド -
find_next_dateが直近の次の日付を直接取得するメソッド -
find_sorted_all_datesが保存されている全日程を昇順で取得するメソッド
-
- DemoRepositoryクラス
- RepositoryをSQLiteを使って実装したクラス
Discussion