(UTC)
  1С++ users forum › 1С++ и прочие расширения v7 › Прямые запросы к БД › затруднения в написании запроса с группировкой по периодам
(Группа Модераторов: 1c++ moderator)
‹ Предыдущая Тема | Следующая Тема ›
Переключение на Главную Страницу Страницы: 1 [2] 3  ОтправитьПечать
Очень популярная тема (более 25 ответов) затруднения в написании запроса с группировкой по периодам (число прочтений - 5721 )
pvase
God Member
*****
Отсутствует



Сообщений: 923
Местоположение: Киев
Зарегистрирован: 19. Мая 2006
Пол: Мужской
Re: затруднения в написании запроса с группировкой по периодам
Ответ #15 - 16. Ноября 2011 :: 13:38
Печать  
Но универсальный отчет, по любому периоду на SQL 2000 будет сложно сделать, лучше сразу определить до какого уровня в датах надо детализация по этим уровням делать промежуточную таблицу.

Т.е. поставить узкую задачу и ее сделать, а не делать универсальное решение.
  
Наверх
wwwSkype/VoIPICQ  
IP записан
 
JBoy
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 52
Зарегистрирован: 29. Декабря 2010
Re: затруднения в написании запроса с группировкой по периодам
Ответ #16 - 16. Ноября 2011 :: 14:11
Печать  
может быть совсем непонятно изложил суть вопроса. попробую снова и более детально - есть база на скл сервере, в ней данные по посещаемости магазинов(а в ней таблица с полями - склад, дата и количествопосещений), нужен отчет который бы выдавал эти данные в различных ракурсах (для аналитики)
JBoy писал(а) 16. Ноября 2011 :: 09:22:
на самом деле вариантов детализаций намного больше - весь период/месяц, весь период/неделя, весь период/день, весь период/час, месяц/день, неделя/день или день/час....

предполагается отчет выводить в таблицу где числитель вышеуказанных группировок - это строки, а знаменатель - столбцы.....к примеру группировка месяц/день имела бы вид:
                                     1   2   3   4   5 .......28 29 30 31   (числа месяца)
01.01.2011-31.01.2011     0   2   5   3   8        15 24 12 10
01.02.2011-28.02.2011     19 11 12 11 .......
.........
01.10.2011-31.10.2011                         .....    12 11 11 15 (данные из базы)
(группируемые периоды - месяцы)
в любом случае, даты с пустыми посещениями будут нужны - в этом как раз поможет тот запрос который получает таблицу дат....
хотелось бы в нее еще добавить время, точнее часы - это будет необходимо в группировках 4 и 7, в том порядке в котором я перечислил....надеюсь более понятно задачу изложил
  
Наверх
 
IP записан
 
val
Full Member
***
Отсутствует


Дорогу осиливает идущий

Сообщений: 137
Зарегистрирован: 07. Июля 2006
Re: затруднения в написании запроса с группировкой по периодам
Ответ #17 - 16. Ноября 2011 :: 14:13
Печать  
(13) (14)
     |create table #Периоды (Период datetime primary key)


     |DECLARE @D datetime;
     |DECLARE @D2 datetime;
     |SET @D=:НачДата
     |WHILE @D<=:КонДата
     |BEGIN
       |SET @D2=@D
     |SET @D=DATEADD(DAY,1,@D)
     |      WHILE @D2<@D
     |      BEGIN
       |      INSERT #Периоды(Период) VALUES(@D2)
     |      SET @D2=DATEADD(Hour,1,@D2)
     |      END
     |END
  
Наверх
 
IP записан
 
val
Full Member
***
Отсутствует


Дорогу осиливает идущий

Сообщений: 137
Зарегистрирован: 07. Июля 2006
Re: затруднения в написании запроса с группировкой по периодам
Ответ #18 - 16. Ноября 2011 :: 14:23
Печать  
(16)  Вы хотели по датам и часам - см. (17)
  
Наверх
 
IP записан
 
JBoy
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 52
Зарегистрирован: 29. Декабря 2010
Re: затруднения в написании запроса с группировкой по периодам
Ответ #19 - 17. Ноября 2011 :: 06:44
Печать  
val писал(а) 16. Ноября 2011 :: 14:23:
(16)  Вы хотели по датам и часам - см. (17)

а можно несколько комментариев как это использовать применительно к моей задаче
  
Наверх
 
IP записан
 
pvase
God Member
*****
Отсутствует



Сообщений: 923
Местоположение: Киев
Зарегистрирован: 19. Мая 2006
Пол: Мужской
Re: затруднения в написании запроса с группировкой по периодам
Ответ #20 - 17. Ноября 2011 :: 07:01
Печать  
Примерно так:
Код
Выбрать все
   |SELECT
		|	convert(char(255),shops.id) Айди,
		|	TABDAT.Dat ДатаСобытия,
		|	SUM(Выборка.RealIn) КолВходов
		|FROM
		|	server3.Cst.dbo.traffics AS Выборка
		|LEFT JOIN server3.Cst.dbo.sensors AS Датчики ON Выборка.sensorid=Датчики.id
		|LEFT JOIN server3.Cst.dbo.entries AS Датчики ON sensors.entryid=Входы.id
		|LEFT JOIN server3.Cst.dbo.shops AS Магазины ON Входы.shopid=Магазины.id
		|LEFT JOIN #Периоды as TABDAT on TABDAT.Период = Выборка.DateCreated
		|WHERE convert(char(255),shops.id)='9D9AB7E8-41F7-4AA5-8539-09307D1DAF07' or convert(char(255),shops.id)='F595578C-51FE-4A3B-B823-04BBBC3992DF'
		|GROUP BY
		|		convert(char(255),shops.id),
		|		TABDAT.Dat
		|";
  
Наверх
wwwSkype/VoIPICQ  
IP записан
 
val
Full Member
***
Отсутствует


Дорогу осиливает идущий

