Для каждой строки применить функцию на основе данных последних четырех строк включая эту строку

@13131717 · Регистрация: 17.06.2018

Студворк — интернет-сервис помощи студентам

Есть таблица формата
date balance
01.01 5
01.01 15
01.01 20
01.01 20
01.01 40

и функция для это таблицы:

SQL
1
2
3
4
5
6
7
8
9
WITH cte_1 AS (
SELECT *,  ROW_NUMBER() OVER(partition BY DATE ORDER BY balance) AS  rank --ранжируем
FROM #table)
 
SELECT DATE, 
    1 - 2 * SUM((CAST(balance AS FLOAT) * (rank - 1) + balance / 2)) / COUNT(*) / SUM(balance) AS gini
FROM cte_1
GROUP BY DATE
ORDER BY DATE ASC

Но она посчитает одно значение для всего столбца. Как сделать так, чтобы она работала на 4х последних значениях для каждой строки? Например, для 04.01 работало бы на значениях 5, 15, 20, 20; для 05.01 работало бы на 15, 20, 20, 40 и т.п.
И результат выдавался бы либо в новый столбец

@k@lYaN · 13.12.2023, 15:38

так?

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
;WITH cte_1 AS (
SELECT *,  ROW_NUMBER() OVER(partition BY DATE ORDER BY balance) AS  rank --ранжируем
FROM #table)
 
SELECT DATE,
    (SELECT
            1 - 2 * SUM((CAST(c2.balance AS FLOAT) * (c2.rank - 1) + c2.balance / 2)) / COUNT(*) / SUM(c2.balance)
    FROM cte_1 c2
    WHERE c1.date=c2.date
    AND c2.rank BETWEEN c1.rank-3 AND c1.rank
    )
    AS gini
FROM cte_1 c1

@13131717 · 13.12.2023, 16:37 **[ТС]**

k@lYaN, Супер, работает!

Подскажите, а как можно его доработать, если таблица будет формата:

id date balance
1 01.01 5
1 01.02 15
1 01.03 20
1 01.04 20
1 01.05 40
2 01.01 5
2 01.02 10
2 01.03 30
2 01.04 60
2 01.05 95

То есть добавится столбец с Id. В разрезе id даты уникальные.

@k@lYaN · 13.12.2023, 16:51

добавить c1.id=c2.id в условие вложенного запроса

@13131717 · 13.12.2023, 17:03 **[ТС]**

Сообщение от k@lYaN

добавить c1.id=c2.id в условие вложенного запроса

Создаю таблицу

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE #table
(
    id INT,
    DATE DATE,
    balance INT
);
 
INSERT INTO #table VALUES (1, '01.01.2000', 5)
INSERT INTO #table VALUES (1, '01.02.2000', 15)
INSERT INTO #table VALUES (1, '01.03.2000', 20)
INSERT INTO #table VALUES (1, '01.04.2000', 20)
INSERT INTO #table VALUES (1, '01.05.2000', 40)
INSERT INTO #table VALUES (2, '01.01.2000', 1)
INSERT INTO #table VALUES (2, '01.02.2000', 15)
INSERT INTO #table VALUES (2, '01.03.2000', 45)
INSERT INTO #table VALUES (2, '01.04.2000', 65)
INSERT INTO #table VALUES (2, '01.05.2000', 90)

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH cte_1 AS (  
SELECT *,  ROW_NUMBER() OVER(partition BY id ORDER BY balance) AS  rank --partition изменено на id
FROM #table)
 
SELECT id, DATE, -- добавляю id
    (SELECT
            1 - 2 * SUM((CAST(c2.balance AS FLOAT) * (c2.rank - 1) + c2.balance / 2)) / COUNT(*) / SUM(c2.balance)
    FROM cte_1 c2
    WHERE c1.id=c2.id --добавляю id
    AND c1.date=c2.date
    AND c2.rank BETWEEN c1.rank-3 AND c1.rank
    )
    AS gini
FROM cte_1 c1

При выполнении даёт некорректный результат

@k@lYaN · 13.12.2023, 17:39

Надо для каждой строки посчитать формулу по текущей строе и трем предыдущим строкам с тем же id?
тогда

T-SQL
1
AND c1.date=c2.date

не нужен

Новые блоги и статьи Все статьи Все блоги /
Установка Emscripten SDK (emsdk) и CMake на Windows для сборки C и C++ приложений в WebAssembly (Wasm) 8Observer8 30.01.2026 Чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. Система контроля версиями Git. . .	Подключение Box2D v3 к SDL3 для Android: физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .	Инструменты COM: Сохранение данный из VARIANT в файл и загрузка из файла в VARIANT bedvit 28.01.2026 Сохранение базовых типов COM и массивов (одномерных или двухмерных) любой вложенности (деревья) в файл, с возможностью выбора алгоритмов сжатия и шифрования. Часть библиотеки BedvitCOM Использованы. . .	Загрузка PNG с альфа-каналом на SDL3 для Android: с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 28.01.2026 Содержание блога SDL3 имеет собственные средства для загрузки и отображения PNG-файлов с альфа-каналом и базовой работы с ними. В этой инструкции используется функция SDL_LoadPNG(), которая. . .
Загрузка PNG с альфа-каналом на SDL3 для Android: с помощью SDL3_image 8Observer8 27.01.2026 Содержание блога SDL3_image - это библиотека для загрузки и работы с изображениями. Эта пошаговая инструкция покажет, как загрузить и вывести на экран смартфона картинку с альфа-каналом, то есть с. . .	Влияние грибов на сукцессию anaschu 26.01.2026 Бифуркационные изменения массы гриба происходят тогда, когда мы уменьшаем массу компоста в 10 раз, а скорость прироста биомассы уменьшаем в три раза. Скорость прироста биомассы может уменьшаться за. . .	Воспроизведение звукового файла с помощью SDL3_mixer при касании экрана Android 8Observer8 26.01.2026 Содержание блога SDL3_mixer - это библиотека я для воспроизведения аудио. В отличие от инструкции по добавлению текста код по проигрыванию звука уже содержится в шаблоне примера. Нужно только. . .	Установка Android SDK, NDK, JDK, CMake и т.д. 8Observer8 25.01.2026 Содержание блога Перейдите по ссылке: https:/ / developer. android. com/ studio и в самом низу страницы кликните по архиву "commandlinetools-win-xxxxxx_latest. zip" Извлеките архив и вы увидите. . .

@k@lYaN 46 / 35 / 11 Регистрация: 16.10.2011 Сообщений: 121
	13.12.2023, 16:51
	добавить c1.id=c2.id в условие вложенного запроса 0

Для каждой строки применить функцию на основе данных последних четырех строк включая эту строку

Решение