ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Аналитика в Pg
9 сообщений из 34, страница 2 из 2
  2  все
Аналитика в Pg
    #35256973
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
bege231Округление, на мой взгляд как раз наименее значимый пункт решения задачи.на мой взгляд, без округления в этой задаче не будет и ошибки, не будет и невязки, не будет и самой задачи.

Код: plaintext
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.
create table t1 (
        id integer,
        summa integer,
        gruppa integer,
        oplata integer
);

insert into t1 values
        (  1 ,  347 ,  1 ,  351  ),
        (  2 ,  347 ,  1 ,  376  ),
        (  3 ,  347 ,  1 ,  100  ),
        (  4 ,  131 ,  2 ,  123  ),
        (  5 ,  131 ,  2 ,  324  ),
        (  6 ,  131 ,  2 ,  324  )
;

select t1.*, summa*oplata/sum_oplata+
coalesce(summa-sum_value, 0 ) as value from t1 join (
 select gruppa, sum(oplata) as sum_oplata from t1 group by gruppa
) as a using (gruppa) natural left join (
 select min(id) as id, sum_value from t1 natural join (
  select gruppa, max(oplata) as oplata,
  sum(summa*oplata/sum_oplata) as sum_value from t1 join (
   select gruppa, sum(oplata) as sum_oplata from t1 group by gruppa
  ) as a using (gruppa) group by gruppa
 ) as b group by gruppa, oplata, sum_value
) as c
;

drop table t1;
...
Рейтинг: 0 / 0
15.04.2008, 17:37
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35257624
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
bege231А тоже работает! Хотя и медленее чем join .Ну, для суммы по группе лучше, конечно же, использовать JOIN основной таблицы с агрегированным подзапросом. А скалярный подзапрос в SELECT-листе нужен для расчета накопительной суммы по группе с учетом сортировки. Типа аналог оракловой аналитической суммы: sum() over(partition by ... order by ...)
...
Рейтинг: 0 / 0
16.04.2008, 05:29
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35257670
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
bege231
Код: plaintext
1.
2.
3.
4.
сумма  группа  оплата   value    по_группе  невязка + на максимальную   |     ИТОГ |       
--------------------------------------------------------------------------------------------------------------------------
 3 . 47         1        351      1 . 39      |          |     	 |       1 , 39    |
 3 . 47         1        376      1 . 56      |     3 . 37   |     + 0 . 10  |       1 , 66    |
 3 . 47         1        100      0 . 42      |          |           |       0 , 42    |

1- value надо рассчитать в процентном соотношении - 3.47 * ( 351/(351+376+100))
2 - т.о. по группе получается общая сумма 3,37 (должно быть 3,47) , невязка - 0.10
3 - невязку прибавляем к максимальной ( max =376 , прибавляем .10 к 1.56 )
На твоих тестовых данных построил табличку T1:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select * from t1

Query finished, retrieving results...

ID   группа   оплата   сумма
--   ------   ------   -----
  1          1        351      3 . 47 
  2          1        376      3 . 47 
  3          1        100      3 . 47 
  4          2        123      1 . 31 
  5          2        324      1 . 31 

 5  row(s) retrieved
где:
ID - уникальный идентификатор записи в таблице;
остальные поля соответствуют твоим исходным данным:
группа - номер группы;
оплата - сумма оплаты;
сумма - сумма для расчета процентного соотношения.

Тогда можно написать запрос, который с учетом округления будет находить VALUE, расхождение суммы найденного VALUE с заданной суммой, а также помещать невязку в строку с максимальной суммой оплаты:
Код: plaintext
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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
select t1.*,
       v2_t1."расчетная сумма",
       v2_t1.delta as "невязка сумм по группе",
       round(t1."сумма" * round(t1."оплата" / v2_t1."оплата по группе", 2 ), 2 ) as "расчетное VALUE",
       case 
         when (
                select count( 1 )
                  from t1 t0
                 where t0."группа" = t1."группа"
                   and (t0."оплата" > t1."оплата"
                        or
                        t0."оплата" = t1."оплата"
                        and t0.id >= t1.id
                       )
              ) =  1 
           then round(t1."сумма" * round(t1."оплата" / v2_t1."оплата по группе", 2 ), 2 ) + v2_t1.delta
         else round(t1."сумма" * round(t1."оплата" / v2_t1."оплата по группе", 2 ), 2 )
       end as "скорректированное VALUE"
  from t1
  join (
         select t1."группа", max(v1_t1."оплата по группе") as "оплата по группе",
                sum(round(t1."сумма" * round(t1."оплата" / v1_t1."оплата по группе", 2 ), 2 )) as "расчетная сумма",
                max(t1."сумма") - sum(round(t1."сумма" * round(t1."оплата" / v1_t1."оплата по группе", 2 ), 2 )) as delta
           from t1
           join (
                  select sum("оплата") as "оплата по группе",
                         "группа"
                    from t1
                   group by "группа" 
                ) v1_t1
             on t1."группа" = v1_t1."группа"
          group by t1."группа"
       ) v2_t1
    on t1."группа" = v2_t1."группа"

Query finished, retrieving results...

ID   группа   оплата   сумма   расчетная сумма   невязка сумм по группе   расчетное VALUE   скорректированное VALUE
--   ------   ------   -----   ---------------   ----------------------   ---------------   -----------------------
  1          1        351      3 . 47                3 . 44                       0 . 03                1 . 46                        1 . 46 
  2          1        376      3 . 47                3 . 44                       0 . 03                1 . 56                        1 . 59 
  3          1        100      3 . 47                3 . 44                       0 . 03                0 . 42                        0 . 42 
  4          2        123      1 . 31                1 . 31                          0                0 . 37                        0 . 37 
  5          2        324      1 . 31                1 . 31                          0                0 . 94                        0 . 94 

 5  row(s) retrieved
...
Рейтинг: 0 / 0
16.04.2008, 08:15
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35258168
bege231
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bege231
Гость
LeXa NalBatна мой взгляд, без округления в этой задаче не будет и ошибки, не будет и невязки, не будет и самой задачи.
Поля "сумма" и "оплата" - это в деньгах (руб) , а 10 знаков после запятой - это уже не деньги. Потому и приходится округлять, и считать невязки.

Бабичев Сергей
select count(1)
from t1 t0
where t0."группа" = t1."группа"
and (t0."оплата" > t1."оплата"
or
t0."оплата" = t1."оплата"
and t0.id >= t1.id
)
Как раз то что искала! Как-то так сразу до такого и не додумаешься. Теперь буду пользоваться!
Спасибо огромное!
...
Рейтинг: 0 / 0
16.04.2008, 11:17
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35258257
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
bege231 Бабичев Сергей
select count(1)
from t1 t0
where t0."группа" = t1."группа"
and (t0."оплата" > t1."оплата"
or
t0."оплата" = t1."оплата"
and t0.id >= t1.id
)
Как раз то что искала! Как-то так сразу до такого и не додумаешься. Теперь буду пользоваться!
Спасибо огромное!
Ну, ты, если что, сразу говори, какую из аналитических функций оракла тебе нужно переложить на ANSI-SQL-99.
Приведенный кусок кода - это аналог аналитического ROW_NUMBER() over(partition by "группа" order by "оплата" desc, id desc) :)
Фактически, любую другую аналитическую функцию можно реализовать при помощи такого же подхода. По крайней мере, ранжирующие функции (rank, dense_rank, row_number), функции получения предыдущей/последующей записи (lead/lag), скользящая сумма (sum() over()) реализуются в легкую. Вот только эффективность запроса при этом значительно снизится... :(
...
Рейтинг: 0 / 0
16.04.2008, 11:41
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35259853
Ленивец
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ленивец
Участник
Вот и монстр получился!
Думаю следующий вопрос автора будет "как мне всё это счастье теперь оптимизировать??".
Потому что если сделать план подобных запросов ох не радует!
Индексы помогут лишь отчасти.
...
Рейтинг: 0 / 0
16.04.2008, 17:24
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35260334
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
Что-то вспомнился старый анекдот, когда психов искали, раздавая им стакан и наперсток и проверяя, как они будут из ванны с водой воду выливать. Для аналитики есть как специальные библиотеки в разных языках, так и специализированные языки. Например, postgresql-8.1-plr поможет решить и более сложные задачи. В оракле просто нет таких возможностей, потому создают доп. функции, которые являются лишь жалким подобием возможностей хорошего пакета мат. статистики.
...
Рейтинг: 0 / 0
16.04.2008, 22:12
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35260336
MBG
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MBG
Гость
Если кто не понял про анекдот, подсказываю - пробку из ванны надо было выдернуть...
...
Рейтинг: 0 / 0
16.04.2008, 22:14
| Ответить | Цитировать | Написать  
Аналитика в Pg
    #35260401
Shweik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shweik
Участник
Склонность пытаться решить любой вопрос чистым T-SQL имхо распространненый синдром.
Думаю тему можно закрывать до следующего пациента с вопросом как по-постгресовски сделать что-то оракловское и чтоб не кодить никаких ХП на Си или Перле.
Короче - тема раскрыта осознана и закрыта. 8-D
...
Рейтинг: 0 / 0
16.04.2008, 23:45
| Ответить | Цитировать | Написать  
9 сообщений из 34, страница 2 из 2
  2  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Аналитика в Pg
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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