Найдите пробел в столбце идентификатора + выберите столбец предыдущей / следующей даты

У нас есть таблица с ID столбцом автоинкремента с пробелами. В каждой строке также есть дата created.

Мы пытаемся узнать для каждого идентификатора пробела (отсутствующих) предыдущую / следующую дату, доступную в нашей таблице.

Мы уже создали SQL, который определяет пробелы (решение от здесь), поэтому я храню их в кешированной таблице, но отсюда, как найти предыдущую / следующую created дату, которая покрывает пробел от исходной таблицы.

Вход:

+----+------------+
| 84 | 1443728132 |
| 91 | 1443728489 |
| 93 | 1443729058 |
| 94 | 1443729200 |
+----+------------+

Вывод

+--------+------------+------------+
| gap_id |  prev_dt   |  next_dt   |
+--------+------------+------------+
|     85 | 1443728132 | 1443728489 |
|     86 | 1443728132 | 1443728489 |
|     87 | 1443728132 | 1443728489 |
|     88 | 1443728132 | 1443728489 |
|     89 | 1443728132 | 1443728489 |
|     90 | 1443728132 | 1443728489 |
|     92 | 1443728489 | 1443729058 |
+--------+------------+------------+

В итоге я использовал Google BigQuery.


sql google-bigquery gaps-and-islands
person Pentium10    schedule 24.10.2016    source источник
comment
Какую СУБД вы используете?   -  person a_horse_with_no_name    schedule 24.10.2016
comment
не имеет значения, мы можем перейти на предпочтительное решение, так как у нас есть системы во многих из них: MySQL, Microsoft, BigQuery.   -  person Pentium10    schedule 24.10.2016
comment
Решение для ограниченных возможностей MySQL будет выглядеть очень по-другому, чем решение для СУБД, которое поддерживает современный SQL   -  person a_horse_with_no_name    schedule 24.10.2016
comment
мы можем адаптироваться, мы не ищем решения для всех систем, а только одно, в котором когда-либо система становится проще   -  person Pentium10    schedule 24.10.2016
comment
что, если есть несколько постоянно пропущенных идентификационных номеров? не могли бы вы добавить больше данных для тестирования?   -  person Nemeros    schedule 24.10.2016
comment
@Nemeros добавил CSV-файлы для данных.   -  person Pentium10    schedule 24.10.2016
comment
@ Pentium10. . . Добавьте образец данных в вопрос не в виде файлов для загрузки. Также укажите желаемые результаты.   -  person Gordon Linoff    schedule 24.10.2016
comment
@GordonLinoff добавил   -  person Pentium10    schedule 24.10.2016

Ответы (3)


arrow_upward
1
arrow_downward

Должен работать с большинством баз данных, кроме MySQL

select      *

from       (select      lag (id) over (order by id) + 1                                 as gap_start
                       ,id - 1                                                          as gap_end
                       ,lag (dt) over (order by id)                                     as dt_before_gap
                       ,dt                                                              as dt_after_gap
                       ,case when lag (id) over (order by id) + 1 <> id then 'Y' end    as is_gap

            from        t
            ) t

where       is_gap = 'Y'
;
person David דודו Markovitz    schedule 24.10.2016

arrow_upward
2
arrow_downward

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

select min(id + 1) as first_missing_id,
       (next_id - 1) as last_missing_id,
       next_dte
from (select t.*,
             lead(id) over (order by id) as next_id,
             lead(dte) over (order by id) as next_dte
      from t
     ) t
where next_id <> id + 1
group by next_id, next_dte;

Получить отдельные идентификаторы сложно. В конце концов, если у вас есть 1, 1000000, 1000000000, вы можете генерировать много строк.

person Gordon Linoff    schedule 24.10.2016
comment
Результаты уже построены по нижнему краю каждого диапазона. Нет необходимости агрегировать (next_id уникален) - person David דודו Markovitz; 24.10.2016

arrow_upward
1
arrow_downward

Для BigQuery Standard SQL

WITH yourTable AS (
SELECT 84 AS id, 1443728132 AS dt UNION ALL
SELECT 91 AS id, 1443728489 AS dt UNION ALL
SELECT 93 AS id, 1443729058 AS dt UNION ALL
SELECT 94 AS id, 1443729200 AS dt 
),
nums AS (
  SELECT num 
  FROM UNNEST(GENERATE_ARRAY((SELECT MIN(id) FROM YourTable), (SELECT MAX(id) FROM YourTable))) AS num
),
gaps AS (
  SELECT
    LAG (id) OVER (ORDER BY id) + 1 AS gap_start,
    id - 1 AS gap_end,
    LAG (dt) OVER (ORDER BY id) AS prev_dt,
    dt AS next_dt,
    CASE 
      WHEN LAG (id) OVER (ORDER BY id) + 1 <> id THEN 'Y'
    END AS is_gap
  FROM
    yourTable 
)
SELECT num as gap_id, prev_dt, next_dt
FROM gaps JOIN nums 
ON num BETWEEN gap_start AND gap_end
WHERE is_gap = 'Y'
ORDER BY num

вывод:

gap_id     prev_dt     next_dt   
85      1443728132  1443728489   
86      1443728132  1443728489   
87      1443728132  1443728489   
88      1443728132  1443728489   
89      1443728132  1443728489   
90      1443728132  1443728489   
92      1443728489  1443729058
person Mikhail Berlyant    schedule 24.10.2016
comment
К сожалению, мне пришлось использовать что-то еще, поскольку GENERATE_ARRAY работает только с 1 миллионом записей, а мне приходилось иметь дело с гораздо большим количеством записей. Также у меня закончились ресурсы из-за order by, поэтому мне пришлось удалить его и использовать гораздо больше статической таблицы, чтобы избежать перекрестного соединения. - person Pentium10; 24.10.2016
comment
конечно: o) обратите внимание - ORDER BY вообще не является частью логики - просто удалите его - это пример только для презентации! - person Mikhail Berlyant; 24.10.2016
comment
было бы здорово, если бы вы могли поделиться своим окончательным решением здесь - person Mikhail Berlyant; 24.10.2016
comment
Я также использовал BQ cli для создания большой статической таблицы, выполняющей несколько запросов GENERATE_ARRAY(i,j), затем удалил order by и разделил запрос на части, чтобы избежать исчерпания ресурсов. - person Pentium10; 24.10.2016
comment
Предел в один миллион элементов в GENERATE_ARRAY является довольно произвольным (мы не хотели, чтобы было слишком легко закончить с превышением ресурсов). Сколько элементов вам понадобилось? Мы могли бы рассмотреть вопрос об увеличении лимита в будущем. - person Elliott Brossard; 24.10.2016