Диапазон на основе набора SQL

Как я могу сделать так, чтобы SQL повторял некоторые операции на основе множеств произвольное количество раз без циклов? Как сделать так, чтобы SQL выполнял операции с диапазоном чисел? Я в основном ищу способ сделать основанный на множестве цикл for.

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

Например, если бы у меня была эта таблица, я мог бы сделать выбор, чтобы найти сумму чисел 100-200 следующим образом:

select sum(n) from numbers where n between 100 and 200

Есть идеи? Я вроде ищу что-то, что работает для T-SQL, но любая платформа будет в порядке.

[Edit] У меня есть собственное решение для этого с использованием SQL CLR, которое прекрасно работает для MS SQL 2005 или 2008. См. Ниже.

12.09.2008 07:09:22
У вас есть много интересных ответов здесь, но я все еще застрял на том же вопросе ... почему вы хотите это сделать? Я предполагаю, что есть ошибка в ваших целях или дизайне системы, если вам нужна такая функциональность. (Не хочу быть резким, на самом деле мне интересно увидеть пример использования sql, где это действительно необходимо).
Hogan 30.11.2009 21:39:16
6 ОТВЕТОВ
РЕШЕНИЕ

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

К сожалению, большие имена в базах данных не имеют встроенных запрашиваемых псевдотаблиц диапазона номеров. Или, в более общем смысле, простые функции генерации данных на чистом SQL. Лично я считаю, что это огромный провал, потому что, если бы они это сделали, можно было бы переместить большую часть кода, который в настоящее время заблокирован в процедурных сценариях (T-SQL, PL / SQL и т. Д.), В чистый SQL, который имеет ряд преимуществ для производительности и сложности кода.

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

Oracle и T-SQL поддерживают предложение WITH, которое можно использовать для этого. Они работают немного по-разному в разных СУБД, и MS называет их «общими табличными выражениями», но они очень похожи по форме. Используя их с рекурсией, вы можете довольно легко сгенерировать последовательность чисел или текстовых значений. Вот как это может выглядеть ...

В Oracle SQL:

WITH
  digits AS  -- Limit recursion by just using it for digits.
    (SELECT
      LEVEL - 1 AS num
    FROM
      DUAL
    WHERE
      LEVEL < 10
    CONNECT BY
      num = (PRIOR num) + 1),
  numrange AS
    (SELECT
      ones.num
        + (tens.num * 10)
        + (hundreds.num * 100)
        AS num
    FROM
      digits ones
      CROSS JOIN
        digits tens
      CROSS JOIN
        digits hundreds
    WHERE
      hundreds.num in (1, 2)) -- Use the WHERE clause to restrict each digit as needed.
SELECT
  -- Some columns and operations
FROM
  numrange
  -- Join to other data if needed

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

Я сам не использовал выражения общих таблиц SQL Server, но, поскольку они допускают самостоятельные ссылки, рекурсия НАМНОГО проще, чем в Oracle. Сравнима ли производительность и каковы пределы вложенности, я не знаю.

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

ОБНОВЛЕНИЕ: Я просто хочу добавить, что, как только вы начнете работать с данными таким образом, это откроет вам новый взгляд на SQL. Это не просто язык сценариев. Это довольно надежный управляемый данными декларативный язык . Иногда это неудобно для использования, потому что в течение многих лет он испытывал недостаток усовершенствований, помогающих уменьшить избыточность, необходимую для сложных операций. Но, тем не менее, это очень мощный и довольно интуитивно понятный способ работы с наборами данных как с целью, так и с драйвером ваших алгоритмов.

5
12.09.2008 15:36:36
Я согласен, что это огромный провал. Я обнаружил, что нуждаюсь в подобных вещах много раз и прибегаю к петлям.
Hafthor 22.09.2008 17:11:23

При использовании SQL Server 2000 или более поздней версии вы можете использовать тип данных таблицы, чтобы избежать создания обычной или временной таблицы. Затем используйте обычные табличные операции над ним.

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

Я нашел хорошее обсуждение здесь: временные таблицы против типа данных таблицы

0
12.09.2008 08:36:01

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

Я считаю, что правильный ответ на ваш вопрос в основном "вы не можете". (Сожалею.)

1
12.09.2008 14:56:22
Я понимаю, почему это модем вниз. Это правильно, но не особенно полезно. (Если только вы, как и я, не думаете, что безотказная работа - хорошая идея здесь.)
Anders Eurenius 19.09.2008 18:33:00

Я создал табличную функцию SQL CLR, которая отлично подходит для этой цели.

SELECT n FROM dbo.Range(1, 11, 2) -- returns odd integers 1 to 11
SELECT n FROM dbo.RangeF(3.1, 3.5, 0.1) -- returns 3.1, 3.2, 3.3 and 3.4, but not 3.5 because of float inprecision. !fault(this)

Вот код:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

[assembly: CLSCompliant(true)]
namespace Range {
    public static partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true, FillRowMethodName = "FillRow", TableDefinition = "n bigint")]
        public static IEnumerable Range(SqlInt64 start, SqlInt64 end, SqlInt64 incr) {
            return new Ranger(start.Value, end.Value, incr.Value);
        }

        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true, FillRowMethodName = "FillRowF", TableDefinition = "n float")]
        public static IEnumerable RangeF(SqlDouble start, SqlDouble end, SqlDouble incr) {
            return new RangerF(start.Value, end.Value, incr.Value);
        }

        public static void FillRow(object row, out SqlInt64 n) {
            n =  new SqlInt64((long)row);
        }

        public static void FillRowF(object row, out SqlDouble n) {
            n = new SqlDouble((double)row);
        }
    }

    internal class Ranger : IEnumerable {
        Int64 _start, _end, _incr;

        public Ranger(Int64 start, Int64 end, Int64 incr) {
            _start = start; _end = end; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            return new RangerEnum(_start, _end, _incr);
        }
    }

    internal class RangerF : IEnumerable {
        double _start, _end, _incr;

        public RangerF(double start, double end, double incr) {
            _start = start; _end = end; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            return new RangerFEnum(_start, _end, _incr);
        }
    }

    internal class RangerEnum : IEnumerator {
        Int64 _cur, _start, _end, _incr;
        bool hasFetched = false;

        public RangerEnum(Int64 start, Int64 end, Int64 incr) {
            _start = _cur = start; _end = end; _incr = incr;
            if ((_start < _end ^ _incr > 0) || _incr == 0)
                throw new ArgumentException("Will never reach end!");
        }

        public long Current {
            get { hasFetched = true; return _cur; }
        }

        object IEnumerator.Current {
            get { hasFetched = true; return _cur; }
        }

        public bool MoveNext() {
            if (hasFetched) _cur += _incr;
            return (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = false;
        }
    }

    internal class RangerFEnum : IEnumerator {
        double _cur, _start, _end, _incr;
        bool hasFetched = false;

        public RangerFEnum(double start, double end, double incr) {
            _start = _cur = start; _end = end; _incr = incr;
            if ((_start < _end ^ _incr > 0) || _incr == 0)
                throw new ArgumentException("Will never reach end!");
        }

        public double Current {
            get { hasFetched = true; return _cur; }
        }

        object IEnumerator.Current {
            get { hasFetched = true; return _cur; }
        }

        public bool MoveNext() {
            if (hasFetched) _cur += _incr;
            return (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = false;
        }
    }
}

и я развернул его так:

create assembly Range from 'Range.dll' with permission_set=safe -- mod path to point to actual dll location on disk.
go
create function dbo.Range(@start bigint, @end bigint, @incr bigint)
  returns table(n bigint)
  as external name [Range].[Range.UserDefinedFunctions].[Range]
go
create function dbo.RangeF(@start float, @end float, @incr float)
  returns table(n float)
  as external name [Range].[Range.UserDefinedFunctions].[RangeF]
go
3
30.09.2008 16:23:46
Круто ... одна вещь, которую я бы сделал по-другому, в вашей логике, чтобы проверить, собираемся ли мы когда-нибудь завершить, вы должны действительно сделать это: if (Math.Sign (_end - _start)! = Math.Sign (_incr)) бросить новое ArgumentException ("" Никогда не достигнет конца! ");
IDisposable 15.08.2009 06:50:58
Кроме того, вы могли бы, вероятно, использовать универсальный для всех базовых числовых типов (байт, int и т. Д.)
IDisposable 15.08.2009 06:51:50
в C # 3,5 это однострочник:for (var i = start.Value; i < end.Value; i += incr.Value) yield return i;
jeroenh 26.02.2011 13:00:59

Вот хак, который вы никогда не должны использовать:

select sum(numberGenerator.rank)
from
(
select
    rank =  ( select count(*)  
              from reallyLargeTable t1 
              where t1.uniqueValue > t2.uniqueValue ), 
    t2.uniqueValue id1, 
    t2.uniqueValue id2
from reallyLargeTable t2 
) numberGenerator
where rank between 1 and 10

Вы можете упростить это, используя функции Rank () или Row_Number в SQL 2005

0
30.09.2008 17:00:20

Вы можете использовать общее табличное выражение, чтобы сделать это в SQL2005 +.

WITH CTE AS
(
    SELECT 100 AS n
    UNION ALL
    SELECT n + 1 AS n FROM CTE WHERE n + 1 <= 200
)
SELECT n FROM CTE
1
26.09.2011 13:45:24
Хороший ответ! К сожалению, он не работает для любого числа больше 100 или любого указанного вами максимального уровня рекурсии CTE («Оператор завершен. Максимальная рекурсия 100 была исчерпана до завершения оператора»).
Wiebe Tijsma 26.09.2011 15:18:02
Спасибо! Вы можете увеличить уровень рекурсии до 32 767 для каждого запроса, используя подсказку запроса MAXRECURSION ( msdn.microsoft.com/en-us/library/ms181714.aspx ). Тем не менее, это не совсем общее решение для любого диапазона.
Mike Powell 26.09.2011 19:57:09