Поиск значения в диапазоне

Существует общепринятый подход к таблицам, хранящим значения диапазонов, и поиска в них. Выглядит он примерно так:

НижняяГраница ВерхняяГраница Ставка

0,00 9,99 0,05

10,00 19,99 0,10

20,00 49,99 0,12

50,00 99999999,00 0,13

С такой таблицей обычно используют запрос типа:

SELECT Ставка
    FROM Ставки
    WHERE [Ввести количество:]
      BETWEEN НижняяГраница and ВерхняяГраница

Я предпочитаю не использовать подобное решение.

Как только вы дадите пользователям возможность совершать ошибки, у вас появятся проблемы. Если пользователи наделены правами указывать диапазон с помощью верхней и нижней границы, они почти наверняка создадут диапазон с перекрывающимися границами либо с дырками. В вышеуказанной таблице фактически имеются дырки, которые станут очевидны при искомой величине 9,993: запрос вообще не вернет записей!

Вместо этого, вполне достаточно указания только одной, конечной точки в каждой записи таблицы со ставками. Хотя составление запроса не будет таким простым, как в предыдущем случае, выполняться он будет достаточно хорошо, поскольку количество записей в таблице ставок наверняка будет небольшим. И в самом деле, индекс для таблицы будет на одном-единственном столбце, так что Аксесс быстро найдет требуемые записи или запись.

Существует принцип в проектировании баз данных: не хранить зависимые значения. Этот принцип вполне можно интерпретировать в отношении к обсуждаемой теме. Вы можете получить "несуществующее" значение, верхнюю либо нижнюю границу любого диапазона, так как это либо предыдущее либо последующее значение из ряда величин (для нижней или верхней границы, соответственно) при сортировке по этому столбцу.

Этот принцип (не хранить зависимые значения) преследует точно такую же цель, как и в более простых случаях, когда зависимость существует между столбцами одной записи. Принцип заключается в том, что когда зависимая величина хранится, но не совпадает с тем, что должно получится в результате, хранимая величина оказывается некорректной и запрос вернет неверные результаты по этой причине. Альтернатива - сверять расчетную величину с хранимой и заменять ее при необходимости. Однако, это потребует запрос по крайней мере настолько же сложный, как и тот, которого вы стремились избежать при расчете "недостающей" величины.

Запрос, который я предлагаю, в общем случае требует использования подзапроса, чтобы найти искомый диапазон, и это слегка разочаровывает тех, кто ищет здесь нашего совета.

Я надеюсь, что освещение моей точки зрения поможет целевой аудитории увидеть альтернативные решения. Итак, я проиллюстрирую внимательным читателям свой подход.

На какой-то стадии построения запроса вам потребуется Ставка - для дальнейших вычислений, или для вывода на экран, или для того и другого. Эта ставка получается из таблицы диапазонов следующим образом:

От До Ставка

0,00 9,99 5%

10,00 19,99 10%

20,00 49,99 12%

50,00 13%

Этот последний диапазон представляет "все, что равно 50-ти или выше."

Есть два способа хранения этого: с помощью значения в колонке От или значения в колонке До. В подобном случае я обычно выбираю для хранения "четные, целые величины", то есть колонку От. Таблица будет выгладеть так:

Минимум Ставка

0,00 0,05

10,00 0,10

20,00 0,12

50,00 0,13

В этой таблице я предлагаю сделать первичным ключом колонку "Минимум".

Если искомая величина в столбце запроса называется Искомое, тогда подзапрос, возвращающий ставку будет:

(SELECT Ставка
    FROM Ставки СТ1
    WHERE Минимум =
      (SELECT MAX(Минимум)
        FROM  Ставки СТ2
        WHERE Минимум <= Искомое))

Итак теперь мы имеем двухэтажный подзапрос. Ну да, он непрост и это как раз то, чего мы возможно хотели бы избежать. В самом деле, это может быть проблемой. так как аксессовский движок Jet не всегда успешно обрабатывает такие запросы. Я полагаю, что это из-за того, что внутренний запрос находятся на двух уровнях ниже своего источника данных во внешнем запросе.

Таким образом, решение становится (к сожалению) даже более сложным. На самом деле же, для ищущих помощи оно может быть более полезным, так как становится видно, что происходит шаг за шагом.

Решением является запрос, который почти повторяет внешний вид изначальной таблицы - той, что со столбцами От и До, вычисляя колонку До. Тем не менее, я сделаю колонку До на 0,01 больше. Запрос, использующий искомый параметр, будет искать диапазон, у которого искомая величина >= От и < До (не меньше или равно!!!)

SELECT Минимум,
    (SELECT MIN(СТ1.Минимум)
      FROM Ставки СТ1
      WHERE СТ1.Минимум > СТ.Минимум)
    AS Максимум,
    Ставка
    FROM Ставки СТ

Если желаете, можете воспроизвести в точности оригинальные величины путем вычитания 0,01 из Максимума. Я предпочитаю не делать эого. Если запрос должен высчитать величину искомого параметра и эта величина не округлена до ближайшей "копейки", тогда возможно, что искомый параметр будет равен 9,993. В оригинальной таблице ставок нет величины ставки для 9,993. Я осознаю, что мы, земляне, вполне вероятно выбрали бы ставку для диапазона от 0,00 до 9,99, но компьютер не сделает этого. Вычисляя верхнюю границу, как я показал выше, и затем ограничивая значение до величины, меньшей, чем верхняя граница, такое поведение компьютера может быть исправлено, исключая "дырки" в структуре диапазонов. Вот где и взвешенное решение для определения, на какой колонке основывать реальные данные (подход с одной колонкой), становится полезным и вот почему я выбираю колнку с "целыми" величинами для этой цели.

В реальности не существует замены необходимости помнить об округлении величин при вычислении входного параметра, чтобы все это работало корректно. Если вам нужно, чтобы 9,993 оказалось в диапазоне от 0,00 до 9,99, то вы обязаны округлять это значение, прежде чем использовать его в поиске диапазона.

Примечания:

[1]Если вы не вполне знакомы с подзапросами, см.: How to Create and Use Subqueries.

Конструктор сайтов - uCoz