Использование одного и того же столбца несколько раз в предложении WHERE

Ответ 1

Это случай relational-division. Я добавил тег.

Индексы

Предполагая ограничение PK или UNIQUE для USER_PROPERTY_MAP(property_value_id, user_id) - в этом порядке, чтобы мои запросы USER_PROPERTY_MAP(property_value_id, user_id) быстро. Связанные с:

• Составной индекс также хорош для запросов по первому полю?

У вас также должен быть индекс PROPERTY_VALUE(value, property_name_id, id). Опять же, столбцы в этом порядке. Добавляйте последний id столбца только в том случае, если вы получаете только сканирование по индексу.

Для данного количества свойств

Есть много способов ее решить. Это должно быть одно из самых простых и быстрых точно для двух свойств:

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'

Не посещать таблицу PROPERTY_NAME, так как вы, кажется, уже разрешили имена свойств в идентификаторы, согласно вашему примеру запроса. В противном случае вы можете добавить присоединение к PROPERTY_NAME в каждом подзапросе.

Мы собрали арсенал приемов по этому смежному вопросу:

• Как отфильтровать результаты SQL в сквозном отношении

Для неизвестного количества свойств

У @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.) Особенно для больших столов и растущего числа свойств.

Лучшее из обоих миров?

Это решение с рекурсивным 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

здесь

Руководство по рекурсивным CTE.

Дополнительная сложность не окупается для небольших столов, где дополнительные накладные расходы перевешивают любую выгоду или разница незначительна для начала. Но он масштабируется намного лучше и все больше превосходит методы "подсчета" с растущими таблицами и растущим числом фильтров свойств.

Методы подсчета должны посещать все строки в user_property_map для всех заданных фильтров свойств, в то время как этот запрос (так же как и 1-й запрос) может устранить ненужных пользователей на раннем этапе.

Оптимизация производительности

Имея текущую статистику таблиц (разумные настройки, запуск autovacuum), Postgres знает о "наиболее распространенных значениях" в каждом столбце и будет переупорядочивать объединения в 1-м запросе, чтобы сначала оценить наиболее селективные фильтры свойств (или, по крайней мере, не наименее селективные), До определенного предела: join_collapse_limit. Связанные с:

• Postgresql join_collapse_limit и время для планирования запросов
• Почему небольшое изменение в поисковом запросе так сильно замедляет запрос?

Это вмешательство "deus-ex-machina" невозможно с 3-м запросом (рекурсивный CTE). Чтобы повысить производительность (возможно, многое), вы должны сначала установить более селективные фильтры. Но даже при наихудшем порядке это все равно превзойдет количество запросов.

Связанные с:

• Проверка статистики целей в PostgreSQL

Гораздо больше кровавых подробностей:

• Частичный индекс PostgreSQL не используется при создании таблицы с существующими данными

Больше объяснений в руководстве:

• Статистика, используемая планировщиком

Ответ 2

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

Нет property_name таблица, необходимая для запроса, если верно имя_имя_ид.

Ответ 3

Если вы хотите просто фильтровать:

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

Ответ 4

вы используете оператор AND между двумя pn.id=1 и pn.id=2. то как вы получаете ответ между этим:

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

Как и выше, используйте оператор or.

Обновление 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');

Ответ 5

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 (...)

Вы не можете сделать AND, потому что нет такого случая, когда id равен 1 и 2 для SAME ROW, вы указываете условие where для каждой строки!

Если вы запустите простой тест, например

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

вы получите 0 результатов. Чтобы добиться этого, используйте

 id in (1,2) 

или

 id=1 or id=2

Этот запрос можно оптимизировать, но это хорошее начало, я надеюсь.

Ответ 6

Если вам просто нужны разные столбцы в U, это:

SELECT DISTINCT u.* 
  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.[value] = '101')
     OR (pn.id = 2 AND pv.[value] = '102')

Заметьте, что я использовал pv.[value] = вместо подзапроса для повторного ввода идентификатора... это упрощение.

Ответ 7

Если я правильно понял ваш вопрос, я бы сделал это так.

SELECT u.id, u.user_name, u.city FROM users u 
WHERE (SELECT count(*) FROM property_value v, user_property_map m 
WHERE m.user_id = u.id AND m.property_value_id = v.id AND v.value IN ('101', '102')) = 2

Это должно вернуть список пользователей, у которых есть все свойства, перечисленные в разделе IN. 2 представляет количество искомых свойств.

Ответ 8

Предполагая, что вы хотите выбрать все поля в таблице 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