Image for post
Image for post
  1. Database Transaction

(1) The Definition of Database Transaction

A transaction is a sequence of operations performed (using one or more SQL statements) on a database as a single logical unit of work. One operation can be treated as a transaction (single-operation transaction) and several operations can also be a transaction (transaction block).

(2) Recall: Autocommit mode

The auto-commit mode is the default mode for PostgreSQL and each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement. …

Image for post
Image for post
  1. AWS Identity and Access Management (IAM)

In practice, we have to collaborate with others. For the reason that several people should have the access to an instance, we have to manage the identity and the accessibility for that specific instance that we are going to use. This is called the IAM for AWS.

To change the accessibility of an instance or to create a new identity, from the AWS Management Console, we click on our user name and then choose “My Security Credentials”.

Image for post
Image for post
  • Create a new IAM user

An IAM user is different from the root user, and the IAM user only has the accessibility of a few instances selected by the root user. As a root user, we have the permission to create IAM users for a specific instance, however, the IAM user doesn’t have the permission to add or delete users. …

Image for post
Image for post
  1. REST API

The API is a computer interface that we can transfer data through different software. The REST API is a series of rules about API that makes the API easy to understand by its URL, method, and response. A simple way to understand REST is that REST is for giving data instead of the HTML web page.

2. The Dog API

The dog API is a free API that we can use to understand APIs. First of all, let’s go to the page,

https://api.thedogapi.com/v1/images/search

Then, it will randomly send back a record of the dog’s image data to us,

[{"breeds":[],"id":"IYnbVDoBJ","url":"https://cdn2.thedogapi.com/images/IYnbVDoBJ.jpg","width":1080,"height":1350}]…

Image for post
Image for post
  1. What is Cookie?

Some people believe the name for internet cookies came from the fairy tale about two children called Hansel and Gretel. The children were able to mark their trail through a dark forest by dropping “cookie crumbs” behind them so that they could see where they had been. This story paints a nice picture of the ability that internet cookies have to track your activity.

We have to use cookies because HTTP is a stateless protocol. For multiple requests from a browser, the server can not identify all these requests are from the same browser. …

Image for post
Image for post
  1. Import Packages
import copy
from math import *
import pandas as pd
import numpy as np
import scipy
from scipy import stats
from scipy.stats import kstest
from scipy.stats import boxcox
import scipy.linalg as linalg
from sklearn import linear_model
import statsmodels.api as sm
import statsmodels.formula.api as smf
from statsmodels.stats.diagnostic import het_breuschpagan
from patsy import dmatrices
from statsmodels.stats.outliers_influence import variance_inflation_factor
import matplotlib.pyplot as plt
import seaborn as sns
%config InlineBackend.figure_format='retina'

2. Subset Selection

An example instance is,

SubsetSelection(df_brand, 'BrandLiking')

The output is,

Image for post
Image for post

3. Forward Stepwise Model Selection (Automatically Select)

An example instance is,

ForwardSelection(df_icecream, 'cons', 'R_sq_adj')

The plot of the model selection is,

Image for post
Image for post

The output is,

'cons ~ temp + income + price'

Note that different methods can give different results,

ForwardSelection(df_icecream, 'cons', 'aic')

The plot of the model selection is,

Image for post
Image for post

The output is,

'cons ~ income + price + time + temp'

Image for post
Image for post
  1. Model Diagnosis Process for MLR
  • (0) Goal of Modeling
  • (1) Step 1. Check Multicollinearity
  • (2) Step 2. Fit the Initial Model
  • (3) Step 3. Check Influential Points
  • (4) Step 4. Check Heteroscedasticity
  • (5) Step 5. Check Normality
  • (6) Step 6. Check Linearity

You can find the topics above in part 1 and part 2.

Let’s continue our discussion.

(7) Step 7. Modify the initial model and fit the data again

Because we have modified the data so that our data can be able to suit the assumptions of our linear model. Because we can put different variables as our predictors and this results in several linear models. We have to choose the best model so that our final model is the best model. …

Image for post
Image for post
  1. Import packages
from math import *import pandas as pd
import numpy as np
import scipy
from scipy import stats
from scipy.stats import kstest
from scipy.stats import boxcox
import scipy.linalg as linalg
from sklearn import linear_model
import statsmodels.api as sm
import statsmodels.formula.api as smf
from statsmodels.stats.diagnostic import het_breuschpaganfrom patsy import dmatrices
from statsmodels.stats.outliers_influence import variance_inflation_factor
import matplotlib.pyplot as plt
import seaborn as sns
%config InlineBackend.figure_format='retina'

2. Build the model

model =smf.ols('y ~ x1 + x2 + x3', data=df).fit()

3. Check Multicollinearity

4. Check Influential Points

5. Check Heteroscedasticity

6. Check Normality

Image for post
Image for post
  1. Model Diagnosis Process for MLR
  • (0) Goal of Modeling
  • (1) Step 1. Check Multicollinearity
  • (2) Step 2. Fit the Initial Model
  • (3) Step 3. Check Influential Points
  • (4) Step 4. Check Heteroscedasticity

You can find the topics above in part 1.

Now let’s continue our discussion.

(5) Step 5. Check Normality

  • Reason: We have made an assumption of normally distributed error terms. As we have said in the SLR part, the normally distributed error terms assumption is a “strong” assumption so that it won’t affect the BLUE feature of the OLSE. …
Image for post
Image for post

1. JOIN

CROSS JOIN                    # cartesian product
JOIN ... ON ... # no NULL values
LEFT JOIN ... ON ... # values in the left table else NULL
RIGHT JOIN ... ON ... # values in the right table else NULL
FULL JOIN ... ON ... # values in both tables else NULL

2. Conditional Statement

CASE WHEN ... THEN ...
WHEN ... THEN ...
ELSE ...
END AS ...

3. Pivot Table

Input: finalscore table

+---------+----------+---------+
| sid | subject | grade |
+---------+----------+---------+
| 1 | Math | 94 |
| 1 | Physics | 87 |
| 2 | English | 82 |
| 3 | Math | 91 |
| 3 | English | 97 |…
Image for post
Image for post
  1. JSON Data Type For SQL
  • Create a table with JSON type
CREATE TABLE tablename (
colname JSON
);
  • Get the data type of data in a Column
SELECT pg_typeof(colname) 
FROM tablename;
  • Get the data in JSON as JSON type
SELECT colname -> '_id'
FROM tablename;
  • Get the data in JSON as text type
SELECT colname ->> '_id'
FROM tablename;
  • Convert a table to JSON
SELECT TO_JSON(tablename) 
FROM tablename;

2. Window Function in PostgreSQL

A window function performs a calculation across a set of table rows that are somehow related to the current row. It makes things easier for us because we don’t have to do JOINs if we want to write some specific kinds of queries. …

About

Adam Edelweiss

Knowledge is power, especially when it‘s shared. Know more about me at https://medium.com/adamedelwiess/about-me-1919c812476.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store