ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / AVG для каждых n строк
16 сообщений из 16, страница 1 из 1
AVG для каждых n строк
    #38375174
Jarz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jarz
Гость
Допустим, имеется таблица:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
id | f1 |
---------
 2 | 1.97
 4 | 1.85
 5 | 1.62
 7 | 3.12
17 | 3.39
23 | 3.77


Необходимо получить таблицу, которая содержала бы средние значения поля f1 для каждых n строк.
Т.е. для n == 3 получилось бы:

Код: sql
1.
2.
3.
4.
avg_f1
------
1.813 // сумма f1 для строк 1-3
3.427 // сумма f1 для строк 4-6


Вопрос: как это реализвать наиболее изящно и с наименьшими извращениями?
...
Рейтинг: 0 / 0
22.08.2013, 18:15:49
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375183
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
Группировка по модулю номера строки.
...
Рейтинг: 0 / 0
22.08.2013, 18:40:17
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375191
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
Jarz,

ахинея. потому что логически данные строки никак между собой не связаны. "Номеров" у строк нет, поэтому при любом изменении порядка строк результат будет другим.
wadman предложил абстрактный вариант, с тем же успехом можно было бы группировать по любому другому критерию, которого тут не существует.
...
Рейтинг: 0 / 0
22.08.2013, 18:53:06
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375193
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
kdvс тем же успехом можно было бы группировать по любому другому критерию, которого тут не существует.
Есть предположение, что среднее берется по целой части f1, а не по номеру строки.
...
Рейтинг: 0 / 0
22.08.2013, 18:56:45
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375202
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Jarzкак это реализвать наиболее изящно и с наименьшими извращениями?в ХП сделайте выборку с order by id и присваивайте последовательные номера каждой выходящей из этой ХП строке (добавьте в ХП выходной параметр типа rownum int).
Далее делайте что-то типа такого:
Код: sql
1.
select p.rownum/3, avg(f01) from sp p group by 1
...
Рейтинг: 0 / 0
22.08.2013, 19:06:39
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375213
m_Sla
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
m_Sla
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
execute block
returns (average float)
as
declare the_sum float = 0;
declare the_count int = 0;
declare n int = 3;
begin
    for select f1 from new_table order by id
    into average
    do
    begin
        the_count = the_count + 1;
        the_sum = the_sum + average;

        if (the_count = n) then
        begin
            average = the_sum / n;
            the_count = 0;
            the_sum = 0;
            suspend;
        end
    end
end
...
Рейтинг: 0 / 0
22.08.2013, 19:34:20
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375431
Ованес Сусанян
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ованес Сусанян
Гость
В трёшке всё будет по-человечьи, одним запросом:
DDL:
 
Код: 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.
SQL> recreate table t(id int primary key, f01 numeric(12,2)); commit;
SQL> insert into t select row_number()over(), rand()*200 from rdb$types order by rand() rows 20; commit;
SQL> select * from t;

          ID                   F01
============ =====================
          80                105.10
          22                127.13
          25                 13.32
          90                152.33
         153                 13.59
         192                115.84
         157                 98.44
          76                 85.68
          96                140.37
         140                100.84
         188                118.09
         131                 24.57
          97                 13.10
         146                190.45
          10                106.27
         245                 93.77
         111                 73.75
          60                165.35
         115                105.16
         126                 19.12

Query + result:
 
Код: 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.
SQL> set explain on;
SQL> select id,f01,cast(avg(f01*1.000)over(partition by g) as numeric(12,3)) avg_3
CON> from(
CON>     select id,f01,(row_number()over(order by id)-1)/3 g
CON>     from t
CON> );

Select Expression
    -> Window
        -> Record Buffer
            -> Window
                -> Record Buffer
                    -> Table "T" Full Scan

          ID                   F01                 AVG_3
============ ===================== =====================
          25                 13.32                82.240
          10                106.27                82.240
          22                127.13                82.240
          76                 85.68               118.710
          80                105.10               118.710
          60                165.35               118.710
          97                 13.10               101.933
          96                140.37               101.933
          90                152.33               101.933
         126                 19.12                66.010
         111                 73.75                66.010
         115                105.16                66.010
         131                 24.57               105.287
         140                100.84               105.287
         146                190.45               105.287
         153                 13.59                76.707
         157                 98.44                76.707
         188                118.09                76.707
         245                 93.77               104.805
         192                115.84               104.805

Надо только дождаться, когда аналитич. ф-ции будут допилены в сторону подхвата индексов.
...
Рейтинг: 0 / 0
23.08.2013, 09:20:20
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375441
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Ованес Сусанян,

И какой же индекс ты собираешься использовать в данном запросе?
...
Рейтинг: 0 / 0
23.08.2013, 09:24:57
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375447
Ованес Сунанян
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ованес Сунанян
Гость
Симонов ДенисИ какой же индекс ты собираешься использовать в данном запросе?row_number()over( order by id )-1)
(вместо натурала + sort'a - просто навигацией идти по нему)
...
Рейтинг: 0 / 0
23.08.2013, 09:27:51
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375450
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Ованес Сунанян,

Выигрыша не будет. Навигация по индексу будет эффективней только в том случае если он читается не полностью. В данном же случае один фиг сканируется вся таблица
...
Рейтинг: 0 / 0
23.08.2013, 09:32:21
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375466
Ованес Сунанян
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ованес Сунанян
Гость
Симонов ДенисВыигрыша не будет. Навигация по индексу будет эффективней только в том случае если он читается не полностью. В данном же случае один фиг сканируется вся таблицаА я не про выигрыш, а про проигрыш. Оптимизатор сейчас не подхватывает индекс (или вообще блокирует его использование).
 
sample
Код: 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.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
SQL> recreate table t(id int primary key, f01 numeric(12,2)); commit;
SQL> insert into t select row_number()over(), rand()*200 from rdb$types,rdb$types  order by rand() rows 5
0000; commit;
SQL> select count(*) from t;

                COUNT
=====================
                50000

SQL> out nul; set stat on; set  explain on;

 -- var_1: 
SQL> select * from t order by id rows 1;

Select Expression
    -> First N Records
        -> Table "T" Access By ID
            -> Index "RDB$PRIMARY13" Scan
Current memory = 291345128
Delta memory = 11672
Max memory = 297667344
Elapsed time= 0.00 sec
Buffers = 65000
Reads = 0
Writes 0
Fetches = 13


SQL> select row_number()over(order by id),t.* from t order by id rows 1;

Select Expression
    -> First N Records
        ->  Sort
             -> Window
                -> Record Buffer
                    -> Table "T" Full Scan
Current memory = 291489136
Delta memory = 144008
Max memory = 307223136
Elapsed time=  0.34 sec 
Buffers = 65000
Reads = 0
Writes 0
Fetches = 101337


 -- var_2: 
SQL> select * from t order by id;

Select Expression
    -> Table "T" Access By ID
        -> Index "RDB$PRIMARY13" Scan
Current memory = 291513096
Delta memory = 23960
Max memory = 307223136
Elapsed time= 0.88 sec
Buffers = 65000
Reads = 0
Writes 0
Fetches = 150102
SQL> select row_number()over(order by id),t.* from t order by id;

Select Expression
    -> Sort
        -> Window
            -> Record Buffer
                -> Table "T" Full Scan
Current memory = 291488720
Delta memory = -24376
Max memory = 307223136
Elapsed time= 1.29 sec
Buffers = 65000
Reads = 0
Writes 0
Fetches = 101337
...
Рейтинг: 0 / 0
23.08.2013, 09:41:00
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375479
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Ованес Сунанян,

Про это был уже разговор с Таблоидом. Ты лучше покажи в какой СУБД этот индекс умеет использоваться.
...
Рейтинг: 0 / 0
23.08.2013, 09:59:06
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375484
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов ДенисПро это был уже разговор с Таблоидом. Ты лучше покажи в какой СУБД этот индекс умеет использоваться.Индекс стопудово используется в ms sql и орацле (причем, и для прямой и для обратной навигации - например, когда в одном запросе есть over(order by id) и over(order by id DESC)).
У тебя есть они там, в виде XE например ?
PS. Все вариации фамилии "Сусанин" - это я и есть, просто в малозначащих топегах не хочу засвечиваться, искать "себя" потом неудобно :-)
...
Рейтинг: 0 / 0
23.08.2013, 10:04:35
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375494
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,
мы с тобой прошлый раз выяснили, что Оракл вообще не правильно работает в данном случае. Про MS SQL надо посмотреть. Подождём пока dimitr не сделает более расширенный план для оконных функций (чтобы он ничего не скрывал). Кстати в fbdevel недавно было обсуждение в каком виде этот план выводить.
...
Рейтинг: 0 / 0
23.08.2013, 10:14:48
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375734
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Симонов ДенисОракл вообще не правильно работает в данном случае.Oracle работает на случае выборки с row_number()over(order by <indexed_field>) ОК, индекс как раз подхватывается.
 
Код: 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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
set feed off
create table tx2(x int primary key);
insert into tx2 select level-1 k from dual connect by level+1 <= 50000;
set autot trace
select * from( select x, row_number()over(order by x) from tx2 ) where rownum<10;

9 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1278314822

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     9 |   234 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |                |       |       |            |          |
|   2 |   VIEW             |                | 47567 |  1207K|     2   (0)| 00:00:01 |
|   3 |    WINDOW NOSORT   |                | 47567 |   603K|     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN| SYS_C001482113 | 47567 |   603K|     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
        508  bytes sent via SQL*Net to client
        334  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

(и при указании over(order by x DESC) - то же будет подхвачен; "но это уже совсем другая история")
...
Рейтинг: 0 / 0
23.08.2013, 12:59:38
| Ответить | Цитировать | Написать  
AVG для каждых n строк
    #38375749
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
Таблоид,

Таблоид
Код: sql
1.
select * from( select x, row_number()over(order by x) from tx2 ) where rownum<10;



Так уже да правильно. Вообщем ждём от Дмитрия полного вывода плана, а потом уже разбираемся.
...
Рейтинг: 0 / 0
23.08.2013, 13:08:06
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / AVG для каждых n строк
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&fpage=115&tid=1564378]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 186ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 70ms
get tp. blocked users:
 2ms
others: 223ms
total:  522ms
созд. / загр.: 522ms
x
x
Закрыть


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