SQL: пара приемов в SELECT-запросах

[Daniil1979]

Давая решение алгоритма islands/gaps, было бы неплохо обозначить, что это именно islands/gaps.
https://www.simple-talk.com/sql/t-sql-programming/calculating-gaps-between-overlapping-time-intervals-in-sql/

[x893]

Статья по ссылке очень неплохая. Спасибо!

[darthunix]

Забавы ради попробовал решить, не подглядывая в ваш вариант (PostgreSQL).
Создаем таблицу с данными (данные неупорядоченные)

create table weather(time timestamp, clear boolean);

insert into weather(time, clear)
select generate_series(
  '2000-01-01'::timestamp, 
  '2009-01-01'::timestamp, 
  random() * 7 * '1 day'::interval
) as time,
random() > 0.5 as clear order by random();

Находим интервалы солнечных дней (начало, конец и длина), упорядочиваем по длине и выбираем 20.

  with ordered_weather as (select * from weather order by time),
  nasty as (
    select row_number() over() as gap_group, time, clear from ordered_weather where not clear
  ),
  gaps as (
    select row_number() over(), b.gap_group, a.time, a.clear from ordered_weather a
    left join nasty b using (time, clear)
  ),
  limited_gaps as (
    select 0 as row_number, 0 as gap_group, 
    (select min(time) - '1 day'::interval from weather) as time, false as clear
    union all
    select * from gaps
    union all
    select (select max(row_number) + 1 from gaps), (select max(gap_group) + 1 from gaps), 
    null::timestamp, false
  )
  select c.time as clear_start, d.time as clear_stop, (d.time - c.time) as clear_interval 
  from limited_gaps a
  left join (
    select row_number, gap_group + 1 as gap_group from limited_gaps where gap_group is not null
  ) b using(gap_group)
  join limited_gaps c on c.row_number = coalesce(b.row_number,0) + 1
  join limited_gaps d on d.row_number = a.row_number - 1
  where a.gap_group is not null and (a.row_number - coalesce(b.row_number,0) > 1)
  order by (d.time - c.time) desc limit 20;

[RedQuark]

Т.е. открыть курсор с парой переменных мы принципиально не хотим? Научим писать заведомо провальные по производительности запросы. Оказывается это еще и именованный алгоритм, судя по комментарию.

[darthunix]

Курсоры — это медленно и очень плохая практика в sql. Ну и да, вы смотрели план выполнения запроса в этом именованном алгоритме?

[DataArt]

1. Большое спасибо за ссылку!

2. Конечно, есть разные способы решения. Exists и неявный join будут работать везде, включая mysql.

3. Производительность не рассматривалась.
«Провальные по производительности» запросы успешно решали реальные задачи в тестировании. Хотя, конечно, кое-где произвидительность станет узким местом.

[el777]

Буквально на днях возникла похожая задача — за все время работы комментариев надо было найти часы и темы в которых было самое активное обсуждение. Маленькая загвоздка — сообщений почти 50 млн. Прямой запрос с JOIN, примерно как предложено, просто поставил сервер раком. Пришлось жестко перезагружать.

Следующая попытка — упростить данные. Выгружены только интересующие 3 колонки: id, datetime, topic_id.
Загружается в свежую базу на сервер. Считается 20+ минут — не дождались, ушли домой. Через несколько часов проверили — правильный результат был получен.

Следующее решение. Берем базу, вычитываем ее в pandas, который установлен только на ноутбуке, затем строим индексы и группировки. Ноутбук считает 3 минуты и выдает результат.