sql - 違う条件 - WHERE句で同じ列を複数回使用する




sql 複数カラム 検索 (6)

私は次のような表構造を持っています。

USERS

PROPERTY_VALUE

PROPERTY_NAME

USER_PROPERTY_MAP

property_valueテーブルに一致するプロパティを持つusersテーブルからユーザーを取得しようとしています。

1人のユーザーが複数のプロパティを持つことができます。 この例のデータには、ユーザー '1'の2つのプロパティがありますが、2つ以上になる可能性があります。これらすべてのユーザープロパティをWHERE句で使用します。

このクエリは、ユーザーが単一のプロパティを持っていても複数のプロパティに対して失敗した場合に機能します。

SELECT * FROM users u
INNER JOIN user_property_map upm ON u.id = upm.user_id
INNER JOIN property_value pv ON upm.property_value_id = pv.id
INNER JOIN property_name pn ON pv.property_name_id = pn.id
WHERE (pn.id = 1 AND pv.id IN (SELECT id FROM property_value WHERE value like '101')
AND pn.id = 2 AND pv.id IN (SELECT id FROM property_value WHERE value like '102')) and u.user_name = 'user1' and u.city = 'city1'

クエリにはpn.id = 1 AND pn.id = 2があるので、 pn.idは1でも2でもpn.idませんが、両方ではないため常に失敗します。 それでは、n個のプロパティに対して機能するようにそれを書き直すにはどうすればよいでしょうか。

上記のデータ例では、 WHERE句で使用される両方の一致するプロパティを持つid = 1ユーザーは1人だけです。 クエリは、 USERSテーブルのすべての列を含む単一のレコードを返す必要があります。

要件を明確にする

私はシステム内のすべてのユーザーをリストするUI上のユーザーリストページを持つアプリケーションを開発しています。 このリストには、ユーザーID、ユーザー名、市区USERSなど、 USERSテーブルのすべての列などの情報があります。 ユーザーは上記のデータベースモデルで詳述されているようにプロパティを持つことができます。

ユーザーリストページには、これらのプロパティに基づいてユーザーを検索する機能もあります。 2つのプロパティ 'property1'と 'property2'を持つユーザーを検索する場合、ページは一致する行のみを取得して表示する必要があります。 上記のテストデータに基づいて、ユーザー「1」のみが請求書に適合します。

「property1」と「property2」 を含む 4つのプロパティ持つユーザーが資格を持ちます。 しかし、1つのプロパティ「property1」のみを持つユーザーは、「property2」が欠落しているために除外されます。


2つのpn.id=1pn.id=2間でAND演算子を使用しています。 それでは、あなたはどうやって答えを得るのでしょう。

(SELECT id FROM property_value WHERE value like '101') and
(SELECT id FROM property_value WHERE value like '102') 

上記のコメントのように、Use or operator。

更新1:

SELECT * FROM users u
INNER JOIN user_property_map upm ON u.id = upm.user_id
INNER JOIN property_value pv ON upm.property_value_id = pv.id
INNER JOIN property_name pn ON pv.property_name_id = pn.id
WHERE pn.id in (1,2) AND pv.id IN (SELECT id FROM property_value WHERE value like '101' or value like '102');

USERSテーブルのすべてのフィールドを選択したいとします。

SELECT u.* 
FROM USERS u
INNER JOIN 
(
    SELECT USERS.id as user_id, COUNT(*) as matching_property_count
    FROM USERS
    INNER JOIN (
        SELECT m.user_id, n.name as property_name, v.value
        FROM PROPERTY_NAME n
        INNER JOIN PROPERTY_VALUE v ON n.id = v.property_name_id
        INNER JOIN USER_PROPERTY_MAP m ON m.property_value_id = v.property_value_id
        WHERE  (n.id = @property_id_1 AND v.value = @property_value_1) -- Property Condition 1
            OR (n.id = @property_id_2 AND v.value = @property_value_2) -- Property Condition 2
            OR (n.id = @property_id_3 AND v.value = @property_value_3) -- Property Condition 3
            OR (n.id = @property_id_N AND v.value = @property_value_N) -- Property Condition N
    ) USER_PROPERTIES ON USER_PROPERTIES.user_id = USERS.id
    GROUP BY USERS.id
    HAVING COUNT(*) = N     --N = the number of Property Condition in the WHERE clause
    -- Note : 
    -- Use HAVING COUNT(*) = N if property matches will be "MUST MATCH ALL"
    -- Use HAVING COUNT(*) > 0 if property matches will be "MUST MATCH AT LEAST ONE"
) USER_MATCHING_PROPERTY_COUNT ON u.id = USER_MATCHING_PROPERTY_COUNT.user_id

これはrelational-division場合です。 質問にタグを追加しました。

索引

USER_PROPERTY_MAP(property_value_id, user_id) - この順序で列にPKまたはUNIQUE制約があると仮定すると、クエリが高速になります。 関連する

PROPERTY_VALUE(value, property_name_id, id)もインデックスが必要です。 繰り返しますが、この順序で列を並べます。 最後の列のidを追加するのは、その列からインデックスのみのスキャンを取得する場合だけにしてください。

与えられた数のプロパティに対して

それを解決する多くの方法があります。 これは、 2つのプロパティに対して最も簡単で最速の方法の1つです。

SELECT u.*
FROM   users             u
JOIN   user_property_map up1 ON up1.user_id = u.id
JOIN   user_property_map up2 USING (user_id)
WHERE  up1.property_value_id =
      (SELECT id FROM property_value WHERE property_name_id = 1 AND value = '101')
AND    up2.property_value_id =
      (SELECT id FROM property_value WHERE property_name_id = 2 AND value = '102')
-- AND    u.user_name = 'user1'  -- more filters?
-- AND    u.city = 'city1'

あなたのサンプルクエリによると、あなたは既にプロパティ名をIDに解決しているようだから、テーブルPROPERTY_NAME訪問しない。 それ以外の場合は、各副問合せでPROPERTY_NAMEに結合を追加できます。

私達はこの関連質問の下で技術の武器庫を集めました:

未知数の物件について

@Mikeと@Valeraはそれぞれの回答に非常に便利なクエリを持っています。 これをさらに動的にするには

WITH input(property_name_id, value) AS (
      VALUES  -- provide n rows with input parameters here
        (1, '101')
      , (2, '102')
      -- more?
      ) 
SELECT *
FROM   users u
JOIN  (
   SELECT up.user_id AS id
   FROM   input
   JOIN   property_value    pv USING (property_name_id, value)
   JOIN   user_property_map up ON up.property_value_id = pv.id
   GROUP  BY 1
   HAVING count(*) = (SELECT count(*) FROM input)
   ) sub USING (id);

VALUES式から行を追加/削除するだけです。 または、 プロパティ句がまったくない場合は、 WITH句とJOINを削除します。

このクラスのクエリ(すべての部分一致をカウントする)の問題はパフォーマンスです。 最初のクエリはあまり動的ではありませんが、通常はかなり高速です。 ( EXPLAIN ANALYZEテストしてEXPLAIN ANALYZE 。)特に大きなテーブルと増え続けるプロパティに対して。

両方の長所?

再帰的CTEを使用したこのソリューションは、妥協点として最適です。高速かつ動的です。

WITH RECURSIVE input AS (
   SELECT count(*)     OVER () AS ct
        , row_number() OVER () AS rn
        , *
   FROM  (
      VALUES  -- provide n rows with input parameters here
        (1, '101')
      , (2, '102')
      -- more?
      ) i (property_name_id, value)
   )
 , rcte AS (
   SELECT i.ct, i.rn, up.user_id AS id
   FROM   input             i
   JOIN   property_value    pv USING (property_name_id, value)
   JOIN   user_property_map up ON up.property_value_id = pv.id
   WHERE  i.rn = 1

   UNION ALL
   SELECT i.ct, i.rn, up.user_id
   FROM   rcte              r
   JOIN   input             i ON i.rn = r.rn + 1
   JOIN   property_value    pv USING (property_name_id, value)
   JOIN   user_property_map up ON up.property_value_id = pv.id
                              AND up.user_id = r.id
   )
SELECT u.*
FROM   rcte  r
JOIN   users u USING (id)
WHERE  r.ct = r.rn;          -- has all matches

dbfiddleはhere

再帰的CTEに関するマニュアル。

複雑さが増しても、追加のオーバーヘッドが利益を上回る、またはその差が最初は無視できるほど小さい小さなテーブルには支障はありません。 しかし、それははるかに優れた拡張性を持ち、成長するテーブルと増加する数のプロパティフィルタを使った「カウント」手法よりもますます優れています。

カウントテクニックは、与えられたすべてのプロパティフィルタについてuser_property_map すべての行を訪問する必要がありますが、このクエリ(および1番目のクエリ)は関係のないユーザーを早期に排除することができます。

パフォーマンスを最適化する

現在のテーブル統計(妥当な設定、 autovacuum実行)では、Postgresは各列の"最も一般的な値"についての知識を持ち、 最初のクエリで結合を並べ替えて最も選択性の高いプロパティフィルタを最初に評価します。 。 一定の限度まで: join_collapse_limit 。 関連する

この "deus-ex-machina"介入は、 3番目のクエリ (再帰的CTE)では不可能です。 パフォーマンスを向上させるには(おそらく多くの場合)、自分で選択フィルターを最初に配置する必要があります。 しかし、最悪の場合の順序付けでも、それはまだカウントクエリよりも優れています。

関連する

もっとゴージャスな詳細:

マニュアルの詳細説明:


フィルタリングしたいだけなら:

SELECT users.*
FROM users
where (
    select count(*)
    from user_property_map
    left join property_value on user_property_map.property_value_id = property_value.id
    left join property_name on property_value.property_name_id = property_name.id
    where user_property_map.user_id = users.id -- join with users table
    and (property_name.name, property_value.value) in (
        values ('property1', '101'), ('property2', '102') -- filter properties by name and value
    )
) = 2 -- number of properties you filter by

あるいは、ユーザーを一致数の多い順に並べ替える必要がある場合は、次のようにします。

select * from (
    SELECT users.*, (
        select count(*) as property_matches
        from user_property_map
        left join property_value on user_property_map.property_value_id = property_value.id
        left join property_name on property_value.property_name_id = property_name.id
        where user_property_map.user_id = users.id -- join with users table
        and (property_name.name, property_value.value) in (
            values ('property1', '101'), ('property2', '102') -- filter properties by name and value
        )
    )
    FROM users
) t
order by property_matches desc

SELECT *
  FROM users u
 WHERE u.id IN(
         select m.user_id
           from property_value v
           join USER_PROPERTY_MAP m
             on v.id=m.property_value_id 
          where (v.property_name_id, v.value) in( (1, '101'), (2, '102') )
          group by m.user_id
         having count(*)=2
      )

または

SELECT u.id
  FROM users u
 INNER JOIN user_property_map upm ON u.id = upm.user_id
 INNER JOIN property_value pv ON upm.property_value_id = pv.id
 WHERE (pv.property_name_id=1 and pv.value='101')
    OR (pv.property_name_id=2 and pv.value='102')
 GROUP BY u.id
HAVING count(*)=2

propery_name_idがわかっていれば、queryにproperty_nameテーブルは必要ありません。


SELECT * FROM users u
INNER JOIN user_property_map upm ON u.id = upm.user_id
INNER JOIN property_value pv ON upm.property_value_id = pv.id
INNER JOIN property_name pn ON pv.property_name_id = pn.id
WHERE (pn.id = 1 AND pv.id IN (SELECT id FROM property_value WHERE value 
like '101') )
OR ( pn.id = 2 AND pv.id IN (SELECT id FROM property_value WHERE value like 
'102'))

OR (...)
OR (...)

SAME ROWに対してidが1と2のような場合はないので、ANDはできません。各行にwhere条件を指定します。

次のように簡単なテストを実行するとします。

SELECT * FROM users where id=1 and id=2 

あなたは0件の結果を得るでしょう。 その用途を達成するために

 id in (1,2) 

または

 id=1 or id=2

そのクエリはもっと​​最適化することができますが、これは良いスタートです。





relational-division