def using_rf_model():
start = collect_date + '0000'
end = collect_date + '2359'
sql = f"""
SELECT *
FROM AH_USE_LOG_BYMINUTE
WHERE 1=1
AND GATEWAY_ID = '{gateway_id}'
AND DEVICE_ID = '{device_id}'
AND CONCAT( COLLECT_DATE, COLLECT_TIME) >= DATE_FORMAT( DATE_ADD( STR_TO_DATE( '{start}', '%Y%m%d%H%i'),INTERVAL -20 MINUTE), '%Y%m%d%H%i')
AND CONCAT( COLLECT_DATE, COLLECT_TIME) <= DATE_FORMAT( DATE_ADD( STR_TO_DATE( '{end}', '%Y%m%d%H%i'),INTERVAL 10 MINUTE), '%Y%m%d%H%i')
ORDER BY COLLECT_DATE, COLLECT_TIME"""
pyperclip.copy(sql)
with settings.open_db_connection() as conn:
df = pd.read_sql(sql, con=conn)
print(df.columns)
x, y = split_x_y(df, x_col='ENERGY_DIFF')
pre = 20
post = 10
length = post + pre
x = [x[i:i + length] for i in range(len(x) - (pre + post))]
model = load(f'./joblib/status/{device_id}_labeling.joblib')
y = model.predict(x)
y = [int(x) for x in y]
return y
def cbl_info(house_no, request_dr_no):
sql = f"""
SELECT sum(s)/4
from ( SELECT *
FROM ( SELECT
sum(ENERGY_DIFF) s
FROM AH_USE_LOG_BYMINUTE
WHERE 1=1
AND GATEWAY_ID = (
SELECT GATEWAY_ID
FROM AH_GATEWAY_INSTALL
WHERE 1=1
AND HOUSE_NO = '{house_no}'
)
AND COLLECT_DATE >= DATE_FORMAT(DATE_ADD((SELECT START_DATE FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}'), INTERVAL -5 DAY), '%Y%m%d')
AND COLLECT_DATE < (SELECT DATE_FORMAT(START_DATE, '%Y%m%d') FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}')
AND COLLECT_TIME >= (SELECT DATE_FORMAT(START_DATE, '%H%i') FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}')
AND COLLECT_TIME <= (SELECT DATE_FORMAT(END_DATE, '%H%i') FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}')
GROUP BY
COLLECT_DATE) t1
WHERE 1=1
ORDER BY s desc
limit 4) t2"""
with settings.open_db_connection() as conn:
cbl = pd.read_sql(sql, con=conn).iloc[0, 0]
if cbl <= 500:
reduction_energy = cbl * 0.3
elif cbl <= 1500:
reduction_energy = cbl * 0.15 + 75
else:
reduction_energy = 300
return (cbl, reduction_energy)
def make_model_status(device_id, lag=10):
sql = f"""
SELECT
GATEWAY_ID
, DEVICE_ID
, COLLECT_DATE
, COLLECT_TIME
, QUALITY
, ONOFF
, ENERGY
, ENERGY_DIFF
, case when APPLIANCE_STATUS is null then 0 else APPLIANCE_STATUS end APPLIANCE_STATUS
, CREATE_DATE
FROM
AH_USE_LOG_BYMINUTE
WHERE
1 = 1
AND DEVICE_ID = '{device_id}'
AND COLLECT_DATE in (
SELECT
t1.COLLECT_DATE
FROM
(SELECT
COLLECT_DATE
, sum(APPLIANCE_STATUS) APPLIANCE_STATUS_SUM
FROM
AH_USE_LOG_BYMINUTE
GROUP by
COLLECT_DATE) t1
WHERE 1=1
AND t1.APPLIANCE_STATUS_SUM is not null)"""
pyperclip.copy(sql)
with settings.open_db_connection() as conn:
df = pd.read_sql(sql, con=conn)
print(df.head())
x, y = dl.split_x_y(df, x_col='ENERGY_DIFF', y_col='APPLIANCE_STATUS')
x, y = dl.sliding_window_transform(x, y, lag=lag, step_size=30)
model, params = dl.select_classification_model('random forest')
gs = sk.model_selection.GridSearchCV(estimator=model,
param_grid=params,
cv=5,
scoring='accuracy',
n_jobs=-1)
gs.fit(x, y)
df = df.iloc[:-lag]
df.loc[:, 'appliance_status_predicted'] = gs.predict(x)
dump_path = f'./joblib/status/{device_id}_labeling.joblib'
dump(gs, dump_path) # 저장
print(df)
return dump_path, gs.best_score_
def get_dr_info(request_dr_no):
sql = f"""
SELECT *
FROM AH_DR_REQUEST
WHERE 1=1
AND REQUEST_DR_NO = '{request_dr_no}'"""
with settings.open_db_connection() as conn:
df = pd.read_sql(sql, con=conn)
dr_type = df.iloc[0, 1]
duration = int((df.iloc[0, 3] - df.iloc[0, 2]).seconds / 60)
return dr_type, duration
def check_weeks(gateway_id, device_id):
sql = f"""
SELECT
CEIL(DATEDIFF(NOW(), DATE_FORMAT(min(COLLECT_DATE), '%Y%m%d'))/7)
FROM AH_USE_LOG_BYMINUTE
WHERE 1=1
AND GATEWAY_ID='{gateway_id}'
AND DEVICE_ID ='{device_id}'
"""
with settings.open_db_connection() as conn:
weeks = pd.read_sql(sql, con=conn).iloc[0, 0]
return weeks
def make_model_elec(house_no):
today = datetime.datetime.now().strftime('%Y%m%d')
sql = f"""
SELECT *
FROM AH_USAGE_DAILY_PREDICT
WHERE 1=1
AND HOUSE_NO = {house_no}
-- AND USE_DATE >= DATE_FORMAT( DATE_ADD( STR_TO_DATE( '{today}', '%Y%m%d'),INTERVAL -28 DAY), '%Y%m%d')""" # 최근 28주만 선택
pyperclip.copy(sql)
with settings.open_db_connection() as conn:
df = pd.read_sql(sql, con=conn)
df.loc[df.USE_ENERGY_DAILY.isnull(), 'USE_ENERGY_DAILY'] = 0
print(df.head())
x, y = dl.split_x_y(df,
x_col='USE_ENERGY_DAILY',
y_col='USE_ENERGY_DAILY')
x, y = dl.sliding_window_transform(x, y, step_size=7, lag=0)
x = x[6:-1]
y = y[7:]
model, param = dl.select_regression_model('linear regression')
gs = sk.model_selection.GridSearchCV(estimator=model,
param_grid=param,
cv=5,
n_jobs=-1)
gs.fit(x, y)
dump(gs, f'./joblib/usage_daily/{house_no}.joblib')
print("complete")
return gs.best_score_
def predict_elec(house_no, date):
sql = f"""
SELECT
*
FROM
AH_USAGE_DAILY_PREDICT
WHERE 1=1
AND HOUSE_NO = '{house_no}'
AND USE_DATE >= DATE_FORMAT( DATE_ADD( STR_TO_DATE( '{date}', '%Y%m%d'),INTERVAL -7 DAY), '%Y%m%d')
AND USE_DATE < '{date}'
ORDER BY
USE_DATE"""
with settings.open_db_connection() as conn:
df = pd.read_sql(sql, con=conn)
# elec = [x for x in df.use_energy_daily.values[-7:]]
elec = [x for x in df.USE_ENERGY_DAILY.values[-7:]]
model = load(f'./joblib/usage_daily/{house_no}.joblib') # 여기서 오류 발생.
y = iter_predict(x=elec, n_iter=31, model=model)
return y
def device_info(device_name=None,
gateway_id=None,
gateway_name=None,
house_name=None,
house_id=None,
energy_info=None):
def sql():
device_info_sql = f"""
SELECT *
FROM
(SELECT
t01.DEVICE_ID
, t01.DEVICE_NAME
, t06.APPLIANCE_NAME
, t01.DEVICE_TYPE
, t01.FLAG_USE_AI
, t02.GATEWAY_ID
, t03.APPLIANCE_NO
, t04.HOUSE_NO
, t05.HOUSE_NAME
FROM
AH_DEVICE t01
INNER JOIN
AH_DEVICE_INSTALL t02
ON t01.DEVICE_ID = t02.DEVICE_ID
INNER JOIN
AH_APPLIANCE_CONNECT t03
ON t01.DEVICE_ID = t03.DEVICE_ID
INNER JOIN
AH_GATEWAY_INSTALL t04
ON t03.GATEWAY_ID = t04.GATEWAY_ID
INNER JOIN
AH_HOUSE t05
ON t04.HOUSE_NO = t05.HOUSE_NO
INNER JOIN
AH_APPLIANCE t06
ON t03.APPLIANCE_NO = t06.APPLIANCE_NO
WHERE 1=1
AND t06.FLAG_DELETE = 'N'
AND t01.FLAG_DELETE = 'N'
AND t03.FLAG_DELETE = 'N') t
WHERE 1=1"""
if device_name is not None:
device_name_condition = f"\nAND DEVICE_NAME like '%{device_name}%'"
device_info_sql += device_name_condition
if gateway_id is not None:
gateway_id_condition = f"\nAND GATEWAY_ID = '{gateway_id}'"
device_info_sql += gateway_id_condition
if gateway_name is not None:
gateway_name_condition = f"\nAND GATEWAY_NAME = '{gateway_name}'"
device_info_sql += gateway_name_condition
if house_name is not None:
house_name_condition = f"\nAND HOUSE_NAME like '%{house_name}%'"
device_info_sql += house_name_condition
if house_id is not None:
house_id_condition = f""
device_info_sql += house_id_condition
return device_info_sql
with settings.open_db_connection() as conn:
df = pd.read_sql(sql(), con=conn)
# settings.conn.close()
return df
def usage_log(device_id,
gateway_id=None,
start_date='20191128',
end_date=None,
start_time='0000',
end_time='2359',
dayofweek=None,
raw_data=False,
sql_print=False,
power=False,
threshold=1):
"""
:param device_id:
:param gateway_id:
:param start_date:
:param end_date:
:param start_time:
:param end_time:
:param dayofweek:
:param raw_data:
:param sql_print:
:return:
"""
def make_sql():
sql = f"""
SELECT
STR_TO_DATE(CONCAT(COLLECT_DATE, COLLECT_TIME), '%Y%m%d%H%i') DATETIME
, POWER
, ENERGY_DIFF
, ONOFF
, APPLIANCE_STATUS
FROM AH_USE_LOG_BYMINUTE
WHERE 1=1
AND DEVICE_ID = '{device_id}'"""
if power:
sql = f"""
SELECT
STR_TO_DATE(CONCAT(COLLECT_DATE, COLLECT_TIME), '%Y%m%d%H%i') DATETIME
, POWER
, ENERGY_DIFF
, ONOFF
, CASE WHEN POWER >= {threshold} THEN 1 ELSE 0 END APPLIANCE_STATUS
FROM AH_USE_LOG_BYMINUTE
WHERE 1=1
AND DEVICE_ID = '{device_id}'"""
if gateway_id is not None:
gateway_id_condition = f"\nAND GATEWAY_ID = '{gateway_id}'"
sql += gateway_id_condition
start_date_condition = f"\nAND COLLECT_DATE >= '{start_date}'"
sql += start_date_condition
if end_date is not None:
end_date_condition = f"\nAND COLLECT_DATE <= '{end_date}'"
sql += end_date_condition
start_time_condition = f"\nAND COLLECT_TIME >= '{start_time}'"
sql += start_time_condition
end_time_condition = f"AND COLLECT_TIME <= '{end_time}'"
sql += end_time_condition
if dayofweek is not None:
dayofweek_condition = f"AND DAYOFWEEK(COLLECT_DATE) = {dayofweek}"
sql += dayofweek_condition
return sql
def transform(df):
df['APPLIANCE_STATUS_LAG'] = df.APPLIANCE_STATUS.shift(1).fillna(0)
df['APPLIANCE_STATUS_LAG'] = [int(x) for x in df.APPLIANCE_STATUS_LAG]
df['APPLIANCE_STATUS'] = df.APPLIANCE_STATUS.fillna(0)
df['APPLIANCE_STATUS'] = [int(x) for x in df.APPLIANCE_STATUS]
df_change = df.loc[df.APPLIANCE_STATUS != df.APPLIANCE_STATUS_LAG, :]
df_change = df_change.reset_index()
df_change['DATETIME_LAG'] = df_change.DATETIME.shift(1).fillna(0)
df_change['duration'] = df_change.DATETIME - df_change.DATETIME.shift(
1)
print(df_change)
df_change['duration'] = [
x.days * 1440 + x.seconds / 60 for x in df_change.duration
if x != 'NaT'
]
history = df_change.loc[:, [
'DATETIME_LAG', 'DATETIME', 'duration', 'APPLIANCE_STATUS_LAG'
]]
history.columns = ['START', 'END', 'DURATION', 'STATUS']
history = history.loc[history.STATUS == 1, :].reset_index(drop=True)
return history
if sql_print:
print(make_sql())
if raw_data:
with settings.open_db_connection() as conn:
result = pd.read_sql(make_sql(), con=conn, index_col='DATETIME')
else:
with settings.open_db_connection() as conn:
df = pd.read_sql(make_sql(), con=conn, index_col='DATETIME')
result = transform(df)
# result.to_clipboard()
# settings.conn.close()
return result
def dr_recommendation():
try:
house_no = request.json['house_no']
request_dr_no = request.json['request_dr_no']
sql = f"""
SELECT sum(s)/4
from ( SELECT *
FROM ( SELECT
sum(ENERGY_DIFF) s
FROM AH_USE_LOG_BYMINUTE
WHERE 1=1
AND GATEWAY_ID = (
SELECT GATEWAY_ID
FROM AH_GATEWAY_INSTALL
WHERE 1=1
AND HOUSE_NO = '{house_no}'
)
AND COLLECT_DATE >= DATE_FORMAT(DATE_ADD((SELECT START_DATE FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}'), INTERVAL -5 DAY), '%Y%m%d')
AND COLLECT_DATE < (SELECT DATE_FORMAT(START_DATE, '%Y%m%d') FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}')
AND COLLECT_TIME >= (SELECT DATE_FORMAT(START_DATE, '%H%i') FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}')
AND COLLECT_TIME <= (SELECT DATE_FORMAT(END_DATE, '%H%i') FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}')
GROUP BY
COLLECT_DATE) t1
WHERE 1=1
ORDER BY s desc
limit 4) t2d
"""
with settings.open_db_connection() as conn:
cbl = pd.read_sql(sql, con=conn).iloc[0, 0]
if cbl <= 500:
reduction_energy = cbl * 0.3
elif cbl <= 1500:
reduction_energy = cbl * 0.15 + 75
else:
reduction_energy = 300
sql = f"""
SELECT
DEVICE_ID
, DEVICE_NAME
, FREQUENCY
, WAIT_ENERGY_AVG
, USE_ENERGY_AVG
, FLAG_USE_AI
, STATUS
, ONOFF
, ENERGY
, USE_ENERGY_AVG * (SELECT TIMESTAMPDIFF(MINUTE, START_DATE, END_DATE) FROM AH_DR_REQUEST WHERE REQUEST_DR_NO = '{request_dr_no}') as ENERGY_SUM_USE
, WAIT_ENERGY_AVG * (SELECT TIMESTAMPDIFF(MINUTE, START_DATE, END_DATE) FROM AH_DR_REQUEST WHERE REQUEST_DR_NO = '{request_dr_no}') as ENERGY_SUM_WAIT
FROM
(SELECT
FR.DEVICE_ID
, T.DEVICE_NAME
, FR.FREQUENCY
, (case when HT.WAIT_ENERGY_AVG is null then 0 else HT.WAIT_ENERGY_AVG end) WAIT_ENERGY_AVG
, (case when HT.USE_ENERGY_AVG is null then 0 else HT.USE_ENERGY_AVG end) USE_ENERGY_AVG
, T.STATUS
, T.ONOFF
, case
when (case when T.STATUS = 1 then HT.USE_ENERGY_AVG else HT.WAIT_ENERGY_AVG end) is null then 0
else (case when T.STATUS = 1 then HT.USE_ENERGY_AVG else HT.WAIT_ENERGY_AVG end) end as ENERGY
, case when FLAG_USE_AI = 'Y' then 1 else 0 end FLAG_USE_AI
FROM (
SELECT
DEVICE_ID
, sum(APPLIANCE_STATUS)
, case when sum(APPLIANCE_STATUS) is null then 0 else sum(APPLIANCE_STATUS) end FREQUENCY
FROM
(SELECT
COLLECT_DATE
, DEVICE_ID
, max(APPLIANCE_STATUS) APPLIANCE_STATUS
FROM AH_USE_LOG_BYMINUTE
WHERE 1=1
AND GATEWAY_ID = (
SELECT GATEWAY_ID
FROM AH_GATEWAY_INSTALL
WHERE 1=1
AND HOUSE_NO = '{house_no}'
)
AND DAYOFWEEK(COLLECT_DATE) = (SELECT DAYOFWEEK(DATE_FORMAT(START_DATE, '%Y%m%d')) FROM AH_DR_REQUEST WHERE REQUEST_DR_NO = '{request_dr_no}')
AND COLLECT_TIME >= (SELECT DATE_FORMAT(START_DATE, '%H%i') FROM AH_DR_REQUEST WHERE REQUEST_DR_NO = '{request_dr_no}')
AND COLLECT_TIME <= (SELECT DATE_FORMAT(END_DATE, '%H%i') FROM AH_DR_REQUEST WHERE REQUEST_DR_NO = '{request_dr_no}')
GROUP BY
COLLECT_DATE
, DEVICE_ID) t
GROUP BY
DEVICE_ID
) FR
INNER JOIN
(SELECT
DEVICE_ID
, sum(WAIT_ENERGY)/sum(WAIT_TIME) WAIT_ENERGY_AVG
, sum(USE_ENERGY)/sum(USE_TIME) USE_ENERGY_AVG
FROM AH_DEVICE_ENERGY_HISTORY
WHERE 1=1
AND GATEWAY_ID = (
SELECT GATEWAY_ID
FROM AH_GATEWAY_INSTALL
WHERE 1=1
AND HOUSE_NO = '{house_no}')
GROUP BY
DEVICE_ID) HT
ON FR.DEVICE_ID = HT.DEVICE_ID
INNER JOIN
(SELECT
gateway_id
, device_id
, device_name
, sum(onoff) onoff_sum
, count(onoff) onoff_count
, avg(POWER) power_avg
, case when sum(onoff) > 2.5 then 1 else 0 end onoff
, case when avg(POWER) > 0.5 then 1 else 0 end status -- 조정필요
FROM aihems_service_db.AH_LOG_SOCKET
WHERE 1=1
AND GATEWAY_ID = (SELECT GATEWAY_ID FROM aihems_api_db.AH_GATEWAY_INSTALL WHERE 1=1 AND HOUSE_NO = '{house_no}')
AND COLLECT_DATE = (SELECT DATE_FORMAT(NOW(), '%Y%m%d') FROM DUAL)
-- DATE_FORMAT(DATE_ADD((SELECT START_DATE FROM AH_DR_REQUEST WHERE 1=1 AND REQUEST_DR_NO = '{request_dr_no}'), INTERVAL -5 DAY), '%Y%m%d')
AND COLLECT_TIME >= DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), INTERVAL 9 HOUR), INTERVAL -5 MINUTE), '%H%i')
GROUP BY
gateway_id
, device_id
, device_name) T on (FR.DEVICE_ID = T.DEVICE_ID)
INNER JOIN
(SELECT
DEVICE_ID
, FLAG_USE_AI
FROM AH_DEVICE) QQ ON FR.DEVICE_ID = QQ.DEVICE_ID
) FF
WHERE 1=1
AND FLAG_USE_AI != 0
ORDER BY
FLAG_USE_AI asc
, STATUS desc
, ONOFF desc
, FREQUENCY desc
, ENERGY asc
"""
with settings.open_db_connection() as conn:
status = pd.read_sql(sql, con=conn)
status['ENERGY_SUM'] = [
max([x[1][0], x[1][1]]) for x in
status.loc[:, ['ENERGY_SUM_WAIT', 'ENERGY_SUM_USE']].iterrows()
]
status['ENERGY_CUMSUM'] = status.ENERGY_SUM.cumsum()
status['USE_MAX'] = cbl - reduction_energy
status['PERMISSION'] = [
x < cbl - reduction_energy for x in status.ENERGY_CUMSUM
]
subset = status.loc[:, ['DEVICE_ID', 'ENERGY_SUM', 'PERMISSION']]
subset.ENERGY_SUM = [str(x) for x in subset.ENERGY_SUM]
dr_success = subset.iloc[0, 2]
if dr_success:
recommendation = subset.to_dict('index')
dr_success = True
else:
recommendation = 0
dr_success = False
print(subset)
return jsonify({
'flag_success': True,
'dr_success': dr_success,
'cbl': str(cbl),
'reduction_energy': str(reduction_energy),
'recommendation': recommendation
})
except Exception as e:
return jsonify({'flag_success': False, 'error': str(e)})
