Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 динамическая сводная таблица  [new]
JASterus
Member

Откуда:
Сообщений: 354
есть txt файл, вида

date time pkw
01.03.15 0 0,0152
01.03.15 1 0,0187
01.03.15 2 0,0112
...
31.03.15 23 0,0252
он импортируется в БД, с этим проблем нет.
требуется для отчета выводить сводную таблицу
вида

ч/день | 01.03.2015 | .... | 31.03.2015
0 0,0152 xxx yyy
1 0,0187 asd dfg
... xxx
23 xxx xxx 0,0252
собственно, фиксированы только часы 0-23, и они должны быть по вертикали, а вот по горизонту даты не фиксированы, отчет должен быть на любое число.
как это можно организовать средствами t-sql? в сторону PIVOT смотрел, но там надо жестко задавать столбцы, что в данном случае не получается, т.к. неизвестно сколько дней в отчете будет
6 апр 15, 12:33    [17478281]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
Glory
Member

Откуда:
Сообщений: 104751
JASterus
отчет должен быть на любое число.

Даже на 10 лет ?
6 апр 15, 12:38    [17478297]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
JASterus
Member

Откуда:
Сообщений: 354
нет. имеется в виду, что отчет нужен, как с 1 по 31 число, так и на 18 или 25, к примеру. опять же, в феврале, к примеру 28 или 29 дней.
6 апр 15, 12:56    [17478392]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
Glory
Member

Откуда:
Сообщений: 104751
JASterus
нет. имеется в виду, что отчет нужен, как с 1 по 31 число, так и на 18 или 25, к примеру. опять же, в феврале, к примеру 28 или 29 дней.

Ну дык я смогу задать период длиной в 10 лет ? с 01.01.2005 по 31.12.2014 ?
6 апр 15, 13:01    [17478418]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
JASterus
Member

Откуда:
Сообщений: 354
Glory, задать можно все, что угодно. даже 50 лет. но этого не требуется.
для начала, требуется выбрать диапазон с 1 по произвольное число месяца. если это имеет значение, то данный отчет снимается с эл.счетчика, и по запросу должен быть отослан в энергосбытовую компанию.
6 апр 15, 13:13    [17478491]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
Glory
Member

Откуда:
Сообщений: 104751
JASterus
но этого не требуется.

Не требуется и будет ограничено - это очень сильно меняет задачу.

JASterus
если это имеет значение,

Имеет значение, сколько полей получится в результате.
6 апр 15, 13:16    [17478514]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
JASterus
как это можно организовать средствами t-sql?
Программирование вывода таблицы с переменным количеством столбцов всегда делается динамическим SQL

Но чаще это делают на клиенте, т.е. средствами построения репортов, которым вы генерите отчёты для отсылки в энергосбытовую компанию.
6 апр 15, 13:18    [17478538]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
JASterus
Member

Откуда:
Сообщений: 354
Glory, именно поэтому я и обозвал тему "динамическая сводная таблица"
ибо неизвестно, сколько полей(столбцов) будет выведено. как я говорил, фиксировано только кол-во строк в результирующей таблице, в отличие от кол-ва выводимых столбцов.
6 апр 15, 13:29    [17478616]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
Glory
Member

Откуда:
Сообщений: 104751
JASterus
ибо неизвестно, сколько полей(столбцов) будет выведено.

Еще раз.
В SELECT не может быть бесконечного числа полей
6 апр 15, 13:32    [17478633]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
JASterus
Member

Откуда:
Сообщений: 354
всем спасибо, за обсуждение. в общем, так и думал, буду через динамический запрос и #tempTables делать.
2alexeyvg, репорт со счетчика формирует тупо txt-файл. все расчеты делались в excel, руками создавалась сводная таблица, руками же формировался окончательный отчет по форме ЭСК. хотелось автоматизировать процесс.
6 апр 15, 14:00    [17478807]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
SSIS
6 апр 15, 14:03    [17478824]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
a_voronin
Member

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

Нужно на OLAP кубах такое делать и это не так сложно для такой простой задачи.
6 апр 15, 14:06    [17478846]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
a_voronin,
у меня допустим нет OLAP и в планах нет, а SSIS есть всегда.
6 апр 15, 14:10    [17478872]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
virtuOS
a_voronin,
у меня допустим нет OLAP и в планах нет, а SSIS есть всегда.


А зря. Если вы всю жизнь забивали гвозди, это не причина не взять шуруповерт.
6 апр 15, 14:23    [17478961]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
гр к
Guest
JASterus
всем спасибо, за обсуждение. в общем, так и думал, буду через динамический запрос и #tempTables делать.
2alexeyvg, репорт со счетчика формирует тупо txt-файл. все расчеты делались в excel, руками создавалась сводная таблица, руками же формировался окончательный отчет по форме ЭСК. хотелось автоматизировать процесс.

Эксель от 2007 может содержать 16000 строк. Ваша задача решается обычной сводной таблицей Экселя на основе исходных данных
Вполне себе вар-т, я так сам периодически делаю, чтобы не писать кучу строк типа
,sum (case when d1='20131201' then nachisl end) [Начислено за декабрь 2013]
,sum (case when d1='20131201' then esld end) [Сальдо на 01.01.2014]
,sum (case when d1='20140101' then nachisl end) [Начислено за январь 2014]
,sum (case when d1='20140101' then summa_opl end) [Оплачено в январе 2014]
,sum (case when d1='20140101' then esld end) [Сальдо на 01.02.2014]
,sum (case when d1='20140201' then nachisl end) [Начислено за февраль 2014]
,sum (case when d1='20140201' then summa_opl end) [Оплачено в феврале 2014]
,sum (case when d1='20140201' then esld end) [Сальдо на 01.03.2014]
6 апр 15, 15:03    [17479295]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
гр к
Guest
гр к
Эксель от 2007 может содержать 16000 строк.

Описался, конечно же речь о числе столбцов
6 апр 15, 15:05    [17479312]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
JASterus
Member

Откуда:
Сообщений: 354
гр к, в экселе, понятное дело, можно любую сводную забацать.
задача упростить работу оператора максимально. т.е. выбрать файл с данными, нажать на ОК, дальше сервер сам заберет файл, загонит в базу, обсчитает, и выведет конечный результат.
6 апр 15, 15:14    [17479376]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
Glory
Member

Откуда:
Сообщений: 104751
JASterus
. т.е. выбрать файл с данными, нажать на ОК, дальше сервер сам заберет файл, загонит в базу, обсчитает, и выведет конечный результат.

А типа сразу открыть файл в Экселе и построить из него PivotTable слишком просто ?
6 апр 15, 15:40    [17479520]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
гр к
Guest
Как-то так накопипастил из учебника
DECLARE
  @sql       AS NVARCHAR(1000),
  @dd AS datetime,
  @first     AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
  SELECT DISTINCT(ddate) AS dd
  FROM _t
  ORDER BY dd;

SET @first = 1;

SET @sql = N'SELECT *
FROM (SELECT time, ddate AS dd, pqw
      FROM _t) AS D
  PIVOT(avg(pqw) FOR dd IN(';

OPEN C

FETCH NEXT FROM C INTO @dd;

WHILE @@fetch_status = 0
BEGIN
  IF @first = 0
    SET @sql = @sql + N','
  ELSE
    SET @first = 0;

  SET @sql = @sql + QUOTENAME(@dd);

  FETCH NEXT FROM C INTO @dd;
END

CLOSE C;

DEALLOCATE C;

SET @sql = @sql + N')) AS P;';

EXEC sp_executesql @stmt = @sql;
GO
6 апр 15, 15:47    [17479578]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
Okmor
Member

Откуда:
Сообщений: 132
Все просто.
Перед транспонированием даты в троку нужно превратить.

if object_id('tempdb..#T') is not null drop table #T;

create table #T (Fio varchar(100),DAT varchar(10) , Value  numeric (15,2))	

INSERT INTO #T   (Fio, DAT, Value)
values 
('Иванов','2015.01.01',256),
('Иванов','2015.02.01',345),
('Иванов','2015.03.01',56),
('Иванов','2015.02.01',345),
('Иванов','2015.02.01',256),
('Петров','2015.01.01',67),
('Иванов','2015.04.01',256),
('Иванов','2015.02.01',35),
('Иванов','2015.01.01',256),
('Сидоров','2015.01.01',35),
('Сидоров','2015.04.01',256),
('Сидоров','2015.02.01',35)

declare @RN nvarchar(4000)
set @RN = (
select distinct '[ALL]'+( 
 select distinct ',['+#T.DAT+']' 
					 from #T  
					 for xml path('')
		)	)

declare @sql nvarchar(4000)
set @sql = ' 
 select *  from #T

 pivot (
   sum(  Value   )   for DAT in ( '+
   @RN
   +' ) ) as pvt 
   '
EXECUTE sp_executesql @sql	
6 апр 15, 16:05    [17479729]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Для тех, кто хочет сделать все по уму, а не заниматься подобными извращениями

Okmor
 ....
declare @sql nvarchar(4000)
set @sql = ' 
 select *  from #T

 pivot (
   sum(  Value   )   for DAT in ( '+
   @RN
   +' ) ) as pvt 
   '
EXECUTE sp_executesql @sql	


Предлагаю обратиться к OLAP кубам и языку MDX. Основное отличие от SQL в том, что в MDX запрос может развертывать данные не только по вертикали, как в SQL, а ещё и по горизонтали. И возможностей по группировке там масса.

http://habrahabr.ru/post/66356/

Многомерные кубы

Так что же все-таки эти многомерные кубы?
Представим себе 3-х мерное пространство, у которого по осям Время, Товары и Покупатели.
Точка в таком пространстве будет задавать факт того, что кто-то из покупателей в каком-то месяце купил какой-то конкретный товар.

Многомерный куб

Фактически, плоскость (или множество всех таких точек) и будет являться кубом, а, соответственно, Время, Товары и Покупатели – его измерениями.
Представить (и нарисовать) четырехмерный и более куб немного сложнее, но суть от этого не меняется, а главное, для OLAP систем совершенно неважно в скольких измерениях вы будете работать (в разумных пределах, конечно).

Немного MDX

Итак, в чем же прелесть MDX – скорее всего в том, что описывать нужно не то как мы хотим выбрать данные, а что именно мы хотим.
Например,

SELECT
{ [Measures].[Units] } ON COLUMNS,
{ [Time].[June, 2009], [Time].[July, 2009] } ON ROWS
FROM [Sales]
WHERE ([Product].[iPhone], [Country].[Mozambik])

* This source code was highlighted with Source Code Highlighter.



Что означает – хочу количество iPhone-ов, проданных в июне и июле в Мозамбике.
При этом я описываю какие именно данные я хочу и как именно я хочу их увидеть в отчете.
Красиво, не правда ли?

А вот чуть посложнее:

WITH MEMBER AverageSpend AS
[Measures].[Amount] / [Measures].[Transaction Count]
SELECT
{ AverageSpend } ON COLUMNS,
{ [Customer].[Sex].[Female], [Customer].[Sex].[Male] } ON ROWS
FROM [Sales]
WHERE ([Shop].[Apple])

* This source code was highlighted with Source Code Highlighter.


Фактически, вначале определяем формулу подсчета «среднего размера покупки» и пытаемся сравнить – кто же (какой пол), за один заход в магазин Apple, тратит больше денег.

Сам язык чрезвычайно интересен и для изучения и для использования, и, пожалуй, заслуживает немало обсуждений.

Заключение

На самом деле, данная статья очень мало покрывает даже базовых понятий, я бы назвал ее «appetizer» — возможность заинтересовать хабра-сообщество данной тематикой и развивать ее дальше. Что же касается развития – тут огромное непаханое поле, а я буду рад ответить на все интересующие вопросы.

P.S. Это мой первый пост об OLAP и первая публикацию на Хабре — буду очень признателен за конструктивный фидбек.
Update: Перенес в SQL, перенесу в OLAP как только разрешат создавать новые блоги.
6 апр 15, 17:13    [17480132]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
грязный комплектовщик
Member

Откуда: екб
Сообщений: 130
гр к
Как-то так накопипастил из учебника
SET @first = 1;
...

WHILE @@fetch_status = 0
BEGIN
  IF @first = 0
    SET @sql = @sql + N','
 ...

Сейчас дома посмотрел, что накопипастил и не понял, к чему проверка выше
Кто-нибудь пояснит?
Скрипт взял из книги "Основы Т-СКЛ" Ицика Бен-Гана.
6 апр 15, 17:55    [17480293]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
грязный комплектовщик
Сейчас дома посмотрел, что накопипастил и не понял, к чему проверка выше
Кто-нибудь пояснит?
Это вы про IF @first = 0?
Что бы первой запятой не было в списке PIVOT ... IN

Можно эту строку делать и каким либо ещё способом, не через цикл, можно тут на форуме поискать.
6 апр 15, 18:02    [17480318]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
Okmor
Member

Откуда:
Сообщений: 132
a_voronin,
автор
Для тех, кто хочет сделать все по уму, а не заниматься подобными извращениями


У нас тоже есть кубы и не хилые.
Но когда начальство просит сделать отчет по продажам торговых в разрезе толщины ихних яиц,
и чтобы Вася и Петя слопнулись, а Колю поделить на два бренда, а на совещании приняли начало месяца
с 5 числа, а конец 27 числа.
Все енто нужно на вчера, ибо совещание. Тогда посылаешь в ж..у все кубы и пишешь простыми запросами.

Вся эта трахомудия с кубами и отчетами закончилась самописной интерактивной системой. Ибо начальство
захотело не только видеть отчет, но и тыкнуть в клеточку и внести туда циферку, а еще карты и GPS хотим и чтобы фотки были.
7 апр 15, 16:17    [17484265]     Ответить | Цитировать Сообщить модератору
 Re: динамическая сводная таблица  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
Okmor
a_voronin,
автор
Для тех, кто хочет сделать все по уму, а не заниматься подобными извращениями


У нас тоже есть кубы и не хилые.
Но когда начальство просит сделать отчет по продажам торговых в разрезе толщины ихних яиц,
и чтобы Вася и Петя слопнулись, а Колю поделить на два бренда, а на совещании приняли начало месяца
с 5 числа, а конец 27 числа.
Все енто нужно на вчера, ибо совещание. Тогда посылаешь в ж..у все кубы и пишешь простыми запросами.

Вся эта трахомудия с кубами и отчетами закончилась самописной интерактивной системой. Ибо начальство
захотело не только видеть отчет, но и тыкнуть в клеточку и внести туда циферку, а еще карты и GPS хотим и чтобы фотки были.


А что у вас такое "отчёт"? Если Reporting Services, то там есть Matrix компонент, который прекрасно справиться с этой галиматьей по развертыванию дат по горизонтали. Excel тоже может сделать pivot -- тот самое, что и в Reporting. К чему все эти выкрутасы с динамическим Pivot на уровне SQL?
7 апр 15, 19:46    [17485145]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить