SQL: Сравниваем значения на временном интервале

2021-06-23T15:18:53.118Z

Довольно часто при анализе данных требуется сравнить значения с предыдущими на некотором интервале. Например, сравнить динамику посещений сайта по дням по сравнению с тем, что было год назад. (То есть мы сравниваем значения 22-07-2021 и 22-07-2020, 23-07-2021 и 23-07-2020 и т.д.) Или сравнить прибыль по дням в текущем и прошлом месяцах. Или среднюю нагрузку сайта по часам в будни и в выходные. В общем, применений - масса, пользы - достаточно.
Данные выбираются достаточно несложным JOIN'ом, но чтобы вам не тратить время, приведу его готовый в виде шаблона с плейсхолдерами.

Для Clickhouse:

SELECT
  date_sub(YEAR, 1, toDate( {{dt}} )) as prev_{{dt}},
  toDate( {{dt}} ) as cur_{{dt}},
  prev_{{value}},
  {{value}} as cur_{{value}},
  {{value}} - prev_{{value}} as diff_{{value}}
FROM
  {{table}}
  JOIN (
    SELECT
      toDate( {{dt}} ) as another_{{dt}},
      {{value}} as prev_{{value}}
    FROM
      {{table}}
    WHERE toDate( {{dt}} ) >= '2020-01-01' and toDate( {{dt}} ) < '2021-01-01'
  ) t2
  ON toDate(prev_{{dt}}) = another_{{dt}}

Для SQLite3:

SELECT
  strftime("%d-%m", {{dt}}) as cmp_{{dt}}, 
  date({{dt}}, '-12 month') as prev_{{dt}},
  prev_{{value}},
  {{value}},
  {{value}} - prev_{{value}} as diff_{{value}}
FROM
  {{table}} t1
  JOIN (
    SELECT
      {{dt}} as another_{{dt}},
      {{value}} as prev_{{value}}
    FROM
      {{table}}
    WHERE toDate( {{dt}} ) >= '2020-01-01' and toDate( {{dt}} ) < '2021-01-01'
  ) t2
  ON prev_{{dt}} = another_{{dt}}

Замените

{{dt}} на имя поля, содержащее дату
{{value}} на имя поля, содержащее значение, для которого мы выполняем сравнение
{{table}} на имя таблицы, откуда тянем данные

По сути, у запроса есть два параметра:
1. анализируемый интервал, за который мы смотрим разницу в значениях
2. диапазон дат, в рамках которого мы смотрим значения

#1 задается функцией date_sub(YEAR, 1, toDate( {{dt}} )) для Clickhouse и date({{dt}}, '-12 month') для SQLite.
Здесь можно указать, разницу между анализируемыми датами. Например, чтобы сравнить значения в интервале 1 месяца, нужно поменять параметры этих функций на date_sub(MONTH, 1, toDate( {{dt}} )) и date({{dt}}, '-1 month') соответственно.

#2 задается условием WHERE toDate( {{dt}} ) >= '2020-01-01' and toDate( {{dt}} ) < '2021-01-01'. Просто поменяйте интервал (это интервал предыдущего диапазона).

И еще, чтобы на графике все выглядело "комильфо", можно первым параметром сделать вывод значения, которое будет общим для интервалов. Например, если мы сравниваем по дням, то в SELECT для SQLite мы добавим strftime("%d-%m", {{dt}}) as cmp_{{dt}}, то есть будет выводится день и месяц (а год у нас отбрасывается, потому что мы сравниваем по годам).

Пример того, как оно может выглядеть:

Greg Zemskov

SQL: Сравниваем значения на временном интервале