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

Откуда:
Сообщений: 53
 		Добрый день, просьба помочь научится выводить таблицу с аналитикой.
Повторяющаяся группа [payment] (принемает 2-а значения это "наличка" и "банк") по этой колонке нужно собрать аналитику...
WITH T0 (id, dates, cod, name, price, quantity, payment) AS--Тестовый набор данных
(SELECT * FROM (VALUES
( 1, '2012-11-01', 1, 'МУКА', 10.5,   2, 'nal'),
( 2, '2012-11-02', 1, 'МУКА', 10.6,   3, 'nal'),
( 4, '2012-11-03', 1, 'МУКА', 10.8,   1, 'nal'),
( 3, '2012-11-02', 1, 'МУКА', 10.2, 100, 'bank'),
( 5, '2012-11-03', 1, 'МУКА', 10.3,  30, 'bank'),
( 6, '2012-11-04', 1, 'МУКА', 10.4,  55, 'bank'),
( 7, '2012-11-05', 1, 'МУКА', 10.5,   4, 'nal'),
( 8, '2012-11-06', 1, 'МУКА', 10.6,   5, 'nal'),
(10, '2012-11-08', 1, 'МУКА', 10.8,   4, 'nal'),
( 9, '2012-11-06', 1, 'МУКА', 10.2, 200, 'bank'),
(11, '2012-11-08', 1, 'МУКА', 10.3,  35, 'bank'),
(12, '2012-11-09', 1, 'МУКА', 10.4, 100, 'bank'))
v(a, b, c, d, e, f, g))
--
SELECT * FROM T0


К сообщению приложен файл. Размер - 37Kb
1 дек 12, 18:08    [13561993]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest 		Логику получения результата не понял. :(
1 дек 12, 19:31    [13562182]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest 		Критерия сортировки не увидел. :(
1 дек 12, 19:42    [13562226]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest 		Логику понял, но критерия сортировки не вижу.
И ещё - почему для первой группы среднее получилось 10.6? Ведь должно быть 10.63 ((10.5 + 10.6 + 10.8)/3)
1 дек 12, 19:48    [13562240]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Neosan
Member

Откуда:
Сообщений: 53
 		Добрый Э - Эх,
да действительно получилась каша, сейчас исправлю...
WITH T0 (id, dates, cod, name, price, quantity, payment) AS--Тестовый набор данных
(SELECT * FROM (VALUES
( 1, '2012-11-01', 1, 'AA', 10.5,   2, 0),
( 2, '2012-11-02', 1, 'AA', 10.6,   3, 0),
( 3, '2012-11-03', 1, 'AA', 10.8,   1, 0),
( 4, '2012-11-04', 1, 'AA', 10.2, 100, 1),
( 5, '2012-11-05', 1, 'AA', 10.3,  30, 1),
( 6, '2012-11-06', 1, 'AA', 10.4,  55, 1),
( 7, '2012-11-07', 1, 'AA', 10.5,   4, 0),
( 8, '2012-11-08', 1, 'AA', 10.6,   5, 0),
( 9, '2012-11-09', 1, 'AA', 10.8,   4, 0),
(10, '2012-11-10', 1, 'AA', 10.2, 200, 1),
(11, '2012-11-11', 1, 'AA', 10.3,  35, 1),
(12, '2012-11-12', 1, 'AA', 10.4, 100, 1))
v(a, b, c, d, e, f, g))
--
SELECT * FROM T0
1 дек 12, 19:50    [13562248]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest 		хотя, и для 2-й и 4-й группы среднее как-то странно у тебя рассчиталось.
1 дек 12, 19:50    [13562249]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest 		Ну, тут либо уже изученный тобой инвариант на двух разнооконных ROW_NUMBER-ах, либо метод начала групп... :)
А далее - просто наложить оконные агрегаты. Сам разберешься, или показывать?
1 дек 12, 19:55    [13562266]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Neosan
Member

Откуда:
Сообщений: 53
 		Добрый Э - Эх,
Это называется среднее-взвешенная цена = "общая сумма произведения строк в группе [price]*[quantity] деленная на общее кол-во [quantity] в группе.
1 дек 12, 19:56    [13562269]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Neosan
Member

Откуда:
Сообщений: 53
 		Добрый Э - Эх,
по поводу наложения агрегатов если можно покажите (не разу не делал)...
1 дек 12, 19:57    [13562279]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Neosan
Member

Откуда:
Сообщений: 53
 		Neosan,
пример для средне-взвешенной цены первой группы (nal) из 3-строк = (10.5*2 + 10.6*3+10.8*1)/(2+3+1)
1 дек 12, 20:01    [13562294]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest 		Neosan,

В лес оконные агрегаты. Обычных хватит. :)
--
-- Тестовый набор данных:
WITH T0 (id, dates, cod, name, price, quantity, payment) AS--Тестовый набор данных
(SELECT * FROM (VALUES
( 1, '2012-11-01', 1, 'AA', 10.5,   2, 0),
( 2, '2012-11-02', 1, 'AA', 10.6,   3, 0),
( 3, '2012-11-03', 1, 'AA', 10.8,   1, 0),
( 4, '2012-11-04', 1, 'AA', 10.2, 100, 1),
( 5, '2012-11-05', 1, 'AA', 10.3,  30, 1),
( 6, '2012-11-06', 1, 'AA', 10.4,  55, 1),
( 7, '2012-11-07', 1, 'AA', 10.5,   4, 0),
( 8, '2012-11-08', 1, 'AA', 10.6,   5, 0),
( 9, '2012-11-09', 1, 'AA', 10.8,   4, 0),
(10, '2012-11-10', 1, 'AA', 10.2, 200, 1),
(11, '2012-11-11', 1, 'AA', 10.3,  35, 1),
(12, '2012-11-12', 1, 'AA', 10.4, 100, 1))
v(a, b, c, d, e, f, g))
--
-- Основной запрос:
select row_number() over(order by min(dates)) as id,
       min(dates) as [open],
       'х.з. что такое и как его вычислять' as period, -- про период - принцип вычисления не понял.
       cod, name, 
       sum(quantity * price)/sum(quantity) as avg_price,
       sum(quantity) as quantity,
       payment
  from (
         SELECT t0.*, 
                row_number() over(partition by cod, name, payment order by id) -
                row_number() over(partition by cod, name order by id) as grp_id -- Инвариант :)
           FROM T0
       ) as v
 group by cod, name, payment, grp_id
 order by min(dates)
Он-лайн проверка на sqlfiddle.com
1 дек 12, 20:08    [13562327]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Neosan
Member

Откуда:
Сообщений: 53
 		Добрый Э - Эх,
спасибо за пример буду изучать "наложение оконных агрегатов",
как разберусь попробую написать более сложный пример.
с уважением neosan...
1 дек 12, 23:55    [13563063]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest 		Neosan,

Нету в моем примере оконных агрегатов. Потому-то и написал: "в лес оконные агрегаты."
Обычные есть, оконных - нет.
3 дек 12, 04:44    [13566207]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Neosan
Member

Откуда:
Сообщений: 53
 		Добрый Э - Эх,
наконец то можно заняться обучением...
так кажется разобрался, в данном примере, чтобы не повторятся - добавил инвариант через COUNT (для меня в предыдущей теме по инварианту это было неожиданное интересное решение) правда для этого пришлось сделать еще одно вложение (что есть не очень хорошо - но для учебы думаю подойдет), для усложнения - в Тестовом наборе данных отвязал счетчик от даты, получилось примерно так 
--ОБУЧЕНИЕ "Вывод таблицы с аналитикой по повторяющимся группам"
WITH T0 (id, dates, cod, name, price, quantity, payment) AS--Тестовый набор данных
(SELECT * FROM (VALUES
( 1, '2012-11-01', 1, 'AA', 10.5,   2, 0),( 2, '2012-11-01', 1, 'AA', 10.6,   3, 0),( 3, '2012-11-02', 1, 'AA', 10.8,   1, 0),
( 4, '2012-11-04', 1, 'AA', 10.2, 100, 1),( 5, '2012-11-05', 1, 'AA', 10.3,  30, 1),( 6, '2012-11-06', 1, 'AA', 10.4,  55, 1),
( 7, '2012-11-03', 1, 'AA', 10.5,   4, 0),( 8, '2012-11-08', 1, 'AA', 10.6,   5, 0),( 9, '2012-11-09', 1, 'AA', 10.8,   4, 0),
(10, '2012-11-07', 1, 'AA', 10.2, 200, 1),(11, '2012-11-11', 1, 'AA', 10.3,  35, 1),(12, '2012-11-12', 1, 'AA', 10.4, 100, 1))
v(a, b, c, d, e, f, g))
--
--УСЛОВИЕ для поиска повторных_ГРУПП (ЕСЛИ "выше_дата <=1 то "NULL" (т.е. цепочка деней не прерывается), ИНАЧЕ "1" - маркер начала группы)
,T1 AS (SELECT * ,[grp_log]=CASE WHEN DATEDIFF(dd, LAG(dates,1) OVER(PARTITION BY cod, name, payment ORDER BY id), dates) <=1 THEN NULL ELSE 1 END FROM T0)
--Формируем столбец с нумерацией ГРУПП_подобных (инвариант группы)
,T2 AS (SELECT * ,[grp_id]=COUNT(grp_log) OVER(PARTITION BY cod, name ORDER BY dates) FROM T1)
--
SELECT
 [id]=ROW_NUMBER()OVER(ORDER BY MIN(dates))--счетчик повторяющихся групп (G.)
,[open]=MIN(dates)--дата начала группы
,[period]=DATEDIFF(day, MIN(dates),MAX (dates))--длительность G. в днях
,[set]=COUNT(payment)--G. в составе кол-ва строк
,cod, name
,[avg_price]=CONVERT(SMALLMONEY, SUM(quantity * price)/SUM(quantity))--СВЦ в G.
,[quantity]=SUM(quantity)--общее кол-во ед. в G.
,payment
FROM T2
GROUP BY cod, name, payment, grp_id
ORDER BY 1

P.S. на счет оконных агрегатов, в каких случаях они могут потребоваться, в моем примере я так понимаю - они только усложнят код?
3 дек 12, 17:10    [13570084]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Neosan
Member

Откуда:
Сообщений: 53
 		т.е. в моем примере достаточно обычных "агрегатных функций" без аналитических (оконных, с приставкой OVER) функций, это связано с тем, что для расчета примера нужно урезание детализации (т.е. не требуется сохранять высокую степень детализации в запросе)...
3 дек 12, 17:59    [13570492]     Ответить | Цитировать Сообщить модератору
 Re: Обучение: Вывод таблицы с аналитикой  [new]
Добрый Э - Эх
Guest
Neosan
P.S. на счет оконных агрегатов, в каких случаях они могут потребоваться, в моем примере я так понимаю - они только усложнят код?
Тогда, когда нужно получить агрегатное значение для каждой строки исходного набора данных, без свертки в "одну строку" по ключу группировки, как это делает обычная группировка. В твоем учебном примере прикрутить их можно, код усложнится не сильно - всего-то заменить обычные агрегаты на их оконные аналоги, GROUP BY убрать, описав разбиение на группы в over(partition) -кляузе оконной функции, добавить модификатор DISTINCT | UNIQUE после слова SELECT. Но в целом, пользоваться оконными агрегаты для получения обычной группировки - моветон
3 дек 12, 18:32    [13570735]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить