Блог

Главная - Вопросы по программированию - почему добавление order by в запрос изменяет совокупное значение?

почему добавление order by в запрос изменяет совокупное значение?

#vertica

Вопрос:

Следующий vertica пример из https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AnalyzingData/SQLAnalytics/AnalyticFunctionsVersusAggregateFunctions.htm?tocpath=Analyzing Data|SQL Analytics|_____2

 CREATE TABLE employees(emp_no INT, dept_no INT);
INSERT INTO employees VALUES(1, 10);
INSERT INTO employees VALUES(2, 30);
INSERT INTO employees VALUES(3, 30);
INSERT INTO employees VALUES(4, 10);
INSERT INTO employees VALUES(5, 30);
INSERT INTO employees VALUES(6, 20);
INSERT INTO employees VALUES(7, 20);
INSERT INTO employees VALUES(8, 20);
INSERT INTO employees VALUES(9, 20);
INSERT INTO employees VALUES(10, 20);
INSERT INTO employees VALUES(11, 20);
COMMIT;

Если я выполняю этот запрос без order by , я получаю одинаковое значение счетчика для всех строк

 dbadmin@b006bc38a718(*)=> 
select 
  emp_no
, dept_not
, count(*) over (partition by dept_not) as emp_count 
from employees;
 
  emp_no | dept_not | emp_count
-------- ---------- -----------
      6 |       20 |         6
      7 |       20 |         6
      8 |       20 |         6
      9 |       20 |         6
     10 |       20 |         6
     11 |       20 |         6
      1 |       10 |         2
      4 |       10 |         2
      2 |       30 |         3
      3 |       30 |         3
      5 |       30 |         3
(11 rows)

Но если я добавлю order by , то получу добавочную стоимость

 dbadmin@b006bc38a718(*)=> 
select 
  emp_no
, dept_not
, count(*) over (partition by dept_not order by emp_no) as emp_count 
from employees;
 
  emp_no | dept_not | emp_count
-------- ---------- -----------
      2 |       30 |         1
      3 |       30 |         2
      5 |       30 |         3
      1 |       10 |         1
      4 |       10 |         2
      6 |       20 |         1
      7 |       20 |         2
      8 |       20 |         3
      9 |       20 |         4
     10 |       20 |         5
     11 |       20 |         6
(11 rows)

Time: First fetch (11 rows): 85.075 ms. All rows formatted: 85.139 ms

На что это влияет order by ? Почему я получаю добавочное значение?

Ответ №1:

Если предложение window содержит только PARTITION BY , оно возвращает общую сумму раздела — для каждой строки раздела одно и то же значение.

Если предложение window содержит оба PARTITION BY и ORDER BY , оно возвращает количество выполняемых операций в разделе . Итак, используя ORDER BY выражение, сколько строк было подсчитано до сих пор в разделе.

Именно так работают оконные функции. Они дают вам целый мир возможностей…

Комментарии:

1. ОК. хотя может быть полезно предоставить способ подсчета количества выполняемых операций, я чувствую, что использование фразы order by — не лучший вариант и вызывает путаницу. Кажется, что количество выполняемых операций является побочным эффектом order by

2. и partition by , похоже, такие же, как group by . Интересно, почему бы не использовать фразу group by , а не partition by потому partition , что имеет другое значение в Vertica.

3. Да, Manu — но если вы перейдете SUM(amount) OVER(PARTITION BY iso_ctry ORDER BY sales_dt) , вы получите текущую сумму ваших продаж по стране с течением времени. И это действительно удобно…

4. GROUP BY возвращает одну строку на GROUP BY комбинацию списка столбцов, уменьшая количество строк. PARTITION BY возвращает все входные строки, но может возвращать один и тот же результат для всех строк в разделе. Это два очень разных варианта использования.

5. правильно ли я понимаю, что использую аналитическую функцию вместо агрегатной функции, если мне нужно выходное значение для каждой входной строки?

Ответ №2:

Это происходит потому, что Vertica применяет значение по умолчанию frame-clause , которое определяется как:

 RANGE UNBOUNDED PRECEDING AND CURRENT ROW

Итак, чтобы получить желаемый результат, вы можете добавить предложение frame, как показано ниже, после вас ORDER BY в OVER() предложении:

 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Это поведение задокументировано как:

Если в предложении OVER не указывается рамка окна, функция создает окно по умолчанию, которое простирается от текущей строки до первой строки в текущем разделе.

Ссылка на документ

Метки: