Гость
Map
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / суммировать пропорции, хранящиеся в таблице / 7 сообщений из 7, страница 1 из 1
17.07.2024, 21:45
    #40138684
kmvp
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
суммировать пропорции, хранящиеся в таблице
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CREATE TABLE dbo.tab1
(
  idrow INT NOT null IDENTITY,
  group1 VARCHAR(10) NOT NULL,
  c1 NUMERIC(10,2) NOT NULL,
  c2 NUMERIC(10,2) NOT NULL
)
GO

INSERT INTO tab1 (
  group1,  --группа
  c1,    --числитель (сверху)
  c2    --знаменатель (снизу)
)
VALUES
('1', 1, 2),
('1', 2, 1.4),
('1', 3, 5.2),
('2', 1, 0.6),
('2', 4, 0.5),
('2', 3, 0.9)
для хранения пропорций в таблице хранится числитель и знаменатель (для одно группы их может быть несколько)
нужно для каждой группы таких значений получить суммарные значения в том же виде: отдельно числитель и отдельно знаменатель

например, для группы 1 правильный ответ в виде результирующей строки будет таким:
group1 c1 c2
1 36.48 14.56
потому что:
1/2 + 2/1.4 + 3/5.2 = по правилам школьной математики приводим к общему знаменателю = 1 * 1.4 * 5.2 / (2 * 1.4 * 5.2) + 2 * 2 * 5.2 / 1.4 * 2 * 5.2 + 3 * 2 * 1.4 / 5.2 * 2 * 1.4 = (7.28 + 20.8 + 8.4) / 14.56 = 36.48 / 14.56
надеюсь не наделал ошибок при расчете

либо если, скажем, получились в результате значения, которые можно сократить, типа:
group1 c1 c2
1 30 10
то хорошо бы (хотя не обязательно) сократить их и получить:
group1 c1 c2
1 3 1
что то типа наименьший общий делитель (или наибольшее общее кратное, уже со школьных времен забыл)

как такое решить на sql?
я могу наколхозить некрасивый код и решить это, но может есть более удачный вариант?
не знаю, как умножить переменное количество значений, когда их может быть как тут всего 3 или много (вот суммировать потом их просто).

вот так пытался сделать, но это не правильно, т.к. нужно перемножить другие числа, дальше не кумекается:
хотелось бы избежать рекурсивного cte, но если никак, то ок...
Код: SQL
1.
2.
3.
4.
SELECT *, t1.c1 * t2.c1
FROM tab1 AS t1
JOIN tab1 AS t2 ON t1.group1 = t2.group1 AND t1.idrow <> t2.idrow
WHERE t1.group1 = '1'
...
Изменено: 17.07.2024, 21:52 - kmvp
Рейтинг: 0 / 0
18.07.2024, 13:19
    #40138686
SeaGate
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
суммировать пропорции, хранящиеся в таблице
kmvp [игнорируется] 

Я не работаю с MSSQL и делал бы через user-defined aggregate function. Однако, в простых случаях может хватить следующего:
https://onecompiler.com/sqlserver/42kgxgp38
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select group1, format(sum(c1_mult), 'N') c1, format(max(product_c2), 'N') as c2
  from (select group1,
               c1*exp(sum(log(c2)) over (partition by group1))/c2 c1_mult,
               exp(sum(log(c2)) over (partition by group1)) product_c2
          from (values
                  ('1', 1, 2),
                  ('1', 2, 1.4),
                  ('1', 3, 5.2),
                  ('2', 1, 0.6),
                  ('2', 4, 0.5),
                  ('2', 3, 0.9)) as tab1(group1, c1, c2)
        ) as t
 group by group1
 order by group1;
Код: SQL
1.
2.
3.
4.
group1 c1     c2
------ ------ --------
1      36.48  14.56
2      3.51   0.27
числа с плавающей точкой, поэтому есть нюансы с точностью. Касаемо сокращений 30/10 - 3/1, это сделать не сложно, потому не приводится. Нужно смотреть в сторону GCD. В Oracle такое (в MSSQL будет аналогично):
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
create or replace function gcd(a number, b number)
return number
as
begin
  if nvl(b,0) = 0
  then
    return a;
  else
    return gcd(b,mod(a,b));
  end if;
end;
/

SQL> select gcd(30, 10) from dual;
  GCD(30,10)
----------
        10
Соответственно, просто поделить на GCD на верхнем уровне.
...
Рейтинг: 1 / 0
Нравится: kmvp
18.07.2024, 13:41
    #40138687
kmvp
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
суммировать пропорции, хранящиеся в таблице
SeaGate [игнорируется] 

спасибо. а что такое nvl?

'nvl' is not a recognized built-in function name.
...
Рейтинг: 0 / 0
18.07.2024, 13:45
    #40138688
kmvp
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
суммировать пропорции, хранящиеся в таблице
SeaGate [игнорируется] 

первый запрос выглядит круто!
можете объяснить всю эту магию с логарифмами и экспонентами?
...
Изменено: 18.07.2024, 13:45 - kmvp
Рейтинг: 0 / 0
18.07.2024, 14:09
    #40138689
SeaGate
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
суммировать пропорции, хранящиеся в таблице
kmvp [игнорируется] 

NVL это функция Oracle как я указал в своём сообщении. Её можно переписать через CASE. Через рекурсию что-нибудь подобное можно, но нужно аккуратно подобрать тип данных, т.к. я не знаком с типами данных MSSQL:
https://onecompiler.com/sqlserver/42kh2mjmv
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
with tab1 as (
  select *
    from (values
            (1, '1', 1, 2),
            (2, '1', 2, 1.4),
            (3, '1', 3, 5.2),
            (4, '2', 1, 0.6),
            (5, '2', 4, 0.5),
            (6, '2', 3, 0.9)) as tab1(idrow, group1, c1, c2)),
  t(group1, idrow, c1, c2, lvl) as (
  select group1, min(idrow),
         (select cast(min(c1) as decimal(10, 4)) from tab1 where tab1.idrow=(select min(idrow) from tab1 where group1=tab1main.group1)),
         (select cast(min(c2) as decimal(10, 4)) from tab1 where tab1.idrow=(select min(idrow) from tab1 where group1=tab1main.group1)),
         1
    from tab1 tab1main
   group by group1
   union all
  select tab1.group1, tab1.idrow,
         cast(tab1.c1*t.c2 + tab1.c2*t.c1 as decimal(10, 4)),
         cast(tab1.c2 * t.c2 as decimal(10,4)), lvl + 1
    from tab1, t
   where tab1.group1 = t.group1
     and tab1.idrow > t.idrow)
select group1,
       (select min(c1) from t where group1=tmain.group1 and lvl=(select max(lvl) from t where group1=tmain.group1)) c1,
       (select min(c2) from t where group1=tmain.group1 and lvl=(select max(lvl) from t where group1=tmain.group1)) c2
  from t tmain
 group by group1;

group1 c1           c2
------ ------------ ------------
1           36.4800      14.5600
2            3.5100        .2700
как я понимаю в MSSQL нет keep first/last, что упрощает данный запрос в Oracle.
...
Рейтинг: 0 / 0
18.07.2024, 15:44
    #40138690
kmvp
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
суммировать пропорции, хранящиеся в таблице
SeaGate [игнорируется] 

а про экспоненту и логарифм не подскажете, как вы до этого додумались?
...
Рейтинг: 0 / 0
19.07.2024, 11:35
    #40138691
tomcat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
суммировать пропорции, хранящиеся в таблице
kmvp [игнорируется] 

ms sql не имеет группировочной функции перемножения, только сложение - SUM(c), поэтому нужно както заменить перемножение на сложение.
математика.- a^b * a^c = a^(b+c)
я бы сделал проще и нагляднее - старым добрым курсором (который с версии 2008 наконец-то залетал), но логарифмы-экспоненты это интереснее
...
Рейтинг: 1 / 0
Нравится: kmvp
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / суммировать пропорции, хранящиеся в таблице / 7 сообщений из 7, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]