Как перебрать каждую строку каждой группы (при выполнении «группировки») в таблице Oracle
У меня есть такая таблица:
Я хочу сгруппировать по таблице на основе столбца «customer_id» и рассчитать столбец «День-день [0]». «День-день[0]» — поле «День» в каждой группе, а «день[0]» — первая строка дня в группе. В то же время я должен рассчитать общий риск, который заключается в следующем:
Это таблица после группировки по:
Это формула полного риска:
На самом деле мне приходится перебирать каждую строку каждой группы, чтобы вычислить общий риск.
Моя примерная таблица выглядит так:
CREATE TABLE risk_test
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
customer_id varchar2 (40BYTE),
risk number,
day VARCHAR2(50 BYTE))
insert into risk_test values(1,102,15,1);
insert into risk_test values(2,102,16,1);
insert into risk_test values(3,104,11,1);
insert into risk_test values(4,102,17,2);
insert into risk_test values(5,102,10,2);
insert into risk_test values(6,102,13,3);
insert into risk_test values(7,104,14,2);
insert into risk_test values(8,104,13,2);
insert into risk_test values(9,104,17,1);
insert into risk_test values(10,104,16,2);
Примерный ответ такой:
Не могли бы вы рассказать мне, как я могу реализовать этот сценарий в базе данных Oracle?
Любая помощь очень ценится.
Уважаемый @EJ Egyed, спасибо за ваш отзыв. Добавляю образец таблицы. Пожалуйста, взгляните на это.
Спасибо за добавление примера данных. Каков результат оператора select, который вы надеетесь получить из этого набора образцов данных?
Извините за неудобства. Я добавил образцы данных. Большое спасибо.
Я исправил свой ответ, чтобы он соответствовал тому, что вы ожидаете от примера данных.
Ответы 2
Ваш расчет общего риска выглядит как средневзвешенное значение для меня. То есть средний риск строк для каждого клиента, взвешенный в соответствии со смещением дня (день-день[0]), так что риски в более поздние дни учитываются больше.
Чтобы вычислить это, вам нужно общее табличное выражение, чтобы сначала вычислить риск, взвешенный по дням, для каждой строки. Затем вы можете просто вычислить средневзвешенное значение путем деления.
Приведенный ниже запрос иллюстрирует этот подход с комментариями.
-- This first WITH clause is just sample data. In your database you would
-- get rid of this and replace all references to "input" with your actual
-- table name
with input ( customer_id, risk, day ) AS (
SELECT 1053, 100, 1 FROM DUAL UNION ALL
SELECT 1053, 100, 1 FROM DUAL UNION ALL
SELECT 1053, 100, 2 FROM DUAL UNION ALL
SELECT 1053, 100, 2 FROM DUAL UNION ALL
SELECT 1053, 100, 3 FROM DUAL UNION ALL
SELECT 1054, 200, 1 FROM DUAL UNION ALL
SELECT 1054, 200, 1 FROM DUAL UNION ALL
SELECT 1054, 200, 3 FROM DUAL UNION ALL
SELECT 1054, 200, 3 FROM DUAL UNION ALL
SELECT 1054, 200, 4 FROM DUAL
),
-- This CTE computes the day offset for each row and multiplies by the risk to
-- compute a day-weighted risk.
-- I added +1 to the day_offset, otherwise risks on the 1st day would not contribute
-- to the total risk, which I think is not what you intended(?)
weighted_input AS (
SELECT i.customer_id,
i.risk,
i.day,
i.day - min(i.day) over ( partition by i.customer_id ) + 1 day_offset,
( i.day - min(i.day) over ( partition by i.customer_id ) + 1 ) * i.risk day_weighted_risk
FROM input i )
-- This is the main SELECT clause that gets all the weighted risks and computes
-- the group total risk, which appears the same in every row in each group.
SELECT wi.*,
sum(wi.day_weighted_risk) over ( partition by wi.customer_id ) / sum(wi.day_offset) over ( partition by wi.customer_id ) total_risk
FROM weighted_input wi;
+-------------+------+-----+------------+-------------------+------------+ | CUSTOMER_ID | RISK | DAY | DAY_OFFSET | DAY_WEIGHTED_RISK | TOTAL_RISK | +-------------+------+-----+------------+-------------------+------------+ | 1053 | 100 | 1 | 1 | 100 | 100 | | 1053 | 100 | 1 | 1 | 100 | 100 | | 1053 | 100 | 2 | 2 | 200 | 100 | | 1053 | 100 | 2 | 2 | 200 | 100 | | 1053 | 100 | 3 | 3 | 300 | 100 | | 1054 | 200 | 1 | 1 | 200 | 200 | | 1054 | 200 | 1 | 1 | 200 | 200 | | 1054 | 200 | 3 | 3 | 600 | 200 | | 1054 | 200 | 3 | 3 | 600 | 200 | | 1054 | 200 | 4 | 4 | 800 | 200 | +-------------+------+-----+------------+-------------------+------------+
Для вашей базы данных, имеющей реальную таблицу и не нуждающейся в input CTE, это будет:
WITH weighted_input AS (
-- This CTE computes the day offset for each row and multiplies by the risk to
-- compute a day-weighted risk.
-- I added +1 to the day_offset, otherwise risks on the 1st day would not contribute
-- to the total risk, which I think is not what you intended(?)
SELECT i.customer_id,
i.risk,
i.day,
i.day - min(i.day) over ( partition by i.customer_id ) + 1 day_offset,
( i.day - min(i.day) over ( partition by i.customer_id ) + 1 ) * i.risk day_weighted_risk
FROM my_table i )
-- This is the main SELECT clause that gets all the weighted risks and computes
-- the group total risk, which appears the same in every row in each group.
SELECT wi.*,
sum(wi.day_weighted_risk) over ( partition by wi.customer_id ) / sum(wi.day_offset) over ( partition by wi.customer_id ) total_risk
FROM weighted_input wi;
Уважаемый @Matthew McPeek, спасибо за ваш ответ. Я скопировал ваш код в жабе, но получил ошибку. Он не принял взвешенный_вход. Он показывает эту ошибку: ORA-00928: отсутствует ключевое слово SELECT. Не могли бы вы направить меня?
Какую версию Oracle вы используете? Использует ли TOAD JDBC или клиент Oracle? Если последнее, какую версию Oracle Client вы используете? Кроме того, когда вы избавляетесь от CTE "input", вам нужно сохранить ключевое слово "WITH". Опубликуйте, что именно вы запустили.
Я использую Oracle 19c. TOAD использует клиент Oracle, который называется OraDB19Home1. Большое спасибо.
Я обновил ответ, чтобы показать, что вы должны отправить, чтобы использовать свою таблицу вместо input CTE (при условии, что ваша таблица называется «my_table». Кроме того, @EJ_Egyed опубликовал ответ, который представляет собой тот же расчет с использованием встроенного представления вместо CTE. Возможно, вам больше повезет с этим.
Уважаемый @Matthew McPeak, большое спасибо. Теперь это работает.
Используя предоставленные образцы данных, я считаю, что этот запрос должен правильно рассчитать риски:
Запрос
SELECT o.*,
ROUND (
SUM (day_minus_day0 * risk) OVER (PARTITION BY customer_id)
/ SUM (day_minus_day0) OVER (PARTITION BY customer_id),
5) AS total_risk
FROM (SELECT rt.*, (rt.day - MIN (rt.day) OVER (PARTITION BY customer_id)) + 1 AS day_minus_day0
FROM risk_test rt) o
ORDER BY customer_id, TO_NUMBER (day), TO_NUMBER (id);
Результат
ID CUSTOMER_ID RISK DAY DAY_MINUS_DAY0 TOTAL_RISK
_____ ______________ _______ ______ _________________ _____________
1 102 15 1 1 13.77778
2 102 16 1 1 13.77778
4 102 17 2 2 13.77778
5 102 10 2 2 13.77778
6 102 13 3 3 13.77778
3 104 11 1 1 14.25
9 104 17 1 1 14.25
7 104 14 2 2 14.25
8 104 13 2 2 14.25
10 104 16 2 2 14.25
Пожалуйста, предоставьте некоторые образцы данных в текстовом формате (не изображение) или вставьте операторы для создания таблицы, а также ожидаемый результат от этих образцов данных. Кроме того, есть ли в таблице какой-либо первичный ключ?