VPF::Выборка по фильтру - Форум программистов

Aliance

Дата 11.4.2012, 09:31 (ссылка)

(нет голосов)

Загрузка ...

I ♥ <script>

Профиль
Группа: Модератор
Сообщений: 6418
Регистрация: 2.8.2004
Где: spb

Репутация: нет
Всего: 137

Имеет таблицу вида:

Цитата

param1 param2 param3 param4 value
  1     NULL   NULL   NULL    70
  1       3    NULL   NULL    80
  1       3      6    NULL    90
  1       8      9     10     100

Имеем функцию, которая на входе получает 4 параметра всегда, а на выходе должна вернуть значение из таблицы, наиболее удовл. выборке.
Например что-то типа:

Код


function pseudoFunc(param1, param2, param3, param4)
{
    $value = db_query('...');
    return $value;
}

Выборка идет такая:

Код


print pseudoFunc(1,2,3,4); // должно вернуть 70
print pseudoFunc(1,3,3,4); // должно вернуть 80
print pseudoFunc(1,3,6,4); // должно вернуть 90
print pseudoFunc(1,8,3,4); // должно вернуть 70
print pseudoFunc(1,8,9,4); // должно вернуть 70
print pseudoFunc(1,8,9,10); // должно вернуть 100

То есть если значение NULL - подразумевается, что это маска * для любого числа.
В общем-то можно сделать нужный мне запрос через комбинацию 8 union`ов, но это слишком мне кажется неправильным.

Это сообщение отредактировал(а) Aliance - 11.4.2012, 09:34

--------------------

• Все о событиях
• Объекты и массивы
• ОКНА И ФРЕЙМЫ

JAVA ≠ JavaScript

Zloxa

Дата 11.4.2012, 10:11 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Код


where (param1 = :param1 or param1 is null)
    and (param2 = :param2 or param2 is null)
    and (param3 = :param3 or param3 is null)
    and (param4 = :param4 or param4 is null)

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Aliance

Дата 11.4.2012, 10:34 (ссылка)

(нет голосов)

Загрузка ...

I ♥ <script>

Профиль
Группа: Модератор
Сообщений: 6418
Регистрация: 2.8.2004
Где: spb

Репутация: нет
Всего: 137

Таковым было и мое первое предположение, но оказалось, что оно неработоспособное.

При предложенном тобой запросе функция:

Код

print pseudoFunc(1,3,100,500);

вернет первые три строчки, а должен только вторую (т.е. 80), т.к. она более релевантна.

Была идея выставлять веса, которую я реализовал в варианте решения с union`ом, дальше сортировать по весам и делать limit 1. Но, повторюсь, такое решение кажется мне не совсем удачным.

Это сообщение отредактировал(а) Aliance - 11.4.2012, 10:36

--------------------

• Все о событиях
• Объекты и массивы
• ОКНА И ФРЕЙМЫ

JAVA ≠ JavaScript

Aliance

Дата 11.4.2012, 11:52 (ссылка)

(нет голосов)

Загрузка ...

I ♥ <script>

Профиль
Группа: Модератор
Сообщений: 6418
Регистрация: 2.8.2004
Где: spb

Репутация: нет
Всего: 137

В общем расскажу как я решил задачу, может быть это натолкнёт кого-то на более разумное решение.

У меня есть 5 параметров, один из них заполнен всегда, остальные могут быть null - что фактически означает "любой"

Цитата

user service number operator region commision
  1     NULL   NULL   NULL   NULL    70
  1       2    NULL   NULL   NULL    80
  1       2      3    NULL   NULL    90
  1       2      4     5     NULL    92
  1       2      4     5      8      86
  1       20     4     5     NULL   100

Грубо говоря табличка комиссий. То есть например мы хотим узнать какая комиссия у пользователя 1 на его сервисе 2 с номером 3 - это будет 90%. А если на любых номерах - то 80%.

Итого у нас получается 5 параметров. То есть варинтов комбинаций этих параметров - !5 = 15.
Я сделал 15 union`ов, выставляя каждому вес (приоритет). Например самый большой приоритет у той записи, в которой заполнены все 5 параметров (приоритет = 15). Самый маленький приоритет у той записи, у который заполнен только первый параметр (приоритет 1). Дальше делаю order by priority limit 1. Вот код с юнионами:

Код


SELECT
`percent`, 15 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` = '{$service_id}'
AND
`short_number` = '{$short_number}'
AND
`operator_id` = '{$operator_id}'
AND
`region_id` = '{$region_id}'
UNION
SELECT
`percent`, 14 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` = '{$service_id}'
AND
`short_number` = '{$short_number}'
AND
`operator_id` = '{$operator_id}'
AND
`region_id` IS NULL
UNION
SELECT
`percent`, 13 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` = '{$service_id}'
AND
`short_number` = '{$short_number}'
AND
`operator_id` IS NULL
AND
`region_id` = '{$region_id}'
UNION
SELECT
`percent`, 12 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` = '{$service_id}'
AND
`short_number` = '{$short_number}'
AND
`operator_id` IS NULL
AND
`region_id` IS NULL
UNION
SELECT
`percent`, 11 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` = '{$service_id}'
AND
`short_number` IS NULL
AND
`operator_id` = '{$operator_id}'
AND
`region_id` = '{$region_id}'
UNION
SELECT
`percent`, 10 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` = '{$service_id}'
AND
`short_number` IS NULL
AND
`operator_id` = '{$operator_id}'
AND
`region_id` IS NULL
UNION
SELECT
`percent`, 9 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` = '{$service_id}'
AND
`short_number` IS NULL
AND
`operator_id` IS NULL
AND
`region_id` IS NULL
UNION
SELECT
`percent`, 8 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` = '{$short_number}'
AND
`operator_id` = '{$operator_id}'
AND
`region_id` = '{$region_id}'
UNION
SELECT
`percent`, 7 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` = '{$short_number}'
AND
`operator_id` = '{$operator_id}'
AND
`region_id` IS NULL
UNION
SELECT
`percent`, 6 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` = '{$short_number}'
AND
`operator_id` IS NULL
AND
`region_id` = '{$region_id}'
UNION
SELECT
`percent`, 5 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` = '{$short_number}'
AND
`operator_id` IS NULL
AND
`region_id` IS NULL
UNION
SELECT
`percent`, 4 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` IS NULL
AND
`operator_id` = '{$operator_id}'
AND
`region_id` = '{$region_id}'
UNION
SELECT
`percent`, 3 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` IS NULL
AND
`operator_id` = '{$operator_id}'
AND
`region_id` IS NULL
UNION
SELECT
`percent`, 2 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` IS NULL
AND
`operator_id` IS NULL
AND
`region_id` = '{$region_id}'
UNION
SELECT
`percent`, 1 as `priority`
FROM
`ma_percent`
WHERE
`partner_id` = '{$partner_id}'
AND
`service_id` IS NULL
AND
`short_number` IS NULL
AND
`operator_id` IS NULL
AND
`region_id` IS NULL
ORDER BY priority DESC
LIMIT 1

Знаю, что извращение, но пока не придумал способа изящнее - этот работает. Вопрос вот в чем - а есть ли он, способ изящнее? smile

Это сообщение отредактировал(а) Aliance - 11.4.2012, 11:58

--------------------

• Все о событиях
• Объекты и массивы
• ОКНА И ФРЕЙМЫ

JAVA ≠ JavaScript

Zloxa

Дата 11.4.2012, 12:07 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Цитата(Aliance @ 11.4.2012, 11:34

)

вернет первые три строчки

вобщета - две

Цитата(Aliance @ 11.4.2012, 11:34

)

только вторую (т.е. 80), т.к. она более релевантна.

Код


order by case when param1 = :param1 then 1 else 0 end
         +case when param2 = :param2 then 1 else 0 end
         +case when param3 = :param3 then 1 else 0 end
         +case when param4 = :param4 then 1 else 0 end
      desc
limit 1

Добавлено через 6 минут и 28 секунд
Но вообще - по хорошему, ваш подход предпочтительнее наверное будет. Он, в отличии от предложенного мной сможет задействовать индексы.. Только вместо union используйте union all.

А еще лучше это все делать не одним запросом а несколькими. Останавливаясь, когда результат получен.

Добавлено через 11 минут и 40 секунд

Цитата(Zloxa @ 11.4.2012, 13:07

)

order by

Я предполагал, что этот ордербай надо добавить к where, хотя и без него работать будет. И, пожалуй столь же мало эффективно как без where

Добавлено через 13 минут и 31 секунду
И, да, вместо еденичек в кейсе ордербая, можно добавить веса 1,2,4,8...

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Garcian

Дата 12.4.2012, 17:51 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 125
Регистрация: 5.11.2008

Репутация: нет
Всего: нет

Цитата(Aliance @ 11.4.2012, 11:52

)

То есть варинтов комбинаций этих параметров - !5 = 15.

Я чего то не понял, это Вы как посчитали?

--------------------

Неродивый студент

Aliance

Дата 13.4.2012, 10:57 (ссылка)

(нет голосов)

Загрузка ...

I ♥ <script>

Профиль
Группа: Модератор
Сообщений: 6418
Регистрация: 2.8.2004
Где: spb

Репутация: нет
Всего: 137

Garcian, теория вероятности, раздел комбинаторика.

--------------------

• Все о событиях
• Объекты и массивы
• ОКНА И ФРЕЙМЫ

JAVA ≠ JavaScript

Zloxa

Дата 13.4.2012, 11:46 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Aliance, так то вообще, с комбинаторной точки зрения, для пяти параметров - 31 комбинация (если исключить случай, когда все is null)

Код


         1 NULL   NULL   NULL   NULL   NULL
         2 NULL   NULL   NULL   NULL   val
         3 NULL   NULL   NULL   val    NULL
         4 NULL   NULL   NULL   val    val
         5 NULL   NULL   val    NULL   NULL
         6 NULL   NULL   val    NULL   val
         7 NULL   NULL   val    val    NULL
         8 NULL   NULL   val    val    val
         9 NULL   val    NULL   NULL   NULL
        10 NULL   val    NULL   NULL   val
        11 NULL   val    NULL   val    NULL
        12 NULL   val    NULL   val    val
        13 NULL   val    val    NULL   NULL
        14 NULL   val    val    NULL   val
        15 NULL   val    val    val    NULL
        16 NULL   val    val    val    val
        17 val    NULL   NULL   NULL   NULL
        18 val    NULL   NULL   NULL   val
        19 val    NULL   NULL   val    NULL
        20 val    NULL   NULL   val    val
        21 val    NULL   val    NULL   NULL
        22 val    NULL   val    NULL   val
        23 val    NULL   val    val    NULL
        24 val    NULL   val    val    val
        25 val    val    NULL   NULL   NULL
        26 val    val    NULL   NULL   val
        27 val    val    NULL   val    NULL
        28 val    val    NULL   val    val
        29 val    val    val    NULL   NULL
        30 val    val    val    NULL   val
        31 val    val    val    val    NULL
        32 val    val    val    val    val

Другое дело, если у вас могут быть исключены какие то комбинации(поле определено как not null). Но тут уже отсыл не к комбинаторике smile

Это сообщение отредактировал(а) Zloxa - 13.4.2012, 11:49

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Aliance

Дата 17.4.2012, 14:24 (ссылка)

(нет голосов)

Загрузка ...

I ♥ <script>

Профиль
Группа: Модератор
Сообщений: 6418
Регистрация: 2.8.2004
Где: spb

Репутация: нет
Всего: 137

Цитата(Zloxa @ 13.4.2012, 12:46

)

Другое дело, если у вас могут быть исключены какие то комбинации(поле определено как not null). Но тут уже отсыл не к комбинаторике

так и есть, первый параметр всегда задан.

--------------------

• Все о событиях
• Объекты и массивы
• ОКНА И ФРЕЙМЫ

JAVA ≠ JavaScript

Zloxa

Дата 17.4.2012, 14:30 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Цитата(Aliance @ 17.4.2012, 15:24

)

так и есть, первый параметр всегда задан.

Да, вы об этом говорили, не углядел, сорри.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MySQL \| Следующая тема »