使用 Tensorflow 在 PostgreSQL 中进行回归分析：第三部分 - 数据分析

# Figure out how many rows to use for training, validation and test
test_rows = int((actual_rows/100) * test_pct)
validation_rows = int(((actual_rows)/100) * validation_pct)
training_rows = actual_rows - test_rows - validation_rows
 
# Split the data into input and output
input = data[columns[:-1]]
output = data[columns[-1:]]
 
# Split the input and output into training, validation and test sets
training_input = input[:training_rows]
training_output = output[:training_rows]
validation_input = input[training_rows:training_rows+validation_rows]
validation_output = output[training_rows:training_rows+validation_rows]
test_input = input[training_rows+validation_rows:]
test_output = output[training_rows+validation_rows:]
 
# Make a note of the largest result value
max_z = max(output[output.columns[0]])

# Define the model
model = tf.keras.Sequential()
 
model.add(tf.keras.layers.Dense(units=units, 
                                input_shape=(len(columns) - 1,), 
                                activation = 'relu'))
 
for units in structure:
    model.add(tf.keras.layers.Dense(units=units, activation = 'relu'))
 
model.add(tf.keras.layers.Dense(units=1, activation='linear'))
 
# Compile it
model.compile(loss=tf.keras.losses.MeanSquaredError(),
             optimizer='adam')
 
summary = []
model.summary(print_fn=lambda x: summary.append(x))
plpy.notice('Model architecture:\n{}'.format('\n'.join(summary)))

NOTICE:  Model architecture:
Model: "sequential"
_________________________________________________________________
Layer (type)                 Output Shape              Param #   
=================================================================
dense (Dense)                (None, 13)                182       
_________________________________________________________________
dense_1 (Dense)              (None, 13)                182       
_________________________________________________________________
dense_2 (Dense)              (None, 1)                 14        
=================================================================
Total params: 378
Trainable params: 378
Non-trainable params: 0

# Save a checkpoint each time our loss metric improves.
checkpoint = ModelCheckpoint('{}/{}.h5'.format(output_path, output_name),
                            monitor='loss',
                            save_best_only=True,
                            mode='min')
 
# Use early stopping
early_stopping = EarlyStopping(patience=50)
 
# Display output
logger = LambdaCallback(
   on_epoch_end=lambda epoch,
   logs: plpy.notice(
       'epoch: {}, training RMSE: {} ({}%), validation RMSE: {} ({}%)'.\
       format(
           epoch,
           sqrt(logs['loss']),
           round(100 / max_z * sqrt(logs['loss']), 5),
           sqrt(logs['val_loss']),
           round(100 / max_z * sqrt(logs['val_loss']), 5))))
 
# Train it!
history = model.fit(training_input,
                   training_output,
                   validation_data=(validation_input, 
                                    validation_output),
                   epochs=epochs,
                   batch_size=50,
                   callbacks=[logger, checkpoint, early_stopping])

NOTICE:  epoch: 0, training RMSE: 34.744217425799796 (95.9785%), validation RMSE: 66.97870901217416 (185.02406%)
NOTICE:  epoch: 1, training RMSE: 20.016233341165083 (55.29346%), validation RMSE: 86.36728608372935 (238.58366%)
NOTICE:  epoch: 2, training RMSE: 20.81612147077206 (57.5031%), validation RMSE: 74.53097811795274 (205.88668%)
NOTICE:  epoch: 3, training RMSE: 15.706952561354747 (43.38937%), validation RMSE: 50.19628950897417 (138.66378%)
...
NOTICE:  epoch: 88, training RMSE: 4.051587516569147 (11.19223%), validation RMSE: 14.049098431858425 (38.80966%)
NOTICE:  epoch: 89, training RMSE: 4.043822798154733 (11.17078%), validation RMSE: 14.405585350211444 (39.79443%)
NOTICE:  epoch: 90, training RMSE: 4.039318438155652 (11.15834%), validation RMSE: 13.547520957602528 (37.42409%)
NOTICE:  epoch: 91, training RMSE: 4.0253103101067085 (11.11964%), validation RMSE: 14.17337045065995 (39.15296%)

# Graph the results
training_loss = history.history['loss']
validation_loss = history.history['val_loss']
 
epochs_range = range(len(history.history['loss']))
 
plt.figure(figsize=(12, 8))
plt.grid(True)
plt.plot(epochs_range, 
         [x ** 0.5 for x in training_loss], 
         label='Training')
plt.plot(epochs_range, 
         [x ** 0.5 for x in validation_loss], 
         label='Validation')
plt.xlabel('Epoch')
plt.ylabel('Root Mean Squared Error')
plt.legend(loc='upper right')
plt.title('Training and Validation Root Mean Squared Error')
plt.savefig('{}/{}_rmse.png'.format(output_path, output_name))
plpy.notice('Created: {}/{}_rmse.png\n'.format(output_path, 
                                               output_name))

CREATE OR REPLACE FUNCTION public.tf_predict(
    input_values double precision[],
    model_path text)
  RETURNS double precision[]
  LANGUAGE 'plpython3u'
  COST 100
  VOLATILE PARALLEL UNSAFE
AS $BODY$
import tensorflow as tf
 
# Reset everything
tf.keras.backend.clear_session()
tf.random.set_seed(42)
 
# Load the model
model = tf.keras.models.load_model(model_path)
 
# Are we dealing with a single prediction, or a list of them?
if not any(isinstance(sub, list) for sub in input_values):
   data = [input_values]
else:
   data = input_values
 
# Make the prediction(s)
result = model.predict([data])[0]
result = [ item for elem in result for item in elem]
 
return result
$BODY$;