113 lines
3.6 KiB
Python
113 lines
3.6 KiB
Python
#!/usr/bin/env python
|
|
# coding: utf-8
|
|
|
|
# In[ ]:
|
|
|
|
|
|
from datetime import datetime, timedelta
|
|
import pandas as pd
|
|
from tms_data_interface import SQLQueryInterface
|
|
|
|
query = """
|
|
WITH time_windows AS (
|
|
SELECT
|
|
-- End time is the current trade time
|
|
date_time AS end_time,
|
|
|
|
-- Subtract seconds from the end_time using date_add() with negative integer interval
|
|
date_add('second', -{time_window_s}, date_time) AS start_time,
|
|
|
|
-- Trade details
|
|
trade_price,
|
|
trade_volume,
|
|
trader_id,
|
|
|
|
-- Calculate minimum price within the time window
|
|
MIN(trade_price) OVER (
|
|
ORDER BY date_time
|
|
RANGE BETWEEN INTERVAL '{time_window_s}' SECOND PRECEDING AND CURRENT ROW
|
|
) AS min_price,
|
|
|
|
-- Calculate maximum price within the time window
|
|
MAX(trade_price) OVER (
|
|
ORDER BY date_time
|
|
RANGE BETWEEN INTERVAL '{time_window_s}' SECOND PRECEDING AND CURRENT ROW
|
|
) AS max_price,
|
|
|
|
-- Calculate total trade volume within the time window
|
|
SUM(trade_volume) OVER (
|
|
ORDER BY date_time
|
|
RANGE BETWEEN INTERVAL '{time_window_s}' SECOND PRECEDING AND CURRENT ROW
|
|
) AS total_volume,
|
|
|
|
-- Calculate participant's trade volume within the time window
|
|
SUM(CASE WHEN trader_id = trader_id THEN trade_volume ELSE 0 END) OVER (
|
|
PARTITION BY trader_id
|
|
ORDER BY date_time
|
|
RANGE BETWEEN INTERVAL '{time_window_s}' SECOND PRECEDING AND CURRENT ROW
|
|
) AS participant_volume
|
|
FROM
|
|
{trade_data_1b}
|
|
)
|
|
SELECT
|
|
-- Select the time window details
|
|
start_time,
|
|
end_time,
|
|
|
|
-- Select the participant (trader) ID
|
|
trader_id AS "Participant",
|
|
|
|
-- Select the calculated min and max prices
|
|
min_price,
|
|
max_price,
|
|
|
|
-- Calculate the price change percentage
|
|
(max_price - min_price) / NULLIF(min_price, 0) * 100 AS "Price Change (%)",
|
|
|
|
-- Calculate the participant's volume as a percentage of total volume
|
|
(participant_volume / NULLIF(total_volume, 0)) * 100 AS "Volume (%)",
|
|
|
|
-- Participant volume
|
|
participant_volume,
|
|
|
|
-- Select the total volume within the window
|
|
total_volume AS "Total Volume"
|
|
FROM
|
|
time_windows
|
|
"""
|
|
|
|
|
|
from tms_data_interface import SQLQueryInterface
|
|
|
|
class Scenario:
|
|
seq = SQLQueryInterface(schema="trade_schema")
|
|
def logic(self, **kwargs):
|
|
validation_window = kwargs.get('validation_window', 300000)
|
|
time_window_s = int(validation_window/1000)
|
|
query_start_time = datetime.now()
|
|
print("Query start time :",query_start_time)
|
|
row_list = self.seq.execute_raw(query.format(trade_data_1b="trade_10m_v3",
|
|
time_window_s = time_window_s)
|
|
)
|
|
cols = [
|
|
'START_DATE_TIME',
|
|
'END_DATE_TIME',
|
|
'Focal_id',
|
|
'MIN_PRICE',
|
|
'MAX_PRICE',
|
|
'PRICE_CHANGE_PCT',
|
|
'PARTICIPANT_VOLUME_PCT',
|
|
'PARTICIPANT_VOLUME',
|
|
'TOTAL_VOLUME',
|
|
]
|
|
final_scenario_df = pd.DataFrame(row_list, columns = cols)
|
|
final_scenario_df['PARTICIPANT_VOLUME_PCT'] = final_scenario_df['PARTICIPANT_VOLUME']/\
|
|
final_scenario_df['TOTAL_VOLUME'] * 100
|
|
final_scenario_df['Segment'] = 'Default'
|
|
final_scenario_df['SAR_FLAG'] = 'N'
|
|
final_scenario_df['Risk'] = 'Medium Risk'
|
|
final_scenario_df.dropna(inplace=True)
|
|
# final_scenario_df['RUN_DATE'] = final_scenario_df['END_DATE']
|
|
return final_scenario_df
|
|
|