SQLデータベースに基づくアルゴリズムを学ぶ

データベースは、データを保存し、大規模な計算を実行する場所です。現実世界の問題を解決するために、データベースでいくつかの簡単なSQL コマンドを使用してデータを保存、クエリ、カウントするのが一般的です。データ量が大幅に増加し、ビジネスルールが複雑化するにつれて、効率性と正確性の要件を満たすために専門的なアプローチがますます必要になっています。問題を解決するための複雑なアルゴリズムを、データベースで実行できるコマンドに変換する方法も、データベースアプリケーション技術の研究の一側面です。この記事では、MSSQL のコマンドを使用して例を説明します。

データベースにはエンティティデータセットを保存できます。計算を実行する場合、データベースはバッチコンピューティング方式を使用してデータを処理し、ストレージデバイスからデータをバッチで読み取り、処理後にデータをストレージデバイスにバッチで書き戻します。一部のデータベースではカーソルが提供されており、テーブル内のデータ行の各フィールドを読み取り、これらのフィールドに対して複雑なビジネスルール計算を実行し、その結果をデータベースに書き戻すことができます。バッチ方式と比較すると、バッチ計算方式は消費するリソースが比較的少ないですが、カーソルを使用するとリソースが大量に消費され、速度が遅く、効率が悪く、ロック条件などの制限が多くなります。

たとえば、データベースに学生のスコア student_Score (sno、cno、score、level) が格納されている場合、スコアの範囲は 0 ～ 100 です。スコアの質を示すフィールドワード level をスコアの後に (A の場合は 90 点以上、B の場合は 80 ～ 90 点、C の場合は 60 ～ 80 点、D の場合は 60 点未満) 格納する必要がある場合、次のアルゴリズムで同じ目的を達成できます。

1. カーソルを定義して、student_Score テーブル内のすべてのスコアレコードを選択し、現在のレコードのスコアを格納する変数 @cur_score を定義します。また、現在のスコアの成績レベルを格納する変数 @cur_level を定義します。このレベルは、成績が良いか悪いかのマークを格納するために使用されます。アルゴリズムは次のとおりです。カーソル内のレコードが空でない場合は、カーソルから現在のレコードのスコアを取得し、スコアの範囲を決定し、結果を変数 @cur_level に格納し、現在のレコードのレベルフィールドを @cur_level の値で更新します。プロセス全体では、データベースを少なくとも 2 回読み取る必要があります。1 回はレコードを取得するため、もう 1 回はデータベースに書き込むためです。各レコードはこのプロセスを経る必要があるため、比較的非効率的です。

2. データベースをバッチで更新し、すべてのレベルフィールドの値を D に設定し、データベースを再度更新し、スコアが 60 以上のレコードのレベルフィールドを C に更新し、B と A を順番に更新します。この方法の欠点の 1 つは、一部のレコードのレベルフィールドが複数回更新されることです。たとえば、レコードの最後のレベルフィールドの値が A の場合、最初に D に更新され、その後、C、B、A の順に更新されます。これらの繰り返し更新は排除でき、アルゴリズムを改善することで繰り返し更新のコストを節約できます。更新されたアルゴリズムは次のとおりです。スコアが 0 ～ 60 ポイントのレコードのレベルフィールドが D に更新され、各スコア範囲のスコアが順番に更新されます。このアルゴリズムを実装するための SQL 文の記述は難しくありません。たとえば、80 から 90 までのレコードの選択条件は、Between...and... 式を使用して表現できます。

3. 2 番目の方法の最終的な分析を考慮すると、 between...and... 式を使用してテーブルを参照すると同時にレコードを更新すると、スコアの範囲と対応するレベル情報を簡単に表現でき、この情報を level_about テーブルに保存して、student_score テーブルを更新するプロセスで参照できます。計算プロセス中に、level_about テーブルの内容を読み取り、計算する必要があります。計算プロセス全体において、操作の利便性と引き換えに、スペースとある程度の効率が犠牲になっています。現在、コンピューターの速度は非常に速く、level_about テーブルが占めるスペースは非常に小さいため、この点での損失は無視できます。 Level_about テーブルの情報には、開始スコアを記録する start_score、終了スコアを記録する end_score、開始スコアと終了スコアの間のスコアに対して取得すべきグレードを記録する level の少なくとも 3 つのフィールドが含まれます。表内のデータは次のようになります。

開始スコア終了スコアレベル
0 59 デイ
60 79 ℃
80 89 B
90 100 A

student_Score テーブルのレコードを更新するには、Start_score と End_score に基づいて現在のレコードのスコアのレベルを決定する必要があります。MSSQL で実装されている SQL ステートメントは次のとおりです。

student_scoreを更新します。student_score.level=level_about.levelを設定します。
level_about で、student.score が level_about.start_score と level_about.end_score の間である

上記の 3 つの方法を比較すると、同じ目標を達成するために異なるアルゴリズムを使用した場合の効果が異なります。

いくつかの単純なアルゴリズムは、変更せずにデータベースに直接適用できます。たとえば、企業がその日のボーナスを毎晩決済する必要がある場合、ボーナスは週 2 回、月曜日と木曜日の夜 0:00 に自動的に決済されます。システムの自動決済を実現するためには、システムタスクを使用し、システムに対してジョブを設定し、毎晩 0:00 に決済を指定することでシステムの自動決済を実現する必要があります (決済時間間隔が変わる可能性があるため、ジョブ内のタイミング機能は使用できません)。月曜日と木曜日に決済を行うために、データベースにテーブル misc を設定します。テーブル内のフィールドはグローバル変数に相当します。テーブルにはレコードが 1 つだけあり、フィールドの 1 つ (days) は現在の決済番号、つまりシステムの実行開始から経過した日数を記録するために使用されます。システムはタスクを実行し、misc テーブル内の日数を更新して増加させます: update misc set days=days+1。

業務要件は、毎週月曜日と木曜日にボーナスを決済することです。奇数決済は 7 日離れており、偶数決済は 7 日離れており、隣接する奇数決済と偶数決済は 3 日離れていることを見つけるのは難しくありません。この問題は、剰余法を使用して統一できます。現在の日数（日数）と 7 の余りが 0 の場合、または現在の日数（日数）から 3 を引いた余りが 0 の場合、現在の日が決済日となります。

具体的な実装アルゴリズムは次のとおりです。

1. 現在の日付を変数に抽出します。declare @days int set @days=(select days from misc)

2. @days%7=0 または (@days-3)%7=0 の場合に決済条件が満たされているかどうかを判断します begin…end

このような単純なアルゴリズムは、問題なくデータベースに直接適用できます。

複雑なビジネスルールには複雑なアルゴリズムが必要です。複雑なルールは、特定の数値を持つ変数に対して実装するだけでも複雑です。データベースに保存された整理されていない大量の数値に適用され、バッチ計算が必要な場合は、アルゴリズムを大幅に調整する必要があります。

たとえば、ビジネスルールでは、従業員のボーナス 4,000 元ごとに 400 元を繰り返し消費として差し引き、残りの 400 元を差し引いた後、繰り返し消費ごとに製品を授与する必要があります。繰り返し消費を記録するには、データベースでテーブル (award_repeat) を使用する必要があります。一度に差し引かれるボーナスが400元未満の場合、差し引かれたボーナスが400元に達するまで、次の決済時に引き続き差し引かれ、その後、商品が報酬として付与され、次のサイクルに入ります。たとえば、現在ボーナスの合計が3,600元に達した場合、差し引かれません。3,700元に達した場合は、100元が差し引かれます。7,700元に達した場合は、410元が差し引かれ、繰り返し消費が発生します。

このルールを実装するために、従業員テーブル（member）に各従業員のボーナスの合計額（[total_award]）と繰り返し消費の回数（[repeat_num]）を記録します。各ボーナスと繰り返し消費を差し引いた後の各ボーナスは、別の遷移テーブル（award_day）に記録されます。最後に、ボーナステーブル（award）は、その時期のボーナスと、その時期に差し引く必要のある繰り返し消費を組み合わせて、その時期に実際に支払われるボーナスを取得します。バッチ計算方式が使用され、実装されているアルゴリズムは次のとおりです。ボーナスを計算した後、繰り返し消費を差し引く前に、現在のボーナスを従業員の ([total_award]) フィールド ([total_award]) に追加し、繰り返し消費を差し引かずにすべてのボーナスの合計を記録します。繰り返し消費計算を実装するアルゴリズムは、メンバーテーブル内のボーナスの合計数が、繰り返し消費数プラス1×4000以上であるという条件（F1）を設定します。条件F1を満たすレコードがある場合は、条件を満たすレコード内の主キーと現在の日付（日数）を選択して繰り返し消費テーブル（award_repeat）に挿入し、条件F1を満たすメンバーテーブル内のrepeat_numを1ずつ増やすように更新し、メンバーテーブル内に条件F1を満たすレコードがなくなるまで条件F1を繰り返しチェックします。

結論: データベースでのアルゴリズムの調査と実装は非常に困難で挑戦的です。現実世界のビジネスルールがますます複雑になるにつれて、対応するデータベースアプリケーションソフトウェアがビジネスルールを実装するために必要なアルゴリズムもますます複雑になっています。複雑なアルゴリズムをデータベースに適用するための統一された方法を見つける必要があります。ビジネスルールに精通していることを前提として、データベースの特性とそれに応じたコマンド実行能力に基づいて、データベースバッチ計算に適した手順を見つけることが、問題を解決する鍵となります。上記の内容が皆様のお役に立てれば幸いです。

【編集者のおすすめ】