Сообщений: 137
Зарегистрирован: 07. Июля 2006
Re: затруднения в написании запроса с группировкой по периодам
Ответ #21 - 17. Ноября 2011 :: 08:27
Печать  
(19) Вариантов несколько. Например:
1. Выбираете из своей таблицы во временную (нпример #Врем) реквизиты склад, месяц и период, округленный до выбранной Вами детализации - такой же, как в таблице #Период. 
2. Делаете шаблон:
     |select Периоды.Период
     |      ,def.Склад,def.Месяц
     |      from #Периоды as Периоды
     |      cross join (select distinct Склад,Месяц from #Врем) as def
3. Соединяете с этим шаблоном #Врем по всем реквизитам - получаете количество.
  
Наверх
 
IP записан
 
JBoy
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 52
Зарегистрирован: 29. Декабря 2010
Re: затруднения в написании запроса с группировкой по периодам
Ответ #22 - 18. Ноября 2011 :: 08:01
Печать  
меня немного другая мысль посетила как все реализовать, попозже могу выложить что получилось, а пока есть вопрос - не могу сообразить как делать точные сравнения значения datetime и дaты 1С? можно ли как нибудь получать из значения datetime только дату, без времени....а также отдельно время?
  
Наверх
 
IP записан
 
JBoy
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 52
Зарегистрирован: 29. Декабря 2010
Re: затруднения в написании запроса с группировкой по периодам
Ответ #23 - 18. Ноября 2011 :: 08:10
Печать  
вот к примеру как я делаю разбивку по месяцам:
Код
Выбрать все
SELECT
		Т.Айди Магаз,
		Т.Группировка1 Период,
		SUM(Т.КолВходов) Посетители
FROM(
	SELECT
		Магазины.Name Айди,
		CASE
                WHEN Выборка.DateCreated BETWEEN '20110101' AND '20110201' THEN '01.01.2011 - 31.01.2011'
                WHEN Выборка.DateCreated BETWEEN '20110201' AND '20110301' THEN '01.02.2011 - 28.02.2011'
                WHEN Выборка.DateCreated BETWEEN '20110301' AND '20110401' THEN '01.03.2011 - 31.03.2011'
                WHEN Выборка.DateCreated BETWEEN '20110401' AND '20110501' THEN '01.04.2011 - 30.04.2011'
                WHEN Выборка.DateCreated BETWEEN '20110501' AND '20110601' THEN '01.05.2011 - 31.05.2011'
                WHEN Выборка.DateCreated BETWEEN '20110601' AND '20110701' THEN '01.06.2011 - 30.06.2011'
                WHEN Выборка.DateCreated BETWEEN '20110701' AND '20110801' THEN '01.07.2011 - 31.07.2011'
                WHEN Выборка.DateCreated BETWEEN '20110801' AND '20110901' THEN '01.08.2011 - 31.08.2011'
                WHEN Выборка.DateCreated BETWEEN '20110901' AND '20111001' THEN '01.09.2011 - 30.09.2011'
                WHEN Выборка.DateCreated BETWEEN '20111001' AND '20111101' THEN '01.10.2011 - 31.10.2011'
                WHEN Выборка.DateCreated BETWEEN '20111101' AND '20111119' THEN '01.11.2011 - 18.11.2011'
                ELSE 'фигня' END AS Группировка1,
				SUM(Выборка.RealIn) КолВходов
			FROM
				server3.Cst.dbo.traffic AS Выборка
			LEFT JOIN server3.Cst.dbo.sensors AS Датчики ON Выборка.sensorid=Датчики.id
			LEFT JOIN server3.Cst.dbo.entries AS Входы ON Датчики.entryid=Входы.id
			LEFT JOIN server3.Cst.dbo.shops AS Магазины ON Входы.shopid=Магазины.id
			WHERE Выборка.DateCreated BETWEEN '20110101' AND '20111118' AND
				(convert(char(255),Магазины.id)='9D9AB7E8-41F7-4AA5-8539-09307D1DAF07' or
                                convert(char(255),Магазины.id)='F595578C-51FE-4A3B-B823-04BBBC3992DF')
		GROUP BY Магазины.Name, Выборка.DateCreated
	) AS Т
GROUP BY Т.Айди,Т.Группировка1
ORDER BY Т.Айди,Т.Группировка1
  
Наверх
 
IP записан
 
val
Full Member
***
Отсутствует


Дорогу осиливает идущий

Сообщений: 137
Зарегистрирован: 07. Июля 2006
Re: затруднения в написании запроса с группировкой по периодам
Ответ #24 - 18. Ноября 2011 :: 08:28
Печать  
(22)
     |   Выборка.DateCreated ДатаИзм,
     |   CONVERT(CHAR(8), Выборка.DateCreated,108) Время,

(23) Почему не используете DatePart ?
  
Наверх
 
IP записан
 
JBoy
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 52
Зарегистрирован: 29. Декабря 2010
Re: затруднения в написании запроса с группировкой по периодам
Ответ #25 - 18. Ноября 2011 :: 08:39
Печать  
val писал(а) 18. Ноября 2011 :: 08:28:
(23) Почему не используете DatePart ?

а где именно его использовать? по месяцам пока вроде как нужды не было...
  
Наверх
 
IP записан
 
val
Full Member
***
Отсутствует


Дорогу осиливает идущий

Сообщений: 137
Зарегистрирован: 07. Июля 2006
Re: затруднения в написании запроса с группировкой по периодам
Ответ #26 - 18. Ноября 2011 :: 08:52
Печать  
Чтобы уйти от такого:
WHEN Выборка.DateCreated BETWEEN '20110101' AND '20110201' THEN '01.01.2011 - 31.01.2011'
  
Наверх
 
IP записан
 
JBoy
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 52
Зарегистрирован: 29. Декабря 2010
Re: затруднения в написании запроса с группировкой по периодам
Ответ #27 - 18. Ноября 2011 :: 09:09
Печать  
конструкцию CASE....WHEN я формирую в цикле вот так:
Код
Выбрать все
       	Группировка1 = "CASE";
		ТекДата = НачДата;
		Пока КонМесяца(ТекДата) < КонДата Цикл
			Группировка1 = Группировка1 + РазделительСтрок + "WHEN Выборка.DateCreated BETWEEN '" + Формат(ТекДата, "Д ГГГГММДД") + "' AND '" + Формат(КонМесяца(ТекДата)+1, "Д ГГГГММДД") + "' THEN '"+ Формат(ТекДата, "Д ДДММГГГГ") +" - " + Формат(КонМесяца(ТекДата), "Д ДДММГГГГ")+"'";
			ТекДата = КонМесяца(ТекДата)+1;  //новый месяц
		КонецЦикла;
		Группировка1 = Группировка1 + РазделительСтрок + "WHEN Выборка.DateCreated BETWEEN '" + Формат(ТекДата, "Д ГГГГММДД") + "' AND '" + Формат(КонДата+1, "Д ГГГГММДД") + "' THEN '"+ Формат(ТекДата, "Д ДДММГГГГ") +" - " + Формат(КонДата, "Д ДДММГГГГ")+"'";
		Группировка1 = Группировка1 + РазделительСтрок + "ELSE 'фигня' END AS Группировка1,";
а потом вставляю в текст запроса. При использовании DATEPАRT условий сравнения будет больше - т.к. надо будет сравнивать и месяц и год..или я что то неправильно понял?
  
Наверх
 
IP записан
 
Eprst
God Member
*****
Отсутствует



Сообщений: 3397
Зарегистрирован: 08. Октября 2007
Re: затруднения в написании запроса с группировкой по периодам
Ответ #28 - 18. Ноября 2011 :: 09:21
Печать  
Там вообще case не нужен в запросе
  
Наверх
 
IP записан
 
JBoy
Junior Member
**
Отсутствует


1C++ rocks!

Сообщений: 52
Зарегистрирован: 29. Декабря 2010
Re: затруднения в написании запроса с группировкой по периодам
Ответ #29 - 18. Ноября 2011 :: 09:23
Печать  
а как же без него??
  
Наверх
 
IP записан
 
Переключение на Главную Страницу Страницы: 1 [2] 3 
ОтправитьПечать
‹ Предыдущая Тема | Следующая Тема ›
 
  « Главная ‹ Раздел Вверх  



Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge