Range sql: range table-valued function — Azure Databricks — Databricks SQL

Сервер

Содержание

sql — в Sql, как рассчитать значения на основе диапазона?

Задавать вопрос

спросил
3 года 8 месяцев назад

Изменено
3 года, 1 месяц назад

Просмотрено
2к раз

Я создал таблицу и получил столбец счетчика после выполнения других операций. Теперь я хочу рассчитать значение на основе диапазона, в котором находится счет. Если он пересекает диапазон, рассмотрите максимальный диапазон.

Например:
Пример 1

Граф | Мин | Макс | Количество
----------------------------------
  4 | 1 | 1 | 5
  4 | 2 | 2 | 10
  4 | 3 | 5 | 12
  4 | 6 | 8 | 15

Пример 2

Граф | Мин | Макс | Количество
----------------------------------
  4 | 1 | 1 | 5
  4 | 2 | 2 | 10

Результаты:
Какие я хочу результаты.
Например, пример 1: число (4) находится между Min(3) и Max(4), поэтому нам нужно учитывать сумму из этой строки и результат, как показано ниже.

Граф | Мин | Макс | Сумма | Общая сумма
--------------------------------------------------------------
  4 | 3 | 5 | 12 | 48 (Количество * Количество)

Для примера 2: число (4) выходит за все пределы, поэтому нам нужно учитывать сумму из строки с более высоким диапазоном и результат, как показано ниже.

Граф | Мин | Макс | Сумма | Общая сумма
--------------------------------------------------------------
  4 | 2 | 2 | 10 | 20 (макс. * количество)

sql
sql-сервер
tsql

Я думаю, вы хотите:

 выбрать верх (1) т.*, (сумма * количество)
от т
порядок (случай, когда счет между минимумом и максимумом, затем 1, иначе 2 заканчивается),
          сумма по убыванию;

Примечание. count , min и max — плохой выбор для имен столбцов, поскольку они являются встроенными функциями SQL.

Это то, что вы ожидаете?

Пример данных

 SELECT * INTO #TAB FROM (
 ВЫБЕРИТЕ 4 КАК КОЛИЧЕСТВО, 1 КАК МИН., 1 КАК МАКС., 5 КАК СУММА
    СОЮЗ ВСЕХ
 ВЫБЕРИТЕ 4,2,2,10
   СОЮЗ ВСЕХ
 ВЫБЕРИТЕ 4,3,5,12
   СОЮЗ ВСЕХ
 ВЫБЕРИТЕ 4,6,8,15 ) КАК А

Запрос:

 ВЫБЕРИТЕ Т.*,
IIF(COUNTNO МЕЖДУ MINNO И MAXNO, CountNO * Amount, MAXNO*AMOUNT) ExpectedOp FROM #TAB T

SQL Fiddle

Настройка схемы MS SQL Server 2017 :

 создать тестовую таблицу (count int,
                  мин инт,
                  макс инт,
                 сумма внутр.)
вставьте тестовые значения (4,3,5,12),
                  (4,2,2,10)

Запрос 1 :

 SELECT *,CASE
            КОГДА count >min и count<=max
               ТОГДА посчитайте * количество
               ИНАЧЕ Макс. * Сумма
       КОНЕЦ как TotalAmount
ОТ теста

Результаты :

 | считать | мин | макс | сумма | Общая сумма |
|-------|-----|-----|--------|-------------|
| 4 | 3 | 5 | 12 | 48 |
| 4 | 2 | 2 | 10 | 20 |

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

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

Индексирование условий диапазона SQL меньше, больше и находится между

Самый большой риск для производительности INDEX RANGE SCAN — это обход конечного узла. Таким образом, золотое правило индексирования состоит в том, чтобы максимально уменьшить диапазон сканируемых индексов. Вы можете проверить это, спросив себя, где начинается просмотр индекса и где он заканчивается.

На этот вопрос легко ответить, если в операторе SQL явно указаны условия запуска и остановки:

 SELECT имя, фамилия, дата_рождения
  ОТ сотрудников
 ГДЕ  date_of_birth >= TO_DATE(?, 'ГГГГ-ММ-ДД')
   AND date_of_birth <= TO_DATE(?, 'ГГГГ-ММ-ДД')

