ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Sybase ASA, ASE, IQ [игнор отключен] [закрыт для гостей] / Запрос с проверкой суммы по столбцу в результате
25 сообщений из 29, страница 1 из 2
  1  все
Запрос с проверкой суммы по столбцу в результате
    #34226122
Leviathan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leviathan
Гость
Здравствуйте!

Предположим, запрос такой:
SELECT
worker, SUM(ordersum) as worker_sum
FROM
workeroder
GROUP BY worker
ORDER BY worker_sum;

Хотелось бы ограничить объем выборки таким образом, чтобы сумма в столбце worker_sum не превышала некоторую константу.

Как это лучше всего сделать?
Спасибо!

(ASA 9)
...
Рейтинг: 0 / 0
26.12.2006, 19:08
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34226158
Фотография komrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
komrad
Участник
LeviathanЗдравствуйте!

Предположим, запрос такой:
SELECT
worker, SUM(ordersum) as worker_sum
FROM
workeroder
GROUP BY worker
ORDER BY worker_sum;

Хотелось бы ограничить объем выборки таким образом, чтобы сумма в столбце worker_sum не превышала некоторую константу.

Как это лучше всего сделать?
Спасибо!

(ASA 9)


попробуй так:

Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT
worker, SUM(ordersum) as worker_sum
FROM
#workorder
GROUP BY worker
having SUM(ordersum)  <= КОНСТАНТА
ORDER BY worker_sum
...
Рейтинг: 0 / 0
26.12.2006, 19:27
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34226472
Фотография A.K.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.K.
Участник
Замечу, что это стандартнейшая задача, одинаково решающаяся во всех SQL'ях.
Называется: наложение условия на результат группировки.
...
Рейтинг: 0 / 0
26.12.2006, 23:25
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34226495
Leviathan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leviathan
Гость
komrad, A.K.,
если я правильно понял, вы предлагаете наложить условие на результат каждой группировки. Мне же нужно наложить условие на совокупность таких группировок. Я плохо выразился: надо ограничить сумму значений в столбце worker_sum _по всем строкам результата_.

komrad, ваш вариант ограничит сумму по КАЖДОМУ воркеру. Мне же надо ограничить сумму по столбцу.

Пример:
|Воркер|Сумма|
|1| 100|
|2| 200|
|3| 300|

Ограничение было 600 - дальше не селектится.

A.K., вполне допускаю что это стандартнейшая задача. Но до меня не доходит, опыта не хватает... хотя чувствую решение лежит где-то на поверхности.
...
Рейтинг: 0 / 0
27.12.2006, 00:01
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34226529
antand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antand
Гость
Подобная задача обсуждалась, поиском посмотрите.
Проще всего сделать хранимую процедуру, в ней курсором пройтись по запросу предложенному kompad, подсчитывая сумму, сравниваем с константой и сохраняя во временную таблицу будущий результат.
Результат - select из временной таблицы.
А "константу" можно в процедуру параметром передавать для красоты.
...
Рейтинг: 0 / 0
27.12.2006, 01:03
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34226543
antand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antand
Гость
Еще можно попробовать OLAP функции в 9 версии.
В BOL в разделе Window functions есть даже пример подсчета нарастающего итога в поле.
Остается видимо наложить ограничение на этот итог(вашу константу).
Хотя не уверен, что можно так сделать. Надо попробовать на досуге.
...
Рейтинг: 0 / 0
27.12.2006, 01:36
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34226756
Leviathan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leviathan
Гость
antand, поиском пытался :) Но видимо у меня какие-то другие ключевые слова ассоциируются с такой проблемой.

Спасибо, попробую курсором. Думал об этом, но все-таки надеялся что можно как-то сделать одним запросом.
...
Рейтинг: 0 / 0
27.12.2006, 09:04
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34226944
Фотография A.K.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.K.
Участник
Leviathan ваш вариант ограничит сумму по КАЖДОМУ воркеру. Мне же надо ограничить сумму по столбцу.

Пример:
|Воркер|Сумма|
|1| 100|
|2| 200|
|3| 300|

Ограничение было 600 - дальше не селектится.

В такой постановке задача действительно не решается одним SELECT'ом, по крайней мере в 8-ке, где нет аналитических функций. Пишите хранимую процедуру, возвращающую рекордсет.
...
Рейтинг: 0 / 0
27.12.2006, 10:18
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229347
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
A.K.В такой постановке задача действительно не решается одним SELECT'ом, по крайней мере в 8-ке, где нет аналитических функций.Ещё как решается. И в один селект, и без аналитических функций. Вот только нужно ли оно, такое решение, которое ставит сервер на колени? Ну разве только для оттачивания мастерства в написании хитровыгнутых запросов...
...
Рейтинг: 0 / 0
28.12.2006, 04:30
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229680
Volokola
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Volokola
Гость
В 9-ке действительно лучше использовать OLAP функцию: примерно так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Select * From 
    (
    SELECT worker
        ,SUM(ordersum) as worker_sum
        ,SUM(ordersum) OVER (Rows Between Unbounded Preceding and Current Row) as all_SUM
    FROM workeroder
    GROUP BY worker
    ORDER BY worker_sum
    ) as a
Where all_SUM <= Константа

авторВот только нужно ли оно, такое решение, которое ставит сервер на колени? Ну разве только для оттачивания мастерства в написании хитровыгнутых запросов...
ASA 9 OLAP функции давольно таки грамотно выполняет. Да и "обычные хитроумные" запросы делает спокойно (если конечно грамотно написаны).
...
Рейтинг: 0 / 0
28.12.2006, 10:29
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229765
Фотография A.K.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.K.
Участник
Бабичев СергейЕщё как решается. И в один селект, и без аналитических функций. Вот только нужно ли оно, такое решение, которое ставит сервер на колени? Ну разве только для оттачивания мастерства в написании хитровыгнутых запросов...
Согласен, что решается. Только как раз мастерства от написания такого запроса много не прибавится Новички в SQL, к примеру, очень любят городить трехэтажные подзапросы там где достаточно простого JOIN'а. Здесь с вычислением суммы нарастающим итогом будет примерно то же самое - решение с суперизбыточным совокупным объемом выборок.

Imho мастерство - это в данном случае умение решить задачу оптимальным образом.
...
Рейтинг: 0 / 0
28.12.2006, 10:53
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229769
Leviathan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leviathan
Гость
Сергей,
вы имеете в виду решение в следующем стиле: отбирать в правильном порядке, подсчитывая на каждом шага сумму по уже отобранным значениям? Что-то такое вертится в голове, но опыт не позволяет быстро реализовать... Да, и мрачновато выглядит перспектива с каждой новой строкой пересчитывать всю сумму.

Volokola, большое спасибо, попробую применить!
...
Рейтинг: 0 / 0
28.12.2006, 10:55
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229815
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
LeviathanСергей,
вы имеете в виду решение в следующем стиле: отбирать в правильном порядке, подсчитывая на каждом шага сумму по уже отобранным значениям? Что-то такое вертится в голове, но опыт не позволяет быстро реализовать... Да, и мрачновато выглядит перспектива с каждой новой строкой пересчитывать всю сумму.

Volokola, большое спасибо, попробую применить!Да, примерно это я и имел ввиду.
Ну или если пояснить на примере, то вот так всё будет выглядеть:

Пусть есть таблица TEST, содержащая в себе записи вида:
workerordersum12013015021102903140316047041305500

Внимательно смотрим на результаты работы вот такого запроса:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
select worker, 
       sum(ordersum) worker_summ,
       (
         select sum(ordersum)
           from test t1
          where t1.worker <= t0.worker
       ) as slide_summ
  from test t0
 group by worker

Query finished, retrieving results...

WORKER   WORKER_SUMM   SLIDE_SUMM
------   -----------   ----------
      1             100            100  
      2             200            300  
      3             300            600  
      4             200            800  
      5             500           1300  

 5  row(s) retrieved

Теперь достаточно перенести подзапрос из SELECT-листа во фразу HAVING и получим то, что нужно:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select worker, 
       sum(ordersum) worker_summ
  from test t0
 group by worker
having (
         select sum(ordersum)
           from test t1
          where t1.worker <= t0.worker
       ) <=  600 

Query finished, retrieving results...

WORKER   WORKER_SUMM
------   -----------
      1             100 
      2             200 
      3             300 

 3  row(s) retrieved
...
Рейтинг: 0 / 0
28.12.2006, 11:05
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229878
Фотография A.K.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.K.
Участник
и для таблицы с миллионом воркеров получим миллион сканов таблицы подзапросом.
даже если сканы по индексу - результата не скоро дождетесь.
...
Рейтинг: 0 / 0
28.12.2006, 11:20
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229931
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
A.K.и для таблицы с миллионом воркеров получим миллион сканов таблицы подзапросом.
даже если сканы по индексу - результата не скоро дождетесь.А я сразу сказал, что такое решение поставит сервер на колени...
Запрос же был приведен исключительно для того, чтобы показать ошибочность высказывания:
A.K.В такой постановке задача действительно не решается одним SELECT'ом, по крайней мере в 8-ке, где нет аналитических функций.

Как видишь - задача решилась в один select-statement...
...
Рейтинг: 0 / 0
28.12.2006, 11:30
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229948
antand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antand
Гость
To Бабичев Сергей
Это все хорошо, но кто Вам сказал что t1.worker <= t0.worker
Посмотрите на начальный запрос Автора ORDER BY worker_sum
...
Рейтинг: 0 / 0
28.12.2006, 11:33
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229960
Фотография A.K.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.K.
Участник
Бабичев СергейКак видишь - задача решилась в один select-statement...
практическая ценность этого решения = 0, поэтому я такие решения даже не рассматриваю.
...
Рейтинг: 0 / 0
28.12.2006, 11:36
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229981
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
antandTo Бабичев Сергей
Это все хорошо, но кто Вам сказал что t1.worker <= t0.worker
Посмотрите на начальный запрос Автора ORDER BY worker_sumASA 9 перестала понимать derived-таблицы?
...
Рейтинг: 0 / 0
28.12.2006, 11:41
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34229995
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
A.K. Бабичев СергейКак видишь - задача решилась в один select-statement...
практическая ценность этого решения = 0, поэтому я такие решения даже не рассматриваю.Практическая ценность этого решения может и ноль. Но решать это автору вопроса ;)
Хотя, я и сам обоими руками за "убиение" тех, кто такие запросы пишет в промышленно эксплуатируемых системах.
Важен был сам факт - любое решение с аналитическими функциями можно написать и без них. Другое дело - насколько это понизит производительность сервера...

З.Ы.
Всё сугубо моё IMHO, никому ничего не навязываю.
...
Рейтинг: 0 / 0
28.12.2006, 11:44
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34230001
antand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antand
Гость
Не в этом дело.
Если я правильно понял, автору нужно сначала просуммировать с группировкой все записи, потом отсортировать их по сумме и только потом вытащить записи, подсчитывая нарастающий итог в поле и ограничивая выборку по нарастающему итогу.
В вашем случае сработает т.к. у Вас сортировки нет.
...
Рейтинг: 0 / 0
28.12.2006, 11:46
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34230017
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
antandНе в этом дело.
Если я правильно понял, автору нужно сначала просуммировать с группировкой все записи, потом отсортировать их по сумме и только потом вытащить записи, подсчитывая нарастающий итог в поле и ограничивая выборку по нарастающему итогу.
В вашем случае сработает т.к. у Вас сортировки нет.Я понял твой вопрос и в первый раз. Поэтому и спросил - что останавливает использовать деривед-таблицу?

З.Ы.
Правда запрос станет ещё более неподъемным для сервера...
...
Рейтинг: 0 / 0
28.12.2006, 11:50
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34230020
Фотография A.K.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
A.K.
Участник
antandНе в этом дело.
Если я правильно понял, автору нужно сначала просуммировать с группировкой все записи, потом отсортировать их по сумме и только потом вытащить записи, подсчитывая нарастающий итог в поле и ограничивая выборку по нарастающему итогу.
В вашем случае сработает т.к. у Вас сортировки нет.
И в этом тоже. Не знаю как в девятке, но в восьмерке AFAIR нельзя сортировать и нумеровать строки в результатах поздапросов.
...
Рейтинг: 0 / 0
28.12.2006, 11:52
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34230079
Бабичев Сергей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Бабичев Сергей
Участник
A.K. antandНе в этом дело.
Если я правильно понял, автору нужно сначала просуммировать с группировкой все записи, потом отсортировать их по сумме и только потом вытащить записи, подсчитывая нарастающий итог в поле и ограничивая выборку по нарастающему итогу.
В вашем случае сработает т.к. у Вас сортировки нет.
И в этом тоже. Не знаю как в девятке, но в восьмерке AFAIR нельзя сортировать и нумеровать строки в результатах поздапросов.Ладно, парни. Поставлю свой сервер ещё раз на колени...

Пусть есть таблица TEST с такими данными:
WORKERORDERSUM 0500 1500 270 2130 2200 3140 3160 4110 490 520 530 550

Внимательно смотрим на результат работы запроса:
Код: 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.
select worker, 
       ordersum worker_summ,
       (
         select sum(ordersum)
           from (
                  select worker, 
                         sum(ordersum) ordersum
                    from test
                   group by worker
                ) t1
          where t1.ordersum < t0.ordersum
                or
                t1.worker <= t0.worker 
            and t1.ordersum <= t0.ordersum
       ) slide_summ
  from (
         select worker, 
                sum(ordersum) ordersum
           from test
          group by worker
       ) t0
order by worker_summ

Query finished, retrieving results...

WORKER   WORKER_SUMM   SLIDE_SUMM
------   -----------   ----------
      5             100            100 
      4             200            300 
      3             300            600 
      2             400           1000 
      0             500           1500 
      1             500           2000 

 6  row(s) retrieved

З.Ы.
2 автор запроса и ALL:
Никогда не используйте такие запросы в промышленных системах!!!
...
Рейтинг: 0 / 0
28.12.2006, 12:12
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34230087
antand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
antand
Гость
Бабичев СергейЯ понял твой вопрос и в первый раз. Поэтому и спросил - что останавливает использовать деривед-таблицу?
З.Ы.
Правда запрос станет ещё более неподъемным для сервера...
Что-то я не пойму вашу идею. Можем на меня праздники сказываются...
При чем тут деривед-таблица?
Сортировка нужна сначала перед подсчетом нарастающего итога.
Вот итог вашего первого запроса

WORKER WORKER_SUMM SLIDE_SUMM
------ ----------- ----------
1 100 100
2 200 300
3 300 600
4 200 800
5 500 1300

Он неправилен для автора
Нужно
1 100 100
2 200 300
4 200 500
3 300 800
5 500 1300
...
Рейтинг: 0 / 0
28.12.2006, 12:14
| Ответить | Цитировать | Написать  
Запрос с проверкой суммы по столбцу в результате
    #34230093
Leviathan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leviathan
Гость
Всем огромное спасибо за участие, особенно Сергею!
Было очень интересно увидеть решение в один запрос. Когда я устраивался на работу, была поставлена задача сделать подобные вещи ОДНИМ запросом. Ну и поломал же я мозг!

Все же такие запросы имеют несомненную ценность как головоломки!
...
Рейтинг: 0 / 0
28.12.2006, 12:17
| Ответить | Цитировать | Написать  
25 сообщений из 29, страница 1 из 2
  1  все
Форумы / Sybase ASA, ASE, IQ [игнор отключен] [закрыт для гостей] / Запрос с проверкой суммы по столбцу в результате
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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