Отображение данных за 12 месяцев за последние 5 лет

ProgramBox

Отображение данных за 12 месяцев за последние 5 лет

Post author:admin
Запись опубликована:18 апреля, 2023
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

В настоящее время я создаю скрипт, который будет извлекать данные счета за 5 лет и суммировать суммы счетов по месяцам этого года для конкретного клиента. Пример

 Year  jan  feb mar 
2011  800  900 700
2012  700  800 900, and so forth

Однако у меня возникают проблемы с получением такого вывода. Мой текущий код

     select MAX(cust) as customer,year(invoicedate) as y, month(invoicedate) as    m, sum(amount) as summary
          from #tquery   
          group by year(dinvoice), month(dinvoice)
          having MAX(ccustno) ='WILLAMETTE'
          order by y asc,m asc

          select * from #tquery

дает мне это. мне просто нужно найти способ их переформатирования.

 customer    year   month  amount
WILLAMETTE  2012    11     500
WILLAMETTE  2012    12     600
WILLAMETTE  2013    1     600

1. ваш запрос в порядке. Теперь проверьте наличие PIVOT

2. Найдите «SQL Server PIVOT», там должно быть множество примеров.

3. посмотрите на этот простой пример для Pivot. Он похож на ваш, за исключением того, что вывод осуществляется по дням недели (Сб, Вс, пн. и т.д.): blogs.msdn.microsoft.com/spike/2009/03/03 /…

Ответ №1:

Нет необходимости выполнять сводку. Это всего лишь 12 столбцов. Условная агрегация была бы более эффективной

  Select Customer = cust
       ,Year     = year(invoicedate) 
       ,Jan      = sum(case when  month(invoicedate) = 1 then amount else 0 end)
       ,Feb      = sum(case when  month(invoicedate) = 2 then amount else 0 end)
       ...
       ,Dec      = sum(case when  month(invoicedate) =12 then amount else 0 end)
 From  #tquery   
 Group by ccustno,year(dinvoice)
 Order By 1,2

1. (1) Зачем использовать нестандартное сглаживание столбцов? (2) SQL, как вы знаете, является декларативным языком. С этой точки зрения лучше подходит PIVOT. (3) Как вы пришли к выводу, что условная агрегация имеет лучшую производительность, чем СВОДНАЯ? вы, случайно, не тестировали это?

2. 1) Это работает и предлагает лучшую визуализацию. Как разработчики, мы имеем дело с объектами и абстрактами. Объектом в данном случае является JAN, а не то, как мы туда попали. 2) Я категорически не согласен. 3) Да, но не в этом конкретном случае. Например, у одного из моих клиентов DWH-строка размером 5,2 Б, и условная агрегация была намного лучше, пункты 1 и 2 чисто субъективны, и я не думаю, что это оправдывает понижение. # 3 Условная агрегация обеспечивает немного большую гибкость (т. Е. дополнительные столбцы / агрегаты).

3. @DuduMarkovitz: По крайней мере, в SQL Server PIVOT это просто упрощенный синтаксис для условной агрегации с использованием РЕГИСТРА (см. sqlmag.com/sql-server/logical-query-processing-clause-and-pivot ), но CASE намного более гибкий.

4. @dnoeth, и x in (1,2,3,4,5) это просто упрощенный синтаксис для, (x=1 or x=2 or x=3 or x=4 or x=5) но or он намного более гибкий. Когда вы использовали OR для этой задачи в последний раз? … и having — это просто упрощенный синтаксис для select ... from (select ... group by ...) where ... и qualify — это просто упрощенный синтаксис для select ... from (select ... over () ...) where ...' and over (… игнорировать нули)` is just …. понимаете мою точку зрения? 🙂

5. @DuduMarkovitz: Конечно, но, эй, вы обычно заботитесь о переносимости :-), IN amp; HAVING поддерживается каждой СУБД (к сожалению, нет QUALIFY ). Когда мне нужно выполнить сводку, существует высокая вероятность того, что мне придется добавить другой агрегат (например, сумму за год или количество в приведенном выше выборе), и при этом произойдет сбой сводки (это упрощенное подмножество синтаксиса, например, COALESCE vs. CASE ). Согласен, что обычно это больше текста, но в основном это вырезание и вставка (и не забудьте изменить)

Ответ №2:

Для переформатирования строк в столбец необходимо использовать PIVOT

Ответ №3:

 select      customer
           ,y
           ,"1","2","3","4","5","6","7","8","9","10","11","12"

from       (select      cust as customer,year(invoicedate) as y, month(invoicedate) as m,amount
            from        #tquery   
            where       ccustno ='WILLAMETTE'
            )
            t

            pivot (sum (amount) for m  in ("1","2","3","4","5","6","7","8","9","10","11","12")) p

order by    y
;