#sql #sql-server #tsql
#sql #sql-сервер #tsql
Вопрос:
У нас есть таблица, в которой указаны ставки игроков и выигрыши в игровых автоматах. Данные, которые мы получаем, содержат транзакции ставок и выигрышей в отдельных строках, и мы хотим иметь возможность помещать их в одну строку, чтобы мы могли приписать каждый выигрыш ставке, из которой он получен.
Ниже приведен пример данных
CREATE TABLE #temp1 (PlayerID smallint,GameID smallint,GamePlayDateTime varchar(20),StakeGBP money,WinGBP money);
INSERT INTO #temp1 (PlayerID,GameID,GamePlayDateTime,StakeGBP,WinGBP)
VALUES
('123', '321', '2019-08-20 10:21:38', '0.4', '0.0'),
('123', '321', '2019-08-20 10:21:41', '0.0', '0.6'),
('123', '321', '2019-08-20 10:21:44', '0.4', '0.0'),
('123', '321', '2019-08-20 10:21:46', '0.4', '0.0'),
('123', '321', '2019-08-20 10:21:49', '0.0', '1.2'),
('123', '321', '2019-08-20 10:21:51', '0.0', '0.2'),
('123', '321', '2019-08-20 10:21:54', '0.0', '1.0'),
('123', '321', '2019-08-20 10:21:56', '0.4', '0.0'),
('824', '321', '2019-08-20 10:21:39', '0.2', '0.0'),
('824', '321', '2019-08-20 10:21:42', '0.0', '0.8'),
('824', '321', '2019-08-20 10:21:45', '0.0', '0.4'),
('824', '321', '2019-08-20 10:21:47', '0.2', '0.0'),
('824', '321', '2019-08-20 10:21:50', '0.0', '1.2'),
('824', '321', '2019-08-20 10:21:53', '0.2', '0.0'),
('824', '321', '2019-08-20 10:21:55', '0.2', '0.0');
Со следующим выводом
PlayerID GameID GamePlayDateTime StakeGBP WinGBP
-------- ------ -------------------- --------------------- ---------------------
123 321 2019-08-20 10:21:38 0.40 0.00
123 321 2019-08-20 10:21:41 0.00 0.60
123 321 2019-08-20 10:21:44 0.40 0.00
123 321 2019-08-20 10:21:46 0.40 0.00
123 321 2019-08-20 10:21:49 0.00 1.20
123 321 2019-08-20 10:21:51 0.00 0.20
123 321 2019-08-20 10:21:54 0.00 1.00
123 321 2019-08-20 10:21:56 0.40 0.00
824 321 2019-08-20 10:21:39 0.20 0.00
824 321 2019-08-20 10:21:42 0.00 0.80
824 321 2019-08-20 10:21:45 0.00 0.40
824 321 2019-08-20 10:21:47 0.20 0.00
824 321 2019-08-20 10:21:50 0.00 1.20
824 321 2019-08-20 10:21:53 0.20 0.00
824 321 2019-08-20 10:21:55 0.20 0.00
Если ставка не приводит к выигрышу, следующая строка является последующей ставкой. Любая выигрышная транзакция проходит в строке после ставки, которой она принадлежит, со StakeGBP = 0.
В приведенном выше примере мы хотим, чтобы выигрыш 0,6 в 10:21:41 был на той же строке, что и ставка 0,4 в 10:21:38 (предыдущая строка). Возвращаемое значение GamePlayDateTime не имеет значения (может быть либо временем ставки, либо временем выигрыша), мы просто хотим знать, что ставка 0.40 принесла выигрыш 0.60.
Ранее я мог использовать функцию задержки, чтобы взять ставку из предыдущей строки, где выигрыш равен> 0.
Проблема, с которой я сталкиваюсь, заключается в том, что бывают случаи, когда одна ставка может привести к нескольким выигрышам. Мы хотим иметь возможность суммировать эти выигрыши и размещать их в той же строке, что и ставка.
В приведенном выше примере ставка 0,40 в 10:21:46 приводит к выигрышам в 1,2, 0,2 и 1,0 до следующей ставки 0,4 в 10: 21: 56.
Что мы хотим сделать, так это просуммировать эти ставки, чтобы получить выигрыш 2,4 и получить этот выигрыш в той же строке со ставкой 0,4, из которой он был получен.
Желаемые результаты из приведенного выше примера будут
PlayerID GameID GamePlayDateTime StakeGBP WinGBP
-------- ------ -------------------- --------------------- ---------------------
123 321 2019-08-20 10:21:38 0.40 0.60
123 321 2019-08-20 10:21:44 0.40 0.00
123 321 2019-08-20 10:21:46 0.40 2.40
123 321 2019-08-20 10:21:56 0.40 0.00
824 321 2019-08-20 10:21:39 0.20 1.20
824 321 2019-08-20 10:21:47 0.20 1.20
824 321 2019-08-20 10:21:53 0.20 0.00
824 321 2019-08-20 10:21:55 0.20 0.00
Если ставка не приводит к выигрышу, столбец WinGBP в строке ставки равен 0.
Там, где ставка приводит к одному выигрышу, столбец WinGBP показывает выигрыш в той же строке, что и ставка (например, первый выигрыш 0,6)
В тех случаях, когда ставка приводит к нескольким выигрышам, эти выигрыши суммируются и отображаются в той же строке, что и ставка (например, выигрыш 2,40, который получается из отдельных выигрышей 1,2, 0,2 и 1,0)
Я не понял, как использовать задержку при попытке вернуть значения из более чем одной строки.
Я также пытался использовать SUM OVER, чтобы получить текущую сумму для нескольких выигрышей, но не смог понять, как ее разделить. Текущий итог должен быть остановлен, когда значение WinGBP = 0, поскольку это сигнализирует о том, что была установлена новая ставка.
Если выигрыша нет, мы просто хотим вернуть ставку. Если есть один выигрыш, мы хотим вернуть ставку (из предыдущей строки) и выигрыш. Если выигрышей несколько, мы хотим вернуть ставку (из строки до первого выигрыша), а затем объединить выигрыши в один. Затем агрегацию необходимо будет остановить при последнем выигрыше.
Надеюсь, это имеет смысл. Любая помощь будет принята с благодарностью! Спасибо
Комментарии:
1. Я понимаю, что следую логике, когда кто-то какие деньги. Я не следую логике для строк, где нет выигрыша.
2. Кроме того: почему все значения
smallintиmoneyв вашем примере данных заключены в кавычки?
Ответ №1:
Это интересный вариант проблемы с пробелами и островами, когда вы хотите сгруппировать только выигрышные строки вместе.
Вы можете определить острова как сумму ставок до каждой строки. Тогда остальное — это в основном агрегирование и фильтрация:
select playerid, gameid, min(GamePlayDateTime) as GamePlayDateTime,
sum(WinGBP) as WinGBP,
sum(StakeGBP)) over (partition by playerid, gameid order by min(GamePlayDateTime) as prev_StakeGBP
from (select t.*,
sum(StakeGBP) over (partition by playerid, gameid order by GamePlayDateTime) as grp
from temp1 t
) t
group by playerid, gameid, grp, StakeGBP
order by playerid, gameid, GamePlayDateTime;
Вот скрипка SQL.
Ответ №2:
with data as (
select *,
sum(case when StakeGBP > 0 then 1 end)
over (partition by PlayerID, GameID order by GamePlayDateTime) as StakeNum
from temp1
)
select
PlayerID,
GameID,
min(GamePlayDateTime) as GamePlayDateTime,
max(StakeGBP) as StakeGBP,
sum(WinGBP) as WinGBP
from data
group by PlayerID, GameID, StakeNum
order by PlayerID, GameID, GamePlayDateTime;
РЕДАКТИРОВАТЬ: скорректировано для обработки игроков и игр отдельно.