Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Добавить в выборку отсутствующие строки.  [new]
ibbk
Member

Откуда:
Сообщений: 13
MS SQL 2005, Есть таблица вида (проектировалась не мной) [Date(int), Time(int), Value0()..ValueN()], каждый час в таблицу пишутся значения, но бывает, что и не пишутся, в итоге надо получить таблицу, где на определенную дату будет 24е строки, даже если на эту дату в БД нет не одной записи. Как можно реализовать подобную выборку?
27 июл 13, 10:16    [14626119]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
Glory
Member

Откуда:
Сообщений: 104751
Заведите 2 таблицы - с календарными датами и с часами/минутами
Можно и одну соответственно с датами/часами/минутами
27 июл 13, 10:38    [14626130]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
ibbk
Member

Откуда:
Сообщений: 13
Glory
Заведите 2 таблицы - с календарными датами и с часами/минутами
Можно и одну соответственно с датами/часами/минутами

Нет доступа на изменение СУБД только чтение.
По идеи задача сводится к созданию виртуальной таблице вида

DECLARE @Date int;
SET @Date = 12345;

declare @t table(A int, myDate int);
insert into @t values(1,@Date);
insert into @t values(2,@Date);
insert into @t values(3,@Date);
insert into @t values(4,@Date);
insert into @t values(5,@Date);
insert into @t values(6,@Date);
insert into @t values(7,@Date);
insert into @t values(8,@Date);
insert into @t values(9,@Date);
insert into @t values(10,@Date);
insert into @t values(11,@Date);
insert into @t values(12,@Date);
...
insert into @t values(24,@Date);

select * from @t AS t1 
RIGHT JOIN MyTable AS t2 ON t1.A = t2.A AND t1.myDate+1 = t2.myDate;


Тока получается куча избыточного кода и в случае формирования диапазона дат придется выносить создание виртуальной таблицы в код где в цикле запускать для каждой даты создание вирт таблицы.
27 июл 13, 11:09    [14626173]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
ibbk,

рекурсивное CTE, и ничего выносить не придётся
27 июл 13, 11:20    [14626178]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
qwerty112
Guest
;with cte as (
select 1 as A, @Date as myDate 
union all
select A+1, myDate from cte where A<24)
 
select * from cte AS t1 
RIGHT JOIN MyTable AS t2 ON t1.A = t2.A AND t1.myDate+1 = t2.myDate;
27 июл 13, 11:21    [14626179]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
qwerty112
Guest
qwerty112
;with cte as (
select 1 as A, @Date as myDate 
union all
select A+1, myDate from cte where A<24)
 
select * from cte AS t1 
left JOIN MyTable AS t2 ON t1.A = t2.A AND t1.myDate+1 = t2.myDate;
27 июл 13, 11:24    [14626182]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
qwerty112, не совсем так:
WITH MyTable AS(SELECT * FROM (VALUES 
	(21,10,100),(21,13,150),(21,14,150),(21,18,100),
   (22,9,200)
   ) AS v_MyTable (Date, Hour, Value)),
Numbers as(
   select Date, number 
   from master..spt_values
   cross apply (select distinct Date from MyTable) a
   where type='P' and number<25
   )
SELECT n.*, t.Value
FROM Numbers n
left join MyTable t on t.Date=n.Date and t.Hour=n.number
27 июл 13, 11:35    [14626194]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
qwerty112, Cygapb-007,

А если дырка > 24 часов?
27 июл 13, 12:52    [14626278]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
qwerty112
Guest
invm
qwerty112, Cygapb-007,

А если дырка > 24 часов?


ТС, в сабжевом посте говорит про выборку на "на определенную дату"
и тут так и делает - 14626173
(другой вопрос нахрена он вот это пишет : t1.myDate+1 = t2.myDate)
мой пример, делает тоже самое (на фиксированную дату), только без создания опорной таб. (вместо неё cte)

а при такой постановке (на фиксированную дату) - проблемы с "дыркой > 24 часов" никакой нет
27 июл 13, 13:00    [14626282]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
ibbk
Member

Откуда:
Сообщений: 13
qwerty112
invm
qwerty112, Cygapb-007,

А если дырка > 24 часов?


ТС, в сабжевом посте говорит про выборку на "на определенную дату"
и тут так и делает - 14626173
(другой вопрос нахрена он вот это пишет : t1.myDate+1 = t2.myDate)
мой пример, делает тоже самое (на фиксированную дату), только без создания опорной таб. (вместо неё cte)

а при такой постановке (на фиксированную дату) - проблемы с "дыркой > 24 часов" никакой нет


t1.myDate+1 = t2.myDate
Забыл убрать. Это так для тренировки делал соединял таблицу саму с собой. +1 чтоб вылетели пустые строки на след дату.

ibbk
Тока получается куча избыточного кода и в случае формирования диапазона дат придется выносить создание виртуальной таблицы в код где в цикле запускать для каждой даты создание вирт таблицы.


Отчет формируется посуточно но так же может потребоваться и за период выборка. Например с 12:00 05.05.2005 по 12:00 06.05.2005

iap
рекурсивное CTE, и ничего выносить не придётся

Ограничение 100. А если формировать на период равный недели? 24*7=168
27 июл 13, 13:59    [14626353]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
ibbk
Member

Откуда:
Сообщений: 13
qwerty112
а при такой постановке (на фиксированную дату) - проблемы с "дыркой > 24 часов" никакой нет

Как обычно по ТЗ отчет на конкретную дату но хотелось бы сделать универсально так как есть предчувствие что отчет придется формировать в различных разрезах и потребуется более одних суток в периоде!.
27 июл 13, 14:05    [14626363]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
qwerty112
Guest
ibbk
iap
рекурсивное CTE, и ничего выносить не придётся

Ограничение 100. А если формировать на период равный недели? 24*7=168

http://msdn.microsoft.com/ru-ru/library/ms181714(v=SQL.90).aspx

"крути" на MAXRECURSION
27 июл 13, 14:08    [14626368]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
qwerty112
Guest
ibbk
qwerty112
а при такой постановке (на фиксированную дату) - проблемы с "дыркой > 24 часов" никакой нет

Как обычно по ТЗ отчет на конкретную дату но хотелось бы сделать универсально так как есть предчувствие что отчет придется формировать в различных разрезах и потребуется более одних суток в периоде!.


так сделайте 2-а рекурсивных cte - одно дат нагенерирует, второе чисел - 1-:-24,
а потом cross join их в 3-ем, и уже результат 3-его используете как опорную таблицу

зы
с таб.-календарём, было бы по лучше
27 июл 13, 14:10    [14626372]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
ibbk
Member

Откуда:
Сообщений: 13
qwerty112
ibbk
пропущено...

Как обычно по ТЗ отчет на конкретную дату но хотелось бы сделать универсально так как есть предчувствие что отчет придется формировать в различных разрезах и потребуется более одних суток в периоде!.


так сделайте 2-а рекурсивных cte - одно дат нагенерирует, второе чисел - 1-:-24,
а потом cross join их в 3-ем, и уже результат 3-его используете как опорную таблицу

зы
с таб.-календарём, было бы по лучше


Хорошая идея тока что то не получается (не пинайте с рекурсивными запросами раньше не сталкивался да и вообше с MS SQL)
DECLARE @Date_S int,  @Date_E int;
SET @Date_S = 15852-10;
SET @Date_E = 15852;


with cte as (
select @Date_S as A
union all
select A+1 from cte where A<@Date_E)

SELECT * FROM cte;

with cte1 as (
select 2 as A
union all
select A+2 from cte1 where A<24)

SELECT * FROM cte1;

SELECT * FROM cte, cte1;
27 июл 13, 14:33    [14626392]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
qwerty112
Guest
DECLARE @Date_S int,  @Date_E int;
SET @Date_S = 15852-10;
SET @Date_E = 15852;


with 
cte as (
select @Date_S as A
union all
select A+1 from cte where A<@Date_E),

cte1 as (
select 2 as B
union all
select B+2 from cte1 where B<24),

cte2 as (
SELECT * FROM cte, cte1)

select * from cte2
27 июл 13, 14:39    [14626397]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
ibbk
Member

Откуда:
Сообщений: 13
qwerty112,

Спасибо огромное!.
27 июл 13, 14:44    [14626405]     Ответить | Цитировать Сообщить модератору
 Re: Добавить в выборку отсутствующие строки.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
ibbk
Тока получается куча избыточного кода и в случае формирования диапазона дат придется выносить создание виртуальной таблицы в код где в цикле запускать для каждой даты создание вирт таблицы.
CTE - это такое же цикл по записям и создание виртуальной таблицы, только более компактно синтаксически.

Лучше всё таки создать постоянную таблицу с датой-временем; если база только для чтения, то можно в другой базе.
27 июл 13, 15:08    [14626435]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить