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)<= ?

ただし、このクエリは重複する日付範囲を効果的に識別しません。

正しい解決策

重複する日付範囲を正確に判断するには、次の基準を考慮するようにクエリを変更する必要があります:

1. 以下のプレイヤー指定された年の範囲が始まる前にチームに参加したプレーヤー。
2. 指定された年の範囲が終わる前にチームを離れなかったプレーヤー。
3. チームを離れなかったプレーヤー (NULL で表される) date_leave 値)。

最適化済みクエリ

次のクエリは、より正確な解決策を提供します。

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 サイトの他の関連記事を参照してください。