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

Откуда: Петропавловск-Камчатский
Сообщений: 54
Если по колонке найти среднее - это функция AVG()

Ну а если нужно найти среднее по строке и при том.. одним запросом..

вот пример:

поля таблицы:

pay1 pay2 pay3 colonka4 colonka5

22 13 0 *** ***
12 0 34 *** ***



нужна выборка типа


select colonka4, colonka5, pay1, pa2, pay3, СРЕДНЕЕ по строке значений pay1, pay2, pay3
24 июн 15, 08:28    [17809401]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
VSVLAD
Member

Откуда: Краснодар
Сообщений: 1358
cold09,

(pay1 + pay2 + pay3) / 3
24 июн 15, 08:36    [17809416]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
справка от КЭПа
Guest
cold09,

среднее - это сумма значений, разделённая на их количество
24 июн 15, 08:36    [17809417]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
очень смешно..

а почему на 3 ?

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

а потому среднее значение и там и там нужно делить не на 3, а на ДВА..

а есть и вариант с одним ненулевым значением..

вот ведь в чём вопрос..
24 июн 15, 08:56    [17809459]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
VSVLAD
Member

Откуда: Краснодар
Сообщений: 1358
cold09,

А чем смущает 0? 0 и NULL это разные вещи, Вы об этом? Тогда Case When в руки, или Unpivot
24 июн 15, 09:02    [17809471]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
к сожалению, у нас уже вечер, пойду домой...


если у кого-то родится решение - пожалуйста, пишите..

Спасибо!
24 июн 15, 09:03    [17809475]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
КЭП,
Guest
cold09
очень смешно..

а почему на 3 ?

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

а потому среднее значение и там и там нужно делить не на 3, а на ДВА..

а есть и вариант с одним ненулевым значением..

вот ведь в чём вопрос..
нулевых или НУЛЛ-овых ?
Если второе:
(isnull(pay1,0) + isnull(pay2,0) + isnull(pay3,0)) 
/ 
(case when pay1 is null then 0 else 1 end +
    case when pay2 is null then 0 else 1 end +
    case when pay3 is null then 0 else 1 end)
24 июн 15, 09:06    [17809484]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
VSVLAD,


нет.. нуль не смущает.. смущает, что в каком то варианте нужно делить на 3, в каком то на 2, а в каком то на 1


это из вот такого...

предприятие платит платежи помесячно..

одно предприятие платит в 1-й и 3-й месяц, другое - только во второй, третье - во второй и в третий..и так дальше..

так вот нужно находить по каждому предприятию СРЕДНЕЕ из этих трёх месяцев..

и соответственно смотреть в каких месяцах были платежи и придётся делить одному на 3, другому на 2, а кому-то на 1
24 июн 15, 09:08    [17809488]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
SELECT
 colonka4
,colonka5
,pay1
,pay2
,pay3
,(SELECT AVG(pay) FROM(VALUES(pay1),(pay2),(pay3))T(pay))[СРЕДНЕЕ по строке]
FROM T;
24 июн 15, 09:36    [17809627]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 610
iap
SELECT
 colonka4
,colonka5
,pay1
,pay2
,pay3
,(SELECT AVG(pay) FROM(VALUES(pay1),(pay2),(pay3))T(pay))[СРЕДНЕЕ по строке]
FROM T;


С учетом комментов ТС выше, то правильнее будет так, IMHO:
SELECT
 colonka4
,colonka5
,pay1
,pay2
,pay3
,(SELECT AVG(pay) FROM(VALUES(NULLIF(pay1, 0)), (NULLIF(pay2, 0)), (NULLIF(pay3, 0))) T(pay)) [СРЕДНЕЕ по строке]
FROM T;
24 июн 15, 10:13    [17809854]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Гузы
Guest
cold09
VSVLAD,
находить по каждому предприятию СРЕДНЕЕ из этих трёх месяцев..
и соответственно смотреть в каких месяцах были платежи и придётся делить одному на 3, другому на 2, а кому-то на 1

как-то не логично это.
Кстати. avg сработает как раз по этой логике (null значения исключает при подсчете агрегатов)
И причем здесь строка?
Таблица -то должна бы быть предприятие - дата - платеж.
А если это все екселе, так флаг в руки и в ем родимом и считать.
24 июн 15, 11:55    [17810525]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
cold09, у вас неверное понимание среднего.
Среднее - это сумма величин, разделенное на их количество. Точка.
Если у Вас существует различная периодичность, то Вы должны её учесть в своих данных.

Те данные, что Вы показали, невозможно правильно обработать запросом, т.к. они не соответствуют второй нормальной форме.
Разбейте Вашу таблицу на таблицы, в которых однозначно хранятся данные по периодичностям 1,2 и 3 месяца. В этом случае Вы легко подсчитаете среднее.
24 июн 15, 13:17    [17810984]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
Владислав Колосов, cold09.

Смысл нормализации не в создании трех таблиц из одной и трех запросов для вычисления средних значений.

