Развернуть диапазон целых чисел для присоединения к SQL. Диапазон sql
sql - Количество диапазонов SQL Server
Я часто использую функцию "Таблица-значение" для создания динамических диапазонов даты/времени. Тактильная/календарная таблица тоже поможет. Однако функция предлагает немного большую гибкость. Параметры - это Диапазон дат, DatePart и Increment.
Я должен также отметить, что ваш конкретный вопрос, я использую Format(). В этой функции есть несколько отличных функций, но она НЕ знает о ее производительности.
Пример:
Declare @YourTable table (Datum datetime,SomeID int) Insert Into @YourTable values ('2017-01-01 07:44:57.840',1), ('2017-01-02 07:45:10.093',2), ('2017-01-02 07:45:21.557',3), ('2017-01-03 09:07:21.253',2), ('2017-01-05 09:07:42.680',1), ('2017-01-06 09:07:49.007',5) Declare @DispFormat varchar(10)='yyyy-MM-dd' --<< Standard Format() options Declare @DatePart varchar(10)='DD' --<< YY,MM,DD,HH,MI,SS Declare @Increment int = 1 --<< Set Increment Declare @DateR1 datetime Declare @DateR2 datetime Select @DateR1=min(Datum),@DateR2=max(Datum) from @YourTable --<< Can be set manually if desired Select A.TimeInterval ,Count = count(Distinct SomeID) From (Select *,TimeInterval = format(RetVal,@DispFormat) From [dbo].[udf-Range-Date](@DateR1,@DateR2,@DatePart,@Increment)) A Left Join @YourTable B on format(A.RetVal,@DispFormat) = format(B.Datum,@DispFormat) Group By A.TimeIntervalUDF, если интересно
CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int) Returns Table Return ( with cte0(M) As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End), cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)), cte2(N) As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ), cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 ) Select RetSeq = N+1 ,RetVal = D From cte3,cte0 Where D<=@R2 ) /* Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS Syntax: Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) */qaru.site
sql - Развернуть диапазон целых чисел для присоединения к SQL
Как все сказали, это больно делать в SQL Server. Если вы должны, я думаю, что это правильный подход.
Сначала определите свои правила для разбора строки, затем разложите процесс на четко определенные и понятные проблемы. Основываясь на вашем примере, я думаю, что это процесс:
- Отдельные значения, разделенные запятыми, в строке в строки
- Если данные не содержат, они заканчиваются (это отдельное значение)
- Если в есть содержится тире, проанализируйте левую и правую стороны черточки
- Учитывая, что левая и правая стороны (диапазон) определяют все значения между ними в строки
Я бы создал временную таблицу для заполнения результатов синтаксического анализа, в которые нужны два столбца: SourceRowID INT, ContainedValue INT
а другой - для промежуточной обработки: SourceRowID INT, ContainedValues VARCHAR
Разделите значения, разделенные запятыми, на свои собственные строки, используя CTE, как этот Шаг 1 теперь является четко определенной и понятной проблемой для решения:
Включение строки, разделенной запятой, в отдельные строки
Итак, ваш результат от источника '1-2,5' будет: '1-2' '5'
Оттуда SELECT из этой таблицы обработки, в которой поле не содержит, содержит тире. Шаг 2 теперь является четко определенной и понятной проблемой для решения. Это автономные номера и могут идти прямо в таблицу результатов temp. В таблице результатов также должна быть ссылка идентификатора на исходную строку.
Далее следует проанализировать значения слева и справа от тире, используя CHARINDEX, чтобы найти его, затем соответствующие функции LEFT и RIGHT будут выполняться по мере необходимости. Это даст вам начальное и конечное значение.
Вот важный вопрос для решения этого шага 3 - это теперь четко определенная и понятная проблема для решения:
Подстрока T-SQL - разделение имени и фамилии
Теперь вы разделили начальные и конечные значения. Используйте другую функцию, которая может взорвать этот диапазон. Шаг 4 - это четкая и понятная проблема для решения:
SQL: создать последовательный список чисел из разных точек запуска
ВЫБРАТЬ все N между @min и @max
Каков наилучший способ создания и заполнения таблицы чисел?
а также вставить его в таблицу temp.
Теперь то, что вам нужно, - это временная таблица с каждым значением в разнесенном диапазоне.
Просто JOIN, что в другой таблице значений теперь, а затем в исходную таблицу по ссылке ID и вы там.
qaru.site
sql - Как группировать записи в соответствии с диапазоном в SQL
Несмотря на то, что некоторые из нижеперечисленных требований выходят за рамки основного стандарта SQL, они являются полными стандартными функциями SQL (например, конструкторы строк - это полная функция SQL-92, CTE - полная функция SQL-99 и т.д.), и это действительно будет найденных в некоторых продуктах, таких как SQL Server и Oracle:
WITH MyTable (my_Number, my_Time, Price) AS ( SELECT my_Number, CAST(my_Time AS DATE), CAST(Price AS DECIMAL(5, 2)) FROM ( VALUES (100, '2010-08-10', 10), (100, '2010-08-12', 15), (160, '2010-08-10', 10), (200, '2010-08-10', 12), (210, '2010-08-11', 13), (300, '2010-08-11', 14), (350, '2010-08-10', 16) ) AS MyTable (my_Number, my_Time, Price) ), Ranges (NumberRange, range_start, range_end) AS ( SELECT NumberRange, range_start, range_end FROM ( VALUES (1, 100, 200), (2, 200, 300), (3, 300, 400), (4, 0, 400) ) AS Ranges (NumberRange, range_start, range_end) ), RangesMaxTimes (NumberRange, range_start, range_end, max_time) AS ( SELECT R1.NumberRange, R1.range_start, R1.range_end, MAX(M1.my_Time) AS max_time FROM MyTable AS M1 INNER JOIN Ranges AS R1 ON R1.range_start <= M1.my_Number AND M1.my_Number < R1.range_end GROUP BY R1.NumberRange, R1.range_start, R1.range_end ) SELECT R1.NumberRange, M1.my_Time, M1.Price FROM MyTable AS M1 INNER JOIN RangesMaxTimes AS R1 ON R1.range_start <= M1.my_Number AND M1.my_Number < R1.range_end AND M1.my_Time = R1.max_time;sql - Диапазон перекрытия SQL
Я предполагаю, что Дата начала и Конечная дата отформатированы как Дата или Дата.
Я не уверен, что вы пытаетесь определить, попали ли записи даты начала в течение последних 3 месяцев или записи даты окончания.
Также вам потребуется ограничение для определения суммы (распределения%). Какие уникальные записи вы хотите суммировать. Поскольку вы хотите суммировать сумму%, а затем выбираете только те, которые больше 100, вам нужно выяснить, какие из них нужно вытащить, а затем суммировать.
Вы захотите выполнить это с помощью инструкции "С" или создания вспомогательных "таблиц" (запросов), которые вытаскивают необходимую информацию поэтапно. Я приведу заявления подзапроса.
Написан для Sql-Server 2008 R2
Сначала выберите данные за последние 3 месяца
Select ResourceAssignmentID, ResourceID, [Assigned To], [Allocation %] From ResourceAssignment Where [Start Date] >= dateadd(Month,-3,Now)Затем, используя эти данные, вы хотите суммировать%. (Я предполагаю, что на основе комбинации двух внешних ключей)
Select Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %]) as Sumofallocation From (Select ResourceAssignmentID, ResourceID, [Assigned To], [Allocation %] From ResourceAssignment Where [Start Date] >= dateadd(Month,-3,Now)) Query1 Group By Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %])Вы можете добавить предложение Where перед группой, чтобы отфильтровать для вашего требования> 100, но я бы просто добавил еще один оператор Select, чтобы собрать все это вместе
Select * From (Select Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %]) as Sumofallocation From (Select ResourceAssignmentID, ResourceID, [Assigned To], [Allocation %] From ResourceAssignment Where [Start Date] >= dateadd(Month,-3,Now)) Query1 Group By Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %]) ) Query2 Where Query2.Sumofallocation > 100Во всяком случае, я надеюсь, что это поможет. SQL может быть не идеальным, но он должен указывать на вас в рабочем состоянии.
qaru.site
sql - Получение дат между диапазоном дат
Чуть более сложным, но, возможно, более гибким, будет использование таблицы, содержащей последовательный набор чисел. Это позволяет использовать более одного диапазона дат с разными интервалами.
/* holds a sequential set of number ie 0 to max */ /* where max is the total number of rows expected */ declare @Numbers table ( Number int ) declare @max int declare @cnt int set @cnt = 0 /* this value could be limited if you knew the total rows expected */ set @max = 999 /* we are building the NUMBERS table on the fly */ /* but this could be a proper table in the database */ /* created at the point of first deployment */ while (@cnt <= @max) begin insert into @Numbers select @cnt set @cnt = @cnt + 1 end /* EXAMPLE of creating dates with different intervals */ declare @DateRanges table ( StartDateTime datetime, EndDateTime datetime, Interval int ) /* example set of date ranges */ insert into @DateRanges select '01 Jan 2009', '10 Jan 2009', 1 /* 1 day interval */ union select '01 Feb 2009', '10 Feb 2009', 2 /* 2 day interval */ /* heres the important bit generate the dates */ select StartDateTime from ( select d.StartDateTime as RangeStart, d.EndDateTime as RangeEnd, dateadd(DAY, d.Interval * n.Number, d.StartDateTime) as StartDateTime from @DateRanges d, @Numbers n ) as dates where StartDateTime between RangeStart and RangeEnd order by StartDateTimeqaru.site
SQL (типы данных): таблица
В современном мире насчитывается большое количество средств и технологий, предназначенных для хранения информации. Одним из наиболее распространенных способов являются базы данных, для работы с которыми используются различные системы управления. Данный способ хранения предполагает, что все данные четко структурированы и занесены в специальные таблицы. Они, в свою очередь, состоят из столбцов-атрибутов определенного типа данных.
Тип данных – что это?
Сегодня существует несколько определений, объясняющих понятие термина «тип данных». Однако каждое из них имеет один общий смысл. Поэтому тип данных можно условно обозначить как группу данных, характеризуемую ее значениями (символьными, числовыми и т.д.), а также операциями, применяемыми к рассматриваемым значениям.
Сфера применения типов данных многогранна. Они используются не только для хранения информации, но также и в программировании для решения различных задач. При проектировании программ широко распространена практика разработки и использования собственных типов данных с определенным набором операций. Однако в основе пользовательских всегда лежат базовые типы данных. SQL-стандарт также основывается на использовании наиболее распространенных базовых типов, но с рядом определенных дополнений.
Классификация типов данных
Группировка данных по их типу возникла давно и была вызвана необходимостью структурирования информации для более удобной обработки. В настоящее время основу существующих типов данных образуют два: символьный и числовой.
На их базе была разработана современная классификация, включающая в себя указатели, логический, целочисленный, числовой с плавающей точкой и строковый типы данных. SQL-классификация полностью охватывает все вышеперечисленное. Однако для некоторых современных СУБД существуют дополнительные надстройки. К ним относятся Oracle и MySQL.
Базовые типы данных
Использующиеся при создании атрибутов таблиц, отвечающих стандартам языка SQL, типы данных подразделяются на 4 класса:
- строковые значения;
- дроби;
- целочиселенные значения;
- значения даты и времени.
Строковый тип данных
Первая группа значений позволяет хранить любые данные, представленные в виде символов.
Это могут быть специальные символы, цифры и буквы, которые в своей совокупности будут обрабатываться как строки в любом запросе SQL. Типы данных, таблица с перечислением которых представлена ниже, образуют первую группу.
CHAR (size) | Используется для хранения строк. Параметр в скобках позволяет фиксировать длину хранимой строки. Максимальный размер в байтах, который можно задать для строки, – 255. |
VARCHAR (size) | Аналогично предыдущему типу позволяет хранить строки длиной до 255 символов. Однако отличие от CHAR в том, что для хранения значения данного типа выделяется требуемое количество памяти. То есть для строки, состоящей из 5-ти символов, потребуется 6 байт памяти. В первом же случае память для значения будет выделена согласно указанному параметру. |
TINY ТЕХТ | Используется для хранения строковых данных размером до 255 символов. |
ТЕХТ | Используется для хранения текстовой информации, размер которой не превышает 65 535 букв. |
BLOB | Рассматриваемый тип данных аналогичен типу ТЕХТ и позволяет сохранять в базе текстовую информацию, объем которой может достигать 65 535 знаков. Но на практике используется для хранения звуковых данных, рисунков, электронной документации и пр. |
MEDIUM ТЕХТ | Был разработан на базе типа ТЕХТ, но позволяет хранить больше данных за счет увеличенного размера до 16 777 215 букв или символов. |
MEDIUM BLOB | Используется для сохранения в базе электронных документов, размер которых не превышает отметку в 16 777 215 знаков. |
LONG ТЕХТ | Функционально аналогичен предыдущим типам, но с увеличенным объемом памяти до 4 гигабайт. |
LONG BLOB | Позволяет помещать в базу данные больших объемов (4 294 967 295 символа). |
ENUM (a, b, c, etc.) | Специальный тип данных, использующийся для задания списка возможных значений. Позволяет указать 65535 значений. Строки рассматриваемого типа могут принимать единственное значение из указанных в множестве. В случае, когда будет происходить добавление значений, которые не присутствуют в заданном списке, в таблицу будут записаны пустые значения. |
SET | Задает множество допустимых значений. В отличие от предыдущего типа, используется для содержания 64 параметров, которые могут быть проинициализированы любым или несколькими элементами из заданных аргументов. |
Таблица дробных типов данных
Дробные SQL типы данных используются для хранения чисел с плавающей точкой. На практике, как правило, задают различные финансовые показатели. В зависимости от необходимой точности применяют один из трех представленных:
FLOAT (size, d) | Позволяет содержать дробные числа указываемой точности d. |
DOUBLE (size, d) | Используется для хранения дробных чисел с двоичной точностью. |
DECIMAL(size, d) | Хранение дробных значений в виде строк. |
Например, в банковских расчетах точность дробной части достигает значение в 8-мь или 10-ть знаков. Первые два типа не могут быть задействованы в данной области.
Хранение финансовых показателей в виде строк значительно облегчает решение многих задач. Однако при решении финансовых вопросов или проведении различных операций SQL преобразование типов данных имеет огромное значение. Разработчики должны обязательно учитывать тип хранения и способы обработки, чтобы данные всегда оставались неизменными.
Целочисленный тип данных
Целые числа – отдельная группа чисел, образующая один из основных классов. Целочисленные SQL типы данных основываются на использовании базового типа INTEGER с некоторым расширением его свойств.
INT (size) | Хранение целочисленных значений, образующих диапазон [-231; 231-1] |
TINYINT (size) | Служит для хранения чисел в диапазоне от -128 до 127 |
SMALLINT (size) | Характеризуется увеличенным диапазоном хранимых значений в размере от -32 768 до 32 767 |
MEDIUMINT (size) | Используется для хранения чисел размерностью от -223 до 223-1 |
BIGINT (size) | Охватывает диапазон целочисленных значений, начиная с -263 и заканчивая 263-1 |
Выбрав правильный тип данных, можно значительно сэкономить память и уменьшить затраты серверного времени, когда выполняются необходимые SQL-запросы. Типы данных, а точнее их диапазон, определяют количество требуемого места для хранения.
Поэтому разработчикам баз данных важно помнить, что использование больших диапазонов для атрибутов влечет увеличение затрат на память. Необходимо четко анализировать решаемые задачи и выявлять случаи, где известен примерный диапазон и определено условие использования чисел со знаком. Если диапазон используемых аргументов невелик, а все числа будут положительными, то будет корректнее использовать беззнаковый тип, образуемый атрибутом UNSIGNED.
Типы данных даты и времени
При изучении основ SQL типы данных даты и времени представляют особый интерес.
Использование нижеуказанных типов предоставляет дополнительные преимущества при разработке систем, работа которых зависит от временных показателей.
DATE | Главное предназначение - хранение даты в формате ГОД-МЕСЯЦ-ДЕНЬ (“ГГГГ-MM-ДД” или "уууу-mm-dd" ). Обычно значения разделены через «-», однако в качестве разделителя может быть задействован любой символ, кроме цифр. |
TIME | Позволяет заносить в ячейку таблицы временные значения. Все значения задаются форматом «hh:mm:ss» |
DATETIME | Объединяет функции предыдущих двух типов. Формат хранения представлен следующим образом: «уууу-mm-dd hh:mm:ss». |
TIMESTAMP | Сохраняет дату и время, исчисляемое количеством секунд, прошедших начиная с полуночи 1.01.1970 года и до заданного значения. |
YEAR (M) | Используется для хранения годовых значений в двух- или четырехзначном формате. |
Что ещё необходимо знать?
Все эти типы данных детально систематизированы компанией Microsoft. SQL типы данных ею разработаны более подробно.
Например, фирмой подробно расписан, какой объем памяти в байтах выделяется при использовании каждого типа. Изучив имеющуюся информацию, разработчикам проще проектировать структуру таблиц и всей базы исходя из аппаратных возможностей сервера.
Специальный указатель – NULL
Иногда при заполнении базы данных возникает ситуация, когда при добавлении записи в таблицу необходимость вносить информацию во все столбцы отсутствует. Для этого применяется специальный указатель пустого значения – NULL, который в качестве вспомогательного средства использует язык SQL. Типы данных столбцов, которые не обязательно должны быть заполнены, при создании таблиц указываются с оператором, разрешающим включение пустых значений. В другом случае оператор NULL с дополнительной приставкой NOT может быть задействован для указания обязательного заполнения всех значений.
Указатель NULL не имеет типа, а просто указывает на пустое значение в таблицах баз данных. Поэтому он может быть скомбинированным с любым из вышеперечисленных типов.
fb.ru
