SQL Server Выберите, где этап / последовательность была пропущена или не соответствует последовательности

Question

У меня есть стол с families_id, date, metric_id

Запись вставляется для каждого families_id там будет date & metric_id 1-10.

Таким образом, должно быть 10 записей для каждого families_id, записи вставляются с датой, которую каждый должен следовать друг за другом. Так metric_id 10 дата должна быть больше чем metric_id 6 свидание.

По массе как выбрать где они

1. Пропустил metric_id
2. Дата для metric_id 6 до даты metric_id 2

Answer 1

Используйте row_number, чтобы назначить порядковый номер для metric_id и дату для каждого семейства, тогда они должны совпадать - также metric_id, 1,2,3,4... должны совпадать с его рассчитанным row_number(), также 1,2,3,4....

SELECT IQ.* FROM (SELECT families_id, [date], metric_id, 
        ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY [date]) rn_date,
        ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY metricid) rn_metric FROM YourTable) IQ 
    WHERE IQ.rn_date != IQ.rn_metric;

--should detect wrongly ordered metric_ids
SELECT IQ.* FROM (SELECT families_id, [date], metric_id, 
        ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY [date]) rn_date,
        ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY metricid) rn_metric FROM YourTable) IQ 
    WHERE IQ.metric_id != IQ.rn_metric;

Другая возможность - определить metricID, где дата раньше для более высокого идентификатора

    SELECT y1.families_id, y1.metric_id FROM yourtable y1
                    WHERE 
                    EXISTS(SELECT 0 FROM yourtable y2 WHERE y1.families_id = y2.families_id 
                                                            AND
                                                            y2.date < y1.date 
                                                            AND
                                                            y2.metricid > y1.metricid)