🤖

RDBのテーブルから直近とその前後の日付を取得する

2023/01/22に公開約7,000字

概要

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)では方法2の方がパフォーマンスが良かったです。

サンプルコード詳細

全体

https://github.com/eno314/python_demo/pull/1

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

ログインするとコメントできます