Уже сказали, нужно создать примерно такую таблицу: предприятие - дата - платеж и всего один запрос для расчета средних значений.
24 июн 15, 13:56    [17811229]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Wlr-l,


declare @__TEMP table(ID int identity(1, 1), PredprName varchar(32), Pay1 money, Pay2 money, Pay3 money)

insert @__TEMP(PredprName, Pay1, Pay2, Pay3)
values('предприяние 1', 100, 200, 300),
      ('предприяние 2', 100,   0, 300),
      ('предприяние 3',   0,   0, 300),
      ('предприяние 4', NULL, NULL, NULL)


select t.*, tp.AVG_Pay
from @__TEMP t
cross apply(select IsNULL(avg(Pay), 0) AVG_Pay
			from (    select t.Pay1 Pay 
			union all select t.Pay2
			union all select t.Pay3
			/*......
			union all select t.PayN */) t
			where
					t.Pay > 0 /**/ ) tp
order by t.ID asc
24 июн 15, 14:09    [17811309]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Jaffar
Member

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

Все эти косяки и "строчки" - результат работы криворуких уродов, уродов "старой закалки", которые ничего не понимают в БД и делают такое вот говно, в котором нормальные люди потом должны разбираться и с этим мучиться.

И проблемы возрастают многократно, когда на это Говно завязано слишком много чтобы его трогать без вони.


Наберут студентов по объявлению.
24 июн 15, 14:15    [17811352]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Wlr-l
Member

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

Решение есть и для такого варианта спецов "старой закалки". Надеюсь, что "старая закалка" ни как не связана с возрастом.

Специально для Владислава Колосова последовательность столбцов pay1, pay2, pay3 нарушает не вторую, а первую нормальную форму, так как все данные должны быть представлены уникальными атрибутами.
24 июн 15, 14:33    [17811469]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Wlr-l, э, где там нарушение уникальности Вы узрели? Все значения атрибутов у автора атомарны, т.е. условия 1НФ выполнены.

нужно создать примерно такую таблицу: предприятие - дата - платеж и всего один запрос для расчета средних значений.


Не хочу открывать дискуссию, но это костыль в неверной архитектуре данных (однако, этим пользуется 99% баз). Dixi.
24 июн 15, 14:51    [17811599]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
iap,

К сожалению, функцию AVG сразу отметаем, ибо необходимо:
суммирование по строке Pay1,2,3 и эту сумму делить на 3, если во всех трёх месяцев есть суммы, если, например, только в: 1 и 2, 1 и 3, 2 и 3 - тогда делим на ДВА, ну а если только 1-ом, во 2-ом или в 3-ем месяце, то делим на ОДИН
25 июн 15, 00:20    [17813391]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
Диклевич Александр
iap
SELECT
 colonka4
,colonka5
,pay1
,pay2
,pay3
,(SELECT AVG(pay) FROM(VALUES(pay1),(pay2),(pay3))T(pay))[СРЕДНЕЕ по строке]
FROM T;


С учетом комментов ТС выше, то правильнее будет так, IMHO:
SELECT
 colonka4
,colonka5
,pay1
,pay2
,pay3
,(SELECT AVG(pay) FROM(VALUES(NULLIF(pay1, 0)), (NULLIF(pay2, 0)), (NULLIF(pay3, 0))) T(pay)) [СРЕДНЕЕ по строке]
FROM T;


К сожалению, функцию AVG сразу отметаем, ибо необходимо:
суммирование по строке Pay1,2,3 и эту сумму делить на 3, если во всех трёх месяцев есть суммы, если, например, только в: 1 и 2, 1 и 3, 2 и 3 - тогда делим на ДВА, ну а если только 1-ом, во 2-ом или в 3-ем месяце, то делим на ОДИН
25 июн 15, 00:22    [17813397]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
Владислав Колосов
cold09, у вас неверное понимание среднего.
Среднее - это сумма величин, разделенное на их количество. Точка.
Если у Вас существует различная периодичность, то Вы должны её учесть в своих данных.

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


Доброе утро!

На самом деле речь не идёт о математическом понимании среднего, а о необходимости жизни..

А пользователь говорит так: Необходимо сложить Pay1,2,3 и разделить эту сумму не тупо на ТРИ, а после некоторого анализа, а именно:
если во всех полях строки - Pay1,2,3 суммы ненулевые, тогда делим на 3
если в полях Pay1 и Pay2, Pay2 и Pay3, Pay1 и Pay3 одной строки ненулевые суммы, тогда делим на 2
если ненулевые суммы ТОЛЬКО в поле Pay1 или Pay2 или Pay3, тогда делим на 1

вот так как то..
25 июн 15, 00:31    [17813407]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
КЭП,
Guest
cold09
К сожалению, функцию AVG сразу отметаем, ибо необходимо:
суммирование по строке Pay1,2,3 и эту сумму делить на 3, если во всех трёх месяцев есть суммы, если, например, только в: 1 и 2, 1 и 3, 2 и 3 - тогда делим на ДВА, ну а если только 1-ом, во 2-ом или в 3-ем месяце, то делим на ОДИН
Не торопись, камчатка.
четай тут - 17810525 вторую строку ответа
и тут - 17809854 втыкай в функцию NULLIF
25 июн 15, 00:41    [17813419]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
Друзья!
думаю, что не раскрою особых секретов структуры БД...
надеюсь, Вы понимаете, что на самом деле приходится иметь дела не с голым вопросом: Среднее по строке..

А вот с таким запросом..

select
P_EMP_NAME "Наименование",
P_EMP_KPP "КПП",
P_EMP_INN "ИНН",
P_AVG_LIST_COUNT "Среднесписочная численность",

(SELECT
sum(r2.OPS_PAYMENTS_TOTAL)
FROM DB2INST.K_P1_R21_V14 r2
WHERE r2.P_ID = a.P_ID
group by P_ID) "Сумма выплат",

(SELECT
sum(r2.OPS_PAYMENTS_1M)
FROM DB2INST.K_P1_R21_V14 r2
WHERE r2.P_ID = a.P_ID
group by P_ID) "Сумма выплат 1 месяц",

(SELECT
sum(r2.OPS_PAYMENTS_2M)
FROM DB2INST.K_P1_R21_V14 r2
WHERE r2.P_ID = a.P_ID
group by P_ID) "Сумма выплат 2 месяц",

(SELECT
sum(r2.OPS_PAYMENTS_3M)
FROM DB2INST.K_P1_R21_V14 r2
WHERE r2.P_ID = a.P_ID
group by P_ID) "Сумма выплат 3 месяц",

case when P_AVG_LIST_COUNT=0 then (SELECT
sum(r2.OPS_PAYMENTS_3M+r2.OPS_PAYMENTS_2M+r2.OPS_PAYMENTS_1M)
FROM DB2INST.K_P1_R21_V14 r2
WHERE r2.P_ID = a.P_ID
group by P_ID)/3 else
(SELECT
sum(r2.OPS_PAYMENTS_3M+r2.OPS_PAYMENTS_2M+r2.OPS_PAYMENTS_1M)
FROM DB2INST.K_P1_R21_V14 r2
WHERE r2.P_ID = a.P_ID
group by P_ID)/3/P_AVG_LIST_COUNT end "Средняя з/п"


FROM DB2INST.K_P_COMMON a

.......

далее джойнты и прочее, прочее, прочее..

а интересует ТОЛЬКО правильность расчёта ПОЛЯ СРЕДНЯЯ З/П

как видите идёт ТУПАЯ делёжка на 3, а это НЕ ПРАВИЛЬНО !!!


поэтому и предложил для решения только выжимку, которую опубликовал в первом посте..

В итоге нужно сосредоточиться именно на том, как вычислить поле СРЕДНЯЯ З/П
25 июн 15, 01:30    [17813463]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
Гузы
cold09
VSVLAD,
находить по каждому предприятию СРЕДНЕЕ из этих трёх месяцев..
и соответственно смотреть в каких месяцах были платежи и придётся делить одному на 3, другому на 2, а кому-то на 1

как-то не логично это.
Кстати. avg сработает как раз по этой логике (null значения исключает при подсчете агрегатов)
И причем здесь строка?
Таблица -то должна бы быть предприятие - дата - платеж.
А если это все екселе, так флаг в руки и в ем родимом и считать.


Логично-не логично.. Логика тут не при чём... Тут база данных (не иксцель) и требование пользователя:

сложить суммы по строке и разделить их не тупо на 3, а с умыслом: либо на ТРИ, если во всех трёх полях ненулевые суммы, на ДВА, если ненулевые суммы только в любых ДВУХ месяцах и на ОДИН, если ненулевая сумма только в одном поле..
вот и всё..
25 июн 15, 01:38    [17813468]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7374
cold09,

Ну вам-же дали решение. Что не так?
with t as ( 
select 1 pay1, 2 pay2, 3 pay3
union all
select 1, null, 6
union all
select 5, 5, 51 
) 
select t.*, (SELECT AVG(pay) FROM(VALUES(pay1),(pay2),(pay3))T(pay)) from t
25 июн 15, 01:45    [17813472]     Ответить | Цитировать Сообщить модератору
 Re: Среднее по строке  [new]
cold09
Member

Откуда: Петропавловск-Камчатский
Сообщений: 54
Relic Hunter
cold09,

Ну вам-же дали решение. Что не так?
with t as ( 
select 1 pay1, 2 pay2, 3 pay3
union all
select 1, null, 6
union all
select 5, 5, 51 
) 
select t.*, (SELECT AVG(pay) FROM(VALUES(pay1),(pay2),(pay3))T(pay)) from t


сейчас проверю..
25 июн 15, 02:03    [17813485]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить