Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

Как указать сортровку при апдейте в Оракле ?

Страницы: 1 2 >
ANB © (2005-05-17 12:32:00)
Делаю апдейт :
update Step S
set S.Num = rownum * 10
where S.Test_ID = :Test_ID
Все замечательно работало, пока не повесил на Step еще один индекс. Съехала сортировка и теперь не так идет перенумерация. В селекте можно ордер бай добавить. А как здесь ? Или без курсора никак ?

ANB (2005-05-18 13:05:00) [1]

> evvcom © (18.05.05 12:14) [38]
- спасибо. Теперь понятней.

evvcom (2005-05-18 12:14:00) [2]
If RANK is specified, the rank of a row is defined as 1 plus the number of rows that strictly precede the row. Thus, if two or more rows are not distinct with respect to the ordering, then there will be one or more gaps in the sequential rank numbering.

If DENSE_RANK 36 is specified, the rank of a row is defined as 1 plus the number of rows preceding that are distinct with respect to the ordering. Therefore, there will be no gaps in the sequential rank numbering.

The ROW_NUMBER 37 function computes the sequential row number of the row within the window defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in arbitrary order as returned by the subselect (not according to any ORDER BY clause in the select-statement).

The data type of the result of RANK, DENSE_RANK or ROW_NUMBER is BIGINT. The result cannot be null.

PARTITION BY (partitioning-expression,...)
Defines the partition within which the function is applied. A partitioning-expression is an expression used in defining the partitioning of the result set. Each column-name referenced in a partitioning-expression must unambiguously reference a result set column of the OLAP function subselect statement (SQLSTATE 42702 or 42703). The length of each partitioning-expression must not be more than 255 bytes (SQLSTATE 42907). A partitioning-expression cannot include a scalar-fullselect (SQLSTATE 42822) or any function that is not deterministic or has an external action (SQLSTATE 42845).

ORDER BY (sort-key-expression,...)
Defines the ordering of rows within a partition that determine the value of the OLAP function or the meaning of the ROW values in the window-aggregation-group-clause (it does not define the ordering of the query result set). A sort-key-expression is an expression used in defining the ordering of the rows within a window partition. Each column-name referenced in a sort-key-expression must unambiguously reference a column of the result set of the subselect including the OLAP function (SQLSTATE 42702 or 42703). The length of each sort-key-expression must not be more than 255 bytes (SQLSTATE 42907). A sort-key-expression cannot include a scalar fullselect (SQLSTATE 42822) or any function that is not deterministic or has an external action (SQLSTATE 42845). This clause is required for the RANK and DENSE_RANK functions (SQLSTATE 42601).

ASC
Uses the values of the sort-key-expression in ascending order. Null values are considered last in the order.

DESC
Uses the values of the sort-key-expression in descending order. Null values are considered first in the order.

window-aggregation-group-clause
The aggregation group of a row R is a set of rows, defined relative to R in the ordering of the rows of R's partition. This clause specifies the aggregation group.

ROWS
Indicates the aggregation group is defined by counting rows.

RANGE
Indicates the aggregation group is defined by an offset from a sort key.

group-start
Specifies the starting point for the aggregation group. The aggregation group end is the current row. Specification of the group-start clause is equivalent to a group-between clause of the form "BETWEEN group-start AND CURRENT ROW".

group-between
Specifies the aggregation group start and end based on either ROWS or RANGE.

UNBOUNDED PRECEDING
Includes the entire partition preceding the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.

UNBOUNDED FOLLOWING
Includes the entire partition following the current row. This can be specified with either ROWS or RANGE. Also, this can be specified with multiple sort-key-expressions in the window-order-clause.

CURRENT ROW
Specifies the start or end of the aggregation group as the current row. This clause cannot be specified in group-bound2 if group-bound1 specifies value FOLLOWING.

value PRECEDING
Specifies either the range or number of rows preceding the current row. If ROWS is specified, then value is a positive integer indicating a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or value FOLLOWING.

value FOLLOWING
Specifies either the range or number of rows following the current row. If ROWS is specified, then value is a positive integer indicating a number of rows. If RANGE is specified, then the data type of value must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow addition.

Examples:
Display the ranking of employees, in order by surname, according to their total salary (based on salary plus bonus) that have a total salary more than $30,000.
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE WHERE SALARY+BONUS > 30000
ORDER BY LASTNAME

Note that if the result is to be ordered by the ranking, then replace ORDER BY LASTNAME with:

ORDER BY RANK_SALARY

or
ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC)

Rank the departments according to their average total salary.
SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY RANK_AVG_SAL

Rank the employees within a department according to their education level. Having multiple employees with the same rank in the department should not increase the next ranking value.
SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL
DENSE_RANK() OVER
(PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME

Provide row numbers in the result of a query.
SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER,
LASTNAME, SALARY
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME

List the top five wage earners.
SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY
FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE) AS RANKED_EMPLOYEE
WHERE RANK_SALARY < 6
ORDER BY RANK_SALARY

Notice that a nested table expression was used to first compute the result, including the rankings, before the rank could be used in the WHERE clause. A common table expression could also have been used.

evvcom (2005-05-18 12:14:00) [3]

> У меня нет подрезанный

SQL Reference

OLAP Functions

OLAP-function

|--+-| ranking-function |-----+---------------------------------|
+-| numbering-function |---+
'-| aggregation-function |-'

ranking-function

|---+-RANK ()-------+--OVER------------------------------------->
'-DENSE_RANK ()-'

>----(--+------------------------------+------------------------>
'-| window-partition-clause |--'

>----| window-order-clause |--)---------------------------------|

numbering-function

|---ROW_NUMBER ()--OVER---(--+------------------------------+--->
'-| window-partition-clause |--'

>----+--------------------------+---)---------------------------|
'-| window-order-clause |--'

aggregation-function

|---column-function--OVER---(--+------------------------------+->
'-| window-partition-clause |--'

>----+--------------------------+------------------------------->
'-| window-order-clause |--'

.-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING--.
>----+------------------------------------------------------------+---)->
'-| window-aggregation-group-clause |------------------------'

>---------------------------------------------------------------|

window-partition-clause

.-,--------------------------.
V |
|---PARTITION BY-----partitioning-expression---+----------------|

window-order-clause

.-,-------------------------------.
V .-ASC--. |
|---ORDER BY-----sort-key-expression--+------+--+---------------|
'-DESC-'

window-aggregation-group-clause

|---+-ROWS--+---+-| group-start |---+---------------------------|
'-RANGE-' '-| group-between |-'

group-start

|---+-UNBOUNDED PRECEDING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
'-CURRENT ROW-------------------'

group-between

|---BETWEEN--| group-bound1 |--AND--| group-bound2 |------------|

group-bound1

|---+-UNBOUNDED PRECEDING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
+-unsigned-constant--FOLLOWING--+
'-CURRENT ROW-------------------'

group-bound2

|---+-UNBOUNDED FOLLOWING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
+-unsigned-constant--FOLLOWING--+
'-CURRENT ROW-------------------'

On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing column function information as a scalar value in a query result. An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement (SQLSTATE 42903). An OLAP function cannot be used as an argument of a column function (SQLSTATE 42607). The query result to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP function.

When specifying an OLAP function, a window is specified that defines the rows over which the function is applied, and in what order. When used with a column function, the applicable rows can be further refined, relative to the current row, as either a range or a number of rows preceding and following the current row. For example, within a partition by month, an average can be calculated over the previous three month period.

The ranking function computes the ordinal rank of a row within the window. Rows that are not distinct with respect to the ordering within their window are assigned the same rank. The results of ranking may be defined with or without gaps in the numbers resulting from duplicate values.

evvcom (2005-05-18 12:12:00) [4]

> А коротенько - OVER что делает

OVER переписывает уже в результате (типа открывает окно) новые значения (на 2 проходе).

ANB (2005-05-18 12:12:00) [5]

> evvcom © (18.05.05 12:10) [34]
- это не тебе, это мне был пост. Плз, напиши по русски сам. У меня нет подрезанный. Закрыты все сайты кроме этого.

evvcom (2005-05-18 12:10:00) [6]

> И где же она описана?
> Ткни пальцем, у меня по всем версиям почти документация
> есть

Это вопрос мне?
Например здесь http://www.constant.obninsk.ru/doc/db2/db2s0/olapfunc.htm- первое что нашел в сети сейчас. Хотя судя по пути это для db2, но в Oracle 9.2.0.1 работает точно.

ANB (2005-05-18 12:09:00) [7]

> evvcom © (18.05.05 12:04) [31]
- сохраню ка я себе твой пост на будущее. Не очень понял, как это у меня отработает. Потом разберусь. Интересно. А коротенько - OVER что делает ? Ни разу не юзал.

ANB (2005-05-18 12:06:00) [8]

> Petr V. Abramov © (18.05.05 11:55) [30]
- да нигде она не описана. Знаю, что оракл не гарантирует сортировку, если она не указана явно. Но бывают частности, которые работают. Вот попробуй сделать вывод дерева с connect by, чтобы в одной ветке листья сортировались хотя бы по алфавиту. А если грамотно индекс завести, то все приходит как надо.

evvcom (2005-05-18 12:04:00) [9]
ROW_NUMBER() - системная функция. Только что проверил в 9 работает, 8 у меня нет. Замечание: ROW_NUMBER()*10 - так ругается, поэтому надо вынести за скобки подзапроса. В любом случае, этот подзапрос можно переделать и под 8, если там нет ROW_NUMBER и конструкции OVER. Просто получи в селекте с ORDER BY нужный порядок, а потом с этим селектом еще один select + rownum.

Petr V. Abramov (2005-05-18 11:55:00) [10]
ANB © (17.05.05 13:16) [4]

> Тут скорее вопрос почему раньше работало. 8-)
- да есть такая фича в оракле.

И где же она описана?
Ткни пальцем, у меня по всем версиям почти документация есть

ANB (2005-05-18 11:36:00) [11]
Не, в 8 не работает. А откуда ROW_NUMBER() ? Я не видел такой функции и на 9.

evvcom (2005-05-18 11:01:00) [12]
Попробуй типа такого:

UPDATE Step S
SET S.Num = (SELECT ROW_NUMBER()*10 OVER (ORDER BY O.Num) FROM Step O WHERE O.ID = S.ID)

Такой вроде синтаксис. Не знаю только с какой версии такое будет работать, в 9-ой точно работает.

ANB (2005-05-18 09:40:00) [13]
Дык у меня уже около 16 закладок и 4 уровня вложенности по ним.

Danilka (2005-05-18 09:36:00) [14]
[25] ANB © (18.05.05 09:29)
> 1) - у меня уже кнопочки цеплять некуда :)

Это ты зря. Лучше разделить на закладки например.
Очень полезная весчь в тему:
http://www.uibook1.ru/stat/click.php?http://www.uibook1.ru/uidesign1.pdf
весит 2.5МБ.

ANB (2005-05-18 09:29:00) [15]

> Danilka © (18.05.05 08:15) [24]
- 1) - у меня уже кнопочки цеплять некуда :)
2) Вариант с полем в гриде всем больше понравился.
3) Через курсор в безымянном блоке все прекрасно работает, как я хочу.

Danilka (2005-05-18 08:15:00) [16]
Понятно.
[22] ANB © (17.05.05 16:23)
> Есть другие варианты решения задачи ?

Например, после с порядком сортировки вообще скрыть от пользователя. В интерфейс добавить две кнопочки "вверх" и "вниз", которые меняют значения полей сортировки у текущий записи и у предыдущей/последующей, после чего переоткрывает запрос, например. Можно еще две кнопочки добавить "на самый верх" и "на самый низ".
:)

Sergey13 (2005-05-17 16:23:00) [17]
2 [21] Danilka © (17.05.05 16:18)
Да не ровнум он хранит, а порядок сортировки, который вычисляется на его основе. 8-)

ANB (2005-05-17 16:23:00) [18]

> Danilka © (17.05.05 16:18) [21]
читай посты выше. Есть другие варианты решения задачи ?

Danilka (2005-05-17 16:18:00) [19]
Интересно очень, все-таки зачем хранить в базеrownum, пусть даже и умноженый на 10?

ANB (2005-05-17 16:16:00) [20]

> Desdechado © (17.05.05 15:40) [17]
, да по первичному ключу оракл сам не сортирует. А вот если условем заставить его идти по индексу, то данные так и приедут. Но не всегда так, как хочешь, если индексов несколько :)))

Страницы: 1 2 >

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »