Comments 15
средняя по средней- какая прелесть))
Особенно приятно удивление бизнеса из серии: "а шо это?" когда он (бизнес) начинает менять очередность измерений в заголовках строк сводной и видит изменение этой "средней".
У них большое совещание было, когда я это сделал. Не знаю, может специально решили мне накинуть задачку, думали не справлюсь. А когда я сделал как они хотели, то уже им пришлось думать, а не мне им объяснять.:)
SELECT * INTO Test FROM (Values (1,'2017-01-01'),(2,'2017-01-02'),(3,'2017-01-03'),(3,'2017-01-04'),(3,'2017-01-05'),(4,'2017-01-06'),(5,'2017-01-07'),(6,'2017-01-05')) AS t(Volume,Date)
После работы преобразования получаю:
data VT_BEG VT_END
3 2017-01-03 2017-01-05
Можете посмотреть execution plan на своем сервере. Итоговый запрос больше для примера, что идея, которую я описал работает. Это ответ на вопрос от AlexeyTopunov, случайно не в ту ветку попал.
Слишком сложный запрос, все пишется в пару строк и без CTE:
SELECT volume, MIN(date) AS VT_BEG, MAX(date) AS VT_END
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY volume ORDER BY date) as grp FROM #test) t
GROUP BY volume,grp ORDER BY VT_BEGСоздаю таблицу запросом:
SELECT * INTO Test FROM (VALUES (1,'2017-01-01'),(2,'2017-01-02'),(3,'2017-01-03'),(3,'2017-01-04'),(3,'2017-01-05'),(4,'2017-01-06'),(1,'2017-01-10'),(1,'2017-01-11'),(1,'2017-01-15')) AS t(Volume, DATE)
Получается табличка:
1 2017-01-01
2 2017-01-02
3 2017-01-03
3 2017-01-04
3 2017-01-05
4 2017-01-06
1 2017-01-10
1 2017-01-11
1 2017-01-15
Делаю Ваш запрос:
SELECT volume, MIN(date) AS VT_BEG, MAX(date) AS VT_END
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY volume ORDER BY date) as grp FROM #test) t
GROUP BY volume,grp ORDER BY VT_BEGПолучаю для Volume = 1 два периода:
1 2017-01-01 2017-01-01
1 2017-01-10 2017-01-15
Хотя периода было 3:
1 2017-01-01 2017-01-01
1 2017-01-10 2017-01-11
1 2017-01-15 2017-01-15
Я не против конструктивных предложений, но, пожалуйста, будьте аккуратнее и не спешите.
SELECT volume, convert(datetime,date) as date INTO #Test FROM (VALUES (1,'2017-01-01'),(2,'2017-01-02'),(3,'2017-01-03'),(3,'2017-01-04'),(3,'2017-01-05'),(4,'2017-01-06'),(1,'2017-01-10'),(1,'2017-01-11'),(1,'2017-01-15')) AS t(Volume, DATE)
;with cte as (
select volume, date,
isnull(LEAD(date) over (ORDER BY date), date+10) as nd,
isnull(LEAD(volume) over (ORDER BY date), volume+1) as nv,
isnull(LAG(date) over (order by date),date-10) as pd,
isnull(LAG(volume) over (ORDER BY date), volume-1) as pv
from #test
)
select s.volume, s.date as vt_beg, e.date as vt_end
from (select row_number() over (order by date) as rn, volume, date from cte where date>pd+1 or volume <> pv) s
join (select row_number() over (order by date) as rn, date from cte where date<nd-1 or volume <> nv) e on s.rn = e.rnВот так подойдет? :)… Кстати если у Вас есть большой объем реальных данных интересно какой запрос и во сколько раз работает быстрей потому что, на маленьком объеме поведение практически идентичное, а план сервер категорически отказывается.
Ну как так????
SELECT volume, convert(datetime,date) as date INTO Test FROM (VALUES (1,'2017-01-01'),(2,'2017-01-02'),(3,'2017-01-03'),(3,'2017-01-04'),(3,'2017-01-05'),(4,'2017-01-06'),(1,'2017-01-10'),(1,'2017-01-11'),(1,'2017-01-15'), (null,'2017-01-05'), (5,'2017-01-05')) AS t(Volume, DATE)
Тогда такой набор не будет в Вашем случае работать. Как бы может не надо такое сюда писать? Я ведь помочь хочу людям, а не чужие запросы проверять.
Так это найти все начала периодов, сравнив данные с предыдущим, и все концы периодов сделав то же самое с последующими потом объединить попарно начала и концы, не выделяя отдельно однодневные и прочие периоды. Насчет NULL значений в поле Value вы меняете постановку на ходу, понимаю что ваш заказчик всегда делает так же, но может в рамках статьи этого делать не нужно? Если вы думаете что вы сделали великое открытие вы ошибаетесь и в данном случае скорее вопрос реализации и ваша реализация просто уродлива. Я пытался написать нормальный запрос с нормальной идеей, но если автору самому не интересна тема обсуждения, то я темболее не вижу в нем смысла. Автор любит свой велосипед и агрессивен ко всем кто отказывается его "полюбить" :).
Причем тут постановка? Мой запрос ничего не теряет и работает тем не менее с таким набором. Если кто-то будет пользоваться этим запросом, то получив Null у него все как работало так и будет работать. И там не только NULL, как насчет (5,'2017-01-05')? Я не ставил постановку, я просто привел пример. Запрос должен работать с любыми наборами, как минимум не терять данные и я постарался сделать так чтобы он работал.
Я пытался сделать запрос как можно более понятным и выделить в нем ключевую идею. Глубоким тюнингом я не занимался.
Дисциплина, Точность, Внимание к деталям