Оптимизировать длительный SQL-запрос куста - имеет соединение с той же таблицей

Question

Приведенный ниже запрос занимает много времени. Он работает с двигателем исполнения Tez.

SELECT STG.EMP_TYPE, DEPT, A.TOTAL_COUNT, COUNT(DISTINCT EMP_ID) AS COUNT_DEPT
FROM 
STAGE_SOURCE STG 
LEFT OUTER JOIN 
( SELECT EMP_TYPE, COUNT(DISTINCT EMP_ID) AS TOTAL_COUNT 
  FROM STAGE_SOURCE 
  GROUP BY EMP_TYPE 
) A
ON STG.EMP_TYPE = A.EMP_TYPE
GROUP BY STG.EMP_TYPE, DEPT, A.TOTAL_COUNT;

Есть ли какая-либо опция перезаписи или стратегия оптимизации, которая может улучшить производительность запроса?

Answer 1

Поняв ваш запрос, я смог понять, что вам нужно количество значений 2. Во-первых, количество EMP_ID для каждого EMP_TYPE и, во-вторых. Количество EMP_ID в DEPT & EMP_TYPE

SELECT 

STG.EMP_TYPE, 
DEPT,
TOTAL_COUNT, 
COUNT(EMP_ID) AS COUNT_DEPT

FROM 
STAGE_SOURCE STG 
JOIN 
( SELECT EMP_TYPE, COUNT(EMP_ID) AS TOTAL_COUNT 
  FROM STAGE_SOURCE 
  GROUP BY EMP_TYPE 
) A
ON STG.EMP_TYPE = A.EMP_TYPE
GROUP BY STG.EMP_TYPE, DEPT,TOTAL_COUNT;

Использование GROUP BY вместо DISTINCT, где это возможно, может сократить время выполнения. Как уже упоминалось выше в разделе "Учитывайте меня", GROUP BY использует преимущества индексов.

Answer 2

Я бы порекомендовал получать записи таблицы только один раз.

Ваше внутреннее совокупное вычисление может быть сделано с использованием оконной функции.

Я считаю, что этот запрос должен дать вам те же результаты, и вы бы избавились от JOIN,

SELECT
    EMP_TYPE,
    DEPT,
    COUNT( DISTINCT EMP_ID ) OVER ( PARTITION BY EMP_TYPE ) AS TOTAL_COUNT,
    COUNT( DISTINCT EMP_ID ) AS COUNT_DEPT
FROM
    STAGE_SOURCE
GROUP BY EMP_TYPE, DEPT

Помните, что GROUP BY также можете воспользоваться индексами.

Вот ссылка на руководство Apache Hive о функциях управления окнами и аналитики

# Редактировать после вашего комментария

По крайней мере в PostgreSQL пункт DISTINCT применяется после вычислений агрегатов оконной функции, что приводит нас к небольшому эксплойту, который может дать вам то, что вам нужно. Таким образом, мы избавились от GROUP BY, Посмотрите, как это работает на Postgres: SQLFiddle

Попробуйте следующий запрос:

SELECT
    DISTINCT
    EMP_TYPE,
    DEPT,
    COUNT( DISTINCT EMP_ID ) OVER ( PARTITION BY EMP_TYPE ) AS TOTAL_COUNT,
    COUNT( DISTINCT EMP_ID ) OVER ( PARTITION BY EMP_TYPE, DEPT ) AS COUNT_DEPT
FROM
    STAGE_SOURCE

# Редакция 2

SELECT
    DISTINCT
    EMP_TYPE,
    DEPT,
    COUNT( DISTINCT EMP_ID ) OVER ( PARTITION BY EMP_TYPE ) AS TOTAL_COUNT,
    COUNT( DISTINCT EMP_ID ) OVER ( PARTITION BY EMP_TYPE, DEPT ) AS COUNT_DEPT
FROM (
    SELECT DISTINCT EMP_TYPE, DEPT, EMP_ID FROM STAGE_SOURCE
    ) foo