Equinor Volve Log ML

Posted in Projectblog ∙ 40 mins read

Work with real Operator Data!

Share:   Twitter   Reddit   LinkedIn   Email

1. Background
2. Data
3. Problem
4. Goal
5. Processing
   1. Imports
   2. Load data
   3. Data Preparation
   4. Exploratory Data Analysis
      1. Pair-plot of the Train Data
      2. Spearman’s Correlation Heatmap
   5. Transformation of the Train Data
      1. Pair-Plot (after transformation)
   6. Removing Outliers
      1. Method 1: Standard Deviation
      2. Method 2: Isolation Forest
      3. Method 3: Minimum Covariance Determinant
      4. Method 4: Local Outlier Factor
      5. Method 5: Support Vector Machine
   7. Train and Validate
      1. Fit to Test and Score on Val
      2. Inverse Transformation of Prediction
   8. Hyperparameter Tuning
   9. Predict Test Wells
      1. Define the Test Data
      2. Transform Test - Predict - Inverse Transform
      3. Plot the Predictions

This repository is my exploration on bringing machine learning to the Equinor “Volve” geophysical/geological dataset, which was opened up to the public in 2018.

For more information about this open data set, its publishing licence, and how to obtain the original version please visit Equinor’s Data Village.

The full dataset contains ~ 40,000 files and the public is invited to learn from it. It is on my to-do-list to explore more data from this treasure trove. In fact, the data is not very much explored yet in short the time frame since Volve became open data and likely still holds many secrets. The good thing is that certain data workflows can be re-used from log to log, well to well, etc. So see this as a skeleton blue print with ample space for adaptation as it fits your goals. Some people may even create their bespoke Object-Oriented workflow for processsing automation and best time savings in repetitious work such as this large data set.

The analysis in this post was inspired in parts by the geophysicist Yohanes Nuwara (see here). He also wrote a TDS article see here on the topic, that’s worth your time if you want to dive in deeper!

More on the topic:

• Alfonso R. Reyes Blog: The impact of the Volve dataset
• Agile Scientific Blog: An update on Volve
• Agile Scientific Blog: Big open data… or is it?

The full repository including Jupyter Notebook, data, and results of what you see below can be found here.

Background

The Volve field was operated by Equinor in the Norwegian North Sea between 2008—2016. Located 200 kilometres west of Stavanger (Norway) at the southern end of the Norwegian sector, was decommissioned in September 2016 after 8.5 years in operation. Equinor operated it more than twice as long as originally planned. The development was based on production from the Mærsk Inspirer jack-up rig, with Navion Saga used as a storage ship to hold crude oil before export. Gas was piped to the Sleipner A platform for final processing and export. The Volve field reached a recovery rate of 54% and in March 2016 when it was decided to shut down its production permanently. Reference.

Data

Wireline-log files (.LAS) of five wells:

• Log 1: 15_9-F-11A
• Log 2: 15_9-F-11B
• Log 3: 15_9-F-1A
• Log 4: 15_9-F-1B
• Log 5: 15_9-F-1C

The .LAS files contain the following feature columns:

Problem

Wells 15/9-F-11B (log 2) and 15/9-F-1C (log 5) lack the DT Sonic Log feature.

Goal

Predict Sonic Log (DT) feature in these two wells.

Processing

Imports

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
import itertools

import lasio

import glob
import os
import md_toc

Load data

LAS is an ASCII file type for borehole logs. It contains:

1. the header with detailed information about a borehole and column descriptions and
2. the main body with the actual data.

The package LASIO helps parsing and writing such files in python. Reference: https://pypi.org/project/lasio/

# Find paths to the log files (MS windows path style)
paths = sorted(glob.glob(os.path.join(os.getcwd(),"well_logs", "*.LAS")))

# Create a list for loop processing
log_list = [0] * len(paths)

# Parse LAS with LASIO to create pandas df
for i in range(len(paths)):
  df = lasio.read(paths[i])
  log_list[i] = df.df()
  # this transforms the depth from index to regular column
  log_list[i].reset_index(inplace=True)

log_list[0].head()

# Save logs from list of dfs into separate variables
log1, log2, log3, log4, log5 = log_list

# Helper function for repeated plotting

def makeplot(df,suptitle_str="pass a suptitle"):

  # Lists of used columns and colors
  columns = ['NPHI', 'RHOB', 'GR', 'RT', 'PEF', 'CALI', 'DT']
  colors = ['red', 'darkblue', 'black', 'green', 'purple', 'brown', 'turquoise']

  # determine how many columns are available in the log (some miss 'DT')
  col_counter = 0
  for i in df.columns:
    if i in columns:
      col_counter+=1

  # Create the subplots
  fig, ax = plt.subplots(nrows=1, ncols=col_counter, figsize=(col_counter*2,10))
  fig.suptitle(suptitle_str, size=20, y=1.05)

  # Looping each log to display in the subplots
  for i in range(col_counter):
    if i == 3:
      # semilog plot for resistivity ('RT')
      ax[i].semilogx(df[columns[i]], df['DEPTH'], color=colors[i])
    else:
      # all other -> normal plot
      ax[i].plot(df[columns[i]], df['DEPTH'], color=colors[i])
  
    ax[i].set_title(columns[i])
    ax[i].grid(True)
    ax[i].invert_yaxis()

  plt.tight_layout() #avoids label overlap
  plt.show()

makeplot(log1,"Log 1 15_9-F-11A")

makeplot(log2, "Log 2 15_9-F-11B")

Data Preparation

1. The train test split is easy, the data is already partitioned by wells:
   • Train on logs 1, 3, and 4.
   • Test (validation) on logs 2 and 5.
2. There are many NAN values in the logs. The plots above only display the samples that are non-NaN and thus can be used to gauge where they need to be clipped. The NaN are primarily present on top and bottom of each log before readings start. The logs get clipped around the following depths:
   • log1 2,600 - 3,720 m
   • log2 3,200 - 4,740 m
   • log3 2,620 - 3,640 m
   • log4 3,100 - 3,400 m
   • log5 3,100 - 4,050 m
3. Furthermore, the logs contain many more featurs than we need. The correct will get selected for further use, the rest gets discarded.

# Lists of depths for clipping
lower = [2600, 3200, 2620, 3100, 3100]
upper = [3720, 4740, 3640, 3400, 4050]

# Lists of selected columns
train_cols = ['DEPTH', 'NPHI', 'RHOB', 'GR', 'RT', 'PEF', 'CALI', 'DT']
test_cols = ['DEPTH', 'NPHI', 'RHOB', 'GR', 'RT', 'PEF', 'CALI']

log_list_clipped = [0] * len(paths)

for i in range(len(log_list)):
    
  # Clip depths
  temp_df = log_list[i].loc[
      (log_list[i]['DEPTH'] >= lower[i]) & 
      (log_list[i]['DEPTH'] <= upper[i])
  ]

  # Select train-log columns
  if i in [0,2,3]:
    log_list_clipped[i] = temp_df[train_cols]
  
  # Select test-log columns
  else:
    log_list_clipped[i] = temp_df[test_cols]

# Save logs from list into separate variables
log1, log2, log3, log4, log5 = log_list_clipped
# check for NaN
log1

Next Steps:

• Concatenate the training logs into a training df and the test logs into a test df

• Assign log/well names to each sample.

• Move column location to the right.

# Concatenate dataframes
train = pd.concat([log1, log3, log4])
pred = pd.concat([log2, log5])

# Assign names
names = ['15_9-F-11A', '15_9-F-11B', '15_9-F-1A', '15_9-F-1B', '15_9-F-1C']

names_train = []
names_pred = []

for i in range(len(log_list_clipped)):
  if i in [0,2,3]:
    # Train data, assign names 
    names_train.append(np.full(len(log_list_clipped[i]), names[i]))
  else:
    # Test data, assign names
    names_pred.append(np.full(len(log_list_clipped[i]), names[i]))

# Concatenate inside list
names_train = list(itertools.chain.from_iterable(names_train))
names_pred = list(itertools.chain.from_iterable(names_pred))

# Add well name to df
train['WELL'] = names_train
pred['WELL'] = names_pred

# Pop and add depth to end of df
depth_train, depth_pred = train.pop('DEPTH'), pred.pop('DEPTH')
train['DEPTH'], pred['DEPTH'] = depth_train, depth_pred

# Train dataframe with logs 1,3,4 vertically stacked
train

# Pred dataframe with logs 2, 5 verically stacked
pred

Exploratory Data Analysis

Pair-plot of the Train Data

train_features = ['NPHI', 'RHOB', 'GR', 'RT', 'PEF', 'CALI', 'DT']

sns.pairplot(train, vars=train_features, diag_kind='kde',
             plot_kws = {'alpha': 0.6, 's': 30, 'edgecolor': 'k'})

Spearman’s Correlation Heatmap

train_only_features = train[train_features]

# Generate a mask for the upper triangle
mask = np.zeros_like(train_only_features.corr(method = 'spearman') , dtype=np.bool)
mask[np.triu_indices_from(mask)] = True

# Custom colormap
cmap = sns.cubehelix_palette(n_colors=12, start=-2.25, rot=-1.3, as_cmap=True)

# Draw the heatmap with the mask and correct aspect ratio
plt.figure(figsize=(12,10))
sns.heatmap(train_only_features.corr(method = 'spearman'), annot=True,  mask=mask, cmap=cmap, vmax=.3, square=True)

plt.show()

Transformation of the Train Data

Normalize/transform the dataset:

• Log transform the RT log
• Use power transform with Yeo-Johnson method (except ‘WELL’ and ‘DEPTH’)

# Log transform the RT to logarithmic
train['RT'] = np.log10(train['RT'])

from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import PowerTransformer

# Transformation / Normalizer object Yeo-Johnson method
scaler = PowerTransformer(method='yeo-johnson')

# ColumnTransformer (feature_target defines to which it is applied, leave Well and Depth untouched)
ct = ColumnTransformer([('transform', scaler, feature_target)], remainder='passthrough')

# Fit and transform
train_trans = ct.fit_transform(train)

# Convert to dataframe
train_trans = pd.DataFrame(train_trans, columns=colnames)
train_trans

Pair-Plot (after transformation)

sns.pairplot(train_trans, vars=feature_target, diag_kind = 'kde',
             plot_kws = {'alpha': 0.6, 's': 30, 'edgecolor': 'k'})

Removing Outliers

Outliers can be removed easily with an API call. The issue is that there are multiple methods that may work with varying efficiency. Let’s run a few and select which one performs best.

from sklearn.ensemble import IsolationForest
from sklearn.covariance import EllipticEnvelope
from sklearn.neighbors import LocalOutlierFactor
from sklearn.svm import OneClassSVM

# Make a copy of train
train_fonly = train_trans.copy()

# Remove WELL, DEPTH
train_fonly = train_fonly.drop(['WELL', 'DEPTH'], axis=1)
train_fonly_names = train_fonly.columns

# Helper function for repeated plotting

def makeboxplot(my_title='enter title',my_data=None):
    _, ax1 = plt.subplots()
    ax1.set_title(my_title, size=15)
    ax1.boxplot(my_data)
    ax1.set_xticklabels(train_fonly_names)
    plt.show()
	
makeboxplot('Unprocessed',train_trans[train_fonly_names])
print('n samples unprocessed:', len(train_fonly))

n samples unprocessed: 24,403

Method 1: Standard Deviation

train_stdev = train_fonly[np.abs(train_fonly - train_fonly.mean()) <= (3 * train_fonly.std())]

# Delete NaN
train_stdev = train_stdev.dropna()

makeboxplot('Method 1: Standard Deviation',train_stdev)
print('Remaining samples:', len(train_stdev))

Remaining samples: 24,101

Method 2: Isolation Forest

iso = IsolationForest(contamination=0.5)
yhat = iso.fit_predict(train_fonly)
mask = yhat != -1
train_iso = train_fonly[mask]

makeboxplot('Method 2: Isolation Forest',train_iso)
print('Remaining Samples:', len(train_iso))

Remaining Samples: 12,202

Method 3: Minimum Covariance Determinant

ee = EllipticEnvelope(contamination=0.1)
yhat = ee.fit_predict(train_fonly)
mask = yhat != -1
train_ee = train_fonly[mask]

makeboxplot('Method 3: Minimum Covariance Determinant',train_ee)
print('Remaining samples:', len(train_ee))

Remaining samples: 21,962

Method 4: Local Outlier Factor

lof = LocalOutlierFactor(contamination=0.3)
yhat = lof.fit_predict(train_fonly)
mask = yhat != -1
train_lof = train_fonly[mask]

makeboxplot('Method 4: Local Outlier Factor',train_lof)
print('Remaining samples:', len(train_lof))

Remaining samples: 17,082

Method 5: Support Vector Machine

svm = OneClassSVM(nu=0.1)
yhat = svm.fit_predict(train_fonly)
mask = yhat != -1
train_svm = train_fonly[mask]

makeboxplot('Method 5: Support Vector Machine',train_svm)
print('Remaining samples:', len(train_svm))

Remaining samples: 21,964

One-class SVM performs best

Make pair-plot of data after outliers removed.

sns.pairplot(train_svm, vars=feature_target,
             diag_kind='kde',
             plot_kws = {'alpha': 0.6, 's': 30, 'edgecolor': 'k'})

Train and Validate

Define the train data as the SVM outlier-removed-data.

# Select columns for features (X) and target (y)
X_train = train_svm[feature_names].values
y_train = train_svm[target_name].values

Define the validation data 
train_trans_copy = train_trans.copy()

train_well_names = ['15_9-F-11A', '15_9-F-1A', '15_9-F-1B']

X_val = []
y_val = []

for i in range(len(train_well_names)):
  # Split the df by log name
  val = train_trans_copy.loc[train_trans_copy['WELL'] == train_well_names[i]]

  # Drop name column 
  val = val.drop(['WELL'], axis=1)

  # Define X_val (feature) and y_val (target)
  X_val_, y_val_ = val[feature_names].values, val[target_name].values
  
  X_val.append(X_val_)
  y_val.append(y_val_)

# save into separate dfs
X_val1, X_val3, X_val4 = X_val
y_val1, y_val3, y_val4 = y_val

Fit to Test and Score on Val

from sklearn.metrics import mean_squared_error
from sklearn.ensemble import GradientBoostingRegressor

# Gradient Booster object
model = GradientBoostingRegressor()

# Fit the regressor to the training data
model.fit(X_train, y_train)

# Validation: Predict on well 1
y_pred1 = model.predict(X_val1)
print("R2 Log 1: {}".format(round(model.score(X_val1, y_val1),4)))
rmse = np.sqrt(mean_squared_error(y_val1, y_pred1))
print("RMSE Log 1: {}".format(round(rmse,4)))

# Validation: Predict on well 3
y_pred3 = model.predict(X_val3)
print("R2 Log 3: {}".format(round(model.score(X_val3, y_val3),4)))
rmse = np.sqrt(mean_squared_error(y_val3, y_pred3))
print("RMSE Log 3: {}".format(round(rmse,4)))

# Validation: Predict on well 4
y_pred4 = model.predict(X_val4)
print("R2 Log 4: {}".format(round(model.score(X_val4, y_val4),4)))
rmse = np.sqrt(mean_squared_error(y_val4, y_pred4))
print("RMSE Log 4: {}".format(round(rmse,4)))

R2 Log 1: 0.9526

RMSE Log 1: 0.2338

R2 Log 3: 0.9428

RMSE Log 3: 0.2211

R2 Log 4: 0.8958

RMSE Log 4: 0.2459

This R2 is relatively good with relatively little effort!

Inverse Transformation of Prediction

# Make the transformer fit to the target
y = train[target_name].values
scaler.fit(y.reshape(-1,1))

# Inverse transform  y_val, y_pred
y_val1, y_pred1 = scaler.inverse_transform(y_val1.reshape(-1,1)), scaler.inverse_transform(y_pred1.reshape(-1,1))
y_val3, y_pred3 = scaler.inverse_transform(y_val3.reshape(-1,1)), scaler.inverse_transform(y_pred3.reshape(-1,1))
y_val4, y_pred4 = scaler.inverse_transform(y_val4.reshape(-1,1)), scaler.inverse_transform(y_pred4.reshape(-1,1))

Plot a comparison between train and prediction of the DT feature.

x = [y_val1, y_pred1, y_val3, y_pred3, y_val4, y_pred4]
y = [log1['DEPTH'], log1['DEPTH'], log3['DEPTH'], log3['DEPTH'], log4['DEPTH'], log4['DEPTH']]

color = ['red', 'blue', 'red', 'blue', 'red', 'blue']
title = ['DT Log1 1', 'Pred. DT Log 1', 'DT Log 3', 'Pred DT Log 3',
         'DT Log 4', 'Pred DT Log 4']

fig, ax = plt.subplots(nrows=1, ncols=6, figsize=(15,10))

for i in range(len(x)):
  ax[i].plot(x[i], y[i], color=color[i])
  ax[i].set_xlim(50, 150)
  ax[i].set_ylim(np.max(y[i]), np.min(y[i]))
  ax[i].set_title(title[i])

plt.tight_layout()

plt.show()

Hyperparameter Tuning

This example below is GridSearchCV hyperparameter tuning on Scikit-Learn’s GradientBoostingRegressor, resulting in 31 models playing through all variations.

Different ways of searching hyperparameters are available with automated approaches of narrowing it down in a smarter way.

from sklearn.model_selection import train_test_split

# Define the X and y from the SVM normalized dataset
X = train_svm[feature_names].values
y = train_svm[target_name].values

# Train and test split
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)

The grid search will churn for several minutes and the best scoring parameter combination saved printed.

from sklearn.model_selection import GridSearchCV

model = GradientBoostingRegressor()

# Hyperparameter ranges

max_features = ['auto', 'sqrt']
min_samples_leaf = [1, 4]
min_samples_split = [2, 10]
max_depth = [10, 100]
n_estimators = [100, 1000]

param_grid = {'max_features': max_features,
              'min_samples_leaf': min_samples_leaf,
              'min_samples_split': min_samples_split,
              'max_depth': max_depth,
              'n_estimators': n_estimators}

# Train with grid
model_random = GridSearchCV(model, param_grid, cv=3)
model_random.fit(X_train, y_train)

# Print best model
model_random.best_params_

{‘max_depth’: 100,

‘max_features’: ‘sqrt’,

‘min_samples_leaf’: 4,

‘min_samples_split’: 10,

‘n_estimators’: 1000}

Predict Test Wells

Define the Test Data

# Define the test data 
names_test = ['15_9-F-11B', '15_9-F-1C']

X_test = []
y_test = []
depths = []

for i in range(len(names_test)):
  # split the df with respect to its name
  test = pred.loc[pred['WELL'] == names_test[i]]

  # Drop well name column 
  test = test.drop(['WELL'], axis=1)

  # Define X_test (feature)
  X_test_ = test[feature_names].values

  # Define depth
  depth_ = test['DEPTH'].values
  
  X_test.append(X_test_)
  depths.append(depth_)

# For each well 2 and 5
X_test2, X_test5 = X_test
depth2, depth5 = depths

Transform Test - Predict - Inverse Transform

# Transform X_test of log 2 and 5
X_test2 = scaler.fit_transform(X_test2)
X_test5 = scaler.fit_transform(X_test5)

# Predict to log 2 and 5 with tuned model
y_pred2 = model_random.predict(X_test2)
y_pred5 = model_random.predict(X_test5)

y = train[target_name].values
scaler.fit(y.reshape(-1,1))

# Inverse transform y_pred
y_pred2 = scaler.inverse_transform(y_pred2.reshape(-1,1))
y_pred5 = scaler.inverse_transform(y_pred5.reshape(-1,1))

Plot the Predictions

plt.figure(figsize=(5,12))

plt.subplot(1,2,1)
plt.plot(y_pred2, depth2, color='green')
plt.ylim(max(depth2), min(depth2))
plt.title('Pred DT Log 2: 15_9-F-11B', size=12)

plt.subplot(1,2,2)
plt.plot(y_pred5, depth5, color='green')
plt.ylim(max(depth5), min(depth5))
plt.title('Pred DT Log 5: 15_9-F-1C', size=12)

plt.tight_layout()
plt.show()

def makeplotpred(df,suptitle_str="pass a suptitle"):
  # Column selection from df
  col_names = ['NPHI', 'RHOB', 'GR', 'RT', 'PEF', 'CALI', 'DT']
  # Plotting titles
  title = ['NPHI', 'RHOB', 'GR', 'RT', 'PEF', 'CALI', 'Predicted DT']
  # plotting colors
  colors = ['purple', 'purple', 'purple', 'purple', 'purple', 'purple', 'green']

  # Create the subplots; ncols equals the number of logs
  fig, ax = plt.subplots(nrows=1, ncols=len(col_names), figsize=(15,10))
  fig.suptitle(suptitle_str, size=20, y=1.05)

  # Looping each log to display in the subplots
  for i in range(len(col_names)):
    if i == 3:
      # for resistivity, semilog plot
      ax[i].semilogx(df[col_names[i]], df['DEPTH'], color=colors[i])
    else:
      # for non-resistivity, normal plot
      ax[i].plot(df[col_names[i]], df['DEPTH'], color=colors[i])
  
    ax[i].set_ylim(max(df['DEPTH']), min(df['DEPTH']))
    ax[i].set_title(title[i], pad=15)
    ax[i].grid(True)

  ax[2].set_xlim(0, 200)
  plt.tight_layout(1)
  plt.show()

makeplotpred(log2,"Log 2: 15_9-F-11B")

makeplotpred(log5,"Log 5: 15_9-F-1C")

ADV SQL QRIES

Posted in Projectblog ∙ 47 mins read

Remind me, how did that expression go?

Share:   Twitter   Reddit   LinkedIn   Email

1. CASE
   1. CASE and aggregate function (SUM)
   2. Percentages with AVG and CASE
2. TRY queries and CATCH errors
   1. Nested TRY/CATCH
   2. CATCH Error Details
   3. Custom Errors, RAISERROR
3. Subqueries
   1. Subqueries in WHERE clause
   2. Subqueries in FROM
   3. Subqueries in SELECT
   4. Correlated Subqueries
   5. Nested Subqueries in FROM
4. Common Table Expressions (CTE)
5. Window Functions - They all use OVER()
   1. RANK()
   2. DENSE_RANK()
   3. Giving rows a number with ROW_NUMBER()
   4. Framing for sliding windows using RANGE BETWEEN…AND or ROWS BETWEEN…AND
   5. Fetching relative rows: LAG() and LEAD()
   6. Fetching absolute rows: FIRST_VALUE() and LAST_VALUE()
   7. PARTITION BY subdivides processing ranges in a column
   8. Paging with NTILE()- subdivide a table into equal-length pages
6. Pivoting with the CROSSTAB() Extension
7. Group total rows with GROUP BY ROLLUP() and CUBE()
8. Fill Null values with COALESCE()
9. String Manipulation
   1. Compress a Column into a single String with STRING_AGG()
   2. Concatenating Multiple Columns with || and CONCAT()
   3. Changing Case with UPPER(), LOWER(), INITCAP()
   4. Replace Characters with REPLACE()
   5. Trim Characters with TRIM(), LTRIM(), RTRIM()
   6. Pad Characters with PAD(), LPAD(), RPAD()
   7. Reverse char order with REVERSE()
   8. Character Count with CHAR_LENGTH() or LENGTH()
   9. Find Char Position with STRPOS() or POSITION()
   10. Clip Strings from the Ends with LEFT(), RIGHT(), and SUBSTRING()
10. Data Types
    1. Casting Data Types
    2. Creating Data Types (PostgreSQL)
    3. Getting Information about Data Types (PostgreSQL)
11. User-Defined Functions (PostgreSQL)
12. Text Search and NLP (PostgreSQL)
    1. Full-Text Search (PostgreSQL)
    2. Compare Two Strings (PostgreSQL)
13. Drop Duplicate Rows (based on Specific Columns)
14. Time Manipulation
    1. Intervals and Time Differences
    2. Extracting from Timestamps
    3. Truncating Timestamps
15. Transactions (MS SQL SERVER)
    1. With TRY/CATCH
    2. With XACT_ABORT
16. Special Case: Accessing ARRAYS
17. SQL query in Python Pandas
    1. ETL process
18. Table Management
    1. Create Table
    2. Insert Values into Columns
    3. System Database Information Gathering
19. Extension Management (PostgreSQL)
    1. Show Extensions
    2. Install/Import Extensions

The following post consists of notes I had on my drive and I had been returning to over and over to remind me of some concepts and SQL syntax.

Of course, the original tables and outputs are not available here, but the point is providing a lookup for structures and building blocks.

CASE

The CASE statement can create a new column based on multiple conditions in a specified column.

The structure always follows CASE WHEN THEN ELSE END AS. The ELSE and AS are optional

SELECT  
	m.date,
	t.team_long_name AS opponent,
	CASE WHEN m.home_goal < m.away_goal THEN 'Barcelona win!'
		 WHEN m.home_goal > m.away_goal THEN 'Barcelona loss :(' 
		 ELSE 'Tie' END AS outcome
FROM matches_spain AS m
LEFT JOIN teams_spain AS t 
ON m.hometeam_id = t.team_api_id
WHERE m.awayteam_id = 8634;

CASE and aggregate function (SUM)

The following query creates multiple new columns with a summation aggregate function.

SELECT 
	c.name AS country,
	SUM(CASE WHEN m.season = '2012/2013' AND m.home_goal > m.away_goal 
			 THEN 1 ELSE 0 END) AS matches_2012_2013,
				
	SUM(CASE WHEN m.season = '2013/2014' AND m.home_goal > m.away_goal 
			 THEN 1 ELSE 0 END) AS matches_2013_2014,
				
	SUM(CASE WHEN m.season = '2014/2015' AND m.home_goal > m.away_goal
			 THEN 1 ELSE 0 END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;

Percentages with AVG and CASE

For each country calculate the percentage of ties (home=away goals_). Use the AVG and CASE (for 1,0 assignment) functions.

SELECT 
	c.name AS country,
	ROUND(AVG(CASE WHEN m.season='2013/2014' AND m.home_goal = m.away_goal THEN 1
					WHEN m.season='2013/2014' AND m.home_goal != m.away_goal THEN 0
					END),2) AS pct_ties_2013_2014,
				   
	ROUND(AVG(CASE WHEN m.season='2014/2015' AND m.home_goal = m.away_goal THEN 1
					WHEN m.season='2014/2015' AND m.home_goal != m.away_goal THEN 0
					END),2) AS pct_ties_2014_2015
FROM country AS c
LEFT JOIN matches AS m
ON c.id = m.country_id
GROUP BY country;

TRY queries and CATCH errors

Similar as in most languages, SQL can make use of Try/Catch (sometimes called exception/error blocks).

BEGIN TRY
	ALTER TABLE products
		ADD CONSTRAINT CHK_Stock CHECK (stock >= 0);
END TRY

BEGIN CATCH
	SELECT 'There was an error adding the constraing!';
END CATCH

Nested TRY/CATCH

It is also possible to have a nested TRY block inside the CATCH block, which lets you attempt a second query if the first failed.

Use example: the nested (inner) TRY block could be used to write an error message into a table that the outer TRY failed.

BEGIN TRY
	INSERT INTO bikes (make, model, year, size)
		VALUES ('Nukeproof', 'Grizzlybear', '2021', 'S');
END TRY

BEGIN CATCH
	SELECT 'An error occurred inserting the bike!';
    BEGIN TRY
    	INSERT INTO errors 
        	VALUES ('Error inserting a bike');
        SELECT 'Error inserted correctly!';
	END TRY
	
    BEGIN CATCH
    	SELECT 'An error occurred inserting the error!';
    END CATCH 
END CATCH

CATCH Error Details

The following query writes a message to an ‘error’ table if the outer Try block fails. If that fails, too, then the error details of the inner try are queried.

BEGIN TRY 	
	INSERT INTO bikes (make, model, year, size)
		VALUES ('Nukeproof', 'Grizzlybear', '2021', 'S');  
END TRY

BEGIN CATCH
	SELECT 'An error occurred inserting the product!';
    BEGIN TRY
    	INSERT INTO errors 
        	VALUES ('Error inserting a product');
    END TRY
	
    BEGIN CATCH
    	SELECT  ERROR_NUMBER() AS number,  
        	ERROR_SEVERITY() AS severity_level,  
        	ERROR_STATE() AS state,
        	ERROR_LINE() AS line,  
        	ERROR_MESSAGE() AS message; 
    END CATCH    
END CATCH

Custom Errors, RAISERROR

‘RAISERROR’ lets you define a custom error and parameters. Syntax:

RAISERROR ( { msg_str | msg_id | @local_variable_message },  
    severity, 
    state,
    [ argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ] 

@product_id = 56747

BEGIN TRY
IF NOT EXISTS (SELECT * FROM products WHERE product_id = @product_id)
	RAISERROR('No product with id %d.', 11, 1, @product_id);
ELSE 
	SELECT * FROM products WHERE product_id = @product_id;
END TRY

BEGIN CATCH
    SELECT  ERROR_NUMBER() AS number,  
        ERROR_SEVERITY() AS severity_level,  
        ERROR_STATE() AS state,
        ERROR_LINE() AS line,  
        ERROR_MESSAGE() AS message; 
END CATCH

Subqueries

While regular subqueries can be calculated on their own, correlated subqueries cannot need results from outer queries. Queries with multiple subqueries will get complicated quickly.

Subqueries in WHERE clause

SELECT
	team_long_name,
	team_short_name
FROM team
WHERE team_api_id IN
	(SELECT hometeam_id 
	 FROM match
	 WHERE home_goal >= 8);

Subqueries in FROM

SELECT
	country,
	date,
	home_goal,
	away_goal
FROM 
	(SELECT c.name AS country, 
			m.date, 
			m.home_goal, 
			m.away_goal,
			(m.home_goal + m.away_goal) AS total_goals
	FROM match AS m
	LEFT JOIN country AS c
	ON m.country_id = c.id) AS subq
WHERE total_goals >= 10;

Subqueries in SELECT

Subqueries in SELECT statements generate a single value!

SELECT 
	l.name AS league,
   	ROUND(AVG(m.home_goal + m.away_goal), 2) AS avg_goals
	(SELECT ROUND(AVG(home_goal + away_goal), 2) 
     	FROM match
     	WHERE season = '2013/2014') AS overall_avg
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
WHERE season = '2013/2014'
GROUP BY league;

Correlated Subqueries

Correlated subqueries reference one or more columns in the main query. They dend on information in the main query to run, and thus, cannot be executed on their own. Correlated subqueries are evaluated in SQL once per row of data retrieved - a process that takes a lot more computing power and time than a simple subquery.

The following WHERE clause filters for entries with total goals higher than three times the average.

SELECT 
	main.country_id,
	date,
	main.home_goal, 
	main.away_goal
FROM match AS main
WHERE 
	(home_goal + away_goal) > 
		(SELECT AVG((sub.home_goal + sub.away_goal) * 3)
		 FROM match AS sub
		 WHERE main.country_id = sub.country_id);

Nested Subqueries in FROM

Now it’s getting complicated. Best design such queries from inner to outer.

SELECT
	c.name AS country,
	AVG(outer_s.matches) AS avg_seasonal_high_scores
FROM country AS c
LEFT JOIN (
		SELECT country_id, season,
			COUNT(id) AS matches
		FROM (
			SELECT country_id, season, id
			FROM match
			WHERE home_goal >= 5 OR away_goal >= 5) AS inner_s
		GROUP BY country_id, season) AS outer_s
ON c.id = outer_s.country_id
GROUP BY country;

Common Table Expressions (CTE)

CTEs are declared and executed ahead of the main query and help organizing queries with complicated subqueries.

They always precede the main query and go WITH cte AS (SELECT…..)

WITH home AS (
	SELECT m.id,
		m.date,
		t.team_long_name AS hometeam,
		m.home_goal
	FROM match AS m
	LEFT JOIN team AS t 
	ON m.hometeam_id = t.team_api_id),

away AS (
	SELECT m.id,
		m.date, 
		t.team_long_name AS awayteam,
		m.away_goal
  	FROM match AS m
  	LEFT JOIN team AS t 
  	ON m.awayteam_id = t.team_api_id)

SELECT 
	home.date,
	home.hometeam,
	away.awayteam,
	home.home_goal,
	away.away_goal
FROM home
INNER JOIN away
ON home.id = away.id;

Window Functions - They all use OVER()

The OVER() clause allows you to pass an aggregate function down a data set, similar to subqueries in SELECT, but run faster.

The empty OVER() generates column with an aggregate of the complete column and the same value in each row.

SELECT 
	m.id, 
	c.name AS country, 
   	m.season,
	m.home_goal,
	m.away_goal,
	AVG(m.home_goal + m.away_goal) OVER() AS overall_avg
FROM match AS m
LEFT JOIN country AS c ON m.country_id = c.id;

The following qerry with the OVER(ORDER BY...) calculates the running total/sum up to the current row.

SELECT
  athlete,
  medals,
  SUM(medals) OVER(ORDER BY athlete ASC) AS sum_medals
FROM athlete_medals
ORDER BY athlete ASC;

RANK()

Calculate a ranking and also order the query by rank.

Note: Assigns the same number to rows with identical values and skips over the next numbers. E.g.: 1,2,3,3,5,6,6,8…

The following query calculates the average goals per game and groups by league. The averages are ranked by league.

SELECT 
	l.name AS league,
	AVG(m.home_goal + m.away_goal) AS avg_goals,
	RANK() OVER(ORDER BY AVG(m.home_goal + m.away_goal) DESC) AS league_rank
FROM league AS l
LEFT JOIN match AS m 
ON l.id = m.country_id
WHERE m.season = '2011/2012'
GROUP BY l.name
ORDER BY league_rank;

SELECT
  athlete,
  medals,
  RANK() OVER (ORDER BY medals DESC) AS Rank_N
FROM Athlete_Medals
ORDER BY Medals DESC;

DENSE_RANK()

Note: Assigns same number to rows with identical values but does NOT skip over the next numbers. E.g.: 1,2,3,3,4,5,5,6…

SELECT
  country,
  athlete,
  DENSE_RANK() OVER (PARTITION BY country ORDER BY medals DESC) AS rank_n
FROM athlete_medals
ORDER BY country ASC, rank_n ASC;

Giving rows a number with ROW_NUMBER()

Generate a column that counts the current ROW_NUMBER().

SELECT
  *,
  ROW_NUMBER() OVER() AS Row_N
FROM Summer_Medals
ORDER BY Row_N AS

The ORDER BY inside the OVER() clause takes precedence over outer ORDER BY. The following queries only calls distinct years and does not requrie a subquery.

SELECT
  Year,
  ROW_NUMBER() OVER(ORDER BY Year ASC) AS Row_N
FROM (
  SELECT DISTINCT Year
  FROM Summer_Medals
) AS Yea

Framing for sliding windows using RANGE BETWEEN…AND or ROWS BETWEEN…AND

Keywords for sliding windows are UNBOUNDED, n PRECEDING, n FOLLOWING, and CURRENT ROW

The window is supposed to run sum and averages between the current row and the top of the table or bottom of the table. They both arrive at the same cumulative sum, but the way to get there is different.

Difference RANGE vs. ROW: RANGE() treats duplicates in OVER(ORDER BY...) as a single entity, which is a special case. ROWS behaves normally.

The following query is calculating the Running Total (RT) and Running Aaverage (). The window uses ascending date ordering (smallest date number at top) -> the window goes from top (oldest date) of table to current row.

SELECT 
	date,
	home_goal,
	away_goal,
	SUM(home_goal) OVER(ORDER BY date 
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
	AVG(home_goal) OVER(ORDER BY date 
		ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
FROM match
WHERE 
	hometeam_id = 9908 
	AND season = '2011/2012';

The following query is calculating the RT and RA in descending date ordering (largest date number at top) -> the window goes from current row to bottom (oldest date).

SELECT 
	date,
	home_goal,
	away_goal,
	SUM(home_goal) OVER(ORDER BY date DESC
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_total,
	AVG(home_goal) OVER(ORDER BY date DESC
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_avg
FROM match
WHERE 
	awayteam_id = 9908 
    AND season = '2011/2012';

Moving average (MA). Calculate a column with the 3-year (2 preceding + current) average.

SELECT
	year,
	medals,
	AVG(medals) OVER(ORDER BY year ASC
		ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS medals_ma
FROM russian_medals
ORDER BY year ASC;

Fetching relative rows: LAG() and LEAD()

The LAG(column, n) function fetches a single value from BEFORE (above) the current row (given vertical lag distance) for creating a new column.

The following query determins (1) for each year’s Olympics which the gold medal country is and (2) which country is the reigning/last champion country.

SELECT
	Year, Champion,
	LAG(Champion) OVER(ORDER BY year ASC) AS Last_Champion
FROM weightlifting_gold
ORDER BY year ASC;

The LEAD(column, n) function fetches a single value from AFTER (below) the current row (given vertical lag distance) for creating a new column.

SELECT
	year,
	athlete,
	LEAD(athlete, 3) OVER (ORDER BY year ASC) AS future_champion
FROM discus_medalists
ORDER BY year ASC;

Fetching absolute rows: FIRST_VALUE() and LAST_VALUE()

These functions do not depend on the current row’s position. FIRST_VALUE(column) and LAST_VALUE(column)

The following query fetches the gold-winner athlete and a second column with the first athlete. The order is alphabetically.

SELECT
	athlete,
	FIRST_VALUE(athlete) OVER(ORDER BY athlete ASC) AS First_Athlete
FROM all_male_medalists;

Fetching the last value is more complicated, since it requires the definition of the RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Otherwise the last value of the window is the current row, and that’s now what we mean.

SELECT
	year,
	city,
	LAST_VALUE(city) OVER(ORDER BY year ASC
		RANGE BETWEEN
		UNBOUNDED PRECEDING AND
		UNBOUNDED FOLLOWING
		) AS last_city
FROM hosts
ORDER BY Year ASC;

PARTITION BY subdivides processing ranges in a column

Window functions can be applied to separate columns into processing sections. This way, it can be avoided that functions leak over from one section into another, e.g. when using ROW_NUMBER or LAG() functions.

The following query partitions the table by gender and avoids the LAG() function determining gold winners of ‘male’ spilling into ‘females’. In that case, a null value will be displayed.

SELECT
	gender,
	year,
	country AS champion,
	LAG(country,1) OVER(PARTITION BY gender ORDER BY year ASC) AS last_champion
FROM athletics_gold
ORDER BY gender ASC, year ASC;

Partition by multiple columns (gender and event).

SELECT
	gender,
	year,
	event,
	country AS champion,
	LAG(country,1) OVER (PARTITION BY gender, event ORDER BY year ASC) AS last_champion
FROM athletics_gold
ORDER BY event ASC, gender ASC, year ASC;

Paging with NTILE()- subdivide a table into equal-length pages

NTILE(num_pages) OVER(ORDER BY column) AS new_column divides the table into num_pages depending on how the values in the given column. The new column contains the number of which page they were assigned. These pages are practically quantiles on which statistical analysis can be performed.

The following query subdivides the ordered medals into three pages.

SELECT
	athlete,
	medals,
	NTILE(3) OVER(ORDER BY medals DESC) AS third
FROM athlete_medals
ORDER BY medals DESC, athlete ASC;

Perform aggregate function analysis on the pages/quantiles.

The following query gets the average medals earned in each page.

WITH thirds AS (
	SELECT
		athlete,
		medals,
		NTILE(3) OVER(ORDER BY medals DESC) AS third
	FROM athlete_medals)
  
SELECT
	third,
	AVG(medals) AS avg_medals
FROM thirds
GROUP BY third
ORDER BY third ASC;

Pivoting with the CROSSTAB() Extension

Pivoting with CROSSTAB requires the tablefunc extension that needs to be created befor qerrying.

A regular query gets entered into the SELECT * FROM CROSSTAB($$....) AS ct (column COLTYPE,...) function and the new pivoted column names defined.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
	SELECT
		gender,
		year,
		country
	FROM summer_medals
	WHERE
		year IN (2008, 2012)
		AND medal = 'gold'
		AND event = 'pole vault'
	ORDER BY gender ASC, year ASC;
$$) AS ct (gender VARCHAR,
           "2008" VARCHAR,
           "2012" VARCHAR)
ORDER BY gender ASC;

Original:

Pivot by year:

Group total rows with GROUP BY ROLLUP() and CUBE()

ROLLUP() vs CUBE()

Use ROLLUP() for hierachical data such as data parts (year, quarter) and don’t wall all possible group-level aggregations. Use CUBE() for all possible group-level aggregations

Null entries in the rows signifies the total rows.

The following query counts medals by country and gender. At the gender group level there is a total row (male+female) using the ROLLUP(gender) clause.

SELECT
	country,
	gender,
	COUNT(*) AS gold_awards
FROM summer_medals
WHERE
	year = 2004
	AND medal = 'Gold'
	AND country IN ('DEN', 'NOR', 'SWE')
GROUP BY country, ROLLUP(gender)
ORDER BY country ASC, gender ASC;

Group totals by all possible groups with CUBE(). Note: all columns have to go into the CUBE() or it will fail.

SELECT
	gender,
	medal,
	COUNT(*) AS awards
FROM Summer_Medals
WHERE
	year = 2012
	AND country = 'RUS'
GROUP BY CUBE(gender, medal)
ORDER BY gender ASC, medal ASC;

Fill Null values with COALESCE()

Useful for any function that produces nulls, such as ROLLUP(), CUBE(), pivoting, first/last row of LAG(), LEAD().

Use in SELECT section of query: COALESCE(column, 'null filler string') AS column.

SELECT
	COALESCE(country, 'all countries') AS country,
	COALESCE(gender, 'all genders') AS gender,
	COUNT(*) AS awards
FROM summer_medals
WHERE
	year = 2004
	AND medal = 'gold'
	AND country IN ('DEN', 'NOR', 'SWE')
GROUP BY ROLLUP(country, cender)
ORDER BY country ASC, gender ASC;

String Manipulation

Compress a Column into a single String with STRING_AGG()

Use in SELECT statemtt like as such: STRING_AGG(column, 'separator')

SELECT
	STRING_AGG(country, ', ')
FROM country_ranks
WHERE rank_col <= 3;

Input Table:

Output string: USA, RUS, AUS

Concatenating Multiple Columns with || and CONCAT()

Merge strings with ||

SELECT 
	first_name || ' ' || last_name AS full_name
FROM
	film;

CHARLOTTE HUNTER

Another example:

SELECT
	customerID || ': ' || first_name || ' ' || last_name AS full_name;

1: CHARLOTTE HUNTER

Merge strings with posgreSQL’s CONCAT()

SELECT
	CONCAT(first_name,' ', last_name) AS full_name
FROM
	film;

1: CHARLOTTE HUNTER

Changing Case with UPPER(), LOWER(), INITCAP()

SELECT
	UPPER(email)
FROM customer;

[email protected]

SELECT
	LOWER(email)
FROM customer;

[email protected]

SELECT
	INITCAP(title)
FROM film;

Police Academy

Replace Characters with REPLACE()

SELECT
	REPLACE(email, 'hedgeAThog.com', '[email protected]') AS email
FROM customer;

Trim Characters with TRIM(), LTRIM(), RTRIM()

TRIM([leading | trailing | both][characters] from string)

If just the column string is passed to TRIM(), then all space chars is be removed by default.

If no trim char string passed, is entered, then space char is the default.

SELECT
	TRIM(),
	LTRIM('    [email protected]    '),
	RTRIM('    [email protected]    ')

‘[email protected] ‘, ‘ [email protected]’

Pad Characters with PAD(), LPAD(), RPAD()

Syntax: LPAD(input_string, length_after_pad, lpad_string) Note that if the input string is shorter than the padded length, then no padding string will be inserted! If no padding string passed, is entered, then space char is the default.

SELECT
	LPAD('[email protected]', 20, '#'),
	RPAD('[email protected]', 17, '$')

’#######[email protected]’, ‘[email protected]\(\)$’

Reverse char order with REVERSE()

SELECT
	email,
	REVERSE(email)
FROM customer;

[email protected], moc.goh@egdeh

Character Count with CHAR_LENGTH() or LENGTH()

SELECT
	title,
	CHAR_LENGTH(title)
	LENGTH(title)
FROM film;

Police Academy, 14, 14

Find Char Position with STRPOS() or POSITION()

Remember, that SQL indexing starts at 1, not 0!

SELECT
	email,
	STRPOS(email, '@'),
	POSITION('@' IN email)
FROM customer;

[email protected], 6, 6

Clip Strings from the Ends with LEFT(), RIGHT(), and SUBSTRING()

LEFT(), RIGHT(): Specify how many characters to be clipped starting from the ends.

SELECT
	RIGHT(column_name, 50),
	LEFT(column_name, 10)
FROM customer;

SUBSTRING()/SUBSTR(): Specify the start index and length.

SELECT
	SUBSTRING(column_name, 10, 50),
	SUBSTR(colum_name, 10, 50)
FROM customer;

SUBSTRING(): Use functions to determine start/end index with FROM…FOR expression.

SELECT
	SUBSTRING(email FROM 0 FOR POSITION('@' IN email)), -- name part of address up to @
	SUBSTRING(email FROM POSITION('@' IN email)+1 FOR CHAR_LENGTH(email)) -- provider part of address starting after @
FROM customer;

hedge, hog.com

Clip a column to 50 characters but do not clip a word in half.

SELECT
	LEFT(description, 50 - POSITION(' ' IN REVERSE(LEFT(description, 50)) -- Subtract the position of the first whitespace character
FROM film;

Data Types

Casting Data Types

PostgreSQL native casting with two colons column :: data-type

SELECT
	NOW():: timestamp;

Standard SQL casting with CAST(column AS data-type) function.

SELECT
	CAST(NOW() AS timestamp);

Creating Data Types (PostgreSQL)

CREATE
	TYPE dayofweek as ENUM(
		'Monday',
		'Tuesday',
		'Wednesday',
		'Thursday',
		'Friday',
		'Saturday',
		'Sunday'
		);

Getting Information about Data Types (PostgreSQL)

SELECT
	typname,
	typcategory
FROM pg_type
WHERE typname = 'dayofweek';

User-Defined Functions (PostgreSQL)

A User-Defined Function is the equivalent of a Stored Proceedure.

A function can bundle several SQL queries together into a package using CREATE.

CREATE FUNCTION squared (i integer) RETURNS integer AS $$
	BEGIN
		RETURN i*i;
	END;
$$ language plpgsql;	--define language

SELECT
	squared(10);

100

Text Search and NLP (PostgreSQL)

Full-Text Search (PostgreSQL)

Postgre has extensions of the normal WHERE column LIKE '%mystring%' with wildcards (%,_) etc. capabilities.

Additional features are stemming, fuzzy matching (spelling mistakes), and ranking.

SELECT
	title,
	description
FROM film
WHERE to_tsvector(title) @@ to_tsquery('elf');

Compare Two Strings (PostgreSQL)

Calculate the Levenshtein Distance (number of edits required between two words).

SELECT
	levenshtein('hugo', 'hagolm');

3

Calculate Similarity (0-1)

	similarity('hugo', 'hagolm');

Drop Duplicate Rows (based on Specific Columns)

Drop duplicate rows based on specific columns, not the entire rows is duplicated.

Use a Common Table Expression (CTE) with a window function ‘PARTITION BY’ that creates a row number per window.

Any duplicates will have a row number >2. Then, evaluate the CTE and only keep row numbers = 1.

WITH cte AS (
    SELECT *, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                col_1, 
                col_2, 
                col_3
			ORDER BY 
                col_1, 
                col_2, 
                col_3
        ) row_num
    FROM orders_table
)
SELECT * 
FROM cte
WHERE row_num = 1;

Time Manipulation

• NOW() and CURRENT_TIMESTAMP() fetch time (millisecond precision) with timezone! However, CURRENT_TIMESTAMP(2) can be passed an argument for roundeding to digits. If the timezone is not required, then these two functions can be cast to timestamp data-type.
• CURRENT_DATE only fetches the date portion of the CURRENT_TIMESTAMP()
• CURRENT_TIME() only fetches the time portion (with timezone) of the CURRENT_TIMESTAMP(). Can be passed rounding parameter.

Intervals and Time Differences

This query selects a timestamp style column and performs arithmetic on it with the INTERVAL function. The INTERVAL add 3 days to the rental date so we can compare if the return date was in time.

SELECT
	rental_date,
	return_date,
	rental_date + INTERVAL '+ `+3 days' AS expected_return_date
FROM rental;

Convert integers to time intervals.

SELECT
	title,
	INTERVAL '1' day * f.rental_duration AS rental_duration,
	return_date - rental_date AS days_rented
FROM film
WHERE return_date IS NOT NULL

The AGE() function calculates an INTERVAL between a given timestamp/date and NOW if ONE parameter given. If TWO parameters are given, then the interval between those.

SELECT
	AGE(rental_date)
FROM rental;

Extracting from Timestamps

Two useful functions are EXTRACT(field FROM source) and DATE_PART('field',source), which produce interchangeable results.

Timestamp parts/fields to extract:

Examples for the quarter of the year in a timestamp:

SELECT
	EXTRACT(quarter FROM TIMESTAMP '2009-02-12 05:18:00') AS quarter;

1

SELECT
	DATE_PART('quarter', TIMESTAMP '2009-02-12 05:18:00') AS quarter;

1

SELECT 
	EXTRACT(dow FROM rental_date) AS dayofweek, 
	COUNT(*) as rentals 
FROM rental 
GROUP BY 1; -- column called by positional index instead of names

The following query extracts quarters and years from a timestamp column and sums the payment amount by by quarter and years.

SELECT
	EXTRACT(quarter FROM payment_date) AS quarter,
	EXTRACT(year FROM payment_date) AS year,
	SUM(amount) AS total_payments
FROM payment
GROUP BY 1, 2; 

The following query extracts the rental duration (rental_days) in a time interval of 90 days starting from 2005-05-01.

SELECT 
	EXTRACT(dow FROM r.rental_date) AS dayofweek,
	AGE(return_date, rental_date) AS rental_days
FROM rental AS r 
WHERE rental_date
	BETWEEN CAST('2005-05-01' AS TIMESTAMP)
	AND CAST('2005-05-01' AS TIMESTAMP) + INTERVAL '90 day';

Truncating Timestamps

The DATE_TRUNC() function will truncate a timmestamp or interval data types from fine detail (e.g., minutes) to low detail (e.g., year). This means the timestamp’s length remains the same but set to the lowest logical value (01 for days/months, 00 for hours/minutes/seconds).

SELECT
	DATE_TRUNC('year', TIMESTAMP '2009-02-12 05:18:00';

2009-01-01 00:00:00

SELECT
	DATE_TRUNC('month', TIMESTAMP '2009-02-12 05:18:00';

2009-05-01 00:00:00

The following query truncates to a day and counts the number of entries (rentals) by day.

SELECT 
	DATE_TRUNC('day', rental_date) AS rental_day,
	COUNT(*) AS rentals 
FROM rental
GROUP BY 1;

Transactions (MS SQL SERVER)

Transactions move around values between owners. For example money between bank accounts. This means a transaction contains a subtraction and addition that both have to succeed.

The concept of “Atomic transactions” rolls back a transaction if any of its parts raised an error. SQL has a specific ‘TRAN’ keyword that can be put in a ‘TRY’ block. If the ‘TRAN’ raised an error, the ‘CATCH’ block calls a ‘ROLLBACK TRAN’.

With TRY/CATCH

BEGIN TRY  
	BEGIN TRAN;
		UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 4451;
		INSERT INTO transactions VALUES (4451, -100, GETDATE());
        
		UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 6743;
		INSERT INTO transactions VALUES (4451, 100, GETDATE());
	COMMIT TRAN;
END TRY

BEGIN CATCH
	SELECT 'Rolling back the transaction';
	ROLLBACK TRAN;
END CATCH

With XACT_ABORT

With ‘SET XACT_ABORT ON;’ errors in a ‘TRAN’ block will always be rolled back. No ‘TRY’ block needed.

Note: ‘THROW’ error still works with XACT_ABORT.

SET XACT_ABORT ON;

BEGIN TRAN; 
	UPDATE accounts set current_balance = current_balance - current_balance * 0.01 / 100
		WHERE current_balance > 5000000;
	IF @@ROWCOUNT <= 10	
		THROW 55000, 'Not enough wealthy customers!', 1;
	ELSE		
		COMMIT TRAN;

Special Case: Accessing ARRAYS

It is not necessary to fetch the entire array of a column. We can select specific elements with square brackets. Indexing in PostgresSQL starts with 1, and not 0 as with many other languages.

Search for text in ARRAY in specific position.

SELECT
	email[1][1] AS type,			-- select from first address the first element (address type/description)
	email[1][2] AS address,			-- select from first address the second element (email address)
	test_scores[1]				    -- select only first element of the array
FROM funky_table
WHERE email[1][1] = "work";			-- only search in in array element 1 and sub-element 1

Search for text in any ARRAY position with ANY or @> operators. Results are equivalent.

SELECT
	email[2][1] AS type,			-- select from second address the first element (address type/description)
	email[2][2] AS address,			-- select from second address the second element (email address)
	test_scores[1]				    -- select only first element of the array
FROM funky_table
WHERE 'home' = ANY (email)			-- select where the column `email` contains an array element named 'home' in any array position)

SELECT 
  email[2][1] AS type, 
  email[2][2] AS address
  test_scores[1]
FROM funky_table
WHERE email @> ARRAY['home'];

SQL query in Python Pandas

The SQL query is passed as a single string. The tripple quotation mark wrapping (“””) is useful to have multi-row strings in Python. The table name goes in quotation marks and the SQL server connection has to be passed after the query.

Connection URI (Uniform Resource Identifier) for PostgreSQL: postgresql://[user[:password]@][host][:port][/database]

import sqlalchemy

connection_uri = "postgresql://repl:password@localhost:5432/pagila"
db_engine = sqlalchemy.create_engine(connection_uri)

import pandas as pd

df = pd.read_sql("""
SELECT
    first_name,
    last_name
FROM "Customer"
ORDER BY last_name, first_name
""", db_engine)

df.head(3)

ETL process

1. For the “Extraction” process from a OLAP db, the following function queries the entire SQL table using a function and loads it into a Pandas dataframe.

import sqlalchemy
import pandas as pd

# custom extraction function
def extract_table_to_pandas(tablename, db_engine):
    query = "SELECT * FROM {}".format(tablename)
    return pd.read_sql(query, db_engine)

connection_uri = "postgresql://repl:password@localhost:5432/pagila" 
db_engine = sqlalchemy.create_engine(connection_uri)

extract_table_to_pandas("film", db_engine)

1. Do some column transformations in Spark e.g., convert float to string and split on pattern into multiple columns.

2. Loading into analytical PostgreSQL database

connection_uri = "postgresql://repl:password@localhost:5432/dwh"
db_engine_dwh = sqlalchemy.create_engine(connection_uri)

film_pdf_joined.to_sql("film", db_engine_dwh, schema="store", if_exists="replace")

# check the table in the SQL db
pd.read_sql("SELECT film_id, recommended_film_ids FROM store.film", db_engine_dwh)

Table Management

Create Table

Creating a table schema works with [column name] [data-type]. ARRAYS can be created with square brackets. Multiple square brackets are nested arrays.

CREATE TABLE funky_table (
	column_1 text,
	column_2 integer,
	column_3 scores[],
	employee_id integer,
	email text[][]
	);

Insert Values into Columns

Inserting works with calling the column names if only specific columns are inserted into.

INERT INTO funnky_table
	(column_1, column_2) VALUES ('string for column_1', 12);

Inserting multiple rows into all columns at once can be done without column names, but the order has to match the columns. ARRAYS are inserted with curly braces {} and array elements separated by commas.

INSERT INTO funky_table
	VALUES ("Prickly Hedgehog",
		2,
		{99,83,18,811}, -- standard array with four elements
		2224,
		{\{"work", "[email protected]"},{"home", "[email protected]"}}  -- nested array (remove escape char \)
		);

System Database Information Gathering

Which tables are in the system database?

SELECT * 
FROM
	information_schema.tables
WHERE
	table_schema = 'public';

Which columns are in the ‘actor’ table and what are their details?

SELECT * 
FROM
	information_schema.columns
WHERE
	table_name = 'actor';

Column descriptions are detailed: table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length ….. and so on.

Extension Management (PostgreSQL)

Show Extensions

Available in the distribution ready to be installed.

SELECT
	name
FROM pg_available_extensions;

dblink, pg_stat_tatements, pg_trgm

Currently installed and ready for use.

SELECT
	extname
FROM pg_extension;

plpgsql

Install/Import Extensions

First install and then check if the installation succeeded.

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
SELECT
	extname
FROM pg_extension;

The New Business of AI (and How It’s Different From Traditional Software) - a16z

Posted in Projectblog ∙ 7 mins read

Summary from reading the Andreessen Horowitz blog

Share:   Twitter   Reddit   LinkedIn   Email

1. Preface
2. Introduction
3. Software + Services = AI?
   1. Software Company
   2. Service Company
   3. AI Company
4. Gross Margins, Part 1: Cloud costs can be massive for AI
5. Gross Margins, Part 2: Many AI applications rely on “humans in the loop”
6. Scaling AI systems can be rockier - AI lives in the long tail
7. The how to defend AI businesses has to be figured out
8. Building, scaling, and defending great AI companies – practical advice for founders
9. Summary

The writeup below are some highlights I wrote for myself. If you find the writeup useful and want to know more, then you should absolutely read the article linked.

Preface

This is my personal summary from reading The New Business of AI (and How It’s Different From Traditional Software), which was published on the Andreessen Horowitz blog a16z.com, a Silicon Valley venture capital (VC) investor. This VC supported the likes of Skype and Facebook when they were still little known and dozens of others whos digital services we are using in our daily lives. The article is excellently written, and part of the AI in 2020 series. This series is a distillation of dozens of interviews the authors lead with ML tams and thus marks a great resource for machine learning professionals who want deep insights into new AI developments, be it startups or mature companies. And last but not least it helps evaluating the next job opportunities. Most of all, the series is intended by Andreessen Horowitz to support AI companies, a sector in which they are investing heavily.

Introduction

Investors are betting today that AI companies will make a similar return as software companies.

However, in many cases that AI companies have a different economic construction than software businesses:

• Lower gross margins -> heavy cloud infrastructure + human support

• Scaling problems due to edge cases.

• Weaker defensive moats -> commoditization of AI models and challenges with data network effects.

Consistent pattern in the financial data of AI companies: gross margins 50-60% range, 60-80%+ for SaaS businesses. Early investors pushing AI companies for rapid growth over returns may obscure lower growth rates. AI is going to be a new type of business - after SaaS - and it is clearly different from Software. The differences have to be acknowledged and addressed.

Software + Services = AI?

Software Company

Software (including SaaS) can be produced once and sold many times. Benefits are: A) recurring revenue streams, B) high (60-80%+) gross margins, and C) super linear scaling with (rare) network effects, D) ownership of intellectual property (source code) builds defensive moats.

Service Company

Each new project requires dedicated headcount and can be sold exactly once: B) revenue is non-recurring, B) gross margins are lower (30-50%), C) and scaling is linear at best, D) Defensibility is challenging – often based on brand or incumbent account control – because any IP not owned by the customer is unlikely to have broad applicability.

AI Company

Appear to combine elements of both software and services. Most AI applications look + feel like normal software: interfacing with users, managing data, or integrating with other systems. However, at the center is a set of trained data models. Maintaining these models is more like a services business. That is: requiring significant, customer-specific work and input costs beyond typical support and success functions.

Gross Margins, Part 1: Cloud costs can be massive for AI

• AI is very demanding on cloud infrastructure: training, inference, and retraining due to data-drift, large file size input data and model transfer across cloud regions are more demanding than traditional software operations.

• Combined, 25% (or more) of revenue are spent on cloud resources. Manual processing can be cheaper in a company’s early start-up phase.

• Model complexity is growing at fast rate. 10x data is needed for 2x model precision. CPU/GPUs cannot be accelerated and made cheaper fast enough. Distributed computing solves speed issues, but not costs.

Gross Margins, Part 2: Many AI applications rely on “humans in the loop”

• Training most of today’s state-of-the-art AI models involves the manual cleaning and labeling of large datasets. This process is laborious, and expensive.

• For many tasks, especially those requiring greater cognitive reasoning, humans are often plugged into AI systems in real time.

• The need for human intervention will likely decline as the performance of AI models improves. It’s unlikely that humans will be removed entirely.

• How much will margins increase with AI fully automated? Reducing human intervention (and their costs) will likely increase cloud costs.

Scaling AI systems can be rockier - AI lives in the long tail

• New customers increases the work for the ML team when the models have to be adjusted for these new customers -> drawing resources away from new sales. The problem is edge cases. Many AI apps have open-ended interfaces and operate on noisy, unstructured data (e.g., images, NLP).

• Handling edge cases means endless possible input values. Each new customer deployment is likely to infer on completely new data.

• AI companies spend time re-training the model for each new customer to eliminate edge cases prior to deployment until accuracy reaches acceptable levels.

• Startups spend more time + resources for deployment than expected. Identifying challenges in advance can be difficult since traditional prototyping tools (mockups, prototypes, beta tests) do not cover edge cases.

The how to defend AI businesses has to be figured out

• Some of the best defensive moats are network effects, high switching costs, and economies of scale. Additionally, branding comes from first to market and near-exclusivity.

• For AI players, differentiation is harder: New model architectures are developed mostly in open, academic settings. Reference implementations (pre-trained models) are available from open-source libraries, and hyperparameters can be optimized automatically.

• While data is at the heart of AI, it is often owned by customers, in the public domain, or over time becomes a commodity.

• Moats for AI companies appear to be shallower than for software and shallower than expected.

Building, scaling, and defending great AI companies – practical advice for founders

• Reduce model complexity: If possible, share a single model (or set of models) among all customers. It is easier to maintain, faster to roll out, and supports a simpler, more efficient engineering organization. Result: minimize cloud costs.

• Choose problem domains carefully and narrowly: The minimum viable task for AI models is narrower than expected. Choose problems that are hard for humans but easy for AI. That is high scale, low complexity tasks.

• Plan for high variable costs: build a business model and Go-To-Market strategy with lower gross margins in mind. Understand the distribution of the model data. Model maintenance and human failover are first-order problems. Measure real variable cost (no hiding in R&D). Make conservative assumptions in financial models (esp. fundraise). Scale and (outside) tech advances may not solve the problem.

