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

Откуда:
Сообщений: 12
Здравствуйте!

Подскажите пожалуйста как рассчитать процент в запросе? - 2 день в инете ищу и найти внятного ничего не могу! Надо рассчитать "процент итого от всего", т.е. второй запрос сопоставить с третьим запросом - помогите пожалуйста

автор
USE AdventureWorks2012;

SELECT Color, COUNT(*), SUM(StandardCost), 0 AS Sort1
FROM Production.Product
WHERE Color = N'Silver' OR Color = N'Black'
GROUP BY Color

UNION ALL
SELECT N'ИТОГО', COUNT(*), SUM(StandardCost), 1 AS Sort1
FROM Production.Product
WHERE Color = N'Silver' OR Color = N'Black'

UNION ALL
SELECT N'ВСЕГО', COUNT(*), SUM(StandardCost), 2 AS Sort1
FROM Production.Product

UNION ALL
SELECT N'процент итого от всего', COUNT(*), SUM(StandardCost), 3 AS Sort1
FROM Production.Product
7 июн 16, 16:04    [19267777]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
KRS544
Member

Откуда:
Сообщений: 497
как вариант посчитать сначала "Всего", сохранить в переменную, потом подставлять в запросе.
7 июн 16, 16:42    [19267954]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
alks
Member

Откуда:
Сообщений: 12
KRS544, пример пожалуйста напишите - я новичок
7 июн 16, 16:52    [19267995]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
KRS544
Member

Откуда:
Сообщений: 497
declare @t table (id int, Color varchar(100))
insert into @t 
values(1,'Black'),(2,'Silver'),(3,'Black'),(4,'Silver'),(5,'Black'),(6,'Silver'),(7,'Silver')

declare @cnt int
select @cnt= COUNT(*) FROM @t

SELECT COUNT(*)*100/@cnt as [процент]
FROM @t
WHERE Color = N'Silver' OR Color = N'Black'
GROUP BY Color
7 июн 16, 17:10    [19268070]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
o-o
Guest
declare @cnt1 int, @tot1 money, @cnt2 int, @tot2 money;

SELECT @cnt1 = COUNT(*), @tot1 = SUM(StandardCost)
FROM Production.Product
WHERE Color = N'Silver' OR Color = N'Black'

SELECT @cnt2 = COUNT(*), @tot2 = SUM(StandardCost)
FROM Production.Product

SELECT Color, COUNT(*), SUM(StandardCost), 0 AS Sort1
FROM Production.Product
WHERE Color = N'Silver' OR Color = N'Black'
GROUP BY Color

UNION ALL
SELECT N'ИТОГО', @cnt1, @tot1, 1 AS Sort1

UNION ALL
SELECT N'ВСЕГО', @cnt2, @tot2, 2 AS Sort1

UNION ALL
SELECT N'процент итого от всего', @cnt1 * 100 / @cnt2, @tot1 / @tot2 * 100, 3 AS Sort1
7 июн 16, 17:14    [19268085]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
alks
Member

Откуда:
Сообщений: 12
KRS544, Спасибо, сейчас буду разбираться с вашем кодом с переменной!
7 июн 16, 17:16    [19268092]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
alks
Member

Откуда:
Сообщений: 12
o-o, Спасибо!!! - все понял!!!,

а переменные делают более длительную загрузку отчета?
7 июн 16, 17:24    [19268127]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
o-o
Guest
нет, наоборот, так не считаются по второму разу итоги 2 и 3.
а отчет-то чем строите?
обычно любой построитель отчетов итоги в состоянии сам посчитать
7 июн 16, 17:28    [19268147]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
Добрый Э - Эх
Guest
alks,

Зачем столько юнионов? Case и расширенные группировки (rollup, cube) не канают?
А для расчётов процентов одного от другого - оконные функции
7 июн 16, 17:29    [19268158]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
alks
Member

Откуда:
Сообщений: 12
o-o, строю в SSMS - учусь формировать отчеты на языке SQL (параллельно изучаю его), затем перейду на SSAS
7 июн 16, 17:37    [19268197]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
Alexander Us
Member

Откуда:
Сообщений: 1153
alks,

навеяло:

Встречаются два одноклассника. Новыи русский, и простой советский инженер.

- Вась, это ты? На 600-том мерсе, в красном пиджаке?
Ведь в школе у тебя всегда были одни двоики.
Как бизнес делаешь, на что живёшь?

- Ты понимаыеш Санек. Все очень просто, в натуре.
Еду в Германию и покупаю бочку пива за 1000 баксов.
Приезжаю домой, толкаю ее за 3000 баксов.
Вот на эти два ПРОЦЕНТА я и живу.
7 июн 16, 17:37    [19268199]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
alks
Member

Откуда:
Сообщений: 12
Alexander Us, Забавный анекдот!
7 июн 16, 17:42    [19268223]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8316
alks,

отчет сам может выполнять такие вычисления. Если Вы под отчетом имеете в виду то же, что и все.
7 июн 16, 17:56    [19268271]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
with s (n, g, c1, s1, c2, s2) as
(
 select
  t.n,
  t.g,
  count(*),
  sum(p.StandardCost),
  count(t.f),
  sum(t.StandardCost)
 from
  Production.Product p cross apply
  (
   select 1, 'Black', 1, p.StandardCost where p.Color = 'Black'
   union all
   select 1, 'Silver', 1, p.StandardCost where p.Color = 'Silver'
   union all
   select 2, 'ИТОГО', 1, p.StandardCost where p.Color in ('Black', 'Silver')
   union all
   select 3, 'ВСЕГО', 1, p.StandardCost
   union all
   select 4, 'процент итого от всего', case when p.Color in ('Black', 'Silver') then 1 end, case when p.Color in ('Black', 'Silver') then p.StandardCost end
  ) t(n, g, f, StandardCost)
 group by
  t.n, t.g
)
select
 s.g,
 case when s.n < 4 then s.c1 else 100 * s.c2 / s.c1 end,
 case when s.n < 4 then s.s1 else 100 * s.s2 / s.s1 end
from
 s
order by
 s.n;
7 июн 16, 18:07    [19268313]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
alks
Member

Откуда:
Сообщений: 12
Владислав Колосов, я только еще изучаю sql, какой отчет может выполнить сам, моя цель работать с большими базами данных (я проф. экономист) в 1С к примеру невозможно работать с гигантской информацией (на ночь люди оставляют чтоб сформировать отчет) - и считаю что за sql будущее (с аналитическими формулами у меня проблем нет как и с vba в экселе, а вот работа с большим объемом данных в современных технологиях - есть)

пока планирую научится языку sql затем перейти на olap кубы через ssas затем на ssrs
7 июн 16, 18:13    [19268330]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
west74
Member

Откуда: Челябинск
Сообщений: 76
Приветствую
invm решил компактней

я уж так чтоб были варианты :)
тем более планируется шаг от sql к olap,
так нужно познать все стороны силы sql :D



declare @t table
(
"color" varchar(20),
StandartCost numeric(12,2)
)

insert into @t(color,StandartCost) values
('Red',1.1),('Red',1.3),('Red',1.5),('Red',2.1),('Red',0.9),
('Blue',5.1),('Blue',4.8),('Blue',5.7),('Blue',6.1),
('Black',15.1),('Black',14.8),('Black',15.7),
('Silver',7.1),('Silver',6.8)

select * from (
select Color,
count_val,
cost_sum,
cast((1.0*count_val/SUM(count_val*case when [color] in ('Всего','Итого') then 0 else 1 end) over())*100 as int) 'процент строк',
cast((1.0*cost_sum/SUM(cost_sum*(case when [color] in ('Всего','Итого') then 0 else 1 end)) over())*100 as int) 'процент сумм'
from (

select
case
when color IS not null then color
when color IS null and aFlag=1 then 'Итого'
else 'Всего'
end as [Color],
count_val,
cost_sum,
aFlag
from (
select
color ,aFlag ,
sum(C_SB) count_val,
sum(S_SB) cost_sum

from (select
Color,aFlag,
COUNT(*) C_SB,
Sum(StandartCost) S_SB
from (
select
color,
case when color in (N'Silver',N'Black') then 1 else 0 end aFlag,
StandartCost
from (select case when color in (N'Silver',N'Black') then color else 'offen' end color,
standartCost from @t)D
) A
group by color,aFlag
) B
group by cube(color ,aFlag )
) C
where
(case
when color is not null and aFlag IS not null then 1
when color is null and aFlag Is null then 1
when color is null and aFlag=1 then 1
else 0 end
)=1
) Al
) A2 where isnull(color,'')<>'offen'
9 июн 16, 15:17    [19276573]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
o-o
Guest
west74
invm решил компактней

у него там смайл аж зеленый.
set statistics time, io on
declare @cnt1 int, @tot1 money, @cnt2 int, @tot2 money;
 
SELECT @cnt1 = COUNT(*), @tot1 = SUM(StandardCost)
FROM Production.Product
WHERE Color = N'Silver' OR Color = N'Black'
 
SELECT @cnt2 = COUNT(*), @tot2 = SUM(StandardCost)
FROM Production.Product
 
SELECT Color, COUNT(*), SUM(StandardCost), 0 AS Sort1
FROM Production.Product
WHERE Color = N'Silver' OR Color = N'Black'
GROUP BY Color
 
UNION ALL
SELECT N'ИТОГО', @cnt1, @tot1, 1 AS Sort1
 
UNION ALL
SELECT N'ВСЕГО', @cnt2, @tot2, 2 AS Sort1
 
UNION ALL
SELECT N'процент итого от всего', @cnt1 * 100 / @cnt2, @tot1 / @tot2 * 100, 3 AS Sort1
-------------------------------------------------------------------------------------------
 
;with s (n, g, c1, s1, c2, s2) as
(
select
  t.n,
  t.g,
  count(*),
  sum(p.StandardCost),
  count(t.f),
  sum(t.StandardCost)
from
  Production.Product p cross apply
  (
   select 1, 'Black', 1, p.StandardCost where p.Color = 'Black'
   union all
   select 1, 'Silver', 1, p.StandardCost where p.Color = 'Silver'
   union all
   select 2, 'ИТОГО', 1, p.StandardCost where p.Color in ('Black', 'Silver')
   union all
   select 3, 'ВСЕГО', 1, p.StandardCost
   union all
   select 4, 'процент итого от всего', case when p.Color in ('Black', 'Silver') then 1 end, case when p.Color in ('Black', 'Silver') then p.StandardCost end
  ) t(n, g, f, StandardCost)
group by
  t.n, t.g
)
select
s.g,
case when s.n < 4 then s.c1 else 100 * s.c2 / s.c1 end,
case when s.n < 4 then s.s1 else 100 * s.s2 / s.s1 end
from
s
order by
s.n;

-------------------------------------------------------------------------------------------

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 22 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 30 ms.

(5 row(s) affected)
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 81 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(5 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 257 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
------------------------------------------------------------------------------------------------------
130ms vs 260ms
9 июн 16, 16:17    [19277055]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
KRS544
declare @t table (id int, Color varchar(100))
insert into @t 
values(1,'Black'),(2,'Silver'),(3,'Black'),(4,'Silver'),(5,'Black'),(6,'Silver'),(7,'Silver')

declare @cnt int
select @cnt= COUNT(*) FROM @t

SELECT COUNT(*)*100/@cnt as [процент]
FROM @t
WHERE Color = N'Silver' OR Color = N'Black'
GROUP BY Color


А в один запрос слабо ?

declare @t table (id int, Color varchar(100))
insert into @t 
values(1,'Black'),(2,'Silver'),(3,'Black'),(4,'Silver'),(5,'Black'),(6,'Silver'),(7,'Silver')


SELECT COUNT(*)*100/SUM(COUNT(*)) OVER () as [процент]
FROM @t
WHERE Color = N'Silver' OR Color = N'Black'
GROUP BY Color
9 июн 16, 16:30    [19277121]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
o-o
Guest
ну оно конечно в один запрос,
правда, не похоже вообще на то, что надо ТС
вот картинкой, что у него,
и что у вас.
а KRS544 просто пример приводил, как его и просили

К сообщению приложен файл. Размер - 33Kb
9 июн 16, 17:20    [19277352]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
fduch f.f.
Member

Откуда: Белокаменная
Сообщений: 260
это нужно делать в одном запросе, с использованием оконных функций
представьте себе что у вас этот скрипт в процедуре имеющей много фильтрующих параметров
если будет через юнианы, то придется пихать эти фильтры в каждый селект
10 июн 16, 12:35    [19280001]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
fduch f.f.
это нужно делать в одном запросе, с использованием оконных функций
представьте себе что у вас этот скрипт в процедуре имеющей много фильтрующих параметров
если будет через юнианы, то придется пихать эти фильтры в каждый селект

в смысле: устанет рука?
10 июн 16, 12:41    [19280038]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
fduch f.f.
Member

Откуда: Белокаменная
Сообщений: 260
TaPaK
fduch f.f.
это нужно делать в одном запросе, с использованием оконных функций
представьте себе что у вас этот скрипт в процедуре имеющей много фильтрующих параметров
если будет через юнианы, то придется пихать эти фильтры в каждый селект

в смысле: устанет рука?


ну да
будет неудобно сопровождать такой код
10 июн 16, 12:44    [19280054]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
fduch f.f.
TaPaK
пропущено...

в смысле: устанет рука?


ну да
будет неудобно сопровождать такой код

плевать на производительность, главное чтобы писать меньше
10 июн 16, 12:45    [19280062]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление процента  [new]
o-o
Guest
fduch f.f.
представьте себе что у вас этот скрипт в процедуре имеющей много фильтрующих параметров...

сказочку про Кикоса не хотите ли?
+ много чего представить можно


10 июн 16, 12:59    [19280146]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить