AllowMultiple не работает с Атрибутами Свойства?

и спасибо, что поделились этим очень интересным испытанием. В следующий раз было бы очень полезно, если бы вы могли предоставить: полный DDL, фактические операторы INSERT для данных примера, желаемый набор результатов в деталях и несколько более подробное объяснение. Несколько человек опубликовали ответы и удалили их, когда поняли, что неправильно поняли вопрос.

Я записал DDL и вставил:

CREATE TABLE FOO 
(ID INT PRIMARY KEY, Value DECIMAL(5,2));

INSERT INTO FOO (ID, Value)
VALUES  (  1 , 118.89 ),
         (  2 , 113.90 ),
         (  3 , 110.62 ),
         (  4 , 105.37 ),
         (  5 , 119.16 ),
         (  6 , 118.33 ),
         (  7 , 116.93 ),
         (  8 , 117.74 ),
         (  9 , 118.01 ),
         ( 10 , 125.00 ),
         ( 11 , 130.62 ),
         ( 12 , 137.50 ),
         ( 13 , 136.65 ),
         ( 14 , 133.80 ),
         ( 15 , 132.53 ),
         ( 16 , 133.03 ),
         ( 17 , 131.91 ),
         ( 18 , 134.06 ),
         ( 19 , 131.03 ),
         ( 20 , 132.38 );

SELECT  * 
FROM    FOO;

Я надеюсь, что я правильно понимаю ваш вопрос, поэтому вот мой подход к решению.

Прежде чем перейти к реальному решению SQL, я попытался понять математическую сложность. Давайте предположим, что у нас есть 10 строк в таблице. Число различных последовательных групп представляет собой расходящийся ряд натуральных чисел или треугольное число. Он начинается с 1 опции для группы из 10 последовательных строк от 1 до 10. тогда у нас есть 2 варианта для любой группы из 9 последовательных строк, 1-9 и 2-10. Затем 3 для любой группы из 8 строк и т. Д. Общее количество последовательных групп любой длины может быть легко вычислено. Если бы это было полное трехъязычное число, форумулой было бы n (n + 1) / 2. Здесь, поскольку наименьшая группа состоит из 2 строк, а не 1, она составляет (n-1) (n-1 + 1) / 2 = n (n-1) / 2.

Я буду использовать для этого синтаксис SQL Server, так как я не люблю использовать PL / pgSQL и не очень разбираюсь в этом. Кто-то с большим опытом в PL / pgSQL может конвертировать его, это не должно быть слишком сложно. Я никогда не понимал, почему так много РСУБД не позволяют объединять императивные конструкции с SQL в одной и той же области действия сценария.

Моей первой мыслью было попробовать наивный подход на основе множеств для вычисления всех возможных групп с использованием рекурсивного запроса с различными размерами групп для предложения OVER. Для 500 строк нам нужно было бы вычислить суммарные дельты для 500 * 499/2 групп = ~ 125K. Было бы хорошо, если бы мы могли сделать что-то вроде:

DECLARE @MaxGroupSize INT = (SELECT COUNT(*) FROM Foo);
DECLARE @Threshold DECIMAL(5,2) = 13.5;
WITH GroupDeltas
AS
(
SELECT  1 AS GroupSize, 
        ID,
        CAST((  LEAD(Value) 
                OVER(ORDER BY ID ASC) - Value) 
        AS DECIMAL(38,2)) AS GroupDelta
FROM    Foo
UNION ALL
SELECT  (GroupSize + 1),
        ID,
        SUM(GroupDelta) 
            OVER (  ORDER BY ID ASC 
                    ROWS BETWEEN CURRENT ROW AND 0 /*NO GO WITH (GroupSize - 2)*/  FOLLOWING)
FROM    GroupDeltas
WHERE   (GroupSize + 1) <= @MaxGroupSize
)
SELECT  * 
FROM    GroupDeltas
WHERE   ABS(GroupDelta) >= @Threshold
        AND
        GroupSize = (
                        SELECT  MIN(GroupSize) 
                        FROM    GroupDeltas 
                        WHERE   GroupSize > 1 -- Eliminate Anchor
                                AND
                                ABS(GroupDelta) >= @Threshold   
                    );

, но, к сожалению, смещение кадра должно использовать константное выражение. Переменные и выражения столбцов не допускаются. Обратите внимание, что приведенный выше запрос работает для первого примера с размером группы 2, но только потому, что я использовал смещение литерала 0 вместо обязательного (GroupSize - 2), что недопустимо ...

Было бы неплохо, если бы мы могли добавить условие остановки к рекурсивному члену

 AND NOT EXISTS (
                    SELECT  NULL
                    FROM    GroupDeltas
                    WHERE   ABS(GroupDelta) >= 13.5
                )

Но мы можем ссылаться на CTE в рекурсивном элементе только один раз ...
Во всяком случае, этот подход не работает с самого начала, поэтому я больше не проверял его. Я только добавил это здесь как интересное умственное упражнение, которое я прошел.

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

Моя идея состояла в том, чтобы создать цикл, который начинается с наименьшего возможного размера группы и останавливается, когда мы достигаем совпадения. Я не хотел использовать курсор RBAR, поэтому я выбрал более эффективную оконную функцию, использующую динамическое выполнение, чтобы обойти ограничение константы смещения. Следующее - моя попытка. Обратите внимание, что если существует более 1 группы, которые удовлетворяют пороговому значению, будут показаны обе.

DROP TABLE IF EXISTS #GroupDeltas;
GO

DECLARE @Threshold DECIMAL(5,2) = 19.2,
        @MaxGroupSize INT = (SELECT COUNT(*) FROM FOO),
        @GroupSize INT = 2, -- Initial Group Size
        @SQL VARCHAR(1000);

CREATE TABLE #GroupDeltas 
    (
        StartID INT, 
        GroupSize INT,
        GroupDelta DECIMAL(9,2),
        PRIMARY KEY (StartID, GroupSize)
    );

WHILE @GroupSize <= @MaxGroupSize
BEGIN
    SET @SQL = '
                ;WITH DeltasFromNext
                AS
                    (
                        SELECT  ID,
                                LEAD(Value) OVER(ORDER BY ID ASC) - Value AS Delta
                        FROM    FOO
                    )
                    SELECT  ID, 
                            ' + CAST(@GroupSize AS VARCHAR(5)) +',
                            SUM(Delta) 
                            OVER (  ORDER BY ID 
                                    ROWS BETWEEN 
                                    CURRENT ROW AND 
                                    ' + CAST(@GroupSize - 2 AS VARCHAR(5)) 
                                    + ' FOLLOWING)
                    FROM DeltasFromNext;
    '
    INSERT INTO #GroupDeltas
    EXECUTE (@SQL);
    IF EXISTS   (
                    SELECT  NULL
                    FROM    #GroupDeltas
                    WHERE   ABS(GroupDelta) >= @Threshold
                )
    BREAK;
    SET @GroupSize += 1
END
SELECT  * 
FROM    #GroupDeltas
WHERE   ABS(GroupDelta) >= @Threshold
ORDER BY GroupSize, StartID;

HTH

PS: обратная связь и предложения по улучшению приветствуются. Я считаю, что это очень интересное упражнение, и, возможно, есть лучшие способы его достичь ... Я могу вернуться к нему снова, если у меня будет свободное время.