Как наиболее эффективно переписать коррелированный подзапрос в предложении SELECT?

Я пытаюсь подсчитать, посещал ли пользователь сайт в трех временных диапазонах:

  • последние 30 дней
  • от 31 до 60 дней
  • от 61 до 90 дней

Я использую Netezza, которая НЕ поддерживает коррелированные подзапросы в предложении SELECT. См. Rextester для успешного запроса, который необходимо переписать, чтобы НЕ использовать коррелированный подзапрос: http://rextester.com/JGR62033< /а>

Пример данных:

| user_id | last_visit | num_days_since_2017117 |
|---------|------------|------------------------|
| 1234    | 2017-11-02 | 15.6                   |
| 1234    | 2017-09-30 | 48.6                   |
| 1234    | 2017-09-03 | 75.0                   |
| 1234    | 2017-08-21 | 88.0                   |
| 9876    | 2017-10-03 | 45.0                   |
| 9876    | 2017-07-20 | 120.0                  |
| 5545    | 2017-09-15 | 63.0                   |

Желаемый результат:

| user_id | last_30 | btwn_31_60 | btwn_61_90 |
|---------|---------|------------|------------|
| 1234    | 1       | 1          | 1          |
| 5545    | 0       | 0          | 1          |
| 9876    | 0       | 1          | 0          |

sql netezza database-performance correlated-subquery
person psrpsrpsr    schedule 17.11.2017    source источник
comment
Здесь вам не нужен коррелированный подзапрос, если СУБД поддерживает CASE или что-то подобное.   -  person Ken White    schedule 17.11.2017
comment
Почему минус? @a_horse_with_no_name? Если есть дополнительная ясность, которую я могу внести в вопрос, пожалуйста, прокомментируйте. Я чувствую, что выразился ясно и предоставил достаточно информации, чтобы ответить на вопрос, не так ли?   -  person psrpsrpsr    schedule 17.11.2017
comment
Я не минусовал. Я прокомментировал.   -  person Ken White    schedule 17.11.2017
comment
@KenWhite, можете ли вы пояснить на примере, как это будет выглядеть с использованием образцов данных?   -  person psrpsrpsr    schedule 17.11.2017
comment
Я точно не знаю, что поддерживает эта СУБД, но вы можете использовать что-то вроде SELECT user_id, SUM(CASE WHEN num_days <= 30 then 1 else 0 end) as last_30, SUM(CASE WHEN numdays > 30 AND numdays <=60 then 1 else 0 end) as btwn_31_60, и т. д. с GROUP BY для user_id.   -  person Ken White    schedule 17.11.2017
comment
Спасибо, СУБД поддерживает операторы CASE. Если вы хотите добавить это в качестве ответа, я проверю его и приму, чтобы будущие пользователи могли его увидеть.   -  person psrpsrpsr    schedule 17.11.2017

Ответы (2)


arrow_upward
2
arrow_downward

Вот один из способов условной агрегации, Rextester:

select 
    user_id
    ,MAX(case when '2017-11-17'-visit_date <=30
          then 1
          else 0
     end) as last_30
    ,MAX(case when '2017-11-17'-visit_date >=31
               and '2017-11-17'-visit_date <=60
          then 1
          else 0
     end) as between_31_60
    ,MAX(case when '2017-11-17'-visit_date >=61
              and '2017-11-17'-visit_date <=90
          then 1
          else 0
     end) as between_61_90
from 
    visits
group by user_id
order by user_id
person Aaron Dietz    schedule 17.11.2017
comment
Поскольку контекст этого вопроса является «наиболее производительным», важно отметить, что коррелированный подзапрос, предложенный в исходном примере «rextester», потенциально НАМНОГО медленнее, чем это решение, основанное на прецедентах. Любое «самосоединение» может привести к тому, что базовая таблица будет сканироваться несколько раз, и вероятность того, что план запроса будет полностью отброшен конструкцией case/сканированием одной таблицы, крайне мала :) - person Lars G Olsen; 19.11.2017
comment
@ Ларс, должно быть, я неправильно понял эффективность коррелированных подзапросов и когда их использовать. Верно ли это утверждение: условную проверку существования критериев в одной таблице лучше всего выполнять с помощью оператора CASE, а условную проверку существования критериев в отдельной таблице лучше всего выполнять с помощью коррелированного подзапроса? - person psrpsrpsr; 20.02.2018
comment
@psrpsrpsr 1.) Проверка существования обычно выполняется быстро и предпочтительнее других подзапросов 2.) Подзапросы любого типа в выборке добавляют риск сканирования таблицы подзапросов построчно 3 .) JOINs обычно предпочтительнее, чем select подзапросы, потому что вы избегаете # 2 4.) Оптимизатор запросов может быть достаточно умен, чтобы создать один и тот же план, используете ли вы подзапрос или JOIN - person Aaron Dietz; 20.02.2018

arrow_upward
2
arrow_downward

Я не знаю, какую конкретную СУБД вы используете, но если она поддерживает CASE или эквивалент, вам не нужен коррелированный подзапрос; вы можете сделать это комбинацией SUM() и CASE.

Конечно, не проверено в вашей СУБД, но это должно дать вам отправную точку:

SELECT 
  user_id, 
  SUM(CASE WHEN num_days <= 30 then 1 else 0 end) as last_30, 
  SUM(CASE WHEN num_days > 30 AND numdays < 61 then 1 else 0 end) as btwn_31_60,
  SUM(CASE WHEN num_days >= 61 then 1 else 0 end) as btwn_61_90
FROM 
  YourTableName  -- You didn't provide a tablename
GROUP BY 
  user_id

Поскольку ваши значения являются значениями с плавающей запятой, а не целыми числами, вам может потребоваться настроить значения, используемые для диапазонов дней, чтобы они соответствовали вашим конкретным требованиям.

person Ken White    schedule 17.11.2017
comment
К вашему сведению, имя таблицы и образцы данных можно найти в ссылке Rextester в вопросе. Я смог получить желаемый результат, обернув SUM(CASE) в другой CASE, например: CASE WHEN SUM(CASE WHEN num_days ‹=30 THEN 1 ELSE 0 END) ›=1 THEN 1 ELSE 0 END - person psrpsrpsr; 17.11.2017
comment
@psrpsrpsr: я видел образец данных, но не имя таблицы (если только оно не находится во внешней ссылке, которую вы предоставили, к которой мой корпоративный прокси не дает мне доступ - еще одна причина не использовать внешние сайты в своем посте). Вам не нужен внешний CASE (тот, который вы добавили); Сначала я тестировал в SQL Server, и без него он работал нормально, хотя в моих тестовых данных использовались значения даты и DateDiff(), а не ваши данные, потому что это то, что у меня было в то время. - person Ken White; 18.11.2017