Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
Логику получения результата не понял. :( |
1 дек 12, 19:31 [13562182] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
Критерия сортировки не увидел. :( |
1 дек 12, 19:42 [13562226] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
Логику понял, но критерия сортировки не вижу. И ещё - почему для первой группы среднее получилось 10.6? Ведь должно быть 10.63 ((10.5 + 10.6 + 10.8)/3) |
1 дек 12, 19:48 [13562240] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
хотя, и для 2-й и 4-й группы среднее как-то странно у тебя рассчиталось. |
1 дек 12, 19:50 [13562249] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
Ну, тут либо уже изученный тобой инвариант на двух разнооконных ROW_NUMBER-ах, либо метод начала групп... :) А далее - просто наложить оконные агрегаты. Сам разберешься, или показывать? |
1 дек 12, 19:55 [13562266] Ответить | Цитировать Сообщить модератору |
Neosan Member Откуда: Сообщений: 53 |
Добрый Э - Эх, Это называется среднее-взвешенная цена = "общая сумма произведения строк в группе [price]*[quantity] деленная на общее кол-во [quantity] в группе. |
1 дек 12, 19:56 [13562269] Ответить | Цитировать Сообщить модератору |
Neosan Member Откуда: Сообщений: 53 |
Добрый Э - Эх, по поводу наложения агрегатов если можно покажите (не разу не делал)... |
1 дек 12, 19:57 [13562279] Ответить | Цитировать Сообщить модератору |
Neosan Member Откуда: Сообщений: 53 |
Neosan, пример для средне-взвешенной цены первой группы (nal) из 3-строк = (10.5*2 + 10.6*3+10.8*1)/(2+3+1) |
1 дек 12, 20:01 [13562294] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
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] Ответить | Цитировать Сообщить модератору |
Neosan Member Откуда: Сообщений: 53 |
Добрый Э - Эх, спасибо за пример буду изучать "наложение оконных агрегатов", как разберусь попробую написать более сложный пример. с уважением neosan... |
1 дек 12, 23:55 [13563063] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
Neosan, Нету в моем примере оконных агрегатов. Потому-то и написал: "в лес оконные агрегаты." Обычные есть, оконных - нет. |
3 дек 12, 04:44 [13566207] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
Neosan Member Откуда: Сообщений: 53 |
т.е. в моем примере достаточно обычных "агрегатных функций" без аналитических (оконных, с приставкой OVER) функций, это связано с тем, что для расчета примера нужно урезание детализации (т.е. не требуется сохранять высокую степень детализации в запросе)... |
3 дек 12, 17:59 [13570492] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
|
||
3 дек 12, 18:32 [13570735] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |