PostgreSQL でプレーヤー チーム メンバーシップの重複する日付範囲を特定する方法

PostgreSQL で重複する日付範囲を特定する

問題

ユーザーが、あるグループに属していたプレーヤーのリストを取得しようとしています。特定の年の範囲内の特定のチーム。最初に提案されたクエリは次のとおりです:

SELECT * 
FROM   contract 
JOIN   team USING (name_team) 
JOIN   player USING(name_player) 
WHERE  name_team = ? 
AND    DATE_PART('YEAR',date_join)>= ? 
AND    DATE_PART('YEAR',date_leave)<p>ただし、このクエリは重複する日付範囲を効果的に識別しません。</p><p><strong>正しい解決策</strong></p><p>重複する日付範囲を正確に判断するには、次の基準を考慮するようにクエリを変更する必要があります:</p><ol>
<li>以下のプレイヤー指定された年の範囲が始まる前にチームに参加したプレーヤー。</li>
<li>指定された年の範囲が終わる前にチームを離れなかったプレーヤー。</li>
<li>チームを離れなかったプレーヤー (NULL で表される) date_leave 値)。</li>
</ol><p><strong>最適化済みクエリ</strong></p><p>次のクエリは、より正確な解決策を提供します。</p><pre class="brush:php;toolbar:false">SELECT DISTINCT name_player 
FROM   contract
WHERE  name_team = ? 
AND    (date_join, COALESCE(date_leave, CURRENT_DATE)) OVERLAPS
       (date '2009-01-01', date '2010-01-01');  -- upper bound excluded

説明

• COALESCE 関数は、次の目的で使用されます。 NULL の date_leave 値をオープン範囲として扱います。
• OVERLAPS 演算子は、各間隔の開始をペアの以前の値として自動的に設定します。
• 日付範囲 '[2009-01-01,2010-01-01)' は、上位を除く、半分開いた間隔を表します。

追加考慮事項

• PostgreSQL 9.2 以降では、範囲タイプをインデックス サポートとともに使用でき、範囲ベースのクエリのパフォーマンスが向上します。
• 提供されたクエリはさらに最適化できます。 daterange(date_join, date_leave) 式に式インデックスを作成します。

以上がPostgreSQL でプレーヤー チーム メンバーシップの重複する日付範囲を特定する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。