• Embrace services: Building hybrid businesses (software/AI plus service) is harder than pure software, but this approach can provide deep insight into customer needs and yield fast-growing, market-defining companies.

• Plan for change in the tech stack: AI is still new. Standardized tools are just now being built: tools to automate model training, make inference more efficient, standardize developer workflows. Tightly coupling an application to the current state may lead to an architectural disadvantage in the future.

• Build defensible: It is unclear whether an AI model or the underlying data is a long-term moat. Good products and proprietary data almost always build good businesses. AI may vitalize old markets. Effective cloud strategies are key.

Summary

Most AI systems are not like software and such the business works differently. AI businesses scale not as easily, and it is harder to build defensibility. Taken together, that makes AI feel more like a service business. These are unfamiliar patterns, which indicate that AI companies are truly something new, including massive opportunities.

Taming the Tail - Adventures in Improving AI Economics - a16z

Posted in Projectblog ∙ 7 mins read

Summary from reading the Andreessen Horowitz blog

Share:   Twitter   Reddit   LinkedIn   Email

1. Preface
2. Introduction
3. Part I: Understanding the problem to be solved
   1. Building vs. experimenting (or, software vs. AI)
   2. The long tail and machine learning
   3. Impact on the economics of AI
4. Part II: Building Better AI Systems
   1. Toward a Solution
   2. Easy mode: Bounded problems
   3. Harder: Global long tail problems
   4. Really hard: Local long tail problems
   5. Table stakes: Operations
5. Summary

The writeup below are some highlights I wrote for myself. If you find the writeup useful and want to know more, then you should absolutely read the article linked.

Preface

This is my personal summary from reading Taming the Tail: Adventures in Improving AI Economics, which was published on the Andreessen Horowitz blog a16z.com, a Silicon Valley venture capital (VC) investor. This VC supported the likes of Skype and Facebook when they were still little known and dozens of others whose digital services we are using in our daily lives. The article is excellently written, and part of the AI in 2020 series. This series is a distillation of dozens of interviews the authors lead with ML tams and thus marks a great resource for machine learning professionals who want deep insights into new AI developments, be it startups or mature companies. And last but not least it helps evaluating the next job opportunities. Most of all, the series is intended by Andreessen Horowitz to support AI companies, a sector in which they are investing heavily.

Introduction

AI companies is different from traditional software companies. Often they have lower margins, are harder to scale, and don’t always have strong defensive moats.

Part I: Understanding the problem to be solved

(…in the presence of long-tailed data distributions)

Building vs. experimenting (or, software vs. AI)

Traditional software is a task of engineering. The specifications of a product are defined and then slowly added to the software - one line of code at a time.

AI developers build statistical models, measure them, build a new one, measure, and so on. Until a satisfactory quality threshold was crossed, the AI modeler has to go through this experimentation loop. The article quotes an AI CTO that the process is “closer to molecule discovery in pharma” than engineering. Another issue is that the code written by the AI developer only indirectly influences the output, which adds an additional layer of complexity. It mainly governs how the dataset is processed, but the quality of the outcome is unsure.

The long tail and machine learning

Many ML natural datasets have a long-tailed (skewed) frequency distribution. The long tail makes up ~70% of the total amount of samples. This makes it when picking randomly much more likely to receive a sample from the tail. Head and Middle, where the most common samples are, receive only 30% of picks.

However, the low-frequency samples from the tail are harder to train with supervised learning, while value is relatively low. These long-tail samples are often also called “edge cases” and are much harder to get right.

Impact on the economics of AI

• Improving models on the long tail costs a lot of compute resources and thus creates more (cloud) costs than traditional software would cause (3-5x higher).

• Data has a cost to collect, process, and maintain. That means the larger the long-tailed dataset becomes, the harder it becomes to work with it and more costly, too. The projects actually show “DISeconomies of scale”. While more data to improve a model is often available, the long-taildness also means a diminishing return. AI developers may need 10x more data to achieve a 2x subjective improvement. While computing power becomes cheaper and more available (Moore’s Law), this does not occur with model performance at the moment.

Part II: Building Better AI Systems

Toward a Solution

Economics of AI are a function of the problem (dataset modeling/characterizing causes computing costs), not part of the technology used (computing efficiency gains). Hence, the approach of the long-tail frequency distribution has to be optimized.

Easy mode: Bounded problems

First of all, it has to be determined if a long-tailed frequency dataset is there at all. If not, then no ML or DL should be used. Linear or Polynomial Regression will be much more efficient.

Logistic Regression and Random Forests are popular because they are A) interpretable, B) scalable, C) computationally efficient. DL may have its use cases in NLP (text generation and understanding), sentiment analysis but accuracy improvement has to be tightly balanced with training and maintenance costs.

ML is not always the best solution to everything, which should be very closely evaluated. Heuristic and if/then approaches may be much easier to implement.

Harder: Global long tail problems

Serving a single (global) model if there is large customer-segment/region overlap is much cheaper than building multiple models. -> “One size fits all”

Tactics to build around the long tail:

• Optimize the model -> add more (customer) training data, adjust hyperparameters, tweak architecture.
• Narrow the problem to the fat head -> restrict user-input complexity to avoid creating an unnecessary tail -> e.g. auto-complete text input for the user.
• Convert the problem -> human intervention for special edge cases or create single-turn interfaces that cover the long tail more easily.

Componentizing:

Train different simpler models to do different jobs concurrently. Example: Couldflare using 7 models to detect 6-7 different bot types attacking websites. Each model is global, and relatively easy to maintain.

Really hard: Local long tail problems

It is common to observe local problem (data) variation with customers, which do have to be addressed. Effort in modeling is based on the degree of local variation (countries, specific large customers….). Example: streaming services generating playlist models for each country.

How to bring the benefits of global models to local problems?

• Adoption of the meta-model technique, which covers multiple clients and tasks: e.g., combine thousands of user-specific models into a single, cheaper meta model.
• Transfer Learning: Using pre-trained models simplifies training needs and makes fine-tuning per customer easier. Example: GPT-3 NLP model that needs to be adjusted for local needs.
• Join similar model with a common trunk to reduce complexity. Making the trunk as thick as possible, the branches as thin as possible without decreasing accuracy. Example: Facebook combining models for furniture, cars, fashion which is cheaper and more accurate. Benefits: parallel model training and high local accuracy, yielding a global-pattern-like model.

Table stakes: Operations

Operational best practices to improve AI economics:

• Consolidate data pipelines. Do not use one pipeline per model. Combine multiple customers into one ETL process, re-train less often (only at night, or when enough data available).
• Edge case engine, a collection of odd long-tail samples and systematically collecting long-tail samples.
• On-Prem ML clusters with GPUs can unlock massive savings. Example: startup saved 10 Million USD with their own cluster in colocation facility compared to AWS.
• Model quantization, distillation, pruning, compression, and compilation (including APIs and pre-trained models)
• Frequent testing, not only accuracy, F1 etc. but also on A) bad data, B) privacy violations, C) precision mismatches, D) model drift, E) bias, and F) adversarial tactics.

Summary

AI is still in its early years and has not yet crossed the peak of the hype cycle. More efficiency is needed. Structures similar to software development will likely appear

My Machine Learning Model Is Perfect

Posted in Projectblog ∙ 5 mins read

Reasons that make ML experts look at your code twice.

Share:   Twitter   Reddit   LinkedIn   Email

1. Code that needs refactoring
   1. Breakout: Who has the smelliest code?
2. What does Machine Learning smell like?

Code that needs refactoring

Strange and hard-to-read code is a common reason to elicit refactoring (rewriting). Think of unstructured Spaghetti Code™ without a lot of documentation – or the opposite – too much documentation that you can’t see the forest for the trees anymore. These kind of code issues are called “code smell”. A term that has been widely adopted after 1999 when the book “Refactoring: Improving the Design of Existing Code” by Martin Fowler was published. Its essence, refactoring is applying a series of small behavior-preserving transformations with the cumulative effect of “reducing the risk of introducing errors”.

Some smells are easy to spot, some subtler and more insidious than others. While these problems and signs may not be detrimental by themselves, they warrant a closer look. You may have seen some of them before:

• Unnecessary complexity (a.k.a. showing off).
• Functions with too many arguments, indicating “God functions” without partitioning into helper-functions.
• Runaway function length across the screen making it hard to read.
• Excessive commenting making it hard to distinguish code from non-code.
• Duplicated code - That’s where the principle Don’t Repeat Yourself (DRY) comes from.

Have a look at the full list of code smells and their description on Wikipedia and what Anupam Chugh discusses in his TowardsDataScience post “5 Python Code Smells You Should Be Wary Of”.

Breakout: Who has the smelliest code?

At the time of writing this, Google Search users from Washington state were the most interested in code smells or at least ranking very highly. Check now on Google Trends. Is their code the smelliest of them all? Interest likely correlates with the amount of coders in a state and inevitably there is problematic code among.

Photo by @claybanks on Unsplash

What does Machine Learning smell like?

Almost all Machine Learning entails someone writing code. This means the known problems or smells may be emanating from the codebase - regardless if we have written it or someone else put it into a black box for us to call. Machine Learning models are traditional functions but are mathematically adhering to different paradigms: a function that relates sample value X to some target value y. While all the functions of a comprehensive ML/DL library such as PyTorch, TensorFlow, and Scikit-learn are hopefully optimized and free of code smells under the hood, a project involves more than just a single algorithm that’s invoked in a single function. The workflow of invoking the library’s functions bespoke to our project’s needs is a “metamodel” with design complexities of its own.

So what are the “ML smells” that might alert us to deeper problems in our prediction tools?

Dr. Matt Hall recently asked the following question on Twitter and in the geological machine learning community Software Underground he co-founded:

🐽 ML smell is probably not a thing, but maybe it should be. What are the superficial signs of potentially deeper problems in a #machinelearning project?— Matt Hall (@kwinkunks) October 7, 2020

Matt crowdsourced some excellent responses:

• Black-box services such as AutoML.

• Counterintuitive model weights -> known effects have low feature importance. (Reece Hopkins, Anchorage)

• Unreproducible, non-deterministic code -> not setting random seeds. (Reece Hopkins, Anchorage)

• No train–val–test split description/justification. Leakage between training and blind data is easy to introduce with random splits in spatially correlated data. (Justin Gosses, Houston)

• No evaluation metric discussion -> how it was selected or designed (Dan Buscombe, Flagstaff)

• No ground truth discussion and how the target labels relate to it. (Justin Gosses, Houston)

• Excessive hyperparameter precision -> might suggest over-tuning. (Chris Dinneen, Perth)

• Precision–recall trade-off not considered -> especially in a binary classification task. (Dan Buscombe, FLagstaff)

• Strong class imbalance and no explicit mention of how it was handled. (Dan Buscombe, Flagstaff)

• Skewed feature importance on one or two features might suggest feature leakage. (John Ramey, Austin)

• Making excuses -> “we need more data”, “the labels are bad”, etc. (Hallgrim Ludvigsen, Stavanger)

• Time spend cleaning data too short -> Less than 80-90% of the effort spent on preparing the data. (Michael Pyrcz, Austin)

• Very high accuracy, e.g., 0.99 for a complex model on a novel task. (Ari Hartikainen, Helsinki and Lukas Mosser, Athens). In most real-world scenarios accuracy near 0.7 is excellent, and anything >0.8 indicates something unusual going on.

That’s a long list, but there are most likely more.

Most likely we have our own biases and tendencies to produce some smell because we were not aware, did not know any better, or had no other choice. This is where peer review provides a second (or third, or fourth…) pair of eyes is critical and all smells get cleaned up.

What do you think?

How perfect is your ML code and what kind of surface phenomena have you seen that may indicate deeper-rooted problems in ML projects?

Maybe it is time for someone to officially coin a new term called “ML Smell”…..

Thank-you to Matt Hall whose question inspired me to write this up!

PhD Résumé and CoverLetter Template

Posted in Projectblog ∙ 1 min read

Don’t build from scratch!

Share:   Twitter   Reddit   LinkedIn   Email

1. Introduction
   1. How to
2. Preview
   1. Résumé
   2. CoverLetter

Introduction

You want:

• A résumé template that does not fall apart when you add a second page?
• Plenty of relevant sections to choose from that highlight your accomplishments.
• Matching cover letter and résumé documents that are neihter boring nor look like a unicorn barfed over it?
• Know why are there rubber gloves in the cover picture anyway?

This is going to easy, because this is a LaTeX template that has your needs covered. You are going to build the documents from source code by yourself.

The template code is an evolution of the “Friggeri CV”, which was later substantially enriched in features by Jesper Dramsch in his repo “latex-cv-boosted”.

How to

1. Download or clone this repo.
2. Install LaTeX if you don’t already have.
3. Substitute all the placeholders.
4. Build the code to a PDF. Done.

If this sounds compelling, then head over to the GitHub repository to learn more.

/ChristianHallerX/PhD_Resume_and_CoverLetter_Template

Preview

Résumé

CoverLetter

Physicists Dissecting "Interstellar"

Posted in Projectblog ∙ 2 mins read

Not even that…. good.

Share:   Twitter   Reddit   LinkedIn   Email

1. Accurate Science Fiction?
2. Bonus

Accurate Science Fiction?

I love watching science-fiction films. In fact, as Student Union President during my Master’s I used to organize geology-related films about which whe had recently rearned in lectures. At least geology-related science-fiction films are usually so bad that they are good again: The Core, 2012, San Andreas, Dante’s Peak etc. Just for reference, in The Core the main characters used a giant drill through magma in the earth to jump-start the metal core’s rotation…. cough.

Whenever I watch a sci-fi movie from a field I am less familiar with, I find it exciting to learn if the plot was at least marginally in accordance with the laws of nature. Honestly, there exits the full breadth of science-accuracy delivered by Hollywood from almost-documentary to completely bogus fluff. In my opinion, the Goldilocks situation is met if they achieve to combine excitement and awe with a reasonable adherence to laws of nature, e.g. “Jurassic Park”.

For the past New Years days I made it an occasion to re-watch “Interstellar” – a gem in the astrophysics realm I had already seen in the theater when it was released. Most of the plot seemed relatively plausible to me. But I am not an astrophysicist, and what do I know about the details of General Relativity? So let’s find out what the the professor of astrophysics at North Carolina State University, Dr. Katie Mack, has to say in her review for the World Science Festival.

Cinema Peer Review: Astrophysicist Katie Mack Reviews ‘Interstellar’

Well, returning from that read, I’m glad that Matthew McConaughey is still with us and did not have to be spaghettified. Dr. Mack brought up numerous pain points that I did not realize they were there. Ach, you were the chosen one, Interstellar.

Bonus

Scientific American: In “2012” neutrinos melt Earth’s core, and other disasters

American Geophysical Union: Cinema Peer Review: Astrophysicist Katie Mack Reviews “Interstellar”

GeoTimes: Watching The Core: A movie review

Arizona Geological Survey: Review: San Andreas – The Movie

Reading in 2020

Posted in Projectblog ∙ 6 mins read

What I read in 2020.

Share:   Twitter   Reddit   LinkedIn   Email

1. Introduction
2. R.S. Sutton and A.G. Barto: “Reinforcement Learning: An Introduction”
3. G.R.R Martin: “A Dance with Dragons”
4. P. Domingos: “The Master Algorithm”
5. C. Thompson: “Coders”
6. S. Pinker: “The Sense of Style”

Introduction

Whenever I am looking for a new book to read, I really enjoy looking at lists of books other people read. Since I started reading more again this year (covid19…), I thought it would be a good idea to write down some thoughts on the individual books. I ended up picking up a new book almost every month. In the following, I describe the more interesting ones of them.

R.S. Sutton and A.G. Barto: “Reinforcement Learning: An Introduction”

Download PDF from authors

Reinforcement learning is a subarea of Machine Learning with some essential differences to (un)supervised learning. The fundamental idea is that we want to train an agent that learns how to perform tasks in a dynamic environment. In contrast to supervised learning, the feedback given to the agent is delayed and does not tell it what the best action would have been.

Because reinforcement learning is a bit of a niche area in Machine Learning, I never really got into it when I was new to the field. Still, the fundamental idea sounded incredibly interesting to me, so in the holidays after the winter semester I decided to work through this book. This was just in time to read the reworked second edition.

The book was written by Sutton and Barto who are renowned experts in the field and came up with many of the most important ideas themselves. While the book does contain a large amount of math, it is still very accessible because everything is explained up from the ground up.

Mentally processing the book was a lot of fun and I got a lot of inspiration for the future. Reinforcement learning is the key element in the AWS Deep Racer and I still marvel at the way it builds up the entire framework step by step.

G.R.R Martin: “A Dance with Dragons”

Book @ Publisher

A Dance with Dragons is the fifth book from the A Song of Ice and Fire series that Game of Thrones is based on. After I read the first four book in 2015–2017, I was planning to read the other last book this year just so that I would be done when the sixth book was going to come out. Unfortunately, that book got delayed further and further and is not even out by now.

All in all, I really enjoyed the book and its excruiciating details that greatly tops the TV series. Remembering all the >100 characters per book is not that easy, as Martin loves to explain backstory that does not really pertain to the plot. The writing style is neihter too dramatic nor withdrawn and I enjoy how each chapter is told from the point of view of a different character. However, sometimes story lines in some threads is not touched in a long time and comes up perhaps once in a book. Then it sometimes is better to get a reminder what was going in that corner of the world a couple hundred of pages ago.

P. Domingos: “The Master Algorithm”

Book @ Publisher

The Master Algorithm is a pop-scientific book about machine learning. It gives a nice overview of fundamental concepts and talks about the history of the field. The book is written for a general audience and does not assume prior knowledge. Domingos uses a lot of analogies, some of which were great, while some did not hit the nail on the head, I think. However, it is evident that he put in a lot of effort into trying to explain machine learning to people that are not from the field.

The fundamental idea behind the book is that there are five different schools of thought in machine learning. These are the evolutionist, Bayesian, analogist, symbolist, and the currently dominating connectionist approach. Domingos argues that if we want to create a master algorithm that matches the learning power of our brain, ideas from all these tribes ought to be combined.

The first part of the book introduces the five tribes and their main ideas. This was the strongest part of the book and I will probably go through this again at some point. The second part attempts to describe how they could be combined. This feels a bit forced and felt fairly abstract, since it is not entirely clarified how this should work.

C. Thompson: “Coders”

Book @ Publisher

The book gives a good overview of subcultures and interests and tries to explain why some people feel drawn to them and coding in general. Thompson introduces the reader to coder culture and highlights a few case-study personalities he interviewed. He starts out with early history, such as computation at university servers, and later programming at home. Large part of the book is analyses of students in Computer Science programs and societal groups dominating the profession.

In general, the book gives a good overview of subcultures and interests and tries to explain why some people feel drawn to coding in general. But it is never too technical to scare off people not familiar with the subject matter, I felt.

S. Pinker: “The Sense of Style”

Book @ Publisher

The Sense of Style is a style guide that tries to explain how to write more elegantly and effectively. Now, there is no lack for how to write effectively, technical, scientifc, fast etc. However, Pinker’s work, Prof at Harvard U. Psychology Departmet, was smart enough to keep me reading – finding it even enjoyabl Partly, this is definitely because it is written by a highly eloquent author, which helps building your own style.

The book addresses not only stylistic choices but also dives into more psychological aspects. One of these points that I really internalized is what the author calls the curse of knowledge. By having a deep understanding of a topic, it becomes incredibly hard to explain it in a good way to people without much prior knowledge. This is due to the fact that we forget the difficulties we had when first learning about the topic ourselves. Now it all seems so simple and it’s easy to fall into the trap of thinking that certain parts are also obvious to other people.

Fashion MNIST

Posted in Projectblog ∙ 9 mins read

Machine Learning’s ‘Hello World V2’ Dataset with Clothing.

Share:   Twitter   Reddit   LinkedIn   Email

1. Introduction
2. Hands-On
3. Google ML introducing Fashion MNIST

Introduction

In 2017, Zalando Research published a dataset, which is very similar to the well known “Hello World” of Computer Vision: the MNIST database of handwritten digits. The dataset is designed for machine learning classification tasks and contains in total 60,000 training and 10,000 test images (gray scale) with each 28x28 pixel. Each training and test case is associated with one of ten labels (0–9). Up till here Zalando’s dataset is basically the same as the original handwritten digits data. However, instead of having images of the digits 0–9, Zalando’s data contains (not unsurprisingly) images with 10 different fashion products. Consequently, the dataset is called Fashion-MNIST dataset, which can be downloaded from GitHub or Kaggle. A few examples are shown in the following image, where each row contains one fashion item.

The 10 different class labels are:

1. T-shirt/top
2. Trouser
3. Pullover
4. Dress
5. Coat
6. Sandal
7. Shirt
8. Sneaker
9. Bag
10. Ankle Boot