Как получить доступ к предыдущим строкам в пользовательской скалярной функции SQL?

Я хочу написать свою собственную скалярную функцию SQL, которая вычисляет среднее значение между точками данных одного столбца по переменному количеству строк на основе второго столбца. Я представляю, как эта функция выглядит примерно так: WINDOWMEAN(data, mileage, 100). Это вычислит среднее значение всех data по строкам, где mileage находится в пределах 100 миль от текущей строки.

Пример:

| data | mileage | 
|  10  |  1000   |
|  15  |  1009   |
|  20  |  1056   |
|  16  |  1098   |
|  13  |  1130   |
|  14  |  1200   |

С таким запросом, как SELECT WINDOWMEAN(data, mileage, 100) AS a FROM t, я бы ожидал:

| data | mileage | a    |
|  10  |  1000   | 10   |
|  15  |  1009   | 12.5 |
|  20  |  1056   | 15   |
|  16  |  1098   | 15.25|
|  13  |  1130   | 16.33|
|  14  |  1200   | 13.5 |

a рассчитывается для каждой строки как среднее значение всех строк в пределах 100 миль от mileage, предшествующих текущей строке.

Где я застреваю, так это в том, как получить доступ к предыдущим строкам в пользовательской функции SQL. Я не уверен, что то, что я пытаюсь сделать, возможно, потому что мне еще предстоит найти документацию для доступа к другим строкам таким образом.

Независимо от того, специфично ли это решение или нет, есть ли способ получить доступ к предыдущим строкам в пользовательской скалярной функции SQL?

(можно предположить, что строки упорядочены по пробегу)


sql sqlite sql-function
person mdmnd18    schedule 04.10.2019    source источник
comment
Вы не можете сделать это со скалярной функцией. Однако вы, вероятно, можете делать то, что хотите, используя оконную функцию avg() с соответствующей рамкой диапазона.   -  person Shawn    schedule 04.10.2019
comment
@Shawn Я пытался использовать оконные функции, но не мог понять, как использовать переменный размер окна (как в примере здесь). Для SQLite.org это не похоже на то, что окно может иметь изменяющийся размер.   -  person mdmnd18    schedule 04.10.2019

Ответы (2)


arrow_upward
0
arrow_downward

Вы можете использовать оконную функцию с соответствующим определением кадра RANGE, чтобы ограничить окно строками в пределах 100 миль от текущей:

SELECT data, mileage
     , avg(data) OVER (ORDER BY mileage RANGE BETWEEN 100 PRECEDING AND CURRENT ROW) AS a
FROM t
ORDER BY mileage;

data        mileage     a
----------  ----------  ----------
10          1000        10.0
15          1009        12.5
20          1056        15.0
16          1098        15.25
13          1130        16.3333333
14          1200        13.5

Заметки:

Для этого запроса требуется Sqlite 3.28 или новее, так как в этой версии были некоторые важные улучшения оконных функций, позволяющие, среди прочего, использовать числовые диапазоны, подобные этому.

Для достижения наилучших результатов создайте индекс для t(mileage) или покрывающий индекс для t(mileage, data).

Версия функции без окна, использующая коррелированный подзапрос (также лучше работает с этим индексом):

SELECT data, mileage
     , (SELECT avg(t2.data) FROM t AS t2
        WHERE t2.mileage BETWEEN t1.mileage - 100 AND t1.mileage) AS a
FROM t AS t1
ORDER BY mileage;
person Shawn    schedule 04.10.2019
comment
Я попытался выполнить этот запрос, но получил ошибку RANGE must use only UNBOUNDED or CURRENT ROW. Кроме того, разве это не просмотр 100 предыдущих строк? - person mdmnd18; 04.10.2019
comment
@ mdmnd18 mdmnd18 Значит, вы не используете достаточно последнюю версию sqlite. Требуется 3.28 или новее. И нет, не 100 строк. RANGE отличается от ROWS спецификацией фрейма. Дополнительные сведения см. на странице sqlite.org/windowfunctions.html#frame_specifications. - person Shawn; 04.10.2019

arrow_upward
0
arrow_downward

В SQL Server это возможно с помощью оконных функций ROW_NUMBER. Эта функция существует для sqlite ROW_NUMBER но я не уверен, что это сработает.

DECLARE @t TABLE (data int ,mileage int)

--SOME DATA
INSERT INTO @t
VALUES (10,1000),
        (15,1009),
        (20,1056)

--Replace @t by the real table name

;WITH TableWithRow(data, mileage, r)
AS
(
    SELECT data, mileage, ROW_NUMBER() OVER (ORDER BY data) 
    FROM @t
)
SELECT c.data, c.mileage, p.data previousData, p.mileage previousmileage
FROM TableWithRow c
LEFT OUTER JOIN TableWithRow p on c.r-1 = p.r

---WITHOUT CTE (bad performance)
SELECT c.data, c.mileage, p.data previousData, p.mileage previousmileage
FROM (
    SELECT data, mileage, ROW_NUMBER() OVER (ORDER BY data) r
    FROM @t
) c
LEFT OUTER JOIN (
    SELECT data, mileage, ROW_NUMBER() OVER (ORDER BY data) r
    FROM @t
) p on c.r-1 = p.r
person Tohm    schedule 04.10.2019