Индекс на DATE_OF_BIRTH сканируется только в указанном диапазоне. Сканирование начинается с первой даты и заканчивается на второй. Мы не можем еще больше сузить диапазон сканируемых индексов.

Условия начала и окончания менее очевидны, если задействован второй столбец:

 SELECT имя, фамилия, дата_рождения
  ОТ сотрудников
 ГДЕ date_of_birth >= TO_DATE(?, 'ГГГГ-ММ-ДД')
   И date_of_birth <= TO_DATE(?, 'ГГГГ-ММ-ДД')
     И дочерний_id = ?

Конечно, идеальный индекс должен охватывать оба столбца, но вопрос в том, в каком порядке?

На следующих рисунках показано влияние порядка столбцов на просканированный диапазон индексов. Для этой иллюстрации мы ищем всех сотрудников дочерней компании 27, которые родились между 1 января ^st и 9 января ^th 1971.

8 — именно в таком порядке. Где база данных начнет следовать по цепочке листовых узлов, или, говоря иначе: где закончится обход дерева?

Рисунок 2.2 Сканирование диапазона в

DATE_OF_BIRTH , SUBSIDIARY_ID Индекс

Индекс сначала упорядочен по датам рождения. Только если два сотрудника родились в один и тот же день, для сортировки этих записей используется SUBSIDIARY_ID . Однако запрос охватывает диапазон дат . Таким образом, порядок SUBSIDIARY_ID бесполезен во время обхода дерева. Это становится очевидным, если вы понимаете, что в узлах ветвления нет записи для дочерней компании 27, хотя она есть в конечных узлах. Фильтр на 9Таким образом, 0047 DATE_OF_BIRTH является единственным условием, ограничивающим диапазон сканируемых индексов. Он начинается с первой записи, соответствующей диапазону дат, и заканчивается последней — всеми пятью конечными узлами, показанными на рис. 2.2.

При изменении порядка столбцов картина выглядит совершенно иначе. На рис. 2.3 показано сканирование, если индекс начинается со столбца SUBSIDIARY_ID .

Рисунок 2.3 Сканирование диапазона в

SUBSIDIARY_ID , DATE_OF_BIRTH Index

Разница в том, что оператор равенства ограничивает первый столбец индекса одним значением. В диапазоне для этого значения ( SUBSIDIARY_ID 27) индекс сортируется по второму столбцу — дате рождения — поэтому нет необходимости посещать первый конечный узел, поскольку узел филиала уже указывает, что нет сотрудника для дочерняя 27 родилась после 25 июня ^-го-го 1969 года в первом листовом узле.

Обход дерева напрямую ведет ко второму листовому узлу. В этом случае все , где условия пункта ограничивают диапазон сканируемых индексов, так что сканирование завершается на том же конечном узле.

Совет

Практическое правило: сначала индекс для равенства, а затем для диапазонов.

Фактическая разница в производительности зависит от данных и критериев поиска. Разница может быть незначительной, если фильтр по DATE_OF_BIRTH сам по себе очень избирательный. Чем больше становится диапазон дат, тем больше будет разница в производительности.

С помощью этого примера мы также можем опровергнуть миф о том, что наиболее селективный столбец должен находиться в крайней левой позиции индекса. Если мы посмотрим на цифры и рассмотрим селективность только первого столбца, мы увидим, что обоим условиям соответствует 13 записей. Это так независимо от того, фильтруем ли мы по DATE_OF_BIRTH только или только SUBSIDIARY_ID . Избирательность здесь бесполезна, но один порядок столбцов все же лучше, чем другой.

Для оптимизации производительности очень важно знать диапазон сканируемых индексов. В большинстве баз данных вы даже можете увидеть это в плане выполнения — вам просто нужно знать, что искать. Следующий план выполнения из базы данных Oracle однозначно указывает, что индекс EMP_TEST начинается с DATE_OF_BIRTH 9столбец 0048.

DB2

 Объяснить план
-------------------------------------------------- --
ID | Операция | Ряды | Расходы
 1 | ВОЗВРАТ | | 26
 2 | ПОЛУЧИТЬ СОТРУДНИКОВ | 3 из 3 (100,00%) | 26
 3 | IXSCAN EMP_TEST | 3 из 10000 (0,03%) | 6
Информация о предикате
 3 - НАЧАЛО (TO_DATE(?, 'ГГГГ-ММ-ДД') <= Q1.DATE_OF_BIRTH)
     НАЧАЛО (Q1.SUBSIDIARY_ID = ?)
      СТОП (Q1.DATE_OF_BIRTH <= TO_DATE(?, 'ГГГГ-ММ-ДД'))
      СТОП (Q1.SUBSIDIARY_ID = ?)
      САРГ (Q1.SUBSIDIARY_ID = ?)

В DB2 предикаты доступа помечены как START и/или STOP , а предикаты фильтра отмечены как SARG .

Оракл

 ------------------------------------------- --------------------
|Идентификатор | Операция | Имя | Ряды | Стоимость |
-------------------------------------------------- ------------
| 0 | ВЫБЕРИТЕ ЗАЯВЛЕНИЕ | | 1 | 4 |
|*1 | ФИЛЬТР | | | |
| 2 | ДОСТУП К ТАБЛИЦАМ ПО ИНДЕКСУ ROWID| СОТРУДНИКИ | 1 | 4 |
|*3 | ИНДЕКС ДИАПАЗОН СКАН | ЭМП_ТЕСТ | 2 | 2 |
-------------------------------------------------- ------------
Информация о предикате (определяется идентификатором операции):
-------------------------------------------------- -
1 – фильтровать(:END_DT >= :START_DT)
3 – доступ (DATE_OF_BIRTH >= :START_DT
       И ДАТА_РОЖДЕНИЯ <= :END_DT)
    фильтр (SUBSIDIARY_ID = :SUBS_ID)

PostgreSQL

 ПЛАН ЗАПРОСА
-------------------------------------------------- ------------------
Сканирование индекса с использованием emp_test для сотрудников
  (стоимость=0,01. .8,59 строк=1 ширина=16)
  Условие индекса: (date_of_birth >= to_date('1971-01-01','ГГГГ-ММ-ДД'))
          И (дата_рождения <= to_date('1971-01-10','ГГГГ-ММ-ДД'))
          AND (subsidiary_id = 27::numeric)

База данных PostgreSQL не указывает доступ к индексу и предикаты фильтрации в плане выполнения. Тем не менее, Раздел Index Cond перечисляет столбцы в порядке определения индекса. В этом случае мы сначала видим два предиката DATE_OF_BIRTH , чем SUBSIDIARY_ID . Зная, что любые предикаты, следующие за условием диапазона, не могут быть предикатом доступа, SUBSIDIARY_ID должен быть предикатом фильтра. Дополнительные сведения см. в разделе « Различение предикатов доступа и фильтров ».

SQL Server

 | -- Вложенные циклы (внутреннее соединение)
   |--Поиск по индексу(ОБЪЕКТ:emp_test,
   | ИСКАТЬ: (дата_рождения, дочерний_идентификатор)
   | >= ('1971-01-01', 27)
   | И (дата_рождения, дочерний_идентификатор)
   | <= ('1971-01-10', 27),
   | ГДЕ: subsidiary_id = 27
   | ЗАКАЗАН ВПЕРЕД)
   |--RID Lookup(OBJECT:employees,
                   ПОИСК:Bmk1000=Bmk1000
                 LOOKUP ORDERED FORWARD)

SQL Server 2012 показывает предикаты поиска (=предикаты доступа) с использованием синтаксиса значения строки.

Предикатная информация для INDEX RANGE SCAN дает важный намек. Он идентифицирует условия предложения where либо как доступа , либо как фильтра предикатов. Вот как база данных сообщает нам, как она использует каждое условие.

`Примечание`

План выполнения был упрощен для ясности. В приложении поясняются детали раздела «Информация о предикатах» в плане выполнения Oracle.

В качестве предикатов доступа перечислены только условия в столбце DATE_OF_BIRTH ; они ограничивают диапазон сканируемых индексов. 9Таким образом, 0047 DATE_OF_BIRTH является первым столбцом в индексе EMP_TEST . Столбец SUBSIDIARY_ID используется только как фильтр.

Важно

Предикаты доступа являются условиями запуска и остановки поиска по индексу. Они определяют диапазон сканируемых индексов.

Предикаты индексного фильтра применяются только при обходе конечного узла. Они не сужают диапазон сканируемых индексов.

В приложении объясняется, как распознавать предикаты доступа в MySQL, SQL Server и PostgreSQL.

База данных может использовать все условия в качестве предикатов доступа, если мы перевернем определение индекса:

DB2

 --------------------------------------- -----------------------------
ID | Операция | Ряды | Расходы
 1 | ВОЗВРАТ | | 13
 2 | ПОЛУЧИТЬ СОТРУДНИКОВ | 3 из 3 (100,00%) | 13
 3 | IXSCAN EMP_TEST2 | 3 из 10000 (0,03%) | 6
Информация о предикате
 3 - НАЧАТЬ (Q1.SUBSIDIARY_ID = ?)
     НАЧАЛО (TO_DATE(?, 'ГГГГ-ММ-ДД') <= Q1.DATE_OF_BIRTH)
      СТОП (Q1.SUBSIDIARY_ID = ?)
      СТОП (Q1.DATE_OF_BIRTH <= TO_DATE(?, 'ГГГГ-ММ-ДД'))

Oracle

 ---------------------------------------------------------- --------------------
| Идентификатор | Операция | Имя | Ряды | Стоимость |
-------------------------------------------------- -------------
| 0 | ВЫБЕРИТЕ ЗАЯВЛЕНИЕ | | 1 | 3 |
|* 1 | ФИЛЬТР | | | |
| 2 | ДОСТУП К ТАБЛИЦАМ ПО ИНДЕКСУ ROWID| СОТРУДНИКИ | 1 | 3 |
|* 3 | ИНДЕКС ДИАПАЗОН СКАН | EMP_TEST2 | 1 | 2 |
-------------------------------------------------- -------------
Информация о предикате (определяется идентификатором операции):
-------------------------------------------------- -
1 – фильтровать(:END_DT >= :START_DT)
  3 - доступ (ДОПОЛНИТЕЛЬНЫЙ_ID = :SUBS_ID
       И ДАТА_РОЖДЕНИЯ >= :START_DT
       И ДАТА_РОЖДЕНИЯ <= :END_T)

PostgreSQL

 ПЛАН ЗАПРОСА
-------------------------------------------------- ------------------
Сканирование индекса с использованием emp_test для сотрудников
   (стоимость=0,01. .8,29 строк=1 ширина=17)
   Условие индекса: (subsidiary_id = 27::numeric)
           И (date_of_birth >= to_date('1971-01-01', 'ГГГГ-ММ-ДД'))
           И (дата_рождения <= to_date('1971-01-10', 'ГГГГ-ММ-ДД'))

База данных PostgreSQL не указывает доступ к индексу и предикаты фильтрации в плане выполнения. Однако в разделе Index Cond столбцы перечислены в порядке определения индекса. В этом случае мы сначала видим предикат SUBSIDIARY_ID , а затем два DATE_OF_BIRTH . Поскольку после условия диапазона DATE_OF_BIRTH фильтрация столбца отсутствует, мы знаем, что все предикаты могут использоваться в качестве предикатов доступа. См. « Различение предикатов доступа и фильтров » для получения более подробной информации.

SQL Server

 | -- Вложенные циклы (внутреннее соединение)
   |--Поиск по индексу(ОБЪЕКТ:emp_test,
   | ИСКАТЬ: дочерняя_id = 27
   | И date_of_birth >= '1971-01-01'
   | И date_of_birth <= '1971-01-10'
   | ЗАКАЗАН ВПЕРЕД)
   |--Поиск RID(ОБЪЕКТ:сотрудники),
                   ПОИСК:Bmk1000=Bmk1000
                 ПРОСМОТР ВПЕРЕД)

Наконец, есть оператор между .